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.
10 comments:
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'
We wrote it for django 1.3.x and sqlalchemy 0.7.7. I will check with latest sqlalchemy version.
Does this handle growing and shrinking the pool, and how are connections returned to the pool.
Cheers - keep up the good work
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
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.
Were you able to fix it ? I guess the issue is with django 1.4.x. I am also facing the same.
Found the solution in here - https://github.com/jeethu/djorm-ext-pool/commit/5d7f8fff5c7dbef97954bfa603ad05eee7477846
Full solution with SSL and patches for Unhashable errors explained here:
http://codeispoetry.me/index.php/django-mysql-connection-pooling-and-ssl/
Which library is load_backend() imported from?
The code is saying load_backend() is not defined.Do we need any other module.
Post a Comment