codekingpro/portable-devtools
115k
1<?xml version="1.0" encoding="UTF-8" standalone="no"?>2<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"><html xmlns="http://www.w3.org/1999/xhtml"><head><meta http-equiv="Content-Type" content="text/html; charset=UTF-8" /><title>F.27. pg_buffercache — inspect PostgreSQL buffer cache state</title><link rel="stylesheet" type="text/css" href="stylesheet.css" /><link rev="made" href="pgsql-docs@lists.postgresql.org" /><meta name="generator" content="DocBook XSL Stylesheets Vsnapshot" /><link rel="prev" href="passwordcheck.html" title="F.26. passwordcheck — verify password strength" /><link rel="next" href="pgcrypto.html" title="F.28. pgcrypto — cryptographic functions" /></head><body id="docContent" class="container-fluid col-10"><div class="navheader"><table width="100%" summary="Navigation header"><tr><th colspan="5" align="center">F.27. pg_buffercache — inspect <span class="productname">PostgreSQL</span>3 buffer cache state</th></tr><tr><td width="10%" align="left"><a accesskey="p" href="passwordcheck.html" title="F.26. passwordcheck — verify password strength">Prev</a> </td><td width="10%" align="left"><a accesskey="u" href="contrib.html" title="Appendix F. Additional Supplied Modules and Extensions">Up</a></td><th width="60%" align="center">Appendix F. Additional Supplied Modules and Extensions</th><td width="10%" align="right"><a accesskey="h" href="index.html" title="PostgreSQL 16.3 Documentation">Home</a></td><td width="10%" align="right"> <a accesskey="n" href="pgcrypto.html" title="F.28. pgcrypto — cryptographic functions">Next</a></td></tr></table><hr /></div><div class="sect1" id="PGBUFFERCACHE"><div class="titlepage"><div><div><h2 class="title" style="clear: both">F.27. pg_buffercache — inspect <span class="productname">PostgreSQL</span>4 buffer cache state <a href="#PGBUFFERCACHE" class="id_link">#</a></h2></div></div></div><div class="toc"><dl class="toc"><dt><span class="sect2"><a href="pgbuffercache.html#PGBUFFERCACHE-PG-BUFFERCACHE">F.27.1. The <code class="structname">pg_buffercache</code> View</a></span></dt><dt><span class="sect2"><a href="pgbuffercache.html#PGBUFFERCACHE-SUMMARY">F.27.2. The <code class="function">pg_buffercache_summary()</code> Function</a></span></dt><dt><span class="sect2"><a href="pgbuffercache.html#PGBUFFERCACHE-USAGE-COUNTS">F.27.3. The <code class="function">pg_buffercache_usage_counts()</code> Function</a></span></dt><dt><span class="sect2"><a href="pgbuffercache.html#PGBUFFERCACHE-SAMPLE-OUTPUT">F.27.4. Sample Output</a></span></dt><dt><span class="sect2"><a href="pgbuffercache.html#PGBUFFERCACHE-AUTHORS">F.27.5. Authors</a></span></dt></dl></div><a id="id-1.11.7.37.2" class="indexterm"></a><p>5 The <code class="filename">pg_buffercache</code> module provides a means for6 examining what's happening in the shared buffer cache in real time.7 </p><a id="id-1.11.7.37.4" class="indexterm"></a><a id="id-1.11.7.37.5" class="indexterm"></a><p>8 This module provides the <code class="function">pg_buffercache_pages()</code>9 function (wrapped in the <code class="structname">pg_buffercache</code> view),10 the <code class="function">pg_buffercache_summary()</code> function, and the11 <code class="function">pg_buffercache_usage_counts()</code> function.12 </p><p>13 The <code class="function">pg_buffercache_pages()</code> function returns a set of14 records, each row describing the state of one shared buffer entry. The15 <code class="structname">pg_buffercache</code> view wraps the function for16 convenient use.17 </p><p>18 The <code class="function">pg_buffercache_summary()</code> function returns a single19 row summarizing the state of the shared buffer cache.20 </p><p>21 The <code class="function">pg_buffercache_usage_counts()</code> function returns a set22 of records, each row describing the number of buffers with a given usage23 count.24 </p><p>25 By default, use is restricted to superusers and roles with privileges of the26 <code class="literal">pg_monitor</code> role. Access may be granted to others27 using <code class="command">GRANT</code>.28 </p><div class="sect2" id="PGBUFFERCACHE-PG-BUFFERCACHE"><div class="titlepage"><div><div><h3 class="title">F.27.1. The <code class="structname">pg_buffercache</code> View <a href="#PGBUFFERCACHE-PG-BUFFERCACHE" class="id_link">#</a></h3></div></div></div><p>29 The definitions of the columns exposed by the view are shown in <a class="xref" href="pgbuffercache.html#PGBUFFERCACHE-COLUMNS" title="Table F.15. pg_buffercache Columns">Table F.15</a>.30 </p><div class="table" id="PGBUFFERCACHE-COLUMNS"><p class="title"><strong>Table F.15. <code class="structname">pg_buffercache</code> Columns</strong></p><div class="table-contents"><table class="table" summary="pg_buffercache Columns" border="1"><colgroup><col /></colgroup><thead><tr><th class="catalog_table_entry"><p class="column_definition">31 Column Type32 </p>33 <p>34 Description35 </p></th></tr></thead><tbody><tr><td class="catalog_table_entry"><p class="column_definition">36 <code class="structfield">bufferid</code> <code class="type">integer</code>37 </p>38 <p>39 ID, in the range 1..<code class="varname">shared_buffers</code>40 </p></td></tr><tr><td class="catalog_table_entry"><p class="column_definition">41 <code class="structfield">relfilenode</code> <code class="type">oid</code>42 (references <a class="link" href="catalog-pg-class.html" title="53.11. pg_class"><code class="structname">pg_class</code></a>.<code class="structfield">relfilenode</code>)43 </p>44 <p>45 Filenode number of the relation46 </p></td></tr><tr><td class="catalog_table_entry"><p class="column_definition">47 <code class="structfield">reltablespace</code> <code class="type">oid</code>48 (references <a class="link" href="catalog-pg-tablespace.html" title="53.56. pg_tablespace"><code class="structname">pg_tablespace</code></a>.<code class="structfield">oid</code>)49 </p>50 <p>51 Tablespace OID of the relation52 </p></td></tr><tr><td class="catalog_table_entry"><p class="column_definition">53 <code class="structfield">reldatabase</code> <code class="type">oid</code>54 (references <a class="link" href="catalog-pg-database.html" title="53.15. pg_database"><code class="structname">pg_database</code></a>.<code class="structfield">oid</code>)55 </p>56 <p>57 Database OID of the relation58 </p></td></tr><tr><td class="catalog_table_entry"><p class="column_definition">59 <code class="structfield">relforknumber</code> <code class="type">smallint</code>60 </p>61 <p>62 Fork number within the relation; see63 <code class="filename">common/relpath.h</code>64 </p></td></tr><tr><td class="catalog_table_entry"><p class="column_definition">65 <code class="structfield">relblocknumber</code> <code class="type">bigint</code>66 </p>67 <p>68 Page number within the relation69 </p></td></tr><tr><td class="catalog_table_entry"><p class="column_definition">70 <code class="structfield">isdirty</code> <code class="type">boolean</code>71 </p>72 <p>73 Is the page dirty?74 </p></td></tr><tr><td class="catalog_table_entry"><p class="column_definition">75 <code class="structfield">usagecount</code> <code class="type">smallint</code>76 </p>77 <p>78 Clock-sweep access count79 </p></td></tr><tr><td class="catalog_table_entry"><p class="column_definition">80 <code class="structfield">pinning_backends</code> <code class="type">integer</code>81 </p>82 <p>83 Number of backends pinning this buffer84 </p></td></tr></tbody></table></div></div><br class="table-break" /><p>85 There is one row for each buffer in the shared cache. Unused buffers are86 shown with all fields null except <code class="structfield">bufferid</code>. Shared system87 catalogs are shown as belonging to database zero.88 </p><p>89 Because the cache is shared by all the databases, there will normally be90 pages from relations not belonging to the current database. This means91 that there may not be matching join rows in <code class="structname">pg_class</code> for92 some rows, or that there could even be incorrect joins. If you are93 trying to join against <code class="structname">pg_class</code>, it's a good idea to94 restrict the join to rows having <code class="structfield">reldatabase</code> equal to95 the current database's OID or zero.96 </p><p>97 Since buffer manager locks are not taken to copy the buffer state data that98 the view will display, accessing <code class="structname">pg_buffercache</code> view99 has less impact on normal buffer activity but it doesn't provide a consistent100 set of results across all buffers. However, we ensure that the information of101 each buffer is self-consistent.102 </p></div><div class="sect2" id="PGBUFFERCACHE-SUMMARY"><div class="titlepage"><div><div><h3 class="title">F.27.2. The <code class="function">pg_buffercache_summary()</code> Function <a href="#PGBUFFERCACHE-SUMMARY" class="id_link">#</a></h3></div></div></div><p>103 The definitions of the columns exposed by the function are shown in <a class="xref" href="pgbuffercache.html#PGBUFFERCACHE-SUMMARY-COLUMNS" title="Table F.16. pg_buffercache_summary() Output Columns">Table F.16</a>.104 </p><div class="table" id="PGBUFFERCACHE-SUMMARY-COLUMNS"><p class="title"><strong>Table F.16. <code class="function">pg_buffercache_summary()</code> Output Columns</strong></p><div class="table-contents"><table class="table" summary="pg_buffercache_summary() Output Columns" border="1"><colgroup><col /></colgroup><thead><tr><th class="catalog_table_entry"><p class="column_definition">105 Column Type106 </p>107 <p>108 Description109 </p></th></tr></thead><tbody><tr><td class="catalog_table_entry"><p class="column_definition">110 <code class="structfield">buffers_used</code> <code class="type">int4</code>111 </p>112 <p>113 Number of used shared buffers114 </p></td></tr><tr><td class="catalog_table_entry"><p class="column_definition">115 <code class="structfield">buffers_unused</code> <code class="type">int4</code>116 </p>117 <p>118 Number of unused shared buffers119 </p></td></tr><tr><td class="catalog_table_entry"><p class="column_definition">120 <code class="structfield">buffers_dirty</code> <code class="type">int4</code>121 </p>122 <p>123 Number of dirty shared buffers124 </p></td></tr><tr><td class="catalog_table_entry"><p class="column_definition">125 <code class="structfield">buffers_pinned</code> <code class="type">int4</code>126 </p>127 <p>128 Number of pinned shared buffers129 </p></td></tr><tr><td class="catalog_table_entry"><p class="column_definition">130 <code class="structfield">usagecount_avg</code> <code class="type">float8</code>131 </p>132 <p>133 Average usage count of used shared buffers134 </p></td></tr></tbody></table></div></div><br class="table-break" /><p>135 The <code class="function">pg_buffercache_summary()</code> function returns a136 single row summarizing the state of all shared buffers. Similar and more137 detailed information is provided by the138 <code class="structname">pg_buffercache</code> view, but139 <code class="function">pg_buffercache_summary()</code> is significantly cheaper.140 </p><p>141 Like the <code class="structname">pg_buffercache</code> view,142 <code class="function">pg_buffercache_summary()</code> does not acquire buffer143 manager locks. Therefore concurrent activity can lead to minor inaccuracies144 in the result.145 </p></div><div class="sect2" id="PGBUFFERCACHE-USAGE-COUNTS"><div class="titlepage"><div><div><h3 class="title">F.27.3. The <code class="function">pg_buffercache_usage_counts()</code> Function <a href="#PGBUFFERCACHE-USAGE-COUNTS" class="id_link">#</a></h3></div></div></div><p>146 The definitions of the columns exposed by the function are shown in147 <a class="xref" href="pgbuffercache.html#PGBUFFERCACHE_USAGE_COUNTS-COLUMNS" title="Table F.17. pg_buffercache_usage_counts() Output Columns">Table F.17</a>.148 </p><div class="table" id="PGBUFFERCACHE_USAGE_COUNTS-COLUMNS"><p class="title"><strong>Table F.17. <code class="function">pg_buffercache_usage_counts()</code> Output Columns</strong></p><div class="table-contents"><table class="table" summary="pg_buffercache_usage_counts() Output Columns" border="1"><colgroup><col /></colgroup><thead><tr><th class="catalog_table_entry"><p class="column_definition">149 Column Type150 </p>151 <p>152 Description153 </p></th></tr></thead><tbody><tr><td class="catalog_table_entry"><p class="column_definition">154 <code class="structfield">usage_count</code> <code class="type">int4</code>155 </p>156 <p>157 A possible buffer usage count158 </p></td></tr><tr><td class="catalog_table_entry"><p class="column_definition">159 <code class="structfield">buffers</code> <code class="type">int4</code>160 </p>161 <p>162 Number of buffers with the usage count163 </p></td></tr><tr><td class="catalog_table_entry"><p class="column_definition">164 <code class="structfield">dirty</code> <code class="type">int4</code>165 </p>166 <p>167 Number of dirty buffers with the usage count168 </p></td></tr><tr><td class="catalog_table_entry"><p class="column_definition">169 <code class="structfield">pinned</code> <code class="type">int4</code>170 </p>171 <p>172 Number of pinned buffers with the usage count173 </p></td></tr></tbody></table></div></div><br class="table-break" /><p>174 The <code class="function">pg_buffercache_usage_counts()</code> function returns a175 set of rows summarizing the states of all shared buffers, aggregated over176 the possible usage count values. Similar and more detailed information is177 provided by the <code class="structname">pg_buffercache</code> view, but178 <code class="function">pg_buffercache_usage_counts()</code> is significantly cheaper.179 </p><p>180 Like the <code class="structname">pg_buffercache</code> view,181 <code class="function">pg_buffercache_usage_counts()</code> does not acquire buffer182 manager locks. Therefore concurrent activity can lead to minor inaccuracies183 in the result.184 </p></div><div class="sect2" id="PGBUFFERCACHE-SAMPLE-OUTPUT"><div class="titlepage"><div><div><h3 class="title">F.27.4. Sample Output <a href="#PGBUFFERCACHE-SAMPLE-OUTPUT" class="id_link">#</a></h3></div></div></div><pre class="screen">185regression=# SELECT n.nspname, c.relname, count(*) AS buffers186 FROM pg_buffercache b JOIN pg_class c187 ON b.relfilenode = pg_relation_filenode(c.oid) AND188 b.reldatabase IN (0, (SELECT oid FROM pg_database189 WHERE datname = current_database()))190 JOIN pg_namespace n ON n.oid = c.relnamespace191 GROUP BY n.nspname, c.relname192 ORDER BY 3 DESC193 LIMIT 10;194 195 nspname | relname | buffers196------------+------------------------+---------197 public | delete_test_table | 593198 public | delete_test_table_pkey | 494199 pg_catalog | pg_attribute | 472200 public | quad_poly_tbl | 353201 public | tenk2 | 349202 public | tenk1 | 349203 public | gin_test_idx | 306204 pg_catalog | pg_largeobject | 206205 public | gin_test_tbl | 188206 public | spgist_text_tbl | 182207(10 rows)208 209 210regression=# SELECT * FROM pg_buffercache_summary();211 buffers_used | buffers_unused | buffers_dirty | buffers_pinned | usagecount_avg212--------------+----------------+---------------+----------------+----------------213 248 | 2096904 | 39 | 0 | 3.141129214(1 row)215 216 217regression=# SELECT * FROM pg_buffercache_usage_counts();218 usage_count | buffers | dirty | pinned219-------------+---------+-------+--------220 0 | 14650 | 0 | 0221 1 | 1436 | 671 | 0222 2 | 102 | 88 | 0223 3 | 23 | 21 | 0224 4 | 9 | 7 | 0225 5 | 164 | 106 | 0226(6 rows)227</pre></div><div class="sect2" id="PGBUFFERCACHE-AUTHORS"><div class="titlepage"><div><div><h3 class="title">F.27.5. Authors <a href="#PGBUFFERCACHE-AUTHORS" class="id_link">#</a></h3></div></div></div><p>228 Mark Kirkwood <code class="email"><<a class="email" href="mailto:markir@paradise.net.nz">markir@paradise.net.nz</a>></code>229 </p><p>230 Design suggestions: Neil Conway <code class="email"><<a class="email" href="mailto:neilc@samurai.com">neilc@samurai.com</a>></code>231 </p><p>232 Debugging advice: Tom Lane <code class="email"><<a class="email" href="mailto:tgl@sss.pgh.pa.us">tgl@sss.pgh.pa.us</a>></code>233 </p></div></div><div class="navfooter"><hr /><table width="100%" summary="Navigation footer"><tr><td width="40%" align="left"><a accesskey="p" href="passwordcheck.html" title="F.26. passwordcheck — verify password strength">Prev</a> </td><td width="20%" align="center"><a accesskey="u" href="contrib.html" title="Appendix F. Additional Supplied Modules and Extensions">Up</a></td><td width="40%" align="right"> <a accesskey="n" href="pgcrypto.html" title="F.28. pgcrypto — cryptographic functions">Next</a></td></tr><tr><td width="40%" align="left" valign="top">F.26. passwordcheck — verify password strength </td><td width="20%" align="center"><a accesskey="h" href="index.html" title="PostgreSQL 16.3 Documentation">Home</a></td><td width="40%" align="right" valign="top"> F.28. pgcrypto — cryptographic functions</td></tr></table></div></body></html>