Switch Your Databases To Flash Storage

Unless you know, you're storing a lot of stuff and are quite happy with your current level of performance and don't want to shell out a load on new hardware that will fail quicker.

It depends on your access patterns and how large your active data set grows. Just because your entire DB is 5 TB doesn't mean anything. You could be running a forum where only the most recent 2 GB of posts are read by humans, most people are reading and not contributing, and the rest is trawled through by indexing bots.

I'm perfectly happy with all DB performance when the write load is reasonable, indexes are doing the right thing, and the working data fits into memory (which these days can be multi hundred GBs -- just pray to the gods of uptime you don't have to failover to a cold secondary server).

Be aware that the performance characteristics of flash are very unlike spinning disks, and vary widely between models. You will see things like weird stalls, wide latency variance, and write performance being all over the place during sustained operations and depending on disk fullness. I chose Intel 520s because they performed better on MySqlPerformanceBlog benchmarks than the then-current Samsung offering [1] and because of OCZ's awful rep [2]. I hit about 5K write IOPS spread across two SSDs before my load becomes CPU-bound, which is nowhere near benchmark numbers but pretty sweet for a sub-$1k disk investment.

It's also my understanding that non-server flash drives like recommended by the article do not obey fsync and are suspect from a ACID standpoint. RAID mirroring does not fix this--if integrity across sudden power loss is critical you might not be able to use these at all and will have to find a more expensive server SSD.

[1] http://www.mysqlperformanceblog.com/2012/04/25/testing-samsu...

[2] http://www.behardware.com/articles/881-7/components-returns-...

p.s. the RAM benefits the article mentions are real and potentially huge. My query and insert performance has gone from having heavy RAM scalability issues to it hardly mattering at all. This is all on MariaDB on a non-virtualized server; I'm looking forward to better SSD-tuned databases in the future doing even better.

"Flash is 10x more expensive than rotational disk. However, you’ll make up the few thousand dollars you’re spending simply by saving the cost of the meetings to discuss the schema optimizations you’ll need to try to keep your database together."

Lots of great technical details presented in a commonsense style, well worth a read.

Data for 1M users would be ~40GB, of which only 8GB of that would be relevant at any single point in time. Well within the realms of in memory caching.

DB writes (our writes are batchy based on external events that we're not in control of) would be our biggest bottleneck but we offset them with MEMORY based mysql tables to hold the 'in progress' data and then update the disk based tables for all users in off-peak times.

We can speed up DB writes with more spindles, and then by an order of magnitude by moving to SSD(s). A single DB server at ~$5000 to handle the data for 10M users and, if necessary, a number of mysql slave servers. Subsequent scaling is just sharding.

Compared to the serving that data via HTTP[s] the DB side of our application is easy.