http://techreport.com/review/27436/the-ssd-endurance-experim...
It rather depends upon the database, no? A 512GB Samsung 850 Pro would last 29 years with 100GB of writes a day in a horrendous, worst case 3x write amplification scenario. Very, very few databases write more than 100GB of data a day, and most are magnitudes less.
-Matt
Nobody gives a shit about a mere 100GB in physical writes to a storage subsystem in a day. A single consumer 512GB crucial can handle a rate like that for almost 30 years.
Write amplification effects are not to be underestimated, but fortunately there are only a very few situations where it's an issue. And as I said, the situations can be largely mitigated by database writers getting off their butts and fixing their backends to push the complexity to the indexes and away from trying to optimize the linear layout of the database by seek-writing into it.
-Matt
How is that relevant to your wrong claim about database 128-byte writes? You word that as if you're correcting me.
I don't even know what point you're trying to make anymore, but you're trying to buttress your argument with what can best be described as diversions.
Most people write far less than they think. Databases aren't particularly magical, any more than the web server log file that is written to a line at a time. Yes, people "give a shit" about a "mere" 100GB of writes, because the vast majority of real projects, including at major corporations, write far less than that per day. So are we just talking about dick measuring now?
Additionally, the WAL has to be synced to disk every commit (unlike a web server log file), and WAL records can be very small. WAL is of course append-only, so you'd hope that a good SSD with a battery/cap backup would cache the writes and flush on the SSD erase block filling up.
All major databases will only deal in the 8KB increments (or whatever their block size is, whether larger or smaller, but never as small as originally claimed) though. They don't write less. Indeed, it's worth noting that most (every single major one) database systems actually write to a sequential transaction log (which they do not have to checkpoint every n-bytes), and only on commit do they actually then make a strategy for changing those source pages and extents, unrolling it from the log and checkpointing it, which by default includes coalescing and combining strategies. The idea that databases are randomly writing small bits of data all over the place is simply wrong, but is the entire foundation of almost every comment in this thread.
https://technet.microsoft.com/en-us/library/aa337560%28v=sql...
As one example. Oracle, pgsql, mysql, and others do the exact same thing.
They aren't randomly changing an int here and a bool there.
I worked on a financial system where we wrote just absurd amounts of data a day. We ran it on a FusionIO SLC device (with a mirror backup), and churned the data essentially around the clock. After three years the FusionIO little lifespan hadn't even moved.
tldr; people grossly overestimate the "magical" nature of databases.
If you have an 8kb block and you change 128 bytes of it, the 'actual update' is much smaller than 8kb. Sure, you're reading/writing 8kb to disk, but everything outside of that 128 bytes is basically fat for the purposes of that change. As I said in my previous post, that can absolutely be mitigated by writes being combined through the checkpointing process, and one would hope that a decent SSD could cope easily with combining writes to an appending log.
A database can still be writing data all over the place. A heavily indexed table can cause quite varied write patterns, which can result in a lot of different pages getting touched. Fortunately, the reality is that well-designed DBs and SSDs are fairly capable of dealing with this.
So now that we're in an understanding that we're talking about database writes to IO, the other matter is how it writes it. I've built a lot of systems on a lot of databases, and the write amplification has generally been very low. I've been building and running significant databases on SSDs for about 7 years now, and while everyone else is finally starting to realize that they're wasting their time if they aren't, we still see the sort of extreme niche fearmongering that makes other people clutch onto their magnetic storage (and I heard it the entire time. "OMG but don't databases kill flash???!?!?". No, certain volumes of writes and types of writes do. Only metering will tell you if that applies). Yes, some people do very odd things that can kill storage, but that is extremely rare. It almost certainly doesn't apply to the overwhelming percentage of HN readers.
It's actually "ironic" in that database systems were built to avoid random IO because it was glacially slow on spinning rust storage. They do everything possible to avoid it, and in actual, empirical metrics running databases on flash the write amplification has been very low, and hardly mirrors the claims throughout this thread.
This comment breaks the HN guidelines. We'd appreciate it if you'd read the site rules and follow them:
The SSD's normal internal scans will detect the bits when they start to get flaky and rewrite the block then. It might do some rough tracking to trigger a scan sooner than it would otherwrise, but it is more a firmware issue and not so much a wearout issue.
-Matt
Yeah, the build machine churns a lot. But that work should be primarily done by the FS cache, by buffers. Yes, it's going to write out those small files, but if DragonflyBSD has any kind of respectable kernel, though should be a solid curve, not lots of bursts.
I would love if my old colleague Przemek from Wikia would talk about the SSD wear on our MySQL servers which had about 100k-200k databases per shard.
We wore the _fuck_ out of some SSDs.
You should replace your HDDs with SSDs, though, for a number of reasons, and take the long view, as kryptistk noted the OP is doing. Really compare the cost of SAS 15k drives and Intel 320s or 530s.
But I think in his place, you can take the words of the inimitable Mr. Bergman:
Stop wasting your life. But don't expect a machine that does lots of random IO, like a database, to have 1-2% SSD wear after two years. It might not last two years. If it does, use it more. Aren't you making money with these drives? ;)