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
|
<!--
$Header: /cvsroot/pgsql/doc/src/sgml/diskusage.sgml,v 1.6.2.1 2002/11/06 23:30:39 petere Exp $
-->
<chapter id="diskusage">
<title>Monitoring Disk Usage</title>
<para>
This chapter discusses how to monitor the disk usage of a PostgreSQL
database system. In the current release, the database administrator
does not have much control over the on-disk storage layout, so this
chapter is mostly informative and can give you some ideas how to
manage the disk usage with operating system tools.
</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. To store long column values, there is also a
<acronym>TOAST</> file associated with the table, named based on the
table's OID (actually <literal>pg_class.relfilenode</>), and an index on the
<acronym>TOAST</> table. There also may be indexes associated with
the base table.
</para>
<para>
You can monitor disk space from three places: from
<application>psql</> using <command>VACUUM</> information, from
<application>psql</> using <filename>contrib/dbsize</>, and from
the command line using <application>contrib/oid2name</>. Using
<application>psql</> on a recently vacuumed (or analyzed) database,
you can issue queries to see the disk usage of any table:
<programlisting>
play=# SELECT relfilenode, relpages
play-# FROM pg_class
play-# WHERE relname = 'customer';
relfilenode | relpages
-------------+----------
16806 | 60
(1 row)
</programlisting>
</para>
<para>
Each page is typically 8 kilobytes. (Remember, <literal>relpages</>
is only updated by <command>VACUUM</> and <command>ANALYZE</>.) To
show the space used by <acronym>TOAST</> tables, use a query based on
the heap relfilenode shown above:
<programlisting>
play=# SELECT relname, relpages
play-# FROM pg_class
play-# WHERE relname = 'pg_toast_16806' OR
play-# relname = 'pg_toast_16806_index'
play-# ORDER BY relname;
relname | relpages
----------------------+----------
pg_toast_16806 | 0
pg_toast_16806_index | 1
</programlisting>
</para>
<para>
You can easily display index usage too:
<programlisting>
play=# SELECT c2.relname, c2.relpages
play-# FROM pg_class c, pg_class c2, pg_index i
play-# WHERE c.relname = 'customer' AND
play-# c.oid = i.indrelid AND
play-# c2.oid = i.indexrelid
play-# ORDER BY c2.relname;
relname | relpages
----------------------+----------
customer_id_indexdex | 26
</programlisting>
</para>
<para>
It is easy to find your largest files using <application>psql</>:
<programlisting>
play=# SELECT relname, relpages
play-# FROM pg_class
play-# ORDER BY relpages DESC;
relname | relpages
----------------------+----------
bigtable | 3290
customer | 3144
</programlisting>
</para>
<para>
<filename>contrib/dbsize</> loads functions into your database that allow
you to find the size of a table or database from inside
<application>psql</> without the need for <command>VACUUM/ANALYZE.</>
</para>
<para>
You can also use <filename>contrib/oid2name</> to show disk usage. See
<filename>README.oid2name</> 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 may
result in subsequent corruption of database indexes, but not of the
fundamental data tables. If the WAL files are on the same disk (as
is the case for a default configuration) then a filled disk during
database initialization may result in corrupted or incomplete WAL
files. This failure condition is detected and the database server
will refuse to start up.
</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 and create a symlink from the original location. But
note that <application>pg_dump</> cannot save the location layout
information of such a setup; a restore would put everything back in
one place. To avoid running out of disk space, you can place the
WAL files or individual databases in other locations while creating
them. See the <application>initdb</> documentation and <xref
linkend="manage-ag-alternate-locs"> for more information.
</para>
<tip>
<para>
Some file systems perform badly when they are almost full, so do
not wait until the disk is full to take action.
</para>
</tip>
</sect1>
</chapter>
<!-- Keep this comment at the end of the file
Local variables:
mode:sgml
sgml-omittag:nil
sgml-shorttag:t
sgml-minimize-attributes:nil
sgml-always-quote-attributes:t
sgml-indent-step:1
sgml-indent-data:t
sgml-parent-document:nil
sgml-default-dtd-file:"./reference.ced"
sgml-exposed-tags:nil
sgml-local-catalogs:("/usr/lib/sgml/catalog")
sgml-local-ecat-files:nil
End:
-->
|
