What is the best tool for an app to profile procedural code in
postgres? I want to instrument my code and trace it so that I can
see which code bits are sucking up the cpu and time. I know I can
stick messages in the code and gather my own stats but I was
wondering is there are any good tools to automate this.
Specifically working with pl/pgSQL and 8.2 (on Linux or windows) if
that helps.
Hi Lewis,
The edb-debugger project at pgFoundry offers three different plugins: a PL/pgSQL debugger, a PL/pgSQL profiler, and a PL/pgSQL tracer.
EnterpriseDB just open-sourced the debugger last week and we are still ironing out a few deployment glitches here and there. pgAdmin3 offers an excellent graphical interface to the PL/pgSQL debugger. The debugger is usable today, but you'll experience one bug for certain (an extra "Step" is required at the end of each PL/pgSQL function).
The PL/pgSQL profiler is what you are looking for and, to be honest, I haven't even tried to compile it in over a year so it is probably pretty rusty.
The profiler watches (all of) your PL/pgSQL code as it executes and records:
execution count (number of times each statement is executed)
total execution time (how long did we spend executing each statement?)
longest execution time (how long did the slowest iteration take?)
number of scans (total number of sequential and indexed scans)
blocks fetched (for each statement)
blocks hit (blocks found in buffer pool) (for each statement)
tuples returned (for each statement)
tuples fetched (for each statement)
tuples inserted (for each statement)
tuples updated (for each statement)
tuples deleted (for each statement)
The results are written to an XML file (along with the source code for each PL/pgSQL function that execute) and you can massage that into whatever form you like (I think I have an XSLT script around here somewhere that will translate the XML file into a nice HTML report).
I will try to get the profiler compiled against 8.2.4 in the next couple of days (and send you an e-mail when I get it running).
-- Korry
