Re: WAL in RAM - Mailing list pgsql-performance

On Sat, Oct 29, 2011 at 11:54 AM, Marcus Engene <mengpg2@engene.se> wrote:

> The problem I have with battery backed raid controllers is the battery part.
> They're simply not reliable and requires testing etc which I as a rather
> insignificant customer at a generic datacenter cannot have done properly. I
> have however found this thing which I find primising:
> http://news.cnet.com/8301-21546_3-10273658-10253464.html
> An Adaptec 5z-controller which has a supercap and flushes to a SSD drive on
> mishap. Perhaps that's the answer to everything?

In over 10 years of using hardware RAID controllers with battery
backup on many many machines, I have had exactly zero data loss due to
a failed battery backup.  Of course proper monitoring is important, to
make sure the batteries aren't old and dead, but every single BBU RAID
controller I have used automatically switched from write back to write
through when they detected a bad battery pack.

Proper testing is essential whether it's BBU Caching or using an SSD,
and failure to do so is inconceivable if your data is at all
important.  Given the current high failure rate of SSDs due to
firmware issues (and it's not just the intel drives experiencing such
failures) I'm much more confident in Areca, 3Ware, and LSI BBU RAID
controllers right now than I am in SSDs.

> As per others suggestions I don't feel encouraged to put WAL on SSD from
> finding several texts by Greg Smith and others warning about this. I do have
> 2x OCI Sandforce 1500 drives (with supercap) for some burst load tables.
>
> The reason I started to think about putting WAL on a RAM drive to begin with
> was that performance figures for unlogged tables looked very promising
> indeed. And the test were of the sort that's occupying my bandwidth;
> accumulating statistical writes.
>
> The present pg9 computer is a Pg 9.0.4, Debian Squeeze, 2xXeon, 72GB,
> software 4xRAID6(sorry) + 2xSSD. It's OLTP website with 10M products and
> SOLR for FTS. During peak it's using ~3-4% CPU, and it's 99.9% reads or
> thereabouts. It's the peaks we want to take down. RAID6 or not, with a
> spindle as bottleneck there is just a certain max# of writes/s.

First things first, get off RAID-6.  A 4 drive RAID-6 gives no more
storage than a 4 drive RAID-10, and is painfully slow by comparison.
Looking at SSDs for WAL is putting the cart about 1,000 miles ahead of
the horse at this point.  You'd be much better off migrating to a
single SSD for everything than running on a 4 disk RAID-6.