Received: from maia.hub.org (maia-3.hub.org [200.46.204.243]) by mail.postgresql.org (Postfix) with ESMTP id 1AFC0B5E077 for ; Tue, 1 Nov 2011 21:04:48 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.243]) (amavisd-maia, port 10024) with ESMTP id 71426-03-3 for ; Wed, 2 Nov 2011 00:04:40 +0000 (UTC) Received: from mx2.hub.org (mx2.hub.org [200.46.204.254]) by mail.postgresql.org (Postfix) with ESMTP id 91D65B5E0BB for ; Tue, 1 Nov 2011 21:00:40 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.8.0-rc2 Received: from mail-qw0-f46.google.com (mail-qw0-f46.google.com [209.85.216.46]) by mx2.hub.org (Postfix) with ESMTP id 9E5D01750C81 for ; Tue, 1 Nov 2011 17:55:41 -0300 (ADT) Received: by qabj40 with SMTP id j40so6162225qab.19 for ; Tue, 01 Nov 2011 13:55:22 -0700 (PDT) Received: by 10.224.194.5 with SMTP id dw5mr677735qab.16.1320175008763; Tue, 01 Nov 2011 12:16:48 -0700 (PDT) Received: from sidon.consistentstate.com (173-164-51-9-colorado.hfc.comcastbusiness.net. [173.164.51.9]) by mx.google.com with ESMTPS id ha3sm7784185qab.2.2011.11.01.12.16.47 (version=SSLv3 cipher=OTHER); Tue, 01 Nov 2011 12:16:48 -0700 (PDT) Message-ID: <4EB04533.4080905@consistentstate.com> Date: Tue, 01 Nov 2011 13:14:59 -0600 From: Brian Fehrle User-Agent: Mozilla/5.0 (X11; U; Linux i686 (x86_64); en-US; rv:1.9.2.18) Gecko/20110616 Lightning/1.0b2 Thunderbird/3.1.11 MIME-Version: 1.0 To: Kevin Grittner CC: Kevin Kempter , pgsql-admin@postgresql.org Subject: Re: background writer being lazy? References: <4EAF2F20.7080205@consistentstate.com> <4EAFC2CA02000025000428D2@gw.wicourts.gov> <4EB03821.6090408@consistentstate.com> <4EAFF7C502000025000428F2@gw.wicourts.gov> In-Reply-To: <4EAFF7C502000025000428F2@gw.wicourts.gov> Content-Type: multipart/alternative; boundary="------------030207090703080103080309" 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 X-Spam-Level: X-Archive-Number: 201111/3 X-Sequence-Number: 38597 This is a multi-part message in MIME format. --------------030207090703080103080309 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit version | PostgreSQL 8.4.1 on x86_64-unknown-linux-gnu, compiled by GCC gcc (SUSE Linux) 4.3.2 [gcc-4_3-branch revision 141291], 64-bit bgwriter_delay | 50ms bgwriter_lru_maxpages | 500 bgwriter_lru_multiplier | 4 checkpoint_segments | 100 checkpoint_timeout | 40min checkpoint_warning | 30min checkpoint_completion_target | 0.5 effective_cache_size | 16GB effective_io_concurrency | 4 extra_float_digits | 3 max_connections | 2000 max_stack_depth | 7MB shared_buffers | 16GB synchronous_commit | off temp_buffers | 8192 TimeZone | US/Pacific wal_buffers | 8MB work_mem | 64MB Since our checkpoint_completion_target is 0.5, we complete a checkpoint around 20 mins after it started, and checkpoints occur like clockwork every 40 minutes, no extra's are forced. Performance overall is fairly good, but of course we're trying to squeeze as much out of it as we can. One main thing is trying to lower 'spikey' disk IO so that performance is more consistent at any given time. - Brian F On 11/01/2011 12:44 PM, Kevin Grittner wrote: > Brian Fehrle wrote: >> On 11/01/2011 08:58 AM, Kevin Grittner wrote: > >>> What problem are you trying to solve? >>> >> We're trying to offload dirty buffer writes from checkpoints and >> from backends (not the bgwriter) to the background writer, I >> believe with the hope that it's the same amount of disk IO, but >> spread out evenly rather than just when a checkpoint is occurring. > > What version of PostgreSQL are you using? Recent versions spread > out the checkpoint activity using the same process which does the > background writing, so there is no benefit from moving writes from > its background writing phase to its distributed checkpoint phase. > Depending on your setting of checkpoint_completion_target you are > probably spending as much or more time spreading the checkpoint as > doing background writing between checkpoints. Each of the last few > major releases has made this much better, so if you're spending time > tweaking something prior to 9.1, you'd probably be better served > putting that time into upgrading. > >>> Yes. Writing dirty buffers when there are enough buffers >>> available to service requests would tend to increase overall disk >>> writes and degrade performance. You don't have a problem unless >>> you have a high percentage of writes from normal backends which >>> need to flush a buffer in order to get one. >>> >> This seems to be the case, as buffers_backend is between >> checkpoint_buffers and clean_buffers from pg_stat_bgwriter. >> For example, on 2011-10-19: >> checkpoint buffers: 622,519 >> clean_buffers: 65,879 >> clean_max_written: 56 >> backend_buffers: 460,471 >> >> Am I reading these right in wanting to reduce backend_buffers and >> checkpoint_buffers? > > Hmm. That is a higher percentage of backend writes than I would > like to see. What is your shared_memory setting? Actually, please > show the results of the query here: > > http://wiki.postgresql.org/wiki/Server_Configuration > > How is your performance? > > -Kevin --------------030207090703080103080309 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit
 version                       | PostgreSQL 8.4.1 on x86_64-unknown-linux-gnu, compiled by GCC gcc (SUSE Linux) 4.3.2 [gcc-4_3-branch revision 141291], 64-bit
 bgwriter_delay                | 50ms
 bgwriter_lru_maxpages         | 500
 bgwriter_lru_multiplier       | 4
 checkpoint_segments           | 100
 checkpoint_timeout            | 40min
 checkpoint_warning            | 30min
 checkpoint_completion_target  | 0.5
 effective_cache_size          | 16GB
 effective_io_concurrency      | 4
 extra_float_digits            | 3
 max_connections               | 2000
 max_stack_depth               | 7MB
 shared_buffers                | 16GB
 synchronous_commit            | off
 temp_buffers                  | 8192
 TimeZone                      | US/Pacific
 wal_buffers                   | 8MB
 work_mem                      | 64MB


