list at joreybump.com
Thu Jan 27 11:23:24 EST 2005
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.