This would be cured by moving the database to solid state memory. Mechanical media has a very long seek time and when many seeks are required, become unreliable.
Either: Run two MySQL instances, one starting after the finest grained database files have been copied to ramdisk. Replicate database to a hard disk file.
Or Install a solid state IDE disk. For example, a 4Gb Compact Flash card has an IDE interface built in as part of the specifications. Access time 0.1ms comapred to mechanical 8.5ms. 85x faster. CF to IDE cables are trivial and available.
To put it another way, you would need 85 mechanical drives to provide the seek performance of a solid state equivalent.
Brion Vibber wrote:
We really, really need to move the database back to a machine with a decent hard drive. The wikis are very sluggish, and a fair chunk of it's from waiting on the database.
Ursula's sitting around with a 90% idle CPU, but everything's blocked on disk I/O to the point it's got a load average of about 16. At any given time from 8-20 processes are blocked and waiting. Operations that hit a lot of rows like history and watchlist are particularly badly hit since they don't play as well with caching.