Re: WAL in RAM

On 10/28/11 5:45 , Kevin Grittner wrote:
> Marcus Engene<mengpg2(at)engene(dot)se> wrote:
>
>
>> Every now and then I have write peaks which causes annoying delay
>> on my website
>>
>
>
>> Does anyone here have any recommendations here?
>>
>
> For our largest machines we put WAL on a RAID1 drive pair dedicated
> to that task, on its own controller with battery-backed cache
> configured for write-back. It does make a big difference, because
> when a DBA accidentally got this wrong once, we saw the problem you
> describe, and moving WAL to the dedicated drives/controller caused
> the problem to go away.
>
> If problems remain, look for posts by Greg Smith on how to tune
> this. You may want to extend your checkpoint completion target,
> make the background writer more aggressive, reduce shared buffers,
> or tune the OS. But if you can afford to put WAL on a dedicated
> file system something like the above, that would be a better place
> to start, IMO.
>
> -Kevin
>
>

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?

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.

Thanks for your answers so far!

Best regards,
Marcus