Patch applied.  Thanks.

---------------------------------------------------------------------------


Greg Smith wrote:
> 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 [EMAIL PROTECTED] http://www.gregsmith.com Baltimore, MD
Content-Description: 

[ Attachment, skipping... ]

> 
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Have you searched our list archives?
> 
>                http://archives.postgresql.org

-- 
  Bruce Momjian  <[EMAIL PROTECTED]>          http://momjian.us
  EnterpriseDB                               http://www.enterprisedb.com

  + If your life is a hard drive, Christ can be your backup. +

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

               http://archives.postgresql.org

Reply via email to