[mod_python] MySQLDb or SQLObject connection pooling snippet wanted

On Friday 13 October 2006 07:35, Mike Looijmans wrote:
> For MySQL, I found that the best approach to connection pooling was "not".
>
> I could not conclude any performance difference opening the same page a few
> thousand times which executed a single SELECT returning a single row with a
> (mutexed) singleton connection, with a pooled set or just connecting for
> every request. Apparently, the overhead of calling MySQLdb.connect() is
> neglegible compared to all the other stuff that happens (i did this on a
> SUN Solaris 2.6 running Apache 2.0 with worker threads).
>
> The best performance gain is to put the SQL database on the same host and
> use a UNIX socket (not TCP) or a named pipe (on Windows NT) for the
> connection. I also improved security this way: No one can access the MySQL
> DB from outside the webserver, even if they have the password.

Just to make a couple of small points here:

For small databases and small-footprint webservers on relatively large 
machines, having the database and webserver on the same machine is fine and 
is indeed the best solution.  However, when the database tables become 
larger, the database and webserver can begin to compete for memory resources.  
This is NOT the same as swapping; if the database can be cached in memory 
(which most databases try to do, in one way or another), operation is MUCH 
faster than if the database is constantly being read from disk.  Also, If the 
database is hit quite heavily, it will also consume i/o bandwidth--the same 
resource that the webserver needs to deliver content quickly.

So, while having the database and webserver on the same machine CAN be a fast 
solution for small sites, I think that dogma in the web development world is 
that one should not follow this pattern in a production environment where 
database and webserver are running at near peak usage and will be competing 
for resources.  Also, using fast switches (gigabit or better) can alleviate 
many of the data transfer issues associated with separate machines.

Sean