Re: [GENERAL] Procedural Code Profiling
2007/8/6, Lewis Cunningham [EMAIL PROTECTED]: Hi all, 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. Thanks, LewisC pgfouine is the best Regards Pavel Stehule ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[GENERAL] Procedural Code Profiling
Hi all, 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. Thanks, LewisC --- Lewis R Cunningham An Expert's Guide to Oracle Technology http://blogs.ittoolbox.com/oracle/guide/ LewisC's Random Thoughts http://lewiscsrandomthoughts.blogspot.com/ EnterpriseDB: The Definitive Reference http://tinyurl.com/39246e -- ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Procedural Code Profiling
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 -- Korry Douglas[EMAIL PROTECTED] mailto:[EMAIL PROTECTED] EnterpriseDB http://www.enterprisedb.com
Re: [GENERAL] Procedural Code Profiling
--- korry.douglas [EMAIL PROTECTED] wrote: 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) Sweet. This is exactly what I was looking for. Thanks, LewisC --- Lewis R Cunningham An Expert's Guide to Oracle Technology http://blogs.ittoolbox.com/oracle/guide/ LewisC's Random Thoughts http://lewiscsrandomthoughts.blogspot.com/ EnterpriseDB: The Definitive Reference http://tinyurl.com/39246e -- ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
