Pgbouncer on large client connections

Carlos picture Carlos · Aug 5, 2013 · Viewed 7.7k times · Source

I need to configure my pgbouncer for work with more than 2000 clients connections, I was reading some information about how to work with max connections, then I have understood what I must to do max_client_con = 2000 on pgbouncer, but what about default_pool_size, them, more than a question is to ask for some support in order to understand the right way on configure pgbouncer on large client connections

postgres.conf

port = 5432                             # (change requires restart)
max_connections = 100                   # (change requires restart)
unix_socket_directory = '/var/run/postgresql'           # (change requires restart)

pgbouncer.ini

[pgbouncer]
logfile = /var/log/postgresql/pgbouncer.log
pidfile = /var/run/postgresql/pgbouncer.pid
listen_addr = 127.0.0.1
listen_port = 6432
unix_socket_dir = /var/run/postgresql
auth_type = md5
auth_file = /etc/pgbouncer/userlist.txt
admin_users = carlos
pool_mode = statement
server_reset_query = DISCARD ALL
max_client_conn = 100
default_pool_size = 20

Answer

Magnus Hagander picture Magnus Hagander · Aug 16, 2013

If you can run pgbouncer in "transaction pooling mode", you're best off. There are a few features that if use will not work with that, but if you don't use them for that.

default_pool_size then controls how many connections you have between pgbouncer and postgresql, and leaving that at something like 20 is probably a good idea. That means you can have 20 simultaneously active transactions in the system, and from the description of what you're doing that seems unlikely. But you want to adjust that value to be a value with a "reasonable margin" over the maximum number of simultaneous transactions you need. pgbouncer will then happily multiplex your 2000 connections over these 20, maintaining good performance on the database side.