27

I'm running a multi-tennant website, where I would like to reduce the overhead of creating a PostgreSQL connection per request. Django's CONN_MAX_AGE allows this, at the expense of creating a lot of open idle connections to PostgreSQL (8 workers * 20 threads = 160 connections). With 10MB per connection, this consumes a lot of memory.

The main purpose is reducing connection-time overhead. Hence my questions:

Django 1.6 settings:

DATABASES['default'] = {
    'ENGINE':   'django.db.backends.postgresql_psycopg2',

     ....

    'PORT': '6432'
    'OPTIONS': {'autocommit': True,},
    'CONN_MAX_AGE': 300,
}

ATOMIC_REQUESTS = False   # default

Postgres:

max_connections = 100

PgBouncer:

pool_mode = session     # Can this be transaction?
max_client_conn = 400   # Should this match postgres max_connections?
default_pool_size = 20
reserve_pool_size = 5
vdboor
  • 21,914
  • 12
  • 83
  • 96
  • Can you please update what did you do finally ? – Anurag Sep 18 '16 at 12:46
  • `max_client_conn` - no it shouldn't match the postgres settings. `pgbouncer` should accept that many connections, which will wait in the pool to be processed, when the pgbouncer real database connections are freed/unused – smido Nov 02 '18 at 13:24

1 Answers1

19

Here's a setup I've used.

pgbouncer running on same machine as gunicorn, celery, etc.

pgbouncer.ini:

[databases]
<dbname> = host=<dbhost> port=<dbport> dbname=<dbname>

[pgbouncer]
: your app will need filesystem permissions to this unix socket
unix_socket_dir = /var/run/postgresql
; you'll need to configure this file with username/password pairs you plan on
; connecting with.
auth_file = /etc/pgbouncer/userlist.txt

; "session" resulted in atrocious performance for us. I think
; "statement" prevents transactions from working.
pool_mode = transaction

; you'll probably want to change default_pool_size. take the max number of
; connections for your postgresql server, and divide that by the number of
; pgbouncer instances that will be conecting to it, then subtract a few
; connections so you can still connect to PG as an admin if something goes wrong.
; you may then need to adjust min_pool_size and reserve_pool_size accordingly.
default_pool_size = 50
min_pool_size = 10
reserve_pool_size = 10
reserve_pool_timeout = 2
; I was using gunicorn + eventlet, which is why this is so high. It
; needs to be high enough to accommodate all the persistent connections we're
; going to allow from Django & other apps.
max_client_conn = 1000
...

/etc/pgbouncer/userlist.txt:

"<dbuser>" "<dbpassword>"

Django settings.py:

...
DATABASES = {
    'default': {
        'ENGINE': 'django.contrib.gis.db.backends.postgresql_psycopg2',
        'NAME': '<dbname>',
        'USER': '<dbuser>',
        'PASSWORD': '<dbpassword>',
        'HOST': '/var/run/postgresql',
        'PORT': '',
        'CONN_MAX_AGE': None,  # Set to None for persistent connections
    }
}
...

If I remember correctly, you can basically have any number of "persistent" connections to pgbouncer, since pgbouncer releases server connections back to the pool when Django is done with them (as long as you're using transaction or statement for pool_mode). When Django tries to reuse its persistent connection, pgbouncer takes care of waiting for a usable connection to Postgres.

Seán Hayes
  • 4,060
  • 4
  • 33
  • 48
  • 1
    I also found this explanation which was pretty helpful: http://comments.gmane.org/gmane.comp.db.postgresql.pgbouncer.general/979 – Seán Hayes Feb 18 '16 at 05:26
  • 1
    The hardest part about using pgbouncer is figuring out which settings are for PG and which are for pgbouncer. They really ought to be prefixed or something. – Seán Hayes Feb 18 '16 at 05:29
  • What about the second question? > Can I use 'transaction' pool mode with Django? – emcpow2 Feb 18 '16 at 06:48
  • Yes, it's used in my example config. `pool_mode = transaction` – Seán Hayes Feb 18 '16 at 19:57
  • I wasn't able to run "HOST" as "/var/run/postgresql" - I set it to the port pgbouncer was listening on (localhost, port 6432). Is this a typo in your response @SeánHayes or am I misunderstanding something? TY for the response - very helpful. – FinDev Jul 24 '16 at 18:24
  • In pgbouncer.ini I have `unix_socket_dir = /var/run/postgresql`, so in this setup PG Bouncer is using a unix socket. – Seán Hayes Jul 25 '16 at 17:37
  • @SeánHayes: how would you tweak your settings.py configuration if pgbouncer was installed on the application server and the db server was separate? – Hassan Baig Oct 25 '16 at 22:13
  • You wouldn't be able to connect to Postgres using a unix socket, you'd need to use IP:port, so that would need to be specified in pgbouncer.ini under `[databases]`. Also look at my comment about changing `default_pool_size`. You can still connect to pgbouncer using a unix socket as long as it's on the same machine, however if pgbouncer is running on a different machine you'll need to use IP:port in settings.py. – Seán Hayes Oct 25 '16 at 23:37
  • Under `[databases]`, I have `db1 = host=xx.xxx.xxx.xxx port=5432 dbname=db1`, i.e. host IP and port are both specified like you said. In settings.py, I'm currently running with this: `DATABASE_URL = 'postgres://user1:pass1@xx.xxx.xxx.xxx:5432/db1'`. What's your opinion on configuration like this? – Hassan Baig Oct 25 '16 at 23:55
  • You need your Django settings to point to wherever pgbouncer is. The settings you describe will simply point to the same PG instance as pgbouncer, thereby bypassing it. Also, `DATABASE_URL` isn't a setting supported by Django AFAIK, I assume you're using some code to translate that into a standard `DATABASES` `dict`? – Seán Hayes Oct 26 '16 at 02:18
  • Yes indeed, I'm not using `DATABASE_URL` as a setting. I actually set up my problem as a separate question here: http://stackoverflow.com/questions/40248970/django-settings-when-using-pgbouncer Could you give it a quick skim? – Hassan Baig Oct 26 '16 at 07:54