Message-ID: <4E455F95.7060805@2ndQuadrant.com>
Date: Fri, 12 Aug 2011 13:15:01 -0400
From: Greg Smith <greg@2ndQuadrant.com>
User-Agent: Mozilla/5.0 (X11; U; Linux x86_64; en-US;
	rv:1.9.1.16) Gecko/20110702 Icedove/3.0.11
MIME-Version: 1.0
To: pgsql-performance@postgresql.org
Subject: Re: Recommended optimisations slows down PostgreSQL 8.4
References: <D9BE4D00-9571-4189-B24A-EC446895EC70@telkomsa.net>
	<4E447F0E.60208@2ndQuadrant.com>
	<DF62C20C-C45A-4443-A917-6547479A6B98@telkomsa.net>
In-Reply-To: <DF62C20C-C45A-4443-A917-6547479A6B98@telkomsa.net>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

On 08/12/2011 12:28 PM, Waldo Nell wrote:
> I guess that means the OS cache is better for this particular use case 
> than the postgresql cache?

There you go.  It's not magic; the database cache has some properties 
that work very well for some workloads.  And for others, you might as 
well let the OS deal with it.  The fact that you have the option of 
adjusting the proportions here is a controversial design point, but it 
does let you tune to your workload in this sort of case.

>  The fsync = off was because the production system runs on a uber expensive SAN system with multipathing over Fibre Channel, it is>  on UPS and backup generators in a secure datacenter, and we have daily backups we can fall back to.


The safer alternative is to turn synchronous_commit off and increase 
wal_writer_delay.  That may not be quite as fast as turning fsync off, 
but the risk level is a lot lower too.  The first time someone 
accidentally unplugs a part of your server, you'll realize that the UPS 
and generators don't really provide very much protection against the 
things that actually happen in a data center.  Having backups is great, 
but needing to restore from them is no fun.

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