|
|
|
|
|
|
by fdr
4988 days ago
|
|
|
If you are lucky enough to be able to use Postgres 9.2, consider running pg_stat_statements all the time. It is one of my favorite pieces of work: it canonicalizes queries and can tell you a number of useful statistics on it. One of the advantages is that it can help you identify queries that are both very short but frequent (and could use a cache, or whatnot), and would fly below the radar of most log aggregations, because most people turn off logging off for very short statements. See also: identifying n+1 query pathologies. It really blows away log analysis and pg_fouine for all but the most heavyweight tasks, and its approach is a lot more sound than log analysis tools because it uses the semantic representation -- not the syntactic one -- of the query submitted. |
|
|
It allows you to get statistics per top-level function, which can reveal abnormalities otherwise impossible to detect, such as a function which is usually very fast, but very slow for a small number of calls relative to the total number of calls. Consider fa()->fx() and fb()->fx(), if fa()->fx() always is fast and you have a million such calls, but for some reason fb()->fx() is very slow, but you only have a few such calls, fx() will look like a very fast function without any performance problems, if you only look at pg_catalog.pg_stat_user_functions, but call_graph will reveal fx() being slow when called by the top-level function fb().
This is only relevant for systems always accessing the database through Stored Procedures though.