Switch Your Databases To Flash Storage

http://highscalability.com/blog/2012/12/10/switch-your-databases-to-flash-storage-now-or-youre-doing-it.html

by jpmc • 14 years ago

187 76 14 years ago

jiggy2011• 14 years ago

"Switch Your Databases To Flash Storage. Now. Or You're Doing It Wrong."

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.

hnriot2• 14 years ago

Is anyone ever actually happy with database performance? I have never met a customer that wouldn't welcome better performance for so little outlay.

The failure rate of drives shouldn't be a huge concern, data is kept in redundant drives and replacing them is just a matter of routine maintenance. The data is typically worth considerably more than the drives it sits on, but several orders of magnitude.

seiji• 14 years ago

Is anyone ever actually happy with database performance?

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).

bcoates2• 14 years ago

I love my consumer SSD backed database, but don't get visions of 380,000 IOPS on a real workload quite yet. Like any radical performance increase on just one component it's more likely to just reveal a non-disk latency bottleneck somewhere else in your system.

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.

jdcryans• 14 years ago

The "weird stalls" can be attributed to GC on lower end SSDs:

http://en.wikipedia.org/wiki/Garbage_collection_(SSD)#Garbag...

baruch5• 14 years ago

There are several background operations that happen on ssds, low or high end doesn't matter they all need them. I've seen quite a few supposedly high-end ssds that show abysmal behavior with wide variations in performance across time.

SSD qualification is a tedious job.

It's not even just about performance of the SSD the other points to care about are non-trivial if you intend to use lots of SSDs for important tasks. I collected some questions to consider at http://disksurvey.org/blog/2012/11/26/considerations-when-ch...

zokier• 14 years ago

Anandtechs recent article on SSD IOPS consistency touches on this topic, and has some links for further info:

http://www.anandtech.com/show/6489/playing-with-op

stephenpiment• 14 years ago

Clearly, there are different usage regimes where different solutions will make sense. Nonetheless, there's a really strong case to be made that SSDs have entered a sweet spot in terms of price/performance for databases, and this trend is only accelerating. Here's one discussion of the rationale: http://www.foundationdb.com/#SSDs.

paulsutter• 14 years ago

My favorite quote:

"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.

pdog• 14 years ago

Sounds great in theory, but in practice you'll be having that conversation about your database schema anyway.

IheartApplesDix• 14 years ago

Nah, just throw it all into a NoSQL store and let the developers figure it out.

staunch• 14 years ago

Shameless plug alert. At Uptano[1], this is one of the neatest things we've seen with our very inexpensive SSD machines. It's amazing what you can do with 8GB RAM + 100 GB RAID1 SSD. It's probably the best price:performance DB you can run, and is sufficient for ~95% of projects.

1. https://uptano.com

lasonrisa• 14 years ago

Hey, nice relooking of your site. I prefer this colour palette better.

buro9• 14 years ago

I would love if cloud providers offered SSD options for their full range of boxes.

For example, to be able to get a Linode at only a fraction more of the cost (say, a 10% premium) with the disk being SSD (and obviously reduced capacity compared to HDD).

I have seen the current offerings but found them to either be too costly (AWS, only one of the the largest instances), or too onerous (ssdnodes.com whose base products aren't aligned with the costs elsewhere, and to move all of your hosts to be near your SSD powered database is a big task when I only seek a little task).

I was even considering co-locating as the most cost-effective way to get SSDs when providers still massively overprice them. It all feels a bit like the RAM scam a decade ago when they'd charge you near the cost of the RAM every 2 months. Again though... co-location fell into the onerous class of actions.

Right now, pragmatically I stay with HDD and Linode.

But Linode should look at my $500 per month account and be well aware that as soon as I see a competitor offer SSD nodes at a cost-competitive point that offsets the burden to move... I'll be gone.

kyrra2• 14 years ago

I hosted at vps.net for a while and they were offering Fusion IO drives as an addon. I can't easily find information on their site about it anymore, but I know it was an option at one point.

(Their management change a couple years ago, made a bunch of changes that pissed me off, so I left and moved to Linode).

EDIT: vps.net charges $25/month for 1GB of storage on FusionIO drives, and they sell in 2GB increments. 2GB is $50/month. 36GB is $900/month

otakucode• 14 years ago

Since NAND flash is being price fixed, and likely will be for several more years to come (we're just now starting to see LCD prices drop to reasonable levels after years and years of price fixing, I expect it'll take a similar amount of time for the NAND fixing to get busted and the market to respond), I don't think a 10% premium will be at all possible for a very long time. NAND storage SHOULD be significantly cheaper than rotational storage since it is cheaper to produce, requires fewer exotic materials, has a far wider market, etc. And eventually it will be. But for now, there is a huge premium on NAND storage. I am sure Amazon, EMC, and the others have done the math and simply don't think there is a significant market of people willing to pay for such a service, at least not at the steep rates they would have to charge to meet their growth projections.

wmf• 14 years ago

Note he didn't say 10% premium for the same capacity. It's possible for cloud providers to replace $50 hard disks with $64 SSDs today.

olgeni• 14 years ago

> I would love if cloud providers offered SSD options for their full range of boxes.

Both CloudSigma and ElasticHosts have SSD options.

On CloudSigma you can even grab a small SSD to use as a L2ARC or ZFS log device :)

