doc/src/sgml/pgbuffercache.sgml

   1 <!-- doc/src/sgml/pgbuffercache.sgml -->
   2
   3 <sect1 id="pgbuffercache" xreflabel="pg_buffercache">
   4  <title>pg_buffercache &mdash; inspect <productname>PostgreSQL</productname>
   5     buffer cache state</title>
   6
   7  <indexterm zone="pgbuffercache">
   8   <primary>pg_buffercache</primary>
   9  </indexterm>
  10
  11  <para>
  12   The <filename>pg_buffercache</filename> module provides a means for
  13   examining what's happening in the shared buffer cache in real time.
  14   It also offers a low-level way to evict data from it, for testing
  15   purposes.
  16  </para>
  17
  18  <indexterm>
  19   <primary>pg_buffercache_pages</primary>
  20  </indexterm>
  21
  22  <indexterm>
  23   <primary>pg_buffercache_summary</primary>
  24  </indexterm>
  25
  26  <indexterm>
  27   <primary>pg_buffercache_evict</primary>
  28  </indexterm>
  29
  30  <para>
  31   This module provides the <function>pg_buffercache_pages()</function>
  32   function (wrapped in the <structname>pg_buffercache</structname> view),
  33   the <function>pg_buffercache_summary()</function> function, the
  34   <function>pg_buffercache_usage_counts()</function> function and
  35   the <function>pg_buffercache_evict()</function> function.
  36  </para>
  37
  38  <para>
  39   The <function>pg_buffercache_pages()</function> function returns a set of
  40   records, each row describing the state of one shared buffer entry. The
  41   <structname>pg_buffercache</structname> view wraps the function for
  42   convenient use.
  43  </para>
  44
  45  <para>
  46   The <function>pg_buffercache_summary()</function> function returns a single
  47   row summarizing the state of the shared buffer cache.
  48  </para>
  49
  50  <para>
  51   The <function>pg_buffercache_usage_counts()</function> function returns a set
  52   of records, each row describing the number of buffers with a given usage
  53   count.
  54  </para>
  55
  56  <para>
  57   By default, use of the above functions is restricted to superusers and roles
  58   with privileges of the <literal>pg_monitor</literal> role. Access may be
  59   granted to others using <command>GRANT</command>.
  60  </para>
  61
  62  <para>
  63   The <function>pg_buffercache_evict()</function> function allows a block to
  64   be evicted from the buffer pool given a buffer identifier.  Use of this
  65   function is restricted to superusers only.
  66  </para>
  67
  68  <sect2 id="pgbuffercache-pg-buffercache">
  69   <title>The <structname>pg_buffercache</structname> View</title>
  70
  71   <para>
  72    The definitions of the columns exposed by the view are shown in <xref linkend="pgbuffercache-columns"/>.
  73   </para>
  74
  75   <table id="pgbuffercache-columns">
  76    <title><structname>pg_buffercache</structname> Columns</title>
  77    <tgroup cols="1">
  78     <thead>
  79      <row>
  80       <entry role="catalog_table_entry"><para role="column_definition">
  81        Column Type
  82       </para>
  83       <para>
  84        Description
  85       </para></entry>
  86      </row>
  87     </thead>
  88
  89     <tbody>
  90      <row>
  91       <entry role="catalog_table_entry"><para role="column_definition">
  92        <structfield>bufferid</structfield> <type>integer</type>
  93       </para>
  94       <para>
  95        ID, in the range 1..<varname>shared_buffers</varname>
  96       </para></entry>
  97      </row>
  98
  99      <row>
 100       <entry role="catalog_table_entry"><para role="column_definition">
 101        <structfield>relfilenode</structfield> <type>oid</type>
 102        (references <link linkend="catalog-pg-class"><structname>pg_class</structname></link>.<structfield>relfilenode</structfield>)
 103       </para>
 104       <para>
 105        Filenode number of the relation
 106       </para></entry>
 107      </row>
 108
 109      <row>
 110       <entry role="catalog_table_entry"><para role="column_definition">
 111        <structfield>reltablespace</structfield> <type>oid</type>
 112        (references <link linkend="catalog-pg-tablespace"><structname>pg_tablespace</structname></link>.<structfield>oid</structfield>)
 113       </para>
 114       <para>
 115        Tablespace OID of the relation
 116       </para></entry>
 117      </row>
 118
 119      <row>
 120       <entry role="catalog_table_entry"><para role="column_definition">
 121        <structfield>reldatabase</structfield> <type>oid</type>
 122        (references <link linkend="catalog-pg-database"><structname>pg_database</structname></link>.<structfield>oid</structfield>)
 123       </para>
 124       <para>
 125        Database OID of the relation
 126       </para></entry>
 127      </row>
 128
 129      <row>
 130       <entry role="catalog_table_entry"><para role="column_definition">
 131        <structfield>relforknumber</structfield> <type>smallint</type>
 132       </para>
 133       <para>
 134        Fork number within the relation;  see
 135        <filename>common/relpath.h</filename>
 136       </para></entry>
 137      </row>
 138
 139      <row>
 140       <entry role="catalog_table_entry"><para role="column_definition">
 141        <structfield>relblocknumber</structfield> <type>bigint</type>
 142       </para>
 143       <para>
 144        Page number within the relation
 145       </para></entry>
 146      </row>
 147
 148      <row>
 149       <entry role="catalog_table_entry"><para role="column_definition">
 150        <structfield>isdirty</structfield> <type>boolean</type>
 151       </para>
 152       <para>
 153        Is the page dirty?
 154       </para></entry>
 155      </row>
 156
 157      <row>
 158       <entry role="catalog_table_entry"><para role="column_definition">
 159        <structfield>usagecount</structfield> <type>smallint</type>
 160       </para>
 161       <para>
 162        Clock-sweep access count
 163       </para></entry>
 164      </row>
 165
 166      <row>
 167       <entry role="catalog_table_entry"><para role="column_definition">
 168        <structfield>pinning_backends</structfield> <type>integer</type>
 169       </para>
 170       <para>
 171        Number of backends pinning this buffer
 172       </para></entry>
 173      </row>
 174     </tbody>
 175    </tgroup>
 176   </table>
 177
 178   <para>
 179    There is one row for each buffer in the shared cache. Unused buffers are
 180    shown with all fields null except <structfield>bufferid</structfield>.  Shared system
 181    catalogs are shown as belonging to database zero.
 182   </para>
 183
 184   <para>
 185    Because the cache is shared by all the databases, there will normally be
 186    pages from relations not belonging to the current database.  This means
 187    that there may not be matching join rows in <structname>pg_class</structname> for
 188    some rows, or that there could even be incorrect joins.  If you are
 189    trying to join against <structname>pg_class</structname>, it's a good idea to
 190    restrict the join to rows having <structfield>reldatabase</structfield> equal to
 191    the current database's OID or zero.
 192   </para>
 193
 194   <para>
 195    Since buffer manager locks are not taken to copy the buffer state data that
 196    the view will display, accessing <structname>pg_buffercache</structname> view
 197    has less impact on normal buffer activity but it doesn't provide a consistent
 198    set of results across all buffers.  However, we ensure that the information of
 199    each buffer is self-consistent.
 200   </para>
 201  </sect2>
 202
 203  <sect2 id="pgbuffercache-summary">
 204   <title>The <function>pg_buffercache_summary()</function> Function</title>
 205
 206   <para>
 207    The definitions of the columns exposed by the function are shown in <xref linkend="pgbuffercache-summary-columns"/>.
 208   </para>
 209
 210   <table id="pgbuffercache-summary-columns">
 211    <title><function>pg_buffercache_summary()</function> Output Columns</title>
 212    <tgroup cols="1">
 213     <thead>
 214      <row>
 215       <entry role="catalog_table_entry"><para role="column_definition">
 216        Column Type
 217       </para>
 218       <para>
 219        Description
 220       </para></entry>
 221      </row>
 222     </thead>
 223
 224     <tbody>
 225      <row>
 226       <entry role="catalog_table_entry"><para role="column_definition">
 227        <structfield>buffers_used</structfield> <type>int4</type>
 228       </para>
 229       <para>
 230        Number of used shared buffers
 231       </para></entry>
 232      </row>
 233
 234      <row>
 235       <entry role="catalog_table_entry"><para role="column_definition">
 236        <structfield>buffers_unused</structfield> <type>int4</type>
 237       </para>
 238       <para>
 239        Number of unused shared buffers
 240       </para></entry>
 241      </row>
 242
 243      <row>
 244       <entry role="catalog_table_entry"><para role="column_definition">
 245        <structfield>buffers_dirty</structfield> <type>int4</type>
 246       </para>
 247       <para>
 248        Number of dirty shared buffers
 249       </para></entry>
 250      </row>
 251
 252      <row>
 253       <entry role="catalog_table_entry"><para role="column_definition">
 254        <structfield>buffers_pinned</structfield> <type>int4</type>
 255       </para>
 256       <para>
 257        Number of pinned shared buffers
 258       </para></entry>
 259      </row>
 260
 261      <row>
 262       <entry role="catalog_table_entry"><para role="column_definition">
 263        <structfield>usagecount_avg</structfield> <type>float8</type>
 264       </para>
 265       <para>
 266        Average usage count of used shared buffers
 267       </para></entry>
 268      </row>
 269     </tbody>
 270    </tgroup>
 271   </table>
 272
 273   <para>
 274    The <function>pg_buffercache_summary()</function> function returns a
 275    single row summarizing the state of all shared buffers. Similar and more
 276    detailed information is provided by the
 277    <structname>pg_buffercache</structname> view, but
 278    <function>pg_buffercache_summary()</function> is significantly cheaper.
 279   </para>
 280
 281   <para>
 282    Like the <structname>pg_buffercache</structname> view,
 283    <function>pg_buffercache_summary()</function> does not acquire buffer
 284    manager locks. Therefore concurrent activity can lead to minor inaccuracies
 285    in the result.
 286   </para>
 287  </sect2>
 288
 289  <sect2 id="pgbuffercache-usage-counts">
 290   <title>The <function>pg_buffercache_usage_counts()</function> Function</title>
 291
 292   <para>
 293    The definitions of the columns exposed by the function are shown in
 294    <xref linkend="pgbuffercache_usage_counts-columns"/>.
 295   </para>
 296
 297   <table id="pgbuffercache_usage_counts-columns">
 298    <title><function>pg_buffercache_usage_counts()</function> Output Columns</title>
 299    <tgroup cols="1">
 300     <thead>
 301      <row>
 302       <entry role="catalog_table_entry"><para role="column_definition">
 303        Column Type
 304       </para>
 305       <para>
 306        Description
 307       </para></entry>
 308      </row>
 309     </thead>
 310
 311     <tbody>
 312      <row>
 313       <entry role="catalog_table_entry"><para role="column_definition">
 314        <structfield>usage_count</structfield> <type>int4</type>
 315       </para>
 316       <para>
 317        A possible buffer usage count
 318       </para></entry>
 319      </row>
 320
 321      <row>
 322       <entry role="catalog_table_entry"><para role="column_definition">
 323        <structfield>buffers</structfield> <type>int4</type>
 324       </para>
 325       <para>
 326        Number of buffers with the usage count
 327       </para></entry>
 328      </row>
 329
 330      <row>
 331       <entry role="catalog_table_entry"><para role="column_definition">
 332        <structfield>dirty</structfield> <type>int4</type>
 333       </para>
 334       <para>
 335        Number of dirty buffers with the usage count
 336       </para></entry>
 337      </row>
 338
 339      <row>
 340       <entry role="catalog_table_entry"><para role="column_definition">
 341        <structfield>pinned</structfield> <type>int4</type>
 342       </para>
 343       <para>
 344        Number of pinned buffers with the usage count
 345       </para></entry>
 346      </row>
 347     </tbody>
 348    </tgroup>
 349   </table>
 350
 351   <para>
 352    The <function>pg_buffercache_usage_counts()</function> function returns a
 353    set of rows summarizing the states of all shared buffers, aggregated over
 354    the possible usage count values.  Similar and more detailed information is
 355    provided by the <structname>pg_buffercache</structname> view, but
 356    <function>pg_buffercache_usage_counts()</function> is significantly cheaper.
 357   </para>
 358
 359   <para>
 360    Like the <structname>pg_buffercache</structname> view,
 361    <function>pg_buffercache_usage_counts()</function> does not acquire buffer
 362    manager locks. Therefore concurrent activity can lead to minor inaccuracies
 363    in the result.
 364   </para>
 365  </sect2>
 366
 367  <sect2 id="pgbuffercache-pg-buffercache-evict">
 368   <title>The <structname>pg_buffercache_evict</structname> Function</title>
 369   <para>
 370    The <function>pg_buffercache_evict()</function> function takes a buffer
 371    identifier, as shown in the <structfield>bufferid</structfield> column of
 372    the <structname>pg_buffercache</structname> view.  It returns true on success,
 373    and false if the buffer wasn't valid, if it couldn't be evicted because it
 374    was pinned, or if it became dirty again after an attempt to write it out.
 375    The result is immediately out of date upon return, as the buffer might
 376    become valid again at any time due to concurrent activity.  The function is
 377    intended for developer testing only.
 378   </para>
 379  </sect2>
 380
 381 <sect2 id="pgbuffercache-sample-output">
 382   <title>Sample Output</title>
 383
 384 <screen>
 385 regression=# SELECT n.nspname, c.relname, count(*) AS buffers
 386              FROM pg_buffercache b JOIN pg_class c
 387              ON b.relfilenode = pg_relation_filenode(c.oid) AND
 388                 b.reldatabase IN (0, (SELECT oid FROM pg_database
 389                                       WHERE datname = current_database()))
 390              JOIN pg_namespace n ON n.oid = c.relnamespace
 391              GROUP BY n.nspname, c.relname
 392              ORDER BY 3 DESC
 393              LIMIT 10;
 394
 395   nspname   |        relname         | buffers
 396 ------------+------------------------+---------
 397  public     | delete_test_table      |     593
 398  public     | delete_test_table_pkey |     494
 399  pg_catalog | pg_attribute           |     472
 400  public     | quad_poly_tbl          |     353
 401  public     | tenk2                  |     349
 402  public     | tenk1                  |     349
 403  public     | gin_test_idx           |     306
 404  pg_catalog | pg_largeobject         |     206
 405  public     | gin_test_tbl           |     188
 406  public     | spgist_text_tbl        |     182
 407 (10 rows)
 408
 409
 410 regression=# SELECT * FROM pg_buffercache_summary();
 411  buffers_used | buffers_unused | buffers_dirty | buffers_pinned | usagecount_avg
 412 --------------+----------------+---------------+----------------+----------------
 413           248 |        2096904 |            39 |              0 |       3.141129
 414 (1 row)
 415
 416
 417 regression=# SELECT * FROM pg_buffercache_usage_counts();
 418  usage_count | buffers | dirty | pinned
 419 -------------+---------+-------+--------
 420            0 |   14650 |     0 |      0
 421            1 |    1436 |   671 |      0
 422            2 |     102 |    88 |      0
 423            3 |      23 |    21 |      0
 424            4 |       9 |     7 |      0
 425            5 |     164 |   106 |      0
 426 (6 rows)
 427 </screen>
 428  </sect2>
 429
 430  <sect2 id="pgbuffercache-authors">
 431   <title>Authors</title>
 432
 433   <para>
 434    Mark Kirkwood <email>markir@paradise.net.nz</email>
 435   </para>
 436
 437   <para>
 438    Design suggestions: Neil Conway <email>neilc@samurai.com</email>
 439   </para>
 440
 441   <para>
 442    Debugging advice: Tom Lane <email>tgl@sss.pgh.pa.us</email>
 443   </para>
 444  </sect2>
 445
 446 </sect1>