Could SSD Mean a Rise in MyISAM Usage?
I had a bit of an idea today about SSD and MyISAM which I think is pretty interesting.
If you’re building a database which is write heavy, you’re going to want to use InnoDB. If you put the entire DB in memory your performance should be fairly impressive.
InnoDB has a cool trick where it uses a write ahead log to commit transactions in memory, and then periodically flush them to disk during a checkpoint operation.
When it flushes the data it’s able to order all the writes so as to minimize the number of disk seeks. You’re then able to write to the disk at nearly full speed.
We use this in Spinn3r and we see about 30MB/s write throughput.
MyISAM on the other hand is a mini finite state machine machine which locks the whole table during writes.
While InnoDB is good for write throughput it uses an MVCC model which causes the DB to grow about 3-4x larger than MyISAM.
This is on HDDs though.
SSDs are really starting to take off. The mtron drives we’re looking at should see about 150k IOPS and 80MB/s write throughput.
The problem is, they’re much smaller.
We’re on 750G SATA drives right now and are thinking of swapping them for 16G SSD.
So why not just run MyISAM on SSD? The write ahead log doesn’t make much sense any more. You can already write to the disk at 80MB/s. A few more IOPS aren’t going to kill you.
The locking isn’t going to hurt you either because while the table is locked you’re going to be able to get it over with very quickly.
You can also fit 3-4x more data on the disk which is another huge win.
The devil is in the details though. I played with running MyISAM on a ramdisk about 4 months ago and InnoDB beat MyISAM here in terms of raw CPU usage.
I’m going to try to get my hands on some SSD drives in January and this will be part of my benchmarks.