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