How to increase the connection throughput for pgbo

问题:

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)

回答1:

Solution (partial): Increase CPU priority of pgbouncer process with renice.

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

回答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.