Home how to determine max_client_conn for pgbouncer

# how to determine max_client_conn for pgbouncer

Richard Greenwood
1#
Richard Greenwood Published in 2017-09-13 13:27:06Z
 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
Vao Tsun
2#
Vao Tsun Reply to 2017-09-13 14:41:37Z
 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
 You need to login account before you can post.
Processed in 0.343677 second(s) , Gzip On .