2 $PostgreSQL: pgsql/doc/src/sgml/monitoring.sgml,v 1.27 2004/12/28 19:08:58 tgl Exp $
5 <chapter id="monitoring">
6 <title>Monitoring Database Activity</title>
8 <indexterm zone="monitoring">
9 <primary>monitoring</primary>
10 <secondary>database activity</secondary>
13 <indexterm zone="monitoring">
14 <primary>database activity</primary>
15 <secondary>monitoring</secondary>
19 A database administrator frequently wonders, <quote>What is the system
20 doing right now?</quote>
21 This chapter discusses how to find that out.
25 Several tools are available for monitoring database activity and
26 analyzing performance. Most of this chapter is devoted to describing
27 <productname>PostgreSQL</productname>'s statistics collector,
28 but one should not neglect regular Unix monitoring programs such as
29 <command>ps</>, <command>top</>, <command>iostat</>, and <command>vmstat</>.
30 Also, once one has identified a
31 poorly-performing query, further investigation may be needed using
32 <productname>PostgreSQL</productname>'s <xref linkend="sql-explain"
33 endterm="sql-explain-title"> command.
34 <xref linkend="using-explain"> discusses <command>EXPLAIN</>
35 and other methods for understanding the behavior of an individual
39 <sect1 id="monitoring-ps">
40 <title>Standard Unix Tools</Title>
42 <indexterm zone="monitoring-ps">
44 <secondary>to monitor activity</secondary>
48 On most platforms, <productname>PostgreSQL</productname> modifies its
49 command title as reported by <command>ps</>, so that individual server
50 processes can readily be identified. A sample display is
53 $ ps auxww | grep ^postgres
54 postgres 960 0.0 1.1 6104 1480 pts/1 SN 13:17 0:00 postmaster -i
55 postgres 963 0.0 1.1 7084 1472 pts/1 SN 13:17 0:00 postgres: stats buffer process
56 postgres 965 0.0 1.1 6152 1512 pts/1 SN 13:17 0:00 postgres: stats collector process
57 postgres 998 0.0 2.3 6532 2992 pts/1 SN 13:18 0:00 postgres: tgl runbug 127.0.0.1 idle
58 postgres 1003 0.0 2.4 6532 3128 pts/1 SN 13:19 0:00 postgres: tgl regression [local] SELECT waiting
59 postgres 1016 0.1 2.4 6532 3080 pts/1 SN 13:19 0:00 postgres: tgl regression [local] idle in transaction
62 (The appropriate invocation of <command>ps</> varies across different
63 platforms, as do the details of what is shown. This example is from a
64 recent Linux system.) The first process listed here is the
65 <application>postmaster</>, the master server process. The command arguments
66 shown for it are the same ones given when it was launched. The next two
67 processes implement the statistics collector, which will be described in
68 detail in the next section. (These will not be present if you have set
69 the system not to start the statistics collector.) Each of the remaining
70 processes is a server process handling one client connection. Each such
71 process sets its command line display in the form
74 postgres: <replaceable>user</> <replaceable>database</> <replaceable>host</> <replaceable>activity</>
77 The user, database, and connection source host items remain the same for
78 the life of the client connection, but the activity indicator changes.
79 The activity may be <literal>idle</> (i.e., waiting for a client command),
80 <literal>idle in transaction</> (waiting for client inside a <command>BEGIN</> block),
81 or a command type name such as <literal>SELECT</>. Also,
82 <literal>waiting</> is attached if the server process is presently waiting
83 on a lock held by another server process. In the above example we can infer
84 that process 1003 is waiting for process 1016 to complete its transaction and
85 thereby release some lock or other.
90 <productname>Solaris</productname> requires special handling. You must
91 use <command>/usr/ucb/ps</command>, rather than
92 <command>/bin/ps</command>. You also must use two <option>w</option>
93 flags, not just one. In addition, your original invocation of the
94 <command>postmaster</command> command must have a shorter
95 <command>ps</command> status display than that provided by each
96 server process. If you fail to do all three things, the <command>ps</>
97 output for each server process will be the original <command>postmaster</>
103 <sect1 id="monitoring-stats">
104 <title>The Statistics Collector</Title>
106 <indexterm zone="monitoring-stats">
107 <primary>statistics</primary>
111 <productname>PostgreSQL</productname>'s <firstterm>statistics collector</>
112 is a subsystem that supports collection and reporting of information about
113 server activity. Presently, the collector can count accesses to tables
114 and indexes in both disk-block and individual-row terms. It also supports
115 determining the exact command currently being executed by other server
119 <sect2 id="monitoring-stats-setup">
120 <title>Statistics Collection Configuration</Title>
123 Since collection of statistics adds some overhead to query execution,
124 the system can be configured to collect or not collect information.
125 This is controlled by configuration parameters that are normally set in
126 <filename>postgresql.conf</>. (See <xref linkend="runtime-config"> for
127 details about setting configuration parameters.)
131 The parameter <xref linkend="guc-stats-start-collector"> must be
132 set to <literal>true</> for the statistics collector to be launched
133 at all. This is the default and recommended setting, but it may be
134 turned off if you have no interest in statistics and want to
135 squeeze out every last drop of overhead. (The savings is likely to
136 be small, however.) Note that this option cannot be changed while
137 the server is running.
141 The parameters <xref linkend="guc-stats-command-string">,
142 <xref linkend="guc-stats-block-level">, and <xref
143 linkend="guc-stats-row-level"> control how much information is
144 actually sent to the collector and thus determine how much run-time
145 overhead occurs. These respectively determine whether a server
146 process sends its current command string, disk-block-level access
147 statistics, and row-level access statistics to the collector.
148 Normally these parameters are set in <filename>postgresql.conf</>
149 so that they apply to all server processes, but it is possible to
150 turn them on or off in individual sessions using the <xref
151 linkend="sql-set" endterm="sql-set-title"> command. (To prevent
152 ordinary users from hiding their activity from the administrator,
153 only superusers are allowed to change these parameters with
159 Since the parameters <varname>stats_command_string</varname>,
160 <varname>stats_block_level</varname>, and
161 <varname>stats_row_level</varname> default to <literal>false</>,
162 very few statistics are collected in the default
163 configuration. Enabling one or more of these configuration
164 variables will significantly enhance the amount of useful data
165 produced by the statistics collector, at the expense of
166 additional run-time overhead.
172 <sect2 id="monitoring-stats-views">
173 <title>Viewing Collected Statistics</Title>
176 Several predefined views, listed in <xref
177 linkend="monitoring-stats-views-table">, are available to show the results
178 of statistics collection. Alternatively, one can
179 build custom views using the underlying statistics functions.
183 When using the statistics to monitor current activity, it is important
184 to realize that the information does not update instantaneously.
185 Each individual server process transmits new block and row access counts to
186 the collector just before going idle; so a query or transaction still in
187 progress does not affect the displayed totals. Also, the collector itself
188 emits a new report at most once per <varname>pgstat_stat_interval</varname>
189 milliseconds (500 by default). So the displayed information lags behind
190 actual activity. Current-query information is reported to the collector
191 immediately, but is still subject to the
192 <varname>pgstat_stat_interval</varname> delay before it becomes visible.
196 Another important point is that when a server process is asked to display
197 any of these statistics, it first fetches the most recent report emitted by
198 the collector process and then continues to use this snapshot for all
199 statistical views and functions until the end of its current transaction.
200 So the statistics will appear not to change as long as you continue the
202 This is a feature, not a bug, because it allows you to perform several
203 queries on the statistics and correlate the results without worrying that
204 the numbers are changing underneath you. But if you want to see new
205 results with each query, be sure to do the queries outside any transaction
209 <table id="monitoring-stats-views-table">
210 <title>Standard Statistics Views</title>
215 <entry>View Name</entry>
216 <entry>Description</entry>
222 <entry><structname>pg_stat_activity</></entry>
223 <entry>One row per server process, showing process
224 <acronym>ID</>, database, user, current query, and the time at
225 which the current query began execution. The columns that report
226 data on the current query are only available if the parameter
227 <varname>stats_command_string</varname> has been turned on.
228 Furthermore, these columns read as null unless the user examining
229 the view is a superuser or the same as the user owning the process
230 being reported on. (Note that because of the
231 collector's reporting delay, current query will only be up-to-date for
232 long-running queries.)</entry>
236 <entry><structname>pg_stat_database</></entry>
237 <entry>One row per database, showing the number of active backend server processes,
238 total transactions committed and total rolled back in that database,
239 total disk blocks read, and total number of buffer hits (i.e., block
240 read requests avoided by finding the block already in buffer cache).
245 <entry><structname>pg_stat_all_tables</></entry>
246 <entry>For each table in the current database, total numbers of
247 sequential and index scans, total numbers of rows returned by
248 each type of scan, and totals of row insertions, updates,
249 and deletions.</entry>
253 <entry><structname>pg_stat_sys_tables</></entry>
254 <entry>Same as <structname>pg_stat_all_tables</>, except that only system tables
259 <entry><structname>pg_stat_user_tables</></entry>
260 <entry>Same as <structname>pg_stat_all_tables</>, except that only user tables
265 <entry><structname>pg_stat_all_indexes</></entry>
266 <entry>For each index in the current database, the total number
267 of index scans that have used that index, the number of index rows
268 read, and the number of successfully fetched heap rows. (This may
269 be less when there are index entries pointing to expired heap rows.)
274 <entry><structname>pg_stat_sys_indexes</></entry>
275 <entry>Same as <structname>pg_stat_all_indexes</>, except that only indexes on
276 system tables are shown.</entry>
280 <entry><structname>pg_stat_user_indexes</></entry>
281 <entry>Same as <structname>pg_stat_all_indexes</>, except that only indexes on
282 user tables are shown.</entry>
286 <entry><structname>pg_statio_all_tables</></entry>
287 <entry>For each table in the current database, the total number of disk
288 blocks read from that table, the number of buffer hits, the numbers of
289 disk blocks read and buffer hits in all the indexes of that table,
290 the numbers of disk blocks read and buffer hits from the table's
291 auxiliary TOAST table (if any), and the numbers of disk blocks read
292 and buffer hits for the TOAST table's index.
297 <entry><structname>pg_statio_sys_tables</></entry>
298 <entry>Same as <structname>pg_statio_all_tables</>, except that only system tables
303 <entry><structname>pg_statio_user_tables</></entry>
304 <entry>Same as <structname>pg_statio_all_tables</>, except that only user tables
309 <entry><structname>pg_statio_all_indexes</></entry>
310 <entry>For each index in the current database, the numbers of
311 disk blocks read and buffer hits in that index.
316 <entry><structname>pg_statio_sys_indexes</></entry>
317 <entry>Same as <structname>pg_statio_all_indexes</>, except that only indexes on
318 system tables are shown.</entry>
322 <entry><structname>pg_statio_user_indexes</></entry>
323 <entry>Same as <structname>pg_statio_all_indexes</>, except that only indexes on
324 user tables are shown.</entry>
328 <entry><structname>pg_statio_all_sequences</></entry>
329 <entry>For each sequence object in the current database, the numbers
330 of disk blocks read and buffer hits in that sequence.
335 <entry><structname>pg_statio_sys_sequences</></entry>
336 <entry>Same as <structname>pg_statio_all_sequences</>, except that only system
337 sequences are shown. (Presently, no system sequences are defined,
338 so this view is always empty.)</entry>
342 <entry><structname>pg_statio_user_sequences</></entry>
343 <entry>Same as <structname>pg_statio_all_sequences</>, except that only user
344 sequences are shown.</entry>
351 The per-index statistics are particularly useful to determine which
352 indexes are being used and how effective they are.
356 The <structname>pg_statio_</> views are primarily useful to
357 determine the effectiveness of the buffer cache. When the number
358 of actual disk reads is much smaller than the number of buffer
359 hits, then the cache is satisfying most read requests without
360 invoking a kernel call. However, these statistics do not give the
361 entire story: due to the way in which <productname>PostgreSQL</>
362 handles disk I/O, data that is not in the
363 <productname>PostgreSQL</> buffer cache may still reside in the
364 kernel's I/O cache, and may therefore still be fetched without
365 requiring a physical read. Users interested in obtaining more
366 detailed information on <productname>PostgreSQL</> I/O behavior are
367 advised to use the <productname>PostgreSQL</> statistics collector
368 in combination with operating system utilities that allow insight
369 into the kernel's handling of I/O.
373 Other ways of looking at the statistics can be set up by writing
374 queries that use the same underlying statistics access functions as
375 these standard views do. These functions are listed in <xref
376 linkend="monitoring-stats-funcs-table">. The per-database access
377 functions take a database OID as argument to identify which
378 database to report on. The per-table and per-index functions take
379 a table or index OID. (Note that only tables and indexes in the
380 current database can be seen with these functions.) The
381 per-backend process access functions take a backend process ID
382 number, which ranges from one to the number of currently active
386 <table id="monitoring-stats-funcs-table">
387 <title>Statistics Access Functions</title>
392 <entry>Function</entry>
393 <entry>Return Type</entry>
394 <entry>Description</entry>
400 <entry><literal><function>pg_stat_get_db_numbackends</function>(<type>oid</type>)</literal></entry>
401 <entry><type>integer</type></entry>
403 Number of active backend processes for database
408 <entry><literal><function>pg_stat_get_db_xact_commit</function>(<type>oid</type>)</literal></entry>
409 <entry><type>bigint</type></entry>
411 Transactions committed in database
416 <entry><literal><function>pg_stat_get_db_xact_rollback</function>(<type>oid</type>)</literal></entry>
417 <entry><type>bigint</type></entry>
419 Transactions rolled back in database
424 <entry><literal><function>pg_stat_get_db_blocks_fetched</function>(<type>oid</type>)</literal></entry>
425 <entry><type>bigint</type></entry>
427 Number of disk block fetch requests for database
432 <entry><literal><function>pg_stat_get_db_blocks_hit</function>(<type>oid</type>)</literal></entry>
433 <entry><type>bigint</type></entry>
435 Number of disk block fetch requests found in cache for database
440 <entry><literal><function>pg_stat_get_numscans</function>(<type>oid</type>)</literal></entry>
441 <entry><type>bigint</type></entry>
443 Number of sequential scans done when argument is a table,
444 or number of index scans done when argument is an index
449 <entry><literal><function>pg_stat_get_tuples_returned</function>(<type>oid</type>)</literal></entry>
450 <entry><type>bigint</type></entry>
452 Number of rows read by sequential scans when argument is a table,
453 or number of index rows read when argument is an index
458 <entry><literal><function>pg_stat_get_tuples_fetched</function>(<type>oid</type>)</literal></entry>
459 <entry><type>bigint</type></entry>
461 Number of valid (unexpired) table rows fetched by sequential scans
462 when argument is a table, or fetched by index scans using this index
463 when argument is an index
468 <entry><literal><function>pg_stat_get_tuples_inserted</function>(<type>oid</type>)</literal></entry>
469 <entry><type>bigint</type></entry>
471 Number of rows inserted into table
476 <entry><literal><function>pg_stat_get_tuples_updated</function>(<type>oid</type>)</literal></entry>
477 <entry><type>bigint</type></entry>
479 Number of rows updated in table
484 <entry><literal><function>pg_stat_get_tuples_deleted</function>(<type>oid</type>)</literal></entry>
485 <entry><type>bigint</type></entry>
487 Number of rows deleted from table
492 <entry><literal><function>pg_stat_get_blocks_fetched</function>(<type>oid</type>)</literal></entry>
493 <entry><type>bigint</type></entry>
495 Number of disk block fetch requests for table or index
500 <entry><literal><function>pg_stat_get_blocks_hit</function>(<type>oid</type>)</literal></entry>
501 <entry><type>bigint</type></entry>
503 Number of disk block requests found in cache for table or index
508 <entry><literal><function>pg_stat_get_backend_idset</function>()</literal></entry>
509 <entry><type>set of integer</type></entry>
511 Set of currently active backend process IDs (from 1 to the
512 number of active backend processes). See usage example in the text.
517 <entry><literal><function>pg_backend_pid</function>()</literal></entry>
518 <entry><type>integer</type></entry>
520 Process ID of the backend process attached to the current session
525 <entry><literal><function>pg_stat_get_backend_pid</function>(<type>integer</type>)</literal></entry>
526 <entry><type>integer</type></entry>
528 Process ID of the given backend process
533 <entry><literal><function>pg_stat_get_backend_dbid</function>(<type>integer</type>)</literal></entry>
534 <entry><type>oid</type></entry>
536 Database ID of the given backend process
541 <entry><literal><function>pg_stat_get_backend_userid</function>(<type>integer</type>)</literal></entry>
542 <entry><type>oid</type></entry>
544 User ID of the given backend process
549 <entry><literal><function>pg_stat_get_backend_activity</function>(<type>integer</type>)</literal></entry>
550 <entry><type>text</type></entry>
552 Active command of the given backend process (null if the
553 current user is not a superuser nor the same user as that of
554 the session being queried, or
555 <varname>stats_command_string</varname> is not on)
560 <entry><literal><function>pg_stat_get_backend_activity_start</function>(<type>integer</type>)</literal></entry>
561 <entry><type>timestamp with time zone</type></entry>
563 The time at which the given backend process' currently
564 executing query was started (null if the
565 current user is not a superuser nor the same user as that of
566 the session being queried, or
567 <varname>stats_command_string</varname> is not on)
572 <entry><literal><function>pg_stat_reset</function>()</literal></entry>
573 <entry><type>boolean</type></entry>
575 Reset all currently collected statistics
584 <function>pg_stat_get_db_blocks_fetched</function> minus
585 <function>pg_stat_get_db_blocks_hit</function> gives the number of kernel
586 <function>read()</> calls issued for the table, index, or
587 database; but the actual number of physical reads is usually
588 lower due to kernel-level buffering.
593 The function <function>pg_stat_get_backend_idset</function> provides
594 a convenient way to generate one row for each active backend process. For
595 example, to show the <acronym>PID</>s and current queries of all backend processes:
598 SELECT pg_stat_get_backend_pid(s.backendid) AS procpid,
599 pg_stat_get_backend_activity(s.backendid) AS current_query
600 FROM (SELECT pg_stat_get_backend_idset() AS backendid) AS s;
607 <sect1 id="monitoring-locks">
608 <title>Viewing Locks</title>
610 <indexterm zone="monitoring-locks">
611 <primary>lock</primary>
612 <secondary>monitoring</secondary>
616 Another useful tool for monitoring database activity is the
617 <structname>pg_locks</structname> system table. It allows the
618 database administrator to view information about the outstanding
619 locks in the lock manager. For example, this capability can be used
625 View all the locks currently outstanding, all the locks on
626 relations in a particular database, all the locks on a
627 particular relation, or all the locks held by a particular
628 <productname>PostgreSQL</productname> session.
634 Determine the relation in the current database with the most
635 ungranted locks (which might be a source of contention among
642 Determine the effect of lock contention on overall database
643 performance, as well as the extent to which contention varies
644 with overall database traffic.
649 Details of the <structname>pg_locks</structname> view appear in
650 <xref linkend="view-pg-locks">.
651 For more information on locking and managing concurrency with
652 <productname>PostgreSQL</productname>, refer to <xref linkend="mvcc">.
657 <!-- Keep this comment at the end of the file
662 sgml-minimize-attributes:nil
663 sgml-always-quote-attributes:t
666 sgml-parent-document:nil
667 sgml-default-dtd-file:"./reference.ced"
668 sgml-exposed-tags:nil
669 sgml-local-catalogs:("/usr/lib/sgml/catalog")
670 sgml-local-ecat-files:nil