[mod_python] pool and mps

Nicolas Lehuen wrote:

> There is no standard way to do it. First, if you are using the Apache
> mpm_prefork (the forked MPM), the efficiency of pooling will be very
> low, since your pool will be instantiated as many times as there are
> Apache processes.

It's still worth doing, however, as you can determine by tailing the 
mysql log that connections are being reused more often.

> I'm using the threaded MPM, and I built my own DB connection pool. The
> best place to instantiate the DB connection pool is in a module on the
> PYTHONPATH, which will be imported from any published modules. This
> way, you are guaranteed that the pool will be instantiated only once.

Here's some code to play with, all using the Publisher module, prefork 
apache 1.3, mod_python 2.7:

1. Most inefficient approach:

#db1.py
import MySQLdb
# set up handle in a function
def getdbh():
     dbhost = "localhost"
     dbuser = "bob"
     dbpwd  = "secret"
     dbname = "foo"
     dbh = MySQLdb.connect(dbhost, dbuser, dbpwd, dbname)
     return dbh
# get handle from function
dbh = getdbh

This will create a new db handle with every connection:

  http://host/db1/dbh

2. Better:

#db2.py
import MySQLdb
# use underscore to prevent values from being exposed via HTTP
_dbhost = "localhost"
_dbuser = "bob"
_dbpwd  = "secret"
_dbname = "foo"
_dbh = MySQLdb.connect(_dbhost, _dbuser, _dbpwd, _dbname)
# db handle is available within functions
def dbh(req):
     return _dbh

This method seems to reuse handles more often:

  http://host/db2/dbh

3. Best, import handle from external module, preferably in path outside 
of DocumentRoot:

# connect.py
# put this module in a directory in PYTHONPATH
import MySQLdb
# Set up db connection and return handle.
_dbhost = "localhost"
_dbuser = "bob"
_dbpwd  = "secret"
_dbname = "foo"
dbh = MySQLdb.connect(_dbhost, _dbuser, _dbpwd, _dbname)

Now create a published module that imports the connection:

# db3.py
import MySQLdb
# import db handle from module in PYTHONPATH
from connect import dbh

While not truly persistent, this seems to provide the best connection reuse:

  http://host/db3/dbh

You need to tail your mysql log to get an idea of how well connections 
are being reused; you can't rely solely on the instance location 
returned by mod_python. Add some SELECT statements if you want to make 
it more interesting. Also, I have no idea how this will work on a 
anything other than a prefork apache.

I think the ideal technique would make the same connection available to 
multiple apache processes but limited to the appropriate interpreters, 
and spawn new connections as needed. I'm not sure how this would be 
done, or if anyone has published code that will do this for mod_python.