4

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
Peter
  • 167
  • 1
  • 3
  • 16

2 Answers2

4

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.

GioMac
  • 4,544
  • 4
  • 27
  • 41
  • +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](http://fossies.org/linux/misc/hdparm-9.43.tar.gz:a/hdparm-9.43/wiper/contrib/raid1ext4trim.sh-1.5) 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 00: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 04:42
  • What about MySQL and discard/trim? Does it do that? MySQL files only grow, so file system trim is useless. – Halfgaar Mar 04 '14 at 15:06
  • It's not about MySQL, it's about FS and block layers. MySQL files (I suppose, it's about InnoDB) grow when they grow. It doesn't matter they grow on SSD or HDD. It matters where they grow on SSD and it's effective, when they grow at the oldest discarded region. We talk about data "replace", it's same as delete+update, file blocks will be rewritten to new region. It won't be same 10MB/day, but still, even in case of full 3GB rewrite - it will last for year or more before one complete rewrite. Still thousands of years are left :D – GioMac Mar 25 '15 at 03:57
0

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.

Marcus
  • 400
  • 2
  • 5
  • 16