/pgcat-docker-compose-demo

Test setup with pgcat, postgres and pgbench

Start the cluster:

docker-compose up

Try with pgbench:

# https://www.postgresql.org/docs/current/pgbench.html
# init "postgres" database for pgbench
pgbench -p 6432 -h localhost -i -U postgres postgres
# baseline perf with 10 clients to the bare postgres
pgbench -p 5432 -h localhost -T 20 -C -c 10 -n -U postgres postgres
# 10 clients via pgcat
pgbench -p 6432 -h localhost -T 20 -C -c 10 -n -U postgres postgres
# 100 clients via pgcat
# bench with 100 clients for 20 seconds
# -C: new connection for each transaction
# -n: no vacuuming before running test
pgbench -p 6432 -h localhost -T 20 -C -c 100 -n -U postgres postgres

Benchmarks

The Ryzen and i7 (x86) numbers can't be right. Any ideas internet?

  • docker pgcat:latest (1.1.2-dev4) and postgres:16
    • on macOS, the pgcat docker was am64 emulated
  • pool size 10
  • pgtuned postgres: 4GB RAM, data warehouse, 20 connections, 10 cores
  • test platform details
    • m1max with 64GB RAM, 1TB SSD
    • 5900x with 48GB RAM allocated to WSL, fast 2TB PCIE 4.0 SSD
    • i7 with 48GB RAM, fast 1TB PCIE 3.0 SSD
Platform baseline 10 pgcat 10 pgcat 100
m1max 154 624 577
ryzen 5900x wsl 133 19 19
i7-8750H ubuntu 88 20 20

macbook pro m1max

baseline 10 clients to postgres

pgbench -p 5432 -h 127.0.0.1 -T 20 -C -c 10 -n -U postgres postgres
pgbench (14.12 (Homebrew), server 16.3 (Debian 16.3-1.pgdg120+1))
transaction type: <builtin: TPC-B (sort of)>
scaling factor: 1
query mode: simple
number of clients: 10
number of threads: 1
duration: 20 s
number of transactions actually processed: 3088
latency average = 64.810 ms
average connection time = 5.414 ms
tps = 154.298263 (including reconnection times)

10 clients via pgcat

pgbench -p 6432 -h 127.0.0.1 -T 20 -C -c 10 -n -U postgres postgres
Password:
pgbench: error: connection to server at "127.0.0.1", port 6432 failed: FATAL:  password authentication failed for user "postgres"
charlbotha@meepm1max sandbox % pgbench -p 6432 -h 127.0.0.1 -T 20 -C -c 10 -n -U postgres postgres
Password:
pgbench (14.12 (Homebrew), server 16.3 (Debian 16.3-1.pgdg120+1))
transaction type: <builtin: TPC-B (sort of)>
scaling factor: 1
query mode: simple
number of clients: 10
number of threads: 1
duration: 20 s
number of transactions actually processed: 12495
latency average = 16.013 ms
average connection time = 1.041 ms
tps = 624.476417 (including reconnection times)

100 clients via pgcat

pgbench -p 6432 -h 127.0.0.1 -T 20 -C -c 100 -n -U postgres postgres
Password:
pgbench (14.12 (Homebrew), server 16.3 (Debian 16.3-1.pgdg120+1))
transaction type: <builtin: TPC-B (sort of)>
scaling factor: 1
query mode: simple
number of clients: 100
number of threads: 1
duration: 20 s
number of transactions actually processed: 11600
latency average = 173.055 ms
average connection time = 1.113 ms
tps = 577.849533 (including reconnection times)

Ryzen 5900x WSL

baseline 10 clients to postgres

pgbench -p 5432 -h localhost -T 20 -C -c 10 -n -U postgres postgres
Password:
pgbench (14.12 (Ubuntu 14.12-0ubuntu0.22.04.1), server 16.3 (Debian 16.3-1.pgdg120+1))
transaction type: <builtin: TPC-B (sort of)>
scaling factor: 1
query mode: simple
number of clients: 10
number of threads: 1
duration: 20 s
number of transactions actually processed: 2666
latency average = 75.092 ms
average connection time = 6.238 ms
tps = 133.169288 (including reconnection times)

10 clients via pgcat

pgbench -p 6432 -h localhost -T 20 -C -c 10 -n -U postgres postgres
Password:
pgbench (14.12 (Ubuntu 14.12-0ubuntu0.22.04.1), server 16.3 (Debian 16.3-1.pgdg120+1))
transaction type: <builtin: TPC-B (sort of)>
scaling factor: 1
query mode: simple
number of clients: 10
number of threads: 1
duration: 20 s
number of transactions actually processed: 396
latency average = 506.607 ms
average connection time = 49.169 ms
tps = 19.739169 (including reconnection times)

100 clients via pgcat

pgbench -p 6432 -h localhost -T 20 -C -c 100 -n -U postgres postgres
Password:
pgbench (14.12 (Ubuntu 14.12-0ubuntu0.22.04.1), server 16.3 (Debian 16.3-1.pgdg120+1))
transaction type: <builtin: TPC-B (sort of)>
scaling factor: 1
query mode: simple
number of clients: 100
number of threads: 1
duration: 20 s
number of transactions actually processed: 396
latency average = 5093.598 ms
average connection time = 49.277 ms
tps = 19.632487 (including reconnection times)