In company where I work we started using SSD for our internal 3 GB MySQL database

Difference in perfomance is HUGE, which is great.

What I am worried about is lifetime of SSD

Writes to DB are perfomed 24hrs/7days, very few reads.

Should I be worried about the lifespan of SSD?

  • Database (binaries) size is 3 GB, MySQL, InnoDB tables
  • Hard drive(s) size is 250 GB (RAID 1)
  • We do about around 100 UPDATE's / INSERT's per minute 24h/7
  • We do about around 10-20 row UPDATE's / INSERT's per minute 24h/7

Update: (some more data)

  • SSD's in use: SAMSUNG 250GB 840 Evo SATA III
  • Software raid (mdadm)
  • System: CentOS 6.4
  • MySQL version: 5.4

Update 2:

  • no TRUNCATE queries are perfomed
  • daily stats: lot of UPDATE's (>300k), < 50 DELETE'S, a quite few INSERT's as database is growing ~7-10 MB/day
  • More information is needed. Which SSD(s) are in use? Make/model/controller/server. – ewwhite Sep 29 '13 at 0:15
  • @ewwhite please check my update – Peter Sep 29 '13 at 0:27
  • Welcome to serverfault. Please update question with filesystem type. Do you have DELETE's/DROP's/TRUNCATE's? Are you filling DB all the time? i.e. data is replaced or added? How about disk space usage grow stats (MB/day)? – GioMac Sep 29 '13 at 11:20
  • @GioMac: please see update 2 – Peter Sep 29 '13 at 13:17

10 MB/day = 4 GB/year. If formatted with ext4 and TRIM is enabled, no other data is saved on SSD (especially swap), then it will need ca. 200GB/4GB*2 = 100 years for one (!) complete RW cycle, SSD can withstand thousands.

Follow general recommendations, enable TRIM and no problem: https://wiki.archlinux.org/index.php/Solid_State_Drives

In your case problem might be in RAID. LVM in Centos 6.4 supports TRIM with issue_discards option through /etc/lvm/lvm.conf. mdraid - doesnt (see RHEL Solid-State Disk Deployment Guideline)

Globally, honestly, I've never heard about dead SSD's caused by internal reallocation reserve exhaustion, I've only read it once when Linus Torvald's SSD died (https://plus.google.com/+LinusTorvalds/posts/V81f6d7QK9j). I use some old (maybe first generation) models as block-cache with HW RAID on the servers and flush rate is MUCH higher, running years.

  • +1. The really funny thing is that low grade school math would show how low the writes per day really are. Yes, it looks impressive, but it istill is SMALL. – TomTom Sep 29 '13 at 13:42
  • Thanks. Regarding TRIM - I found this script for RAID1 SSD TRIM – Peter Sep 29 '13 at 17:42
  • 1
    You can try that, but data won't be discarded unless cron job is run and this operation isn't effective. You can create RAID1 LVM device instead of mdadm RAID. – GioMac Sep 29 '13 at 19:15
  • @TomTom yeah, still laughing about that. My friend had same question today, he wants to buy SSD for desktop, but was unsure about lifetime. So, if he will rewrite entire disk once a day, SSD will last at least 3 years, it's more than enough :) – GioMac Sep 30 '13 at 0:18
  • And that is bad quality. If you get a better drive you get 3-5 rewrites per day tehse days - and that includes the overcommit area. SSD reliability is really not that bad, even in a database scenario. Not if you consider the quality gain on top - damn cheap compared to getting the same IO from SAS drives. – TomTom Sep 30 '13 at 4:42

For anyone else wondering, yes you will experience a terrible loss in performance with 840 EVO drives if TRIM isn't supported. We had servers in production with writes 24/7 and performance dropped considerably to about 10% of when the drive was new.

I was very unhappy with our hosting provider for putting poor consumer grade drives in production servers. Good SSDs are the Intel S3500/S3700.

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.