Following discussion on -hackers, the attached patch implements
- remove stats_row_level and stats_block_level stats, replaced with
single stats_collection = on | off parameter, default on
- remove stats_collector_start and make it always start
Includes doc and test changes.
Performance test results please; if they're neutral we should be good to
apply this.
--
Simon Riggs
EnterpriseDB http://www.enterprisedb.com
Index: doc/src/sgml/config.sgml
===================================================================
RCS file: /projects/cvsroot/pgsql/doc/src/sgml/config.sgml,v
retrieving revision 1.133
diff -c -r1.133 config.sgml
*** doc/src/sgml/config.sgml 24 Jul 2007 04:54:08 -0000 1.133
--- doc/src/sgml/config.sgml 27 Jul 2007 13:47:25 -0000
***************
*** 3021,3028 ****
<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>
--- 3021,3028 ----
<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 supports collection of
! block-level and row-level statistics.
</para>
</note>
***************
*** 3062,3111 ****
</listitem>
</varlistentry>
! <varlistentry id="guc-stats-start-collector" xreflabel="stats_start_collector">
! <term><varname>stats_start_collector</varname> (<type>boolean</type>)</term>
<indexterm>
! <primary><varname>stats_start_collector</> configuration parameter</primary>
</indexterm>
<listitem>
<para>
! Controls whether the server should start the
! statistics-collection subprocess. This is on by default, but
! can be turned off if you know you have no interest in
! collecting statistics or running autovacuum.
! This parameter can only be set at server start, because the collection
! subprocess cannot be started or stopped on-the-fly. (However, the
! extent to which statistics are actually gathered can be changed while
! the server is running, so long as the subprocess exists.)
! </para>
! </listitem>
! </varlistentry>
!
! <varlistentry id="guc-stats-block-level" xreflabel="stats_block_level">
! <term><varname>stats_block_level</varname> (<type>boolean</type>)</term>
! <indexterm>
! <primary><varname>stats_block_level</> configuration parameter</primary>
! </indexterm>
! <listitem>
! <para>
! Enables the collection of block-level statistics on database
! activity. This parameter is off by default.
! Only superusers can change this setting.
! </para>
! </listitem>
! </varlistentry>
!
! <varlistentry id="guc-stats-row-level" xreflabel="stats_row_level">
! <term><varname>stats_row_level</varname> (<type>boolean</type>)</term>
! <indexterm>
! <primary><varname>stats_row_level</> configuration parameter</primary>
! </indexterm>
! <listitem>
! <para>
! Enables the collection of row-level statistics on database
activity. This parameter is on by default, because the autovacuum
! daemon needs the collected information.
! Only superusers can change this setting.
</para>
</listitem>
</varlistentry>
--- 3062,3079 ----
</listitem>
</varlistentry>
! <varlistentry id="guc-stats-collection" xreflabel="stats_collection">
! <term><varname>stats_collection</varname> (<type>boolean</type>)</term>
<indexterm>
! <primary><varname>stats_collection</> configuration parameter</primary>
</indexterm>
<listitem>
<para>
! Enables the collection of row-level and block-level statistics on database
activity. This parameter is on by default, because the autovacuum
! daemon needs the collected information. The statistics-collection process
! is always active, allowing you to turn this setting on or off when
! required. Only superusers can change this setting.
</para>
</listitem>
</varlistentry>
Index: doc/src/sgml/maintenance.sgml
===================================================================
RCS file: /projects/cvsroot/pgsql/doc/src/sgml/maintenance.sgml,v
retrieving revision 1.77
diff -c -r1.77 maintenance.sgml
*** doc/src/sgml/maintenance.sgml 23 Jul 2007 17:22:00 -0000 1.77
--- doc/src/sgml/maintenance.sgml 27 Jul 2007 13:47:26 -0000
***************
*** 479,486 ****
tables that have had a large number of inserted, updated or deleted
tuples. These checks use the row-level statistics collection facility;
therefore, autovacuum cannot be used unless <xref
! linkend="guc-stats-start-collector"> and <xref
! linkend="guc-stats-row-level"> are set to <literal>true</literal>.
In the default configuration, autovacuuming is enabled and the related
configuration parameters are appropriately set.
</para>
--- 479,485 ----
tables that have had a large number of inserted, updated or deleted
tuples. These checks use the row-level statistics collection facility;
therefore, autovacuum cannot be used unless <xref
! linkend="guc-stats-collection"> is set to <literal>true</literal>.
In the default configuration, autovacuuming is enabled and the related
configuration parameters are appropriately set.
</para>
Index: doc/src/sgml/monitoring.sgml
===================================================================
RCS file: /projects/cvsroot/pgsql/doc/src/sgml/monitoring.sgml,v
retrieving revision 1.51
diff -c -r1.51 monitoring.sgml
*** doc/src/sgml/monitoring.sgml 28 Jun 2007 00:02:37 -0000 1.51
--- doc/src/sgml/monitoring.sgml 27 Jul 2007 13:47:26 -0000
***************
*** 139,162 ****
</para>
<para>
! The parameter <xref linkend="guc-stats-start-collector"> must be
! set to <literal>true</> for the statistics collector to be launched
! at all. This is the default and recommended setting, but it can be
! turned off if you have no interest in statistics and want to
! squeeze out every last drop of overhead. (The savings is likely to
! be small, however.) Note that this option cannot be changed while
! the server is running.
! </para>
!
! <para>
! 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
! 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 either of these parameters are set.
</para>
<para>
--- 139,155 ----
</para>
<para>
! The statistics collector process will always be launched at startup.
! The <xref linkend="guc-stats-collection"> parameter controls whether
! information is actually sent to the collector to allow inspection of
! disk-block access statistics and row-level access statistics.
Additionally, per-database transaction commit and abort statistics
! are collected if this parameter is set. The default and recommended
! setting for <varname>stats_collection</varname> is on, but it can be
! turned off if you have no interest in statistics and want to squeeze
! out every last drop of overhead. (The savings is likely to be small,
! however.) Note that this option can be changed while the server is
! running.
</para>
<para>
Index: src/backend/postmaster/autovacuum.c
===================================================================
RCS file: /projects/cvsroot/pgsql/src/backend/postmaster/autovacuum.c,v
retrieving revision 1.55
diff -c -r1.55 autovacuum.c
*** src/backend/postmaster/autovacuum.c 1 Jul 2007 18:30:54 -0000 1.55
--- src/backend/postmaster/autovacuum.c 27 Jul 2007 13:47:27 -0000
***************
*** 2599,2605 ****
AutoVacuumingActive(void)
{
if (!autovacuum_start_daemon || !pgstat_collect_startcollector ||
! !pgstat_collect_tuplelevel)
return false;
return true;
}
--- 2599,2605 ----
AutoVacuumingActive(void)
{
if (!autovacuum_start_daemon || !pgstat_collect_startcollector ||
! !pgstat_collect_stats)
return false;
return true;
}
***************
*** 2616,2626 ****
if (!autovacuum_start_daemon)
return;
! if (!pgstat_collect_startcollector || !pgstat_collect_tuplelevel)
{
ereport(WARNING,
(errmsg("autovacuum not started because of misconfiguration"),
! errhint("Enable options \"stats_start_collector\" and \"stats_row_level\".")));
/*
* Set the GUC var so we don't fork autovacuum uselessly, and also to
--- 2616,2626 ----
if (!autovacuum_start_daemon)
return;
! if (!pgstat_collect_startcollector || !pgstat_collect_stats)
{
ereport(WARNING,
(errmsg("autovacuum not started because of misconfiguration"),
! errhint("Enable option \"stats_collection\".")));
/*
* Set the GUC var so we don't fork autovacuum uselessly, and also to
Index: src/backend/postmaster/pgstat.c
===================================================================
RCS file: /projects/cvsroot/pgsql/src/backend/postmaster/pgstat.c,v
retrieving revision 1.161
diff -c -r1.161 pgstat.c
*** src/backend/postmaster/pgstat.c 8 Jul 2007 22:23:16 -0000 1.161
--- src/backend/postmaster/pgstat.c 27 Jul 2007 13:47:28 -0000
***************
*** 93,102 ****
* GUC parameters
* ----------
*/
! bool pgstat_collect_startcollector = true;
bool pgstat_collect_resetonpmstart = false;
- bool pgstat_collect_tuplelevel = false;
- bool pgstat_collect_blocklevel = false;
bool pgstat_collect_querystring = false;
/*
--- 93,101 ----
* GUC parameters
* ----------
*/
! bool pgstat_collect_startcollector = true;
! bool pgstat_collect_stats = true;
bool pgstat_collect_resetonpmstart = false;
bool pgstat_collect_querystring = false;
/*
***************
*** 257,271 ****
#define TESTBYTEVAL ((char) 199)
/*
- * Force start of collector daemon if something to collect. Note that
- * pgstat_collect_querystring is now an independent facility that does not
- * require the collector daemon.
- */
- if (pgstat_collect_tuplelevel ||
- pgstat_collect_blocklevel)
- pgstat_collect_startcollector = true;
-
- /*
* If we don't have to start a collector or should reset the collected
* statistics on postmaster start, simply remove the stats file.
*/
--- 256,261 ----
***************
*** 494,501 ****
/* Adjust GUC variables to suppress useless activity */
pgstat_collect_startcollector = false;
! pgstat_collect_tuplelevel = false;
! pgstat_collect_blocklevel = false;
}
/*
--- 484,490 ----
/* Adjust GUC variables to suppress useless activity */
pgstat_collect_startcollector = false;
! pgstat_collect_stats = false;
}
/*
***************
*** 1053,1059 ****
PgStat_MsgVacuum msg;
if (pgStatSock < 0 ||
! !pgstat_collect_tuplelevel)
return;
pgstat_setheader(&msg.m_hdr, PGSTAT_MTYPE_VACUUM);
--- 1042,1048 ----
PgStat_MsgVacuum msg;
if (pgStatSock < 0 ||
! !pgstat_collect_stats)
return;
pgstat_setheader(&msg.m_hdr, PGSTAT_MTYPE_VACUUM);
***************
*** 1079,1085 ****
PgStat_MsgAnalyze msg;
if (pgStatSock < 0 ||
! !pgstat_collect_tuplelevel)
return;
pgstat_setheader(&msg.m_hdr, PGSTAT_MTYPE_ANALYZE);
--- 1068,1074 ----
PgStat_MsgAnalyze msg;
if (pgStatSock < 0 ||
! !pgstat_collect_stats)
return;
pgstat_setheader(&msg.m_hdr, PGSTAT_MTYPE_ANALYZE);
***************
*** 1139,1147 ****
return;
}
! if (pgStatSock < 0 ||
! !(pgstat_collect_tuplelevel ||
! pgstat_collect_blocklevel))
{
/* We're not counting at all */
rel->pgstat_info = NULL;
--- 1128,1134 ----
return;
}
! if (pgStatSock < 0 || !pgstat_collect_stats)
{
/* We're not counting at all */
rel->pgstat_info = NULL;
***************
*** 1274,1280 ****
{
PgStat_TableStatus *pgstat_info = rel->pgstat_info;
! if (pgstat_collect_tuplelevel && pgstat_info != NULL)
{
int nest_level = GetCurrentTransactionNestLevel();
--- 1261,1267 ----
{
PgStat_TableStatus *pgstat_info = rel->pgstat_info;
! if (pgstat_collect_stats && pgstat_info != NULL)
{
int nest_level = GetCurrentTransactionNestLevel();
***************
*** 1298,1304 ****
{
PgStat_TableStatus *pgstat_info = rel->pgstat_info;
! if (pgstat_collect_tuplelevel && pgstat_info != NULL)
{
int nest_level = GetCurrentTransactionNestLevel();
--- 1285,1291 ----
{
PgStat_TableStatus *pgstat_info = rel->pgstat_info;
! if (pgstat_collect_stats && pgstat_info != NULL)
{
int nest_level = GetCurrentTransactionNestLevel();
***************
*** 1324,1330 ****
{
PgStat_TableStatus *pgstat_info = rel->pgstat_info;
! if (pgstat_collect_tuplelevel && pgstat_info != NULL)
{
int nest_level = GetCurrentTransactionNestLevel();
--- 1311,1317 ----
{
PgStat_TableStatus *pgstat_info = rel->pgstat_info;
! if (pgstat_collect_stats && pgstat_info != NULL)
{
int nest_level = GetCurrentTransactionNestLevel();
Index: src/backend/utils/misc/guc.c
===================================================================
RCS file: /projects/cvsroot/pgsql/src/backend/utils/misc/guc.c,v
retrieving revision 1.407
diff -c -r1.407 guc.c
*** src/backend/utils/misc/guc.c 24 Jul 2007 04:54:09 -0000 1.407
--- src/backend/utils/misc/guc.c 27 Jul 2007 13:47:30 -0000
***************
*** 727,740 ****
&Explain_pretty_print,
true, NULL, NULL
},
{
! {"stats_start_collector", PGC_POSTMASTER, STATS_COLLECTOR,
gettext_noop("Starts the server statistics-collection subprocess."),
NULL
},
! &pgstat_collect_startcollector,
true, NULL, NULL
},
{
{"stats_reset_on_server_start", PGC_POSTMASTER, STATS_COLLECTOR,
gettext_noop("Zeroes collected statistics on server restart."),
--- 727,742 ----
&Explain_pretty_print,
true, NULL, NULL
},
+
{
! {"stats_collection", PGC_SUSET, STATS_COLLECTOR,
gettext_noop("Starts the server statistics-collection subprocess."),
NULL
},
! &pgstat_collect_stats,
true, NULL, NULL
},
+
{
{"stats_reset_on_server_start", PGC_POSTMASTER, STATS_COLLECTOR,
gettext_noop("Zeroes collected statistics on server restart."),
***************
*** 743,764 ****
&pgstat_collect_resetonpmstart,
false, NULL, NULL
},
- {
- {"stats_row_level", PGC_SUSET, STATS_COLLECTOR,
- gettext_noop("Collects row-level statistics on database activity."),
- NULL
- },
- &pgstat_collect_tuplelevel,
- true, NULL, NULL
- },
- {
- {"stats_block_level", PGC_SUSET, STATS_COLLECTOR,
- gettext_noop("Collects block-level statistics on database activity."),
- NULL
- },
- &pgstat_collect_blocklevel,
- false, NULL, NULL
- },
{
{"stats_command_string", PGC_SUSET, STATS_COLLECTOR,
--- 745,750 ----
Index: src/backend/utils/misc/postgresql.conf.sample
===================================================================
RCS file: /projects/cvsroot/pgsql/src/backend/utils/misc/postgresql.conf.sample,v
retrieving revision 1.221
diff -c -r1.221 postgresql.conf.sample
*** src/backend/utils/misc/postgresql.conf.sample 24 Jul 2007 04:54:09 -0000 1.221
--- src/backend/utils/misc/postgresql.conf.sample 27 Jul 2007 13:47:30 -0000
***************
*** 356,365 ****
#stats_command_string = on
#update_process_title = on
! #stats_start_collector = on # needed for block or row stats
! # (change requires restart)
! #stats_block_level = off
! #stats_row_level = on
#stats_reset_on_server_start = off # (change requires restart)
--- 356,362 ----
#stats_command_string = on
#update_process_title = on
! #stats_collection = on # needed for block or row stats
#stats_reset_on_server_start = off # (change requires restart)
***************
*** 376,383 ****
#---------------------------------------------------------------------------
#autovacuum = on # enable autovacuum subprocess?
! # 'on' requires stats_start_collector
! # and stats_row_level to also be on
#autovacuum_max_workers = 3 # max # of autovacuum subprocesses
#autovacuum_naptime = 1min # time between autovacuum runs
#log_autovacuum = -1 # -1 is disabled, 0 logs all actions
--- 373,379 ----
#---------------------------------------------------------------------------
#autovacuum = on # enable autovacuum subprocess?
! # 'on' requires stats_collection also to be on
#autovacuum_max_workers = 3 # max # of autovacuum subprocesses
#autovacuum_naptime = 1min # time between autovacuum runs
#log_autovacuum = -1 # -1 is disabled, 0 logs all actions
Index: src/include/pgstat.h
===================================================================
RCS file: /projects/cvsroot/pgsql/src/include/pgstat.h,v
retrieving revision 1.64
diff -c -r1.64 pgstat.h
*** src/include/pgstat.h 8 Jul 2007 22:23:16 -0000 1.64
--- src/include/pgstat.h 27 Jul 2007 13:47:31 -0000
***************
*** 450,459 ****
* GUC parameters
* ----------
*/
! extern bool pgstat_collect_startcollector;
extern bool pgstat_collect_resetonpmstart;
! extern bool pgstat_collect_tuplelevel;
! extern bool pgstat_collect_blocklevel;
extern bool pgstat_collect_querystring;
/*
--- 450,458 ----
* GUC parameters
* ----------
*/
! extern bool pgstat_collect_startcollector;
extern bool pgstat_collect_resetonpmstart;
! extern bool pgstat_collect_stats;
extern bool pgstat_collect_querystring;
/*
***************
*** 510,546 ****
#define pgstat_count_heap_scan(rel) \
do { \
! if (pgstat_collect_tuplelevel && (rel)->pgstat_info != NULL) \
(rel)->pgstat_info->t_counts.t_numscans++; \
} while (0)
#define pgstat_count_heap_getnext(rel) \
do { \
! if (pgstat_collect_tuplelevel && (rel)->pgstat_info != NULL) \
(rel)->pgstat_info->t_counts.t_tuples_returned++; \
} while (0)
#define pgstat_count_heap_fetch(rel) \
do { \
! if (pgstat_collect_tuplelevel && (rel)->pgstat_info != NULL) \
(rel)->pgstat_info->t_counts.t_tuples_fetched++; \
} while (0)
#define pgstat_count_index_scan(rel) \
do { \
! if (pgstat_collect_tuplelevel && (rel)->pgstat_info != NULL) \
(rel)->pgstat_info->t_counts.t_numscans++; \
} while (0)
#define pgstat_count_index_tuples(rel, n) \
do { \
! if (pgstat_collect_tuplelevel && (rel)->pgstat_info != NULL) \
(rel)->pgstat_info->t_counts.t_tuples_returned += (n); \
} while (0)
#define pgstat_count_buffer_read(rel) \
do { \
! if (pgstat_collect_blocklevel && (rel)->pgstat_info != NULL) \
(rel)->pgstat_info->t_counts.t_blocks_fetched++; \
} while (0)
#define pgstat_count_buffer_hit(rel) \
do { \
! if (pgstat_collect_blocklevel && (rel)->pgstat_info != NULL) \
(rel)->pgstat_info->t_counts.t_blocks_hit++; \
} while (0)
--- 509,545 ----
#define pgstat_count_heap_scan(rel) \
do { \
! if (pgstat_collect_stats && (rel)->pgstat_info != NULL) \
(rel)->pgstat_info->t_counts.t_numscans++; \
} while (0)
#define pgstat_count_heap_getnext(rel) \
do { \
! if (pgstat_collect_stats && (rel)->pgstat_info != NULL) \
(rel)->pgstat_info->t_counts.t_tuples_returned++; \
} while (0)
#define pgstat_count_heap_fetch(rel) \
do { \
! if (pgstat_collect_stats && (rel)->pgstat_info != NULL) \
(rel)->pgstat_info->t_counts.t_tuples_fetched++; \
} while (0)
#define pgstat_count_index_scan(rel) \
do { \
! if (pgstat_collect_stats && (rel)->pgstat_info != NULL) \
(rel)->pgstat_info->t_counts.t_numscans++; \
} while (0)
#define pgstat_count_index_tuples(rel, n) \
do { \
! if (pgstat_collect_stats && (rel)->pgstat_info != NULL) \
(rel)->pgstat_info->t_counts.t_tuples_returned += (n); \
} while (0)
#define pgstat_count_buffer_read(rel) \
do { \
! if (pgstat_collect_stats && (rel)->pgstat_info != NULL) \
(rel)->pgstat_info->t_counts.t_blocks_fetched++; \
} while (0)
#define pgstat_count_buffer_hit(rel) \
do { \
! if (pgstat_collect_stats && (rel)->pgstat_info != NULL) \
(rel)->pgstat_info->t_counts.t_blocks_hit++; \
} while (0)
Index: src/test/regress/expected/stats.out
===================================================================
RCS file: /projects/cvsroot/pgsql/src/test/regress/expected/stats.out,v
retrieving revision 1.11
diff -c -r1.11 stats.out
*** src/test/regress/expected/stats.out 30 Apr 2007 03:23:49 -0000 1.11
--- src/test/regress/expected/stats.out 27 Jul 2007 13:47:31 -0000
***************
*** 4,16 ****
-- Must be run after tenk2 has been created (by create_table),
-- populated (by create_misc) and indexed (by create_index).
--
- -- conditio sine qua non
- SHOW stats_start_collector; -- must be on
- stats_start_collector
- -----------------------
- on
- (1 row)
-
-- wait to let any prior tests finish dumping out stats;
-- else our messages might get lost due to contention
SELECT pg_sleep(2.0);
--- 4,9 ----
***************
*** 57,64 ****
end
$$ language plpgsql;
-- enable statistics
! SET stats_block_level = on;
! SET stats_row_level = on;
-- do a seqscan
SELECT count(*) FROM tenk2;
count
--- 50,56 ----
end
$$ language plpgsql;
-- enable statistics
! SET stats_collection = on;
-- do a seqscan
SELECT count(*) FROM tenk2;
count
Index: src/test/regress/sql/stats.sql
===================================================================
RCS file: /projects/cvsroot/pgsql/src/test/regress/sql/stats.sql,v
retrieving revision 1.9
diff -c -r1.9 stats.sql
*** src/test/regress/sql/stats.sql 30 Apr 2007 03:23:49 -0000 1.9
--- src/test/regress/sql/stats.sql 27 Jul 2007 13:47:31 -0000
***************
*** 5,13 ****
-- populated (by create_misc) and indexed (by create_index).
--
- -- conditio sine qua non
- SHOW stats_start_collector; -- must be on
-
-- wait to let any prior tests finish dumping out stats;
-- else our messages might get lost due to contention
SELECT pg_sleep(2.0);
--- 5,10 ----
***************
*** 52,59 ****
$$ language plpgsql;
-- enable statistics
! SET stats_block_level = on;
! SET stats_row_level = on;
-- do a seqscan
SELECT count(*) FROM tenk2;
--- 49,55 ----
$$ language plpgsql;
-- enable statistics
! SET stats_collection = on;
-- do a seqscan
SELECT count(*) FROM tenk2;
---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend
