background writer being lazy?

From: Brian Fehrle <brianf(at)consistentstate(dot)com>
To: pgsql-admin(at)postgresql(dot)org
Subject: background writer being lazy?
Date: 2011-10-31 23:28:32
Message-ID: 4EAF2F20.7080205@consistentstate.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-admin

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

Responses

Browse pgsql-admin by date

  From Date Subject
Next Message Karuna Karpe 2011-11-01 11:40:29 streaming replication
Previous Message Matthew Sellers 2011-10-31 16:34:06 Corruption Debug Help.