Announcement

Wednesday, July 11, 2012

Django connection pooling using sqlalchemy connection pool

As you know, Django uses new database connection for each request. This works well initially. However as the load on the server increases, creating/destroying connections to database starts taking significant amount of time.  You will find many questions about using some kind of connection pooling for Django on sites like StackOverflow . For example, Django persistent database connection. 

At BootStrapToday we use sqlalchemy's connection pooling mechanism with Django for pooling the database connections. We use variation of approach by Igor Katson described in http://dumpz.org/67550/.  Igor's approach requires patching Django which we wanted to avoid. Hence we created a small function that we import in one of __init__.py (or models.py) (i.e. some file which gets imported early in the application startup).


import sqlalchemy.pool as pool
pool_initialized=False

def init_pool():
     if not globals().get('pool_initialized', False):
         global pool_initialized
         pool_initialized = True
         try:
             backendname = settings.DATABASES['default']['ENGINE']
             backend = load_backend(backendname)

             #replace the database object with a proxy.
             backend.Database = pool.manage(backend.Database)

             backend.DatabaseError = backend.Database.DatabaseError
             backend.IntegrityError = backend.Database.IntegrityError
             logging.info("Connection Pool initialized")
         except:
             logging.exception("Connection Pool initialization error")

#Now call init_pool function to initialize the connection pool. No change required in the
# Django code.
init_pool()

So far this seems to be working quite well.

8 comments:

Eytan Daniyalzade said...

What version of django/sqlalchemy did you achieve this with? When I try this with django 1.4.1 and sqlalchemy 0.8, the code is failing for me with the following stacktrace:

File "/Users/eytan/.virtualenvs/parsnip/lib/python2.7/site-packages/django/db/backends/mysql/base.py", line 387, in _cursor
self.connection = Database.connect(**kwargs)
File "/Users/eytan/.virtualenvs/parsnip/lib/python2.7/site-packages/sqlalchemy/pool.py", line 1048, in connect
return self.get_pool(*args, **kw).connect()
File "/Users/eytan/.virtualenvs/parsnip/lib/python2.7/site-packages/sqlalchemy/pool.py", line 1019, in get_pool
return self.pools[key]
TypeError: unhashable type: 'dict'

Nitin Bhide said...

We wrote it for django 1.3.x and sqlalchemy 0.7.7. I will check with latest sqlalchemy version.

Drew said...

Does this handle growing and shrinking the pool, and how are connections returned to the pool.

Cheers - keep up the good work

Nitin Bhide said...

When django calls connection.close(), the connection is returned to pool. We use SQLAlchemy connection pooling. Hence growing and shrinking of pool has handled by SQLAlchemy. check http://docs.sqlalchemy.org/en/rel_0_8/core/pooling.html

Kent said...

Did either of you manage to figure out what was causing this issue? I'm getting the same issue with Django 1.4.3 and sqlalchemy 0.7.9.

karthik said...

Were you able to fix it ? I guess the issue is with django 1.4.x. I am also facing the same.

karthik said...

Found the solution in here - https://github.com/jeethu/djorm-ext-pool/commit/5d7f8fff5c7dbef97954bfa603ad05eee7477846

Danigosa said...

Full solution with SSL and patches for Unhashable errors explained here:
http://codeispoetry.me/index.php/django-mysql-connection-pooling-and-ssl/