6

My stack incudes django (1.4.3), psycopg2 (0.0.3), and postgres (9.1). Additionally, I am using psycogreen.gevent.patch_psycopg because I am serving up my django with gunicorn and gevent.

Everything seems pretty happy, but I am getting a lot (~40) of open database connections. Until I set 'autocommit' to True in my django database setup, they were all "idle in transaction." Now they are all just "idle."

Here is a sample of my pg_top output. 

last pid: 22043;  load avg:  0.09,  0.05,  0.05;       up 6+21:49:58                                                    16:21:08
45 processes: 45 sleeping
CPU states:  3.0% user,  0.9% nice,  0.2% system, 96.0% idle,  0.0% iowait
Memory: 871M used, 130M free, 32M buffers, 530M cached
Swap: 10M used, 246M free, 2192K cached

  PID USERNAME PRI NICE  SIZE   RES STATE   TIME   WCPU    CPU COMMAND
10035 postgres  39   19   51M 6668K sleep   0:00  0.00%  0.00% postgres: user production 127.0.0.1(41309) idle                  
16783 postgres  39   19   51M 6652K sleep   0:00  0.00%  0.00% postgres: user production 127.0.0.1(42426) idle                  
10034 postgres  39   19   51M 6640K sleep   0:00  0.00%  0.00% postgres: user production 127.0.0.1(41308) idle                  
12690 postgres  39   19   51M 6604K sleep   0:00  0.00%  0.00% postgres: user staging 127.0.0.1(36931) idle                   
10293 postgres  39   19   51M 6704K sleep   0:00  0.00%  0.00% postgres: user staging 127.0.0.1(36931) idle

... and 36 more lines like the above.

This is the setup my gunicorn.conf for the psycogreen:

def post_fork(server, worker):
    from psycogreen.gevent import patch_psycopg
    patch_psycopg()

Two questions:

  1. Are all these open connections okay? It looks like a lot of wasted memory use to me.
  2. Is this related to gevent or psycogreen? I am not explicitly using the database inside any greenlets.

I have read a lot about database connections left open from django, but it all appears to be from about 2 years ago when there was still an open bug in django (https://code.djangoproject.com/ticket/9964#comment:51).

Any insights are much appreciated.

Chris Travers
  • 25,424
  • 6
  • 65
  • 182
t1m0
  • 735
  • 1
  • 7
  • 14
  • I found some resources to help this: use pgpool2 to set a connection timeout http://chase-seibert.github.io/blog/2011/09/30/using-pgpool2-to-timeout-idle-postgres-connections-from-django.html and a similar problem on SO: http://stackoverflow.com/questions/13236160/is-there-a-timeout-for-postgresql-connections It sounds like I have a connection leak. pgpool2 will help handle it, but better would be to fix the leak. – t1m0 Apr 26 '13 at 18:45

0 Answers0