Reputation: 199
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