Login

Persistent connection to PostgreSQL database

Author:
mike_tk
Posted:
August 29, 2009
Language:
Python
Version:
1.1
Score:
4 (after 4 ratings)

Hi,

I made some small custom psycopg2 backend that implements persistent connection using global variable. With this I was able to improve the amout of requests per second from 350 to 1600 (on very simple page with few selects) Just save it in the file called base.py in any directory (e.g. postgresql_psycopg2_persistent) and set in settings

DATABASE_ENGINE to projectname.postgresql_psycopg2_persistent

This code is threadsafe, however because python don't use multiple processors with threads you won't get bit performance boost with this one.

I really recommend using it in daemon mode. In apache mod_wsgi just set processes=8 threads=1

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
# Custom DB backend postgresql_psycopg2 based
# implements persistent database connection using thread local storage
from threading import local

from django.db.backends.postgresql_psycopg2.base import DatabaseError, \
    DatabaseWrapper as BaseDatabaseWrapper, IntegrityError
from psycopg2 import OperationalError

threadlocal = local()

class DatabaseWrapper(BaseDatabaseWrapper):
    def _cursor(self, *args, **kwargs):
        if hasattr(threadlocal, 'connection') and threadlocal.connection is \
            not None and self.connection is None:
            try: # Check if connection is alive
                threadlocal.connection.cursor().execute('SELECT 1')
            except OperationalError: # The connection is not working, need reconnect
                threadlocal.connection = None
            else:
                self.connection = threadlocal.connection
        cursor = super(DatabaseWrapper, self)._cursor(*args, **kwargs)
        if (not hasattr(threadlocal, 'connection') or threadlocal.connection \
             is None) and self.connection is not None:
            threadlocal.connection = self.connection
        return cursor

    def close(self):
        if self.connection is not None:
            self.connection.commit()
            self.connection = None

More like this

  1. Template tag - list punctuation for a list of items by shapiromatron 11 months, 2 weeks ago
  2. JSONRequestMiddleware adds a .json() method to your HttpRequests by cdcarter 11 months, 3 weeks ago
  3. Serializer factory with Django Rest Framework by julio 1 year, 6 months ago
  4. Image compression before saving the new model / work with JPG, PNG by Schleidens 1 year, 7 months ago
  5. Help text hyperlinks by sa2812 1 year, 8 months ago

Comments

adamcik (on August 30, 2009):

Love the idea, however pyscopg2.pool.PersistentConnectionPool might be of interest if one wishes to make this snippet thread safe :-)

See the pyscopg2 git-repo for the pool code for more details.

#

mike_tk (on August 31, 2009):

I have the pyscopg2.pool.PersistentConnectionPool code ready too, but not published yet. I've added note about threads safety.

#

ripper (on May 7, 2010):

This snippet more then doubled my speed while making huge amounts of posts to my django app but in its current state leaves idle connections that dont go away in postgres. I have crashed many test servers with this code by filling up postgres with huge amounts of idle connections that dont go away =)

#

mike_tk (on October 29, 2010):

@ripper

Probably because you use it in threaded mode. The number of connections should not goes higher than processes amount * threads in each process.

I never had such kind of problems with apache mod_wsgi in daemon mode.

#

Please login first before commenting.