Thoughts on Maria and SSD

A lot of people have been buzzing about Maria over the last few days.

One of the things I’ve been thinking about is how Maria/MyISAM fits into the SSD puzzle WRT InnoDB.

InnoDB was generally designed for use with HDDs. Specifically, the write ahead log was added for MVCC and additional performance.

However, MyISAM on SSD wouldn’t suffer form the same performance hit due to lock contention. At least I don’t think so. I need to run some tests to see if my theory holds.

In high write environments it’s going to lock more but those lock will complete quickly.

So why not ditch InnoDB and just go with MyISAM? InnoDB is a bloated beast compared to MyISAM. Or tables take up about 3.5x additional storage.

SSDs are smaller. About 25x smaller than HDDs (but 30x faster). This means you can fit more than 3x data on the same SSDs.

That, and there are a LOT less IOs that you need to complete – which means 3x additional performance.

The missing piece to the puzzle was crash recovery. If you lose a box due to a hardware problem or core dump how do you get the image back online quickly?

A full restore might work since SSDs are smaller and would be quick to rebuild. The other alternative might be to use Maria since it supports crash recover.

At least there are more options here.

  1. mdcallag

    My tables are about the same size using InnoDB as they are with MyISAM. My guess is that your tables have large primary keys with several secondary indexes and repeating the large primary keys in the secondary indexes is what causes the bloat.

  2. We do have large primary keys. Not very many secondary indexes though. However, in practice ditching the indexes doesn’t save much.

    Are you using the compact row format?

  3. mdcallag

    We just upgraded from 4.0.26 to 5.0.37. With the compact row format, database size shrank by 15%. InnoDB may also use more space for tables with very small rows, as the per row overhead of ~20 bytes becomes significant and for tables with many blob columns that are just big enough to use overflow pages, but then leave a lot of empty space in those pages. Fortunately, neither of those conditions apply for me. Also, when you measure the size difference are you comparing space used by an old InnoDB table that has seen many page splits and deletes versus a recently loaded MyISAM table?

  4. Yeah…. 15% is what I saw for the compact row size as well. Not too impressive and right now not worth the switch for us…

    I forgot about the overflow pages. I should take a look at that.

    All of our space comparisons that I’ve done have been for new tables….


%d bloggers like this: