Skip site navigation (1) Skip section navigation (2)

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 (view raw or flat)
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: lockstats-1.patch.gz
Description: application/x-gzip (10.1 KB)

Responses

pgsql-hackers by date

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

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group