2014-08-13 11:14 GMT+02:00 MauMau <maumau...@gmail.com>: > From: "Pavel Stehule" <pavel.steh...@gmail.com> > > There are two relative independent tasks >> >> a) monitor and show total lock time of living queries >> >> b) monitor and log total lock time of executed queries. >> >> I am interested by @b now. When we work with slow query log, then we would >> to identify reason for long duration. Locks are important source of these >> queries on some systems. >> > > 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. >
isn't it too heavy? I have just terrible negative experience with Vertica, where this design is used - almost all information about queries are available, but any query to related tables are terrible slow, so I am inclined to more simple design oriented to log based solution. Table based solutions is not practical when you exec billions queries per day. I understand to motivation, but I afraid so it can be very expensive and slow on highly load servers. > > The point is to collect as much information about bottlenecks as possible, > including lock waits. The rough sketch is: > > What info to collect: > * heavyweight lock waits shown by pg_locks > * lightweight lock waits > * latch waits > * socket waits (mainly for client input) > > > How the info is delivered: > * pg_stat_system_events shows the accumulated total accross the server > instance > * pg_stat_session_events shows the accumulated total for each session > * EXPLAIN ANALYZE and auto_explain shows the accumulated total for each > query > > We need to describe in the manual how to diagnose and tne the system with > these event info. > > Regards > MauMau > >
