Ed's Blog

MySQL Connection Pooling with Django and SQLAlchemy

Here's a quick and dirty recipe to get connection caching from SQLAlchemy. This is really not connection pooling as that would require a separate process to only handle connections. All this does is prevent the connection from closing after you finish a query. We're also not replacing the Django ORM.

To give a little more background, normally when you use Django to get to your database, Django will automatically disconnect from the database when the thread is done with that query.

So, if you do something like:

q = Model.objects.all()

As soon as you destroy "q" it will close the database connection. This is not ideal as it takes time to connect to the database and then to release the database connection. To prevent this, you can use SQLAlchemy to cache the connections for you. With SQLAlchemy, when "q" is destroyed the database connection is not closed. The database connection is only closed when the process running Python is closed. If you have your web server killing threads that are idle then you still have a problem, but you should be avoiding this anyway. See my previous blog post for details about thread thrashing.

Step 1 - Install SQL Alchemy:

This is a straight-forward install. You can get the latest here. You can get install instructions from sqlalchemy.org.

Step 2 - Clone mysql backend:

Copy mysql directotory from /django/db/backends/mysql to another directory in your PYTHON_PATH and call it mysql_pool.

You will be using this as your DATABASE_ENGINE instead of 'mysql'. For example, in your settings.py do this:

DATABASE_ENGINE = 'mysql_pool'

Step 3 - Update settings.py and database settings:

Add a DATABASE_WAIT_TIMEOUT = 120 setting to your settings.py. This timeout setting should match what your timeout setting in MySQL is set to. To see what it's set to in MySQL run this:

show GLOBAL variables;

Scroll to the bottom to find 'wait_timeout'. To set the wait_timeout setting in MySQL you can do this:

set global wait_timeout = 120;

Note that you'll probably want to change this settings in MySQL's my.ini or in the script you use to start MySQL in case you need to reset MySQL. It will revert back to what is in the .ini file. To do it via command line add --wait_timeout=120 to the command line. to change it in the my.ini file add wait_timeout=120 in the same section you find max_connections in.

Remember to change the DATABASE_ENGINE in settings.py!

Step 4 - Change the new mysql_pool backend:

Edit the new base.py that is in your new mysql_pool directory and add this to the top:

try:
    from settings import DATABASE_WAIT_TIMEOUT
except ImportError:
    print u'DATABASE_WAIT_TIMEOUT not in settings.py, defaulting to 120.'
    DATABASE_WAIT_TIMEOUT = 120

import sqlalchemy.pool as pool

Then, look for the line that reads "import MySQLdb as Database" and after that line (same indent level) add this:

Database = pool.manage(Database, recycle=DATABASE_WAIT_TIMEOUT-1) # must match or be less than wait_timeout in mysql

Finally, look for the line towards the bottom of base.py that reads "self.connection = Database.connect(**kwargs)" and add the following lines (same indent level):

if settings.DATABASE_HOST.startswith('/'):
    self.connection = Database.connect(port=kwargs['port'], unix_socket=kwargs['unix_socket'], user=kwargs['user'], db=kwargs['db'], passwd=kwargs['passwd'], use_unicode=kwargs['use_unicode'], charset='utf8')
else:
    self.connection = Database.connect(host=kwargs['host'], port=kwargs['port'], user=kwargs['user'], db=kwargs['db'], passwd=kwargs['passwd'], use_unicode=kwargs['use_unicode'], charset='utf8')

Testing:

To test your new setup, load your application and monitor the database connection in MySQL manager in the "Server Connections" section. Notice as you refresh the pages that the PID is not incrementing anymore. You can switch your DATABASE_ENGINE back to 'mysql' from 'mysql_pool' to compare how it was behaving before.

Hope this helps you get setup with connection caching quickly!