wishlist: dynamic log volume control
We extract great value from logging every SQL statement, its duration, etc.,
in service to debugging, troubleshooting, monitoring, etc. But we have a
number of clusters that seem to always be bumping into disk I/O
bottlenecks. With a system running at 100 QPS, we're inclined to quiet the
logging and only turn it up as needed (which will, by definition, be while
it was quieted). But many of these production clusters are not easily
restarted; the customer has to be notified, etc. "It'd be nice" if we
could adjust the volume of logging dynamically (i.e., turn-off of statement
logging, etc) without have to restart the server...
On Wed, Feb 18, 2004 at 03:54:39PM -0700, Ed L. wrote:
We extract great value from logging every SQL statement, its duration, etc.,
in service to debugging, troubleshooting, monitoring, etc. But we have a
number of clusters that seem to always be bumping into disk I/O
bottlenecks. With a system running at 100 QPS, we're inclined to quiet the
logging and only turn it up as needed (which will, by definition, be while
it was quieted). But many of these production clusters are not easily
restarted; the customer has to be notified, etc. "It'd be nice" if we
could adjust the volume of logging dynamically (i.e., turn-off of statement
logging, etc) without have to restart the server...
In any particular session you can do SET log_statement = off or some such.
That way you can make logging more fine grained.
--
Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/
Show quoted text
If the Catholic church can survive the printing press, science fiction
will certainly weather the advent of bookwarez.
http://craphound.com/ebooksneitherenorbooks.txt - Cory Doctorow
Martijn van Oosterhout wrote:
-- Start of PGP signed section.
On Wed, Feb 18, 2004 at 03:54:39PM -0700, Ed L. wrote:
We extract great value from logging every SQL statement, its duration, etc.,
in service to debugging, troubleshooting, monitoring, etc. But we have a
number of clusters that seem to always be bumping into disk I/O
bottlenecks. With a system running at 100 QPS, we're inclined to quiet the
logging and only turn it up as needed (which will, by definition, be while
it was quieted). But many of these production clusters are not easily
restarted; the customer has to be notified, etc. "It'd be nice" if we
could adjust the volume of logging dynamically (i.e., turn-off of statement
logging, etc) without have to restart the server...In any particular session you can do SET log_statement = off or some such.
That way you can make logging more fine grained.
You can also change the postgresql.conf file and SIGHUP the postmaster
for it to take affect. It doesn't restart the server.
--
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 19073On Wednesday February 18 2004 7:13, Bruce Momjian wrote:
Martijn van Oosterhout wrote:
-- Start of PGP signed section.On Wed, Feb 18, 2004 at 03:54:39PM -0700, Ed L. wrote:
We extract great value from logging every SQL statement, its
duration, etc., in service to debugging, troubleshooting, monitoring,
etc. But we have a number of clusters that seem to always be bumping
into disk I/O bottlenecks. With a system running at 100 QPS, we're
inclined to quiet the logging and only turn it up as needed (which
will, by definition, be while it was quieted). But many of these
production clusters are not easily restarted; the customer has to be
notified, etc. "It'd be nice" if we could adjust the volume of
logging dynamically (i.e., turn-off of statement logging, etc)
without have to restart the server...In any particular session you can do SET log_statement = off or some
such. That way you can make logging more fine grained.You can also change the postgresql.conf file and SIGHUP the postmaster
for it to take affect. It doesn't restart the server.
Very fast coding, and simply amazing patch distribution. ;-)
Thx.
"Ed L." <pgsql@bluepolka.net> writes:
"It'd be nice" if we
could adjust the volume of logging dynamically (i.e., turn-off of statement
logging, etc) without have to restart the server...
Why do you think you need to restart the server? Adjust postgresql.conf
and SIGHUP the postmaster.
regards, tom lane
On Wednesday February 18 2004 9:33, Tom Lane wrote:
"Ed L." <pgsql@bluepolka.net> writes:
"It'd be nice" if we
could adjust the volume of logging dynamically (i.e., turn-off of
statement logging, etc) without have to restart the server...Why do you think you need to restart the server? Adjust postgresql.conf
and SIGHUP the postmaster.
Well, on closer look, there it is in plain sight in the runtime config
documentation I've looked through a hundred times for other purposes. Just
one of many features I haven't discovered, I guess.
The 7.3.4 doc (doc/html/runtime-config.html) does say the log_connections
option can only be set at server start or in the postgresql.conf
configuration file. That seems a little ambiguous on the SIGHUP'ability of
that option. My 7.3.4 install here shows all the log_* options are
dynamically reloadable.
Ed L. wrote:
On Wednesday February 18 2004 9:33, Tom Lane wrote:
"Ed L." <pgsql@bluepolka.net> writes:
"It'd be nice" if we
could adjust the volume of logging dynamically (i.e., turn-off of
statement logging, etc) without have to restart the server...Why do you think you need to restart the server? Adjust postgresql.conf
and SIGHUP the postmaster.Well, on closer look, there it is in plain sight in the runtime config
documentation I've looked through a hundred times for other purposes. Just
one of many features I haven't discovered, I guess.The 7.3.4 doc (doc/html/runtime-config.html) does say the log_connections
option can only be set at server start or in the postgresql.conf
configuration file. That seems a little ambiguous on the SIGHUP'ability of
that option. My 7.3.4 install here shows all the log_* options are
dynamically reloadable.
The issue is that you can't set log_connections from psql. Should we
improve the doc wording?
--
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 19073On Thursday February 19 2004 9:13, Bruce Momjian wrote:
The 7.3.4 doc (doc/html/runtime-config.html) does say the
log_connections option can only be set at server start or in the
postgresql.conf configuration file. That seems a little ambiguous on
the SIGHUP'ability of that option. My 7.3.4 install here shows all the
log_* options are dynamically reloadable.The issue is that you can't set log_connections from psql. Should we
improve the doc wording?
Ah, I had no idea that was the issue. Maybe a quick reference
table in the docs would be helpful:
Option SET option Reloads on SIGHUP
=====================================================
shared_buffers no no
log_connections no yes
log_statement yes yes
...
Maybe it's already in there somewhere, I dunno.
Bruce Momjian <pgman@candle.pha.pa.us> writes:
The issue is that you can't set log_connections from psql. Should we
improve the doc wording?
I think the only thing missing is that in the introduction to section
16.4, where it says "The configuration file is reread whenever the
postmaster process receives a SIGHUP signal", we should add something
along the line of "Some parameters can only be set at server start;
any changes to their entries in the configuration file will be ignored
until the server is restarted". While a reasonable person could
probably figure that out ;-) it would not hurt to say it explicitly.
regards, tom lane
On Thu, 19 Feb 2004, Ed L. wrote:
On Thursday February 19 2004 9:13, Bruce Momjian wrote:
The 7.3.4 doc (doc/html/runtime-config.html) does say the
log_connections option can only be set at server start or in the
postgresql.conf configuration file. That seems a little ambiguous on
the SIGHUP'ability of that option. My 7.3.4 install here shows all the
log_* options are dynamically reloadable.The issue is that you can't set log_connections from psql. Should we
improve the doc wording?Ah, I had no idea that was the issue. Maybe a quick reference
table in the docs would be helpful:Option SET option Reloads on SIGHUP
=====================================================
shared_buffers no no
log_connections no yes
log_statement yes yes
...Maybe it's already in there somewhere, I dunno.
The basic info is there, but not in a table. That would come in quite
handy...
Added to docs.
---------------------------------------------------------------------------
Tom Lane wrote:
Bruce Momjian <pgman@candle.pha.pa.us> writes:
The issue is that you can't set log_connections from psql. Should we
improve the doc wording?I think the only thing missing is that in the introduction to section
16.4, where it says "The configuration file is reread whenever the
postmaster process receives a SIGHUP signal", we should add something
along the line of "Some parameters can only be set at server start;
any changes to their entries in the configuration file will be ignored
until the server is restarted". While a reasonable person could
probably figure that out ;-) it would not hurt to say it explicitly.regards, tom lane
--
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