On Fri, Oct 3, 2014 at 05:53:59PM +0200, Ilya Kosmodemiansky wrote: > > What that gives us is almost zero overhead on backends, high > > reliability, and the ability of the scan daemon to give higher weights > > to locks that are held longer. Basically, if you just stored the locks > > you held and released, you either have to add timing overhead to the > > backends, or you have no timing information collected. By scanning > > active locks, a short-lived lock might not be seen at all, while a > > longer-lived lock might be seen by multiple scans. What that gives us > > is a weighting of the lock time with almost zero overhead. If we want > > finer-grained lock statistics, we just increase the number of scans per > > second. > > So I could add the function, which will accumulate the data in some > view/table (with weights etc). How it should be called? From specific > process? From some existing maintenance process such as autovacuum? > Should I implement GUC for example lwlock_pull_rate, 0 for off, from 1 > to 10 for 1 to 10 samples pro second?
Yes, that's the right approach. You would implement it as a background worker process, and a GUC as you described. I assume it would populate a view like we already do for the pg_stat_ views, and the counters could be reset somehow. I would pattern it after how we handle the pg_stat_ views. > > I am assuming almost no one cares about the number of locks, but rather > > they care about cummulative lock durations. > > Oracle and DB2 measure both, cummulative durations and counts. Well, the big question is whether counts are really useful. You did a good job of explaining that when you find heavy clog or xlog lock usage you would adjust your server. What I am unclear about is why you would adjust your server based on lock _counts_ and not cummulative lock duration. I don't think we want the overhead of accumulating information that isn't useful. > > I am having trouble seeing any other option that has such a good > > cost/benefit profile. > > At least cost. In Oracle documentation clearly stated, that it is all > about diagnostic convenience, performance impact is significant. Oh, we don't want to go there then, and I think this approach is a big win. -- Bruce Momjian <br...@momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com + Everyone has their own god. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
