Mysql – Database (MySQL) and SSD lifetime – “lot” of writes to DB

databaseMySQLssdstorage

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

Best Answer

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.

Related Topic