Message-ID: <4B9FEFC4.1020705@2ndquadrant.com>
Date: Tue, 16 Mar 2010 16:53:24 -0400
From: Greg Smith <greg@2ndquadrant.com>
User-Agent: Thunderbird 2.0.0.23 (X11/20090817)
MIME-Version: 1.0
To: Greg Stark <gsstark@mit.edu>
CC: Tom Lane <tgl@sss.pgh.pa.us>, Pierre C <lists@peufeu.com>,
	Dave Crooke <dcrooke@gmail.com>,
	Paul McGarry <paul@paulmcgarry.com>, pgsql-performance@postgresql.org
Subject: Re: shared_buffers advice
References: <a056b1d41003101728h2bfe964cg43bf5ef1aabae14d@mail.gmail.com>
	<ca24673e1003152217q117b0fd3q7d2d75ff20185c8b@mail.gmail.com>
	<4B9F3302.4020900@2ndquadrant.com>
	<op.u9nrbeeteorkce@localhost> <8285.1268749832@sss.pgh.pa.us>
	<407d949e1003160753o6e5faa48rabaa42f7548029b7@mail.gmail.com>
In-Reply-To: <407d949e1003160753o6e5faa48rabaa42f7548029b7@mail.gmail.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Greg Stark wrote:
> On Tue, Mar 16, 2010 at 2:30 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>   
>> "Pierre C" <lists@peufeu.com> writes:
>>     
>>> Does PG issue checkpoint writes in "sorted" order ?
>>>       
>> No.  IIRC, a patch for that was submitted, and rejected because no
>> significant performance improvement could be demonstrated.
> If the OS filesystem buffer cache is really small then that might not
> work so well. It might be worth rerunning those benchmarks on a
> machine with shared buffers taking up all of RAM.
>   

Here's the original patch again:  
http://archives.postgresql.org/message-id/20080415181742.6C97.52131E4D@oss.ntt.co.jp

I was the person who tried to reproduce the suggested 10% pgbench 
speedup on a similar system and couldn't replicate any improvement.  
Never was sure what was going on to show such a difference on the 
reference system used to develop the patch versus mine, since they were 
pretty similar.  Possibly some positive interaction with LVM in the test 
case I didn't have.  Maybe the actual reason sorting helped was 
limitations in the HP P400 controller used there I wasn't running into 
with the Areca card I used.  And the always popular "didn't account 
fully for all pgbench run to run variation"  possibility crossed my mind 
too--that the original observed speedup wasn't caused by the patch but 
by something else.

I did not go out of my way to find test conditions where the patch would 
more likely to help, like the situation you describe where 
shared_buffers was really large relative to the OS cache.  Since the 
patch complicates the checkpoint code and requires some working memory 
to operate, it would have to be a unquestionable win using standard 
practices before it was worth applying.  If it only helps in a situation 
people are unlikely to use in the field, and it net negative for 
everyone else, that's still going to end up on the interesting but 
rejected idea scrapheap at the end of the day.

-- 
Greg Smith  2ndQuadrant US  Baltimore, MD
PostgreSQL Training, Services and Support
greg@2ndQuadrant.com   www.2ndQuadrant.us