job.answiz.com
3 Answers
  • 4
Votes
name
name Punditsdkoslkdosdkoskdo

MySQL, disks I/O and SSD drives

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?

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.

  • 0
Reply Report

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.

  • 0
Reply Report

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.

  • 0
Reply Report