Logging Lock Waits

From: "Simon Riggs" <simon(at)2ndquadrant(dot)com>
To: <pgsql-hackers(at)postgresql(dot)org>
Subject: Logging Lock Waits
Date: 2007-01-30 23:48:20
Message-ID: 1170200900.3681.338.camel@silverbirch.site
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

Right now, I need a way to log the lock wait times for certain queries,
to see if they are acceptable. DTrace is not available.

I'm dealing with a problem that is either a standard lock wait involving
RI trigger locking, or a weirder problem involving lock starvation as a
result of soft deadlock queue re-arrangement. I doubt its the latter but
neither situation can be straightforwardly enabled to diagnose the
situation.

Sometime earlier, I proposed log_min_lockwait.

A frequent case is where the lockwait is so long that the statement
effectively never completes, so the statement duration doesn't appear in
the log. That can cause further diagnosis problems.

Ideally, I'd like to know both
1. there is a lock wait and it is happening now
2. there was a lock wait and it was THIS bad

For 1, I need to log something while the lock wait is happening, not
just when it ends - it might not end successfully. For 2 we can log it
during or afterwards, not a problem.

I'm thinking to write an INFO message, so that people can choose to log
this and/or the SQL statement if they choose.

e.g. INFO: lock wait time of XXX secs has been exceeded

Comments?

--
Simon Riggs
EnterpriseDB http://www.enterprisedb.com

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Tom Lane 2007-01-31 00:10:28 Re: parsenodes vs. primnodes
Previous Message Simon Riggs 2007-01-30 23:24:40 Re: Improving NOT IN