Обсуждение: Notice lock waits
One time too many, I ran some minor change using psql on a production server and was wondering why it was taking so much longer than it did on the test server. Only to discover, after messing around with opening new windows and running queries against pg_stat_activity and pg_locks and so on, that it was waiting for a lock. So I created a new guc, notice_lock_waits, which acts like log_lock_waits but sends the message as NOTICE so it will show up on interactive connections like psql. I turn it on in my .psqlrc, as it doesn't make much sense for me to turn it on in non-interactive sessions. A general facility for promoting selected LOG messages to NOTICE would be nice, but I don't know how to design or implement that. This is much easier, and I find it quite useful. I have it PGC_SUSET because it does send some tiny amount of information about the blocking process (the PID) to the blocked process. That is probably too paranoid, because the PID can be seen by anyone in the pg_locks table anyway. Do you think this is useful and generally implemented in the correct way? If so, I'll try to write some sgml documentation for it. Cheers, Jeff
Вложения
On 05/08/2016 19:00, Jeff Janes wrote: > One time too many, I ran some minor change using psql on a production > server and was wondering why it was taking so much longer than it did > on the test server. Only to discover, after messing around with > opening new windows and running queries against pg_stat_activity and > pg_locks and so on, that it was waiting for a lock. > > So I created a new guc, notice_lock_waits, which acts like > log_lock_waits but sends the message as NOTICE so it will show up on > interactive connections like psql. > > I turn it on in my .psqlrc, as it doesn't make much sense for me to > turn it on in non-interactive sessions. > > A general facility for promoting selected LOG messages to NOTICE would > be nice, but I don't know how to design or implement that. This is > much easier, and I find it quite useful. > > I have it PGC_SUSET because it does send some tiny amount of > information about the blocking process (the PID) to the blocked > process. That is probably too paranoid, because the PID can be seen > by anyone in the pg_locks table anyway. > > Do you think this is useful and generally implemented in the correct > way? If so, I'll try to write some sgml documentation for it. > I really like the idea. I'm not really sure on current implementation. Unless I'm wrong, disabling log_lock_waits would also disable notice_lock_waits, even if it's on. Maybe a new value for log_lock_waits, like "interactive". If switching this GUC from bool to enum is not acceptable or allowing to see blocking PID for anyone is an issue, then maybe adding a new GUC to say to also send a NOTICE instead? -- Julien Rouhaud http://dalibo.com - http://dalibo.org
Jeff Janes <jeff.janes@gmail.com> writes:
> I have it PGC_SUSET because it does send some tiny amount of
> information about the blocking process (the PID) to the blocked
> process. That is probably too paranoid, because the PID can be seen
> by anyone in the pg_locks table anyway.
Why not just leave out the PID? I think it's often far too simplistic
to blame a lock wait on a single other process, anyway.
regards, tom lane
On Fri, Aug 5, 2016 at 12:17 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote: > Jeff Janes <jeff.janes@gmail.com> writes: >> I have it PGC_SUSET because it does send some tiny amount of >> information about the blocking process (the PID) to the blocked >> process. That is probably too paranoid, because the PID can be seen >> by anyone in the pg_locks table anyway. > > Why not just leave out the PID? I think it's often far too simplistic > to blame a lock wait on a single other process, anyway. It actually wasn't including the PID anyway, as the errdetail_log_plural was not getting passed to the client. So I changed it to PGC_USERSET, didn't attempt to include details that won't be sent anyway (although it would be nice for a superuser to be able to see the statement text of the blocker, but that is a bigger issue than I am willing to deal with here) and have removed a memory leak/bug I introduced by foolishly trying to use 'continue' to avoid introducing yet another layer of nesting. Cheers, Jeff
Вложения
On 8/5/16 12:00 PM, Jeff Janes wrote: > So I created a new guc, notice_lock_waits, which acts like > log_lock_waits but sends the message as NOTICE so it will show up on > interactive connections like psql. I would strongly prefer that this accept a log level instead of being hard-coded to NOTICE. The reason is that I find the NOTICE chatter from many DDL commands to be completely worthless (looking at you %TYPE), so I normally set client_min_messages to WARNING in DDL scripts. I can work on that patch; would it essentially be a matter of changing notice_lock_waits to int lock_wait_level? -- Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX Experts in Analytics, Data Architecture and PostgreSQL Data in Trouble? Get it in Treble! http://BlueTreble.com 855-TREBLE2 (855-873-2532) mobile: 512-569-9461
On Tue, Aug 9, 2016 at 5:17 PM, Jim Nasby <Jim.Nasby@bluetreble.com> wrote:
On 8/5/16 12:00 PM, Jeff Janes wrote:So I created a new guc, notice_lock_waits, which acts like
log_lock_waits but sends the message as NOTICE so it will show up on
interactive connections like psql.
I would strongly prefer that this accept a log level instead of being hard-coded to NOTICE. The reason is that I find the NOTICE chatter from many DDL commands to be completely worthless (looking at you %TYPE),
Perhaps we should do something about those notices? In 9.3 we removed ones about adding implicit unique indexes to implement primary keys, and I think that that was a pretty good call.
so I normally set client_min_messages to WARNING in DDL scripts. I can work on that patch; would it essentially be a matter of changing notice_lock_waits to int lock_wait_level?
How would it be turned off? Is there a err level which would work for that? And what levels would non-superusers be allowed to set it to?
And, I'd be happy if you were to work on a patch to implement it.
Cheers,
Jeff
On Fri, Aug 5, 2016 at 10:30 PM, Jeff Janes <jeff.janes@gmail.com> wrote:
A general facility for promoting selected LOG messages to NOTICE would
be nice, but I don't know how to design or implement that. This is
much easier, and I find it quite useful.
IMHO that's what we need and it will benefit many more users instead of adding a new GUC every time.
FWIW I recently wrote a patch for Postgres-XL to do exactly this and I found it very useful, especially while debugging race conditions and problems with ongoing sessions. Sorry, I don't mean to hijack this thread, will post that patch as a separate thread.
Thanks,
Pavan
Pavan Deolasee http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services
PostgreSQL Development, 24x7 Support, Training & Services
On Sat, Aug 6, 2016 at 3:00 AM, Jeff Janes <jeff.janes@gmail.com> wrote:
One time too many, I ran some minor change using psql on a production
server and was wondering why it was taking so much longer than it did
on the test server. Only to discover, after messing around with
opening new windows and running queries against pg_stat_activity and
pg_locks and so on, that it was waiting for a lock.
So I created a new guc, notice_lock_waits, which acts like
log_lock_waits but sends the message as NOTICE so it will show up on
interactive connections like psql.
I turn it on in my .psqlrc, as it doesn't make much sense for me to
turn it on in non-interactive sessions.
A general facility for promoting selected LOG messages to NOTICE would
be nice, but I don't know how to design or implement that. This is
much easier, and I find it quite useful.
I have it PGC_SUSET because it does send some tiny amount of
information about the blocking process (the PID) to the blocked
process. That is probably too paranoid, because the PID can be seen
by anyone in the pg_locks table anyway.
Do you think this is useful and generally implemented in the correct
way? If so, I'll try to write some sgml documentation for it.
Providing the details of lock wait to the client is good. I fell this message
is useful for the cases where User/administrator is trying to perform some
SQL operations.
may not be good. Changing the existing GUC may be better.
I am not sure whether it really beneficial in providing all LOG as NOTICE
messages with a generic framework, it may be unnecessary overhead
for some users, I am not 100% sure.
Regards,
Hari Babu
Fujitsu Australia
On Wed, Sep 28, 2016 at 11:57 PM, Haribabu Kommi <kommi.haribabu@gmail.com> wrote:
On Sat, Aug 6, 2016 at 3:00 AM, Jeff Janes <jeff.janes@gmail.com> wrote:One time too many, I ran some minor change using psql on a production
server and was wondering why it was taking so much longer than it did
on the test server. Only to discover, after messing around with
opening new windows and running queries against pg_stat_activity and
pg_locks and so on, that it was waiting for a lock.
So I created a new guc, notice_lock_waits, which acts like
log_lock_waits but sends the message as NOTICE so it will show up on
interactive connections like psql.
I turn it on in my .psqlrc, as it doesn't make much sense for me to
turn it on in non-interactive sessions.
A general facility for promoting selected LOG messages to NOTICE would
be nice, but I don't know how to design or implement that. This is
much easier, and I find it quite useful.
I have it PGC_SUSET because it does send some tiny amount of
information about the blocking process (the PID) to the blocked
process. That is probably too paranoid, because the PID can be seen
by anyone in the pg_locks table anyway.
Do you think this is useful and generally implemented in the correct
way? If so, I'll try to write some sgml documentation for it.Providing the details of lock wait to the client is good. I fell this messageis useful for the cases where User/administrator is trying to perform someSQL operations.I also feel that, adding a GUC variable for these logs to show it to usermay not be good. Changing the existing GUC may be better.
I don't think it would be a good idea to refactor the existing GUC (log_lock_waits) to accomplish this.
There would have to be four states, log only, notice only, both log and notice, and neither. But non-superusers can't be allowed to change the log flag, only the notice flag. It is probably possible to implement that, but it seems complicated both to implement, and to explain/document. I think that adding another GUC is better than greatly complicating an existing one.
What do you think of Jim Nasby's idea of making a settable level, rather just on or off?
Thanks,
Jeff
On Fri, Sep 30, 2016 at 3:00 AM, Jeff Janes <jeff.janes@gmail.com> wrote:
On Wed, Sep 28, 2016 at 11:57 PM, Haribabu Kommi <kommi.haribabu@gmail.com> wrote:Providing the details of lock wait to the client is good. I fell this messageis useful for the cases where User/administrator is trying to perform someSQL operations.I also feel that, adding a GUC variable for these logs to show it to usermay not be good. Changing the existing GUC may be better.I don't think it would be a good idea to refactor the existing GUC (log_lock_waits) to accomplish this.There would have to be four states, log only, notice only, both log and notice, and neither. But non-superusers can't be allowed to change the log flag, only the notice flag. It is probably possible to implement that, but it seems complicated both to implement, and to explain/document. I think that adding another GUC is better than greatly complicating an existing one.
Yes, I understood. Changing the existing GUC will make it complex.
What do you think of Jim Nasby's idea of making a settable level, rather just on or off?
I am not clearly understood, how the settable level works here? Based on log_min_messages
or something, the behavior differs?
The Notification messages are good, If we are going to add this facility only for lock waits, then
a simple GUC is enough. If we are going to enhance the same for other messages, then I prefer
something like log_statement GUC to take some input from user and those messages will be
sent to the user.
Regards,
Hari Babu
Fujitsu Australia
On Fri, Sep 30, 2016 at 2:00 AM, Jeff Janes <jeff.janes@gmail.com> wrote: > What do you think of Jim Nasby's idea of making a settable level, rather > just on or off? [reading the code] That would be a better idea. The interface proposed, aka 2 GUCs doing basically the same thing is quite confusing I think. I am marking the patch as returned with feedback for now. -- Michael
On Mon, Oct 3, 2016 at 11:40 AM, Michael Paquier <michael.paquier@gmail.com> wrote: > On Fri, Sep 30, 2016 at 2:00 AM, Jeff Janes <jeff.janes@gmail.com> wrote: >> What do you think of Jim Nasby's idea of making a settable level, rather >> just on or off? > > [reading the code] > That would be a better idea. The interface proposed, aka 2 GUCs doing > basically the same thing is quite confusing I think. I am marking the > patch as returned with feedback for now. Forgot to mention that I also found myself enforcing client_min_messages to warning to avoid annoying NOTICE messages. -- Michael