Re: Dynamic LWLock tracing via pg_stat_lwlock (proof of concept)

On 10/02/2014 12:19 AM, Ilya Kosmodemiansky wrote:

> From an Oracle DBA's point of view, currently we have a lack of
> performance diagnostics tools. 

Agreed. Sometimes very frustratingly so.

> Even better idea is to collect daily LWLock distribution, find most
> frequent of them etc.

While we could add this within PostgreSQL, I wonder if it's worth
looking at whether it can be done non-intrusively with operating system
facilities like perf.

I've had really good results using perf to trace and graph xlog
generation and other metrics in the past.

> The patch https://commitfest.postgresql.org/action/patch_view?id=885
> (discussion starts here I hope -
> http://www.postgresql.org/message-id/4FE8CA2C.3030809@uptime.jp)
> demonstrates performance problems; LWLOCK_STAT,  LOCK_DEBUG and
> DTrace-like approach are slow, unsafe for production use and a bit
> clumsy for using by DBA.

It's not at all clear to me that a DTrace-like (or perf-based, rather)
approach is unsafe, slow, or unsuitable for production use.

Resolving lock IDs to names might be an issue, though.

With appropriate wrapper tools I think we could have quite a useful
library of perf-based diagnostics and tracing tools for PostgreSQL.

-- Craig Ringer                   http://www.2ndQuadrant.com/PostgreSQL Development, 24x7 Support, Training & Services