jacques_chester2• 14 years ago

In Australia, OrionVM offer fully SSD-backed VMs.

It's cost-effective because in Australia, the major determinant of monthly hosting bills is our ludicrously overpriced bandwidth fees.

leif• 14 years ago

"Use large block writes and small block reads"

Yep. Write amplification is a big deal on SSDs and gets worse due to their internal garbage collection, if you give them a high-entropy write pattern. This is not a problem though, with TokuDB. See our "advantage 3" here: http://www.tokutek.com/2012/09/three-ways-that-fractal-tree-...

bunderbunder4• 14 years ago

Wear patterns and flash are an issue, although rotational drives fail too. There are several answers. When a flash drive fails, you can still read the data. A clustered database and multiple copies of the data, you gain reliability – a server level of RAID. As drives fail, you replace them.

Unlike magnetic disks, SSDs have a tendency to fail at a really predictable rate. So predictably that if you've got two drives of the same model, put them into commission at the same time, and subject them to the same usage patterns, they will probably fail at about the same time. That's a real problem if you're using SSDs in a RAID array, since RAID's increased reliability relies on the assumption that it's very unlikely for two drives to fail at about the same time.

With an SSD, though, once one drive goes there's a decent (perhaps small, but far from negligible) chance that a second drive will go out before you've had a chance to replace the first one. Which makes things complicated, but is much better than the similarly likely scenario that a second SSD fails shortly after you replace the first one. Because then it's possibly happening during the rebuild, and if that happens then it really will bring down the whole RAID array.

That said, if you're careful then that predictability should be a good thing. A good SSD will keep track of wear for you. So all you've got to do is monitor the status of the drives, and replace them before they get too close to their rated lifespan. If you add that extra step you're probably actually improving your RAID's reliability. But if you treat your RAID as if SSDs are just fast HDDs, you're asking for trouble.

venomsnake• 14 years ago

But with current SSD speed/size ratio this vulnerability window can be only a few minutes, also this can be minimized with mixing batches and vendors of drives.

bunderbunder4• 14 years ago

Hopefully. . . though speaking of speed, there's another pitfall to be aware of: At present, very few RAID controllers support the TRIM command. On one that doesn't any SSDs plugged into it will slow down over time, perhaps becoming slower than magnetic disks.

jQueryIsAwesome• 14 years ago

Or just mixing intervals of memory installation; so you install the next SSD at half the life-span of the previous one.

kyrra2• 14 years ago

I bet you could setup your drives to fail in a set pattern. Lets say you had 4 drives in a RAID-10. If they were all fresh, swap out the first 2 drives when they are at 50% wear... then on you could swap them out back and forth as they approach 100% wear.

Since they lifespan of some of the drives (Intel and Samsung I believe) is reported in the SMART data, you could easily do this.

SSD drives with SLC memory (enterprise SSD) have 100,000 P/E cycles, so they should last a while unless you are writing just a massive amount of data. Anandtech had a nice little writeup about SLC vs MLC vs TLC memory a little bit ago:

http://www.anandtech.com/show/6459/samsung-ssd-840-testing-t...

specto• 14 years ago

Most sysadmins watch counters and set their calendars to replace the drives before they fail since it is so predictable.

baruch5• 14 years ago

Assuming this predictability is not a good idea in my experience. SSDs fail in various ways, some may be predictable and some are completely unpredictable. It is also not true that an ssd failure means it simply goes to readonly mode. I've seen plenty of SSDs failing unexpectedly and are no longer readable, returning sense key 0x4 (HARDWARE ERROR) and the only recourse is to ship them out.

The risk of correlated failures is indeed non-trivial in SSDs and plain RAID is riskier, be sure to keep a watchful eye on your arrays.

bunderbunder4• 14 years ago

Good point. I was only addressing the predictability that comes from the flash memory simply wearing out. There are plenty of other ways that drives can fail. But I don't think they introduce any new worries for RAID users the way that flash memory wearing out after so many writes does.

