MySQL, disk 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?

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.

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.