Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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 ?
share|improve this question

closed as primarily opinion-based by Paul White, Phil, Mark Storey-Smith, Max Vernon, Marian Feb 6 at 16:10

Many good questions generate some degree of opinion based on expert experience, but answers to this question will tend to be almost entirely based on opinions, rather than facts, references, or specific expertise.If this question can be reworded to fit the rules in the help center, please edit the question.

2 Answers 2

up vote 3 down vote accepted

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

share|improve this answer

Modern SSDs are a lot more reliable than people tend to think in these discussions if you by 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 coudl 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 rives you shoudl be a 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 biased. For my own PoV: these days I put SSDs anywhere were 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).

share|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 Douglas Feb 6 at 15:41
    
Thank you. I have got ~30k inserts and updates per hour. It is not so much, right? –  Bald Feb 6 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 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 at 11:35
    
OK, there are doubtless a few other edge cases but we're agreed in principle :) –  Jack Douglas Feb 7 at 12:24

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