|
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 |
