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

Tom Lane wrote:
> Bruce Momjian <bruce@momjian.us> writes:
> > Yep, I see 8% here.  I will add a patch to allow the ps display to be
> > turned off.
>
> I think we'd still want a backend to set the PS display once with its
> identification data (user/DB name and client address).  It's just the
> transient activity updates that should stop.

Attached patch adds GUC 'update_process_title' to control ps display
updates per SQL command.  Default to 'on'.  GUC name OK?

--
  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 19:59:53 -0000
***************
*** 2888,2893 ****
--- 2888,2908 ----
        </listitem>
       </varlistentry>

+      <varlistentry id="guc-update-process-title" xreflabel="update_process_title">
+       <term><varname>update_process_title</varname> (<type>boolean</type>)</term>
+       <indexterm>
+        <primary><varname>update_process_title</> configuration parameter</primary>
+       </indexterm>
+       <listitem>
+        <para>
+         Enables updating of the process title every time a new SQL command
+         is received by the server.  The process title is typically viewed
+         by the <command>ps</> command or in Windows using the <application>Process
+         Explorer</>.   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>
Index: src/backend/commands/async.c
===================================================================
RCS file: /cvsroot/pgsql/src/backend/commands/async.c,v
retrieving revision 1.131
diff -c -c -r1.131 async.c
*** src/backend/commands/async.c    25 Apr 2006 14:11:54 -0000    1.131
--- src/backend/commands/async.c    26 Jun 2006 19:59:56 -0000
***************
*** 908,914 ****
      if (Trace_notify)
          elog(DEBUG1, "ProcessIncomingNotify");

!     set_ps_display("notify interrupt");

      notifyInterruptOccurred = 0;

--- 908,915 ----
      if (Trace_notify)
          elog(DEBUG1, "ProcessIncomingNotify");

!     if (update_process_title)
!         set_ps_display("notify interrupt");

      notifyInterruptOccurred = 0;

***************
*** 979,985 ****
       */
      pq_flush();

!     set_ps_display("idle");

      if (Trace_notify)
          elog(DEBUG1, "ProcessIncomingNotify: done");
--- 980,987 ----
       */
      pq_flush();

!     if (update_process_title)
!         set_ps_display("idle");

      if (Trace_notify)
          elog(DEBUG1, "ProcessIncomingNotify: done");
Index: src/backend/postmaster/postmaster.c
===================================================================
RCS file: /cvsroot/pgsql/src/backend/postmaster/postmaster.c,v
retrieving revision 1.488
diff -c -c -r1.488 postmaster.c
*** src/backend/postmaster/postmaster.c    20 Jun 2006 22:52:00 -0000    1.488
--- src/backend/postmaster/postmaster.c    26 Jun 2006 20:00:03 -0000
***************
*** 2714,2721 ****
       * title for ps.  It's good to do this as early as possible in startup.
       */
      init_ps_display(port->user_name, port->database_name, remote_ps_data);
!     set_ps_display("authentication");
!
      /*
       * Now perform authentication exchange.
       */
--- 2714,2724 ----
       * title for ps.  It's good to do this as early as possible in startup.
       */
      init_ps_display(port->user_name, port->database_name, remote_ps_data);
!     if (update_process_title)
!         set_ps_display("authentication");
!     else
!         set_ps_display("");
!
      /*
       * Now perform authentication exchange.
       */
Index: src/backend/storage/lmgr/lock.c
===================================================================
RCS file: /cvsroot/pgsql/src/backend/storage/lmgr/lock.c,v
retrieving revision 1.164
diff -c -c -r1.164 lock.c
*** src/backend/storage/lmgr/lock.c    14 Apr 2006 03:38:55 -0000    1.164
--- src/backend/storage/lmgr/lock.c    26 Jun 2006 20:00:05 -0000
***************
*** 1069,1075 ****
      new_status = (char *) palloc(len + 8 + 1);
      memcpy(new_status, old_status, len);
      strcpy(new_status + len, " waiting");
!     set_ps_display(new_status);
      new_status[len] = '\0';        /* truncate off " waiting" */

      awaitedLock = locallock;
--- 1069,1076 ----
      new_status = (char *) palloc(len + 8 + 1);
      memcpy(new_status, old_status, len);
      strcpy(new_status + len, " waiting");
!     if (update_process_title)
!         set_ps_display(new_status);
      new_status[len] = '\0';        /* truncate off " waiting" */

      awaitedLock = locallock;
***************
*** 1108,1114 ****

      awaitedLock = NULL;

