Re: Add usage counts to pg_buffercache

Possibly minor detail;

from buf_internals.h

uint16 usage_count; /* usage counter for clock sweep code */

and you have a int16 to store that. Currently the max buffer count is
5. But is that a complete safe assumption? Maybe a compile time check
that BM_MAX_USAGE_COUNT is < 16k would ensure that things don't go wrong?

Regards

Russell Smith

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(at)gregsmith(dot)com http://www.gregsmith.com Baltimore, MD
> ------------------------------------------------------------------------
>
> Index: README.pg_buffercache
> ===================================================================
> RCS file: /projects/cvsroot/pgsql/contrib/pg_buffercache/README.pg_buffercache,v
> retrieving revision 1.3
> diff -c -r1.3 README.pg_buffercache
> *** README.pg_buffercache 26 Apr 2006 22:50:17 -0000 1.3
> --- README.pg_buffercache 1 Apr 2007 02:17:15 -0000
> ***************
> *** 40,46 ****
> reldatabase | pg_database.oid | Database for the relation.
> relblocknumber | | Offset of the page in the relation.
> isdirty | | Is the page dirty?
> !
>
> There is one row for each buffer in the shared cache. Unused buffers are
> shown with all fields null except bufferid.
> --- 40,46 ----
> reldatabase | pg_database.oid | Database for the relation.
> relblocknumber | | Offset of the page in the relation.
> isdirty | | Is the page dirty?
> ! usagecount | | Page LRU count
>
> There is one row for each buffer in the shared cache. Unused buffers are
> shown with all fields null except bufferid.
> ***************
> *** 60,79 ****
>
> regression=# \d pg_buffercache;
> View "public.pg_buffercache"
> ! Column | Type | Modifiers
> ! ----------------+---------+-----------
> ! bufferid | integer |
> ! relfilenode | oid |
> ! reltablespace | oid |
> ! reldatabase | oid |
> ! relblocknumber | bigint |
> ! isdirty | boolean |
> View definition:
> SELECT p.bufferid, p.relfilenode, p.reltablespace, p.reldatabase,
> ! p.relblocknumber, p.isdirty
> FROM pg_buffercache_pages() p(bufferid integer, relfilenode oid,
> reltablespace oid, reldatabase oid, relblocknumber bigint,
> ! isdirty boolean);
>
> regression=# SELECT c.relname, count(*) AS buffers
> FROM pg_class c INNER JOIN pg_buffercache b
> --- 60,81 ----
>
> regression=# \d pg_buffercache;
> View "public.pg_buffercache"
> ! Column | Type | Modifiers
> ! ----------------+----------+-----------
> ! bufferid | integer |
> ! relfilenode | oid |
> ! reltablespace | oid |
> ! reldatabase | oid |
> ! relblocknumber | bigint |
> ! isdirty | boolean |
> ! usagecount | smallint |
> !
> View definition:
> SELECT p.bufferid, p.relfilenode, p.reltablespace, p.reldatabase,
> ! p.relblocknumber, p.isdirty, p.usagecount
> FROM pg_buffercache_pages() p(bufferid integer, relfilenode oid,
> reltablespace oid, reldatabase oid, relblocknumber bigint,
> ! isdirty boolean, usagecount smallint);
>
> regression=# SELECT c.relname, count(*) AS buffers
> FROM pg_class c INNER JOIN pg_buffercache b
> Index: pg_buffercache.sql.in
> ===================================================================
> RCS file: /projects/cvsroot/pgsql/contrib/pg_buffercache/pg_buffercache.sql.in,v
> retrieving revision 1.4
> diff -c -r1.4 pg_buffercache.sql.in
> *** pg_buffercache.sql.in 26 Apr 2006 22:50:17 -0000 1.4
> --- pg_buffercache.sql.in 1 Apr 2007 02:17:15 -0000
> ***************
> *** 12,18 ****
> CREATE VIEW pg_buffercache AS
> SELECT P.* FROM pg_buffercache_pages() AS P
> (bufferid integer, relfilenode oid, reltablespace oid, reldatabase oid,
> ! relblocknumber int8, isdirty bool);
>
> -- Don't want these to be available at public.
> REVOKE ALL ON FUNCTION pg_buffercache_pages() FROM PUBLIC;
> --- 12,18 ----
> CREATE VIEW pg_buffercache AS
> SELECT P.* FROM pg_buffercache_pages() AS P
> (bufferid integer, relfilenode oid, reltablespace oid, reldatabase oid,
> ! relblocknumber int8, isdirty bool, usagecount int2);
>
> -- Don't want these to be available at public.
> REVOKE ALL ON FUNCTION pg_buffercache_pages() FROM PUBLIC;
> Index: pg_buffercache_pages.c
> ===================================================================
> RCS file: /projects/cvsroot/pgsql/contrib/pg_buffercache/pg_buffercache_pages.c,v
> retrieving revision 1.11
> diff -c -r1.11 pg_buffercache_pages.c
> *** pg_buffercache_pages.c 22 Oct 2006 17:49:21 -0000 1.11
> --- pg_buffercache_pages.c 1 Apr 2007 02:17:15 -0000
> ***************
> *** 16,22 ****
> #include "utils/relcache.h"
>
>
> ! #define NUM_BUFFERCACHE_PAGES_ELEM 6
>
> PG_MODULE_MAGIC;
>
> --- 16,22 ----
> #include "utils/relcache.h"
>
>
> ! #define NUM_BUFFERCACHE_PAGES_ELEM 7
>
> PG_MODULE_MAGIC;
>
> ***************
> *** 35,40 ****
> --- 35,41 ----
> BlockNumber blocknum;
> bool isvalid;
> bool isdirty;
> + uint16 usagecount;
> } BufferCachePagesRec;
>
>
> ***************
> *** 91,96 ****
> --- 92,99 ----
> INT8OID, -1, 0);
> TupleDescInitEntry(tupledesc, (AttrNumber) 6, "isdirty",
> BOOLOID, -1, 0);
> + TupleDescInitEntry(tupledesc, (AttrNumber) 7, "usage_count",
> + INT2OID, -1, 0);
>
> fctx->tupdesc = BlessTupleDesc(tupledesc);
>
> ***************
> *** 126,131 ****
> --- 129,135 ----
> fctx->record[i].reltablespace = bufHdr->tag.rnode.spcNode;
> fctx->record[i].reldatabase = bufHdr->tag.rnode.dbNode;
> fctx->record[i].blocknum = bufHdr->tag.blockNum;
> + fctx->record[i].usagecount = bufHdr->usage_count;
>
> if (bufHdr->flags & BM_DIRTY)
> fctx->record[i].isdirty = true;
> ***************
> *** 172,177 ****
> --- 176,182 ----
> nulls[3] = true;
> nulls[4] = true;
> nulls[5] = true;
> + nulls[6] = true;
> }
> else
> {
> ***************
> *** 185,190 ****
> --- 190,197 ----
> nulls[4] = false;
> values[5] = BoolGetDatum(fctx->record[i].isdirty);
> nulls[5] = false;
> + values[6] = Int16GetDatum(fctx->record[i].usagecount);
> + nulls[6] = false;
> }
>
> /* Build and return the tuple. */
>
> ------------------------------------------------------------------------
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Have you searched our list archives?
>
> http://archives.postgresql.org
>