Re: Add usage counts to pg_buffercache - Mailing list pgsql-patches

Your patch has been added to the PostgreSQL unapplied patches list at:

    http://momjian.postgresql.org/cgi-bin/pgpatches

It will be applied as soon as one of the PostgreSQL committers reviews
and approves it.

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


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 gsmith@gregsmith.com 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  <bruce@momjian.us>          http://momjian.us
  EnterpriseDB                               http://www.enterprisedb.com

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