Hackers,
(some snippage...)
Our Problem: We work with 75+ geographically distributed pg clusters; it is a significant challenge keeping tabs on performance. We see degradations from rogue applications, vacuums, dumps, bloating indices, I/O and memory shortages, and so on. Customers don't generally tell us when applications are slow, so we need to know for ourselves in a timely manner. At present, we can remotely and systematically query system relations for diskspace usage, detailed I/O usage, index/sequential scans, and more. But our _ultimate_ DB performance measure is query execution time. Obviously, you can measure that now in an ad hoc fashion with EXPLAIN ANALYZE, and by examining historical logs. But we need to be able to see the history in a timely fashion to systematically identify customer-experienced execution time degradations for "query patterns of interest" without any visual log inspection whatsoever, and correlate those with other events.
Is enabling the various postgresql.conf stats* options and taking regular snapshots of pg_stat_activity a possible way to get this?
regards
Mark
---------------------------(end of broadcast)--------------------------- TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
