Thread: Query preformence

Hi,

We try to migrate from mysql  to postgres 7.2 (Debian).  In mysql we use
"slow-query-logging" to improve the preformance of our query's.
This is a very important for us.

In postgres there is no relationship between de debug_print_query and the
show_query_stats, so you can't monitor the query preformance.

Is someone already solving this problem (i'ts on de to-do list) ?

I've made a simple change (i'm a newbe) in the src/backend/tcop/postgres.c.
I don't now if it's good enough, but it solves my problems.

Yours,
Ferdinand Smit (ferdinand@telegraafnet.nl)

Ferdinand Smit <ferdinand@telegraafnet.nl> writes:
> In postgres there is no relationship between de debug_print_query and the
> show_query_stats, so you can't monitor the query preformance.

What?  This seems completely redundant with the existing logging tools.

            regards, tom lane

Hi Tom,

> > In postgres there is no relationship between de debug_print_query and the
> > show_query_stats, so you can't monitor the query preformance.
>
> What?  This seems completely redundant with the existing logging tools.

True, but i can't see any usable function for the statics because they are
logged without any relation. You don't know what is causing the preformence
problem. We want to monitor the duration of query's so we can create smart
indexed and smart query's. With postgres this is imposible at the moment.

In the progres the debug is only for logging and the statics only for
statics, so you always have redundant code if don't want to change the whole
code.

But, do you have a better idea?

Ferdinand Smit

Yes, i'm a newbe so i choose a simple solution without disturbing any code ;-)

Ferdinand Smit <ferdinand@telegraafnet.nl> writes:
>> What?  This seems completely redundant with the existing logging tools.

> True, but i can't see any usable function for the statics because they are
> logged without any relation. You don't know what is causing the preformence
> problem.

Sure you do: you look at the execution statistics you don't like and
then look back to see the most recent query logged by the same backend.

I do see an issue here, which is that the STATISTICS messages should be
emitted by elog() so that they will be tagged with a PID when log_pid is
true.  But that's easily fixed.

BTW, you might want to look at 7.2's EXPLAIN ANALYZE command as a
different approach.

            regards, tom lane

Hoi Tom,

> Sure you do: you look at the execution statistics you don't like and
> then look back to see the most recent query logged by the same backend.

Our DB is heavily used so this would be a full-time job.

> I do see an issue here, which is that the STATISTICS messages should be
> emitted by elog() so that they will be tagged with a PID when log_pid is
> true.  But that's easily fixed.

This would be a good solution, so i can generate automatic reports.

Ferdinand

Hi Tom,

> I do see an issue here, which is that the STATISTICS messages should be
> emitted by elog() so that they will be tagged with a PID when log_pid is
> true.  But that's easily fixed.

Second try, this time by using the elog function. Is this a better solution ??

Yours,
Ferdinand Smit

Hi Tom,

> > I do see an issue here, which is that the STATISTICS messages should be
> > emitted by elog() so that they will be tagged with a PID when log_pid is
> > true.  But that's easily fixed.
>
> Second try, this time by using the elog function. Is this a better solution
> ??

I've made a (little) mistake. I accidently removed the reset of de usage.
This is fixed in this version.

Yours,
Ferdinand Smit

Ferdinand Smit <ferdinand@telegraafnet.nl> writes:
>> I do see an issue here, which is that the STATISTICS messages should be
>> emitted by elog() so that they will be tagged with a PID when log_pid is
>> true.  But that's easily fixed.

> Second try, this time by using the elog function. Is this a better solution ??

I've already committed changes that do the former (emit the STATISTICS
messages via elog, not fprintf, so that they can be timestamped and
PID-stamped).  Does that address your concern?

            regards, tom lane

Hi Tom,

> I've already committed changes that do the former (emit the STATISTICS
> messages via elog, not fprintf, so that they can be timestamped and
> PID-stamped).  Does that address your concern?

Not at all, that's perfect. But i thought that I must recreate my patch.
But if its done, forget my patches !!

Ferdinand Smit