Re: SSDs with Postgresql? - Mailing list pgsql-general

From Toby Corkindale
Subject Re: SSDs with Postgresql?
Date
Msg-id 1000688582.482.1303828241339.JavaMail.root@dmz03
Whole thread Raw
In response to Re: SSDs with Postgresql?  (Greg Smith <greg@2ndQuadrant.com>)
Responses Re: SSDs with Postgresql?
List pgsql-general
----- Original Message -----
> From: "Greg Smith" <greg@2ndQuadrant.com>
> To: pgsql-general@postgresql.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-diskto 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
customerwith 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.
(Andwhat 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
servicebeyond 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
heavywrite loads in order to risk wearing them out within a matter of years - and there are SMART attributes that
shouldbe 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
postedabout it because it's working fine..? 

-Toby

pgsql-general by date:

Previous
From: Merlin Moncure
Date:
Subject: Re: 10 missing features
Next
From: Joel Reymont
Date:
Subject: optimizing a cpu-heavy query