
One of the functions of the DBA is to monitor what people are doing to
the database.  My experience is that "ps" is often sorely lacking in
this regard: its output is somewhat limited, from what I've seen, and
in any case the DBA's domain is the database itself: that's the
environment he's going to be most proficient in.

The ability to select the list of current connections from
pg_stat_activity is really handy for monitoring the database, but the
default configuration disables stats_command_string -- so the
current_query winds up being blank by default.

That's not exactly the most useful configuration for the DBA.

Would it make more sense to enable stats_command_string by default?
It could be a problem if doing so would have a significant impact on
performance, but that's the only reason I can think of for not doing
it.  Are there others?


It would also be handy if users could see their own queries while the
rest remain blank.  That would require changing
pg_stat_get_backend_activity() so that it returns a value if the user
is the superuser or if the user asking for the answer is the same as
the user who owns the backend entry being looked up.  Are there any
pitfalls to implementing that?




-- 
Kevin Brown					      kevin@sysexperts.com

Fri, 14 Feb 2003 11:17:53 -0800
Kevin Brown <kevin@sysexperts.com>
stats_command_string default?
Kevin Brown <kevin@sysexperts.com> writes:
> Would it make more sense to enable stats_command_string by default?

I'd vote against it.  If we turn it on by default, people are paying
for a feature they may not even know exists.  Once they find out about
it and decide they want it, they can turn it on easily enough.

If you can show that the overhead is unmeasurable, that'd indicate that
this argument is bogus; but I suspect it's not negligible, at least on
simple queries.

> It would also be handy if users could see their own queries while the
> rest remain blank.  That would require changing
> pg_stat_get_backend_activity() so that it returns a value if the user
> is the superuser or if the user asking for the answer is the same as
> the user who owns the backend entry being looked up.  Are there any
> pitfalls to implementing that?

Seems reasonable offhand ...

			regards, tom lane

Fri, 14 Feb 2003 16:51:14 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: stats_command_string default? 
You can just turn it on in one second in your postgresql.conf file...

Chris

On Fri, 14 Feb 2003, Kevin Brown wrote:

>
> One of the functions of the DBA is to monitor what people are doing to
> the database.  My experience is that "ps" is often sorely lacking in
> this regard: its output is somewhat limited, from what I've seen, and
> in any case the DBA's domain is the database itself: that's the
> environment he's going to be most proficient in.
>
> The ability to select the list of current connections from
> pg_stat_activity is really handy for monitoring the database, but the
> default configuration disables stats_command_string -- so the
> current_query winds up being blank by default.
>
> That's not exactly the most useful configuration for the DBA.
>
> Would it make more sense to enable stats_command_string by default?
> It could be a problem if doing so would have a significant impact on
> performance, but that's the only reason I can think of for not doing
> it.  Are there others?
>
>
> It would also be handy if users could see their own queries while the
> rest remain blank.  That would require changing
> pg_stat_get_backend_activity() so that it returns a value if the user
> is the superuser or if the user asking for the answer is the same as
> the user who owns the backend entry being looked up.  Are there any
> pitfalls to implementing that?
>
>
>
>
> --
> Kevin Brown					      kevin@sysexperts.com
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
>


Sat, 15 Feb 2003 22:23:38 +0800 (WST)
Christopher Kings-Lynne <chriskl@familyhealth.com.au>
Re: stats_command_string default?
I wrote:

> > Would it make more sense to enable stats_command_string by default?
> > It could be a problem if doing so would have a significant impact on
> > performance, but that's the only reason I can think of for not doing
> > it.  Are there others?

and Christopher Kings-Lynne responded:

> You can just turn it on in one second in your postgresql.conf
> file...

Sure.  But my initial perusal of the documentation didn't reveal that
option (once I knew the name of the option to enable, I knew better
where to look in the documentation, but by that time it was too late).
I ended up figuring it out by (a) hearing that it was possible, (b)
looking at the definition of pg_stat_activity, and (c) looking through
the source code to find out how the pg_stat_get_backend_activity()
function worked, and from there how to make it display something.

Since we've been talking about fixing the defaults, it seems to me
a good opportunity to address little things like this as well.  It
doesn't cost us anything and it'll make someone's life easier.

It seems to me that it makes the most sense for the defaults to be
whatever is the most useful for the most people.  I can see lots of
use in enabling stats_command_string by default and little use in
disabling it by default.  It seems to me that most DBAs will want it
turned on unless there's a big performance loss as a result.

That's why I asked the question: is there a really good reason that
most DBAs would want it disabled?  My sense is that there isn't, but I
don't know, which is why I'm asking.


-- 
Kevin Brown					      kevin@sysexperts.com

Sun, 16 Feb 2003 00:19:21 -0800
Kevin Brown <kevin@sysexperts.com>
Re: stats_command_string default?
Tom Lane wrote:
> Kevin Brown <kevin@sysexperts.com> writes:
> > Would it make more sense to enable stats_command_string by default?
> 
> I'd vote against it.  If we turn it on by default, people are paying
> for a feature they may not even know exists.  Once they find out about
> it and decide they want it, they can turn it on easily enough.
> 
> If you can show that the overhead is unmeasurable, that'd indicate that
> this argument is bogus; but I suspect it's not negligible, at least on
> simple queries.

It's not unmeasurable, but it is reasonably low (guess it depends on
your definition of "reasonable" :-).  I wrote a small perl script
which would do a "SELECT 1" in a loop as many times as I specified on
the command line (autocommit was turned off).  I measured the amount
of wall clock time it took to do 100000 passes on an unloaded system
with stats_command_string enabled, and then the same thing with it
disabled.

The difference in time over 100000 passes was 20 seconds (44 seconds
with stats_command_string turned on, 24 with it turned off), for an
impact of 0.2 milliseconds per command executed.  This was on a 1.5GHz
P4 with 1G of RAM running Linux 2.4.20 on ReiserFS.  The data is
stored on a software RAID-5 across 3 Seagate ST-380021A IDE drives,
each connected to a separate channel on a Promise ATA100 card.

I have no idea if that's small enough to be considered negligible or
not, considering the hardware it was running on.



-- 
Kevin Brown					      kevin@sysexperts.com

Sun, 16 Feb 2003 00:45:22 -0800
Kevin Brown <kevin@sysexperts.com>
Re: stats_command_string default?
Kevin Brown <kevin@sysexperts.com> writes:
> Tom Lane wrote:
>> Kevin Brown <kevin@sysexperts.com> writes:
>>> Would it make more sense to enable stats_command_string by default?
>> 
>> I'd vote against it.  If we turn it on by default, people are paying
>> for a feature they may not even know exists.  Once they find out about
>> it and decide they want it, they can turn it on easily enough.


> The difference in time over 100000 passes was 20 seconds (44 seconds
> with stats_command_string turned on, 24 with it turned off), for an
> impact of 0.2 milliseconds per command executed.

In other words, more than an eighty percent penalty on simple commands.
Not negligible in my book.

> I have no idea if that's small enough to be considered negligible or
> not, considering the hardware it was running on.

I would imagine that the CPU-time ratio would not depend all that much
on the particular hardware.

			regards, tom lane

Sun, 16 Feb 2003 14:26:24 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: stats_command_string default? 
Kevin Brown <kevin@sysexperts.com> writes:
> It's not unmeasurable, but it is reasonably low (guess it depends on
> your definition of "reasonable" :-).  I wrote a small perl script
> which would do a "SELECT 1" in a loop as many times as I specified on
> the command line (autocommit was turned off).  I measured the amount
> of wall clock time it took to do 100000 passes on an unloaded system
> with stats_command_string enabled, and then the same thing with it
> disabled.

FWIW, I did a comparable test using a slightly more reasonable example
(ie, a query that does useful work): in the regression database,
	PREPARE q(int) AS SELECT * FROM tenk1 WHERE unique1 = $1
followed by 10000 executions of
	EXECUTE q(42)
This was with autocommit on (ie, each EXECUTE is its own transaction)
and using a C-coded client (a small tweak of src/test/examples/testlibpq.c).

Averaging over three trials on an unloaded system, I got 21.0 seconds
with stats_command_string off, 27.7 with it on, or about 32% overhead.

My conclusion is that stats_command_string overhead is non-negligible
for simple commands.  So I stand by my previous opinion that it should
not be turned on without the DBA taking explicit action to turn it on.
Do you want it on in every future benchmark, for example?

			regards, tom lane

Sun, 16 Feb 2003 15:55:57 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: stats_command_string default? 
> Averaging over three trials on an unloaded system, I got 21.0 seconds
> with stats_command_string off, 27.7 with it on, or about 32% overhead.
>
> My conclusion is that stats_command_string overhead is non-negligible
> for simple commands.  So I stand by my previous opinion that it should
> not be turned on without the DBA taking explicit action to turn it on.
> Do you want it on in every future benchmark, for example?

How about with the stats_collector on?  ie. Recording block and row level
stats?

Chris


Mon, 17 Feb 2003 09:32:04 +0800
"Christopher Kings-Lynne" <chriskl@familyhealth.com.au>
Re: stats_command_string default? 
"Christopher Kings-Lynne" <chriskl@familyhealth.com.au> writes:
>> My conclusion is that stats_command_string overhead is non-negligible
>> for simple commands.  So I stand by my previous opinion that it should
>> not be turned on without the DBA taking explicit action to turn it on.

> How about with the stats_collector on?  ie. Recording block and row level
> stats?

Didn't measure that, but I believe the block/row stats are dumped to the
collector once per transaction, so the overhead ought to be roughly
comparable to this test.

			regards, tom lane

Sun, 16 Feb 2003 21:30:06 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: stats_command_string default? 
Tom Lane wrote:
> Kevin Brown <kevin@sysexperts.com> writes:
> > Tom Lane wrote:
> >> Kevin Brown <kevin@sysexperts.com> writes:
> >>> Would it make more sense to enable stats_command_string by default?
> >> 
> >> I'd vote against it.  If we turn it on by default, people are paying
> >> for a feature they may not even know exists.  Once they find out about
> >> it and decide they want it, they can turn it on easily enough.
> 
> 
> > The difference in time over 100000 passes was 20 seconds (44 seconds
> > with stats_command_string turned on, 24 with it turned off), for an
> > impact of 0.2 milliseconds per command executed.
> 
> In other words, more than an eighty percent penalty on simple commands.
> Not negligible in my book.

Guess that's true when looked at that way.  :-)

Then again, that's the worst possible case: a simple command that only
invokes the parser and executor, doesn't reference any tables, doesn't
call any functions, and doesn't even write to anything.  As a
percentage of the work actually done on real systems, how often are
such commands executed?

In any case, it *does* show that there is a very high penalty for the
option relative to the operations that should be much more complex,
like parsing the command.  Why in the world is the penalty so high?  I
thought it would be a simple matter of copying the command to an
element of a structure that's overlaid onto a bit of shared memory
allocated to the backend process for its statistics.  In short, a
simple memory to memory copy, with perhaps the acquisition of a write
lock on the structure.  I'd expect such an operation to take a few
microseconds at most (especially on the kind of hardware I was testing
on), but it's orders of magnitude worse.  I have trouble believing
that the locking protocols required for this operation are that
inefficient unless we're doing something drastically wrong on that
front, and it's almost impossible for me to believe that the simple
operation of copying data to a shared memory segment would be that
inefficient.



-- 
Kevin Brown					      kevin@sysexperts.com

Sun, 16 Feb 2003 19:54:07 -0800
Kevin Brown <kevin@sysexperts.com>
Re: stats_command_string default?
Tom Lane wrote:
> Kevin Brown <kevin@sysexperts.com> writes:

[...]

> > It would also be handy if users could see their own queries while the
> > rest remain blank.  That would require changing
> > pg_stat_get_backend_activity() so that it returns a value if the user
> > is the superuser or if the user asking for the answer is the same as
> > the user who owns the backend entry being looked up.  Are there any
> > pitfalls to implementing that?
> 
> Seems reasonable offhand ...

Here's the patch to make this happen.  The first is against 7.2.4, the
second is against CVS tip.


If I got it wrong and need to use something other than GetUserId(),
please let me know.



-- 
Kevin Brown					      kevin@sysexperts.com
Thu, 6 Mar 2003 20:10:31 -0800
Kevin Brown <kevin@sysexperts.com>
Re: stats_command_string default?
Kevin Brown <kevin@sysexperts.com> writes:
>>> It would also be handy if users could see their own queries while the
>>> rest remain blank.
>> 
>> Seems reasonable offhand ...

> Here's the patch to make this happen.  The first is against 7.2.4, the
> second is against CVS tip.

You forgot documentation fixes.  Certainly the entry in
http://www.ca.postgresql.org/users-lounge/docs/7.3/postgres/monitoring-stats.html#MONITORING-STATS-VIEWS
needs to change; not sure if there are any other places, but that's your
task to look...

Also, submitting a patch against two revs back is a waste of time.  Only
*very* critical bug fixes would get applied against 7.2.* at this point.
This isn't likely to get into 7.3.* either.

The code itself looks fine though ;-)

			regards, tom lane

Fri, 07 Mar 2003 00:07:56 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: stats_command_string default? 
Tom Lane wrote:
> Kevin Brown <kevin@sysexperts.com> writes:
> >>> It would also be handy if users could see their own queries while the
> >>> rest remain blank.
> >> 
> >> Seems reasonable offhand ...
> 
> > Here's the patch to make this happen.  The first is against 7.2.4, the
> > second is against CVS tip.
> 
> You forgot documentation fixes.  Certainly the entry in
> http://www.ca.postgresql.org/users-lounge/docs/7.3/postgres/monitoring-stats.html#MONITORING-STATS-VIEWS
> needs to change; not sure if there are any other places, but that's your
> task to look...

Ooops.  Okay, the attached patch attached fixes that as well.  The
only references to pg_stat_get_backend_activity(), pg_stat_activity,
or stats_command_string that needed to be changed were those in
doc/src/monitoring.sgml.  If I missed something let me know.

So: the following patch fixes doc/src/monitoring.sgml and
src/backend/utils/adt/pgstatfuncs.c to make it possible to see your
own queries when looking at pg_stat_activity or when using the
pg_stat_get_backend_activity function.  The patch is against the
current (as of now :-) CVS tip.

-- 
Kevin Brown					      kevin@sysexperts.com
Thu, 6 Mar 2003 23:07:52 -0800
Kevin Brown <kevin@sysexperts.com>
Re: stats_command_string default?

Your patch has been added to the PostgreSQL unapplied patches list at:

	http://momjian.postgresql.org/cgi-bin/pgpatches

I will try to apply it within the next 48 hours.

---------------------------------------------------------------------------


Kevin Brown wrote:
> Tom Lane wrote:
> > Kevin Brown <kevin@sysexperts.com> writes:
> > >>> It would also be handy if users could see their own queries while the
> > >>> rest remain blank.
> > >> 
> > >> Seems reasonable offhand ...
> > 
> > > Here's the patch to make this happen.  The first is against 7.2.4, the
> > > second is against CVS tip.
> > 
> > You forgot documentation fixes.  Certainly the entry in
> > http://www.ca.postgresql.org/users-lounge/docs/7.3/postgres/monitoring-stats.html#MONITORING-STATS-VIEWS
> > needs to change; not sure if there are any other places, but that's your
> > task to look...
> 
> Ooops.  Okay, the attached patch attached fixes that as well.  The
> only references to pg_stat_get_backend_activity(), pg_stat_activity,
> or stats_command_string that needed to be changed were those in
> doc/src/monitoring.sgml.  If I missed something let me know.
> 
> So: the following patch fixes doc/src/monitoring.sgml and
> src/backend/utils/adt/pgstatfuncs.c to make it possible to see your
> own queries when looking at pg_stat_activity or when using the
> pg_stat_get_backend_activity function.  The patch is against the
> current (as of now :-) CVS tip.
> 
> -- 
> Kevin Brown					      kevin@sysexperts.com

[ Attachment, skipping... ]

> 
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
> 
> http://www.postgresql.org/docs/faqs/FAQ.html

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Mon, 17 Mar 2003 20:34:44 -0500 (EST)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: stats_command_string default?

Patch applied.  Thanks.

I merge the SGML in around recent additions.

---------------------------------------------------------------------------



Kevin Brown wrote:
> Tom Lane wrote:
> > Kevin Brown <kevin@sysexperts.com> writes:
> > >>> It would also be handy if users could see their own queries while the
> > >>> rest remain blank.
> > >> 
> > >> Seems reasonable offhand ...
> > 
> > > Here's the patch to make this happen.  The first is against 7.2.4, the
> > > second is against CVS tip.
> > 
> > You forgot documentation fixes.  Certainly the entry in
> > http://www.ca.postgresql.org/users-lounge/docs/7.3/postgres/monitoring-stats.html#MONITORING-STATS-VIEWS
> > needs to change; not sure if there are any other places, but that's your
> > task to look...
> 
> Ooops.  Okay, the attached patch attached fixes that as well.  The
> only references to pg_stat_get_backend_activity(), pg_stat_activity,
> or stats_command_string that needed to be changed were those in
> doc/src/monitoring.sgml.  If I missed something let me know.
> 
> So: the following patch fixes doc/src/monitoring.sgml and
> src/backend/utils/adt/pgstatfuncs.c to make it possible to see your
> own queries when looking at pg_stat_activity or when using the
> pg_stat_get_backend_activity function.  The patch is against the
> current (as of now :-) CVS tip.
> 
> -- 
> Kevin Brown					      kevin@sysexperts.com

[ Attachment, skipping... ]

> 
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
> 
> http://www.postgresql.org/docs/faqs/FAQ.html

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Thu, 20 Mar 2003 13:51:21 -0500 (EST)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: stats_command_string default?