Message-ID: <48079AED.8090906@geizhals.at>
Date: Thu, 17 Apr 2008 20:46:05 +0200
From: Marinos Yannikos <mjy@geizhals.at>
User-Agent: Thunderbird 2.0.0.12 (Windows/20080213)
MIME-Version: 1.0
To: pgsql-performance@postgresql.org
Subject: Re: Background writer underemphasized ...
References: <480619CA.2080904@geizhals.at>
	<Pine.GSO.4.64.0804161524410.27404@westnet.com>
In-Reply-To: <Pine.GSO.4.64.0804161524410.27404@westnet.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Greg Smith schrieb:
> You also didn't mention what disk controller you have, or how much write 
> cache it has (if any).

8.3.1, Controller is 
http://www.infortrend.com/main/2_product/es_a08(12)f-g2422.asp with 2GB 
cache (writeback was enabled).

> That's almost turning the background writer off.  If that's what 
> improved your situation, you might as well as turn it off altogether by 
> setting all the bgwriter_lru_maxpages parameters to be 0.  The 
> combination you describe here, running very infrequently but with 
> lru_maxpages set to its maximum, is a bit odd.

Perhaps the background writer takes too long to find the required number 
of dirty pages among the 16GB shared buffers (currently), which should 
be mostly clean. We could reduce the shared buffers to a more commonly 
used amount (<= 2GB or so) but some of our most frequently used tables 
are in the 8+ GB range and sequential scans are much faster with this 
setting (for ~, ~* etc.).

>> Other options we have tried/used were shared_buffers between 200MB and 
>> 20GB, wal_buffers = 256MB, wal_writer_delay=5000ms ...
> 
> The useful range for wal_buffers tops at around 1MB, so no need to get 
> extreme there.  wal_writer_delay shouldn't matter here unless you turned 
> on asyncronous commit.

I was under the impression that wal_buffers should be kept at/above the 
size of tyical transactions. We do have some large-ish ones that are 
time-critical.

-mjy