<!-- $PostgreSQL: pgsql/doc/src/sgml/config.sgml,v 1.65 2006/06/18 15:38:35 petere Exp $ -->

<!-- $PostgreSQL: pgsql/doc/src/sgml/config.sgml,v 1.66 2006/06/19 01:51:21 tgl Exp $ -->

<chapter Id="runtime-config">

<title>Server Configuration</title>

<sect1 id="runtime-config-statistics">

<title>Run-Time Statistics</title>

<sect2 id="runtime-config-statistics-monitor">

<title>Statistics Monitoring</title>

<variablelist>

<varlistentry>

<term><varname>log_statement_stats</varname> (<type>boolean</type>)</term>

<term><varname>log_parser_stats</varname> (<type>boolean</type>)</term>

<term><varname>log_planner_stats</varname> (<type>boolean</type>)</term>

<term><varname>log_executor_stats</varname> (<type>boolean</type>)</term>

<indexterm>

<primary><varname>log_statement_stats</> configuration parameter</primary>

</indexterm>

<indexterm>

<primary><varname>log_parser_stats</> configuration parameter</primary>

</indexterm>

<indexterm>

<primary><varname>log_planner_stats</> configuration parameter</primary>

</indexterm>

<indexterm>

<primary><varname>log_executor_stats</> configuration parameter</primary>

</indexterm>

<listitem>

<para>

For each query, write performance statistics of the respective

module to the server log. This is a crude profiling

instrument. <varname>log_statement_stats</varname> reports total

statement statistics, while the others report per-module statistics.

<varname>log_statement_stats</varname> 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.

</para>

</listitem>

</varlistentry>

</variablelist>

</sect2>

<sect2 id="runtime-config-statistics-collector">

<title>Query and Index Statistics Collector</title>

Refer to <xref linkend="monitoring"> for more information.

</para>

<note>

<para>

As of <productname>PostgreSQL</productname> 8.2,

<varname>stats_command_string</varname> 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.

</para>

</note>

<variablelist>

<varlistentry id="guc-stats-command-string" xreflabel="stats_command_string">

<term><varname>stats_command_string</varname> (<type>boolean</type>)</term>

<indexterm>

<primary><varname>stats_command_string</> configuration parameter</primary>

</indexterm>

<listitem>

<para>

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.

</para>

</listitem>

</varlistentry>

<varlistentry id="guc-stats-start-collector" xreflabel="stats_start_collector">

<term><varname>stats_start_collector</varname> (<type>boolean</type>)</term>

<indexterm>

</listitem>

</varlistentry>

<varlistentry id="guc-stats-command-string" xreflabel="stats_command_string">

<term><varname>stats_command_string</varname> (<type>boolean</type>)</term>

<indexterm>

<primary><varname>stats_command_string</> configuration parameter</primary>

</indexterm>

<listitem>

<para>

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.

</para>

</listitem>

</varlistentry>

<varlistentry id="guc-stats-block-level" xreflabel="stats_block_level">

<term><varname>stats_block_level</varname> (<type>boolean</type>)</term>

<indexterm>

</indexterm>

<listitem>

<para>

If on, collected statistics are zeroed out whenever the server

is restarted. If off, statistics are accumulated across server

restarts. The default is <literal>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.

</para>

</listitem>

</varlistentry>

</variablelist>

</sect2>

<sect2 id="runtime-config-statistics-monitor">

<title>Statistics Monitoring</title>

<variablelist>

<varlistentry>

<term><varname>log_statement_stats</varname> (<type>boolean</type>)</term>

<term><varname>log_parser_stats</varname> (<type>boolean</type>)</term>

<term><varname>log_planner_stats</varname> (<type>boolean</type>)</term>

<term><varname>log_executor_stats</varname> (<type>boolean</type>)</term>

<indexterm>

<primary><varname>log_statement_stats</> configuration parameter</primary>

</indexterm>

<indexterm>

<primary><varname>log_parser_stats</> configuration parameter</primary>

</indexterm>

<indexterm>

<primary><varname>log_planner_stats</> configuration parameter</primary>

</indexterm>

<indexterm>

<primary><varname>log_executor_stats</> configuration parameter</primary>

</indexterm>

<listitem>

<para>

For each query, write performance statistics of the respective

module to the server log. This is a crude profiling

instrument. <varname>log_statement_stats</varname> reports total

statement statistics, while the others report per-module statistics.

<varname>log_statement_stats</varname> 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.

</para>

</listitem>

</varlistentry>

</variablelist>

</sect2>

</sect1>

<!-- $PostgreSQL: pgsql/doc/src/sgml/monitoring.sgml,v 1.33 2006/06/18 15:38:35 petere Exp $ -->

<!-- $PostgreSQL: pgsql/doc/src/sgml/monitoring.sgml,v 1.34 2006/06/19 01:51:21 tgl Exp $ -->

<chapter id="monitoring">

<title>Monitoring Database Activity</title>

<productname>PostgreSQL</productname>'s <firstterm>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.

</para>

<para>

<productname>PostgreSQL</productname> 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.

</para>

<sect2 id="monitoring-stats-setup">

</para>

<para>

The parameters <xref linkend="guc-stats-command-string">,

<xref linkend="guc-stats-block-level">, and <xref

The parameters <xref linkend="guc-stats-block-level"> and <xref

linkend="guc-stats-row-level"> 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

processsends its current command string, disk-block-level access

statistics, and row-level access statistics to the collector.

processtracks 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.

</para>

<para>

The parameter <xref linkend="guc-stats-command-string"> enables monitoring

of the current command being executed by any server process.

The statistics collector subprocess need not be running to enable this

feature.

</para>

<para>

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 statisticscollector, at the expense of

produced by the statisticsfacilities, at the expense of

additional run-time overhead.

</para>

</note>

progress does not affect the displayed totals. Also, the collector itself

emits a new report at most once per <varname>PGSTAT_STAT_INTERVAL</varname>

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 <varname>PGSTAT_STAT_INTERVAL</varname> delay before it becomes

visible.

displayed information lags behind actual activity. However, current-query

information collected by <varname>stats_command_string</varname> is

always up-to-date.

</para>

<para>

the collector process and then continues to use this snapshot for all

statistical views and functions until the end of its current transaction.

So the statistics will appear not to change as long as you continue the

current transaction.

current transaction. Similarly, information about the current queries of

all processes is collected when any such information is first requested

within a transaction, and the same information will be displayed throughout

the transaction.

This is a feature, not a bug, because it allows you to perform several

queries on the statistics and correlate the results without worrying that

the numbers are changing underneath you. But if you want to see new

parameter <varname>stats_command_string</varname> 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.)</entry>

owning the process being reported on.

</entry>

</row>

<row>

<entry><literal><function>pg_stat_get_last_vacuum_time</function>(<type>oid</type>)</literal></entry>

<entry><type>timestamptz</type></entry>

<entry>

Time of the last vacuum initiated by the user

Time of the last vacuum initiated by the user on this table

</entry>

</row>

<row>

<entry><literal><function>pg_stat_get_last_autovacuum_time</function>(<type>oid</type>)</literal></entry>

<entry><type>timestamptz</type></entry>

<entry>

Time of the last vacuum initiated by the autovacuum daemon

Time of the last vacuum initiated by the autovacuum daemon on this table

</entry>

</row>

<row>

<entry><literal><function>pg_stat_get_last_analyze_time</function>(<type>oid</type>)</literal></entry>

<entry><type>timestamptz</type></entry>

<entry>

Time of the last analyze initiated by the user

Time of the last analyze initiated by the user on this table

</entry>

</row>

<row>

<entry><literal><function>pg_stat_get_last_autoanalyze_time</function>(<type>oid</type>)</literal></entry>

<entry><type>timestamptz</type></entry>

<entry>

Time of the last analyze initiated by the autovacuum daemon

Time of the last analyze initiated by the autovacuum daemon on this

table

</entry>

</row>

<entry><literal><function>pg_stat_reset</function>()</literal></entry>

<entry><type>boolean</type></entry>

<entry>

Reset allcurrently collectedstatistics

Reset allblock-level and row-levelstatistics to zero

</entry>

</row>

</tbody>