I've recently run into something I am having difficulty understanding.
I am running pgbench with the following
pgbench -h localhost -c 100 -j 100 -t 2 -S -s 1000 pgbench -U pgbench --protocol=simple
Without pgbouncer I get around 5k TPS
with pgbouncer I get around 15k TPS
Looking at the code connection initiation time should not be part of the calculation so I' puzzled why pgbouncer is making such a dramatic difference ?
Dave
Turns out that for this specific test, pg is faster with a pooler.
Dave Cramer, [May 16, 2023 at 9:49:24 AM]:
turns out having a connection pool helps. First run is without a pool, second with
pgbench=# select mean_exec_time, stddev_exec_time, calls, total_exec_time, min_exec_time, max_exec_time from pg_stat_statements where queryid=-531095336438083412;
mean_exec_time | stddev_exec_time | calls | total_exec_time | min_exec_time | max_exec_time
--------------------+--------------------+-------+-------------------+----------------------+---------------
0.4672699999999998 | 2.2758508661446535 | 200 | 93.45399999999997 | 0.046616000000000005 | 17.434766
(1 row)
pgbench=# select pg_stat_statements_reset();
pg_stat_statements_reset
--------------------------
(1 row)
pgbench=# select mean_exec_time, stddev_exec_time, calls, total_exec_time, min_exec_time, max_exec_time from pg_stat_statements where queryid=-531095336438083412;
mean_exec_time | stddev_exec_time | calls | total_exec_time | min_exec_time | max_exec_time
---------------------+----------------------+-------+--------------------+---------------+---------------
0.06640186499999999 | 0.021800404695481574 | 200 | 13.280373000000004 | 0.034006 | 0.226696
(1 row)