From b13c9686d08411a284afeb881b49d7955761d8cb Mon Sep 17 00:00:00 2001 From: Tom Lane Date: Mon, 19 Jun 2006 01:51:22 +0000 Subject: Take the statistics collector out of the loop for monitoring backends' current commands; instead, store current-status information in shared memory. This substantially reduces the overhead of stats_command_string and also ensures that pg_stat_activity is fully up to date at all times. Per my recent proposal. --- doc/src/sgml/config.sgml | 134 +++++++++++++++++++++++-------------------- doc/src/sgml/monitoring.sgml | 59 +++++++++++-------- 2 files changed, 109 insertions(+), 84 deletions(-) (limited to 'doc/src') diff --git a/doc/src/sgml/config.sgml b/doc/src/sgml/config.sgml index ebfd6012fc7..8f6ffa9a1ab 100644 --- a/doc/src/sgml/config.sgml +++ b/doc/src/sgml/config.sgml @@ -1,4 +1,4 @@ - + Server Configuration @@ -2845,43 +2845,6 @@ SELECT * FROM parent WHERE key = 2400; Run-Time Statistics - - Statistics Monitoring - - - - log_statement_stats (boolean) - log_parser_stats (boolean) - log_planner_stats (boolean) - log_executor_stats (boolean) - - log_statement_stats configuration parameter - - - log_parser_stats configuration parameter - - - log_planner_stats configuration parameter - - - log_executor_stats configuration parameter - - - - For each query, write performance statistics of the respective - module to the server log. This is a crude profiling - instrument. log_statement_stats reports total - statement statistics, while the others report per-module statistics. - log_statement_stats cannot be enabled together with - any of the per-module options. All of these options are disabled by - default. Only superusers can change these settings. - - - - - - - Query and Index Statistics Collector @@ -2893,8 +2856,38 @@ SELECT * FROM parent WHERE key = 2400; Refer to for more information. + + + As of PostgreSQL 8.2, + stats_command_string controls a separate data + collection mechanism that can be turned on or off independently + of whether the statistics-collection subprocess is running. + The subprocess is only needed to support collection of + block-level or row-level statistics. + + + + + stats_command_string (boolean) + + stats_command_string configuration parameter + + + + Enables the collection of information on the currently + executing command of each session, along with the time at + which that command began execution. This parameter is off by + default. Note that even when enabled, this information is not + visible to all users, only to superusers and the user owning + the session being reported on; so it should not represent a + security risk. + Only superusers can change this setting. + + + + stats_start_collector (boolean) @@ -2914,25 +2907,6 @@ SELECT * FROM parent WHERE key = 2400; - - stats_command_string (boolean) - - stats_command_string configuration parameter - - - - Enables the collection of statistics on the currently - executing command of each session, along with the time at - which that command began execution. This parameter is off by - default. Note that even when enabled, this information is not - visible to all users, only to superusers and the user owning - the session being reported on; so it should not represent a - security risk. - Only superusers can change this setting. - - - - stats_block_level (boolean) @@ -2968,15 +2942,53 @@ SELECT * FROM parent WHERE key = 2400; - If on, collected statistics are zeroed out whenever the server - is restarted. If off, statistics are accumulated across server - restarts. The default is off. This parameter can only - be set at server start. + If on, collected block-level and row-level statistics are zeroed out + whenever the server is restarted. If off, statistics are accumulated + across server restarts. This parameter is off by default. + This parameter can only be set at server start. + + + + + + + + + Statistics Monitoring + + + + log_statement_stats (boolean) + log_parser_stats (boolean) + log_planner_stats (boolean) + log_executor_stats (boolean) + + log_statement_stats configuration parameter + + + log_parser_stats configuration parameter + + + log_planner_stats configuration parameter + + + log_executor_stats configuration parameter + + + + For each query, write performance statistics of the respective + module to the server log. This is a crude profiling + instrument. log_statement_stats reports total + statement statistics, while the others report per-module statistics. + log_statement_stats cannot be enabled together with + any of the per-module options. All of these options are disabled by + default. Only superusers can change these settings. + diff --git a/doc/src/sgml/monitoring.sgml b/doc/src/sgml/monitoring.sgml index 2eac524f80f..54c6b4f973a 100644 --- a/doc/src/sgml/monitoring.sgml +++ b/doc/src/sgml/monitoring.sgml @@ -1,4 +1,4 @@ - + Monitoring Database Activity @@ -109,9 +109,14 @@ postgres: user database host PostgreSQL's statistics collector is a subsystem that supports collection and reporting of information about server activity. Presently, the collector can count accesses to tables - and indexes in both disk-block and individual-row terms. It also supports - determining the exact command currently being executed by other server - processes. + and indexes in both disk-block and individual-row terms. + + + + PostgreSQL also supports determining the exact + command currently being executed by other server processes. This is an + independent facility that can be enabled or disabled whether or not + block-level and row-level statistics are being collected. @@ -136,15 +141,21 @@ postgres: user database host - The parameters , - , and and control how much information is actually sent to the collector and thus determine how much run-time overhead occurs. These respectively determine whether a server - process sends its current command string, disk-block-level access - statistics, and row-level access statistics to the collector. + process tracks disk-block-level access + statistics and row-level access statistics and sends these to the collector. Additionally, per-database transaction commit and abort statistics - are collected if any of these parameters are set. + are collected if either of these parameters are set. + + + + The parameter enables monitoring + of the current command being executed by any server process. + The statistics collector subprocess need not be running to enable this + feature. @@ -165,7 +176,7 @@ postgres: user database host @@ -190,10 +201,9 @@ postgres: user database host PGSTAT_STAT_INTERVAL milliseconds (500 unless altered while building the server). So the - displayed information lags behind actual activity. Current-query - information is reported to the collector immediately, but is still subject - to the PGSTAT_STAT_INTERVAL delay before it becomes - visible. + displayed information lags behind actual activity. However, current-query + information collected by stats_command_string is + always up-to-date. @@ -202,7 +212,10 @@ postgres: user database host user database host stats_command_string has been turned on. Furthermore, these columns read as null unless the user examining the view is a superuser or the same as the user - owning the process being reported on. (Note that because of the - collector's reporting delay, the current query will only be - up-to-date for long-running queries.) + owning the process being reported on. + @@ -549,7 +561,7 @@ postgres: user database host pg_stat_get_last_vacuum_time(oid) timestamptz - Time of the last vacuum initiated by the user + Time of the last vacuum initiated by the user on this table @@ -557,7 +569,7 @@ postgres: user database host pg_stat_get_last_autovacuum_time(oid) timestamptz - Time of the last vacuum initiated by the autovacuum daemon + Time of the last vacuum initiated by the autovacuum daemon on this table @@ -565,7 +577,7 @@ postgres: user database host pg_stat_get_last_analyze_time(oid) timestamptz - Time of the last analyze initiated by the user + Time of the last analyze initiated by the user on this table @@ -573,7 +585,8 @@ postgres: user database host pg_stat_get_last_autoanalyze_time(oid) timestamptz - Time of the last analyze initiated by the autovacuum daemon + Time of the last analyze initiated by the autovacuum daemon on this + table @@ -677,7 +690,7 @@ postgres: user database host pg_stat_reset() boolean - Reset all currently collected statistics + Reset all block-level and row-level statistics to zero -- cgit v1.2.3