Thread: Logging functions executed by queries in 8.2?
Hackers, In recent versions, we've changed the logging of function executions so that only the function call is logged, and not any of the queries which it may execute internally. While most of the time this method is superior for performance analysis, in applications with extensive multi-line stored procedures sometimes you want to log each individual query. While it's kind of possible to do this via RAISE NOTICE, that doesn't accurately get you execution times, let alone allow you do to thinks like log query plans. This is seriously hampering our ability to performance-optimize for TPCE. Questions: 1) Is there any workaround for 8.2 which would allow us to log the function queries and plans? 2) Would it be reasonable to add a log_function_bodies option for 8.3? -- --Josh Josh Berkus PostgreSQL @ Sun San Francisco
Hackers, > In recent versions, we've changed the logging of function executions so > that only the function call is logged, and not any of the queries which > it may execute internally. While most of the time this method is > superior for performance analysis, in applications with extensive > multi-line stored procedures sometimes you want to log each individual > query. Actually, it's even more twisted now; if I turn on debug_print_plan, I can get the *plan* for queries executed by a function, but I can't get their durations. -- --Josh Josh Berkus PostgreSQL @ Sun San Francisco
Josh Berkus <josh@agliodbs.com> writes:
> In recent versions, we've changed the logging of function executions so
> that only the function call is logged, and not any of the queries which it
> may execute internally. While most of the time this method is superior
> for performance analysis, in applications with extensive multi-line stored
> procedures sometimes you want to log each individual query.
ISTM that the wave of the future for this is an instrumentation plug-in,
not further kluging of the query logging functionality. I had the
impression that Korry and EDB had some prototype capability in that
direction already, and surely it shouldn't be that hard to write if not.
regards, tom lane
<blockquote type="CITE"><pre> <font color="#000000">Josh Berkus <<a href="mailto:josh@agliodbs.com">josh@agliodbs.com</a>> writes:</font> <font color="#000000">> In recent versions, we've changed the logging of function executions so </font> <font color="#000000">> that only the function call is logged, and not any of the queries which it </font> <font color="#000000">> may execute internally. While most of the time this method is superior </font> <font color="#000000">> for performance analysis, in applications with extensive multi-line stored </font> <font color="#000000">> procedures sometimes you want to log each individual query.</font> <font color="#000000">ISTM that the wave of the future for this is an instrumentation plug-in,</font> <font color="#000000">not further kluging of the query logging functionality. I had the</font> <font color="#000000">impression that Korry and EDB had some prototype capability in that</font> <font color="#000000">direction already, and surely it shouldn't be that hard to write if not.</font> </pre></blockquote><br /> There's a sort of "proof-of-concept" PL/pgSQL tracer plugin in the debugger project on pgFoundry- I haven't played with it in a few months so I can't promise that it will run at the moment.<br /><br /> If anyoneis interested, let me know and I'll add this to my ToDo list. <br /><br /> -- Korry
Korry, > If anyone is interested, let me know and I'll add this to my ToDo > list. The Sun benchmarking team needs this. However, we need to be able to feed the data into some kind of mass analysis ala pg_fouine so that we can do overall performance analysis. -- --Josh Josh Berkus PostgreSQL @ Sun San Francisco
<blockquote type="CITE"><pre> <font color="#000000">> If anyone is interested, let me know and I'll add this to my ToDo</font> <font color="#000000">> list.</font> <font color="#000000">The Sun benchmarking team needs this. However, we need to be able to feed </font> <font color="#000000">the data into some kind of mass analysis ala pg_fouine so that we can do </font> <font color="#000000">overall performance analysis.</font> </pre></blockquote><br /> I've also included a PL/pgSQL profiler in the PL debugger project - this plugin spits out an XMLprofile so you should be able to massage it/aggregate it however you like.<br /><br /> -- Korry<br /><br />