Re: Logging functions executed by queries in 8.2?

Поиск
Список
Период
Сортировка
Искать

Re: Logging functions executed by queries in 8.2?

От:
<korryd@enterprisedb.com>
Дата:
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.


 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.

 If anyone is interested, let me know and I'll add this to my ToDo list.  

             -- Korry

Re: Logging functions executed by queries in 8.2?

От:
<korryd@enterprisedb.com>
Дата:
> 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.


 I've also included a PL/pgSQL profiler in the PL debugger project - this plugin spits out an XML profile so you should be able to massage it/aggregate it however you like.

         -- Korry

Logging functions executed by queries in 8.2?

От:
Josh Berkus <josh@agliodbs.com>
Дата:
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

Re: Logging functions executed by queries in 8.2?

От:
Josh Berkus <josh@agliodbs.com>
Дата:
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

Re: Logging functions executed by queries in 8.2?

От:
Josh Berkus <josh@agliodbs.com>
Дата:
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

Re: Logging functions executed by queries in 8.2?

От:
Tom Lane <tgl@sss.pgh.pa.us>
Дата:
Josh Berkus  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
FAQ