The second point sounds bad, but it is the best option because it does not
freeze the Django MySQL backend at a specific revision. Using this method
allows us to benefit from any bugs that the Django project fixes, while
layering on connection pooling.

The actual pooling is done using SQLAlchemy. While imperfect (this backend
is per-process only) it has usefulness. The main problem it solves for us
is that it restricts a process to a certain number of total connections.

Connection Closing

While this has nothing to do directly with connection pooling, it is tangentially
related. Once you start pooling (and limiting) the database connections it
becomes important to close them.

This is really only relevant when you are dealing with a threaded application. Such
was the case for one of our servers. It would create many threads for handling
conncurrent operations. Each thread resulted in a connection to the database being
opened persistently. Once we deployed connection pooling, this service quickly
exhausted the connection limit of it’s pool.

This sounds like a huge failure, but for us it was a great success. The reason is
that we implemented pooling specifically to limit each process to a certain
number of connections. This prevents any given process from impacting other
services, turning a global issue into a local issue. Once we were able to identify
the specific service that was abusing our MySQL server, we were able to fix it.

The problem we were having with this threaded server is very well described below.

With pooling (and threads), closing the connection early and often is the key to good
performance. Closing returns the connection to the pool to be reused, thus the total
number of connections is decreased. We also needed to disable the use_threadlocal
option of the QueuePool, so that multiple threads could share the same connection.
Once we decorated all functions that utilized a connection, this service used less
connections than it’s total thread count.

Forking

If you are using mysqlpool with a daemon (our project uses Django admin commands to
build daemons) then you need to take care with the connection pool. After a fork()
the pool will be unusable. In our case, the file descriptors for the connections
were closed, and in the child, any new connections or files assumed the fd of the
MySQL connection this caused the Django ORM to read/write on some non-MySQL
connection in our case, Redis, so Django would send SQL to redis an expect a
reply! The solution is to close the pool before fork()ing. This will release the
pooled connections which will be reopened when the child first attempts to use
them.