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(dot)smith(at)crunchydatasolutions(dot)com
Chief PostgreSQL Evangelist - http://crunchydatasolutions.com/