!     set_ps_display(new_status);
      pfree(new_status);

      LOCK_PRINT("WaitOnLock: wakeup on lock",
--- 1109,1116 ----

      awaitedLock = NULL;

!     if (update_process_title)
!         set_ps_display(new_status);
      pfree(new_status);

      LOCK_PRINT("WaitOnLock: wakeup on lock",
Index: src/backend/tcop/postgres.c
===================================================================
RCS file: /cvsroot/pgsql/src/backend/tcop/postgres.c,v
retrieving revision 1.489
diff -c -c -r1.489 postgres.c
*** src/backend/tcop/postgres.c    20 Jun 2006 22:52:00 -0000    1.489
--- src/backend/tcop/postgres.c    26 Jun 2006 20:00:14 -0000
***************
*** 910,916 ****
           */
          commandTag = CreateCommandTag(parsetree);

!         set_ps_display(commandTag);

          BeginCommand(commandTag, dest);

--- 910,917 ----
           */
          commandTag = CreateCommandTag(parsetree);

!         if (update_process_title)
!             set_ps_display(commandTag);

          BeginCommand(commandTag, dest);

***************
*** 1144,1150 ****

      pgstat_report_activity(query_string);

!     set_ps_display("PARSE");

      if (save_log_statement_stats)
          ResetUsage();
--- 1145,1152 ----

      pgstat_report_activity(query_string);

!     if (update_process_title)
!         set_ps_display("PARSE");

      if (save_log_statement_stats)
          ResetUsage();
***************
*** 1376,1382 ****

      pgstat_report_activity("<BIND>");

!     set_ps_display("BIND");

      /*
       * Start up a transaction command so we can call functions etc. (Note that
--- 1378,1385 ----

      pgstat_report_activity("<BIND>");

!     if (update_process_title)
!         set_ps_display("BIND");

      /*
       * Start up a transaction command so we can call functions etc. (Note that
***************
*** 1711,1717 ****
          pgstat_report_activity("<EXECUTE>");
      }

!     set_ps_display(portal->commandTag);

      /*
       * We use save_log_statement_stats so ShowUsage doesn't report incorrect
--- 1714,1721 ----
          pgstat_report_activity("<EXECUTE>");
      }

!     if (update_process_title)
!         set_ps_display(portal->commandTag);

      /*
       * We use save_log_statement_stats so ShowUsage doesn't report incorrect
***************
*** 2486,2492 ****
      if (!IsUnderPostmaster)
          MemoryContextInit();

!     set_ps_display("startup");

      SetProcessingMode(InitProcessing);

--- 2490,2497 ----
      if (!IsUnderPostmaster)
          MemoryContextInit();

!     if (update_process_title)
!         set_ps_display("startup");

      SetProcessingMode(InitProcessing);

***************
*** 3121,3134 ****
          {
              if (IsTransactionOrTransactionBlock())
              {
!                 set_ps_display("idle in transaction");
                  pgstat_report_activity("<IDLE> in transaction");
              }
              else
              {
                  pgstat_report_tabstat();

!                 set_ps_display("idle");
                  pgstat_report_activity("<IDLE>");
              }

--- 3126,3141 ----
          {
              if (IsTransactionOrTransactionBlock())
              {
!                 if (update_process_title)
!                     set_ps_display("idle in transaction");
                  pgstat_report_activity("<IDLE> in transaction");
              }
              else
              {
                  pgstat_report_tabstat();

!                 if (update_process_title)
!                     set_ps_display("idle");
                  pgstat_report_activity("<IDLE>");
              }

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 20:00:30 -0000
***************
*** 64,69 ****
--- 64,70 ----
  #include "utils/builtins.h"
  #include "utils/memutils.h"
  #include "utils/pg_locale.h"
+ #include "utils/ps_status.h"
  #include "pgstat.h"
  #include "access/gin.h"

***************
*** 729,734 ****
--- 730,745 ----
      },

      {
+         {"update_process_title", PGC_SUSET, STATS_COLLECTOR,
+             gettext_noop("Updates the process title to show the active SQL command."),
+             gettext_noop("Enables updating of the process title every time a new
+             SQL command is received by the server.")
+         },
+         &update_process_title,
+         true, NULL, NULL
+     },
+
+     {
          {"autovacuum", PGC_SIGHUP, AUTOVACUUM,
              gettext_noop("Starts the autovacuum subprocess."),
              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 20:00:30 -0000
***************
*** 323,333 ****
--- 323,336 ----
  # - Query/Index Statistics Collector -

  #stats_command_string = off
+ #update_process_title = on
+
  #stats_start_collector = on        # needed for block or row stats
  #stats_block_level = off
  #stats_row_level = off
  #stats_reset_on_server_start = off

+
  # - Statistics Monitoring -

  #log_parser_stats = off
Index: src/backend/utils/misc/ps_status.c
===================================================================
RCS file: /cvsroot/pgsql/src/backend/utils/misc/ps_status.c,v
retrieving revision 1.30
diff -c -c -r1.30 ps_status.c
*** src/backend/utils/misc/ps_status.c    12 Jun 2006 02:39:49 -0000    1.30
--- src/backend/utils/misc/ps_status.c    26 Jun 2006 20:00:31 -0000
***************
*** 31,36 ****
--- 31,37 ----
  #include "utils/ps_status.h"

  extern char **environ;
+ bool update_process_title = true;


  /*
Index: src/include/utils/ps_status.h
===================================================================
RCS file: /cvsroot/pgsql/src/include/utils/ps_status.h,v
retrieving revision 1.26
diff -c -c -r1.26 ps_status.h
*** src/include/utils/ps_status.h    5 Nov 2005 03:04:53 -0000    1.26
--- src/include/utils/ps_status.h    26 Jun 2006 20:00:32 -0000
***************
*** 12,17 ****
--- 12,19 ----
  #ifndef PS_STATUS_H
  #define PS_STATUS_H

+ extern bool update_process_title;
+
  extern char **save_ps_display_args(int argc, char **argv);

  extern void init_ps_display(const char *username, const char *dbname,