Overhead for stats_command_string et al, take 2
I redid my previous measurements after finishing up the weekend's
hacking. The numbers shown below are elapsed time in seconds for
time psql -f testfile.sql postgres >/dev/null
using CVS HEAD and REL8_1_STABLE branch tip, compiled --enable-debug
--disable-cassert, no nondefault options except for turning fsync off
(which doesn't particularly affect read-only tests like these anyway).
The machines are both running current Fedora Core 5. The older x86
machine is known to have the slow-gettimeofday problem from previous
experimentation with EXPLAIN ANALYZE. Each number is the median of 3
or more tests, rounded off to 0.1 second (I wouldn't put a lot of faith
in differences of 0.1 sec or so, because of the variance I saw in the
tests).
x86 x86_64
8.1 HEAD 8.1 HEAD
100000 "SELECT 1;" 25.9 27.0 9.2 9.1
with stats_command_string=1 63.5 27.6 18.7 9.2
with log_min_duration_statement=100 26.9 27.8 9.6 9.2
with statement_timeout=100 27.5 28.6 9.6 9.8
with all 3 features 66.1 29.3 19.5 9.7
BEGIN, 100000 "SELECT 1;", COMMIT 21.2 23.1 8.3 8.4
with stats_command_string=1 52.3 23.5 15.4 8.5
with log_min_duration_statement=100 22.1 23.4 8.4 8.4
with statement_timeout=100 23.7 24.3 8.6 8.8
with all 3 features 55.2 25.5 16.0 8.8
I chose the log_min_duration_statement and statement_timeout settings
high enough so that no actual logging or timeout would happen --- the
point is to measure the measurement overhead.
The good news is that we've pretty much licked the problem of
stats_command_string costing an unreasonable amount.
The bad news is that except in the stats_command_string cases, HEAD
is noticeably slower than 8.1 on the machine with slow gettimeofday.
In the single-transaction test this might be blamed on the addition
of statement_timestamp support (which requires a gettimeofday per
statement that wasn't there in 8.1) ... but in the one-transaction-
per-statement tests that doesn't hold water, because each branch is
doing a gettimeofday per statement, just in different places.
Can anyone else reproduce this slowdown? It might be only an artifact
of these particular builds, but it's a bit too consistent in my x86 data
to just ignore.
BTW, according to "top" the CPU usage percentages in these tests are
on the order of 55% backend, 45% psql. Methinks psql needs a round
of performance tuning ...
regards, tom lane
I wrote:
BTW, according to "top" the CPU usage percentages in these tests are
on the order of 55% backend, 45% psql. Methinks psql needs a round
of performance tuning ...
oprofile tells the tale:
CPU: P4 / Xeon with 2 hyper-threads, speed 2793.03 MHz (estimated)
Counted GLOBAL_POWER_EVENTS events (time during which processor is not stopped)
with a unit mask of 0x01 (mandatory) count 240000
GLOBAL_POWER_E...|
samples| %|
------------------
682534 52.7683 /usr/lib/debug/lib/modules/2.6.16-1.2133_FC5/vmlinux
274747 21.2413 /home/tgl/testversion/bin/postgres
226306 17.4962 /lib64/libc-2.4.so
54296 4.1977 /home/tgl/testversion/bin/psql
45376 3.5081 /home/tgl/testversion/lib/libpq.so.5.0
5302 0.4099 /usr/bin/oprofiled
1954 0.1511 /oprofile
It's all about the kernel process-switch overhead, which is being blamed
equally on both processes.
I did find some low-hanging fruit in GetVariable(), but nothing else
that looked readily improvable.
regards, tom lane
Sorry I am traveling for EnterpriseDB Wednesday and Thursday so I can't
run the tests right now.
Seeing stats_command_string with almost zero overhead is great news!
Should we remove that setting and just have it enabled all
the time?
As far as pg_stat_activity.query_start, I never suspected that column
would show the time of start of IDLE. I think we can just document that
that column is the time of the start of the last query, and avoid the
gettimeofday() call on IDLE start. If we get demand for the old
behavior, we can consider re-adding it, but I bet when they hear the
downside (a second gettimeofday() call for every query), no one will
want the old behavior.
---------------------------------------------------------------------------
Tom Lane wrote:
I redid my previous measurements after finishing up the weekend's
hacking. The numbers shown below are elapsed time in seconds fortime psql -f testfile.sql postgres >/dev/null
using CVS HEAD and REL8_1_STABLE branch tip, compiled --enable-debug
--disable-cassert, no nondefault options except for turning fsync off
(which doesn't particularly affect read-only tests like these anyway).
The machines are both running current Fedora Core 5. The older x86
machine is known to have the slow-gettimeofday problem from previous
experimentation with EXPLAIN ANALYZE. Each number is the median of 3
or more tests, rounded off to 0.1 second (I wouldn't put a lot of faith
in differences of 0.1 sec or so, because of the variance I saw in the
tests).x86 x86_64
8.1 HEAD 8.1 HEAD
100000 "SELECT 1;" 25.9 27.0 9.2 9.1
with stats_command_string=1 63.5 27.6 18.7 9.2
with log_min_duration_statement=100 26.9 27.8 9.6 9.2
with statement_timeout=100 27.5 28.6 9.6 9.8
with all 3 features 66.1 29.3 19.5 9.7BEGIN, 100000 "SELECT 1;", COMMIT 21.2 23.1 8.3 8.4
with stats_command_string=1 52.3 23.5 15.4 8.5
with log_min_duration_statement=100 22.1 23.4 8.4 8.4
with statement_timeout=100 23.7 24.3 8.6 8.8
with all 3 features 55.2 25.5 16.0 8.8I chose the log_min_duration_statement and statement_timeout settings
high enough so that no actual logging or timeout would happen --- the
point is to measure the measurement overhead.The good news is that we've pretty much licked the problem of
stats_command_string costing an unreasonable amount.The bad news is that except in the stats_command_string cases, HEAD
is noticeably slower than 8.1 on the machine with slow gettimeofday.
In the single-transaction test this might be blamed on the addition
of statement_timestamp support (which requires a gettimeofday per
statement that wasn't there in 8.1) ... but in the one-transaction-
per-statement tests that doesn't hold water, because each branch is
doing a gettimeofday per statement, just in different places.Can anyone else reproduce this slowdown? It might be only an artifact
of these particular builds, but it's a bit too consistent in my x86 data
to just ignore.BTW, according to "top" the CPU usage percentages in these tests are
on the order of 55% backend, 45% psql. Methinks psql needs a round
of performance tuning ...regards, tom lane
---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend
--
Bruce Momjian bruce@momjian.us
EnterpriseDB http://www.enterprisedb.com
+ If your life is a hard drive, Christ can be your backup. +
Tom Lane wrote:
The bad news is that except in the stats_command_string cases, HEAD
is noticeably slower than 8.1 on the machine with slow gettimeofday.
In the single-transaction test this might be blamed on the addition
of statement_timestamp support (which requires a gettimeofday per
statement that wasn't there in 8.1) ... but in the one-transaction-
per-statement tests that doesn't hold water, because each branch is
doing a gettimeofday per statement, just in different places.Can anyone else reproduce this slowdown? It might be only an artifact
of these particular builds, but it's a bit too consistent in my x86 data
to just ignore.
This is what I get on a fast AMD Dual Opteron box(Running Debian
Sarge/AMD64):
8.1.4 HEAD
1000000 SELECT 1; 74,74,73 77,76,77
stats_command_string=1; 105,99,106 78,79,78
log_min_duration_statement=100 79,80,81 75,80,76
statement_timeout=100 78,79,78 75,79,77
all 3 104,108,107 82,81,81
all values in seconds with 3 consecutive runs of one million "SELECT 1;"
queries. It takes about 48 seconds to run the same test without
stat-collection btw.
Stefan
Stefan Kaltenbrunner <stefan@kaltenbrunner.cc> writes:
This is what I get on a fast AMD Dual Opteron box(Running Debian
Sarge/AMD64):
8.1.4 HEAD
1000000 SELECT 1; 74,74,73 77,76,77
stats_command_string=1; 105,99,106 78,79,78
log_min_duration_statement=100 79,80,81 75,80,76
statement_timeout=100 78,79,78 75,79,77
all 3 104,108,107 82,81,81
all values in seconds with 3 consecutive runs of one million "SELECT 1;"
queries. It takes about 48 seconds to run the same test without
stat-collection btw.
I'm confused. Isn't your first table row for the case of no stat collection?
Or do you mean that you have stats_row_level and/or stats_block_level on
in all four cases?
regards, tom lane
Tom Lane wrote:
Stefan Kaltenbrunner <stefan@kaltenbrunner.cc> writes:
This is what I get on a fast AMD Dual Opteron box(Running Debian
Sarge/AMD64):8.1.4 HEAD
1000000 SELECT 1; 74,74,73 77,76,77
stats_command_string=1; 105,99,106 78,79,78
log_min_duration_statement=100 79,80,81 75,80,76
statement_timeout=100 78,79,78 75,79,77
all 3 104,108,107 82,81,81all values in seconds with 3 consecutive runs of one million "SELECT 1;"
queries. It takes about 48 seconds to run the same test without
stat-collection btw.I'm confused. Isn't your first table row for the case of no stat collection?
Or do you mean that you have stats_row_level and/or stats_block_level on
in all four cases?
yes - stats_row_level and stats_block_level on in all cases (sorry for
the confusion) - I can easily redo the tests without those - but that's
what I had in the running conf and I only remember that after I was
nearly done with all the testing :-)
Stefan
Stefan Kaltenbrunner <stefan@kaltenbrunner.cc> writes:
Tom Lane wrote:
Or do you mean that you have stats_row_level and/or stats_block_level on
in all four cases?
yes - stats_row_level and stats_block_level on in all cases (sorry for
the confusion) - I can easily redo the tests without those - but that's
what I had in the running conf and I only remember that after I was
nearly done with all the testing :-)
It'd be interesting to compare 8.1 and HEAD for the no-overhead case;
I don't think you need to redo all four cases, but I'd like to see that one.
regards, tom lane
Tom Lane wrote:
Stefan Kaltenbrunner <stefan@kaltenbrunner.cc> writes:
Tom Lane wrote:
Or do you mean that you have stats_row_level and/or stats_block_level on
in all four cases?yes - stats_row_level and stats_block_level on in all cases (sorry for
the confusion) - I can easily redo the tests without those - but that's
what I had in the running conf and I only remember that after I was
nearly done with all the testing :-)It'd be interesting to compare 8.1 and HEAD for the no-overhead case;
I don't think you need to redo all four cases, but I'd like to see that one.
8.1: 50,50,49
HEAD: 49,48,49
Stefan
Stefan Kaltenbrunner <stefan@kaltenbrunner.cc> writes:
Tom Lane wrote:
It'd be interesting to compare 8.1 and HEAD for the no-overhead case;
I don't think you need to redo all four cases, but I'd like to see that one.
8.1: 50,50,49
HEAD: 49,48,49
OK, so that seems comparable to my results on a dual Xeon ... probably,
both your machine and my newer one have fast-to-read clock hardware.
We need to get some numbers from one of the people who have complained
about EXPLAIN ANALYZE overhead.
I'll have to try the stats-collection-active case on my machines, too.
I was still planning to look into removing the buffer process to reduce
context-swap overhead for stats collection ...
regards, tom lane
Tom Lane wrote:
Stefan Kaltenbrunner <stefan@kaltenbrunner.cc> writes:
Tom Lane wrote:
It'd be interesting to compare 8.1 and HEAD for the no-overhead case;
I don't think you need to redo all four cases, but I'd like to see that one.8.1: 50,50,49
HEAD: 49,48,49OK, so that seems comparable to my results on a dual Xeon ... probably,
both your machine and my newer one have fast-to-read clock hardware.
We need to get some numbers from one of the people who have complained
about EXPLAIN ANALYZE overhead.
I'm compiling here without the assert stuff. It takes a while ...
--
Alvaro Herrera http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.
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.
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.
regards, tom lane
Tom Lane wrote:
Stefan Kaltenbrunner <stefan@kaltenbrunner.cc> writes:
Tom Lane wrote:
It'd be interesting to compare 8.1 and HEAD for the no-overhead case;
I don't think you need to redo all four cases, but I'd like to see that one.8.1: 50,50,49
HEAD: 49,48,49OK, so that seems comparable to my results on a dual Xeon ... probably,
both your machine and my newer one have fast-to-read clock hardware.
We need to get some numbers from one of the people who have complained
about EXPLAIN ANALYZE overhead.
Data from two (identical) dual P-III, one running Linux and one running
Freebsd - both doing the 100000 SELECT 1 test:
Freebsd 6.1:
- 8.1 21.5 (median times)
- HEAD 22.2
Linux 2.6.16
- 8.1 16.1
- HEAD 17.2
The variation in run times seems to be up to 0.5 seconds, so I'm not
sure that I'm seeing a real difference between 8.1 and HEAD (though this
test seems to run noticeably slower on Freebsd - recall from my previous
posting featuring these boxes that EXPLAIN ANALYZE seems to have a
*much* higher overhead on Freebsd).
(8.1 is 8.1.3 on the Freebsd box and 8.1.4 on the linux one. HEAD is
from today).
regards
Mark
Tom Lane wrote:
I redid my previous measurements after finishing up the weekend's
hacking. The numbers shown below are elapsed time in seconds fortime psql -f testfile.sql postgres >/dev/null
Average of 5 runs, for the first two cases, on the x86 machine that
shows high overhead in gettimeofday.
I used only 30000 SELECT 1 queries instead of 100k.
30000 SELECT 1;
HEAD 8.1
no overhead 21.9 23.1
stats_command_string=1 22.4 36.6
BEGIN; 30000 SELECT 1; COMMIT;
HEAD 8.1
no overhead 19.1 20.3
stats_command_string=1 19.4 30.3
It can be observed that HEAD in the no overhead case is actually faster
than 8.1 on this machine. And while stats_command_string adds some
overhead, it still is faster than the no overhead case in 8.1. (These
results took me by surprise actually.)
For the curious, here are medians and averages for each run ("file3" is
the plain SELECT, while "file4" is BEGIN-30k * SELECT-COMMIT. "caso1"
is no overhead, "caso2" is stats_command_string=1). I couldn't find a
quick'n dirty way to calculate stddev in shell but if anyone knows one
let me know.
median average
8.1-file3-caso1: 23.098 23.145
8.1-file3-caso2: 36.595 36.607
8.1-file4-caso1: 20.304 20.269
8.1-file4-caso2: 30.169 30.256
head-file3-caso1: 21.890 21.931
head-file3-caso2: 22.509 22.390
head-file4-caso1: 19.142 19.126
head-file4-caso2: 19.488 19.442
--
Alvaro Herrera http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support
Alvaro Herrera <alvherre@commandprompt.com> writes:
Average of 5 runs, for the first two cases, on the x86 machine that
shows high overhead in gettimeofday.
I used only 30000 SELECT 1 queries instead of 100k.
30000 SELECT 1;
HEAD 8.1
no overhead 21.9 23.1
stats_command_string=1 22.4 36.6
BEGIN; 30000 SELECT 1; COMMIT;
HEAD 8.1
no overhead 19.1 20.3
stats_command_string=1 19.4 30.3
It can be observed that HEAD in the no overhead case is actually faster
than 8.1 on this machine.
That's more or less what I would have hoped to find, because we're
always finding ways to squeeze out bits of overhead here and there.
I wonder why your results are different from what I got on my older
machine? I'll have to break out oprofile again and try to see what's
happening there.
regards, tom lane
I ran your test with all defaults in 8.1 and CVS HEAD on a BSD/OS dual
Xeon and got:
8.1.X 1.946
HEAD 1.986
I ran the test ten times on three runs and took the middle value.
It is a slowdown of 2%. I used these configure options:
configure \
--with-tcl \
--with-perl \
--with-tclconfig=/u/lib \
--with-includes="/usr/local/include/readline /usr/contrib/include" \
--with-libraries="/usr/local/lib /usr/contrib/lib" \
--with-openssl \
--enable-thread-safety \
--enable-nls
---------------------------------------------------------------------------
Tom Lane wrote:
I redid my previous measurements after finishing up the weekend's
hacking. The numbers shown below are elapsed time in seconds fortime psql -f testfile.sql postgres >/dev/null
using CVS HEAD and REL8_1_STABLE branch tip, compiled --enable-debug
--disable-cassert, no nondefault options except for turning fsync off
(which doesn't particularly affect read-only tests like these anyway).
The machines are both running current Fedora Core 5. The older x86
machine is known to have the slow-gettimeofday problem from previous
experimentation with EXPLAIN ANALYZE. Each number is the median of 3
or more tests, rounded off to 0.1 second (I wouldn't put a lot of faith
in differences of 0.1 sec or so, because of the variance I saw in the
tests).x86 x86_64
8.1 HEAD 8.1 HEAD
100000 "SELECT 1;" 25.9 27.0 9.2 9.1
with stats_command_string=1 63.5 27.6 18.7 9.2
with log_min_duration_statement=100 26.9 27.8 9.6 9.2
with statement_timeout=100 27.5 28.6 9.6 9.8
with all 3 features 66.1 29.3 19.5 9.7BEGIN, 100000 "SELECT 1;", COMMIT 21.2 23.1 8.3 8.4
with stats_command_string=1 52.3 23.5 15.4 8.5
with log_min_duration_statement=100 22.1 23.4 8.4 8.4
with statement_timeout=100 23.7 24.3 8.6 8.8
with all 3 features 55.2 25.5 16.0 8.8I chose the log_min_duration_statement and statement_timeout settings
high enough so that no actual logging or timeout would happen --- the
point is to measure the measurement overhead.The good news is that we've pretty much licked the problem of
stats_command_string costing an unreasonable amount.The bad news is that except in the stats_command_string cases, HEAD
is noticeably slower than 8.1 on the machine with slow gettimeofday.
In the single-transaction test this might be blamed on the addition
of statement_timestamp support (which requires a gettimeofday per
statement that wasn't there in 8.1) ... but in the one-transaction-
per-statement tests that doesn't hold water, because each branch is
doing a gettimeofday per statement, just in different places.Can anyone else reproduce this slowdown? It might be only an artifact
of these particular builds, but it's a bit too consistent in my x86 data
to just ignore.BTW, according to "top" the CPU usage percentages in these tests are
on the order of 55% backend, 45% psql. Methinks psql needs a round
of performance tuning ...regards, tom lane
---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend
--
Bruce Momjian bruce@momjian.us
EnterpriseDB http://www.enterprisedb.com
+ If your life is a hard drive, Christ can be your backup. +
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. +
Attachments:
/pgpatches/stat.ontext/x-diffDownload
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
Bruce Momjian <bruce@momjian.us> writes:
Tom Lane wrote:
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?
IIRC, newer BSDen use a kernel call for this, so you should be able to
measure it on your own machine. Just tweak ps_status.c to force it to
select PS_USE_NONE instead of PS_USE_SETPROCTITLE to generate a
comparison case. I'll try it on my old HPUX box too.
regards, tom lane
I wrote:
IIRC, newer BSDen use a kernel call for this, so you should be able to
measure it on your own machine. Just tweak ps_status.c to force it to
select PS_USE_NONE instead of PS_USE_SETPROCTITLE to generate a
comparison case. I'll try it on my old HPUX box too.
On HPUX, I get a median time of 5.59 sec for CVS HEAD vs 5.36 sec with
ps_status diked out, for the test case of 10000 "SELECT 1;" as separate
transactions, assert-disabled build. So, almost 10% overhead. Given
that the transactions can't get any more trivial than this, that's about
a worst-case number. Not sure if it's worth worrying about or not.
However Kris Kennaway's report a couple weeks ago suggested things might
be worse on BSD.
regards, tom lane
Tom Lane wrote:
I wrote:
IIRC, newer BSDen use a kernel call for this, so you should be able to
measure it on your own machine. Just tweak ps_status.c to force it to
select PS_USE_NONE instead of PS_USE_SETPROCTITLE to generate a
comparison case. I'll try it on my old HPUX box too.On HPUX, I get a median time of 5.59 sec for CVS HEAD vs 5.36 sec with
ps_status diked out, for the test case of 10000 "SELECT 1;" as separate
transactions, assert-disabled build. So, almost 10% overhead. Given
that the transactions can't get any more trivial than this, that's about
a worst-case number. Not sure if it's worth worrying about or not.
However Kris Kennaway's report a couple weeks ago suggested things might
be worse on BSD.
Yep, I see 8% here. I will add a patch to allow the ps display to be
turned off.
--
Bruce Momjian bruce@momjian.us
EnterpriseDB http://www.enterprisedb.com
+ If your life is a hard drive, Christ can be your backup. +
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.
regards, tom lane
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.
Oh, good point. Do we remove stats_command_string? Does it have any
measurable overhead? I see a little here, like 1%.
--
Bruce Momjian bruce@momjian.us
EnterpriseDB http://www.enterprisedb.com
+ If your life is a hard drive, Christ can be your backup. +
Bruce Momjian <bruce@momjian.us> writes:
Oh, good point. Do we remove stats_command_string?
You mean, remove the option to turn it off? I don't think so. Aside
from whatever remaining overhead there is, there's a possible security
argument to be made that one might not want one's commands exposed,
even to other sessions with the same userid.
regards, tom lane
Tom Lane wrote:
Bruce Momjian <bruce@momjian.us> writes:
Oh, good point. Do we remove stats_command_string?
You mean, remove the option to turn it off? I don't think so. Aside
from whatever remaining overhead there is, there's a possible security
argument to be made that one might not want one's commands exposed,
even to other sessions with the same userid.
OK.
--
Bruce Momjian bruce@momjian.us
EnterpriseDB http://www.enterprisedb.com
+ If your life is a hard drive, Christ can be your backup. +
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. +
Attachments:
/pgpatches/no_titletext/x-diffDownload
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,
Bruce Momjian <bruce@momjian.us> writes:
Attached patch adds GUC 'update_process_title' to control ps display
updates per SQL command. Default to 'on'. GUC name OK?
This is an ugly patch. Why not *one* test of the GUC variable, inside
set_ps_display(), and no side-effects on callers? You would need to
force an initial update from init_ps_display, but that only requires a
small amount of code refactoring inside ps_status.c.
The one place that might be worth having an external test on the GUC is
in lock.c, but then it should bypass the entire business of copying,
modifying, and restoring the title ... not just the two set_ps_display
calls.
regards, tom lane
Tom Lane wrote:
Bruce Momjian <bruce@momjian.us> writes:
Attached patch adds GUC 'update_process_title' to control ps display
updates per SQL command. Default to 'on'. GUC name OK?This is an ugly patch. Why not *one* test of the GUC variable, inside
set_ps_display(), and no side-effects on callers? You would need to
force an initial update from init_ps_display, but that only requires a
small amount of code refactoring inside ps_status.c.
Consider all the helper processes that set their process title. The
only thing I can think of is to add a boolean to set_ps_display() so say
whether this is per-command set or not. Is that your idea?
The one place that might be worth having an external test on the GUC is
in lock.c, but then it should bypass the entire business of copying,
modifying, and restoring the title ... not just the two set_ps_display
calls.
OK, that makes sense.
--
Bruce Momjian bruce@momjian.us
EnterpriseDB http://www.enterprisedb.com
+ If your life is a hard drive, Christ can be your backup. +
Bruce Momjian <bruce@momjian.us> writes:
Tom Lane wrote:
This is an ugly patch. Why not *one* test of the GUC variable, inside
set_ps_display(), and no side-effects on callers? You would need to
force an initial update from init_ps_display, but that only requires a
small amount of code refactoring inside ps_status.c.
Consider all the helper processes that set their process title. The
only thing I can think of is to add a boolean to set_ps_display() so say
whether this is per-command set or not. Is that your idea?
No, that's not what I said at all. Currently init_ps_display doesn't
actually force the display to update; it's left to the first
set_ps_display call to do that. If we made init_ps_display update the
status unconditionally, then set_ps_display could be a conditional
no-op, and in the helper process setup code
/* Identify myself via ps */
init_ps_display("autovacuum process", "", "");
set_ps_display("");
we could remove the now-unnecessary set_ps_display("") calls, but
the other set_ps_display() calls would stay exactly like they are.
regards, tom lane
Tom Lane wrote:
Bruce Momjian <bruce@momjian.us> writes:
Tom Lane wrote:
This is an ugly patch. Why not *one* test of the GUC variable, inside
set_ps_display(), and no side-effects on callers? You would need to
force an initial update from init_ps_display, but that only requires a
small amount of code refactoring inside ps_status.c.Consider all the helper processes that set their process title. The
only thing I can think of is to add a boolean to set_ps_display() so say
whether this is per-command set or not. Is that your idea?No, that's not what I said at all. Currently init_ps_display doesn't
actually force the display to update; it's left to the first
set_ps_display call to do that. If we made init_ps_display update the
status unconditionally, then set_ps_display could be a conditional
no-op, and in the helper process setup code/* Identify myself via ps */
init_ps_display("autovacuum process", "", "");
set_ps_display("");we could remove the now-unnecessary set_ps_display("") calls, but
the other set_ps_display() calls would stay exactly like they are.
Yea, I figured that out the merge idea after I replied.
If you put a contition test in set_ps_display(), the only clean way to
do this is for init_ps_display() to force update_process_title to true
before we call set_ps_display(), then reset it to its original value,
but that sounds pretty ugly. Do we create another function that
unconditionally sets the title, and conditionally call that from the
set_ps_display()? These seem uglier than the if() test. Or add a
'force' parameter.
--
Bruce Momjian bruce@momjian.us
EnterpriseDB http://www.enterprisedb.com
+ If your life is a hard drive, Christ can be your backup. +
Bruce Momjian <bruce@momjian.us> writes:
If you put a contition test in set_ps_display(), the only clean way to
do this is for init_ps_display() to force update_process_title to true
before we call set_ps_display(), then reset it to its original value,
but that sounds pretty ugly.
No, refactor the code. I was envisioning something called maybe
transmit_ps_display that would contain the part of set_ps_display
beginning at "Transmit new setting to kernel". Then both set_ps_display
and init_ps_display would call that.
regards, tom lane
Tom Lane wrote:
Bruce Momjian <bruce@momjian.us> writes:
If you put a contition test in set_ps_display(), the only clean way to
do this is for init_ps_display() to force update_process_title to true
before we call set_ps_display(), then reset it to its original value,
but that sounds pretty ugly.No, refactor the code. I was envisioning something called maybe
transmit_ps_display that would contain the part of set_ps_display
beginning at "Transmit new setting to kernel". Then both set_ps_display
and init_ps_display would call that.
I went with a 'force' boolean for set_ps_display().
--
Bruce Momjian bruce@momjian.us
EnterpriseDB http://www.enterprisedb.com
+ If your life is a hard drive, Christ can be your backup. +
Attachments:
/pgpatches/no_titletext/x-diffDownload
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 23:20:43 -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/bootstrap/bootstrap.c
===================================================================
RCS file: /cvsroot/pgsql/src/backend/bootstrap/bootstrap.c,v
retrieving revision 1.217
diff -c -c -r1.217 bootstrap.c
*** src/backend/bootstrap/bootstrap.c 18 Jun 2006 15:38:36 -0000 1.217
--- src/backend/bootstrap/bootstrap.c 26 Jun 2006 23:20:43 -0000
***************
*** 353,360 ****
statmsg = "??? process";
break;
}
! init_ps_display(statmsg, "", "");
! set_ps_display("");
}
/* Acquire configuration parameters, unless inherited from postmaster */
--- 353,359 ----
statmsg = "??? process";
break;
}
! init_ps_display(statmsg, "", "", "");
}
/* Acquire configuration parameters, unless inherited from postmaster */
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 23:20:44 -0000
***************
*** 908,914 ****
if (Trace_notify)
elog(DEBUG1, "ProcessIncomingNotify");
! set_ps_display("notify interrupt");
notifyInterruptOccurred = 0;
--- 908,914 ----
if (Trace_notify)
elog(DEBUG1, "ProcessIncomingNotify");
! set_ps_display("notify interrupt", false);
notifyInterruptOccurred = 0;
***************
*** 979,985 ****
*/
pq_flush();
! set_ps_display("idle");
if (Trace_notify)
elog(DEBUG1, "ProcessIncomingNotify: done");
--- 979,985 ----
*/
pq_flush();
! set_ps_display("idle", false);
if (Trace_notify)
elog(DEBUG1, "ProcessIncomingNotify: done");
Index: src/backend/postmaster/autovacuum.c
===================================================================
RCS file: /cvsroot/pgsql/src/backend/postmaster/autovacuum.c,v
retrieving revision 1.20
diff -c -c -r1.20 autovacuum.c
*** src/backend/postmaster/autovacuum.c 18 Jun 2006 15:38:37 -0000 1.20
--- src/backend/postmaster/autovacuum.c 26 Jun 2006 23:20:44 -0000
***************
*** 239,246 ****
MyProcPid = getpid();
/* Identify myself via ps */
! init_ps_display("autovacuum process", "", "");
! set_ps_display("");
SetProcessingMode(InitProcessing);
--- 239,245 ----
MyProcPid = getpid();
/* Identify myself via ps */
! init_ps_display("autovacuum process", "", "", "");
SetProcessingMode(InitProcessing);
***************
*** 416,422 ****
*/
InitPostgres(db->name, NULL);
SetProcessingMode(NormalProcessing);
! set_ps_display(db->name);
ereport(DEBUG1,
(errmsg("autovacuum: processing database \"%s\"", db->name)));
--- 415,421 ----
*/
InitPostgres(db->name, NULL);
SetProcessingMode(NormalProcessing);
! set_ps_display(db->name, false);
ereport(DEBUG1,
(errmsg("autovacuum: processing database \"%s\"", db->name)));
Index: src/backend/postmaster/pgarch.c
===================================================================
RCS file: /cvsroot/pgsql/src/backend/postmaster/pgarch.c,v
retrieving revision 1.23
diff -c -c -r1.23 pgarch.c
*** src/backend/postmaster/pgarch.c 18 Jun 2006 15:38:37 -0000 1.23
--- src/backend/postmaster/pgarch.c 26 Jun 2006 23:20:44 -0000
***************
*** 244,251 ****
/*
* Identify myself via ps
*/
! init_ps_display("archiver process", "", "");
! set_ps_display("");
pgarch_MainLoop();
--- 244,250 ----
/*
* Identify myself via ps
*/
! init_ps_display("archiver process", "", "", "");
pgarch_MainLoop();
Index: src/backend/postmaster/pgstat.c
===================================================================
RCS file: /cvsroot/pgsql/src/backend/postmaster/pgstat.c,v
retrieving revision 1.130
diff -c -c -r1.130 pgstat.c
*** src/backend/postmaster/pgstat.c 20 Jun 2006 22:52:00 -0000 1.130
--- src/backend/postmaster/pgstat.c 26 Jun 2006 23:20:46 -0000
***************
*** 1743,1750 ****
/*
* Identify myself via ps
*/
! init_ps_display("stats collector process", "", "");
! set_ps_display("");
/*
* Arrange to write the initial status file right away
--- 1743,1749 ----
/*
* Identify myself via ps
*/
! init_ps_display("stats collector process", "", "", "");
/*
* Arrange to write the initial status file right away
***************
*** 1975,1982 ****
/*
* Identify myself via ps
*/
! init_ps_display("stats buffer process", "", "");
! set_ps_display("");
/*
* We want to die if our child collector process does. There are two ways
--- 1974,1980 ----
/*
* Identify myself via ps
*/
! init_ps_display("stats buffer process", "", "", "");
/*
* We want to die if our child collector process does. There are two ways
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 23:20:48 -0000
***************
*** 2713,2721 ****
* Now that we have the user and database name, we can set the process
* 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.
*/
--- 2713,2721 ----
* Now that we have the user and database name, we can set the process
* 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,
! update_process_title ? "authentication" : "");
!
/*
* Now perform authentication exchange.
*/
Index: src/backend/postmaster/syslogger.c
===================================================================
RCS file: /cvsroot/pgsql/src/backend/postmaster/syslogger.c,v
retrieving revision 1.25
diff -c -c -r1.25 syslogger.c
*** src/backend/postmaster/syslogger.c 18 Jun 2006 15:38:37 -0000 1.25
--- src/backend/postmaster/syslogger.c 26 Jun 2006 23:20:48 -0000
***************
*** 141,148 ****
am_syslogger = true;
! init_ps_display("logger process", "", "");
! set_ps_display("");
/*
* If we restarted, our stderr is already redirected into our own input
--- 141,147 ----
am_syslogger = true;
! init_ps_display("logger process", "", "", "");
/*
* If we restarted, our stderr is already redirected into our own input
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 23:20:49 -0000
***************
*** 1059,1077 ****
LOCKMETHODID lockmethodid = LOCALLOCK_LOCKMETHOD(*locallock);
LockMethod lockMethodTable = LockMethods[lockmethodid];
const char *old_status;
! char *new_status;
int len;
LOCK_PRINT("WaitOnLock: sleeping on lock",
locallock->lock, locallock->tag.mode);
! old_status = get_ps_display(&len);
! 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;
awaitedOwner = owner;
--- 1059,1080 ----
LOCKMETHODID lockmethodid = LOCALLOCK_LOCKMETHOD(*locallock);
LockMethod lockMethodTable = LockMethods[lockmethodid];
const char *old_status;
! char *new_status = NULL;
int len;
LOCK_PRINT("WaitOnLock: sleeping on lock",
locallock->lock, locallock->tag.mode);
! if (update_process_title)
! {
! old_status = get_ps_display(&len);
! new_status = (char *) palloc(len + 8 + 1);
! memcpy(new_status, old_status, len);
! strcpy(new_status + len, " waiting");
! set_ps_display(new_status, false);
! new_status[len] = '\0'; /* truncate off " waiting" */
! }
!
awaitedLock = locallock;
awaitedOwner = owner;
***************
*** 1108,1115 ****
awaitedLock = NULL;
! set_ps_display(new_status);
! pfree(new_status);
LOCK_PRINT("WaitOnLock: wakeup on lock",
locallock->lock, locallock->tag.mode);
--- 1111,1121 ----
awaitedLock = NULL;
! if (update_process_title)
! {
! set_ps_display(new_status, false);
! pfree(new_status);
! }
LOCK_PRINT("WaitOnLock: wakeup on lock",
locallock->lock, locallock->tag.mode);
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 23:20:50 -0000
***************
*** 910,916 ****
*/
commandTag = CreateCommandTag(parsetree);
! set_ps_display(commandTag);
BeginCommand(commandTag, dest);
--- 910,916 ----
*/
commandTag = CreateCommandTag(parsetree);
! set_ps_display(commandTag, false);
BeginCommand(commandTag, dest);
***************
*** 1144,1150 ****
pgstat_report_activity(query_string);
! set_ps_display("PARSE");
if (save_log_statement_stats)
ResetUsage();
--- 1144,1150 ----
pgstat_report_activity(query_string);
! set_ps_display("PARSE", false);
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
--- 1376,1382 ----
pgstat_report_activity("<BIND>");
! set_ps_display("BIND", false);
/*
* 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
--- 1711,1717 ----
pgstat_report_activity("<EXECUTE>");
}
! set_ps_display(portal->commandTag, false);
/*
* We use save_log_statement_stats so ShowUsage doesn't report incorrect
***************
*** 2486,2492 ****
if (!IsUnderPostmaster)
MemoryContextInit();
! set_ps_display("startup");
SetProcessingMode(InitProcessing);
--- 2486,2492 ----
if (!IsUnderPostmaster)
MemoryContextInit();
! set_ps_display("startup", false);
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>");
}
--- 3121,3134 ----
{
if (IsTransactionOrTransactionBlock())
{
! set_ps_display("idle in transaction", false);
pgstat_report_activity("<IDLE> in transaction");
}
else
{
pgstat_report_tabstat();
! set_ps_display("idle", false);
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 23:20:52 -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 23:20:53 -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 23:20:53 -0000
***************
*** 31,36 ****
--- 31,37 ----
#include "utils/ps_status.h"
extern char **environ;
+ bool update_process_title = true;
/*
***************
*** 210,216 ****
*/
void
init_ps_display(const char *username, const char *dbname,
! const char *host_info)
{
Assert(username);
Assert(dbname);
--- 211,217 ----
*/
void
init_ps_display(const char *username, const char *dbname,
! const char *host_info, const char *initial_str)
{
Assert(username);
Assert(dbname);
***************
*** 270,275 ****
--- 271,277 ----
ps_buffer_fixed_size = strlen(ps_buffer);
+ set_ps_display(initial_str, true);
#endif /* not PS_USE_NONE */
}
***************
*** 280,287 ****
* indication of what you're currently doing passed in the argument.
*/
void
! set_ps_display(const char *activity)
{
#ifndef PS_USE_NONE
/* no ps display for stand-alone backend */
if (!IsUnderPostmaster)
--- 282,293 ----
* indication of what you're currently doing passed in the argument.
*/
void
! set_ps_display(const char *activity, bool force)
{
+
+ if (!force && !update_process_title)
+ return;
+
#ifndef PS_USE_NONE
/* no ps display for stand-alone backend */
if (!IsUnderPostmaster)
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 23:20:55 -0000
***************
*** 12,23 ****
#ifndef PS_STATUS_H
#define PS_STATUS_H
extern char **save_ps_display_args(int argc, char **argv);
extern void init_ps_display(const char *username, const char *dbname,
! const char *host_info);
! extern void set_ps_display(const char *activity);
extern const char *get_ps_display(int *displen);
--- 12,25 ----
#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,
! const char *host_info, const char *initial_str);
! extern void set_ps_display(const char *activity, bool force);
extern const char *get_ps_display(int *displen);
Applied.
---------------------------------------------------------------------------
Bruce Momjian wrote:
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. +
---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly
--
Bruce Momjian bruce@momjian.us
EnterpriseDB http://www.enterprisedb.com
+ If your life is a hard drive, Christ can be your backup. +
Patch applied.
---------------------------------------------------------------------------
Bruce Momjian wrote:
Tom Lane wrote:
Bruce Momjian <bruce@momjian.us> writes:
If you put a contition test in set_ps_display(), the only clean way to
do this is for init_ps_display() to force update_process_title to true
before we call set_ps_display(), then reset it to its original value,
but that sounds pretty ugly.No, refactor the code. I was envisioning something called maybe
transmit_ps_display that would contain the part of set_ps_display
beginning at "Transmit new setting to kernel". Then both set_ps_display
and init_ps_display would call that.I went with a 'force' boolean for set_ps_display().
--
Bruce Momjian bruce@momjian.us
EnterpriseDB http://www.enterprisedb.com+ If your life is a hard drive, Christ can be your backup. +
---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings
--
Bruce Momjian bruce@momjian.us
EnterpriseDB http://www.enterprisedb.com
+ If your life is a hard drive, Christ can be your backup. +