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

Add usage counts to pg_buffercache

From: Greg Smith <gsmith(at)gregsmith(dot)com>
To: pgsql-patches(at)postgresql(dot)org
Subject: Add usage counts to pg_buffercache
Date: 2007-04-01 02:20:55
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-patches
This patch adds the usage count statistic to the information available in 
contrib/pgbuffercache.  Earlier this month a discussion about my first 
attempt to instrument the background writer had Tom asking for details 
about the usage histogram I was seeing, and this patch proved to be the 
easiest way I found to take a look at that.

In situations where one is trying to optimize the background writer, it's 
very hard to adjust how much to rely on the LRU writer versus the one that 
writes everything unless you know whether your dirty buffers are typically 
used heavily (like index blocks) or not (like new INSERT data).  Some 
statistics about the usage counts in your buffer cache are extremely 
helpful in making that decision.

I'll even pass along an ugly but fun query that utilizes this.  The 
following will give you a summary of your buffer cache broken into 32 
sections.  Each line shows the average usage count of that section, as a 
positive number if most buffers dirty and a negative one if most are 
clean.  If you refresh this frequently enough, you can actually watch 
things like how checkpoints move through the buffer cache:

SELECT current_timestamp,
-- Split into 32 bins of data
round(bufferid / (cast((select setting from pg_settings where 
name='shared_buffers') as int) / (32 - 1.0)))
as section, round(
-- Average usage count, capped at 5
case when avg(usagecount)>5 then 5 else avg(usagecount) end *
-- -1 when the majority are clean records, 1 when most are dirty
(case when sum(case when isdirty then 1 else -1 end)>0 then 1 else -1 
end)) as color_intensity
FROM pg_buffercache GROUP BY
round(bufferid / (cast((select setting from pg_settings where 
name='shared_buffers') as int) / (32 - 1.0)));

The 32 can be changed to anything, that's just what fits on my screen. 
The main idea of the above is that if you dump all this to a file 
regularly, it's possible to produce a graph of it showing how the cache 
has changed over time by assigning a different color intensity based on 
the usage count--at a massive cost in overhead, of course.  I'll be 
passing along all that code once I get it ready for other people to use.

* Greg Smith gsmith(at)gregsmith(dot)com Baltimore, MD

Attachment: pgbufcache-usage.txt
Description: text/plain (5.7 KB)


pgsql-patches by date

Next:From: Tom LaneDate: 2007-04-01 02:31:26
Subject: Re: Macros for typtype (was Re: Arrays of Complex Types)
Previous:From: Alvaro HerreraDate: 2007-04-01 02:12:45
Subject: Re: Current enums patch

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