[mod_python] modpython, mysqldb best paractice

Hi,

I am running stable on a production machine (uptime > 1 year) on a  
6000 hits/week system:
(CentOs 64 bit)

I solved problems with hanging connections (cousing "memory leaks")

some hints and tips:

Never ever connect to mysql globally but connect to mysql in you  
handler stage
Not even consider connection pools, mysql is fast enough.

make the connection a part of the request object so you can pass the  
request object around
so req.connection and req.cursor exist.

right after you make the connection do a req.register_cleanup to a  
function similair to:

def closedb(req):
	req.cusor.close()
	req.connection.close()

Now when users stop the downloading (by pressing the stop button)or  
when a python error occurs the connection is closed too. Otherwise  
mysql will keep the connection and runs out after a while (for me this  
happenend at around 19000 request but that depends on system  
configuration and installed memory.

Off topic: when I started using SQLAlchemy i had to find a way to to  
this (since sqlAlchemy is using mysqldb) because this problem started  
to come back.
I have found a way around it by using a "hidden" feature of  
SQLAlchemy, If people are interested please let me know, i'll post it.

Martijn


On Jul 19, 2006, at 11:04 PM, Deron Meranda wrote:

> On 7/19/06, Jim Gallacher <jpg at jgassociates.ca> wrote:
>> CentOS kernel issues resulting in similar problem (but not just  
>> mysql):
>> http://bugs.centos.org/view.php?id=1174
>
> Ah, I did not know about that one.  From reading it, this sounds
> almost exactly like the problem Martijn is probably experiencing.
> I doubt mod_python memory leaks would exhibit the same type
> of behavior Martijn describes.  This is why I suggested
> monitoring with vmstat...if this is the problem it should show up
> quite obviously.  (Of course I don't know what kernel Martijn
> is running at this point in the thread)
>
> BTW, I'm also running everything under CentOS 4 as well
> (with the 2.6.9-34.0.1.ELsmp kernel), but have not seen any
> problems at all.  But then I'm in IA32, and not IA64, which is
> what this bug report as well as Martjin are running.  So it looks
> like a possible bad Linux kernel for 64-bit.
>
> Martijn, can you provide your kernel version, etc.?
>
> Good news is that a new fixed kernel is apparently on the way.
> -- 
> Deron Meranda