ChuckMcM• 14 years ago

We've currently got about 3500 SSDs in production across our clusters. I worry about them deciding to all fail at once, so far they have been sporadic failures (about 1/2 of which leave the drive unusable).

codemac3• 14 years ago

> That said, if you're careful then that predictability should be a good thing.

Yes, it's a very good thing. In a high end SSD storage system, you predict early enough based on a calculation of how many drives there are, and what their current wear is, what type they are (SLC, eMLC, cMLC), etc. Then you phone home and have a drive delivered before the user even sees a disk failure.

With HDD's, the failure rate is so random that the disk completely failing is the signal that get's a replacement drive into the enclosure. S.M.A.R.T-type alert systems have been epic failures (too little info too late). The difference is because of the RAID rebuild having a lower probability of failure (let's set aside mulitple URE's for a second) that you can count on the mttf of the next drive failing being longer than the time it takes to get a drive out there.

However this is not much of a guarantee, so most people crazy over provision their storage.

SSDs let you predict this, thus provision correctly, and choose how to replace the drives to least impact the customer. It's win win to have predictable failure. I don't understand people who say otherwise.

bbulkow3• 14 years ago

It's hard to predict without SMART features to measure the current wear state. Write amplification from the file system, and from the drive itself if you're not using large block writes, means you can't just calculate - you have to measure.

jasonkolb• 14 years ago

I wasn't aware of that... anyone know how that's handled when you're buying an SSD instance from say, Amazon? Do they predict failure and replace the drives before they go bad, or do you have to bake this into your deployment logic somehow?

scoot• 14 years ago

Not disputed that there's a slightly increased chance of concurrent disk failures with SSD, but on what basis is a second failure before rebuild any better than aduring it?

Also, I'm guessing you're referring to RAID5, as RAID6 / RAID DP is immune to double-disk failure, and RAID 10 and 0+1 are more tolerant of it.

cmer• 14 years ago

Just curious, which software do you recommend to monitor SSDs life span/upcoming death in production?

Can something like Munin do this out of the box?-

incredimike• 14 years ago

Munin cannot predict a SSD's lifespan.

moe2• 14 years ago

So all you've got to do is monitor the status of the drives, and replace them before they get too close to their rated lifespan

Sorry, that is terrible advice. Do not do that.

In availability planning two is one and one is zero.

If you love your data you run your databases in pairs. If you really love your data you run them in triplets. This applies no matter what disk technology you're using.

Speculations about failure rate or -prediction don't belong here. Your server can go up in flames at any time for a dozen reasons, the disks being only one of them.

hvidgaard• 14 years ago

