Re: [GENERAL] timer script from SAMS book or equivalent?

2007-10-09 Thread korry.douglas 



In the book PostgreSQL (2nd ed) the author mentions a timer script he wrote to 
analyze various performance bits about PostgreSQL. I've looked everywhere and 
can't find it. Does anyone know where I can find a copy, or find an equivalent 
tool?
  

Hi John, sorry for the delay in getting back to you.

You can find the timer program at 
http://www.conjectrix.com/pgbook/index.html.  You want the sample 
data/code for chapter 4.


  -- Korry

---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

Re: [GENERAL] Procedural Code Profiling

2007-08-06 Thread korry.douglas 



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