SSD + MyISAM Thread Scalability

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).

Thread Scalability

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.

200802052158

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


  1. yonkovim

    Kevin,

    Interesting stuff. With inndob being CPU bound couldn’t this be more a function of how the buffer pool works? With MyIsam since only the index blocks are stored in memory there should be more disk activity. If everything is able to be loaded into the innodb buffer pool there should be a much smaller IO footprint, and conversely the cpu would go higher. With the Mtron drive I have seen pretty decent sysbench & dbt2 results. When I was doing the dbt2 tests ( http://www.bigdbahead.com/?p=37 ) Until I loaded 200 warehouses and tested with 60 active the disk in innodb was not touched. Really up until that point it was nothing but a test of the CPU. I may give the MyISAM stuff a whirl to check it out though.

  2. tgabi

    That is pretty much my experience too, except I get about 17-18MB/s from MemoRight drives. You should try these drives. I don’t think the 16K InnoDB block make much of a difference. The SSD drive itself has to rewrite some 2MB of data for every sector – unlike HDD.

  3. 17-18MB/s as what? Random IO? Sequential IO?

    URL for your MemoRight drives?

  4. Yankovin,

    I’m working under this same theory. If the InnoDB buffer pool isn’t very efficient it could be 100% CPU bound after filling up.

    I”m going to run a few more tests. I can’t imagine that it needs to be so CPU intensive so this must be a bug of design flaw.

    I’d say MyISAM is a winner here but it’s far too slow at INSERTs. It becomes CPU bound. Damned if you do, damned if you don’t really.

  5. Tgabi,

    How much did you pay for the MemoRight drives? What size?

    They don’t have all the specs on their site… 800 random writes is nice though!

    The Mtrons will probably be better in random reads in that they can do 19k random 512B reads.

  6. tgabi

    Random IO. Sequential IO goes to about 50MB/s

  7. tgabi

    I’ve got the MemoRight from DVNation. The 64GB I use now are $2K.

  8. tgabi

    BTW, I use 4K blocks so I guess your MTRONs are faster at reading. I’ve got the MemoRight over MTRON because of faster writes, plus they have 64GB in 2.5 inch format.

  9. From the specs on that drive it should only be able to do about 4-5MB 512B IOs. Could this benchmark be on 4096 byte IOs?

    Yeah… it’s DEF nice that Mtron has competition. The price point for the Mtron was right.

    I think it would be $1100 for a 32G RAID…

    Of course all of this depends on how much storage you need.

    We’re going to do a 96G RAID which is more than enough for us right now.

    In 6 months the prices for 64G is going to come down and I’ll probably swap them out or bring on more machines.

    I also think they’re going to start having drives with better random write performance.

    We’ll see.

    Kevin

  10. tgabi

    If you do a RAID, don’t forget to test for optimum strip size and share. Thanks.

  11. You mean share the results?

    That was the plan. I was going to do another post on RAID size.

    Kevin

  12. er….. RAID performance.

  13. tgabi

    Share the results of course.

  14. tgabi

    Isn’t possible that myisam INSERTs are due to many/complex unique indices ? I’m IO bound on myisam inserts, can’t figure what would consume CPU for INSERT.

  15. tgabi

    I was talking about myisam INSERTs being CPU bound.

  16. There were only two indexes here. Varchar and integer. I could ditch the varchar and see if that improves things.

    I’m going to try to figure out why the INSERTs are CPU bound. Makes no sense. If this problem is fixed MyISAM will more than solve my problem.






%d bloggers like this: