diff options
Diffstat (limited to 'contrib/pg_freespacemap/README.pg_freespacemap')
| -rw-r--r-- | contrib/pg_freespacemap/README.pg_freespacemap | 173 |
1 files changed, 0 insertions, 173 deletions
diff --git a/contrib/pg_freespacemap/README.pg_freespacemap b/contrib/pg_freespacemap/README.pg_freespacemap deleted file mode 100644 index 9210419cb8..0000000000 --- a/contrib/pg_freespacemap/README.pg_freespacemap +++ /dev/null @@ -1,173 +0,0 @@ -Pg_freespacemap - Real time queries on the free space map (FSM). ---------------- - - This module consists of two C functions: 'pg_freespacemap_relations()' and - 'pg_freespacemap_pages()' that return a set of records, plus two views - 'pg_freespacemap_relations' and 'pg_freespacemap_pages' for more - user-friendly access to the functions. - - The module provides the ability to examine the contents of the free space - map, without having to restart or rebuild the server with additional - debugging code. - - By default public access is REVOKED from the functions and views, just in - case there are security issues present in the code. - - -Installation ------------- - - Build and install the main Postgresql source, then this contrib module: - - $ cd contrib/pg_freespacemap - $ gmake - $ gmake install - - - To register the functions and views: - - $ psql -d <database> -f pg_freespacemap.sql - - -Notes ------ - - The definitions for the columns exposed in the views are: - - pg_freespacemap_relations - - Column | references | Description - ------------------+----------------------+---------------------------------- - reltablespace | pg_tablespace.oid | Tablespace oid of the relation. - reldatabase | pg_database.oid | Database oid of the relation. - relfilenode | pg_class.relfilenode | Relfilenode of the relation. - avgrequest | | Moving average of free space - | | requests (NULL for indexes) - interestingpages | | Count of pages last reported as - | | containing useful free space. - storedpages | | Count of pages actually stored - | | in free space map. - nextpage | | Page index (from 0) to start next - | | search at. - - - pg_freespacemap_pages - - Column | references | Description - ----------------+----------------------+------------------------------------ - reltablespace | pg_tablespace.oid | Tablespace oid of the relation. - reldatabase | pg_database.oid | Database oid of the relation. - relfilenode | pg_class.relfilenode | Relfilenode of the relation. - relblocknumber | | Page number in the relation. - bytes | | Free bytes in the page, or NULL - | | for an index page (see below). - - - For pg_freespacemap_relations, there is one row for each relation in the free - space map. storedpages is the number of pages actually stored in the map, - while interestingpages is the number of pages the last VACUUM thought had - useful amounts of free space. - - If storedpages is consistently less than interestingpages then it'd be a - good idea to increase max_fsm_pages. Also, if the number of rows in - pg_freespacemap_relations is close to max_fsm_relations, then you should - consider increasing max_fsm_relations. - - For pg_freespacemap_pages, there is one row for each page in the free space - map. The number of rows for a relation will match the storedpages column - in pg_freespacemap_relations. - - 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. - - Because the map is shared by all the databases, it will include relations - not belonging to the current database. - - When either of the views are accessed, internal free space map locks are - taken, and a copy of the map data is made for them to 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. - - -Sample output - pg_freespacemap_relations -------------- - -regression=# \d pg_freespacemap_relations -View "public.pg_freespacemap_relations" - Column | Type | Modifiers -------------------+---------+----------- - reltablespace | oid | - reldatabase | oid | - relfilenode | oid | - avgrequest | integer | - interestingpages | integer | - storedpages | integer | - nextpage | integer | -View definition: - SELECT p.reltablespace, p.reldatabase, p.relfilenode, p.avgrequest, p.interestingpages, p.storedpages, p.nextpage - FROM pg_freespacemap_relations() p(reltablespace oid, reldatabase oid, relfilenode oid, avgrequest integer, interestingpages integer, storedpages integer, nextpage integer); - -regression=# SELECT c.relname, r.avgrequest, r.interestingpages, r.storedpages - FROM pg_freespacemap_relations r INNER JOIN pg_class c - ON c.relfilenode = r.relfilenode INNER JOIN pg_database d - ON r.reldatabase = d.oid AND (d.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) - - -Sample output - pg_freespacemap_pages -------------- - -regression=# \d pg_freespacemap_pages - View "public.pg_freespacemap_pages" - Column | Type | Modifiers -----------------+---------+----------- - reltablespace | oid | - reldatabase | oid | - relfilenode | oid | - relblocknumber | bigint | - bytes | integer | -View definition: - SELECT p.reltablespace, p.reldatabase, p.relfilenode, p.relblocknumber, p.bytes - FROM pg_freespacemap_pages() p(reltablespace oid, reldatabase oid, relfilenode oid, relblocknumber bigint, bytes integer); - -regression=# SELECT c.relname, p.relblocknumber, p.bytes - FROM pg_freespacemap_pages p INNER JOIN pg_class c - ON c.relfilenode = p.relfilenode INNER JOIN pg_database d - ON (p.reldatabase = d.oid AND d.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) - - - -Author ------- - - * Mark Kirkwood <markir@paradise.net.nz> - |