1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
|
<sect1 id="pgfreespacemap">
<title>pg_freespacemap</title>
<indexterm zone="pgfreespacemap">
<primary>pg_freespacemap</primary>
</indexterm>
<para>
This module provides a means for examining the free space map (FSM). It
consists of two C functions: <literal>pg_freespacemap_relations()</literal>
and <literal>pg_freespacemap_pages()</literal> that return a set
of records, plus two views <literal>pg_freespacemap_relations</literal> and
<literal>pg_freespacemap_pages</literal> for more user-friendly access to
the functions.
</para>
<para>
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.
</para>
<para>
By default public access is REVOKED from the functions and views, just in
case there are security issues present in the code.
</para>
<sect2>
<title>Notes</title>
<para>
The definitions for the columns exposed in the views are:
</para>
<table>
<title>pg_freespacemap_relations</title>
<tgroup cols="3">
<thead>
<row>
<entry>Column</entry>
<entry>references</entry>
<entry>Description</entry>
</row>
</thead>
<tbody>
<row>
<entry>reltablespace</entry>
<entry>pg_tablespace.oid</entry>
<entry>Tablespace oid of the relation.</entry>
</row>
<row>
<entry>reldatabase</entry>
<entry>pg_database.oid</entry>
<entry>Database oid of the relation.</entry>
</row>
<row>
<entry>relfilenode</entry>
<entry>pg_class.relfilenode</entry>
<entry>Relfilenode of the relation.</entry>
</row>
<row>
<entry>avgrequest</entry>
<entry></entry>
<entry>Moving average of free space requests (NULL for indexes)</entry>
</row>
<row>
<entry>interestingpages</entry>
<entry></entry>
<entry>Count of pages last reported as containing useful free space.</entry>
</row>
<row>
<entry>storedpages</entry>
<entry></entry>
<entry>Count of pages actually stored in free space map.</entry>
</row>
<row>
<entry>nextpage</entry>
<entry></entry>
<entry>Page index (from 0) to start next search at.</entry>
</row>
</tbody>
</tgroup>
</table>
<table>
<title>pg_freespacemap_pages</title>
<tgroup cols="3">
<thead>
<row>
<entry>Column</entry>
<entry> references</entry>
<entry>Description</entry>
</row>
</thead>
<tbody>
<row>
<entry>reltablespace</entry>
<entry>pg_tablespace.oid</entry>
<entry>Tablespace oid of the relation.</entry>
</row>
<row>
<entry>reldatabase</entry>
<entry>pg_database.oid</entry>
<entry>Database oid of the relation.</entry>
</row>
<row>
<entry>relfilenode</entry>
<entry>pg_class.relfilenode</entry>
<entry>Relfilenode of the relation.</entry>
</row>
<row>
<entry>relblocknumber</entry>
<entry></entry>
<entry>Page number in the relation.</entry>
</row>
<row>
<entry>bytes</entry>
<entry></entry>
<entry>Free bytes in the page, or NULL for an index page (see below).</entry>
</row>
</tbody>
</tgroup>
</table>
<para>
For <literal>pg_freespacemap_relations</literal>, there is one row for each
relation in the free space map. <literal>storedpages</literal> is the
number of pages actually stored in the map, while
<literal>interestingpages</literal> is the number of pages the last VACUUM
thought had useful amounts of free space.
</para>
<para>
If <literal>storedpages</literal> is consistently less than interestingpages
then it'd be a good idea to increase <literal>max_fsm_pages</literal>. Also,
if the number of rows in <literal>pg_freespacemap_relations</literal> is
close to <literal>max_fsm_relations</literal>, then you should consider
increasing <literal>max_fsm_relations</literal>.
</para>
<para>
For <literal>pg_freespacemap_pages</literal>, there is one row for each page
in the free space map. The number of rows for a relation will match the
<literal>storedpages</literal> column in
<literal>pg_freespacemap_relations</literal>.
</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.
</para>
<para>
Because the map is shared by all the databases, it will include relations
not belonging to the current database.
</para>
<para>
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.
</para>
</sect2>
<sect2>
<title>Sample output - pg_freespacemap_relations</title>
<programlisting>
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)
</programlisting>
</sect2>
<sect2>
<title>Sample output - pg_freespacemap_pages</title>
<programlisting>
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)
</programlisting>
</sect2>
<sect2>
<title>Author</title>
<para>
Mark Kirkwood <email>markir@paradise.net.nz</email>
</para>
</sect2>
</sect1>
|
