Received: from maia.hub.org (maia-2.hub.org [200.46.204.251])
	by mail.postgresql.org (Postfix) with ESMTP id BB030645B0D
	for <pgsql-admin-postgresql.org@mail.postgresql.org>;
	Sat, 12 Nov 2011 14:57:46 -0400 (AST)
Received: from mail.postgresql.org ([200.46.204.86])
	by maia.hub.org (mx1.hub.org [200.46.204.251]) (amavisd-maia,
	port 10024) with ESMTP id 39816-09 for
	<pgsql-admin-postgresql.org@mail.postgresql.org>;
	Sat, 12 Nov 2011 18:57:40 +0000 (UTC)
X-Greylist: from auto-whitelisted by SQLgrey-1.8.0-rc2
Received: from moutng.kundenserver.de (moutng.kundenserver.de
	[212.227.126.187])
	by mail.postgresql.org (Postfix) with ESMTP id E9D0B645B0C
	for <pgsql-admin@postgresql.org>; Sat, 12 Nov 2011 14:51:07 -0400 (AST)
Received: from [192.168.1.6] (mail.highperformancepostgresql.com
	[71.179.240.8])
	by mrelayeu.kundenserver.de (node=mreu2) with ESMTP (Nemesis)
	id 0LrGGm-1QvRaQ13rI-013ftZ; Sat, 12 Nov 2011 19:51:06 +0100
Message-ID: <4EBEC019.4040903@2ndQuadrant.com>
Date: Sat, 12 Nov 2011 13:51:05 -0500
From: Greg Smith <greg@2ndQuadrant.com>
User-Agent: Mozilla/5.0 (X11; U; Linux x86_64; en-US;
	rv:1.9.1.16) Gecko/20111110 Icedove/3.0.11
MIME-Version: 1.0
To: pgsql-admin@postgresql.org
Subject: Re: background writer being lazy?
References: <4EAF2F20.7080205@consistentstate.com>
In-Reply-To: <4EAF2F20.7080205@consistentstate.com>
Content-Type: multipart/alternative;
	boundary="------------010207020408040205030607"
X-Provags-ID: V02:K0:fW3uhPy0lk8RYDRYi/TQo2fERZLe+eLSpnrNtQ4mNfZ
	PR5lCzw/0dK9LPBA4uKVew1ch24PQWdCo1qtTRw5BpViFFKVqv
	htIBniQDRuu40ZcJWc4qF6+wqLmQOMYdFonDlcMAjqTCwkrMwi
	gaXBz/q3sMgJIe71qJVCd/vvnxDoptIDIIH2BTaqBHGQlT0NZb
	5kNSTTpmvIIeZOFF/IbVa2GRhgoi6mpD2EhdQRmh0l2s7UoWra
	6rd78VwC3JZt0I+QIHUbQNUHHq+Tr4SVGcjYw56KOpe+laYIss
	6WN7M8CpMPbmv/MfP6A1+9HSar1oYTC0JI841g6cD3vJAV47uy
	OFWIqV2REmeoKuo/mrcs=
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=-1.899 tagged_above=-10 required=5
	tests=BAYES_00=-1.9, HTML_MESSAGE=0.001, RCVD_IN_DNSWL_NONE=-0.0001
X-Spam-Level: 
X-Archive-Number: 201111/62
X-Sequence-Number: 38656

This is a multi-part message in MIME format.
--------------010207020408040205030607
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

On 10/31/2011 07:28 PM, Brian Fehrle wrote:
> The main thing I am currently seeing is that there are 300X or more 
> buffers written by checkpoints rather than background writer.
>

Writing buffers at checkpoint time is more efficient than having the 
background writer handle them.  I think your efforts to space 
checkpoints out may have backfired a bit on you.  You're letting 40 
minutes of dirty buffers accumulate before they're written out.  Putting 
checkpoint_timeout closer to its default of 5 minutes again may reduce 
the spikes you're seeing.

The changes you've made to the background writer configuration are also 
counterproductive, given that it's not really going to trigger anyway.  
I would only recommend decreasing bgwriter_delay or increasing 
bgwriter_lru_maxpages or you see the total_clean_max_written value get 
incremented regularly.  If that's not happening, making the background 
writer run more often and try to do more work just adds overhead.

Also:  you've set shared_buffers to 16GB.  That's beyond where most 
people find increases to stop being useful.  I'd wager you'll get less 
spiky performance just by lowering that a lot.  The 256MB to 1GB range 
is where I normally end up on servers where lower latency is prioritized 
instead of maximum throughput.

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


--------------010207020408040205030607
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
  <meta content="text/html; charset=ISO-8859-1"
 http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#000000">
On 10/31/2011 07:28 PM, Brian Fehrle wrote:
<blockquote cite="mid:4EAF2F20.7080205@consistentstate.com" type="cite">
  <meta http-equiv="content-type"
 content="text/html; charset=ISO-8859-1">
The main thing I am currently seeing is that there are 300X or more
buffers written by checkpoints rather than background writer.<br>
  <br>
</blockquote>
<br>
Writing buffers at checkpoint time is more efficient than having the
background writer handle them.&nbsp; I think your efforts to space
checkpoints out may have backfired a bit on you.&nbsp; You're letting 40
minutes of dirty buffers accumulate before they're written out.&nbsp;
Putting checkpoint_timeout closer to its default of 5 minutes again may
reduce the spikes you're seeing.<br>
<br>
The changes you've made to the background writer configuration are also
counterproductive, given that it's not really going to trigger anyway.&nbsp;
I would only recommend decreasing bgwriter_delay or increasing
bgwriter_lru_maxpages or you see the total_clean_max_written value get
incremented regularly.&nbsp; If that's not happening, making the background
writer run more often and try to do more work just adds overhead.<br>
<br>
Also:&nbsp; you've set shared_buffers to 16GB.&nbsp; That's beyond where most
people find increases to stop being useful.&nbsp; I'd wager you'll get less
spiky performance just by lowering that a lot.&nbsp; The 256MB to 1GB range
is where I normally end up on servers where lower latency is
prioritized instead of maximum throughput.<br>
<br>
<pre class="moz-signature" cols="72">-- 
Greg Smith   2ndQuadrant US    <a class="moz-txt-link-abbreviated" href="mailto:greg@2ndQuadrant.com">greg@2ndQuadrant.com</a>   Baltimore, MD
PostgreSQL Training, Services, and 24x7 Support  <a class="moz-txt-link-abbreviated" href="http://www.2ndQuadrant.us">www.2ndQuadrant.us</a>
</pre>
</body>
</html>

--------------010207020408040205030607--