Re: Display Pg buffer cache (WIP) - Mailing list pgsql-patches

A couple of minor amendments here:

- remove link to libpq (from cut+past of dblnk's Makefile)
- add comment for pg_buffercache module in contrib/README
- change my listed email to this one (I have resigned....)

regards

Mark
diff -Nacr pgsql.orig/contrib/Makefile pgsql/contrib/Makefile
*** pgsql.orig/contrib/Makefile    Thu Mar  3 11:29:53 2005
--- pgsql/contrib/Makefile    Wed Mar  9 10:10:41 2005
***************
*** 26,31 ****
--- 26,32 ----
          noupdate    \
          oid2name    \
          pg_autovacuum    \
+         pg_buffercache    \
          pg_dumplo    \
          pg_trgm        \
          pgbench        \
diff -Nacr pgsql.orig/contrib/README pgsql/contrib/README
*** pgsql.orig/contrib/README    Thu Mar  3 11:29:53 2005
--- pgsql/contrib/README    Fri Mar 11 12:11:18 2005
***************
*** 136,141 ****
--- 136,145 ----
      Automatically performs vacuum
      by Matthew T. O'Connor <matthew@zeut.net>

+ pg_buffercace -
+     Real time queries on the shared buffer cache.
+     by Mark Kirkwood <markir@paradise.net.nz>
+
  pg_dumplo -
      Dump large objects
      by Karel Zak <zakkr@zf.jcu.cz>
diff -Nacr pgsql.orig/contrib/pg_buffercache/Makefile pgsql/contrib/pg_buffercache/Makefile
*** pgsql.orig/contrib/pg_buffercache/Makefile    Thu Jan  1 12:00:00 1970
--- pgsql/contrib/pg_buffercache/Makefile    Thu Mar 10 08:19:20 2005
***************
*** 0 ****
--- 1,19 ----
+ # $PostgreSQL$
+
+ MODULE_big = pg_buffercache
+ OBJS    = pg_buffercache_pages.o
+
+ DATA_built = pg_buffercache.sql
+ DOCS = README.pg_buffercache
+ REGRESS = pg_buffercache
+
+
+ ifdef USE_PGXS
+ PGXS = $(shell pg_config --pgxs)
+ include $(PGXS)
+ else
+ subdir = contrib/pg_buffercache
+ top_builddir = ../..
+ include $(top_builddir)/src/Makefile.global
+ include $(top_srcdir)/contrib/contrib-global.mk
+ endif
diff -Nacr pgsql.orig/contrib/pg_buffercache/README.pg_buffercache pgsql/contrib/pg_buffercache/README.pg_buffercache
*** pgsql.orig/contrib/pg_buffercache/README.pg_buffercache    Thu Jan  1 12:00:00 1970
--- pgsql/contrib/pg_buffercache/README.pg_buffercache    Fri Mar 11 12:11:03 2005
***************
*** 0 ****
--- 1,112 ----
+ Pg_buffercache - Real time queries on the shared buffer cache.
+ --------------
+
+   This module consists of a C function 'pg_buffercache_pages()' that returns
+   a set of records, plus a view 'pg_buffercache' to wrapper the function.
+
+   The intent is to do for the buffercache what pg_locks does for locks, i.e -
+   ability to examine what is happening at any given time without having to
+   restart or rebuild the server with debugging code added.
+
+   By default public access is REVOKED from both of these, just in case there
+   are security issues lurking.
+
+
+ Installation
+ ------------
+
+   Build and install the main Postgresql source, then this contrib module:
+
+   $ cd contrib/pg_buffercache
+   $ gmake
+   $ gmake install
+
+
+   To register the functions:
+
+   $ psql -d <database> -f pg_buffercache.sql
+
+
+ Notes
+ -----
+
+   The definition of the columns exposed in the view is:
+
+        Column     |  references          | Description
+   ----------------+----------------------+------------------------------------
+    bufferid       |                      | Id, 1->shared_buffers.
+    relfilenode    | pg_class.relfilenode | Refilenode of the relation.
+    reltablespace  | pg_tablespace.oid    | Tablespace oid of the relation.
+    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.
+
+   Because the cache is shared by all the databases, there are pages from
+   relations not belonging to the current database.
+
+   When the pg_buffercache view is accessed, internal buffer manager locks are
+   taken, and a copy of the buffer cache data is made for the view to display.
+   This ensures that the view produces a consistent set of results, while not
+   blocking normal buffer activity longer than necessary.  Nonetheless there
+   could be some impact on database performance if this view is read often.
+
+
+ Sample output
+ -------------
+
+   regression=# \d pg_buffercache;
+        View "public.pg_buffercache"
+        Column     |  Type   | Modifiers
+   ----------------+---------+-----------
+    bufferid       | integer |
+    relfilenode    | oid     |
+    reltablespace  | oid     |
+    reldatabase    | oid     |
+    relblocknumber | numeric |
+    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 numeric(10,0),
+      isdirty boolean);
+
+   regression=# SELECT c.relname, count(*) AS buffers
+                FROM pg_class c, pg_buffercache b
+                WHERE b.relfilenode = c.relfilenode
+                GROUP BY c.relname
+                ORDER BY 2 DESC LIMIT 10;
+                relname             | buffers
+   ---------------------------------+---------
+    tenk2                           |     345
+    tenk1                           |     141
+    pg_proc                         |      46
+    pg_class                        |      45
+    pg_attribute                    |      43
+    pg_class_relname_nsp_index      |      30
+    pg_proc_proname_args_nsp_index  |      28
+    pg_attribute_relid_attnam_index |      26
+    pg_depend                       |      22
+    pg_depend_reference_index       |      20
+   (10 rows)
+
+   regression=#
+
+
+ Author
+ ------
+
+   * Mark Kirkwood <markir@paradise.net.nz>
+
+
+ Help
+ ----
+
+   * Design suggestions : Neil Conway <neilc@samurai.com>
+   * Debugging advice : Tom Lane <tgl@sss.pgh.pa.us>
+
+   Thanks guys!
diff -Nacr pgsql.orig/contrib/pg_buffercache/pg_buffercache.sql.in pgsql/contrib/pg_buffercache/pg_buffercache.sql.in
*** pgsql.orig/contrib/pg_buffercache/pg_buffercache.sql.in    Thu Jan  1 12:00:00 1970
--- pgsql/contrib/pg_buffercache/pg_buffercache.sql.in    Wed Mar  9 11:45:32 2005
***************
*** 0 ****
--- 1,18 ----
+ -- Adjust this setting to control where the objects get created.
+ SET search_path = public;
+
+ -- Register the function.
+ CREATE OR REPLACE FUNCTION pg_buffercache_pages()
+ RETURNS SETOF RECORD
+ AS 'MODULE_PATHNAME', 'pg_buffercache_pages'
+ LANGUAGE 'C';
+
+ -- Create a view for convenient access.
+ CREATE VIEW pg_buffercache AS
+     SELECT P.* FROM pg_buffercache_pages() AS P
+      (bufferid integer, relfilenode oid, reltablespace oid, reldatabase oid,
+       relblocknumber numeric(10), isdirty bool);
+
+ -- Don't want these to be available at public.
+ REVOKE ALL ON FUNCTION pg_buffercache_pages() FROM PUBLIC;
+ REVOKE ALL ON pg_buffercache FROM PUBLIC;
diff -Nacr pgsql.orig/contrib/pg_buffercache/pg_buffercache_pages.c pgsql/contrib/pg_buffercache/pg_buffercache_pages.c
*** pgsql.orig/contrib/pg_buffercache/pg_buffercache_pages.c    Thu Jan  1 12:00:00 1970
--- pgsql/contrib/pg_buffercache/pg_buffercache_pages.c    Thu Mar 10 08:19:13 2005
***************
*** 0 ****
--- 1,238 ----
+ /*-------------------------------------------------------------------------
+  *
+  * pg_buffercache_pages.c
+  *    display some contents of the buffer cache
+  *
+  *      $PostgreSQL$
+  *-------------------------------------------------------------------------
+  */
+ #include "postgres.h"
+ #include "funcapi.h"
+ #include "catalog/pg_type.h"
+ #include "storage/buf_internals.h"
+ #include "storage/bufmgr.h"
+ #include "utils/relcache.h"
+ #include "pg_buffercache_pages.h"
+
+
+ #define NUM_BUFFERCACHE_PAGES_ELEM    6
+
+
+ /*
+  * Record structure holding the to be exposed cache data.
+  */
+ typedef struct
+ {
+
+     uint32        bufferid;
+     Oid            relfilenode;
+     Oid            reltablespace;
+     Oid            reldatabase;
+     BlockNumber    blocknum;
+     bool        isvalid;
+     bool        isdirty;
+
+ } BufferCachePagesRec;
+
+
+ /*
+  * Function context for data persisting over repeated calls.
+  */
+ typedef struct
+ {
+
+     AttInMetadata        *attinmeta;
+     BufferCachePagesRec    *record;
+     char                *values[NUM_BUFFERCACHE_PAGES_ELEM];
+
+ } BufferCachePagesContext;
+
+
+ /*
+  * Function returning data from the shared buffer cache - buffer number,
+  * relation node/tablespace/database/blocknum and dirty indicator.
+  */
+ PG_FUNCTION_INFO_V1(pg_buffercache_pages);
+ Datum
+ pg_buffercache_pages(PG_FUNCTION_ARGS)
+ {
+
+     FuncCallContext        *funcctx;
+     Datum                result;
+     MemoryContext        oldcontext;
+     BufferCachePagesContext    *fctx;        /* User function context. */
+     TupleDesc            tupledesc;
+     HeapTuple            tuple;
+
+     if (SRF_IS_FIRSTCALL())
+     {
+         RelFileNode    rnode;
+         uint32        i;
+         BufferDesc    *bufHdr;
+
+
+         funcctx = SRF_FIRSTCALL_INIT();
+
+         /* Switch context when allocating stuff to be used in later calls */
+         oldcontext = MemoryContextSwitchTo(funcctx->multi_call_memory_ctx);
+
+         /* Construct a tuple to return. */
+         tupledesc = CreateTemplateTupleDesc(NUM_BUFFERCACHE_PAGES_ELEM, false);
+         TupleDescInitEntry(tupledesc, (AttrNumber) 1, "bufferid",
+                                     INT4OID, -1, 0);
+         TupleDescInitEntry(tupledesc, (AttrNumber) 2, "relfilenode",
+                                     OIDOID, -1, 0);
+         TupleDescInitEntry(tupledesc, (AttrNumber) 3, "reltablespace",
+                                     OIDOID, -1, 0);
+         TupleDescInitEntry(tupledesc, (AttrNumber) 4, "reldatabase",
+                                     OIDOID, -1, 0);
+         TupleDescInitEntry(tupledesc, (AttrNumber) 5, "relblockbumber",
+                                     NUMERICOID, -1, 0);
+         TupleDescInitEntry(tupledesc, (AttrNumber) 6, "isdirty",
+                                     BOOLOID, -1, 0);
+
+         /* Generate attribute metadata needed later to produce tuples */
+         funcctx->attinmeta = TupleDescGetAttInMetadata(tupledesc);
+
+         /*
+          * Create a function context for cross-call persistence
+          * and initialize the buffer counters.
+          */
+         fctx = (BufferCachePagesContext *) palloc(sizeof(BufferCachePagesContext));
+         funcctx->max_calls = NBuffers;
+         funcctx->user_fctx = fctx;
+
+
+         /* Allocate NBuffers worth of BufferCachePagesRec records. */
+         fctx->record = (BufferCachePagesRec *) palloc(sizeof(BufferCachePagesRec) * NBuffers);
+
+         /* allocate the strings for tuple formation */
+         fctx->values[0] = (char *) palloc(3 * sizeof(uint32) + 1);
+         fctx->values[1] = (char *) palloc(3 * sizeof(uint32) + 1);
+         fctx->values[2] = (char *) palloc(3 * sizeof(uint32) + 1);
+         fctx->values[3] = (char *) palloc(3 * sizeof(uint32) + 1);
+         fctx->values[4] = (char *) palloc(3 * sizeof(uint32) + 1);
+         fctx->values[5] = (char *) palloc(2);
+
+
+         /* Return to original context when allocating transient memory */
+         MemoryContextSwitchTo(oldcontext);
+
+
+         /*
+          * Lock Buffer map and scan though all the buffers, saving the
+          * relevant fields in the fctx->record structure.
+          */
+         LWLockAcquire(BufMappingLock, LW_SHARED);
+
+         for (i = 0, bufHdr = BufferDescriptors; i < NBuffers; i++, bufHdr++)
+         {
+             /* Lock each buffer header before inspecting. */
+             LockBufHdr(bufHdr);
+
+             rnode = bufHdr->tag.rnode;
+
+             fctx->record[i].bufferid = BufferDescriptorGetBuffer(bufHdr);
+             fctx->record[i].relfilenode = rnode.relNode;
+             fctx->record[i].reltablespace = rnode.spcNode;
+             fctx->record[i].reldatabase = rnode.dbNode;
+             fctx->record[i].blocknum = bufHdr->tag.blockNum;
+
+             if ( bufHdr->flags & BM_DIRTY)
+             {
+                 fctx->record[i].isdirty = true;
+             }
+             else
+             {
+                 fctx->record[i].isdirty = false;
+             }
+
+             /* Note if the buffer is valid, and has storage created */
+             if ( (bufHdr->flags & BM_VALID) && (bufHdr->flags & BM_TAG_VALID))
+             {
+                 fctx->record[i].isvalid = true;
+             }
+             else
+             {
+                 fctx->record[i].isvalid = false;
+             }
+
+             UnlockBufHdr(bufHdr);
+
+         }
+
+         /* Release Buffer map. */
+         LWLockRelease(BufMappingLock);
+     }
+
+     funcctx = SRF_PERCALL_SETUP();
+
+     /* Get the saved state */
+     fctx = funcctx->user_fctx;
+
+
+     if (funcctx->call_cntr < funcctx->max_calls)
+     {
+         uint32         i = funcctx->call_cntr;
+         char        *values[NUM_BUFFERCACHE_PAGES_ELEM];
+         int            j;
+
+         /*
+          * Use a temporary values array, initially pointing to
+          * fctx->values, so it can be reassigned w/o losing the storage
+          * for subsequent calls.
+          */
+         for (j = 0; j < NUM_BUFFERCACHE_PAGES_ELEM; j++)
+         {
+             values[j] = fctx->values[j];
+         }
+
+
+         /*
+          * Set all fields except the bufferid to null if the buffer is
+          * unused or not valid.
+          */
+         if (fctx->record[i].blocknum == InvalidBlockNumber ||
+             fctx->record[i].isvalid == false )
+         {
+
+             sprintf(values[0], "%u", fctx->record[i].bufferid);
+             values[1] = NULL;
+             values[2] = NULL;
+             values[3] = NULL;
+             values[4] = NULL;
+             values[5] = NULL;
+
+         }
+         else
+         {
+
+             sprintf(values[0], "%u", fctx->record[i].bufferid);
+             sprintf(values[1], "%u", fctx->record[i].relfilenode);
+             sprintf(values[2], "%u", fctx->record[i].reltablespace);
+             sprintf(values[3], "%u", fctx->record[i].reldatabase);
+             sprintf(values[4], "%u", fctx->record[i].blocknum);
+             if (fctx->record[i].isdirty)
+             {
+                 strcpy(values[5], "t");
+             }
+             else
+             {
+                 strcpy(values[5], "f");
+             }
+
+         }
+
+
+         /* Build and return the tuple. */
+         tuple = BuildTupleFromCStrings(funcctx->attinmeta, values);
+         result = HeapTupleGetDatum(tuple);
+
+
+         SRF_RETURN_NEXT(funcctx, result);
+     }
+     else
+         SRF_RETURN_DONE(funcctx);
+
+ }
+
diff -Nacr pgsql.orig/contrib/pg_buffercache/pg_buffercache_pages.h pgsql/contrib/pg_buffercache/pg_buffercache_pages.h
*** pgsql.orig/contrib/pg_buffercache/pg_buffercache_pages.h    Thu Jan  1 12:00:00 1970
--- pgsql/contrib/pg_buffercache/pg_buffercache_pages.h    Wed Mar  9 11:42:20 2005
***************
*** 0 ****
--- 1,18 ----
+ /*-------------------------------------------------------------------------
+  *
+  * pg_buffercache_pages.h
+  *    Prototypes for pg_buffercache_pages
+  *
+  *
+  *    $PostgreSQL$
+  *
+  *-------------------------------------------------------------------------
+  */
+
+
+ #ifndef PG_BUFFERCACHE_PAGES_H
+ #define PG_BUFFERCACHE_PAGES_H
+
+ extern Datum pg_buffercache_pages(PG_FUNCTION_ARGS);
+
+ #endif  /* PG_BUFFERCACHE_PAGES_H */