How to increase the connection throughput for pgbouncer?

Question

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)

Answer 1

If you are running PgBouncer on the same host as PostgreSQL, you should increase CPU priority of pgbouncer process with renice.

renice -10 -p `pidof pgbouncer`

Default Linux scheduler is round-robin and it starves PgBouncer because it treats all processes equally - and hundreds of postgres processes overwhelm single pgbouncer process.

It's usually not a problem under moderate load, but it can be a really important factor when running lots of small transactions.

Please be aware that PgBouncer is a single-threaded program. If you run more traffic than a single CPU core can handle (on y2020 hardware it's something between 10k-30k TPS), you should consider some load balancing (HAProxy?) in front of (multiple) PgBouncer instances.

Answer 2

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.