Re: proposal for 9.5: monitoring lock time for slow queries

On 8/13/14, 5:14 AM, MauMau wrote:
> I'm interested in b, too.  I was thinking of proposing a performance 
> diagnostics feature like Oracle's wait events (V$SYSTEM_EVENT and 
> V$SESSION_EVENT).  So, if you do this, I'd like to contribute to the 
> functional design, code and doc review, and testing.

I already wrote up a design like this once: 
http://www.postgresql.org/message-id/509300F7.5000803@2ndQuadrant.com

The main problem when I tried to code it was figuring out how to store 
the data.  When I wrote that, I thought I could just stuff it into a 
shared memory block the way pg_stat_statements did.  That didn't really 
work out.  I think it's manageable now because the Dynamic Shared Memory 
management written since then has the right sort of shape to do the job.

This is one of those jobs where I think the coding itself is the hard 
part, not the design nor the review.  What I really want is something 
that dumps this data into memory, then a second process that persists to 
disk in batches.  I think that's the only way we'll get high performance 
on reads while still saving enough data to be useful after a crash.

We're working on getting a few things in this area fully funded to dig 
into them harder.  The idea of designing for high-speed audit logs into 
memory and then persisting to disk has a lot of overlap with this one 
too, and that may get picked up too.

-- 
Greg Smith greg.smith@crunchydatasolutions.com
Chief PostgreSQL Evangelist - http://crunchydatasolutions.com/