Re: SSDs with Postgresql?

----- Original Message -----
> From: "Greg Smith" <greg(at)2ndQuadrant(dot)com>
> To: pgsql-general(at)postgresql(dot)org
> Sent: Friday, 22 April, 2011 12:49:28 AM
> Subject: Re: [GENERAL] SSDs with Postgresql?
> On 04/20/2011 01:50 AM, Toby Corkindale wrote:
> > Also, the number of erase cycles you can get, over the whole disk,
> > is
> > quite large on modern disks!
> >
> > So large that you'll probably go decades before you wear the disk
> > out,
> > even with continual writes.
> >
> > Don't buy into the SSD FUD myths..
>
> There is no FUD being spread here. Particularly given the PostgreSQL
> WAL write pattern, it's not impossible to wear out a SSD placed there
> in
> a small number of years. A system with a trivial but not completely
> idle workload will generate one 16MB WAL segment every 5 minutes,
> which
> works out to 4.5GB/day of writes. That's the baseline--the reality is
> much, much higher than that on most systems. The fact that every row
> update can temporarily use more than 8K means that actual write
> throughput on the WAL can be shockingly large. The smallest customer I
> work with regularly has a 50GB database, yet they write 20GB of WAL
> every day. You can imagine how much WAL is generated daily on systems
> with terabyte databases.

The larger the database, the larger number of disks the DB will be spread over, thus keeping the amount of data written per-disk to a manageable amount. (Also, larger SSDs seem to have higher write-limits too)

I see Intel is/was claiming their SLC SSDs had a *minimum* lifetime of 2PB in writes for their 64GB disks; for your customer with a 50GB db and 20GB/day of WAL, that would work out at a minimum lifetime of a million days, or about 273 years!
The cheaper "consumer grade" MLC drives should still last minimum 5 years at 20GB/day according to their literature. (And what I found was fairly out of date)
That doesn't seem too bad to me - I don't think I've worked anywhere that keeps their traditional spinning disks in service beyond 5 years either.

> As for what this translates into in the real world, go read
> http://archives.postgresql.org/message-id/BANLkTi=GsyBfq+ApWPR_qCA7AN+NqT=zww@mail.gmail.com
> as one worked out sample. Anyone deploying PostgreSQL onto MLC can't
> necessarily ignore this issue.

Agreed, it shouldn't be ignored, but it seems like you have to have an early-generation SSD and combine that with very heavy write loads in order to risk wearing them out within a matter of years - and there are SMART attributes that should be able to indicate when the drive has used a lot of its life-span as an early-warning signal.
You've linked to one anecdotal failure, but surely there are many more people using SSDs successfully, who just haven't posted about it because it's working fine..?

-Toby