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
|
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1">
<title>Chapter25.Monitoring Disk Usage</title>
<link rel="stylesheet" href="stylesheet.css" type="text/css">
<link rev="made" href="pgsql-docs@postgresql.org">
<meta name="generator" content="DocBook XSL Stylesheets V1.70.0">
<link rel="start" href="index.html" title="PostgreSQL 8.1.4 Documentation">
<link rel="up" href="admin.html" title="PartIII.Server Administration">
<link rel="prev" href="monitoring-locks.html" title="24.3.Viewing Locks">
<link rel="next" href="disk-full.html" title="25.2.Disk Full Failure">
<link rel="copyright" href="ln-legalnotice.html" title="Legal Notice">
</head>
<body bgcolor="white" text="black" link="#0000FF" vlink="#840084" alink="#0000FF"><div class="chapter" lang="en" id="diskusage">
<div class="titlepage"><div><div><h2 class="title">
<a name="diskusage"></a>Chapter25.Monitoring Disk Usage</h2></div></div></div>
<div class="toc">
<p><b>Table of Contents</b></p>
<dl>
<dt><span class="sect1"><a href="diskusage.html#disk-usage">25.1. Determining Disk Usage</a></span></dt>
<dt><span class="sect1"><a href="disk-full.html">25.2. Disk Full Failure</a></span></dt>
</dl>
</div>
<p> This chapter discusses how to monitor the disk usage of a
<span class="productname">PostgreSQL</span> database system.
</p>
<div class="sect1" lang="en">
<div class="titlepage"><div><div><h2 class="title" style="clear: both">
<a name="disk-usage"></a>25.1.Determining Disk Usage</h2></div></div></div>
<a name="id673228"></a><p> 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 class="acronym">TOAST</acronym> file associated with the table,
which is used to store values too wide to fit comfortably in the main
table (see <a href="storage-toast.html" title="50.2.TOAST">Section50.2, “TOAST”</a>). There will be one index on the
<acronym class="acronym">TOAST</acronym> table, if present. There may also be indexes associated
with the base table. Each table and index is stored in a separate disk
file [mdash ] possibly more than one file, if the file would exceed one
gigabyte. Naming conventions for these files are described in <a href="storage.html#storage-file-layout" title="50.1.Database File Layout">Section50.1, “Database File Layout”</a>.
</p>
<p> You can monitor disk space from three ways: using
SQL functions listed in <a href="functions-admin.html#functions-admin-dbsize" title="Table9.47.Database Object Size Functions">Table9.47, “Database Object Size Functions”</a>,
using <code class="command">VACUUM</code> information, and from the command line
using the tools in <code class="filename">contrib/oid2name</code>. The SQL functions
are the easiest to use and report information about tables, tables with
indexes and long value storage (TOAST), databases, and tablespaces.
</p>
<p> Using <span class="application">psql</span> on a recently vacuumed or analyzed database,
you can issue queries to see the disk usage of any table:
</p>
<pre class="programlisting">SELECT relfilenode, relpages FROM pg_class WHERE relname = 'customer';
relfilenode | relpages
-------------+----------
16806 | 60
(1 row)</pre>
<p>
Each page is typically 8 kilobytes. (Remember, <code class="structfield">relpages</code>
is only updated by <code class="command">VACUUM</code>, <code class="command">ANALYZE</code>, and
a few DDL commands such as <code class="command">CREATE INDEX</code>.) The
<code class="structfield">relfilenode</code> value is of interest if you want to examine
the table's disk file directly.
</p>
<p> To show the space used by <acronym class="acronym">TOAST</acronym> tables, use a query
like the following:
</p>
<pre class="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</pre>
<p>
</p>
<p> You can easily display index sizes, too:
</p>
<pre class="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</pre>
<p>
</p>
<p> It is easy to find your largest tables and indexes using this
information:
</p>
<pre class="programlisting">SELECT relname, relpages FROM pg_class ORDER BY relpages DESC;
relname | relpages
----------------------+----------
bigtable | 3290
customer | 3144</pre>
<p>
</p>
<p> You can also use <code class="filename">contrib/oid2name</code> to show disk usage. See
<code class="filename">README.oid2name</code> in that directory for examples. It includes a script that
shows disk usage for each database.
</p>
</div>
</div></body>
</html>
|
