From a18331004a15c4e37fe88312bd882b49edb8228f Mon Sep 17 00:00:00 2001 From: Bruce Momjian Date: Thu, 20 Mar 2003 03:34:57 +0000 Subject: Add start time to pg_stat_activity Neil Conway --- doc/src/sgml/func.sgml | 10 +++++-- doc/src/sgml/monitoring.sgml | 70 +++++++++++++++++++++++++++++++------------- doc/src/sgml/runtime.sgml | 29 +++++++++++++----- 3 files changed, 78 insertions(+), 31 deletions(-) (limited to 'doc/src') diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml index 91cc2c70af4..256b0fa3fae 100644 --- a/doc/src/sgml/func.sgml +++ b/doc/src/sgml/func.sgml @@ -1,5 +1,5 @@ @@ -5315,7 +5315,7 @@ SELECT LOCALTIMESTAMP; There is also the function timeofday(), which for historical - reasons returns a text string rather than a timestamp value: + reasons returns a text string rather than a timestamp value: SELECT timeofday(); Result: Sat Feb 17 19:07:32.000126 2001 EST @@ -5326,7 +5326,11 @@ SELECT timeofday(); It is important to know that CURRENT_TIMESTAMP and related functions return the start time of the current transaction; their values do not - change during the transaction. timeofday() + change during the transaction. This is considered a feature: + the intent is to allow a single transaction to have a consistent + notion of the current time, so that multiple + modifications within the same transaction bear the same + timestamp. timeofday() returns the wall-clock time and does advance during transactions. diff --git a/doc/src/sgml/monitoring.sgml b/doc/src/sgml/monitoring.sgml index 78c1bdbec3f..002134c9acd 100644 --- a/doc/src/sgml/monitoring.sgml +++ b/doc/src/sgml/monitoring.sgml @@ -1,5 +1,5 @@ @@ -146,12 +146,13 @@ postgres: user database host Since the variables STATS_COMMAND_STRING, - STATS_BLOCK_LEVEL, - and STATS_ROW_LEVEL - default to false, no statistics are actually collected - in the default configuration. You must turn one or more of them on - before you will get useful results from the statistical display - functions. + STATS_BLOCK_LEVEL, and + STATS_ROW_LEVEL default to false, + very few statistics are collected in the default + configuration. Enabling one or more of these configuration + variables will significantly enhance the amount of useful data + produced by the statistics collector, at the expense of + additional run-time overhead. @@ -205,11 +206,15 @@ postgres: user database host pg_stat_activity - One row per server process, showing process ID, database, - user, and current query. The current query column is only available - to superusers; for others it reads as null. (Note that because of - the collector's reporting delay, current query will only be up-to-date - for long-running queries.) + One row per server process, showing process + ID, database, user, current query, and the time at + which the current query began execution. The columns that report + data on the current query are only available if the + STATS_COMMAND_STRING configuration option has + been enabled. Furthermore, these columns can only be accessed by + superusers; to other users, they always appear NULL. (Note that + because of the collector's reporting delay, current query will + only be up-to-date for long-running queries.) @@ -333,10 +338,20 @@ postgres: user database host - The pg_statio_ views are primarily useful to determine - the effectiveness of the buffer cache. When the number of actual disk - reads is much smaller than the number of buffer hits, then the cache - is satisfying most read requests without invoking a kernel call. + The pg_statio_ views are primarily useful to + determine the effectiveness of the buffer cache. When the number + of actual disk reads is much smaller than the number of buffer + hits, then the cache is satisfying most read requests without + invoking a kernel call. However, these statistics do not give the + entire story: due to the way in which PostgreSQL + handles disk I/O, data that is not in the + PostgreSQL buffer cache may still reside in the + kernel's I/O cache, and may therefore still be fetched without + requiring a physical read. Users interested in obtaining more + detailed information on PostgreSQL I/O behavior are + advised to use the PostgreSQL statistics collector + in combination with operating system utilities that allow insight + into the kernel's handling of I/O. @@ -401,7 +416,7 @@ postgres: user database host pg_stat_get_db_blocks_hit(oid) bigint - Number of disk block requests found in cache for database + Number of disk block fetch requests found in cache for database @@ -478,7 +493,7 @@ postgres: user database host set of integer Set of currently active backend IDs (from 1 to N where N is the - number of active backends). See usage example below. + number of active backends). See usage example below @@ -518,15 +533,27 @@ postgres: user database host pg_stat_get_backend_activity(integer) text - Current query of backend process (NULL if caller is not superuser) + Current query of backend process (NULL if caller is not + superuser, or STATS_COMMAND_STRING is not enabled) + + pg_stat_get_backend_activity_start(integer) + text + + The time at which the specified backend's currently executing query was + initiated (NULL if caller is not superuser, or + STATS_COMMAND_STRING is not enabled) + + + + pg_stat_reset() boolean - Reset all currently collected statistics. + Reset all currently collected statistics @@ -535,7 +562,8 @@ postgres: user database host - Blocks_fetched minus blocks_hit gives the number of kernel + blocks_fetched minus + blocks_hit gives the number of kernel read() calls issued for the table, index, or database; but the actual number of physical reads is usually lower due to kernel-level buffering. diff --git a/doc/src/sgml/runtime.sgml b/doc/src/sgml/runtime.sgml index 86d5ddc2852..408c81e2ee5 100644 --- a/doc/src/sgml/runtime.sgml +++ b/doc/src/sgml/runtime.sgml @@ -1,5 +1,5 @@ @@ -1182,16 +1182,31 @@ env PGOPTIONS='-c geqo=off' psql STATS_COMMAND_STRING (boolean) + + + Enables the collection of statistics on the currently + executing command of each backend, along with the time at + which that query began execution. This option is off by + default. Note that even when enabled, this information is only + visible to the superuser, so it should not represent a + security risk. This data can be accessed via the + pg_stat_activity system view; refer + to the &cite-admin; for more information. + + + + + STATS_BLOCK_LEVEL (boolean) STATS_ROW_LEVEL (boolean) - Determines what information backends send to the statistics - collector process: current commands, block-level activity - statistics, or row-level activity statistics. All default to - off. Enabling statistics collection costs a small amount of - time per query, but is invaluable for debugging and - performance tuning. + Enables the collection of block-level and row-level statistics + on database activity, respectively. These options are off by + default. This data can be accessed via the + pg_stat and + pg_statio family of system views; + refer to the &cite-admin; for more information. -- cgit v1.2.3