From 5f2b0893871cce1ffb77ac7f13b3fba227e6f11f Mon Sep 17 00:00:00 2001 From: Tom Lane Date: Sun, 29 Apr 2012 15:35:57 -0400 Subject: [PATCH] Further editorialization on the new documentation for statistics views. Get rid of the per-column documentation of underlying functions, which did far more to clutter the view descriptions than it did to be helpful, and was rather incomplete and typo-ridden anyway. Instead suggest that people consult the definitions of the standard views to see the underlying functions. The older functions for obtaining individual facts about backends are now somewhat obsoleted by pg_stat_get_activity, which means that they are not documented by any standard view. So I put that information into a separate table. (Maybe we should just deprecate them instead?) In passing, fix a couple more documentation errors. --- doc/src/sgml/monitoring.sgml | 423 +++++++++++++++-------------------- 1 file changed, 185 insertions(+), 238 deletions(-) diff --git a/doc/src/sgml/monitoring.sgml b/doc/src/sgml/monitoring.sgml index 1f4f0929e3..8378f039ff 100644 --- a/doc/src/sgml/monitoring.sgml +++ b/doc/src/sgml/monitoring.sgml @@ -479,9 +479,7 @@ postgres: user database host datid oid - OID of the database this backend is connected to. - This value can also be returned by directly calling - the pg_stat_get_backend_dbid function. + OID of the database this backend is connected to datname @@ -491,16 +489,12 @@ postgres: user database host pid integer - Process ID of this backend. - This value can also be returned by directly calling - the pg_stat_get_backend_pid. + Process ID of this backend usesysid oid - OID of the user logged into this backend. - This value can also be returned by directly calling - the pg_stat_get_backend_userid. + OID of the user logged into this backend usename @@ -517,11 +511,9 @@ postgres: user database host client_addr inet IP address of the client connected to this backend. - If this field is not set, it indicates either that the client is + If this field is null, it indicates either that the client is connected via a Unix socket on the server machine or that this is an internal process such as autovacuum. - This value can also be returned by directly calling - the pg_stat_get_backend_client_addr. @@ -529,7 +521,7 @@ postgres: user database host text Host name of the connected client, as reported by a reverse DNS lookup of client_addr. This field will - only be set for IP connections, and only when is enabled. @@ -537,38 +529,31 @@ postgres: user database host client_port integer TCP port number that the client is using for communication - with the backend, or NULL if a Unix socket is used. - This value can also be returned by directly calling - the pg_stat_get_backend_client_port. + with this backend, or -1 if a Unix socket is used backend_start timestamp with time zone Time when this process was started, i.e., when the - client connected to the server. - This value can also be returned by directly calling - the pg_stat_get_backend_start. + client connected to the server xact_start timestamp with time zone - Time when the current transaction was started. If the current - query is the first of its transaction, this value is equal to the + Time when this process' current transaction was started, or null + if no transaction is active. If the current + query is the first of its transaction, this column is equal to the query_start column. - This value can also be returned by directly calling - the pg_stat_get_backend_xact_start. query_start timestamp with time zone Time when the currently active query was started, or if - state is idle, when the last query - was started. - This value can also be returned by directly calling - the pg_stat_get_backend_activity_start. + state is not active, when the last query + was started @@ -579,10 +564,7 @@ postgres: user database host waiting boolean - True if the backend is currently waiting on a lock. - This value can also be returned by directly calling - the pg_stat_get_backend_waiting. - + True if this backend is currently waiting on a lock state @@ -631,7 +613,7 @@ postgres: user database host query text - Text of the backend's most recent query. If + Text of this backend's most recent query. If state is active this field shows the currently executing query. In all other states, it shows the last query that was executed. @@ -674,26 +656,19 @@ postgres: user database host checkpoints_timed bigint - Number of scheduled checkpoints that have been performed. - This value can also be returned by directly calling - the pg_stat_get_bgwriter_timed_checkpoints function. + Number of scheduled checkpoints that have been performed checkpoints_req bigint - Number of requested checkpoints that have been performed. - This value can also be returned by directly calling - the pg_stat_get_bgwriter_requested_checkpoints function. + Number of requested checkpoints that have been performed checkpoint_write_time bigint Total amount of time that has been spent in the portion of - checkpoint processing where files are written to disk, in milliseconds. - This value can also be returned by directly calling the - pg_stat_get_checkpoint_write_time - function. + checkpoint processing where files are written to disk, in milliseconds @@ -702,39 +677,29 @@ postgres: user database host Total amount of time that has been spent in the portion of checkpoint processing where files are synchronized to disk, in - milliseconds. This value can also be returned by directly calling - the pg_stat_get_checkpoint_sync_time - function. + milliseconds buffers_checkpoint bigint - Number of buffers written during checkpoints. - This value can also be returned by directly calling - the pg_stat_get_bgwriter_buf_written_checkpoints function. + Number of buffers written during checkpoints buffers_clean bigint - Number of buffers written by the background writer. - This value can also be returned by directly calling - the pg_stat_get_bgwriter_buf_written_clean function. + Number of buffers written by the background writer maxwritten_clean bigint Number of times the background writer stopped a cleaning - scan because it had written too many buffers. - This value can also be returned by directly calling - the pg_stat_get_bgwriter_maxwritten_clean function. + scan because it had written too many buffers buffers_backend bigint - Number of buffers written directly by a backend. - This value can also be returned by directly calling - the pg_stat_get_buf_written_backend function. + Number of buffers written directly by a backend buffers_backend_fsync @@ -746,16 +711,12 @@ postgres: user database host buffers_alloc bigint - Number of buffers allocated. - This value can also be returned by directly calling - the pg_stat_get_buf_alloc function. + Number of buffers allocated stats_reset timestamp with time zone - Time at which these statistics were last reset. - This value can also be returned by directly calling - the pg_stat_get_bgwriter_stat_reset_time function. + Time at which these statistics were last reset @@ -794,75 +755,57 @@ postgres: user database host Number of backends currently connected to this database. This is the only column in this view that returns a value reflecting current state; all other columns return the accumulated values since - the last reset. This value can also be returned by directly calling - the pg_stat_get_db_numbackends function. + the last reset. xact_commit bigint Number of transactions in this database that have been - committed. This value can also be returned by directly calling - the pg_stat_get_db_xact_commit function. + committed xact_rollback bigint Number of transactions in this database that have been - rolled back. This value can also be returned by directly calling - the pg_stat_get_db_xact_rollback function. + rolled back blks_read bigint - Number of disk blocks read in this database. - This value can also be returned by directly calling - the pg_stat_get_db_blocks_fetched and - pg_stat_get_db_blocks_hit functions and - subtracting the results. + Number of disk blocks read in this database blks_hit bigint Number of times disk blocks were found already in the buffer cache, so that a read was not necessary (this only includes hits in the - PostgreSQL buffer cache, not the operating system's filesystem cache). - This value can also be returned by directly calling - the pg_stat_get_db_blocks_hit function. + PostgreSQL buffer cache, not the operating system's filesystem cache) + tup_returned bigint - Number of rows returned by queries in this database. - This value can also be returned by directly calling - the pg_stat_get_db_tuples_returned function. + Number of rows returned by queries in this database tup_fetched bigint - Number of rows fetched by queries in this database. - This value can also be returned by directly calling - the pg_stat_get_db_tuples_fetched function. + Number of rows fetched by queries in this database tup_inserted bigint - Number of rows inserted by queries in this database. - This value can also be returned by directly calling - the pg_stat_get_db_tuples_inserted function. + Number of rows inserted by queries in this database tup_updated bigint - Number of rows updated by queries in this database. - This value can also be returned by directly calling - the pg_stat_get_db_tuples_updated function. + Number of rows updated by queries in this database tup_deleted bigint - Number of rows deleted by queries in this database. - This value can also be returned by directly calling - the pg_stat_get_db_tuples_deleted function. + Number of rows deleted by queries in this database conflicts @@ -870,8 +813,6 @@ postgres: user database host Number of queries canceled due to conflicts with recovery in this database. (Conflicts occur only on standby servers; see for details.) - This value can also be returned by directly calling - the pg_stat_get_db_conflict_all function. @@ -879,10 +820,8 @@ postgres: user database host bigint Number of temporary files created by queries in this database. All temporary files are counted, regardless of why the temporary file - was created (e.g., sorting or hash), and regardless of the + was created (e.g., sorting or hashing), and regardless of the setting. - This value can also be returned by directly calling - the pg_stat_get_db_temp_files function. @@ -892,39 +831,29 @@ postgres: user database host setting. - This value can also be returned by directly calling - the pg_stat_get_db_temp_bytes function. deadlocks bigint - Number of deadlocks detected in this database. - This value can also be returned by directly calling - the pg_stat_get_db_deadlocks function. + Number of deadlocks detected in this database block_read_time bigint Time spent reading data file blocks by backends in this database, - in milliseconds. - The same value can be returned in microseconds by directly calling - the pg_stat_get_db_block_time_read function. + in milliseconds block_write_time bigint Time spent writing data file blocks by backends in this database, - in milliseconds. - The same value can be returned in microseconds by directly calling - the pg_stat_get_db_block_time_write function. + in milliseconds stats_reset timestamp with time zone - Time at which these statistics were last reset. - This value can also be returned by directly calling - the pg_stat_get_reset_time function. + Time at which these statistics were last reset @@ -965,16 +894,12 @@ postgres: user database host seq_scan bigint - Number of sequential scans initiated on this table. - This value can also be returned by directly calling - the pg_stat_get_numscans function. + Number of sequential scans initiated on this table seq_tup_read bigint - Number of live rows fetched by sequential scans. - This value can also be returned by directly calling - the pg_stat_get_tuples_returned function. + Number of live rows fetched by sequential scans idx_scan @@ -989,76 +914,56 @@ postgres: user database host n_tup_ins bigint - Number of rows inserted. - This value can also be returned by directly calling - the pg_stat_get_tuples_inserted function. + Number of rows inserted n_tup_upd bigint - Number of rows updated. - This value can also be returned by directly calling - the pg_stat_get_tuples_updated function. + Number of rows updated n_tup_del bigint - Number of rows deleted. - This value can also be returned by directly calling - the pg_stat_get_tuples_deleted function. + Number of rows deleted n_tup_hot_upd bigint Number of rows HOT updated (i.e., with no separate index - update required). - This value can also be returned by directly calling - the pg_stat_get_tuples_hot_updated function. + update required) n_live_tup bigint - Estimated number of live rows. - This value can also be returned by directly calling - the pg_stat_get_live_tuples function. + Estimated number of live rows n_dead_tup bigint - Estimated number of dead rows. - This value can also be returned by directly calling - the pg_stat_get_dead_tuples function. + Estimated number of dead rows last_vacuum timestamp with time zone Last time at which this table was manually vacuumed - (not counting VACUUM FULL). - This value can also be returned by directly calling - the pg_stat_get_last_vacuum_time function. + (not counting VACUUM FULL) last_autovacuum timestamp with time zone Last time at which this table was vacuumed by the autovacuum - daemon. - This value can also be returned by directly calling - the pg_stat_get_last_autovacuum_time function. + daemon last_analyze timestamp with time zone - Last time at which this table was manually analyzed. - This value can also be returned by directly calling - the pg_stat_get_last_analyze_time function. + Last time at which this table was manually analyzed last_autoanalyze timestamp with time zone Last time at which this table was analyzed by the autovacuum - daemon. - This value can also be returned by directly calling - the pg_stat_get_last_autoanalyze_time function. + daemon vacuum_count @@ -1070,24 +975,18 @@ postgres: user database host autovacuum_count bigint Number of times this table has been vacuumed by the autovacuum - daemon. - This value can also be returned by directly calling - the pg_stat_get_autovacuum_count function. + daemon analyze_count bigint - Number of times this table has been manually analyzed. - This value can also be returned by directly calling - the pg_stat_get_analyze_count function. + Number of times this table has been manually analyzed autoanalyze_count bigint Number of times this table has been analyzed by the autovacuum - daemon. - This value can also be returned by directly calling - the pg_stat_get_autoanalyze_count function. + daemon @@ -1143,24 +1042,18 @@ postgres: user database host idx_scan bigint - Number of index scans initiated on this index. - This value can also be returned by directly calling - the pg_stat_get_numscans function. + Number of index scans initiated on this index idx_tup_read bigint - Number of index entries returned by scans on this index. - This value can also be returned by directly calling - the pg_stat_get_tuples_returned function. + Number of index entries returned by scans on this index idx_tup_fetch bigint Number of live table rows fetched by simple index scans using this - index. - This value can also be returned by directly calling - the pg_stat_get_tuples_fetched function. + index @@ -1232,18 +1125,12 @@ postgres: user database host heap_blks_read bigint - Number of disk blocks read from this table. - This value can also be returned by directly calling - the pg_stat_get_blocks_fetched and - pg_stat_get_blocks_hit functions and - subtracting the results. + Number of disk blocks read from this table heap_blks_hit bigint - Number of buffer hits in this table. - This value can also be returned by directly calling - the pg_stat_get_blocks_hit function. + Number of buffer hits in this table idx_blks_read @@ -1253,7 +1140,7 @@ postgres: user database host idx_blks_hit bigint - Number of buffer hits in all indexes of this table + Number of buffer hits in all indexes on this table toast_blks_read @@ -1329,18 +1216,12 @@ postgres: user database host idx_blks_read bigint - Number of disk blocks read from this index. - This value can also be returned by directly calling - the pg_stat_get_blocks_fetched and - pg_stat_get_blocks_hit functions and - subtracting the results. + Number of disk blocks read from this index idx_blks_hit bigint - Number of buffer hits in this index. - This value can also be returned by directly calling - the pg_stat_get_blocks_hit function. + Number of buffer hits in this index @@ -1433,25 +1314,19 @@ postgres: user database host calls bigint - Number of times this function has been called. - This value can also be returned by directly calling - the pg_stat_get_function_calls function. + Number of times this function has been called total_time bigint Total time spent in this function and all other functions - called by it, in milliseconds. - The same value can be returned in microseconds by directly calling - the pg_stat_get_function_time function. + called by it, in milliseconds self_time bigint Total time spent in this function itself, not including - other functions called by it, in milliseconds. - The same value can be returned in microseconds by directly calling - the pg_stat_get_function_self_time function. + other functions called by it, in milliseconds @@ -1501,7 +1376,7 @@ postgres: user database host client_addr inet IP address of the client connected to this WAL sender. - If this field is not set, it indicates that the client is + If this field is null, it indicates that the client is connected via a Unix socket on the server machine. @@ -1510,7 +1385,7 @@ postgres: user database host text Host name of the connected client, as reported by a reverse DNS lookup of client_addr. This field will - only be set for IP connections, and only when is enabled. @@ -1518,7 +1393,7 @@ postgres: user database host client_port integer TCP port number that the client is using for communication - with this WAL sender, or NULL if a Unix socket is used + with this WAL sender, or -1 if a Unix socket is used @@ -1604,37 +1479,31 @@ postgres: user database host confl_tablespace bigint Number of queries in this database that have been canceled due to - dropped tablespaces. This value can also be returned by directly calling - the pg_stat_get_db_conflict_tablespace function. + dropped tablespaces confl_lock bigint Number of queries in this database that have been canceled due to - lock timeouts. This value can also be returned by directly calling - the pg_stat_get_db_conflict_lock function. + lock timeouts confl_snapshot bigint Number of queries in this database that have been canceled due to - old snapshots. This value can also be returned by directly calling - the pg_stat_get_db_conflict_snapshot function. + old snapshots confl_bufferpin bigint Number of queries in this database that have been canceled due to - pinned buffers. This value can also be returned by directly calling - the pg_stat_get_db_conflict_bufferpin function. + pinned buffers confl_deadlock bigint Number of queries in this database that have been canceled due to - deadlocks. This value can also be returned by directly calling - the pg_stat_get_db_conflict_startup_deadlock - function. + deadlocks @@ -1655,16 +1524,18 @@ postgres: user database host Other ways of looking at the statistics can be set up by writing - queries that use the same underlying statistics access functions as - the standard views do. The per-database access - functions take a database OID as an argument to identify which - database to report on. The per-table and per-index functions take - a table or index OID. The functions for function-call statistics - take a function OID. (Note that only tables, indexes, and functions - in the current database can be seen with these functions.) The - per-server-process access functions take a server process - number, which ranges from one to the number of currently active - server processes. + queries that use the same underlying statistics access functions used by + the standard views shown above. For details such as the functions' names, + consult the definitions of the standard views. (For example, in + psql you could issue \d+ pg_stat_activity.) + The access functions for per-database statistics take a database OID as an + argument to identify which database to report on. + The per-table and per-index functions take a table or index OID. + The functions for per-function statistics take a function OID. + Note that only tables, indexes, and functions in the current database + can be seen with these functions. + It should also be noted that while the views present timing values in + milliseconds, the underlying functions report timings in microseconds. @@ -1673,7 +1544,7 @@ postgres: user database host - Other Statistics Functions + Additional Statistics Functions @@ -1706,24 +1577,6 @@ postgres: user database host - - pg_stat_get_backend_idset() - setof integer - - Set of currently active server process numbers (from 1 to the - number of active server processes). See usage example in the text. - - - - - pg_stat_get_wal_senders() - setof record - - One record for each active WAL sender. The fields returned are a subset - of those in the pg_stat_replication view. - - - pg_stat_clear_snapshot() void @@ -1774,12 +1627,19 @@ postgres: user database host - All the underlying functions of the pg_stat_activity view - require a backend ID number. + pg_stat_get_activity, the underlying function of + the pg_stat_activity view, returns a set of records + containing all the available information about each backend process. + Sometimes it may be more convenient to obtain just a subset of this + information. In such cases, an older set of per-backend statistics + access functions can be used; these are shown in . + These access functions use a backend ID number, which ranges from one + to the number of currently active backends. The function pg_stat_get_backend_idset provides a - convenient way to generate one row for each active server process for + convenient way to generate one row for each active backend for invoking these functions. For example, to show the PIDs and - current queries of all server processes: + current queries of all backends: SELECT pg_stat_get_backend_pid(s.backendid) AS pid, @@ -1788,6 +1648,93 @@ SELECT pg_stat_get_backend_pid(s.backendid) AS pid, +
+ Per-Backend Statistics Functions + + + + + Function + Return Type + Description + + + + + + + pg_stat_get_backend_idset() + setof integer + Set of currently active backend ID numbers (from 1 to the + number of active backends) + + + + pg_stat_get_backend_activity(integer) + text + Text of this backend's most recent query + + + + pg_stat_get_backend_activity_start(integer) + timestamp with time zone + Time when the most recent query was started + + + + pg_stat_get_backend_client_addr(integer) + inet + IP address of the client connected to this backend + + + + pg_stat_get_backend_client_port(integer) + integer + TCP port number that the client is using for communication + + + + pg_stat_get_backend_dbid(integer) + oid + OID of the database this backend is connected to + + + + pg_stat_get_backend_pid(integer) + integer + Process ID of this backend + + + + pg_stat_get_backend_start(integer) + timestamp with time zone + Time when this process was started + + + + + + pg_stat_get_backend_userid(integer) + oid + OID of the user logged into this backend + + + + pg_stat_get_backend_waiting(integer) + boolean + True if this backend is currently waiting on a lock + + + + pg_stat_get_backend_xact_start(integer) + timestamp with time zone + Time when the current transaction was started + + + + +
+ -- 2.40.0