0

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
Zegarek
  • 6,424
  • 1
  • 13
  • 24
Gerzzog
  • 95
  • 7
  • The `-t` is not really for higher load but rather for the test duration: with `-t` you're telling each of the 700 clients to run the test a given number of times before they stop to calculate the stats, with `-T` it would just run as many as possible until time runs out. One will see how long it took to run the 10 loops and divide the 10 by that time, the other will divide the amount of loops it managed to run by the time limit. `-t` and `-T` would be considered for *sustaining* load resulting from `-c`, `-j` and the test script complexity. – Zegarek Feb 09 '23 at 11:06
  • Can you share any of your pgbouncer settings? Are these 3 (pgbench, pgbouncer, db) on different machines, networks, or do they share resources? Does PGBouncer hold the connections when you switch pgbench to direct-to-db? Can you share stats from the direct test? – Zegarek Feb 09 '23 at 11:10
  • What is auth_proxy? – jjanes Feb 09 '23 at 15:49
  • don't know. its devops made settings))) – Gerzzog Feb 09 '23 at 16:05
  • Well, you need to find out. It seems like you are not even use pgbouncer, maybe some fork of it. – jjanes Feb 09 '23 at 16:13
  • This whole thing just doesn't make sense. Who cares how it performs for -t 10? that is like measuring some ones sprint speed in the 100 millimeter dash. And the combination of -c700 -j8 seems equally pointless. – jjanes Feb 09 '23 at 16:22
  • 1
    According to these settings, pgbouncer uses only 30 connections per db (`max_db_connections`), 30 per user (`max_user_connections`), 30 per db+user pair (`default_pool_size`). Unless your tests ran on different users and databases, the `max_client_conn=1000` should be unreachable. Would you mind attaching the direct tests as well, the ones where you connected pgbench directly to port 5433? – Zegarek Feb 09 '23 at 17:35
  • Added information about direct connection. And yes, our programmers made some injections in pgbouncer and now it works only like poller. There is no authentification block in it. That's why we can connect through pgbouncer even using kerberos – Gerzzog Feb 10 '23 at 06:20
  • @jjanes could you tell me optimal pgbench settings fo testing direct connection and via pgbouncer? I mean -c 250 or more? how much -t ? – Gerzzog Feb 10 '23 at 06:36
  • 1
    There are generally no optimal settings here. pgbench and pgbouncer both need to be configured to match what your application is actually doing. I generally use -T, not -t, and regard -T 60 as the minimum sensible value to use. I might go lower than that if I already have experience and now the timings are very reliable. – jjanes Feb 10 '23 at 16:51
  • And I would say the optimal settings for pgbouncer is "not to use it". pgbouncer is a last resort tool, when your app is poorly written/designed and you can't fix it. – jjanes Feb 10 '23 at 16:53
  • You should look in the database server's log file to see why it is failing. It is likely that is related to the way-too-many connections, which is perhaps what pgbouncer is intended to ameliorate, but it isn't a straightforward connection or else it would happen every time. Probably memory exhaustion. – jjanes Feb 10 '23 at 17:02
  • Zegarek, pgbouncer can accept 1000 incoming client connections and make them wait for their turn on one of the 30 real db connections. So it might be sensible to have max_client_conn be higher than default_pool_size. – jjanes Feb 10 '23 at 17:44
  • @jjanes Sure, that's pretty much what I'm pointing out to the OP. Part of the reason these tests differ when they go straight for the db compared to going through the pool is that the pgbouncer doesn't let through more than 30 transactions at a time. – Zegarek Feb 10 '23 at 17:58
  • @jjanes. Do you know, or maybe someone can help, how pgbench manage with -T parameter? Cause I can launch several times with the same number of connection and tps are twice differ. Is parameter -t more accurate? – Gerzzog Feb 28 '23 at 12:02

0 Answers0