Fine Tuning pgbouncer - High number of sessions

[V0lantis]

Hello here 👋

I am implementing a production stack where I have pgbouncer as a connections poolers in front of a AWS RDS postgres database, hosted in RDS AWS.
I have tried to tune my pgbouncer, but I am little bit walking into the dark. I somehow cannot find any tutorial to help me understand how to get the most out of pgbouncer with AWS RDS postgres.

Nonetheless, I noticed that in the beginning of my research that my pool size was too low which was creating a huge bottleneck for my apps. So I increased it dramatically (but maybe too much?).

here are my different configurations:

db sizing :

1. vCPU: 2
2. RAM: 8 GB
3. instance class: db.t3.large

#  pgbouncer.ini 

[databases]
db = host=my-host dbname=mydb port=5432 pool_size=550

[pgbouncer]
pool_mode = transaction
listen_port = 6543
listen_addr = *
auth_type = md5
auth_file = /etc/pgbouncer/users.txt
stats_users = user
ignore_startup_parameters = extra_float_digits
max_client_conn = 500
verbose = 0
log_disconnections = 0
log_connections = 0

server_tls_sslmode = prefer
server_tls_ciphers = normal

Basically, I just changed the following paramets:

1. max_client_conn: 500
2. pool_size=550

The CPU performance insight seems to be ok, but I have a huge numbers of sessions (cannot post any screenshot at the moment due to a GitHub bug apparently):

1. CPU avg utilization: ~35%
2. DB numbers of connections ~75, but cloud watch metrics says the limit is 600
3. ~ 25 open sessions, but AWS RDS advises to have numbers_of_sessions < vCPU, then < 2. (Link to the doc)

CPU bottleneck
In the preceding examples, there are two vCPU cores, so only two sessions can concurrently run on the CPU without queueing. If three sessions run on the CPU concurrently, then, at any given point in time, at least one of them is waiting in the run queue and therefore not getting work done.

[petere]

I think one problem is that with max_client_conn 500 and pool_size 550 you are defeating one of the purposes of PgBouncer a bit. That purpose is roughly speaking to allow more connections from client->pgbouncer and convert those to fewer connections pgbouncer->server. However, in your configuration, you are at best passing all connections through and not gaining anything.

The exact configuration of course depends very much on local specifics, but consider something like max_client_conn 1000 and pool_size 100 to get a real effect out of pgbouncer.

The discussion about number of CPUs versus number of sessions is for PostgreSQL proper but does not apply to PgBouncer in the same way. PgBouncer can run thousands of connections on a single CPU.

[V0lantis]

would you have any resources you would advise me to tune the different pgbouncer parameters to get the most out of it?

[V0lantis]

Somehow, I found the doc little bit dry