[mod_python] modpython, mysqldb best paractice

Deron Meranda wrote:
> On 7/19/06, Martijn Moeling <martijn at xs4us.nu> wrote:
>> If mod python is running in Interpreter per directory mode, one
>> interpreter is created since all my content for mkbOK resides in / in
>> total over 1 in total over 14.000 different pages, and since we have
>> over 10.000 pageviews per day and aim for 100.000+ per day at the end of
>> the year I am preparing for a second server (which my system can handle)
>>
>> If mod python is running in Interpreter per directive mode I can and up
>> with god knows how many interpreters.
> 
> There's probably no need for you to use multiple python interpreters
> at all.  The only advantage is that it can provide you with some
> level of isolation (but not perfect); it will not provide any performance
> benifits (and actually is more likely to decrease performance somewhat).
> But as you're using it, you don't need more than one interpreter.  So just
> avoid all the PythonInter* directives.
> 
>> The register cleanup is clear now, since my system creates the database
>> connection in the class module (the init call creates the class) I might
>> have to alter that but.
> 
> Also don't forget about try:...finally:... blocks.  That's often the
> simplest way to make sure you clean up after something.
> 
> If the database connection is made inside your class, perhaps you
> should put a disconnect call in the class's destructor, __del__().  I
> don't know if you're using new-style classes, or traditional classes,
> but perhaps something like:
> 
>    class db_based_service(object):
>        def __init__(self):
>            self.db = None
>        def __del__(self):
>            self.disconnect()
>        def connect(self):
>            self.db = MySQLdb.connect( ..... )
>        def disconnect(self):
>            if self.db is not None:
>                self.db.disconnect()
>                self.db = None
>        def init(self):
>            self.connect()
> 
> Furthermore, if you're using transactions, you should make sure
> that you don't have any lingering open transactions.  If you're
> connecting and disconnecting on every request you probably don't
> need to worry quite so much.  But if you ever re-use or pool your
> database connections in the future, you may want to consider
> insuring that all your transactions get terminated at the end of
> the request.  Perhaps extending the framework to something like
> 
>    class ..... (same as above)
>        def __init__(self):
>            # same other stuff above
>            self.in_trans = False
>        def __del__(self):
>             self.rollback()
>             self.disconnect()
>        def start_transaction(self):
>            if self.in_trans:
>                raise RuntimeError("Attempted nested transaction")
>            self.db.begin()
>            self.in_trans = True
>        def commit(self):
>            if self.in_trans:
>                db.commit()
>                self.in_trans = False
>        def rollback(self):
>            if self.in_trans:
>                db.rollback()
>                self.in_trans = False
> 
> Of course you may want to see if too-many-connections or
> non-terminated transactions are even a problem.  Periodically
> run the mysql "show processlist" command.  Maybe even an
> occasional "show status" may be informative.
> 
>> The system goes from normal cpu utilization to 100% within a few
>> microseconds, and it happens now and then, sometimes within a few hours
>> after a reboot, sometimes it runs for weeks without trouble
> 
> Once it gets in that state will it ever eventually clear up?
> 
> Is your system going into an I/O paging fit?  Run the command
> "vmstat 5" and watch the "so" and "bo" columns for a minute.
> "so" should stay near 0, and "bo" should have faily low numbers
> (say <30), but really you should compare it against when the
> system is running okay.
> 
> Also run "top" and determine exactly which process(es) are
> charged with using the most cpu.
> 
>> I tried multiple cron thingies to investigate, but even cron slows down
>> so mutch that a "service httpd restart, and/or a service mysql restart"
>> take hours to complete,
> 
> Certainly sounds like heavy paging or swapping.
> 
>> in fact (but keep in mind I have had no interactive access) I think
>> mysql stops responding at all even to signals. I even tried "nice" in
>> the hope that mysql could not take 100% but that was not the case and it
>> slowed down the page building process (not surprised haha). Even
>> installing a second CPU did not help.
> 
>> The even more stupid thing is that this behavior does not happen on a
>> PIII 1 Ghz with a excact copy of the HDD (dd if=/dev/hd1 of=/dev/hd2)
>> Since the cpu in our production machine is 64 bit I suspected that, and
>> build apache, and mod_python and python all from scratch,.. no luck.
> 
> What about the amount of memory.  That can have an even bigger
> impact than the speed of the CPU.
> 
>> Different mysql versions did not matter too.
>>
>> The oddest thing is that after an update of my python code on the server
>> (new release of my system) is takes 1 or 2 days before it happens, than
>> it takes say 4 or 5 days, next it runs ok for weeks.
> 
> Perhaps you've got some suboptimal SQL.  For instance are you
> doing a lot of sorts, or very large joins?
> 
> Also what MySQL storage engine are you using?  InnoDB, or
> MyISAM, etc?

Also, I'm not sure if you told us anything about your OS, apache version
and mpm (prefork or worker) or mod_python version.

The fact is the mod_python has memory leaks. We are tracking them down -
3.2.8 is better than 3.1.4, and the next stable release will be better
yet. (Fixed a leak util.parse_qsl used in FieldStorage). Having
mod_python leak memory could impact mysql, causing heavy swapping when
trying to build a query result. Just a WAG and another thing to consider.

Jim