Thread: 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/ > 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
Attachment
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 19073
On 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 19073
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.
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