1

We've been using pgbouncer for couple days now and found out that the bouncer is super slow. I'd like to have your feedback on what is wrong with my bouncer configuration:

Pressures table is 600k rows

The query I use to test:

SELECT * FROM Pressures LIMIT 1000;

I run this query 50 times

  • Targeting the db directly each query simple takes 10s to return

  • Targeting the bouncer the first queries are 10s each then it goes up like crazy and each query takes 1min to return

The postgresDB and the bouncer run on the same EC2 T3.xlarge instance (4vCPU Intel Xeon Platinum 8000, 16gb RAM) When i ssh the instance the load is <2%

My Pgbouncer configuration :

Pool_mode : transaction
max_db_connections=20
default_pool_size=20
max_client_conn=800

I tried also tried

Pool_mode : session
max_db_connections=20
default_pool_size=20
max_client_conn=800

And

Pool_mode : transaction
max_db_connections=5
default_pool_size=5
max_client_conn=600

Exact same issue. What am i missing?

Thanks!

Louis Creteur
  • 70
  • 8
  • Hi I wonder if this blog post provides any insight https://www.percona.com/blog/2018/06/27/scaling-postgresql-with-pgbouncer-you-may-need-a-connection-pooler-sooner-than-you-expect/ >The use of a connection pooler, in this case, was extremely detrimental to performance. At such a small scale there were no gains obtained from a pool of connections, only overhead. – greenweeds Aug 17 '21 at 10:07
  • Are you running the query 50 times sequentially or simultaneously? – Jeremy Aug 17 '21 at 10:17
  • @Jeremy simultaneously – Louis Creteur Aug 17 '21 at 11:20
  • T class machines do not give consistent performance, by design. How many times did you repeat this experiment? Was it always in the same order of direct then through bouncer? How much recovery time between experiments? – jjanes Aug 17 '21 at 14:53
  • @jjanes we've been running test for more than 2 weeks. +- 100 queries simultaneously all the time and the problem is always there. No recovery time but i guess that its more binary (either it works as it should or it is not and very slow?) – Louis Creteur Aug 17 '21 at 19:16
  • Having the same queries running at the same time, some going through pgbouncer and some going direct, doesn't seem like a very sensible way of running pgbouncer, nor of testing it. If pgbouncer's pool is limited to 20 connections at a time and the direct connections get the other 80, then of course the pool is going to be much slower. But it isn't clear what your test is even trying to determine. – jjanes Aug 17 '21 at 21:53
  • Can you show the load script? You create 50 processes/threads and run in each one of them: open connection, query, close connection? – Alex Yu Aug 22 '21 at 23:32

0 Answers0