Help: High Average Connection Latency

[dineshtessell]

Hi,

I conducted a pgbench test case with no connection pooler, pgBouncer, and pgCat. Below is the custom SQL script I used to run the pgbench test cases with PostgreSQL 14.5 version instance:

BEGIN WORK;
INSERT INTO test VALUES(1);
UPDATE test SET t=-1 WHERE t=1;
SELECT COUNT(*) FROM test;
END WORK;

BEGIN WORK;
SELECT COUNT(*) FROM test;
SELECT COUNT(*) FROM test;
SELECT COUNT(*) FROM test;
END WORK;

No Connection Pool

./pgbench -h 10.10.60.41 -f /tmp/test.sql -U master -d postgres -c 65 -T 100 -C
query mode: simple
number of clients: 90
number of threads: 1
duration: 100 s
number of transactions actually processed: 3215
latency average = 2824.063 ms
average connection time = 27.935 ms
tps = 31.868972 (including reconnection times)

pgBouncer

./pgbench -h localhost -f /tmp/test.sql -U master -d postgres -c 65 -T 100 -C -p 6432
transaction type: /tmp/test.sql
scaling factor: 1
query mode: simple
number of clients: 65
number of threads: 1
duration: 100 s
number of transactions actually processed: 3562
latency average = 1840.114 ms
average connection time = 9.950 ms
tps = 35.323903 (including reconnection times)

pgCat

./pgbench -h localhost -f /tmp/test.sql -U master -d postgres -c 65 -T 100 -C
transaction type: /tmp/test.sql
scaling factor: 1
query mode: simple
number of clients: 65
number of threads: 1
duration: 100 s
number of transactions actually processed: 1450
latency average = 4502.338 ms
average connection time = 41.256 ms
tps = 14.436943 (including reconnection times)

From the above tests, it is clear that pgCat has a higher connection latency average than pgBouncer. I also observed that the pgCat query execution behavior is more like a batch process than a seamless execution.

Please find the below pgCat configuration and do let me know if I miss anything here.

[general]
prometheus_exporter_port = 9200
admin_username = "admin_user"
admin_password = "admin_pass"
[pools.postgres]
default_role = "primary"
query_parser_enabled = false
primary_reads_enabled = false
sharding_function="pg_bigint_hash"
[pools.postgres.users.0]
username = "master"
password = "Tessell123"
pool_size = 70
statement_timeout = 0
[pools.postgres.shards.0]
servers = [
    [ "10.10.60.41", 5432, "primary" ],
]
database = "postgres"

[levkk]

Did you build PgCat in release mode? cargo build --release

[dineshtessell]

Yes @levkk ,

Used same command which is mentioned in the document cargo build --release