Gerzzog
Gerzzog

Reputation: 199

pgbench benchmark rises using pgbouncer

I've got Postgres 13 and PGbouncer. When I make pgbench test, when I rise client connection and quantity of query, tps also rises. Why? For example:

pgbench -U postgres -h .... -p 6544 datafactory -c 700 -j 8 -t 50 -S
latency average = 1591.633 ms
tps = 439.799988 (including connections establishing)
tps = 442.163375 (excluding connections establishing)

pgbench -U postgres -h ... -p 6544 datafactory -c 700 -j 8 -t 100 -S
latency average = 1286.131 ms
tps = 544.268178 (including connections establishing)
tps = 545.953341 (excluding connections establishing)

pgbench -U postgres -h ... -p 6544 datafactory -c 700 -j 8 -t 300 -S
latency average = 1246.031 ms
tps = 561.783731 (including connections establishing)
tps = 562.399700 (excluding connections establishing)

Finally, if I lower amount of query, I get low level of tps:

pgbench -U postgres -h .. -p 6544 datafactory -c 700 -j 8 -t 10 -S
latency average = 8633.526 ms
tps = 81.079273 (including connections establishing)
tps = 81.465337 (excluding connections establishing)

So, I've got a question - why does the tps rise? I thought that if load increase, then tps should reduce. If I connect to database directly on port 5433, not using PGbouncer, then if load increases, benchmark reduces

Additional information: All 3 (pgbench, pgbouncer, db) are running on one machine. When directly to port 5433, pgbouncer doesn't hold connection.

Here are examples of direct connection to database on port 5433:

pgbench -U postgres -h ... -p 5433 datafactory -c 250 -j 8 -t 500
latency average = 2155.394 ms
tps = 115.988055 (including connections establishing)
tps = 116.134037 (excluding connections establishing)

pgbench -U postgres -h ... -p 5433 datafactory -c 250 -j 8 -t 700
latency average = 835.555 ms
tps = 299.202467 (including connections establishing)
tps = 299.228977 (excluding connections establishing)

Here it fails

pgbench -U postgres -h ... -p 5433 datafactory -c 250 -j 8 -t 1000
WARNING:  terminating connection because of crash of another server process

pgbench -U postgres -h ... -p 5433 datafactory -c 250 -j 8 -t 700
connection to database "datafactory" failed:
FATAL:  the database system is in recovery mode

PGBOUNCER:

 listen_port = 6544
 listen_addr = '*'
 auth_type = scram-sha-256
 auth_file = /etc/pgbouncer/userlist.txt
 auth_proxy = on
 auth_failure_threshold = 3
 auth_inactivity_period = 60
 auth_last_size = 10
 log_audit = 1
 logfile = /pgerrorlogs/tkldd-ldisu0001/pgbouncer.log
 pidfile = /var/run/pgbouncer/pgbouncer.pid
 admin_users = pgbouncer
 max_client_conn = 1000
 pool_mode = transaction
 min_pool_size = 0
 default_pool_size = 30
 max_db_connections = 30
 max_user_connections = 30
 ignore_startup_parameters = extra_float_digits

Upvotes: 1

Views: 843

Answers (0)

Related Questions