Extreme slow periods (system locking up)
About two weeks after installation, a database of modest size (no more than ~100k records in any table, mostly SELECT) is starting to regularly "freeze", where the processlist swells to dozens of entries and the simplest SELECT query take minutes to complete. This is not easily reproducible, as the same small set of queries seems to cause no problem elsewhere. I tried every one of the queries individually, and none took more than a second of time (most much less). I did a "CHECK TABLE", with no error indications.
The system usually "recovers" after a while, but access might be blocked for several minutes and system response is extremely slow for a longer time period. During that time, I can't even get a process list and the Linux queue size, as reported by 'w', increases to obscene values of 20 or more (normal load is around 0.1).
System: 5.0.20 and 5.0.21 on Centos 4 with replication (very similar problems occurred with 5.0.19 on Solaris, obviously on a completely different platform with no replication); this occurs on two different hardware platforms
The database is using 'localhost' and the PHP mysql database interface.
This seems similar to the earlier "system locking up" thread from early May, but using a different version of MySQL. I was told, in declarative tone, that this could not possibly be a mySQL bug when I filed a bug report. If this is expected behavior, I'm rather worried about the usability of the platform...
Any insights would be appreciated; process list snippets available upon request.