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.


  1. bagapiev

    Cool, I have been looking at SSD too for bridging RAM/disk gap. I would definitely add to this Fusion IO – http://www.fusionio.com/ (supposedly launching now), they claim 600 MB/s write (with 87,500 IOPS).

    They obviously don’t say how they do it but my guess is that they pipeline writes and have reads hit both the flash and the write pipeline, with the latter including comparison logic. This has been used for ages in processor design (I used to design microprocessors at Intel, brings back fond memories :) )

    Price is of course high, but IMO reasonable at $30/GB. You can get RAM at these prices these days but then you have to account for a machine to put the RAM in which will triple it. If it comes close to their claims should be very good as one should be able to emulate a machine with a ton of RAM.

    Standard disclaimer: I am not affiliated with them in any way, just thinking it is cool :)

  2. gregschulz

    I have been a fan of SSD, both RAM and FLASH based for almost 20 years, first as a user/sys admin to support I/O intensive journals, logs, indices, transient message files and so forth, then later as a vendor (manufacturer, oem and reseller) of various products and now as an industry analyst/consultant covering the technologies. Certainly with the price of RAM continuing to decline, the emergence and improved performance/reliability of FLASH, we are again seeing a resurgence of SSD particularly on the FLASH front.

    There are many FLASH and/or RAM SSD vendors in addition to Mtron and Fusiuonio including DNF, Soliddata, and Curtis, Samsung, Texas Memory Systems (TMS) among others providing different types of SSD solutions or components. For example there are the devices that physically look like a hard disk drive (HDD) in terms of packaging, essentially becoming plug compatible and replaces an HDD using either RAM, FLASH or a combination of RAM and FLASH in what is being referred to as a Hybrid HDD (HHDD). Some HHDD even combine RAM+FLASH+HDD for essentially three tiers of performance and storage media. Some of the FLASH based SSD are appearing in laptops, some are appearing in storage arrays replacing HDDs. Some like the Fusion card is a dedicated card with FLASH that sits in a PCI slot for dedicated use unless you enable the server to be an NFS/CIFS or iSCSI server or use some other storage sharing software (e.g. NFS/CIFS storage sharing like MSFT WSS, LeftHand, SeaNodes, StorMagic, etc.).

    While FLASH is inexpensive compared to RAM, FLASH has had a performance issue on writes in that FLASH is not as fast on writes, and, FLASH traditionally could only be cycled or written to “x” number of times. Current FLASH is improving on this so unlike an older USB thumb drive that if you did constant writes to it would eventually fail; newer FLASH media will operate a lot longer before failing. FLASH is great for persistency, RAM is great for read/write, IOPS and Bandwidth performance.

    Companies like TMS have combined the two in RAMSAN 500 where RAM is used as a cache or buffer to sit in front of FLASH modules that are parity protected to provide data persistence and isolate against component failure. The benefit of these types of SSDs is that they are more affordable than in the past compared to RAM with HDD for persistence’s solutions, and, they consume a lot less power while boosting performance.
    For the most part, SSD either FLASH, RAM or combinations are targeted for block access using SAS/SATA, SCSI, Fibre Channel (FCP), InfiniBand (SRP) or iSCSI on GbE/10GbE, however there are cache and I/O accelerators (e.g. Gear6) for NFS operations to sit in front of your existing NFS NAS appliances to boost performance.

    There are many different faces and uses for SSD, for high IOPS, for high bandwidth, for lower power consumption, for rugged environments and various combinations. Learn more about SSD in the StorageIO Industry Trends and Perspectives report “Achieving energy efficiency and performance gains using SSD” located at http://www.storageio.com/xreports.htm

    Greg Schulz
    Founder and Sr. Analyst – The StorageIO Group http://www.storageio.com
    Author – “Resilient Storage Networks – Designing Flexible Scalable Data Infrastructures” (Elsevier)

  3. bugmn

    The main point against MyISAM is that it doesn’t have support for transactions, unlike InnoDB and challengers (Falcon, Solid, and PBXT). No matter how fast the disk is, anyone who needs transaction semantics can’t use MyISAM.

    -sapphirecat






%d bloggers like this: