1 <!-- doc/src/sgml/monitoring.sgml -->
3 <chapter id="monitoring">
4 <title>Monitoring Database Activity</title>
6 <indexterm zone="monitoring">
7 <primary>monitoring</primary>
8 <secondary>database activity</secondary>
11 <indexterm zone="monitoring">
12 <primary>database activity</primary>
13 <secondary>monitoring</secondary>
17 A database administrator frequently wonders, <quote>What is the system
18 doing right now?</quote>
19 This chapter discusses how to find that out.
23 Several tools are available for monitoring database activity and
24 analyzing performance. Most of this chapter is devoted to describing
25 <productname>PostgreSQL</productname>'s statistics collector,
26 but one should not neglect regular Unix monitoring programs such as
27 <command>ps</>, <command>top</>, <command>iostat</>, and <command>vmstat</>.
28 Also, once one has identified a
29 poorly-performing query, further investigation might be needed using
30 <productname>PostgreSQL</productname>'s <xref linkend="sql-explain"> command.
31 <xref linkend="using-explain"> discusses <command>EXPLAIN</>
32 and other methods for understanding the behavior of an individual
36 <sect1 id="monitoring-ps">
37 <title>Standard Unix Tools</title>
39 <indexterm zone="monitoring-ps">
41 <secondary>to monitor activity</secondary>
45 On most Unix platforms, <productname>PostgreSQL</productname> modifies its
46 command title as reported by <command>ps</>, so that individual server
47 processes can readily be identified. A sample display is
50 $ ps auxww | grep ^postgres
51 postgres 960 0.0 1.1 6104 1480 pts/1 SN 13:17 0:00 postgres -i
52 postgres 963 0.0 1.1 7084 1472 pts/1 SN 13:17 0:00 postgres: writer process
53 postgres 965 0.0 1.1 6152 1512 pts/1 SN 13:17 0:00 postgres: stats collector process
54 postgres 998 0.0 2.3 6532 2992 pts/1 SN 13:18 0:00 postgres: tgl runbug 127.0.0.1 idle
55 postgres 1003 0.0 2.4 6532 3128 pts/1 SN 13:19 0:00 postgres: tgl regression [local] SELECT waiting
56 postgres 1016 0.1 2.4 6532 3080 pts/1 SN 13:19 0:00 postgres: tgl regression [local] idle in transaction
59 (The appropriate invocation of <command>ps</> varies across different
60 platforms, as do the details of what is shown. This example is from a
61 recent Linux system.) The first process listed here is the
62 master server process. The command arguments
63 shown for it are the same ones used when it was launched. The next two
64 processes are background worker processes automatically launched by the
65 master process. (The <quote>stats collector</> process will not be present
67 the system not to start the statistics collector.) Each of the remaining
68 processes is a server process handling one client connection. Each such
69 process sets its command line display in the form
72 postgres: <replaceable>user</> <replaceable>database</> <replaceable>host</> <replaceable>activity</>
75 The user, database, and (client) host items remain the same for
76 the life of the client connection, but the activity indicator changes.
77 The activity can be <literal>idle</> (i.e., waiting for a client command),
78 <literal>idle in transaction</> (waiting for client inside a <command>BEGIN</> block),
79 or a command type name such as <literal>SELECT</>. Also,
80 <literal>waiting</> is appended if the server process is presently waiting
81 on a lock held by another session. In the above example we can infer
82 that process 1003 is waiting for process 1016 to complete its transaction and
83 thereby release some lock.
87 If you have turned off <xref linkend="guc-update-process-title"> then the
88 activity indicator is not updated; the process title is set only once
89 when a new process is launched. On some platforms this saves a measurable
90 amount of per-command overhead; on others it's insignificant.
95 <productname>Solaris</productname> requires special handling. You must
96 use <command>/usr/ucb/ps</command>, rather than
97 <command>/bin/ps</command>. You also must use two <option>w</option>
98 flags, not just one. In addition, your original invocation of the
99 <command>postgres</command> command must have a shorter
100 <command>ps</command> status display than that provided by each
101 server process. If you fail to do all three things, the <command>ps</>
102 output for each server process will be the original <command>postgres</>
108 <sect1 id="monitoring-stats">
109 <title>The Statistics Collector</title>
111 <indexterm zone="monitoring-stats">
112 <primary>statistics</primary>
116 <productname>PostgreSQL</productname>'s <firstterm>statistics collector</>
117 is a subsystem that supports collection and reporting of information about
118 server activity. Presently, the collector can count accesses to tables
119 and indexes in both disk-block and individual-row terms. It also tracks
120 the total number of rows in each table, and information about vacuum and
121 analyze actions for each table. It can also count calls to user-defined
122 functions and the total time spent in each one.
126 <productname>PostgreSQL</productname> also supports reporting of the exact
127 command currently being executed by other server processes. This
128 facility is independent of the collector process.
131 <sect2 id="monitoring-stats-setup">
132 <title>Statistics Collection Configuration</title>
135 Since collection of statistics adds some overhead to query execution,
136 the system can be configured to collect or not collect information.
137 This is controlled by configuration parameters that are normally set in
138 <filename>postgresql.conf</>. (See <xref linkend="runtime-config"> for
139 details about setting configuration parameters.)
143 The parameter <xref linkend="guc-track-counts"> controls whether
144 statistics are collected about table and index accesses.
148 The parameter <xref linkend="guc-track-functions"> enables tracking of
149 usage of user-defined functions.
153 The parameter <xref linkend="guc-track-activities"> enables monitoring
154 of the current command being executed by any server process.
158 Normally these parameters are set in <filename>postgresql.conf</> so
159 that they apply to all server processes, but it is possible to turn
160 them on or off in individual sessions using the <xref
161 linkend="sql-set"> command. (To prevent
162 ordinary users from hiding their activity from the administrator,
163 only superusers are allowed to change these parameters with
168 The statistics collector transmits the collected
169 information to backends (including autovacuum) through temporary files.
170 These files are stored in the <filename>pg_stat_tmp</filename> subdirectory.
171 When the postmaster shuts down, a permanent copy of the statistics
172 data is stored in the <filename>global</filename> subdirectory. For increased
173 performance, the parameter <xref linkend="guc-stats-temp-directory"> can
174 be pointed at a RAM-based file system, decreasing physical I/O requirements.
179 <sect2 id="monitoring-stats-views">
180 <title>Viewing Collected Statistics</title>
183 Several predefined views, listed in <xref
184 linkend="monitoring-stats-views-table">, are available to show the results
185 of statistics collection. Alternatively, one can
186 build custom views using the underlying statistics functions.
190 When using the statistics to monitor current activity, it is important
191 to realize that the information does not update instantaneously.
192 Each individual server process transmits new statistical counts to
193 the collector just before going idle; so a query or transaction still in
194 progress does not affect the displayed totals. Also, the collector itself
195 emits a new report at most once per <varname>PGSTAT_STAT_INTERVAL</varname>
196 milliseconds (500 unless altered while building the server). So the
197 displayed information lags behind actual activity. However, current-query
198 information collected by <varname>track_activities</varname> is
203 Another important point is that when a server process is asked to display
204 any of these statistics, it first fetches the most recent report emitted by
205 the collector process and then continues to use this snapshot for all
206 statistical views and functions until the end of its current transaction.
207 So the statistics will show static information as long as you continue the
208 current transaction. Similarly, information about the current queries of
209 all sessions is collected when any such information is first requested
210 within a transaction, and the same information will be displayed throughout
212 This is a feature, not a bug, because it allows you to perform several
213 queries on the statistics and correlate the results without worrying that
214 the numbers are changing underneath you. But if you want to see new
215 results with each query, be sure to do the queries outside any transaction
216 block. Alternatively, you can invoke
217 <function>pg_stat_clear_snapshot</function>(), which will discard the
218 current transaction's statistics snapshot (if any). The next use of
219 statistical information will cause a new snapshot to be fetched.
223 A transaction can also see its own statistics (as yet untransmitted to the
224 collector) in the views <structname>pg_stat_xact_all_tables</>,
225 <structname>pg_stat_xact_sys_tables</>,
226 <structname>pg_stat_xact_user_tables</>, and
227 <structname>pg_stat_xact_user_functions</>, or via these views' underlying
228 functions. These numbers do not act as stated above; instead they update
229 continuously throughout the transaction.
232 <table id="monitoring-stats-views-table">
233 <title>Standard Statistics Views</title>
238 <entry>View Name</entry>
239 <entry>Description</entry>
245 <entry><structname>pg_stat_activity</><indexterm><primary>pg_stat_activity</primary></indexterm></entry>
246 <entry>One row per server process, showing database OID, database
247 name, process <acronym>ID</>, user OID, user name, application name,
248 client's address, host name (if available), and port number, times at
249 which the server process, current transaction, and current query began
250 execution, process's waiting status, and text of the current query.
251 The columns that report data on the current query are available unless
252 the parameter <varname>track_activities</varname> has been turned off.
253 Furthermore, these columns are only visible if the user examining
254 the view is a superuser or the same as the user owning the process
255 being reported on. The client's host name will be available only if
256 <xref linkend="guc-log-hostname"> is set or if the user's host name
257 needed to be looked up during <filename>pg_hba.conf</filename>
263 <entry><structname>pg_stat_bgwriter</><indexterm><primary>pg_stat_bgwriter</primary></indexterm></entry>
264 <entry>One row only, showing cluster-wide statistics from the
265 background writer: number of scheduled checkpoints, requested
266 checkpoints, buffers written by checkpoints and cleaning scans,
267 and the number of times the background writer stopped a cleaning scan
268 because it had written too many buffers. Also includes
269 statistics about the shared buffer pool, including buffers written
270 by backends (that is, not by the background writer), how many times
271 those backends had to execute their own fsync calls (normally the
272 background writer handles those even when the backend does its own
273 write), total buffers allocated, and time of last statistics reset.
278 <entry><structname>pg_stat_database</><indexterm><primary>pg_stat_database</primary></indexterm></entry>
279 <entry>One row per database, showing database OID, database name,
280 number of active server processes connected to that database,
281 number of transactions committed and rolled back in that database,
282 total disk blocks read, total buffer hits (i.e., block
283 read requests avoided by finding the block already in buffer cache),
284 number of rows returned, fetched, inserted, updated and deleted, the
285 total number of queries canceled due to conflict with recovery (on
286 standby servers), and time of last statistics reset.
291 <entry><structname>pg_stat_database_conflicts</><indexterm><primary>pg_stat_database_conflicts</primary></indexterm></entry>
292 <entry>One row per database, showing database OID, database name and
293 the number of queries that have been canceled in this database due to
294 dropped tablespaces, lock timeouts, old snapshots, pinned buffers and
295 deadlocks. Will only contain information on standby servers, since
296 conflicts do not occur on master servers.
301 <entry><structname>pg_stat_replication</><indexterm><primary>pg_stat_replication</primary></indexterm></entry>
302 <entry>One row per WAL sender process, showing process <acronym>ID</>,
303 user OID, user name, application name, client's address, host name
304 (if available) and port number, time at which the server process began
305 execution, and the current WAL sender state and transaction log
306 location. In addition, the standby reports the last transaction log
307 position it received and wrote, the last position it flushed to disk,
308 and the last position it replayed, and this information is also
309 displayed here. If the standby's application names matches one of the
310 settings in <varname>synchronous_standby_names</> then the sync_priority
311 is shown here also, that is the order in which standbys will become
312 the synchronous standby. The columns detailing what exactly the connection
313 is doing are only visible if the user examining the view is a superuser.
314 The client's host name will be available only if
315 <xref linkend="guc-log-hostname"> is set or if the user's host name
316 needed to be looked up during <filename>pg_hba.conf</filename>
317 processing. Only directly connected standbys are listed; no information
318 about downstream standby servers is recorded.
323 <entry><structname>pg_stat_all_tables</><indexterm><primary>pg_stat_all_tables</primary></indexterm></entry>
324 <entry>For each table in the current database (including TOAST tables),
325 the table OID, schema and table name, number of sequential
326 scans initiated, number of live rows fetched by sequential
327 scans, number of index scans initiated (over all indexes
328 belonging to the table), number of live rows fetched by index
329 scans, numbers of row insertions, updates, and deletions,
330 number of row updates that were HOT (i.e., no separate index update),
331 numbers of live and dead rows,
332 the last time the table was non-<option>FULL</> vacuumed manually,
333 the last time it was vacuumed by the autovacuum daemon,
334 the last time it was analyzed manually,
335 the last time it was analyzed by the autovacuum daemon,
336 number of times it has been non-<option>FULL</> vacuumed manually,
337 number of times it has been vacuumed by the autovacuum daemon,
338 number of times it has been analyzed manually,
339 and the number of times it has been analyzed by the autovacuum daemon.
344 <entry><structname>pg_stat_sys_tables</><indexterm><primary>pg_stat_sys_tables</primary></indexterm></entry>
345 <entry>Same as <structname>pg_stat_all_tables</>, except that only
346 system tables are shown.</entry>
350 <entry><structname>pg_stat_user_tables</><indexterm><primary>pg_stat_user_tables</primary></indexterm></entry>
351 <entry>Same as <structname>pg_stat_all_tables</>, except that only user
352 tables are shown.</entry>
356 <entry><structname>pg_stat_xact_all_tables</><indexterm><primary>pg_stat_xact_all_tables</primary></indexterm></entry>
357 <entry>Similar to <structname>pg_stat_all_tables</>, but counts actions
358 taken so far within the current transaction (which are <emphasis>not</>
359 yet included in <structname>pg_stat_all_tables</> and related views).
360 The columns for numbers of live and dead rows and vacuum and
361 analyze actions are not present in this view.</entry>
365 <entry><structname>pg_stat_xact_sys_tables</><indexterm><primary>pg_stat_xact_sys_tables</primary></indexterm></entry>
366 <entry>Same as <structname>pg_stat_xact_all_tables</>, except that only
367 system tables are shown.</entry>
371 <entry><structname>pg_stat_xact_user_tables</><indexterm><primary>pg_stat_xact_user_tables</primary></indexterm></entry>
372 <entry>Same as <structname>pg_stat_xact_all_tables</>, except that only
373 user tables are shown.</entry>
377 <entry><structname>pg_stat_all_indexes</><indexterm><primary>pg_stat_all_indexes</primary></indexterm></entry>
378 <entry>For each index in the current database,
379 the table and index OID, schema, table and index name,
380 number of index scans initiated on that index, number of
381 index entries returned by index scans, and number of live table rows
382 fetched by simple index scans using that index.
387 <entry><structname>pg_stat_sys_indexes</><indexterm><primary>pg_stat_sys_indexes</primary></indexterm></entry>
388 <entry>Same as <structname>pg_stat_all_indexes</>, except that only
389 indexes on system tables are shown.</entry>
393 <entry><structname>pg_stat_user_indexes</><indexterm><primary>pg_stat_user_indexes</primary></indexterm></entry>
394 <entry>Same as <structname>pg_stat_all_indexes</>, except that only
395 indexes on user tables are shown.</entry>
399 <entry><structname>pg_statio_all_tables</><indexterm><primary>pg_statio_all_tables</primary></indexterm></entry>
400 <entry>For each table in the current database (including TOAST tables),
401 the table OID, schema and table name, number of disk
402 blocks read from that table, number of buffer hits, numbers of
403 disk blocks read and buffer hits in all indexes of that table,
404 numbers of disk blocks read and buffer hits from that table's
405 auxiliary TOAST table (if any), and numbers of disk blocks read
406 and buffer hits for the TOAST table's index.
411 <entry><structname>pg_statio_sys_tables</><indexterm><primary>pg_statio_sys_tables</primary></indexterm></entry>
412 <entry>Same as <structname>pg_statio_all_tables</>, except that only
413 system tables are shown.</entry>
417 <entry><structname>pg_statio_user_tables</><indexterm><primary>pg_statio_user_tables</primary></indexterm></entry>
418 <entry>Same as <structname>pg_statio_all_tables</>, except that only
419 user tables are shown.</entry>
423 <entry><structname>pg_statio_all_indexes</><indexterm><primary>pg_statio_all_indexes</primary></indexterm></entry>
424 <entry>For each index in the current database,
425 the table and index OID, schema, table and index name,
426 numbers of disk blocks read and buffer hits in that index.
431 <entry><structname>pg_statio_sys_indexes</><indexterm><primary>pg_statio_sys_indexes</primary></indexterm></entry>
432 <entry>Same as <structname>pg_statio_all_indexes</>, except that only
433 indexes on system tables are shown.</entry>
437 <entry><structname>pg_statio_user_indexes</><indexterm><primary>pg_statio_user_indexes</primary></indexterm></entry>
438 <entry>Same as <structname>pg_statio_all_indexes</>, except that only
439 indexes on user tables are shown.</entry>
443 <entry><structname>pg_statio_all_sequences</><indexterm><primary>pg_statio_all_sequences</primary></indexterm></entry>
444 <entry>For each sequence object in the current database,
445 the sequence OID, schema and sequence name,
446 numbers of disk blocks read and buffer hits in that sequence.
451 <entry><structname>pg_statio_sys_sequences</><indexterm><primary>pg_statio_sys_sequences</primary></indexterm></entry>
452 <entry>Same as <structname>pg_statio_all_sequences</>, except that only
453 system sequences are shown. (Presently, no system sequences are defined,
454 so this view is always empty.)</entry>
458 <entry><structname>pg_statio_user_sequences</><indexterm><primary>pg_statio_user_sequences</primary></indexterm></entry>
459 <entry>Same as <structname>pg_statio_all_sequences</>, except that only
460 user sequences are shown.</entry>
464 <entry><structname>pg_stat_user_functions</><indexterm><primary>pg_stat_user_functions</primary></indexterm></entry>
465 <entry>For all tracked functions, function OID, schema, name, number
466 of calls, total time, and self time. Self time is the
467 amount of time spent in the function itself, total time includes the
468 time spent in functions it called. Time values are in milliseconds.
473 <entry><structname>pg_stat_xact_user_functions</><indexterm><primary>pg_stat_xact_user_functions</primary></indexterm></entry>
474 <entry>Similar to <structname>pg_stat_user_functions</>, but counts only
475 calls during the current transaction (which are <emphasis>not</>
476 yet included in <structname>pg_stat_user_functions</>).</entry>
484 The per-index statistics are particularly useful to determine which
485 indexes are being used and how effective they are.
490 used either directly or via <quote>bitmap scans</>. In a bitmap scan
491 the output of several indexes can be combined via AND or OR rules;
492 so it is difficult to associate individual heap row fetches
493 with specific indexes when a bitmap scan is used. Therefore, a bitmap
495 <structname>pg_stat_all_indexes</>.<structfield>idx_tup_read</>
496 count(s) for the index(es) it uses, and it increments the
497 <structname>pg_stat_all_tables</>.<structfield>idx_tup_fetch</>
498 count for the table, but it does not affect
499 <structname>pg_stat_all_indexes</>.<structfield>idx_tup_fetch</>.
504 Before <productname>PostgreSQL</productname> 8.1, the
505 <structfield>idx_tup_read</> and <structfield>idx_tup_fetch</> counts
506 were essentially always equal. Now they can be different even without
507 considering bitmap scans, because <structfield>idx_tup_read</> counts
508 index entries retrieved from the index while <structfield>idx_tup_fetch</>
509 counts live rows fetched from the table; the latter will be less if any
510 dead or not-yet-committed rows are fetched using the index, or if any
511 heap fetches are avoided by means of an index-only scan.
516 The <structname>pg_statio_</> views are primarily useful to
517 determine the effectiveness of the buffer cache. When the number
518 of actual disk reads is much smaller than the number of buffer
519 hits, then the cache is satisfying most read requests without
520 invoking a kernel call. However, these statistics do not give the
521 entire story: due to the way in which <productname>PostgreSQL</>
522 handles disk I/O, data that is not in the
523 <productname>PostgreSQL</> buffer cache might still reside in the
524 kernel's I/O cache, and might therefore still be fetched without
525 requiring a physical read. Users interested in obtaining more
526 detailed information on <productname>PostgreSQL</> I/O behavior are
527 advised to use the <productname>PostgreSQL</> statistics collector
528 in combination with operating system utilities that allow insight
529 into the kernel's handling of I/O.
533 Other ways of looking at the statistics can be set up by writing
534 queries that use the same underlying statistics access functions as
535 these standard views do. These functions are listed in <xref
536 linkend="monitoring-stats-funcs-table">. The per-database access
537 functions take a database OID as argument to identify which
538 database to report on. The per-table and per-index functions take
539 a table or index OID. The functions for function-call statistics
540 take a function OID. (Note that only tables, indexes, and functions
541 in the current database can be seen with these functions.) The
542 per-server-process access functions take a server process
543 number, which ranges from one to the number of currently active
547 <table id="monitoring-stats-funcs-table">
548 <title>Statistics Access Functions</title>
553 <entry>Function</entry>
554 <entry>Return Type</entry>
555 <entry>Description</entry>
561 <entry><literal><function>pg_stat_get_db_numbackends</function>(<type>oid</type>)</literal></entry>
562 <entry><type>integer</type></entry>
564 Number of active server processes for database
569 <entry><literal><function>pg_stat_get_db_xact_commit</function>(<type>oid</type>)</literal></entry>
570 <entry><type>bigint</type></entry>
572 Number of transactions committed in database
577 <entry><literal><function>pg_stat_get_db_xact_rollback</function>(<type>oid</type>)</literal></entry>
578 <entry><type>bigint</type></entry>
580 Number of transactions rolled back in database
585 <entry><literal><function>pg_stat_get_db_blocks_fetched</function>(<type>oid</type>)</literal></entry>
586 <entry><type>bigint</type></entry>
588 Number of disk block fetch requests for database
593 <entry><literal><function>pg_stat_get_db_blocks_hit</function>(<type>oid</type>)</literal></entry>
594 <entry><type>bigint</type></entry>
596 Number of disk block fetch requests found in cache for database
601 <entry><literal><function>pg_stat_get_db_tuples_returned</function>(<type>oid</type>)</literal></entry>
602 <entry><type>bigint</type></entry>
604 Number of tuples returned for database
609 <entry><literal><function>pg_stat_get_db_tuples_fetched</function>(<type>oid</type>)</literal></entry>
610 <entry><type>bigint</type></entry>
612 Number of tuples fetched for database
617 <entry><literal><function>pg_stat_get_db_tuples_inserted</function>(<type>oid</type>)</literal></entry>
618 <entry><type>bigint</type></entry>
620 Number of tuples inserted in database
625 <entry><literal><function>pg_stat_get_db_tuples_updated</function>(<type>oid</type>)</literal></entry>
626 <entry><type>bigint</type></entry>
628 Number of tuples updated in database
633 <entry><literal><function>pg_stat_get_db_tuples_deleted</function>(<type>oid</type>)</literal></entry>
634 <entry><type>bigint</type></entry>
636 Number of tuples deleted in database
641 <entry><literal><function>pg_stat_get_db_conflict_tablespace</function>(<type>oid</type>)</literal></entry>
642 <entry><type>bigint</type></entry>
644 Number of queries canceled because of recovery conflict with dropped tablespaces in database
649 <entry><literal><function>pg_stat_get_db_conflict_lock</function>(<type>oid</type>)</literal></entry>
650 <entry><type>bigint</type></entry>
652 Number of queries canceled because of recovery conflict with locks in database
657 <entry><literal><function>pg_stat_get_db_conflict_snapshot</function>(<type>oid</type>)</literal></entry>
658 <entry><type>bigint</type></entry>
660 Number of queries canceled because of recovery conflict with old snapshots in database
665 <entry><literal><function>pg_stat_get_db_conflict_bufferpin</function>(<type>oid</type>)</literal></entry>
666 <entry><type>bigint</type></entry>
668 Number of queries canceled because of recovery conflict with pinned buffers in database
673 <entry><literal><function>pg_stat_get_db_conflict_startup_deadlock</function>(<type>oid</type>)</literal></entry>
674 <entry><type>bigint</type></entry>
676 Number of queries canceled because of recovery conflict with deadlocks in database
681 <entry><literal><function>pg_stat_get_db_stat_reset_time</function>(<type>oid</type>)</literal></entry>
682 <entry><type>timestamptz</type></entry>
684 Time of the last statistics reset for the database. Initialized to the
685 system time during the first connection to each database. The reset time
686 is updated when you call <function>pg_stat_reset</function> on the
687 database, as well as upon execution of
688 <function>pg_stat_reset_single_table_counters</function> against any
689 table or index in it.
694 <entry><literal><function>pg_stat_get_numscans</function>(<type>oid</type>)</literal></entry>
695 <entry><type>bigint</type></entry>
697 Number of sequential scans done when argument is a table,
698 or number of index scans done when argument is an index
703 <entry><literal><function>pg_stat_get_tuples_returned</function>(<type>oid</type>)</literal></entry>
704 <entry><type>bigint</type></entry>
706 Number of rows read by sequential scans when argument is a table,
707 or number of index entries returned when argument is an index
712 <entry><literal><function>pg_stat_get_tuples_fetched</function>(<type>oid</type>)</literal></entry>
713 <entry><type>bigint</type></entry>
715 Number of table rows fetched by bitmap scans when argument is a table,
716 or table rows fetched by simple index scans using the index
717 when argument is an index
722 <entry><literal><function>pg_stat_get_tuples_inserted</function>(<type>oid</type>)</literal></entry>
723 <entry><type>bigint</type></entry>
725 Number of rows inserted into table
730 <entry><literal><function>pg_stat_get_tuples_updated</function>(<type>oid</type>)</literal></entry>
731 <entry><type>bigint</type></entry>
733 Number of rows updated in table (includes HOT updates)
738 <entry><literal><function>pg_stat_get_tuples_deleted</function>(<type>oid</type>)</literal></entry>
739 <entry><type>bigint</type></entry>
741 Number of rows deleted from table
746 <entry><literal><function>pg_stat_get_tuples_hot_updated</function>(<type>oid</type>)</literal></entry>
747 <entry><type>bigint</type></entry>
749 Number of rows HOT-updated in table
754 <entry><literal><function>pg_stat_get_live_tuples</function>(<type>oid</type>)</literal></entry>
755 <entry><type>bigint</type></entry>
757 Number of live rows in table
762 <entry><literal><function>pg_stat_get_dead_tuples</function>(<type>oid</type>)</literal></entry>
763 <entry><type>bigint</type></entry>
765 Number of dead rows in table
770 <entry><literal><function>pg_stat_get_blocks_fetched</function>(<type>oid</type>)</literal></entry>
771 <entry><type>bigint</type></entry>
773 Number of disk block fetch requests for table or index
778 <entry><literal><function>pg_stat_get_blocks_hit</function>(<type>oid</type>)</literal></entry>
779 <entry><type>bigint</type></entry>
781 Number of disk block requests found in cache for table or index
786 <entry><literal><function>pg_stat_get_last_vacuum_time</function>(<type>oid</type>)</literal></entry>
787 <entry><type>timestamptz</type></entry>
789 Time of the last non-<option>FULL</option> vacuum initiated by the user on this table
794 <entry><literal><function>pg_stat_get_last_autovacuum_time</function>(<type>oid</type>)</literal></entry>
795 <entry><type>timestamptz</type></entry>
797 Time of the last vacuum initiated by the autovacuum daemon on this table
802 <entry><literal><function>pg_stat_get_last_analyze_time</function>(<type>oid</type>)</literal></entry>
803 <entry><type>timestamptz</type></entry>
805 Time of the last analyze initiated by the user on this table
810 <entry><literal><function>pg_stat_get_last_autoanalyze_time</function>(<type>oid</type>)</literal></entry>
811 <entry><type>timestamptz</type></entry>
813 Time of the last analyze initiated by the autovacuum daemon on this
819 <entry><literal><function>pg_stat_get_vacuum_count</function>(<type>oid</type>)</literal></entry>
820 <entry><type>bigint</type></entry>
822 The number of times this table has been non-<option>FULL</> vacuumed manually
827 <entry><literal><function>pg_stat_get_autovacuum_count</function>(<type>oid</type>)</literal></entry>
828 <entry><type>bigint</type></entry>
830 The number of times this table has been vacuumed by the autovacuum daemon
835 <entry><literal><function>pg_stat_get_analyze_count</function>(<type>oid</type>)</literal></entry>
836 <entry><type>bigint</type></entry>
838 The number of times this table has been analyzed manually
843 <entry><literal><function>pg_stat_get_autoanalyze_count</function>(<type>oid</type>)</literal></entry>
844 <entry><type>bigint</type></entry>
846 The number of times this table has been analyzed by the autovacuum daemon
851 <entry><literal><function>pg_stat_get_xact_numscans</function>(<type>oid</type>)</literal></entry>
852 <entry><type>bigint</type></entry>
854 Number of sequential scans done when argument is a table,
855 or number of index scans done when argument is an index, in the current transaction
860 <entry><literal><function>pg_stat_get_xact_tuples_returned</function>(<type>oid</type>)</literal></entry>
861 <entry><type>bigint</type></entry>
863 Number of rows read by sequential scans when argument is a table,
864 or number of index entries returned when argument is an index, in the current transaction
869 <entry><literal><function>pg_stat_get_xact_tuples_fetched</function>(<type>oid</type>)</literal></entry>
870 <entry><type>bigint</type></entry>
872 Number of table rows fetched by bitmap scans when argument is a table,
873 or table rows fetched by simple index scans using the index
874 when argument is an index, in the current transaction
879 <entry><literal><function>pg_stat_get_xact_tuples_inserted</function>(<type>oid</type>)</literal></entry>
880 <entry><type>bigint</type></entry>
882 Number of rows inserted into table, in the current transaction
887 <entry><literal><function>pg_stat_get_xact_tuples_updated</function>(<type>oid</type>)</literal></entry>
888 <entry><type>bigint</type></entry>
890 Number of rows updated in table (includes HOT updates), in the current transaction
895 <entry><literal><function>pg_stat_get_xact_tuples_deleted</function>(<type>oid</type>)</literal></entry>
896 <entry><type>bigint</type></entry>
898 Number of rows deleted from table, in the current transaction
903 <entry><literal><function>pg_stat_get_xact_tuples_hot_updated</function>(<type>oid</type>)</literal></entry>
904 <entry><type>bigint</type></entry>
906 Number of rows HOT-updated in table, in the current transaction
911 <entry><literal><function>pg_stat_get_xact_blocks_fetched</function>(<type>oid</type>)</literal></entry>
912 <entry><type>bigint</type></entry>
914 Number of disk block fetch requests for table or index, in the current transaction
919 <entry><literal><function>pg_stat_get_xact_blocks_hit</function>(<type>oid</type>)</literal></entry>
920 <entry><type>bigint</type></entry>
922 Number of disk block requests found in cache for table or index, in the current transaction
927 <!-- See also the entry for this in func.sgml -->
928 <entry><literal><function>pg_backend_pid()</function></literal></entry>
929 <entry><type>integer</type></entry>
931 Process ID of the server process attached to the current session
936 <entry><literal><function>pg_stat_get_activity</function>(<type>integer</type>)</literal></entry>
937 <entry><type>setof record</type></entry>
939 Returns a record of information about the backend with the specified PID, or
940 one record for each active backend in the system if <symbol>NULL</symbol> is
941 specified. The fields returned are a subset of those in the
942 <structname>pg_stat_activity</structname> view.
947 <entry><literal><function>pg_stat_get_function_calls</function>(<type>oid</type>)</literal></entry>
948 <entry><type>bigint</type></entry>
950 Number of times the function has been called
955 <entry><literal><function>pg_stat_get_function_time</function>(<type>oid</type>)</literal></entry>
956 <entry><type>bigint</type></entry>
958 Total wall clock time spent in the function, in microseconds. Includes
959 the time spent in functions called by this one.
964 <entry><literal><function>pg_stat_get_function_self_time</function>(<type>oid</type>)</literal></entry>
965 <entry><type>bigint</type></entry>
967 Time spent in only this function. Time spent in called functions
973 <entry><literal><function>pg_stat_get_xact_function_calls</function>(<type>oid</type>)</literal></entry>
974 <entry><type>bigint</type></entry>
976 Number of times the function has been called, in the current transaction.
981 <entry><literal><function>pg_stat_get_xact_function_time</function>(<type>oid</type>)</literal></entry>
982 <entry><type>bigint</type></entry>
984 Total wall clock time spent in the function, in microseconds, in the
985 current transaction. Includes the time spent in functions called by
991 <entry><literal><function>pg_stat_get_xact_function_self_time</function>(<type>oid</type>)</literal></entry>
992 <entry><type>bigint</type></entry>
994 Time spent in only this function, in the current transaction. Time
995 spent in called functions is excluded.
1000 <entry><literal><function>pg_stat_get_backend_idset()</function></literal></entry>
1001 <entry><type>setof integer</type></entry>
1003 Set of currently active server process numbers (from 1 to the
1004 number of active server processes). See usage example in the text.
1009 <entry><literal><function>pg_stat_get_backend_pid</function>(<type>integer</type>)</literal></entry>
1010 <entry><type>integer</type></entry>
1012 Process ID of the given server process
1017 <entry><literal><function>pg_stat_get_backend_dbid</function>(<type>integer</type>)</literal></entry>
1018 <entry><type>oid</type></entry>
1020 Database ID of the given server process
1025 <entry><literal><function>pg_stat_get_backend_userid</function>(<type>integer</type>)</literal></entry>
1026 <entry><type>oid</type></entry>
1028 User ID of the given server process
1033 <entry><literal><function>pg_stat_get_backend_activity</function>(<type>integer</type>)</literal></entry>
1034 <entry><type>text</type></entry>
1036 Active command of the given server process, but only if the
1037 current user is a superuser or the same user as that of
1038 the session being queried (and
1039 <varname>track_activities</varname> is on)
1044 <entry><literal><function>pg_stat_get_backend_waiting</function>(<type>integer</type>)</literal></entry>
1045 <entry><type>boolean</type></entry>
1047 True if the given server process is waiting for a lock,
1048 but only if the current user is a superuser or the same user as that of
1049 the session being queried (and
1050 <varname>track_activities</varname> is on)
1055 <entry><literal><function>pg_stat_get_backend_activity_start</function>(<type>integer</type>)</literal></entry>
1056 <entry><type>timestamp with time zone</type></entry>
1058 The time at which the given server process' currently
1059 executing query was started, but only if the
1060 current user is a superuser or the same user as that of
1061 the session being queried (and
1062 <varname>track_activities</varname> is on)
1067 <entry><literal><function>pg_stat_get_backend_xact_start</function>(<type>integer</type>)</literal></entry>
1068 <entry><type>timestamp with time zone</type></entry>
1070 The time at which the given server process' currently
1071 executing transaction was started, but only if the
1072 current user is a superuser or the same user as that of
1073 the session being queried (and
1074 <varname>track_activities</varname> is on)
1079 <entry><literal><function>pg_stat_get_backend_start</function>(<type>integer</type>)</literal></entry>
1080 <entry><type>timestamp with time zone</type></entry>
1082 The time at which the given server process was started, or
1083 null if the current user is not a superuser nor the same user
1084 as that of the session being queried
1089 <entry><literal><function>pg_stat_get_backend_client_addr</function>(<type>integer</type>)</literal></entry>
1090 <entry><type>inet</type></entry>
1092 The IP address of the client connected to the given
1093 server process; null if the connection is over a Unix domain
1094 socket, also null if the current user is not a superuser nor
1095 the same user as that of the session being queried
1100 <entry><literal><function>pg_stat_get_backend_client_port</function>(<type>integer</type>)</literal></entry>
1101 <entry><type>integer</type></entry>
1103 The TCP port number of the client connected to the given
1104 server process; -1 if the connection is over a Unix domain
1105 socket, null if the current user is not a superuser nor the
1106 same user as that of the session being queried
1111 <entry><literal><function>pg_stat_get_bgwriter_timed_checkpoints()</function></literal></entry>
1112 <entry><type>bigint</type></entry>
1114 Number of times the background writer has started timed checkpoints
1115 (because the <varname>checkpoint_timeout</varname> time has expired)
1120 <entry><literal><function>pg_stat_get_bgwriter_requested_checkpoints()</function></literal></entry>
1121 <entry><type>bigint</type></entry>
1123 Number of times the background writer has started checkpoints based
1124 on requests from backends because the <varname>checkpoint_segments</varname>
1125 has been exceeded or because the <command>CHECKPOINT</command>
1126 command has been issued
1131 <entry><literal><function>pg_stat_get_bgwriter_buf_written_checkpoints()</function></literal></entry>
1132 <entry><type>bigint</type></entry>
1134 Number of buffers written by the background writer during checkpoints
1139 <entry><literal><function>pg_stat_get_bgwriter_buf_written_clean()</function></literal></entry>
1140 <entry><type>bigint</type></entry>
1142 Number of buffers written by the background writer for routine cleaning of
1148 <entry><literal><function>pg_stat_get_bgwriter_maxwritten_clean()</function></literal></entry>
1149 <entry><type>bigint</type></entry>
1151 Number of times the background writer has stopped its cleaning scan because
1152 it has written more buffers than specified in the
1153 <varname>bgwriter_lru_maxpages</varname> parameter
1158 <entry><literal><function>pg_stat_get_bgwriter_stat_reset_time()</function></literal></entry>
1159 <entry><type>timestamptz</type></entry>
1161 Time of the last statistics reset for the background writer, updated
1162 when executing <function>pg_stat_reset_shared('bgwriter')</function>
1163 on the database cluster.
1168 <entry><literal><function>pg_stat_get_buf_written_backend()</function></literal></entry>
1169 <entry><type>bigint</type></entry>
1171 Number of buffers written by backends because they needed
1172 to allocate a new buffer
1177 <entry><literal><function>pg_stat_get_buf_alloc()</function></literal></entry>
1178 <entry><type>bigint</type></entry>
1180 Total number of buffer allocations
1185 <entry><literal><function>pg_stat_get_wal_senders()</function></literal></entry>
1186 <entry><type>setof record</type></entry>
1188 One record for each active wal sender. The fields returned are a subset
1189 of those in the <structname>pg_stat_replication</structname> view.
1194 <entry><literal><function>pg_stat_clear_snapshot()</function></literal></entry>
1195 <entry><type>void</type></entry>
1197 Discard the current statistics snapshot
1202 <entry><literal><function>pg_stat_reset()</function></literal></entry>
1203 <entry><type>void</type></entry>
1205 Reset all statistics counters for the current database to zero
1206 (requires superuser privileges)
1211 <entry><literal><function>pg_stat_reset_shared</function>(text)</literal></entry>
1212 <entry><type>void</type></entry>
1214 Reset some of the shared statistics counters for the database cluster to
1215 zero (requires superuser privileges). Calling
1216 <literal>pg_stat_reset_shared('bgwriter')</> will zero all the values shown by
1217 <structname>pg_stat_bgwriter</>.
1222 <entry><literal><function>pg_stat_reset_single_table_counters</function>(oid)</literal></entry>
1223 <entry><type>void</type></entry>
1225 Reset statistics for a single table or index in the current database to
1226 zero (requires superuser privileges)
1231 <entry><literal><function>pg_stat_reset_single_function_counters</function>(oid)</literal></entry>
1232 <entry><type>void</type></entry>
1234 Reset statistics for a single function in the current database to
1235 zero (requires superuser privileges)
1244 <function>pg_stat_get_blocks_fetched</function> minus
1245 <function>pg_stat_get_blocks_hit</function> gives the number of kernel
1246 <function>read()</> calls issued for the table, index, or
1247 database; the number of actual physical reads is usually
1248 lower due to kernel-level buffering. The <literal>*_blks_read</>
1249 statistics columns use this subtraction, i.e., fetched minus hit.
1254 All functions to access information about backends are indexed by backend id
1255 number, except <function>pg_stat_get_activity</function> which is indexed by PID.
1256 The function <function>pg_stat_get_backend_idset</function> provides
1257 a convenient way to generate one row for each active server process. For
1258 example, to show the <acronym>PID</>s and current queries of all server processes:
1261 SELECT pg_stat_get_backend_pid(s.backendid) AS procpid,
1262 pg_stat_get_backend_activity(s.backendid) AS current_query
1263 FROM (SELECT pg_stat_get_backend_idset() AS backendid) AS s;
1270 <sect1 id="monitoring-locks">
1271 <title>Viewing Locks</title>
1273 <indexterm zone="monitoring-locks">
1274 <primary>lock</primary>
1275 <secondary>monitoring</secondary>
1279 Another useful tool for monitoring database activity is the
1280 <structname>pg_locks</structname> system table. It allows the
1281 database administrator to view information about the outstanding
1282 locks in the lock manager. For example, this capability can be used
1288 View all the locks currently outstanding, all the locks on
1289 relations in a particular database, all the locks on a
1290 particular relation, or all the locks held by a particular
1291 <productname>PostgreSQL</productname> session.
1297 Determine the relation in the current database with the most
1298 ungranted locks (which might be a source of contention among
1305 Determine the effect of lock contention on overall database
1306 performance, as well as the extent to which contention varies
1307 with overall database traffic.
1312 Details of the <structname>pg_locks</structname> view appear in
1313 <xref linkend="view-pg-locks">.
1314 For more information on locking and managing concurrency with
1315 <productname>PostgreSQL</productname>, refer to <xref linkend="mvcc">.
1319 <sect1 id="dynamic-trace">
1320 <title>Dynamic Tracing</title>
1322 <indexterm zone="dynamic-trace">
1323 <primary>DTrace</primary>
1327 <productname>PostgreSQL</productname> provides facilities to support
1328 dynamic tracing of the database server. This allows an external
1329 utility to be called at specific points in the code and thereby trace
1334 A number of probes or trace points are already inserted into the source
1335 code. These probes are intended to be used by database developers and
1336 administrators. By default the probes are not compiled into
1337 <productname>PostgreSQL</productname>; the user needs to explicitly tell
1338 the configure script to make the probes available.
1343 <ulink url="http://opensolaris.org/os/community/dtrace/">DTrace</ulink>
1344 utility is supported, which is available
1345 on OpenSolaris, Solaris 10, and Mac OS X Leopard. It is expected that
1346 DTrace will be available in the future on FreeBSD and possibly other
1347 operating systems. The
1348 <ulink url="http://sourceware.org/systemtap/">SystemTap</ulink> project
1349 for Linux also provides a DTrace equivalent. Supporting other dynamic
1350 tracing utilities is theoretically possible by changing the definitions for
1351 the macros in <filename>src/include/utils/probes.h</>.
1354 <sect2 id="compiling-for-trace">
1355 <title>Compiling for Dynamic Tracing</title>
1358 By default, probes are not available, so you will need to
1359 explicitly tell the configure script to make the probes available
1360 in <productname>PostgreSQL</productname>. To include DTrace support
1361 specify <option>--enable-dtrace</> to configure. See <xref
1362 linkend="install-procedure"> for further information.
1366 <sect2 id="trace-points">
1367 <title>Built-in Probes</title>
1370 A number of standard probes are provided in the source code,
1371 as shown in <xref linkend="dtrace-probe-point-table">;
1372 <xref linkend="typedefs-table">
1373 shows the types used in the probes. More probes can certainly be
1374 added to enhance <productname>PostgreSQL</>'s observability.
1377 <table id="dtrace-probe-point-table">
1378 <title>Built-in DTrace Probes</title>
1383 <entry>Parameters</entry>
1384 <entry>Description</entry>
1391 <entry>transaction-start</entry>
1392 <entry>(LocalTransactionId)</entry>
1393 <entry>Probe that fires at the start of a new transaction.
1394 arg0 is the transaction ID.</entry>
1397 <entry>transaction-commit</entry>
1398 <entry>(LocalTransactionId)</entry>
1399 <entry>Probe that fires when a transaction completes successfully.
1400 arg0 is the transaction ID.</entry>
1403 <entry>transaction-abort</entry>
1404 <entry>(LocalTransactionId)</entry>
1405 <entry>Probe that fires when a transaction completes unsuccessfully.
1406 arg0 is the transaction ID.</entry>
1409 <entry>query-start</entry>
1410 <entry>(const char *)</entry>
1411 <entry>Probe that fires when the processing of a query is started.
1412 arg0 is the query string.</entry>
1415 <entry>query-done</entry>
1416 <entry>(const char *)</entry>
1417 <entry>Probe that fires when the processing of a query is complete.
1418 arg0 is the query string.</entry>
1421 <entry>query-parse-start</entry>
1422 <entry>(const char *)</entry>
1423 <entry>Probe that fires when the parsing of a query is started.
1424 arg0 is the query string.</entry>
1427 <entry>query-parse-done</entry>
1428 <entry>(const char *)</entry>
1429 <entry>Probe that fires when the parsing of a query is complete.
1430 arg0 is the query string.</entry>
1433 <entry>query-rewrite-start</entry>
1434 <entry>(const char *)</entry>
1435 <entry>Probe that fires when the rewriting of a query is started.
1436 arg0 is the query string.</entry>
1439 <entry>query-rewrite-done</entry>
1440 <entry>(const char *)</entry>
1441 <entry>Probe that fires when the rewriting of a query is complete.
1442 arg0 is the query string.</entry>
1445 <entry>query-plan-start</entry>
1447 <entry>Probe that fires when the planning of a query is started.</entry>
1450 <entry>query-plan-done</entry>
1452 <entry>Probe that fires when the planning of a query is complete.</entry>
1455 <entry>query-execute-start</entry>
1457 <entry>Probe that fires when the execution of a query is started.</entry>
1460 <entry>query-execute-done</entry>
1462 <entry>Probe that fires when the execution of a query is complete.</entry>
1465 <entry>statement-status</entry>
1466 <entry>(const char *)</entry>
1467 <entry>Probe that fires anytime the server process updates its
1468 <structname>pg_stat_activity</>.<structfield>current_query</> status.
1469 arg0 is the new status string.</entry>
1472 <entry>checkpoint-start</entry>
1473 <entry>(int)</entry>
1474 <entry>Probe that fires when a checkpoint is started.
1475 arg0 holds the bitwise flags used to distinguish different checkpoint
1476 types, such as shutdown, immediate or force.</entry>
1479 <entry>checkpoint-done</entry>
1480 <entry>(int, int, int, int, int)</entry>
1481 <entry>Probe that fires when a checkpoint is complete.
1482 (The probes listed next fire in sequence during checkpoint processing.)
1483 arg0 is the number of buffers written. arg1 is the total number of
1484 buffers. arg2, arg3 and arg4 contain the number of xlog file(s) added,
1485 removed and recycled respectively.</entry>
1488 <entry>clog-checkpoint-start</entry>
1489 <entry>(bool)</entry>
1490 <entry>Probe that fires when the CLOG portion of a checkpoint is started.
1491 arg0 is true for normal checkpoint, false for shutdown
1495 <entry>clog-checkpoint-done</entry>
1496 <entry>(bool)</entry>
1497 <entry>Probe that fires when the CLOG portion of a checkpoint is
1498 complete. arg0 has the same meaning as for clog-checkpoint-start.</entry>
1501 <entry>subtrans-checkpoint-start</entry>
1502 <entry>(bool)</entry>
1503 <entry>Probe that fires when the SUBTRANS portion of a checkpoint is
1505 arg0 is true for normal checkpoint, false for shutdown
1509 <entry>subtrans-checkpoint-done</entry>
1510 <entry>(bool)</entry>
1511 <entry>Probe that fires when the SUBTRANS portion of a checkpoint is
1512 complete. arg0 has the same meaning as for
1513 subtrans-checkpoint-start.</entry>
1516 <entry>multixact-checkpoint-start</entry>
1517 <entry>(bool)</entry>
1518 <entry>Probe that fires when the MultiXact portion of a checkpoint is
1520 arg0 is true for normal checkpoint, false for shutdown
1524 <entry>multixact-checkpoint-done</entry>
1525 <entry>(bool)</entry>
1526 <entry>Probe that fires when the MultiXact portion of a checkpoint is
1527 complete. arg0 has the same meaning as for
1528 multixact-checkpoint-start.</entry>
1531 <entry>buffer-checkpoint-start</entry>
1532 <entry>(int)</entry>
1533 <entry>Probe that fires when the buffer-writing portion of a checkpoint
1535 arg0 holds the bitwise flags used to distinguish different checkpoint
1536 types, such as shutdown, immediate or force.</entry>
1539 <entry>buffer-sync-start</entry>
1540 <entry>(int, int)</entry>
1541 <entry>Probe that fires when we begin to write dirty buffers during
1542 checkpoint (after identifying which buffers must be written).
1543 arg0 is the total number of buffers.
1544 arg1 is the number that are currently dirty and need to be written.</entry>
1547 <entry>buffer-sync-written</entry>
1548 <entry>(int)</entry>
1549 <entry>Probe that fires after each buffer is written during checkpoint.
1550 arg0 is the ID number of the buffer.</entry>
1553 <entry>buffer-sync-done</entry>
1554 <entry>(int, int, int)</entry>
1555 <entry>Probe that fires when all dirty buffers have been written.
1556 arg0 is the total number of buffers.
1557 arg1 is the number of buffers actually written by the checkpoint process.
1558 arg2 is the number that were expected to be written (arg1 of
1559 buffer-sync-start); any difference reflects other processes flushing
1560 buffers during the checkpoint.</entry>
1563 <entry>buffer-checkpoint-sync-start</entry>
1565 <entry>Probe that fires after dirty buffers have been written to the
1566 kernel, and before starting to issue fsync requests.</entry>
1569 <entry>buffer-checkpoint-done</entry>
1571 <entry>Probe that fires when syncing of buffers to disk is
1575 <entry>twophase-checkpoint-start</entry>
1577 <entry>Probe that fires when the two-phase portion of a checkpoint is
1581 <entry>twophase-checkpoint-done</entry>
1583 <entry>Probe that fires when the two-phase portion of a checkpoint is
1587 <entry>buffer-read-start</entry>
1588 <entry>(ForkNumber, BlockNumber, Oid, Oid, Oid, int, bool)</entry>
1589 <entry>Probe that fires when a buffer read is started.
1590 arg0 and arg1 contain the fork and block numbers of the page (but
1591 arg1 will be -1 if this is a relation extension request).
1592 arg2, arg3, and arg4 contain the tablespace, database, and relation OIDs
1593 identifying the relation.
1594 arg5 is the ID of the backend which created the temporary relation for a
1595 local buffer, or InvalidBackendId (-1) for a shared buffer.
1596 arg6 is true for a relation extension request, false for normal
1600 <entry>buffer-read-done</entry>
1601 <entry>(ForkNumber, BlockNumber, Oid, Oid, Oid, int, bool, bool)</entry>
1602 <entry>Probe that fires when a buffer read is complete.
1603 arg0 and arg1 contain the fork and block numbers of the page (if this
1604 is a relation extension request, arg1 now contains the block number
1605 of the newly added block).
1606 arg2, arg3, and arg4 contain the tablespace, database, and relation OIDs
1607 identifying the relation.
1608 arg5 is the ID of the backend which created the temporary relation for a
1609 local buffer, or InvalidBackendId (-1) for a shared buffer.
1610 arg6 is true for a relation extension request, false for normal
1612 arg7 is true if the buffer was found in the pool, false if not.</entry>
1615 <entry>buffer-flush-start</entry>
1616 <entry>(ForkNumber, BlockNumber, Oid, Oid, Oid)</entry>
1617 <entry>Probe that fires before issuing any write request for a shared
1619 arg0 and arg1 contain the fork and block numbers of the page.
1620 arg2, arg3, and arg4 contain the tablespace, database, and relation OIDs
1621 identifying the relation.</entry>
1624 <entry>buffer-flush-done</entry>
1625 <entry>(ForkNumber, BlockNumber, Oid, Oid, Oid)</entry>
1626 <entry>Probe that fires when a write request is complete. (Note
1627 that this just reflects the time to pass the data to the kernel;
1628 it's typically not actually been written to disk yet.)
1629 The arguments are the same as for buffer-flush-start.</entry>
1632 <entry>buffer-write-dirty-start</entry>
1633 <entry>(ForkNumber, BlockNumber, Oid, Oid, Oid)</entry>
1634 <entry>Probe that fires when a server process begins to write a dirty
1635 buffer. (If this happens often, it implies that
1636 <xref linkend="guc-shared-buffers"> is too
1637 small or the bgwriter control parameters need adjustment.)
1638 arg0 and arg1 contain the fork and block numbers of the page.
1639 arg2, arg3, and arg4 contain the tablespace, database, and relation OIDs
1640 identifying the relation.</entry>
1643 <entry>buffer-write-dirty-done</entry>
1644 <entry>(ForkNumber, BlockNumber, Oid, Oid, Oid)</entry>
1645 <entry>Probe that fires when a dirty-buffer write is complete.
1646 The arguments are the same as for buffer-write-dirty-start.</entry>
1649 <entry>wal-buffer-write-dirty-start</entry>
1651 <entry>Probe that fires when when a server process begins to write a
1652 dirty WAL buffer because no more WAL buffer space is available.
1653 (If this happens often, it implies that
1654 <xref linkend="guc-wal-buffers"> is too small.)</entry>
1657 <entry>wal-buffer-write-dirty-done</entry>
1659 <entry>Probe that fires when a dirty WAL buffer write is complete.</entry>
1662 <entry>xlog-insert</entry>
1663 <entry>(unsigned char, unsigned char)</entry>
1664 <entry>Probe that fires when a WAL record is inserted.
1665 arg0 is the resource manager (rmid) for the record.
1666 arg1 contains the info flags.</entry>
1669 <entry>xlog-switch</entry>
1671 <entry>Probe that fires when a WAL segment switch is requested.</entry>
1674 <entry>smgr-md-read-start</entry>
1675 <entry>(ForkNumber, BlockNumber, Oid, Oid, Oid, int)</entry>
1676 <entry>Probe that fires when beginning to read a block from a relation.
1677 arg0 and arg1 contain the fork and block numbers of the page.
1678 arg2, arg3, and arg4 contain the tablespace, database, and relation OIDs
1679 identifying the relation.
1680 arg5 is the ID of the backend which created the temporary relation for a
1681 local buffer, or InvalidBackendId (-1) for a shared buffer.</entry>
1684 <entry>smgr-md-read-done</entry>
1685 <entry>(ForkNumber, BlockNumber, Oid, Oid, Oid, int, int, int)</entry>
1686 <entry>Probe that fires when a block read is complete.
1687 arg0 and arg1 contain the fork and block numbers of the page.
1688 arg2, arg3, and arg4 contain the tablespace, database, and relation OIDs
1689 identifying the relation.
1690 arg5 is the ID of the backend which created the temporary relation for a
1691 local buffer, or InvalidBackendId (-1) for a shared buffer.
1692 arg6 is the number of bytes actually read, while arg7 is the number
1693 requested (if these are different it indicates trouble).</entry>
1696 <entry>smgr-md-write-start</entry>
1697 <entry>(ForkNumber, BlockNumber, Oid, Oid, Oid, int)</entry>
1698 <entry>Probe that fires when beginning to write a block to a relation.
1699 arg0 and arg1 contain the fork and block numbers of the page.
1700 arg2, arg3, and arg4 contain the tablespace, database, and relation OIDs
1701 identifying the relation.
1702 arg5 is the ID of the backend which created the temporary relation for a
1703 local buffer, or InvalidBackendId (-1) for a shared buffer.</entry>
1706 <entry>smgr-md-write-done</entry>
1707 <entry>(ForkNumber, BlockNumber, Oid, Oid, Oid, int, int, int)</entry>
1708 <entry>Probe that fires when a block write is complete.
1709 arg0 and arg1 contain the fork and block numbers of the page.
1710 arg2, arg3, and arg4 contain the tablespace, database, and relation OIDs
1711 identifying the relation.
1712 arg5 is the ID of the backend which created the temporary relation for a
1713 local buffer, or InvalidBackendId (-1) for a shared buffer.
1714 arg6 is the number of bytes actually written, while arg7 is the number
1715 requested (if these are different it indicates trouble).</entry>
1718 <entry>sort-start</entry>
1719 <entry>(int, bool, int, int, bool)</entry>
1720 <entry>Probe that fires when a sort operation is started.
1721 arg0 indicates heap, index or datum sort.
1722 arg1 is true for unique-value enforcement.
1723 arg2 is the number of key columns.
1724 arg3 is the number of kilobytes of work memory allowed.
1725 arg4 is true if random access to the sort result is required.</entry>
1728 <entry>sort-done</entry>
1729 <entry>(bool, long)</entry>
1730 <entry>Probe that fires when a sort is complete.
1731 arg0 is true for external sort, false for internal sort.
1732 arg1 is the number of disk blocks used for an external sort,
1733 or kilobytes of memory used for an internal sort.</entry>
1736 <entry>lwlock-acquire</entry>
1737 <entry>(LWLockId, LWLockMode)</entry>
1738 <entry>Probe that fires when an LWLock has been acquired.
1739 arg0 is the LWLock's ID.
1740 arg1 is the requested lock mode, either exclusive or shared.</entry>
1743 <entry>lwlock-release</entry>
1744 <entry>(LWLockId)</entry>
1745 <entry>Probe that fires when an LWLock has been released (but note
1746 that any released waiters have not yet been awakened).
1747 arg0 is the LWLock's ID.</entry>
1750 <entry>lwlock-wait-start</entry>
1751 <entry>(LWLockId, LWLockMode)</entry>
1752 <entry>Probe that fires when an LWLock was not immediately available and
1753 a server process has begun to wait for the lock to become available.
1754 arg0 is the LWLock's ID.
1755 arg1 is the requested lock mode, either exclusive or shared.</entry>
1758 <entry>lwlock-wait-done</entry>
1759 <entry>(LWLockId, LWLockMode)</entry>
1760 <entry>Probe that fires when a server process has been released from its
1761 wait for an LWLock (it does not actually have the lock yet).
1762 arg0 is the LWLock's ID.
1763 arg1 is the requested lock mode, either exclusive or shared.</entry>
1766 <entry>lwlock-condacquire</entry>
1767 <entry>(LWLockId, LWLockMode)</entry>
1768 <entry>Probe that fires when an LWLock was successfully acquired when the
1769 caller specified no waiting.
1770 arg0 is the LWLock's ID.
1771 arg1 is the requested lock mode, either exclusive or shared.</entry>
1774 <entry>lwlock-condacquire-fail</entry>
1775 <entry>(LWLockId, LWLockMode)</entry>
1776 <entry>Probe that fires when an LWLock was not successfully acquired when
1777 the caller specified no waiting.
1778 arg0 is the LWLock's ID.
1779 arg1 is the requested lock mode, either exclusive or shared.</entry>
1782 <entry>lock-wait-start</entry>
1783 <entry>(unsigned int, unsigned int, unsigned int, unsigned int, unsigned int, LOCKMODE)</entry>
1784 <entry>Probe that fires when a request for a heavyweight lock (lmgr lock)
1785 has begun to wait because the lock is not available.
1786 arg0 through arg3 are the tag fields identifying the object being
1787 locked. arg4 indicates the type of object being locked.
1788 arg5 indicates the lock type being requested.</entry>
1791 <entry>lock-wait-done</entry>
1792 <entry>(unsigned int, unsigned int, unsigned int, unsigned int, unsigned int, LOCKMODE)</entry>
1793 <entry>Probe that fires when a request for a heavyweight lock (lmgr lock)
1794 has finished waiting (i.e., has acquired the lock).
1795 The arguments are the same as for lock-wait-start.</entry>
1798 <entry>deadlock-found</entry>
1800 <entry>Probe that fires when a deadlock is found by the deadlock
1808 <table id="typedefs-table">
1809 <title>Defined Types Used in Probe Parameters</title>
1814 <entry>Definition</entry>
1821 <entry>LocalTransactionId</entry>
1822 <entry>unsigned int</entry>
1825 <entry>LWLockId</entry>
1829 <entry>LWLockMode</entry>
1833 <entry>LOCKMODE</entry>
1837 <entry>BlockNumber</entry>
1838 <entry>unsigned int</entry>
1842 <entry>unsigned int</entry>
1845 <entry>ForkNumber</entry>
1860 <sect2 id="using-trace-points">
1861 <title>Using Probes</title>
1864 The example below shows a DTrace script for analyzing transaction
1865 counts in the system, as an alternative to snapshotting
1866 <structname>pg_stat_database</> before and after a performance test:
1868 #!/usr/sbin/dtrace -qs
1870 postgresql$1:::transaction-start
1872 @start["Start"] = count();
1873 self->ts = timestamp;
1876 postgresql$1:::transaction-abort
1878 @abort["Abort"] = count();
1881 postgresql$1:::transaction-commit
1884 @commit["Commit"] = count();
1885 @time["Total time (ns)"] = sum(timestamp - self->ts);
1889 When executed, the example D script gives output such as:
1891 # ./txn_count.d `pgrep -n postgres` or ./txn_count.d <PID>
1896 Total time (ns) 2312105013
1902 SystemTap uses a different notation for trace scripts than DTrace does,
1903 even though the underlying trace points are compatible. One point worth
1904 noting is that at this writing, SystemTap scripts must reference probe
1905 names using double underscores in place of hyphens. This is expected to
1906 be fixed in future SystemTap releases.
1911 You should remember that DTrace scripts need to be carefully written and
1912 debugged, otherwise the trace information collected might
1913 be meaningless. In most cases where problems are found it is the
1914 instrumentation that is at fault, not the underlying system. When
1915 discussing information found using dynamic tracing, be sure to enclose
1916 the script used to allow that too to be checked and discussed.
1920 More example scripts can be found in the PgFoundry
1921 <ulink url="http://pgfoundry.org/projects/dtrace/">dtrace project</ulink>.
1925 <sect2 id="defining-trace-points">
1926 <title>Defining New Probes</title>
1929 New probes can be defined within the code wherever the developer
1930 desires, though this will require a recompilation. Below are the steps
1931 for inserting new probes:
1937 Decide on probe names and data to be made available through the probes
1943 Add the probe definitions to <filename>src/backend/utils/probes.d</>
1949 Include <filename>pg_trace.h</> if it is not already present in the
1950 module(s) containing the probe points, and insert
1951 <literal>TRACE_POSTGRESQL</> probe macros at the desired locations
1958 Recompile and verify that the new probes are available
1964 <title>Example:</title>
1966 Here is an example of how you would add a probe to trace all new
1967 transactions by transaction ID.
1974 Decide that the probe will be named <literal>transaction-start</> and
1975 requires a parameter of type LocalTransactionId
1981 Add the probe definition to <filename>src/backend/utils/probes.d</>:
1983 probe transaction__start(LocalTransactionId);
1985 Note the use of the double underline in the probe name. In a DTrace
1986 script using the probe, the double underline needs to be replaced with a
1987 hyphen, so <literal>transaction-start</> is the name to document for
1994 At compile time, <literal>transaction__start</> is converted to a macro
1995 called <literal>TRACE_POSTGRESQL_TRANSACTION_START</> (notice the
1996 underscores are single here), which is available by including
1997 <filename>pg_trace.h</>. Add the macro call to the appropriate location
1998 in the source code. In this case, it looks like the following:
2001 TRACE_POSTGRESQL_TRANSACTION_START(vxid.localTransactionId);
2008 After recompiling and running the new binary, check that your newly added
2009 probe is available by executing the following DTrace command. You
2010 should see similar output:
2012 # dtrace -ln transaction-start
2013 ID PROVIDER MODULE FUNCTION NAME
2014 18705 postgresql49878 postgres StartTransactionCommand transaction-start
2015 18755 postgresql49877 postgres StartTransactionCommand transaction-start
2016 18805 postgresql49876 postgres StartTransactionCommand transaction-start
2017 18855 postgresql49875 postgres StartTransactionCommand transaction-start
2018 18986 postgresql49873 postgres StartTransactionCommand transaction-start
2025 There are a few things to be careful about when adding trace macros
2031 You should take care that the data types specified for a probe's
2032 parameters match the data types of the variables used in the macro.
2033 Otherwise, you will get compilation errors.
2040 On most platforms, if <productname>PostgreSQL</productname> is
2041 built with <option>--enable-dtrace</>, the arguments to a trace
2042 macro will be evaluated whenever control passes through the
2043 macro, <emphasis>even if no tracing is being done</>. This is
2044 usually not worth worrying about if you are just reporting the
2045 values of a few local variables. But beware of putting expensive
2046 function calls into the arguments. If you need to do that,
2047 consider protecting the macro with a check to see if the trace
2048 is actually enabled:
2051 if (TRACE_POSTGRESQL_TRANSACTION_START_ENABLED())
2052 TRACE_POSTGRESQL_TRANSACTION_START(some_function(...));
2055 Each trace macro has a corresponding <literal>ENABLED</> macro.