Hi Lewis,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. 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)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
|
