how to determine max_client_conn for pgbouncer

user_1644885 0
Viewed 19042

I'm sort of an "accidental dba" so apologies for a real noob question here. I'm using pgbouncer in pool_mode = transaction mode. Yesterday I started getting errors in my php log:

no more connections allowed (max_client_conn)

I had max_client_conn = 150 to match max_connections in my postgresql.conf.

So my first question is, should pgbouncer max_client_conn be set equal to postgresql max_connections, or am I totally misunderstanding that relationship?

I have 20 databases on a single postgres instance behind pgbouncer with the default default_pool_size = 20. So should max_client_conn be 400? (pool_size * number_of_databases)?

Thanks

pgbouncer postgresql
2 Answers

Like almost everyone, then you are setting your pool size way to high. Don't let your postgresql server do the connection pooling. If you do then it severely hurts your performance.

The optimal setting for how many concurrent connection to postgresql is 

connections = ((core_count * 2) + effective_spindle_count)

That means that if you are running your database on a 2 core server, then your total pool size from pgbouncer should be no more than 5. Pgbouncer is a lot better at handling pooling than postgresql, so let it do that.

So, leave the max_connections in my postgresql.conf to it's default 100 (no reason to change as it is a max. Also this should always be higher than what your application needs as some logging, admin and backup processes needs connections as well)

And in your pgbouncer.ini file set

max_db_connections=5
default_pool_size=5
max_client_conn=400

For more information https://www.percona.com/blog/2018/06/27/scaling-postgresql-with-pgbouncer-you-may-need-a-connection-pooler-sooner-than-you-expect/

user_6321820
Related