Database Connection Pool Solution for Django + Mysql

The solution:

An alternative DATABASE_ENGINE for django that leverages core django mysql code with minimal overlap and sqlalchemy for connection pooling. Also, a pretty ‘DRY’ stub to mix in your own pooling if desired, or adapt to use postgres instead of mysql.

1. Install sqlalchemy library: http://www.sqlalchemy.org/download.html
I used “Latest 0.5? myself.
2. Grab: mysql_pool.tgz

3. Make mysql_pool reachable by your project
3a. Unpack in your python’s “site-packages” directory
3b. Or: unpack somewhere in your project directory, and edit the “base.py” file in mysql_pool to fix the import lines containing uw.udjango.db.engine to be the new location mysql_pool.

4. Edit your settings.py to change DATABASE_ENGINE
4a. If 3a, set to uw.udjango.db.engine.mysql_pool
4b. If 3b, set to yourproject.whatever.mysql_pool

5. Edit your settings.py to add these (required) tuning settings:

  DBPOOL_WAIT_TIMEOUT = 28800  # your mysql db’s server side inactive connection kill time

  # discernable by ’show GLOBAL variables;’ in mysql, look for ‘wait_timeout’, changeable if desired

  DBPOOL_SIZE = 20 # the maintained number of dbconnections, over this returned conns are destroyed

  DBPOOL_MAX = 100 # the max allow connections, period

  DBPOOL_INTERNAL_CONN_TIMEOUT = 10 # how long to wait for mysql to give you a connection

That should do it!

The explanation:

I’ve been reading up on the various was being proposed to make some kind of persistent connection re-use part of core django. The are a couple of options I like, especially having a core database engine choice of sqlalchemy. A lot of implications there though, and I didn’t want to tackle them all.

At first I figured I’d write my own, and stubbed out the code to do that. Since I’m using mysql, I copied it’s engine tree in django.db.backends into my project and began working on it. Quickly I realized I didn’t want to rewrite, or maintain duplicates of, the whole tree. I only cared about two calls really: self.connection = Database.connect() and self.connection.close(). Modifying those would be a way to plug in pooling of my own. Also, in the process I looked to SQLAlchemy’s pool code as an example of Python implementation, and it seemed pretty good. Eventually I’d like to add some more subtlety to the pool grow/shrink strategy.

I started riffing on the solution described on Ed Menendez’s site, and merging in some of my ideas to make it more DRY.

Basically, all the classes in mysql_pool use class naming tricks to extend-without-modification and assume the identity and characteristics of the core mysql engine. That means there is no code in mysql_pool to maintain other than the “base.py” file, and even that one is able to use the same short cut for most of its classes, the exception being DatabaseWrapper, which is updated to use SQLAlchemy’s pool.

Also, I added settings.py level control of SQLAlchemy’s QueuePool, which is the pool type mysql_pool is forced to use.

Other links of note on related topics:

Proposal: user-friendly API for multi-database support