I spent some more time today computing numbers on MyISAM thread scalability running on MyISAM.
This was JUST a random read test. No INSERTs, UPDATEs, or DELETEs.
I wanted to see if MyISAM could saturate the SSD to the point where it was faster than an all in memory InnoDB database running on a conventional HDD.
Some good news:
- MyISAM totally saturated the drive. It was doing 19k 512B random reads per second – reading data at about 7.5MB/s. This is the full maximum random read capacity of the drive!
- MyISAM was able to do this with very little CPU overhead (about 10%).
- If I RAID together three SSDs I should be able to get about 21MB/s random read throughout.
Now the bad news:
- The InnoDB numbers are looking very pathetic. InnoDB was 100% CPU bound. It was about 3x faster but I can make up the difference by buying 3 SSDs and RAIDing them.
- I don’t think InnoDB will be able to perform well on the current generation of SSDs because it uses a 16k block size. I tried to tune this back to 8k to see if performance would improve but the MySQL daemon would dump core.
- While InnoDB was great for CPU utilization during INSERTs it fails at CPU utilization during SELECTS. MyISAM is the reverse. It was VERY slow during INSERTS (bottlenecking on the CPU).
The figure below details the thread scalability of MyISAM on SSD. I ran the exact same sysbench simple OLTP test (point SELECT queries) with a different number of threads for each test.
There’s a HUGE spike around 140 threads. I’m wondering if this might be the SATA controller or the on board controller on the SSD.
Figure 1: MyISAM time to completion for concurrent threads accessing MyISAM at the same time. The X axis is the number of threads. The Y axis is the seconds this test took to complete