Received: from maia.hub.org (maia-5.hub.org [200.46.204.29]) by mail.postgresql.org (Postfix) with ESMTP id 4B194B5DC2B for ; Mon, 31 Oct 2011 20:30:28 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.29]) (amavisd-maia, port 10024) with ESMTP id 89656-07 for ; Mon, 31 Oct 2011 23:30:22 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.8.0-rc2 Received: from mail-pz0-f44.google.com (mail-pz0-f44.google.com [209.85.210.44]) by mail.postgresql.org (Postfix) with ESMTP id 9BF91B5DC20 for ; Mon, 31 Oct 2011 20:30:21 -0300 (ADT) Received: by pzk4 with SMTP id 4so39941195pzk.3 for ; Mon, 31 Oct 2011 16:30:21 -0700 (PDT) Received: by 10.68.28.4 with SMTP id x4mr26743012pbg.56.1320103820961; Mon, 31 Oct 2011 16:30:20 -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 x8sm54149170pbx.15.2011.10.31.16.30.19 (version=SSLv3 cipher=OTHER); Mon, 31 Oct 2011 16:30:20 -0700 (PDT) Message-ID: <4EAF2F20.7080205@consistentstate.com> Date: Mon, 31 Oct 2011 17:28:32 -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: pgsql-admin@postgresql.org Subject: background writer being lazy? Content-Type: multipart/alternative; boundary="------------000603090104070107000209" 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: 201110/142 X-Sequence-Number: 38594 This is a multi-part message in MIME format. --------------000603090104070107000209 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Hi all, I have a large database that I'm trying to do some buffer writer tuning to. I have a data collector set up on pg_stat_bgwriter so I can see the statistics over time, and see how many buffers are being written by checkpoints, and how many are being written by the background writer. The main thing I am currently seeing is that there are 300X or more buffers written by checkpoints rather than background writer. Here are my current settings: PostgreSQL 8.4 bgwriter_delay = 50ms bgwriter_lru_maxpages = 500 bgwriter_lru_multiplier = 4 checkpoint_segments=100 checkpoint_timeout=40min (NOTE: Watching my logs, timed checkpoints occur on time, exactly 40 minutes apart, no extra) I have a lot of snapshots of pg_stat_bgwriter, but here are the totals for three days worth that I find interesting. These numbers represent the total increase the entire day, so end of day value - begining of day value. Day 1: total_ckpt_buffers | total_clean_buffers | total_clean_max_written --------------------+---------------------+------------------------- 474322 | 1368 | 0 Day 2: total_ckpt_buffers | total_clean_buffers | total_clean_max_written --------------------+---------------------+------------------------- 507956 | 2208 | 0 Day 3: total_ckpt_buffers | total_clean_buffers | total_clean_max_written --------------------+---------------------+------------------------- 622519 | 65879 | 56 day three had a big spike, where my max_written (set to 500 pages) was reached. more often than not, max pages is not reached, and I can go 6 hours without a single buffer written by background writer, while 100K is written by my checkpoints (side note, i have log_checkpoints = on and the log confirms the checkpoint buffers written values). So my main question is, where is the issue? It doesn't seem (to me) that the background writer is having a hard time keeping up, because there are simply tons of times where it's doing nothing. So is it just not determining that it needs to do anything because there are already enough 'clean buffers' ready for use at any given time? Would increasing bgwriter_lru_multiplier to a higher value help get more to be written by the bgwriter, and if so are there any negative side effects I would need to consider for this? Or, is there another explanation to this behavior that I'm not thinking of? Thanks in advance for any help, thoughts - Brian F --------------000603090104070107000209 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Hi all,

I have a large database that I'm trying to do some buffer writer tuning to. I have a data collector set up on pg_stat_bgwriter so I can see the statistics over time, and see how many buffers are being written by checkpoints, and how many are being written by the background writer.

The main thing I am currently seeing is that there are 300X or more buffers written by checkpoints rather than background writer.

Here are my current settings:
PostgreSQL 8.4
bgwriter_delay = 50ms
bgwriter_lru_maxpages = 500
bgwriter_lru_multiplier = 4

checkpoint_segments=100
checkpoint_timeout=40min
(NOTE: Watching my logs, timed checkpoints occur on time, exactly 40 minutes apart, no extra)

I have a lot of snapshots of pg_stat_bgwriter, but here are the totals for three days worth that I find interesting. These numbers represent the total increase the entire day, so end of day value - begining of day value.
Day 1:
 total_ckpt_buffers | total_clean_buffers | total_clean_max_written
--------------------+---------------------+-------------------------
             474322 |                1368 |                       0

Day 2:
 total_ckpt_buffers | total_clean_buffers | total_clean_max_written
--------------------+---------------------+-------------------------
             507956 |                2208 |                       0

Day 3:
 total_ckpt_buffers | total_clean_buffers | total_clean_max_written
--------------------+---------------------+-------------------------
             622519 |               65879 |                      56

day three  had a big spike, where my max_written (set to 500 pages) was reached. more often than not, max pages is not reached, and I can go 6 hours without a single buffer written by background writer, while 100K is written by my checkpoints (side note, i have log_checkpoints = on and the log confirms the checkpoint buffers written values).


So my main question is, where is the issue? It doesn't seem (to me) that the background writer is having a hard time keeping up, because there are simply tons of times where it's doing nothing. So is it just not determining that it needs to do anything because there are already enough 'clean buffers' ready for use at any given time? Would increasing bgwriter_lru_multiplier to a higher value help get more to be written by the bgwriter, and if so are there any negative side effects I would need to consider for this?

Or, is there another explanation to this behavior that I'm not thinking of?

Thanks in advance for any help, thoughts

- Brian F
--------------000603090104070107000209--