buffercache.sgml 4.25 KB
<sect1 id="buffercache">
 <title>pg_buffercache</title>
 
 <indexterm zone="buffercache">
  <primary>pg_buffercache</primary>
 </indexterm>

 <para>
  <literal>pg_buffercache</literal> module provides the means for examining 
  what's happening to the buffercache at any given time without having to
  restart or rebuild the server with debugging code added. The intent is to 
  do for the buffercache what pg_locks does for locks.
 </para>
 <para>
  This module consists of a C function <literal>pg_buffercache_pages()</literal> 
  that returns a set of records, plus a view <literal>pg_buffercache</literal> 
  to wrapper the function.
 </para>
 <para>
  By default public access is REVOKED from both of these, just in case there
  are security issues lurking.
 </para>

 <sect2>
  <title>Notes</title>
  <para>
   The definition of the columns exposed in the view is:
  </para>
  <programlisting>
       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?
   usagecount     |                      | Page LRU count
  </programlisting>
  <para>
   There is one row for each buffer in the shared cache. Unused buffers are
   shown with all fields null except bufferid.
  </para>
  <para>
   Because the cache is shared by all the databases, there are pages from
   relations not belonging to the current database.
  </para>
  <para>
   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.
  </para>
 </sect2>

 <sect2>
  <title>Sample output</title>
  <programlisting>
  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
               ON b.relfilenode = c.relfilenode INNER JOIN pg_database d
               ON (b.reldatabase = d.oid AND d.datname = current_database())
               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=# 
  </programlisting>
 </sect2>

 <sect2>
  <title>Authors</title>
  <itemizedlist>
   <listitem>
    <para>
     Mark Kirkwood <email>markir@paradise.net.nz</email>
    </para>
   </listitem>
   <listitem>
    <para>Design suggestions: Neil Conway <email>neilc@samurai.com</email></para>
   </listitem>
   <listitem>
    <para>Debugging advice: Tom Lane <email>tgl@sss.pgh.pa.us</email></para>
   </listitem>
  </itemizedlist>
 </sect2>

</sect1>