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