i have a database that is the information station of our company. We monitor machines and send alerts to our customers or display their information on the web. The database is very busy, with some of our tables getting about 1.5 inserts per second. Sometimes more.

Our history table is growing about 45M / day

The process that puts data into the database and the processes that manipulate that data is run by threads in a python program. About 12 threads in all, each one either reading from or writing to a particular table. We have two tables that are causing us trouble, and another that will be soon.
Our history table because it is very very large, and another table we have that is small, like 140 rows, this is just where we put configuration variables that are read from the threads.

So, our large table and our small configuration are showing up a lot in the slow query log. I have read tons of online articles and i have an SQL book. Just can't seem to find what is breaking our system. We are getting errors like lock wait timeout, and msyql database has gone away.

The tables are all Innodb and the server has 8 dual-core processors, 16Gig of RAM, on a 32 bit version of Ubuntu Linux. ( our processors would not let us use a 64 bit )

What to i do about this small table ?

the server health meters in the mysql-admin are not ramping up. It is a problem that happens after a while of running, it will start crashing our engine with mysql errors, restarting the MySQL server does not seem to help, but rebooting the computer does help, but then after a few days we are in the same mess again.


thanks for any suggestions

Python has a Global Interpreter Lock (GIL) that allows only one thread at a time to "run" within Python. It's likely that you're deadlocking like this:

Thread-1: Has GIL, waiting for lock to release on database
Thread-2: Waiting for GIL to release, has lock on database

That's the first thought that comes to mind for me...and once two threads get deadlocked like this, the Python program would come to a halt (until the lock timeout on the database allowed one Python Thread to bomb...but it wouldn't be too long before the condition arose again). To work around this, you could potentially run the 12 or so Python threads in their own process so if one locks, it won't cause a wait on the other 11.

As for the history table, can you run a weekly or monthly process to partition the table into, say, "History-2008-08", "History-2008-07", "History-2008-06", etc...? Breaking the large history table into smaller monthly history tables would greatly speed accesses and writes to the history table(s).

thanks, we have started the history split into different years, and it does make a difference. I had never know about the thread and python issue, will check out your workaround.

thanks again