4

Recently, I heard that I should not host my MySQL database on an SSD drive because there is a big chance of disk failure and data lost.

Given that 20% of all queries in my database are INSERTs and UPDATEs, here are my questions:

QUESTIONS

  • What are the disadvantages of using MySQL on SSD ?
  • What solutions or alternatives are available for MySQL/SSD usage ?
4

You need to keep in mind that the lifespan of an SSD device is dependent on the number of writes. You may need to implement a hybrid approach which accommodates frequently written files sitting on HDDs and other components sitting in SSDs, thus reducing the need for tons of sequential writes. I first learned about this layout from a FaceBook Engineer's blog.

Rather than go into a long discussion on this, I would like to recommend three old posts where I discuss this for MySQL(InnoDB) and PostgreSQL:

You should consider this since sequential and random reads have the same performance on SSD IMHO

| improve this answer | |
5

Modern SSDs are a lot more reliable than people tend to think in these discussions if you buy decent drives. If drive failure is a concern then RAID the drives as you would with spinning metal based drives.

One concern with SSDs is write lifetime, but for all but pathological cases if you have good drives the reliability shouldn't be much (if any) worse than traditional drives. The other issue is random write performance which can be a problem on some drives under heavy write loads (this is mitigated somewhat with drives that have a larger "over allocation" factor, and you'll not find it slower than spinning metal manages). Neither of these issues is going to be significant unless you have write-heavy loads.

"20% of operations are inserts or updates" isn't particularly meaningful as they could be affecting one row each or many millions. From a performance PoV the best thing to do is test your app and run realistic benchmarks for it. From a reliability PoV with good drives you should be as safe as with spinning metal, though of course use RAID1 (or 10) and backups to cover all the bases as you would normally.

Any recommendation more specific than that is going to be very opinion rather than fact based. For my own PoV: these days I put SSDs anywhere where I can afford the larger $/Gb and cope with the smaller individual device sizes, but I don't trust a single SSD any more than a single spinning-metal drive so anything important gets both RAIDed and (more importantly) regularly backed up and those backups regularly tested (a step that too often gets skipped).

| improve this answer | |
  • 4
    "If drive failure is a concern then RAID the drives as you would with spinning metal based drives." Is drive failure ever not a concern? – Jack says try topanswers.xyz Feb 6 '14 at 15:41
  • Thank you. I have got ~30k inserts and updates per hour. It is not so much, right? – Bald Feb 6 '14 at 17:52
  • @JackDouglas: re. "Is drive failure ever not a concern?" - well that was a rhetorical question really, though if using master<->master replication with fail-over DR then you might not care about individual drives and instead bring up a new node when one dies due to disk failure. Unlikely I know, but other redundancy models beyond drive level RAID do exist. – David Spillett Feb 7 '14 at 11:31
  • @Bald: that depends on what those inserts/updates are and how they are spread. If you mean ~30k row changes spread evenly over the hour then that is less then 9 per second, nothing is going to break a sweat over that. If most of the updates are bunched in a few seconds and/or you aren't talking about individual rows (so any one of those 30,000 actions could itself be affecting thousands of rows) than that might be a different matter. – David Spillett Feb 7 '14 at 11:35
  • OK, there are doubtless a few other edge cases but we're agreed in principle :) – Jack says try topanswers.xyz Feb 7 '14 at 12:24

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