Message-ID: <4E448D2A.7050000@ringerc.id.au>
Date: Fri, 12 Aug 2011 10:17:14 +0800
From: Craig Ringer <ringerc@ringerc.id.au>
User-Agent: Mozilla/5.0 (Windows NT 6.1; WOW64;
	rv:5.0) Gecko/20110624 Thunderbird/5.0
MIME-Version: 1.0
To: Waldo Nell <pwnell@telkomsa.net>
CC: pgsql-performance@postgresql.org
Subject: Re: Recommended optimisations slows down PostgreSQL 8.4
References: <C4FDB85D-3D6D-4C6E-A972-4176E49A6C43@telkomsa.net>
In-Reply-To: <C4FDB85D-3D6D-4C6E-A972-4176E49A6C43@telkomsa.net>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

On 12/08/2011 8:03 AM, Waldo Nell wrote:
> I have PostgreSQL 8.4.8 on Ubuntu Linux x64.  Server is a Core i7 950 with 6GB of RAM.  2GB of RAM us used by Java, some small amount by the kernel / services and the rest is available to PostgreSQL.  Hard drive is a single 7200 RPM SATA 1TB Caviar Black HDD.  No other applications / processes are running when I perform my tests.
>
> I have an application that performs about 80% reads and 20% writes for a specific billrun.   It takes about 60 minutes to complete, and I can have it perform precisely the same queries repeatedly.  I have consistently showed that when shared_buffers = 24MB (the default), and wal_buffers = 64kB, the system completes the process in 50 minutes.  When I bump shared_buffers to 1500MB, the system slows down and takes 60 minutes to complete the same process.  Changing that back to 24MB, but then changing wal_buffers to 16MB has the same impact - performance drops from 50 minutes to about 61 minutes.  Changing those two parameters back to the defaults returns the time to 50 minutes.
>
> fsync = off for these tests - not sure if it is relevant.

It certainly is if you're doing any writes as part of your testing.

fsync=off is pretty much the same as saying "eat my data whenever you 
feel like it, I really don't care about it". It's a good option for 
bulk-loading data into new empty clusters and for systems that rely on 
replication plus a tolerance for data loss. For anything else it's a 
really, really bad idea.

> Please explain why the system is slower with the recommended values for these two settings?  The DB is about 74GB, the largest table has 180 million rows.

Without any performance measurements, EXPLAIN ANALYZE results, etc it's 
very hard to know. You'd need to collect vmstat and iostat output among 
other things to be able to get any idea.

--
Craig Ringer