doc/src/sgml/pgbuffercache.sgml

   1 <!-- $PostgreSQL$ -->
   2
   3 <sect1 id="pgbuffercache">
   4  <title>pg_buffercache</title>
   5
   6  <indexterm zone="pgbuffercache">
   7   <primary>pg_buffercache</primary>
   8  </indexterm>
   9
  10  <para>
  11   The <filename>pg_buffercache</filename> module provides a means for
  12   examining what's happening in the shared buffer cache in real time.
  13  </para>
  14
  15  <para>
  16   The module provides a C function <function>pg_buffercache_pages</function>
  17   that returns a set of records, plus a view
  18   <structname>pg_buffercache</structname> that wraps the function for
  19   convenient use.
  20  </para>
  21
  22  <para>
  23   By default public access is revoked from both of these, just in case there
  24   are security issues lurking.
  25  </para>
  26
  27  <sect2>
  28   <title>The <structname>pg_buffercache</structname> view</title>
  29
  30   <para>
  31    The definitions of the columns exposed by the view are shown in <xref linkend="pgbuffercache-columns">.
  32   </para>
  33
  34   <table id="pgbuffercache-columns">
  35    <title><structname>pg_buffercache</> Columns</title>
  36
  37    <tgroup cols="4">
  38     <thead>
  39      <row>
  40       <entry>Name</entry>
  41       <entry>Type</entry>
  42       <entry>References</entry>
  43       <entry>Description</entry>
  44      </row>
  45     </thead>
  46     <tbody>
  47
  48      <row>
  49       <entry><structfield>bufferid</structfield></entry>
  50       <entry><type>integer</type></entry>
  51       <entry></entry>
  52       <entry>ID, in the range 1..<varname>shared_buffers</></entry>
  53      </row>
  54
  55      <row>
  56       <entry><structfield>relfilenode</structfield></entry>
  57       <entry><type>oid</type></entry>
  58       <entry><literal>pg_class.relfilenode</literal></entry>
  59       <entry>Relfilenode of the relation</entry>
  60      </row>
  61
  62      <row>
  63       <entry><structfield>reltablespace</structfield></entry>
  64       <entry><type>oid</type></entry>
  65       <entry><literal>pg_tablespace.oid</literal></entry>
  66       <entry>Tablespace OID of the relation</entry>
  67      </row>
  68
  69      <row>
  70       <entry><structfield>reldatabase</structfield></entry>
  71       <entry><type>oid</type></entry>
  72       <entry><literal>pg_database.oid</literal></entry>
  73       <entry>Database OID of the relation</entry>
  74      </row>
  75
  76      <row>
  77       <entry><structfield>relblocknumber</structfield></entry>
  78       <entry><type>bigint</type></entry>
  79       <entry></entry>
  80       <entry>Page number within the relation</entry>
  81      </row>
  82
  83      <row>
  84       <entry><structfield>relforknumber</structfield></entry>
  85       <entry><type>smallint</type></entry>
  86       <entry></entry>
  87       <entry>Fork number within the relation</entry>
  88      </row>
  89
  90      <row>
  91       <entry><structfield>isdirty</structfield></entry>
  92       <entry><type>boolean</type></entry>
  93       <entry></entry>
  94       <entry>Is the page dirty?</entry>
  95      </row>
  96
  97      <row>
  98       <entry><structfield>usagecount</structfield></entry>
  99       <entry><type>smallint</type></entry>
 100       <entry></entry>
 101       <entry>Page LRU count</entry>
 102      </row>
 103
 104     </tbody>
 105    </tgroup>
 106   </table>
 107
 108   <para>
 109    There is one row for each buffer in the shared cache. Unused buffers are
 110    shown with all fields null except <structfield>bufferid</>.  Shared system
 111    catalogs are shown as belonging to database zero.
 112   </para>
 113
 114   <para>
 115    Because the cache is shared by all the databases, there will normally be
 116    pages from relations not belonging to the current database.  This means
 117    that there may not be matching join rows in <structname>pg_class</> for
 118    some rows, or that there could even be incorrect joins.  If you are
 119    trying to join against <structname>pg_class</>, it's a good idea to
 120    restrict the join to rows having <structfield>reldatabase</> equal to
 121    the current database's OID or zero.
 122   </para>
 123
 124   <para>
 125    When the <structname>pg_buffercache</> view is accessed, internal buffer
 126    manager locks are taken for long enough to copy all the buffer state
 127    data that the view will display.
 128    This ensures that the view produces a consistent set of results, while not
 129    blocking normal buffer activity longer than necessary.  Nonetheless there
 130    could be some impact on database performance if this view is read often.
 131   </para>
 132  </sect2>
 133
 134  <sect2>
 135   <title>Sample output</title>
 136
 137   <programlisting>
 138   regression=# SELECT c.relname, count(*) AS buffers
 139                FROM pg_buffercache b INNER JOIN pg_class c
 140                ON b.relfilenode = c.relfilenode AND
 141                   b.reldatabase IN (0, (SELECT oid FROM pg_database
 142                                         WHERE datname = current_database()))
 143                GROUP BY c.relname
 144                ORDER BY 2 DESC
 145                LIMIT 10;
 146                relname             | buffers
 147   ---------------------------------+---------
 148    tenk2                           |     345
 149    tenk1                           |     141
 150    pg_proc                         |      46
 151    pg_class                        |      45
 152    pg_attribute                    |      43
 153    pg_class_relname_nsp_index      |      30
 154    pg_proc_proname_args_nsp_index  |      28
 155    pg_attribute_relid_attnam_index |      26
 156    pg_depend                       |      22
 157    pg_depend_reference_index       |      20
 158   (10 rows)
 159   </programlisting>
 160  </sect2>
 161
 162  <sect2>
 163   <title>Authors</title>
 164
 165   <para>
 166    Mark Kirkwood <email>markir@paradise.net.nz</email>
 167   </para>
 168
 169   <para>
 170    Design suggestions: Neil Conway <email>neilc@samurai.com</email>
 171   </para>
 172
 173   <para>
 174    Debugging advice: Tom Lane <email>tgl@sss.pgh.pa.us</email>
 175   </para>
 176  </sect2>
 177
 178 </sect1>