Since our checkpoint_completion_target is 0.5, we complete a checkpoint around 20 mins after it started, and checkpoints occur like clockwork every 40 minutes, no extra's are forced.

Performance overall is fairly good, but of course we're trying to squeeze as much out of it as we can. One main thing is trying to lower 'spikey' disk IO so that performance is more consistent at any given time.

- Brian F

On 11/01/2011 12:44 PM, Kevin Grittner wrote: 
Brian Fehrle <brianf@consistentstate.com> wrote:

On 11/01/2011 08:58 AM, Kevin Grittner wrote:

 

What problem are you trying to solve?


We're trying to offload dirty buffer writes from checkpoints and
from backends (not the bgwriter) to the background writer, I
believe with the hope that it's the same amount of disk IO, but
spread out evenly rather than just when a checkpoint is occurring.

 
What version of PostgreSQL are you using?  Recent versions spread
out the checkpoint activity using the same process which does the
background writing, so there is no benefit from moving writes from
its background writing phase to its distributed checkpoint phase. 
Depending on your setting of checkpoint_completion_target you are
probably spending as much or more time spreading the checkpoint as
doing background writing between checkpoints.  Each of the last few
major releases has made this much better, so if you're spending time
tweaking something prior to 9.1, you'd probably be better served
putting that time into upgrading.
 

Yes.  Writing dirty buffers when there are enough buffers
available to service requests would tend to increase overall disk
writes and degrade performance.  You don't have a problem unless
you have a high percentage of writes from normal backends which
need to flush a buffer in order to get one.


This seems to be the case, as buffers_backend is between 
checkpoint_buffers and clean_buffers from pg_stat_bgwriter.
For example, on 2011-10-19:
checkpoint buffers: 622,519
clean_buffers:  65,879
clean_max_written: 56
backend_buffers: 460,471

Am I reading these right in wanting to reduce backend_buffers and 
checkpoint_buffers?

 
Hmm.  That is a higher percentage of backend writes than I would
like to see.  What is your shared_memory setting?  Actually, please
show the results of the query here:
 
http://wiki.postgresql.org/wiki/Server_Configuration
 
How is your performance?
 
-Kevin

--------------030207090703080103080309--