Thoughts on SSDs in Production

We’ve had our SSDs in production for more than 72 hours now. We’ve had them in a slave role for nearly a week but they’ve now replaced existing hardware including the master.

The drives are FAST. In our production roles they’re reading at about 45MB/s and writing to disk at about 15MB/s and using only about 22% of disk utilization.

Not too bad.

We also have about 70GB free on these drives so that leaves plenty of room to grow.

There was small problem that I didn’t anticipate.

When we were running our entire disks out of memory we would only use one or two indexes per column. We had a set of reporting tasks which ran some queries once every 5 minutes.

The columns these queries were using didn’t have any indexes so InnoDB CPU would spike for a moment and continue.

Modern machines have memory bandwidth of about 15GB/s so these queries were mostly CPU bound but completed in a few seconds.

When we switched over to SSDs all of a sudden these queries needed to perform full table scans and were reading at about 100MB/s for two minutes at a time.

Fortunately, an ALTER TABLE later and a few more indexes fixed the problem.

We dropped the indexes when we were running out of memory because the queries could be resolved so quickly. Now that they were on disk again we had to revert to the olde school way of doing things.

  1. We ran into the same problem. For us, it’s too much of a pain to maintain different copies of DBs with different indexes, some optimized for memory, some optimized for disk access (especially when some tables are 60 million rows and 15GB in size). Since we are still unable to get SSDs at all of our hosting providers, we have dropped the SSDs for now and gone back to RAM.

  2. Yup…. it’s an interesting problem. For production queries it’s fine.

    Though I’m going to stick with this config…

    We’re going to have 1/2 of the cluster split out in a large storage system anyway.

    We we’ll have one section of the cluster that’s in the 5-15TB range on HDDs with RAID controllers and the other that’s in the .5-2TB range that’s all SSD.

%d bloggers like this: