Thread: stats_command_string default?
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
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
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 >
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
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
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
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 ofEXECUTE 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
> 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
"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
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
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
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
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
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
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