Not only that - you make sure that critical components, like hard drives (rotational or SSD, doesn't matter), are from different manufacturers or at least not the same production, or at the very least not put them to use at the same time. Basic design flaws (intentional or not), that result in non functional hardware, tend to hit at the same time - so you'd rather not want to have all 12 drives, and the hard drives of the 3 replicas, fail within the same week.

bunderbunder4• 14 years ago

You're responding as if I had suggested that this is a replacement for all the other practices one should already be doing.

If I had, yes I would agree with you 100%. However, far from suggesting anything remotely like that, I made sure to work in the phrase "add that extra step." It's not a panacea, it's an additional thing that needs to be done to account for one new quirk that a particular technology throws into the mix.

crazygringo• 14 years ago

Anyone have any idea when Amazon will start providing SSD-backed RDS?

oasisbob• 14 years ago

They already do, although it is quite new. The AWS provisioned IOPS layer ("pIOPS") is SSD-backed, and can be used for RDS:

http://aws.amazon.com/rds/#PIOPS

There isn't currently an easy way to migrate to pIOPS from traditional RDS, but the performance is fantastic and works as advertised.

ghshephard• 14 years ago

I'm surprised that the author didn't capture what I consider to be the most important component of HDD/Flash/Memory Balancing - frequency of access.

The rule of thumb that I've heard thrown about is, "If you touch it more than once a day, move to flash. If you touch it more than once an hour, move to memory."

While we can debate where that actual line falls based on both the price and performance of the various media (And, as the price of flash drops, it may be more like, "once every couple days) - it's important to note that frequency of access is critical when determining which media to put your data on.

We have some 50 TB+ Data Sets that are queried weekly for analytics, that don't make a heckuva lot of sense on flash storage. Contra-wise, our core device files are queried multiple times a second, and so we make certain those database servers always have enough memory to keep the dataset in memory cache, even if that means dropping 256 GB onto those database servers for larger customers.

rxin• 14 years ago

Jim Gray wrote a classic paper about this. "The 5 Minute Rule for Trading Memory for Disc Accesses and the 5 Byte Rule for Trading Memory for CPU Time". http://www.hpl.hp.com/techreports/tandem/TR-86.1.pdf

There is an updated version that also talks about SSDs. http://cacm.acm.org/magazines/2009/7/32091-the-five-minute-r...

dschiptsov• 14 years ago

Only that append-only journal (transaction log).

pjungwir2• 14 years ago

The PostgreSQL mailing list is having a conversation right now about using SSDs. This seems like a very important comment for anyone considering them:

    http://archives.postgresql.org/pgsql-general/2012-12/msg00202.php

Basically, you need to make sure that you buy SSDs with a capacitor that allows the drive to flush what it needs in event of abrupt power loss.

EDIT: Looks like the list archives didn't preserve the thread very well, so here is the original question for anyone interested:

    http://archives.postgresql.org/pgsql-general/2012-11/msg00427.php

bbulkow3• 14 years ago

Is this because PostgreSQL's file format will become hopelessly confused and unable to restart if flush doesn't work?

A lot of applications can lose the last 100ms of writes, especially if its rare because of a k-safe cluster design, as long as you don't have a corrupted file format. A good transaction log based system will recover - as the author's should.

hosay123• 14 years ago

It's not only a case of file formats.. a few years ago one of the Linux kernel developers (Theodore Tso IIRC) made a post regarding drive behaviour under power loss, and the results were pretty insane.

For example when a rotating drive fails, you might lose +/- 4kb around the previous sector under write, whereas with particular SSDs, he witnessed 1mb chunks zeroed out every Nmb across the entire drive. That kind of thing, you simply can't work around in software

jacques_chester2• 14 years ago

> A good transaction log based system will recover - as the author's should.

Unless the drive lies. Tells filthy dirty ugly lies.

And lots of them do.

OS: "Did you persist that data I flushed?"

Disk controller (hiding single copy in RAM cache behind its back): "Oh absolutely, bro. It's tight. Solid. You got nothing to worr-"BZZZT

dclusin• 14 years ago

Disk drives with battery backed write caches are a requirement for any system where data corruption is considered a system failure. We use these at my job for things other than just our data bases. It doesn't need to be just SSD's either; traditional platter-based hard drives have this feature as well.

pjungwir2• 14 years ago

Thanks; I wondered why this would be unique to SSDs.

cioc• 14 years ago

Does anyone else find the section "Don’t use someone else’s file system" a bit confusing? It starts off by convincingly saying O_DIRECT shouldn't be used and then goes on to say O_DIRECT works very well.

riobard• 14 years ago

Linus Torvalds said O_DIRECT shouldn't be used because Linux's already implemented page cache and application developers should not bother to re-invent the wheel.

However page caching algorithms are pretty generic and database people think performance could be improved by using customized db caching routines instead of the generic OS one.

There are two ways to bypass OS page cache: 1) directly access the disk as a block device; or 2) use the O_DIRECT flag to disable page cache on a per-file/directory basis.

Direct access to disk as a block device would be ideal from the performance and flexibility point of view, but then you lose all the benefits and tools to manage databases as files. O_DIRECT flag seems to strike a sweet pot and that's what ended up being used most in the real world.

Then no body is really interested in improving the F_ADVISE interface, which is supposed to be a better than O_DIRECT from Linus's perspective. You know, the “worse is better” thing.

I'm not a db or kernel dev, but I've been watching this argument for a while, and so far that's my understanding. Very interesting.

trotsky• 14 years ago

I don't disagree with the conclusions, but don't you have to short stroke those ssds pretty significantly in a high transaction environment to avoid write amplification?

It's too bad longevity worries are keeping them out of the no commitment market.

bbulkow3• 14 years ago

Not really. "short stroking" is called "overprovisioning" with SSDs, and you'll see different effects with different drives. The magic number with most consumer SSDs (the mentioned Intel and Samsung drives) do best with about 20% overprovisioning. The "enterprise class" drives don't require this - they bake in the overprovisioning. The new Intel s3700 works extraordinarily well with no overprovisioning.

knappador• 14 years ago

Those caught in the middle on DB size needs and performance would be well off to take a look at Bcache. http://bcache.evilpiepirate.org/ It's a block write-back cache and seems to perform really nicely. Here's some benchmarks. http://www.accelcloud.com/2012/04/18/linux-flashcache-and-bc...

dutchbrit• 14 years ago

Funny, and it's a no brainer really.. There was a thread about SSD's about 2 years back, regarding good ways to use them. My conclusion was pretty much the same when it came to DB's, yet nobody agreed with me back then and I received 3 downvotes. Odd!!

Good article!

Switch Your Databases To Flash Storage

Related Stories