Postgresql – Django: CONN_MAX_AGE persists connections, but doesn’t reuse them with PostgreSQL

djangopostgresqlpostgresql-9.3

I've got a django setup are using Django 1.6.7 and Postgres 9.3 on Ubuntu 14.04 LTS.

At any given time, the site gets about ~250 simultaneous connections to the PostgreSQL database, which is a Quad Core Xeon E5-2670 at 2.5GHz, and has 16GB of ram. The load average on that particular machine throughout the day is around 20 to 30.

Occasionally I will get emails in sentry about connections timing out to the database, and I figure enabling some sort of connection pooling will help mitigate this issue, as well as lower the load on the database a bit.

Since we are using Django 1.6, we do have the built in pooling available to us. However, when I set CONN_MAX_AGE to 10 seconds, or 60 seconds, almost immediately the number of simultaneous connections jumps to the maximum allowed setting (which is about double what we usually see), and connections start getting rejected.

So, it appears for what ever reason, the connections ARE persisting, but they ARE NOT being reused.

What could be the cause of this?

PS. We are also using gunicorn with –worker-class=eventlet. Perhaps this is the source of our woes?

Best Answer

Doing some more experimenting, I have found that the cause of our problem was indeed gunicorn's eventlet worker class. Each microthread made it's own persistent connection, and there was no way at all to reuse any of them.

Disabling eventlet has made the load on our webservers go up (but not by much), but the postgres load is now down to an average of 3. From 30.

Related Solutions

Postgresql – Postgres suddenly slower and higher CPU usage

Turn on full logging for a short period (~10 minutes) if you can. Save the log, and have pgfouine (http://pgfouine.projects.postgresql.org/) analyse it. It's possible you're doing more queries than before, or you're doing a query which isn't very efficient.

Next it's possible you have some slow queries impacting things badly. Take a look at which queries are running right now:

SELECT pg_stat_activity.procpid AS pid, pg_stat_activity.usename AS username, pg_stat_activity.waiting, now() - pg_stat_activity.query_start AS age, pg_stat_activity.current_query AS query FROM pg_stat_activity WHERE pg_stat_activity.current_query <> '<IDLE>'::text ORDER BY now() - pg_stat_activity.query_start DESC;

Look for the queries at the top of the list. Has one been running for a very long time? If you have a transaction which has been open for 16+ hours, it will definitely slow things down a lot.

Apache+mod_wsgi configuration for django project(s) on a quad core

Enable mod_headers in Apache and then add to your VirtualHost:

RequestHeader add X-Queue-Start "%t"

New Relic will then show you queueing time in the main overview chart.

This is the time between when request is first accepted by Apache child worker process and when mod_wsgi daemon process gets to handle the request. This can be used as one indicator of requests getting backlogged which in turn can indicate thread starvation in daemon process due to deadlocked threads or threads waiting on an external resource.

Unfortunately New Relic relies on requests completing to report data for that request. So if a request gets stuck you will not know about it. If all threads get stuck then daemon process will stop handling more requests.

Problem is that if number of processes/threads across Apache child worker processes is less than 100, the daemon process listener backlog, then all those threads can also get stuck and you will not know from Apache error logs because they will just sit there waiting for daemon to accept connection which never happens. Only the HTTP browser client will know as it will get connection refused when Apache child worker socket backlog fills up.

In mod_wsgi 4.0 I will be adding ability to configure the listener backlog for daemon process so can be reduced so you might get an error of some sort. There are already new options in mod_wsgi 4.0 to look for blocked threads and to restart daemon processes automatically and also dump a stack trace of where blocked threads were in code at the time.

To get that you would need to use mod_wsgi 4.0 dev code from mod_wsgi repo. You could then set blocked-timeout on WSGIDaemonProcess to 60 seconds and when all threads get stuck it will do the restart and recover, plus dump stack traces. Am still tweaking this and there are other configuration options related to this why don't describe here.

The mod_wsgi 4.0 code also has some other features which can be used with custom charting in New Relic to track growing number of blocked threads. Am not happy with it and needs to be changed a bit, but is stable.

Anyway, jump onto the mod_wsgi mailing list to discuss further.

Related Topic