I am using pgbouncer in transaction mode & trying to allow close to 500 active transaction. The purpose is simply to stress test the setup
Current setup: [ 'n' clients --->1 pgbouncer ----> 1 postgres ]
I notice that my transaction/second (tps) decreases considerably when I use pgbouncer instead of a direct connection to postgres.
For the same set to transaction (via pgbench)
Direct connections => 10k (tps) appx
pgbouncer connection => 3k (tps) appx
Is there any configuration in pgbouncer that needs to be tuned to allow a better performance?
I understand pgbouncer is a single threaded application, but would like to tune it till its best. Following is my pgbouncer configuration:
pgbouncer.ini
pool_mode = transaction
server_reset_query =
# Time outs
server_lifetime=6000
server_idle_timeout=0
server_connect_timeout=30
#pool configuration
max_client_conn=10000
default_pool_size=500
pool_size=500
##other
pkt_buf=4096
server_login_retry=2
The only application I can see is to use multiple pgbouncers to point to the same db server.
UPDATE
while executing the test:
cpu utilization : 30% appx
disk utilization : 40% appx
Observation: many transactions in 'idle' state
TEST DETAILS:
10 machine acting as clients running pgbench firing request to the DB server.
command: pgbench -h -p 6541 -c 512 -j 16 -f pgbench_SchemaScript.sql -T 360 -U postgres test
pgbench_SchemaScript.sql
\setrandom delta 0 100000
insert into t1.emplog values(nextval('t1.employeeSeq'),:delta);
1 DB server with pgbouncer installed (16core , 24 Gb RAM)
I know this is an old question but we had similar problem and we simply run more pgbouncers in Docker on different ports against the same database and it works OK. This way you can have different queues from different apps on separate instances of pgbouncer.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With