4

We have a LAMP box with 2x mirrored 1 TB WD Black Caviar disks running the whole OS and MySQL. 8 GB / RAM, 2x quad core CPUs.

We're really taxed on disk I/O, and I've been thinking of suggesting getting a couple SSD drives in there for /var/lib/mysql, and be done with it. I did a little research, and I like the price point of the Intel X25-M 160 GB, but I've read conflicting options about SSDs in production.

We are at ~70 GB, mostly MyISAM tables (> 95%). We are doing mostly reads during production (8-5 p.m.), mostly writes overnight (12 a.m. - 8 a.m.).

There have been some helpful posts on here before about SSDs in production, but I think the better ones are a bit dated (the best one was in 2008). Is there more up-to-date feedback on whether SSDs are really ready for medium sized businesses? If not, how can I scale our database server a little better?

Community
  • 1
Stephen J. Fuhry
  • 154
  • 1
  • 8

4 Answers4

10

Stephen, you need to dig deeper first.

  • Would the entire 'hot' (frequently used) subset of the database fit in RAM if you just upgraded RAM to something larger, like 32 or 64GB?
  • Have you checked that your database has the right indexes in place, have you done a basic MySQL performance audit?

About consumer gear: Using consumer-grade gear like your WD disks in servers is a strongly debated topic. Personally, I think it's a wrong choice in general. But certainly, do not use consumer-grade SSDs like the Intel X-25M (M stands for mainstream). Enterprise-grade SSDs have radically different durability and write endurance goals from consumer SSDs (better wear leveling, more space overprovisioning).

Typical setup: A typical disk setup for a server like yours might be 4 enterprise SAS disks, in RAID10, using a proper RAID controller, with a controller RAM cache buffering all writes, and a battery backup unit for the cache. Such gear isn't exactly cheap, but it is a proven choice.

SSDs do have advantages, and can be substantially faster than a couple of conventional disks in RAID 10 (especially on heavy random reads, assuming the hot dataset doesn't fit in RAM). The Percona team blogs about SSDs and real-life performance with MySQL here.

Anyone have any more up-to-date feedback on whether SSDs are really ready for medium sized businesses?

They are, but IMHO only the enterprise-grade SSDs, and preferably a series of SSDs that have been in production for some time to fix bugs. Good choices right now are the Intel X25-E (Extreme) series, and in a 4-6 months (when they're more mature) the Sandforce 25xx series drives with the enterprise feature set.

If not, how can I scale our db server a little better?

Perhaps you have already done this, but if not: my first suggestion would be to find a MySQL database administrator, and have him do a performance audit of your system. You could very well discover that adding more disk-I/O isn't a cost effective solution for your case.

  • 2
    Is adding an SSD or 2 not cheaper the hiring a MySQL database administrator to do a performance audit? – Petah Oct 24 '12 at 22:25
3

I've been running raid arrays of those exact drives, the 160GB intel G2's for almost a year now. Its a cluster of 12 servers doing about 3500 queries per second right now, but thats with a lot of spare capacity, I've had it down at 6 servers and everything was fine. If you do the research and the math it basically boils down to "don't use them for five years or longer", which for me I rounded down and plan to replace them in another year. Considering the insane amount of hardware and developer time they saved I could replace them every quarter and it would still be worth it.

cagenut
  • 4,808
  • 2
  • 23
  • 27
  • 1
    +1 for real-world deployment stories – Daniel Lawson Apr 02 '11 at 09:08
  • 1
    +1 for the "hardware and developer time .. saved". But, I respectfully mean that "do .. the math ... use for 5 years" is on the high end for servers. 5+ years of endurance is often quoted for *desktop* workloads, but for a *multiuser server workload* that sounds high unless that server is unusually light on writes and/or much spare capacity is given: http://www.anandtech.com/show/4159/ocz-vertex-3-pro-preview-the-first-sf2500-ssd/2 –  Apr 02 '11 at 13:22
  • sure, take the numbers in your link. for the 25nm he comes to 108K days at 7GB/day. So lets 10x that and figure i'm writing 70GB/day (the db's only about 150GB and it took 5 years to grow to that). Then in turn that means I'm looking at 10,800 days. What the hell, lets fudge that out a whole nother order of magnitude just for saftey's sake and say 1080 days. Thats two weeks shy of 3 years. So I planned for 2. Plus, like that link says, they're supposed to just go read-only when they wear out, not actually lose data. – cagenut Apr 03 '11 at 04:11
0

For anything serious you should go enterprise grade SSDs like FusionIO and the gear STEC sells . They have data on when to run maintenance (TRIM etc.) and provide support in case something goes wrong. I have even seen STEC SSDs beeing yanked by HBAs. My guess is that the real problem here is that most vendors expose SSDs not as flash but as a block device and the controller firmwares out there don't really know how to handle disks and their disk firmware interface. Only experience will tell. The usual thing about backups and RAID also applies to SSDs.

But since you are using MyISAM as a storage backend reliability and consistency might not be your primary concern so just buy them and see what happens.

pfo
  • 5,630
  • 23
  • 36
0

Consider upgrading to Percona MySQL which is specifically tuned for SSD's I/O capabilities. Just switching to SSD drives will give some improvements but MySQL doesn't take full advantage of the capabilities of SSDs http://www.percona.com/software/percona-server/for-ssd/

See their benchmarks stats at http://www.percona.com/docs/wiki/benchmark:ssd:start

sreimer
  • 2,168
  • 14
  • 17