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
|
<!-- $PostgreSQL: pgsql/doc/src/sgml/diskusage.sgml,v 1.17 2006/09/16 00:30:12 momjian Exp $ -->
<chapter id="diskusage">
<title>Monitoring Disk Usage</title>
<para>
This chapter discusses how to monitor the disk usage of a
<productname>PostgreSQL</> database system.
</para>
<sect1 id="disk-usage">
<title>Determining Disk Usage</Title>
<indexterm zone="disk-usage">
<primary>disk usage</primary>
</indexterm>
<para>
Each table has a primary heap disk file where most of the data is
stored. If the table has any columns with potentially-wide values,
there is also a <acronym>TOAST</> file associated with the table,
which is used to store values too wide to fit comfortably in the main
table (see <xref linkend="storage-toast">). There will be one index on the
<acronym>TOAST</> table, if present. There may also be indexes associated
with the base table. Each table and index is stored in a separate disk
file — possibly more than one file, if the file would exceed one
gigabyte. Naming conventions for these files are described in <xref
linkend="storage-file-layout">.
</para>
<para>
You can monitor disk space from three ways: using
SQL functions listed in <xref linkend="functions-admin-dbsize">,
using <command>VACUUM</> information, and from the command line
using the tools in <filename>contrib/oid2name</>. The SQL functions
are the easiest to use and report information about tables, tables with
indexes and long value storage (TOAST), databases, and tablespaces.
</para>
<para>
Using <application>psql</> on a recently vacuumed or analyzed database,
you can issue queries to see the disk usage of any table:
<programlisting>
SELECT relfilenode, relpages FROM pg_class WHERE relname = 'customer';
relfilenode | relpages
-------------+----------
16806 | 60
(1 row)
</programlisting>
Each page is typically 8 kilobytes. (Remember, <structfield>relpages</>
is only updated by <command>VACUUM</>, <command>ANALYZE</>, and
a few DDL commands such as <command>CREATE INDEX</>.) The
<structfield>relfilenode</> value is of interest if you want to examine
the table's disk file directly.
</para>
<para>
To show the space used by <acronym>TOAST</> tables, use a query
like the following:
<programlisting>
SELECT relname, relpages
FROM pg_class,
(SELECT reltoastrelid FROM pg_class
WHERE relname = 'customer') ss
WHERE oid = ss.reltoastrelid
OR oid = (SELECT reltoastidxid FROM pg_class
WHERE oid = ss.reltoastrelid)
ORDER BY relname;
relname | relpages
----------------------+----------
pg_toast_16806 | 0
pg_toast_16806_index | 1
</programlisting>
</para>
<para>
You can easily display index sizes, too:
<programlisting>
SELECT c2.relname, c2.relpages
FROM pg_class c, pg_class c2, pg_index i
WHERE c.relname = 'customer'
AND c.oid = i.indrelid
AND c2.oid = i.indexrelid
ORDER BY c2.relname;
relname | relpages
----------------------+----------
customer_id_indexdex | 26
</programlisting>
</para>
<para>
It is easy to find your largest tables and indexes using this
information:
<programlisting>
SELECT relname, relpages FROM pg_class ORDER BY relpages DESC;
relname | relpages
----------------------+----------
bigtable | 3290
customer | 3144
</programlisting>
</para>
<para>
You can also use <filename>contrib/oid2name</> to show disk usage. See
<filename>README.oid2name</> in that directory for examples. It includes a script that
shows disk usage for each database.
</para>
</sect1>
<sect1 id="disk-full">
<title>Disk Full Failure</title>
<para>
The most important disk monitoring task of a database administrator
is to make sure the disk doesn't grow full. A filled data disk will
not result in data corruption, but it may well prevent useful activity
from occurring. If the disk holding the WAL files grows full, database
server panic and consequent shutdown may occur.
</para>
<para>
If you cannot free up additional space on the disk by deleting
other things, you can move some of the database files to other file
systems by making use of tablespaces. See <xref
linkend="manage-ag-tablespaces"> for more information about that.
</para>
<tip>
<para>
Some file systems perform badly when they are almost full, so do
not wait until the disk is completely full to take action.
</para>
</tip>
<para>
If your system supports per-user disk quotas, then the database
will naturally be subject to whatever quota is placed on the user
the server runs as. Exceeding the quota will have the same bad
effects as running out of space entirely.
</para>
</sect1>
</chapter>
|