[mod_python] modpython, mysqldb best paractice

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?

Oh, you may also want to visit the MySQL forums too and see
if anybody over there has any insights.
-- 
Deron Meranda