Message-ID: <4EAC3DCD.80709@engene.se>
Date: Sat, 29 Oct 2011 19:54:21 +0200
From: Marcus Engene <mengpg2@engene.se>
User-Agent: Mozilla/5.0 (Macintosh; U; Intel Mac OS X 10.6; en-US;
	rv:1.9.1.16) Gecko/20101125 Thunderbird/3.0.11
MIME-Version: 1.0
To: pgsql-performance@postgresql.org
Subject: Re: WAL in RAM
References: <4EAACA17.7010503@engene.se>
	<4EAA87D80200002500042818@gw.wicourts.gov>
In-Reply-To: <4EAA87D80200002500042818@gw.wicourts.gov>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

On 10/28/11 5:45 , Kevin Grittner wrote:
> Marcus Engene<mengpg2@engene.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