diff options
Diffstat (limited to 'doc/src')
| -rw-r--r-- | doc/src/sgml/acronyms.sgml | 4 | ||||
| -rw-r--r-- | doc/src/sgml/config.sgml | 76 | ||||
| -rw-r--r-- | doc/src/sgml/pageinspect.sgml | 49 | ||||
| -rw-r--r-- | doc/src/sgml/pgfreespacemap.sgml | 283 | ||||
| -rw-r--r-- | doc/src/sgml/ref/vacuum.sgml | 8 | ||||
| -rw-r--r-- | doc/src/sgml/release.sgml | 5 | ||||
| -rw-r--r-- | doc/src/sgml/runtime.sgml | 12 | ||||
| -rw-r--r-- | doc/src/sgml/storage.sgml | 51 |
8 files changed, 179 insertions, 309 deletions
diff --git a/doc/src/sgml/acronyms.sgml b/doc/src/sgml/acronyms.sgml index c7c5f865d95..82d70de730c 100644 --- a/doc/src/sgml/acronyms.sgml +++ b/doc/src/sgml/acronyms.sgml @@ -1,4 +1,4 @@ -<!-- $PostgreSQL: pgsql/doc/src/sgml/acronyms.sgml,v 1.5 2008/03/18 16:05:07 mha Exp $ --> +<!-- $PostgreSQL: pgsql/doc/src/sgml/acronyms.sgml,v 1.6 2008/09/30 10:52:09 heikki Exp $ --> <appendix id="acronyms"> <title>Acronyms</title> @@ -216,7 +216,7 @@ <term><acronym>FSM</acronym></term> <listitem> <para> - <link linkend="runtime-config-resource-fsm">Free Space Map</link> + <link linkend="storage-fsm">Free Space Map</link> </para> </listitem> </varlistentry> diff --git a/doc/src/sgml/config.sgml b/doc/src/sgml/config.sgml index 9d33918a3ec..dfb976c4731 100644 --- a/doc/src/sgml/config.sgml +++ b/doc/src/sgml/config.sgml @@ -1,4 +1,4 @@ -<!-- $PostgreSQL: pgsql/doc/src/sgml/config.sgml,v 1.190 2008/08/25 19:03:37 tgl Exp $ --> +<!-- $PostgreSQL: pgsql/doc/src/sgml/config.sgml,v 1.191 2008/09/30 10:52:09 heikki Exp $ --> <chapter Id="runtime-config"> <title>Server Configuration</title> @@ -897,80 +897,6 @@ SET ENABLE_SEQSCAN TO OFF; </variablelist> </sect2> - <sect2 id="runtime-config-resource-fsm"> - <title>Free Space Map</title> - - <indexterm> - <primary>free space map</primary> - </indexterm> - - <para> - These parameters control the size of the shared <firstterm>free space - map</> (<acronym>FSM</>), which tracks the locations of unused space in the database. - An undersized free space map can cause the database to consume - increasing amounts of disk space over time, because free space that - is not in the map cannot be re-used; instead <productname>PostgreSQL</> - will request more disk space from the operating system when it needs - to store new data. - The last few lines displayed by a database-wide <command>VACUUM VERBOSE</> - command can help in determining if the current settings are adequate. - A <literal>NOTICE</> message is also printed during such an operation - if the current settings are too low. - </para> - - <para> - Increasing these parameters might cause <productname>PostgreSQL</> - to request more <systemitem class="osname">System V</> shared - memory than your operating system's default configuration - allows. See <xref linkend="sysvipc"> for information on how to - adjust those parameters, if necessary. - </para> - - <variablelist> - <varlistentry id="guc-max-fsm-pages" xreflabel="max_fsm_pages"> - <term><varname>max_fsm_pages</varname> (<type>integer</type>)</term> - <indexterm> - <primary><varname>max_fsm_pages</> configuration parameter</primary> - </indexterm> - <listitem> - <para> - Sets the maximum number of disk pages for which free space will - be tracked in the shared free-space map. Six bytes of shared memory - are consumed for each page slot. This setting must be at least - 16 * <varname>max_fsm_relations</varname>. The default is chosen - by <application>initdb</> depending on the amount of available memory, - and can range from 20k to 200k pages. - This parameter can only be set at server start. - </para> - </listitem> - </varlistentry> - - <varlistentry id="guc-max-fsm-relations" xreflabel="max_fsm_relations"> - <term><varname>max_fsm_relations</varname> (<type>integer</type>)</term> - <indexterm> - <primary><varname>max_fsm_relations</> configuration parameter</primary> - </indexterm> - <listitem> - <para> - Sets the maximum number of relations (tables and indexes) for which - free space will be tracked in the shared free-space map. Roughly - seventy bytes of shared memory are consumed for each slot. - The default is one thousand relations. - This parameter can only be set at server start. - </para> - </listitem> - </varlistentry> - - </variablelist> - - <note> - <para> - See the <xref linkend="sql-vacuum" endterm="sql-vacuum-title"> - command for information on setting this parameter. - </para> - </note> - - </sect2> <sect2 id="runtime-config-resource-kernel"> <title>Kernel Resource Usage</title> diff --git a/doc/src/sgml/pageinspect.sgml b/doc/src/sgml/pageinspect.sgml index e398733d013..94249399e10 100644 --- a/doc/src/sgml/pageinspect.sgml +++ b/doc/src/sgml/pageinspect.sgml @@ -1,4 +1,4 @@ -<!-- $PostgreSQL: pgsql/doc/src/sgml/pageinspect.sgml,v 1.3 2007/12/10 05:32:51 tgl Exp $ --> +<!-- $PostgreSQL: pgsql/doc/src/sgml/pageinspect.sgml,v 1.4 2008/09/30 10:52:09 heikki Exp $ --> <sect1 id="pageinspect"> <title>pageinspect</title> @@ -19,7 +19,7 @@ <variablelist> <varlistentry> <term> - <function>get_raw_page(text, int) returns bytea</function> + <function>get_raw_page(relname text, forknum int, blkno int) returns bytea</function> </term> <listitem> @@ -27,13 +27,28 @@ <function>get_raw_page</function> reads the specified block of the named table and returns a copy as a <type>bytea</> value. This allows a single time-consistent copy of the block to be obtained. + <literal>forknum</literal> should be 0 for the main data fork, or 1 for + the FSM. </para> </listitem> </varlistentry> <varlistentry> <term> - <function>page_header(bytea) returns record</function> + <function>get_raw_page(relname text, blkno int) returns bytea</function> + </term> + + <listitem> + <para> + A shorthand of above, for reading from the main fork. Equal to + <literal>get_raw_page(relname, 0, blkno)</literal> + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term> + <function>page_header(page bytea) returns record</function> </term> <listitem> @@ -63,7 +78,7 @@ test=# SELECT * FROM page_header(get_raw_page('pg_class', 0)); <varlistentry> <term> - <function>heap_page_items(bytea) returns setof record</function> + <function>heap_page_items(page bytea) returns setof record</function> </term> <listitem> @@ -90,7 +105,7 @@ test=# SELECT * FROM heap_page_items(get_raw_page('pg_class', 0)); <varlistentry> <term> - <function>bt_metap(text) returns record</function> + <function>bt_metap(relname text) returns record</function> </term> <listitem> @@ -113,7 +128,7 @@ fastlevel | 0 <varlistentry> <term> - <function>bt_page_stats(text, int) returns record</function> + <function>bt_page_stats(relname text, blkno int) returns record</function> </term> <listitem> @@ -141,7 +156,7 @@ btpo_flags | 3 <varlistentry> <term> - <function>bt_page_items(text, int) returns setof record</function> + <function>bt_page_items(relname text, blkno int) returns setof record</function> </term> <listitem> @@ -164,6 +179,26 @@ test=# SELECT * FROM bt_page_items('pg_cast_oid_index', 1); </programlisting> </listitem> </varlistentry> + + <varlistentry> + <term> + <function>fsm_page_contents(page bytea) returns text</function> + </term> + + <listitem> + <para> + <function>fsm_page_contents</function> shows the internal node structure + of a FSM page. The output is a multi-line string, with one line per + node in the binary tree within the page. Only those nodes that are not + zero are printed. The so-called "next" pointer, which points to the + next slot to be returned from the page, is also printed. + </para> + <para> + See <filename>src/backend/storage/freespace/README</> for more + information on the structure of an FSM page. + </para> + </listitem> + </varlistentry> </variablelist> </sect2> diff --git a/doc/src/sgml/pgfreespacemap.sgml b/doc/src/sgml/pgfreespacemap.sgml index bc821ead6ba..3d749a953d3 100644 --- a/doc/src/sgml/pgfreespacemap.sgml +++ b/doc/src/sgml/pgfreespacemap.sgml @@ -1,4 +1,4 @@ -<!-- $PostgreSQL: pgsql/doc/src/sgml/pgfreespacemap.sgml,v 2.2 2007/12/10 05:32:51 tgl Exp $ --> +<!-- $PostgreSQL: pgsql/doc/src/sgml/pgfreespacemap.sgml,v 2.3 2008/09/30 10:52:09 heikki Exp $ --> <sect1 id="pgfreespacemap"> <title>pg_freespacemap</title> @@ -9,183 +9,66 @@ <para> The <filename>pg_freespacemap</> module provides a means for examining the - free space map (FSM). It provides two C functions: - <function>pg_freespacemap_relations</function> and - <function>pg_freespacemap_pages</function> that each return a set of - records, plus two views <structname>pg_freespacemap_relations</structname> - and <structname>pg_freespacemap_pages</structname> that wrap the functions - for convenient use. + free space map (FSM). It provides a function called + <function>pg_freespacemap</function>, or two overloaded functions, to be + precise. The functions show the value recorded in the free space map for + a given page, or for all pages in the relation. </para> <para> - By default public access is revoked from the functions and views, just in - case there are security issues lurking. + By default public access is revoked from the functions, just in case + there are security issues lurking. </para> <sect2> - <title>The <filename>pg_freespacemap</> views</title> + <title>Functions</title> + + <variablelist> + <varlistentry> + <term> + <function>pg_freespacemap(rel regclass IN, blkno bigint IN) returns int2</function> + </term> + + <listitem> + <para> + Returns the amount of free space on the page of the relation, specified + by <literal>blkno</>, according to the FSM. + (blkno). + </para> + </listitem> + </varlistentry> + + + <varlistentry> + <term> + <function>pg_freespacemap(rel regclass IN, blkno OUT int4, avail OUT int2)</function> + </term> + + <listitem> + <para> + Displays the the amount of free space on each page of the relation, + according to the FSM. A set of <literal>(blkno int4, avail int2)</> + tuples is returned, one tuple for each page in the relation. + </para> + </listitem> + </varlistentry> + </variablelist> <para> - The definitions of the columns exposed by the views are: - </para> - - <table> - <title><structname>pg_freespacemap_relations</> Columns</title> - - <tgroup cols="4"> - <thead> - <row> - <entry>Name</entry> - <entry>Type</entry> - <entry>References</entry> - <entry>Description</entry> - </row> - </thead> - <tbody> - - <row> - <entry><structfield>reltablespace</structfield></entry> - <entry><type>oid</type></entry> - <entry><literal>pg_tablespace.oid</literal></entry> - <entry>Tablespace OID of the relation</entry> - </row> - <row> - <entry><structfield>reldatabase</structfield></entry> - <entry><type>oid</type></entry> - <entry><literal>pg_database.oid</literal></entry> - <entry>Database OID of the relation</entry> - </row> - <row> - <entry><structfield>relfilenode</structfield></entry> - <entry><type>oid</type></entry> - <entry><literal>pg_class.relfilenode</literal></entry> - <entry>Relfilenode of the relation</entry> - </row> - <row> - <entry><structfield>avgrequest</structfield></entry> - <entry><type>integer</type></entry> - <entry></entry> - <entry>Moving average of free space requests (NULL for indexes)</entry> - </row> - <row> - <entry><structfield>interestingpages</structfield></entry> - <entry><type>integer</type></entry> - <entry></entry> - <entry>Count of pages last reported as containing useful free space</entry> - </row> - <row> - <entry><structfield>storedpages</structfield></entry> - <entry><type>integer</type></entry> - <entry></entry> - <entry>Count of pages actually stored in free space map</entry> - </row> - <row> - <entry><structfield>nextpage</structfield></entry> - <entry><type>integer</type></entry> - <entry></entry> - <entry>Page index (from 0) to start next search at</entry> - </row> - - </tbody> - </tgroup> - </table> - - <table> - <title><structname>pg_freespacemap_pages</> Columns</title> - - <tgroup cols="4"> - <thead> - <row> - <entry>Name</entry> - <entry>Type</entry> - <entry>References</entry> - <entry>Description</entry> - </row> - </thead> - <tbody> - - <row> - <entry><structfield>reltablespace</structfield></entry> - <entry><type>oid</type></entry> - <entry><literal>pg_tablespace.oid</literal></entry> - <entry>Tablespace OID of the relation</entry> - </row> - <row> - <entry><structfield>reldatabase</structfield></entry> - <entry><type>oid</type></entry> - <entry><literal>pg_database.oid</literal></entry> - <entry>Database OID of the relation</entry> - </row> - <row> - <entry><structfield>relfilenode</structfield></entry> - <entry><type>oid</type></entry> - <entry><literal>pg_class.relfilenode</literal></entry> - <entry>Relfilenode of the relation</entry> - </row> - <row> - <entry><structfield>relblocknumber</structfield></entry> - <entry><type>bigint</type></entry> - <entry></entry> - <entry>Page number within the relation</entry> - </row> - <row> - <entry><structfield>bytes</structfield></entry> - <entry><type>integer</type></entry> - <entry></entry> - <entry>Free bytes in the page, or NULL for an index page (see below)</entry> - </row> - - </tbody> - </tgroup> - </table> - - <para> - For <structname>pg_freespacemap_relations</structname>, there is one row - for each relation in the free space map. - <structfield>storedpages</structfield> is the number of pages actually - stored in the map, while <structfield>interestingpages</structfield> is the - number of pages the last <command>VACUUM</> thought had useful amounts of - free space. - </para> - - <para> - If <structfield>storedpages</structfield> is consistently less than - <structfield>interestingpages</> then it'd be a good idea to increase - <varname>max_fsm_pages</varname>. Also, if the number of rows in - <structname>pg_freespacemap_relations</structname> is close to - <varname>max_fsm_relations</varname>, then you should consider increasing - <varname>max_fsm_relations</varname>. - </para> - - <para> - For <structname>pg_freespacemap_pages</structname>, there is one row for - each page in the free space map. The number of rows for a relation will - match the <structfield>storedpages</structfield> column in - <structname>pg_freespacemap_relations</structname>. + The values stored in the free space map are not exact. They're rounded + to precision of 1/256th of BLCKSZ (32 bytes with default BLCKSZ), and + they're not kept fully up-to-date as tuples are inserted and updated. </para> <para> For indexes, what is tracked is entirely-unused pages, rather than free - space within pages. Therefore, the average request size and free bytes - within a page are not meaningful, and are shown as NULL. + space within pages. Therefore, the values are not meaningful, just + whether a page is full or empty. </para> <para> - Because the map is shared by all the databases, there will normally be - entries for relations not belonging to the current database. This means - that there may not be matching join rows in <structname>pg_class</> for - some rows, or that there could even be incorrect joins. If you are - trying to join against <structname>pg_class</>, it's a good idea to - restrict the join to rows having <structfield>reldatabase</> equal to - the current database's OID or zero. - </para> - - <para> - When either of the views is accessed, internal free space map locks are - taken for long enough to copy all the state data that the view will display. - This ensures that the views produce a consistent set of results, while not - blocking normal activity longer than necessary. Nonetheless there - could be some impact on database performance if they are read often. + NOTE: The interface was changed in version 8.4, to reflect the new FSM + implementation introduced in the same version. </para> </sect2> @@ -193,45 +76,37 @@ <title>Sample output</title> <programlisting> -regression=# SELECT c.relname, r.avgrequest, r.interestingpages, r.storedpages - FROM pg_freespacemap_relations r INNER JOIN pg_class c - ON r.relfilenode = c.relfilenode AND - r.reldatabase IN (0, (SELECT oid FROM pg_database - WHERE datname = current_database())) - ORDER BY r.storedpages DESC LIMIT 10; - relname | avgrequest | interestingpages | storedpages ----------------------------------+------------+------------------+------------- - onek | 256 | 109 | 109 - pg_attribute | 167 | 93 | 93 - pg_class | 191 | 49 | 49 - pg_attribute_relid_attnam_index | | 48 | 48 - onek2 | 256 | 37 | 37 - pg_depend | 95 | 26 | 26 - pg_type | 199 | 16 | 16 - pg_rewrite | 1011 | 13 | 13 - pg_class_relname_nsp_index | | 10 | 10 - pg_proc | 302 | 8 | 8 -(10 rows) - -regression=# SELECT c.relname, p.relblocknumber, p.bytes - FROM pg_freespacemap_pages p INNER JOIN pg_class c - ON p.relfilenode = c.relfilenode AND - p.reldatabase IN (0, (SELECT oid FROM pg_database - WHERE datname = current_database())) - ORDER BY c.relname LIMIT 10; - relname | relblocknumber | bytes ---------------+----------------+------- - a_star | 0 | 8040 - abstime_tbl | 0 | 7908 - aggtest | 0 | 8008 - altinhoid | 0 | 8128 - altstartwith | 0 | 8128 - arrtest | 0 | 7172 - b_star | 0 | 7976 - box_tbl | 0 | 7912 - bt_f8_heap | 54 | 7728 - bt_i4_heap | 49 | 8008 -(10 rows) +postgres=# SELECT * FROM pg_freespace('foo'); + blkno | avail +-------+------- + 0 | 0 + 1 | 0 + 2 | 0 + 3 | 32 + 4 | 704 + 5 | 704 + 6 | 704 + 7 | 1216 + 8 | 704 + 9 | 704 + 10 | 704 + 11 | 704 + 12 | 704 + 13 | 704 + 14 | 704 + 15 | 704 + 16 | 704 + 17 | 704 + 18 | 704 + 19 | 3648 +(20 rows) + +postgres=# SELECT * FROM pg_freespace('foo', 7); + pg_freespace +-------------- + 1216 +(1 row) + </programlisting> </sect2> @@ -239,7 +114,9 @@ regression=# SELECT c.relname, p.relblocknumber, p.bytes <title>Author</title> <para> - Mark Kirkwood <email>markir@paradise.net.nz</email> + Original version by Mark Kirkwood <email>markir@paradise.net.nz</email>. + Rewritten in version 8.4 to suit new FSM implementation by Heikki + Linnakangas <email>heikki@enterprisedb.com</email> </para> </sect2> diff --git a/doc/src/sgml/ref/vacuum.sgml b/doc/src/sgml/ref/vacuum.sgml index 082473c069b..0568fd4eeb8 100644 --- a/doc/src/sgml/ref/vacuum.sgml +++ b/doc/src/sgml/ref/vacuum.sgml @@ -1,5 +1,5 @@ <!-- -$PostgreSQL: pgsql/doc/src/sgml/ref/vacuum.sgml,v 1.51 2008/02/03 16:24:08 tgl Exp $ +$PostgreSQL: pgsql/doc/src/sgml/ref/vacuum.sgml,v 1.52 2008/09/30 10:52:10 heikki Exp $ PostgreSQL documentation --> @@ -96,11 +96,7 @@ VACUUM [ FULL ] [ FREEZE ] [ VERBOSE ] ANALYZE [ <replaceable class="PARAMETER"> <term><literal>VERBOSE</literal></term> <listitem> <para> - Prints a detailed vacuum activity report for each table. Can be used - to help determine appropriate settings for - <xref linkend="guc-max-fsm-pages">, - <xref linkend="guc-max-fsm-relations">, and - <xref linkend="guc-default-statistics-target">. + Prints a detailed vacuum activity report for each table. </para> </listitem> </varlistentry> diff --git a/doc/src/sgml/release.sgml b/doc/src/sgml/release.sgml index 6f3daac2b8c..eea942d1a2e 100644 --- a/doc/src/sgml/release.sgml +++ b/doc/src/sgml/release.sgml @@ -1,4 +1,4 @@ -<!-- $PostgreSQL: pgsql/doc/src/sgml/release.sgml,v 1.585 2008/09/17 20:57:35 tgl Exp $ --> +<!-- $PostgreSQL: pgsql/doc/src/sgml/release.sgml,v 1.586 2008/09/30 10:52:09 heikki Exp $ --> <!-- Typical markup: @@ -6004,8 +6004,7 @@ current_date < 2017-11-17 <para> Increase default values for <link linkend="guc-shared-buffers"><varname>shared_buffers</></link> - and <link - linkend="guc-max-fsm-pages"><varname>max_fsm_pages</></link> + and <varname>max_fsm_pages</> (Andrew) </para> </listitem> diff --git a/doc/src/sgml/runtime.sgml b/doc/src/sgml/runtime.sgml index adde49e1a39..6884e66d7ad 100644 --- a/doc/src/sgml/runtime.sgml +++ b/doc/src/sgml/runtime.sgml @@ -1,4 +1,4 @@ -<!-- $PostgreSQL: pgsql/doc/src/sgml/runtime.sgml,v 1.417 2008/09/23 09:20:34 heikki Exp $ --> +<!-- $PostgreSQL: pgsql/doc/src/sgml/runtime.sgml,v 1.418 2008/09/30 10:52:10 heikki Exp $ --> <chapter Id="runtime"> <title>Operating System Environment</title> @@ -1118,16 +1118,6 @@ set semsys:seminfo_semmsl=32 </row> <row> - <entry><xref linkend="guc-max-fsm-relations"></> - <entry>70</> - </row> - - <row> - <entry><xref linkend="guc-max-fsm-pages"></> - <entry>6</> - </row> - - <row> <entry>Fixed space requirements</> <entry>770 kB</entry> </row> diff --git a/doc/src/sgml/storage.sgml b/doc/src/sgml/storage.sgml index e564fd2be9a..51f8a2fe165 100644 --- a/doc/src/sgml/storage.sgml +++ b/doc/src/sgml/storage.sgml @@ -1,4 +1,4 @@ -<!-- $PostgreSQL: pgsql/doc/src/sgml/storage.sgml,v 1.24 2008/08/05 12:09:30 mha Exp $ --> +<!-- $PostgreSQL: pgsql/doc/src/sgml/storage.sgml,v 1.25 2008/09/30 10:52:10 heikki Exp $ --> <chapter id="storage"> @@ -130,7 +130,12 @@ there. <para> Each table and index is stored in a separate file, named after the table or index's <firstterm>filenode</> number, which can be found in -<structname>pg_class</>.<structfield>relfilenode</>. +<structname>pg_class</>.<structfield>relfilenode</>. In addition to the +main file (aka. main fork), a <firstterm>free space map</> (see +<xref linkend="storage-fsm">) that stores information about free space +available in the relation, is stored in a file named after the filenode +number, with the the _1 suffix. For example, if the table's filenode number +is 12345, the FSM file is named <filename>12345_1</>. </para> <caution> @@ -367,6 +372,48 @@ comparison table, in which all the HTML pages were cut down to 7 kB to fit. </sect1> +<sect1 id="storage-fsm"> + +<title>Free Space Map</title> + + <indexterm> + <primary>Free Space Map</primary> + </indexterm> + <indexterm><primary>FSM</><see>Free Space Map</></indexterm> + +<para> +A Free Space Map is stored with every heap and index relation, except for +hash indexes, to keep track of available space in the relation. It's stored +along the main relation data, in a separate FSM relation fork, named after +relfilenode of the relation, but with a <literal>_1</> suffix. For example, +if the relfilenode of a relation is 12345, the FSM is stored in a file called +<filename>12345_1</>, in the same directory as the main relation file. +</para> + +<para> +The Free Space Map is organized as a tree of <acronym>FSM</> pages. The +bottom level <acronym>FSM</> pages stores the free space available on every +heap (or index) page, using one byte to represent each heap page. The upper +levels aggregate information from the lower levels. +</para> + +<para> +Within each <acronym>FSM</> page is a binary tree, stored in an array with +one byte per node. Each leaf node represents a heap page, or a lower level +<acronym>FSM</> page. In each non-leaf node, the higher of its children's +values is stored. The maximum value in the leaf nodes is therefore stored +at the root. +</para> + +<para> +See <filename>src/backend/storage/freespace/README</> for more details on +how the <acronym>FSM</> is structured, and how it's updated and searched. +<xref linkend="pgfreespacemap"> contrib module can be used to view the +information stored in free space maps. +</para> + +</sect1> + <sect1 id="storage-page-layout"> <title>Database Page Layout</title> |