75

Django 1.6 now supports CONN_MAX_AGE to pool database connections.

By default, the value is 0 (no pooling). What is a sensible value for this option?

Andre Miras
  • 3,580
  • 44
  • 47
Brendan Nee
  • 5,087
  • 2
  • 33
  • 32
  • 2
    0 makes sense.. Why would you want to keep a connection open after the request is complete unless you explicitly need it ? – karthikr Nov 12 '13 at 18:44
  • 45
    @karthikr Creating a connection is an expensive operation. This allows you to avoid creating a connection on every single request. – aehlke Nov 18 '13 at 07:40
  • 5
    @karthikr because establishing a connection is expensive and you don't want to re-establish connections at every query. Very inefficient. – Saher Ahwal Nov 17 '16 at 09:15

2 Answers2

35

Not as simple as "the more traffic the more seconds to retain the connection".

It also depend on how you run Django.

Now, one popular way to launch Django in gunicorn+greenlet(evenlet or gevent). And if you set CONN_MAX_AGE to 60 (even 5 in my case), you may get complain 'too many connections' from DB server.

See this for details.

https://github.com/benoitc/gunicorn/issues/996

https://serverfault.com/questions/635100/django-conn-max-age-persists-connections-but-doesnt-reuse-them-with-postgresq

Community
  • 1
  • 1
Jcyrss
  • 1,513
  • 3
  • 19
  • 31
  • We decided to go through a pgpool-II Server to connect to the postgres server, using CONN_MAX_AGE != 0 then leads to painful timeouts very quickly if you have enough worker/threads. – kelvan Apr 10 '20 at 13:40
  • I had the same problem, but I switched to gunicorn --worker-class uvicorn.workers.UvicornWorker and it manages connections properly – Alexander Mar 16 '22 at 08:40
34

This value depends on the traffic of your site, the more traffic the more seconds to retain the connection, I'd recommend setting a relatively small value like 60 and tuning it accordingly to the usage pattern.



Edit (2018):

Like @jcyrss pointed out, this method has its quirks, for future reference I'd recommend handing out pooling to something like pgbouncer instead.

MGP
  • 2,981
  • 35
  • 34
  • Are you suggesting that with pgbouncer enabled, one should not set CONN_MAX_AGE in settings at all? – Hassan Baig Aug 30 '20 at 15:05
  • @HassanBaig sure, once you have pgbouncer in place there's no gain in pooling from Django as well. – MGP Aug 30 '20 at 19:39
  • 1
    For [Django 3.2](https://docs.djangoproject.com/en/3.2/ref/databases/#transaction-pooling-and-server-side-cursors), Using a connection pooler in transaction pooling mode (e.g. PgBouncer) requires disabling server-side cursors for that connection. I don't think that it's easy to use pgbouncer if all iterators code have to be changed – Benny Chan Oct 26 '21 at 03:15