Skip site navigation (1) Skip section navigation (2)

Re: WAL in RAM

From: Marcus Engene <mengpg2(at)engene(dot)se>
To: pgsql-performance(at)postgresql(dot)org
Subject: Re: WAL in RAM
Date: 2011-10-29 17:54:21
Message-ID: 4EAC3DCD.80709@engene.se (view raw or flat)
Thread:
Lists: pgsql-performance
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


In response to

Responses

pgsql-performance by date

Next:From: Scott MarloweDate: 2011-10-29 20:11:42
Subject: Re: WAL in RAM
Previous:From: Mark StosbergDate: 2011-10-29 14:45:22
Subject: Re: application of KNN code to US zipcode searches?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group