Lock Wait Statistics (next commitfest)

From: Mark Kirkwood <markir(at)paradise(dot)net(dot)nz>
To: pgsql-hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: Lock Wait Statistics (next commitfest)
Date: 2009-01-25 23:57:56
Message-ID: 497CFC84.2070302@paradise.net.nz
Views: Raw Message | Whole Thread | Download mbox
Thread:
Lists: pgsql-hackers

Where I work they make extensive use of Postgresql. One of the things
they typically want to know about are lock waits. Out of the box in
there is not much in the way of tracking for such, particularly in older
versions. The view pg_stats is fine for stuff happening *now*, but
typically I find I'm being asked about something that happened last
night...

Now for those platforms with dtrace there is a lock wait probe, useful -
but not for those of us on Linux! There is the conf option to log lock
waits > deadlock timeout (8.3 onwards), and this is great, but I
wondered if having something like this available as part of the stats
module would be useful.

So here is my initial attempt at this, at this point merely to spark
discussion (attached patch)

I have followed some of the ideas from the function execution stats, but
locks required some special treatment.

- new parameter track_locks (maybe should be track_lock_waits?)
- new hash locks attached to stats dbentry
- several new stat*lock c functions
- new view pg_stat_lock_waits
- new attributes for pg_stat_database

This version has simply exposed the locktag structure in the view along
with corresponding #waits and wait time. This should probably get
reformed to look a little more like pg_locks. I figured this is easily
doable along with the no doubt many changes coming from revew comments.

Also I did not do any clever amalgamation of transaction lock waits -
there is probably gonna be a lot of those and keeping the detail is
unlikely to be useful. It would be easy to collect them all together in
one transaction entry.

regards

Mark

Attachment Content-Type Size
lockstats-1.patch.gz application/x-gzip 10.1 KB

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message ITAGAKI Takahiro 2009-01-26 04:14:00 Compiler warnings fix
Previous Message Jaime Casanova 2009-01-25 21:40:34 Re: [COMMITTERS] pgsql: Automatic view update rules Bernd Helmle