Re: [HACKERS] Overhead for stats_command_string et al, take 2 - Mailing list pgsql-patches

Tom Lane wrote:
> Bruce Momjian <bruce@momjian.us> writes:
> > Seeing stats_command_string with almost zero overhead is great news!
> > Should we remove that setting and just have it enabled all
> > the time?
>
> If you don't need it, you shouldn't have to pay any overhead for it,
> I think.  One could make an argument now for having stats_command_string
> default to ON, though.

The attached patch makes stats_command_string default to 'on', and
updates the documentation.

> Something that might also be interesting is an option to suppress
> per-command ps_status reporting.  On machines where updating ps status
> takes a kernel call, there's now a pretty good argument why you might
> want to turn that off and rely on pg_stat_activity instead.

OK, can I get a timing report from someone with the title on/off that
shows a difference?

--
  Bruce Momjian   bruce@momjian.us
  EnterpriseDB    http://www.enterprisedb.com

  + If your life is a hard drive, Christ can be your backup. +
Index: doc/src/sgml/config.sgml
===================================================================
RCS file: /cvsroot/pgsql/doc/src/sgml/config.sgml,v
retrieving revision 1.66
diff -c -c -r1.66 config.sgml
*** doc/src/sgml/config.sgml    19 Jun 2006 01:51:21 -0000    1.66
--- doc/src/sgml/config.sgml    26 Jun 2006 17:13:05 -0000
***************
*** 2878,2884 ****
         <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
--- 2878,2884 ----
         <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 on 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
Index: doc/src/sgml/monitoring.sgml
===================================================================
RCS file: /cvsroot/pgsql/doc/src/sgml/monitoring.sgml,v
retrieving revision 1.34
diff -c -c -r1.34 monitoring.sgml
*** doc/src/sgml/monitoring.sgml    19 Jun 2006 01:51:21 -0000    1.34
--- doc/src/sgml/monitoring.sgml    26 Jun 2006 17:13:05 -0000
***************
*** 170,181 ****

     <note>
      <para>
!      Since the parameters <varname>stats_command_string</varname>,
!      <varname>stats_block_level</varname>, and
       <varname>stats_row_level</varname> default to <literal>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 facilities, at the expense of
       additional run-time overhead.
      </para>
--- 170,180 ----

     <note>
      <para>
!      Since the parameters <varname>stats_block_level</varname>, and
       <varname>stats_row_level</varname> default to <literal>false</>,
       very few statistics are collected in the default
!      configuration. Enabling either of these configuration
!      variables will significantly increase the amount of useful data
       produced by the statistics facilities, at the expense of
       additional run-time overhead.
      </para>
***************
*** 241,249 ****
        process <acronym>ID</>, user OID, user name, current query, time at
        which the current query began execution, time at which the process
        was started, and client's address and port number.  The columns
!       that report data on the current query are only available if the
        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.
       </entry>
--- 240,248 ----
        process <acronym>ID</>, user OID, user name, current query, time at
        which the current query began execution, time at which the process
        was started, and client's address and port number.  The columns
!       that report data on the current query are available unless the
        parameter <varname>stats_command_string</varname> has been
!       turned off.  Furthermore, these columns are only visible if the
        user examining the view is a superuser or the same as the user
        owning the process being reported on.
       </entry>
***************
*** 635,644 ****
        <entry><literal><function>pg_stat_get_backend_activity</function>(<type>integer</type>)</literal></entry>
        <entry><type>text</type></entry>
        <entry>
!        Active command of the given server process (null if the
!        current user is not a superuser nor the same user as that of
!        the session being queried, or
!        <varname>stats_command_string</varname> is not on)
        </entry>
       </row>

--- 634,643 ----
        <entry><literal><function>pg_stat_get_backend_activity</function>(<type>integer</type>)</literal></entry>
        <entry><type>text</type></entry>
        <entry>
!        Active command of the given server process, but only if the
!        current user is a superuser or the same user as that of
!        the session being queried (and
!        <varname>stats_command_string</varname> is on)
        </entry>
       </row>

***************
*** 647,656 ****
        <entry><type>timestamp with time zone</type></entry>
        <entry>
         The time at which the given server process' currently
!        executing query was started (null if the
!        current user is not a superuser nor the same user as that of
!        the session being queried, or
!        <varname>stats_command_string</varname> is not on)
        </entry>
       </row>

--- 646,655 ----
        <entry><type>timestamp with time zone</type></entry>
        <entry>
         The time at which the given server process' currently
!        executing query was started, but only if the
!        current user is a superuser or the same user as that of
!        the session being queried (and
!        <varname>stats_command_string</varname> is on)
        </entry>
       </row>

Index: src/backend/utils/misc/guc.c
===================================================================
RCS file: /cvsroot/pgsql/src/backend/utils/misc/guc.c,v
retrieving revision 1.322
diff -c -c -r1.322 guc.c
*** src/backend/utils/misc/guc.c    19 Jun 2006 01:51:21 -0000    1.322
--- src/backend/utils/misc/guc.c    26 Jun 2006 17:13:09 -0000
***************
*** 725,731 ****
                           "at which that command began execution.")
          },
          &pgstat_collect_querystring,
!         false, NULL, NULL
      },

      {
--- 725,731 ----
                           "at which that command began execution.")
          },
          &pgstat_collect_querystring,
!         true, NULL, NULL
      },

      {
Index: src/backend/utils/misc/postgresql.conf.sample
===================================================================
RCS file: /cvsroot/pgsql/src/backend/utils/misc/postgresql.conf.sample,v
retrieving revision 1.180
diff -c -c -r1.180 postgresql.conf.sample
*** src/backend/utils/misc/postgresql.conf.sample    19 Jun 2006 01:51:21 -0000    1.180
--- src/backend/utils/misc/postgresql.conf.sample    26 Jun 2006 17:13:11 -0000
***************
*** 322,328 ****

  # - Query/Index Statistics Collector -

! #stats_command_string = off
  #stats_start_collector = on        # needed for block or row stats
  #stats_block_level = off
  #stats_row_level = off
--- 322,328 ----

  # - Query/Index Statistics Collector -

! #stats_command_string = on
  #stats_start_collector = on        # needed for block or row stats
  #stats_block_level = off
  #stats_row_level = off