16

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)

dland
  • 4,319
  • 6
  • 36
  • 60
jayanth88
  • 564
  • 1
  • 6
  • 14
  • Before changing anything I would check cpu usage of pgbouncer. 10000 client connections is a lot and it could be too much for a single threaded application. 500 active connections on the PostgreSQL server is also a lot, you do need some serious hardware for high performance. – Frank Heikens Dec 11 '14 at 08:43
  • The cpu usage for this box is around 30% when executing the test. The box has 16 cores & 24gb RAM. I notice that the disk utilization is also around 40%. While using pgbouncer I notice a lot of 'idle' transactions & I believe thats causing the low tps , but am not sure how to avoid them. – jayanth88 Dec 11 '14 at 20:31
  • Idle when you have 10000 clients that should be busy, that's not good. What tests are you running? Are all transactions closed when you're done with one test? If not, that might cause the delay because the connection isn't available yet for another client. Both cpu and disk should go up to 100%, or at least getting close to 100%. – Frank Heikens Dec 11 '14 at 20:47
  • First of all try to check out pgbouncer itself. It has a lot of statistics inside (SHOW command). You can do it manually by connecting to its virtual database 'pgbouncer' (psql -U user -p bouncer_port pgbouncer). But it is always better to have some monitoring frameworks with pgbouncer integration. As previously said 500 connections is a lot especially in transaction mode. The more connections you have the slower tps will be (IPC, mem share, etc, https://wiki.postgresql.org/wiki/Number_Of_Database_Connections). I doubt you need real 500 pg backends on16 cores. – Zorg Jul 17 '15 at 21:16
  • Why are you using transaction pooling vs session pooling? – Joe Love Sep 06 '18 at 21:24
  • 1
    How is phbouncer connected to your DB? Is pgb on one machine and the DB on another? Is it using tcp/ip? How is the connection different from your client->db direct connection? Are you using docker a well and having to go through additional IP stacks/abstraction layers in 1 config vs another? – Joe Love Sep 06 '18 at 21:32

2 Answers2

5

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.

filiprem
  • 6,721
  • 1
  • 29
  • 42
  • Could't you use a different scheduler? I know I play with schedulers a lot on android phones, but haven't switched schedulers for something like this before. Different schedulers definitely make a huge performance difference – Joe Love Aug 26 '21 at 06:24
3

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.

JosMac
  • 2,164
  • 1
  • 17
  • 23