Re: shared_buffers advice

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