how to determine max_client_conn for pgbouncer

Question

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

Answer 1

https://pgbouncer.github.io/config.html

max_client_conn Maximum number of client connections allowed.

default_pool_size How many server connections to allow per user/database pair.

so max_client_conn should be way larger then postgres max_connections, otherwise why you use connection pooler at all?..

If you have 20 databases and set default_pool_size to 20, you will allow pgbouncer to open 400 connections to db, so you need to adjust posgtres.conf max_connections to 400 and set pgbouncer max_client_conn to smth like 4000 (to have average 10 connections in pool for each actual db connection)

This answer is only meant to provide an example for understanding the settings, not as a statement to literally follow. (eg I just saw a config with:

max_client_conn = 10000
default_pool_size = 100
max_db_connections = 100
max_user_connections = 100

for cluster with two databases and max_connections set to 100). Here the logic is different, also mind max_db_connections is set and in fact connection limits are set individually per database in pgbouncer [database] section.

So - play with small settings to get the idea of how config influence each other - this is "how to determine max_client_conn for pgbouncer" the best

Answer 2

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/