Here’s a small experiment I made that afernoon in order to see how to optimize a 2 disk MySQL server that is getting damn slow. That server is used for a search-engine like service. Very few updates, but tons of concurrent random reads. The server is running Debian Etch, it has 1 Gb of memory, while the main MyISAM table has 82 Go of data and 93 Go of indexes.
Out of the 2 disks, only the first one was used for the database, the other one was mainly here as a backup. The following benches have been made on ext3 volumes; the volumes were recreated and the database was reimported at every step to start without any fragmentation.
The app peaked at 41 requests/second with the initial configuration.
I then wanted to see how it would perform with 2 stripped disks.
The second disk was wiped, and the MySQL partition has been recreated as a RAID0 partition.
The app peaked at 72 requests/second with that configuration.
Then, I removed the RAID0 in order to get back to the initial configuration. One MySQL partition on each disk.
But the index and the data files were splitted. The .MYD file was left on the first disk, while the .MYI file was moved on the second disk using a simple symbolic links.
The app peaked at 137 requests/second with that configuration.
By moving the index file to another disk, there was a 334 % speedup!
My guess is that having data and indexes on the same volume (even a RAID volume) forces the head of the disk to constantly go back and forth to the index tracks and to the data tracks. And the kernel disk scheduler doesn’t help much when it comes to read data. Having the index file on a dedicated disk always keeps the head next to the needed tracks.
I didn’t expect such an improvement, more than 3 times, just by adding a disk!