Skip site navigation (1) Skip section navigation (2)

Re: Details about pg_stat_bgwriter

From: Greg Smith <greg(at)2ndquadrant(dot)com>
To: Thomas Kellerer <spam_eater(at)gmx(dot)net>
Cc: pgsql-admin(at)postgresql(dot)org
Subject: Re: Details about pg_stat_bgwriter
Date: 2010-06-09 05:14:33
Message-ID: 4C0F2339.8070403@2ndquadrant.com (view raw or flat)
Thread:
Lists: pgsql-admin
Greg Smith wrote:
> You don't much with a single snapshot of pg_stat_bgwriter data.  Try 
> saving this instead:
> select *,now() from pg_stat_bgwriter;
> And then take another snapshot at least a few hours later, preferably 
> the next day.  With two snapshots and timestamps on them, then it's 
> possible to make some sense of the numbers.

I probably should have explained the next part.  I've now shared what I 
do with this information at 
http://www.pgcon.org/2010/schedule/events/218.en.html

Basically, if you put the data from the two snapshots into one of the 
Statistics Spreadsheet versions, you'll get several derived numbers that 
pop out:

-Average checkpoint frequency
-Average size of each checkpoint
-Average rate at which new buffers are allocated
-Average rate of writes out of the buffer cache
-Percentage of writes done by checkpoints, the background writer LRU 
cleaner, and client backends

These are the sort of things you can actually think about in useful 
real-world terms.  And if you tune the database by doing things like 
increasing checkpoint_segments/checkpoint_timeout or changing the size 
of shared_buffers, you can see what impact that has on the overall 
system response, by noting how it changes these numeric measures.  It's 
a bit more exact of an approach for tuning such things than what people 
have traditionally done here.  Increasing shared_buffers and watching 
the total writes/second number drop significantly is more exact than 
just nothing whether the server waiting for I/O percentage dropped or not.

I also highly recommend installing pg_buffercache and looking at what's 
inside your buffer cache too--the data actually being cached by the 
server never fails to surprise people, relative to what they expected 
was being cached.  Source for how to analyze those things is in the 
pg_buffercache samples shell script, the "Buffer contents summary, with 
percentages" is the one that gives useful output.  One of the thing I 
did in the presentation that does not come through on the slides is note 
how the distribution of high usage count data in your buffer cache 
determines whether you will benefit from making it large or not.  I've 
reduced this to a single figure of merit now:  "cumulative % of buffers 
that have a usage count of >=2".  On a system that benefits from having 
a big buffer cache, that number will be quite large (86% in the first of 
the samples in the spreadsheet).  On one that doesn't, it will be small 
(35% on the last system listed there).  You really have to tune those 
two types of workload differently.

-- 
Greg Smith  2ndQuadrant US  Baltimore, MD
PostgreSQL Training, Services and Support
greg(at)2ndQuadrant(dot)com   www.2ndQuadrant.us


In response to

Responses

pgsql-admin by date

Next:From: Scott MarloweDate: 2010-06-09 05:54:27
Subject: Re: optimizer behavior in the case of highly updated tables
Previous:From: Mark RostronDate: 2010-06-09 02:58:34
Subject: optimizer behavior in the case of highly updated tables

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group