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
|
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1">
<title>Chapter24.Monitoring Database Activity</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="migration.html" title="23.4.Migration Between Releases">
<link rel="next" href="monitoring-stats.html" title="24.2.The Statistics Collector">
<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="monitoring">
<div class="titlepage"><div><div><h2 class="title">
<a name="monitoring"></a>Chapter24.Monitoring Database Activity</h2></div></div></div>
<div class="toc">
<p><b>Table of Contents</b></p>
<dl>
<dt><span class="sect1"><a href="monitoring.html#monitoring-ps">24.1. Standard Unix Tools</a></span></dt>
<dt><span class="sect1"><a href="monitoring-stats.html">24.2. The Statistics Collector</a></span></dt>
<dd><dl>
<dt><span class="sect2"><a href="monitoring-stats.html#monitoring-stats-setup">24.2.1. Statistics Collection Configuration</a></span></dt>
<dt><span class="sect2"><a href="monitoring-stats.html#monitoring-stats-views">24.2.2. Viewing Collected Statistics</a></span></dt>
</dl></dd>
<dt><span class="sect1"><a href="monitoring-locks.html">24.3. Viewing Locks</a></span></dt>
</dl>
</div>
<a name="id671480"></a><a name="id671492"></a><p> A database administrator frequently wonders, “<span class="quote">What is the system
doing right now?</span>”
This chapter discusses how to find that out.
</p>
<p> Several tools are available for monitoring database activity and
analyzing performance. Most of this chapter is devoted to describing
<span class="productname">PostgreSQL</span>'s statistics collector,
but one should not neglect regular Unix monitoring programs such as
<code class="command">ps</code>, <code class="command">top</code>, <code class="command">iostat</code>, and <code class="command">vmstat</code>.
Also, once one has identified a
poorly-performing query, further investigation may be needed using
<span class="productname">PostgreSQL</span>'s <a href="sql-explain.html">EXPLAIN</a> command.
<a href="performance-tips.html#using-explain" title="13.1.Using EXPLAIN">Section13.1, “Using <code class="command">EXPLAIN</code>”</a> discusses <code class="command">EXPLAIN</code>
and other methods for understanding the behavior of an individual
query.
</p>
<div class="sect1" lang="en">
<div class="titlepage"><div><div><h2 class="title" style="clear: both">
<a name="monitoring-ps"></a>24.1.Standard Unix Tools</h2></div></div></div>
<a name="id671595"></a><p> On most platforms, <span class="productname">PostgreSQL</span> modifies its
command title as reported by <code class="command">ps</code>, so that individual server
processes can readily be identified. A sample display is
</p>
<pre class="screen">$ ps auxww | grep ^postgres
postgres 960 0.0 1.1 6104 1480 pts/1 SN 13:17 0:00 postmaster -i
postgres 963 0.0 1.1 7084 1472 pts/1 SN 13:17 0:00 postgres: stats buffer process
postgres 965 0.0 1.1 6152 1512 pts/1 SN 13:17 0:00 postgres: stats collector process
postgres 998 0.0 2.3 6532 2992 pts/1 SN 13:18 0:00 postgres: tgl runbug 127.0.0.1 idle
postgres 1003 0.0 2.4 6532 3128 pts/1 SN 13:19 0:00 postgres: tgl regression [local] SELECT waiting
postgres 1016 0.1 2.4 6532 3080 pts/1 SN 13:19 0:00 postgres: tgl regression [local] idle in transaction</pre>
<p>
(The appropriate invocation of <code class="command">ps</code> varies across different
platforms, as do the details of what is shown. This example is from a
recent Linux system.) The first process listed here is the
<span class="application">postmaster</span>, the master server process. The command arguments
shown for it are the same ones given when it was launched. The next two
processes implement the statistics collector, which will be described in
detail in the next section. (These will not be present if you have set
the system not to start the statistics collector.) Each of the remaining
processes is a server process handling one client connection. Each such
process sets its command line display in the form
</p>
<pre class="screen">postgres: <em class="replaceable"><code>user</code></em> <em class="replaceable"><code>database</code></em> <em class="replaceable"><code>host</code></em> <em class="replaceable"><code>activity</code></em></pre>
<p>
The user, database, and connection source host items remain the same for
the life of the client connection, but the activity indicator changes.
The activity may be <code class="literal">idle</code> (i.e., waiting for a client command),
<code class="literal">idle in transaction</code> (waiting for client inside a <code class="command">BEGIN</code> block),
or a command type name such as <code class="literal">SELECT</code>. Also,
<code class="literal">waiting</code> is attached if the server process is presently waiting
on a lock held by another server process. In the above example we can infer
that process 1003 is waiting for process 1016 to complete its transaction and
thereby release some lock or other.
</p>
<div class="tip" style="margin-left: 0.5in; margin-right: 0.5in;">
<h3 class="title">Tip</h3>
<p> <span class="productname">Solaris</span> requires special handling. You must
use <code class="command">/usr/ucb/ps</code>, rather than
<code class="command">/bin/ps</code>. You also must use two <code class="option">w</code>
flags, not just one. In addition, your original invocation of the
<code class="command">postmaster</code> command must have a shorter
<code class="command">ps</code> status display than that provided by each
server process. If you fail to do all three things, the <code class="command">ps</code>
output for each server process will be the original <code class="command">postmaster</code>
command line.
</p>
</div>
</div>
</div></body>
</html>
|
