Pgbouncer on large client connections

Question

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

What are those 2,000 clients? Are they trying to hit the databases directly or they connect to a web application or some other service that queries the db server with its own user? That makes all the difference. — Clodoaldo Neto, Aug 05 '13 at 16:20
They are are connection from a gps listener, this listener receives data on 10 seconds intervals from gps trackers, and then the data is stored on the postgres database — Carlos, Aug 05 '13 at 16:27
If the listener is the only one connecting to pgbouncer then you have one only client which can make _n_ simultaneous connections. How many depends on the duration of each connection. Is the listener a PC? Is it on the same machine as the DB? Where is pgbouncer listening? On the listener or on the DB server? — Clodoaldo Neto, Aug 05 '13 at 16:47
Hmmm the listener is a twisted app, wich one manage multiple protocols and it creates a connection for each gps tracker device, I have found the solution on it, however thanks — Carlos, Aug 05 '13 at 17:00

score 13 · Accepted Answer · answered Aug 16 '13 at 09:59

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.

Pgbouncer on large client connections

1 Answers1