We are planning to implement a four node MySQL Cluster and are considering using SSDs as the storage. We would like to get a high level of performance and very low latency on disk IO from the small cluster, so we are looking at SSDs. Does anyone have any experiences with either MySQL or MySQL Cluster on SSD, or building SSD raid groups that could share their experiences or thoughts?

We're looking for long answers that provide some explanation and context. Don't just give a one-line answer; explain why your answer is right, ideally with citations. Answers that don't include explanations may be removed.

  • Take a gander at blog.serverfault.com/2011/02/09/our-storage-decision - not MySQL, but relevant. – Shane Madden Dec 22 '11 at 1:02
  • @Shane Thanks for the link. In the comments the first post regarding TRIM really puts me off to SSDs. I've been researching SSDs for a few weeks now and it seems SSD raid support is in its infancy and is not recommended for long term use as TRIM is not supported. – somecallmemike Dec 22 '11 at 1:26
  • 1
    Sure, but don't over-estimate the need for TRIM on a dedicated database drive - the performance loss from small file churn that TRIM cleans up is not nearly the same issue on a database drive as it is on an OS drive. – Shane Madden Dec 22 '11 at 1:37
  • @Shane What about SSDs behind a raid controller? I have read a few blogs that state raid can actually pattern the data in ways that make the drives fail prematurely. Do you have any opinions of using LVM or ZFS to combine multiple SSDs into larger disks? – somecallmemike Dec 22 '11 at 1:53
  • Found this very interesting presentation on MySQL and SSDs. It covers pci-e vs sata/sas, raid, network, memory, and cpu as it relates specifically to performance of database operations. – somecallmemike Dec 22 '11 at 2:16

SSD are demon performers and are great for high throughput conditions, as you know. For database servers they do very well housing the transaction logs and also any constantly hit indexes, in addition to being very fast to serve up queries.

TRIM, not the end of the world

TRIM is not that important for databases for a few reasons:

  • Databases tend to be of the 'few, large files' kind of data. Depending on how you view SHRINK, those files may or may not ever get smaller.
  • Transaction-logs are constantly being written to, cleared, and rewritten to.
  • Linux has no on-the-fly TRIM support, as it's all on-demand.
  • FSTrim quiesces I/O while it locates deleted blocks, which can cause temporary latency spikes while it is doing its thing.

The third point bears paying attention to. Since TRIM is on-demand, you'd get your best performance calling fstrim immediately after you clear your transaction logs. However, it'll introduce a momentary period where I/O to your translog volume can't commit. If you're sensitive enough that you're using SSDs for this, such an event may be a deal breaker for you.

Because TRIM is a minor feature, you can ignore the fact that most RAID controllers don't support it yet, and the fact that all but the latest of Linux kernels (newer than 2.6.39 IIRC) can support it in software.

Quality Counts

One of the biggest things to be aware of is that you use enterprise grade SSD drives. These are MLC Flash based disks with high endurance flash cells (~30-40K erase/program cycles), and a generous measure of spare blocks to handle block wear. If you're using SSDs for a reason, you're going to be doing high throughput computing to these, so you want devices that won't crap out after less than a year. These will get you there.

SLC Flash is actually better (100K+ erase/program cycles), but the price differential is what drives people to MLC. Yes, they do make enterprise-grade MLC these days! But if your drives are going to be running flat out for 3 years, SLC will make your devices last longer.

Alignment and RAID

Block alignment is a big deal since it will impact your wearing. This is where RAID cards can get it wrong, and why some RAID card spec-sheets state that they don't work with SSDs even though they'll happily do so if you connect a pair to them. If each block-write incurs a double-write to the flash, then you'll wear faster. Ideally you want the RAID stripe boundaries to fall on the Erase-block size boundaries.

However, SSDs are getting more intelligent about handling wear as each new generation comes out. Pounding the same set of logical clusters with writes gets less and less damaging as the SSDs themselves get smarter.

For software RAID, Linux and supporting utilities have had some SSD support for a few years now. The very latest of kernels has much better support than what's shipping in Enterprise Linux land right now, so be aware. LVM has had support for TRIM (and thus SSD-awareness) for a couple years now. MD-RAID only recently got TRIM. XFS and Btrfs got TRIM support in 2.6.39, the EXTs got stable support by 2.6.36 and experimental support by the late 20's.

Replace in pairs

Because of how wear works, a pair of SSDs in a mirror pair will fail about the same time. So when one goes, replace both as soon as possible.

  • Great response, thanks for the detail! To expound on your point about what SSDs are good at doing in databases (e.g. transaction logs & indexes), do you have any thoughts on what SSDs might be inefficient at doing while hosting a database? I found this presentation which goes into some detail comparing SSD to HDD (as well as fusion-io) in many respects, and finds that SSDs can lack in sequential I/O operations. – somecallmemike Dec 22 '11 at 14:11

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.