From: Brian Fehrle <brianf@consistentstate.com>
To: pgsql-admin@postgresql.org
Subject: background writer being lazy?
Date: Mon, 31 Oct 2011 17:28:32 -0600
Message-ID: <4EAF2F20.7080205@consistentstate.com> (raw)
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: brianf@consistentstate.com
Subject: Re: background writer being lazy?
In-Reply-To: <4EAF2F20.7080205@consistentstate.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox