Re: patch to implement ECPG side tracing / tracking ...

hi,

this patch implements SQL side tracing / tracking of statements and
statement execution times.
it is primarily intended to allow programmers to gather information
about the runtime behavior of a program and to figure out easily where
the bottlenecks are.
i used the ECPG prepared statement infrastructure to implement this.
the goal of this code is allow people to port code from databases such
as Informix to PostgreSQL more easily and to figure out as fast as
possible which types of queries are fast and which ones are slow.

best regards,

hans

2010/1/12 Hans-Jürgen Schönig <hs(at)cybertec(dot)at>:
> hi,
>
> this patch implements SQL side tracing / tracking of statements and
> statement execution times.
>

why is this better than using the "auto explain" module?

--
Atentamente,
Jaime Casanova
Soporte y capacitación de PostgreSQL
Asesoría y desarrollo de sistemas
Guayaquil - Ecuador
Cel. +59387171157

Hans,

nce Jaime already asked for a use case, just a few small comments from
me.

> @@ -4,6 +4,7 @@
> #include "postgres_fe.h"
>
> #include <ctype.h>
> +#include <inttypes.h>

This is not portable. You don't want to include this header.

Did I see this right that you use the statement cache for auto-prepared
statements even if the statement is not auto prepared? Some statements are not
profiled, how did you decide which one to do?

There is no test case.

Before looking into it in detail I think we should first figure out if this
feature really has a benefit.

Michael
--
Michael Meskes
Michael at Fam-Meskes dot De, Michael at Meskes dot (De|Com|Net|Org)
Michael at BorussiaFan dot De, Meskes at (Debian|Postgresql) dot Org
ICQ: 179140304, AIM/Yahoo/Skype: michaelmeskes, Jabber: meskes(at)jabber(dot)org
VfL Borussia! Forca Barca! Go SF 49ers! Use: Debian GNU/Linux, PostgreSQL

Michael Meskes wrote:
> Hans,
>
> nce Jaime already asked for a use case, just a few small comments from
> me.
>
>
>> @@ -4,6 +4,7 @@
>> #include "postgres_fe.h"
>>
>> #include <ctype.h>
>> +#include <inttypes.h>
>>
>
> This is not portable. You don't want to include this header.
>
> Did I see this right that you use the statement cache for auto-prepared
> statements even if the statement is not auto prepared? Some statements are not
> profiled, how did you decide which one to do?
>
> There is no test case.
>
> Before looking into it in detail I think we should first figure out if this
> feature really has a benefit.
>
> Michael
>

hello ...

the use cases for this thing are quite simple: we are currently porting
hundreds (!) of complex Informix terminal applications to PostgreSQL.
these are basically terminal applications used to perform a certain
tasks. given the vast amount of code, we simply cannot change a single
program because if we have to dig into the actual application code, we
are dead before actually starting (business logic is a nightmare). so,
to get around the problem we are basically adding all extensions to ECPG
we need to make this work. this is why we did all this SQLDA stuff and
so on you have seen recently.

the current problems are a bit more delicate: we have this vast number
of programs and some of them perform better than Informix and some
simply don't. Informix has some sort of "explain mode" (I forgot the
exact name) which allows you to see which query is executed how by the
system. effectively, you can use it to performance tune your precompiler
application. in PostgreSQL it is currently a little hard to get from the
log what is executed how often by which application in which speed and
so on. so, we came up with the idea of adding a flag to the precompiler
which essential keep stats for us and display it on exit (could be sent
to a file then or so without anybody's notice). this would give
excellent data to start with and it would make checking the database
part of the application easily.
why for prepared queries: we found out that Informix is heavily using
prepared queries internally. we already fixed something in this area
(patch sent some time ago) and we were finally able to catch up with
Informix performance-wise in this area (mostly cursor work). before this
auto_prepare fix, we were sometimes 2-3 times slower than Informix.
saving on network time solved the job. now we are left with many many
programs performing somehow strange and we need to check for every
program why. a decent summary on exit would be gold here.

it seems we will also come up with a server-side extension soon which
basically compares and logs planner / executor starts the way we do it
for stored procedures now (thanks to martin pilhak). we simply need it
so that we can figure out which of our XXX programs did what then.
testing one after the other is not so easy, some of them depend on each.

to make it short: it is impossible to port hundreds of applications to
PostgreSQL without having the chance to trace what the precompiler is
doing how often in which program via which connection. it is simply
impossible. so, we really and desparately need this patch in.

many thanks,

hans

--
Cybertec Schoenig & Schoenig GmbH
Reyergasse 9 / 2
A-2700 Wiener Neustadt
Web: www.postgresql-support.de

Hans-Juergen Schoenig <hs(at)cybertec(dot)at> writes:
> Michael Meskes wrote:
>> Before looking into it in detail I think we should first figure out if this
>> feature really has a benefit.

> the use cases for this thing are quite simple: we are currently porting
> hundreds (!) of complex Informix terminal applications to PostgreSQL.
> [ and need to optimize them ]

What you didn't explain is why you need client-side tracing rather than
using the rather extensive facilities that already exist server-side.
In particular, have you looked at CVS tip contrib/auto_explain? It
seems like you are duplicating a lot of what that can do. If that needs
some additional features, you could work on that. From the big picture
standpoint I think it makes a lot more sense to add instrumentation
server-side than client-side. Any features you add client-side are only
available to ecpg users, and you have to cope with ensuring there's a
way to collect the data out of the application (which may be running in
an environment where that's hard).

regards, tom lane

On Wed, Jan 13, 2010 at 4:42 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Hans-Juergen Schoenig <hs(at)cybertec(dot)at> writes:
>> Michael Meskes wrote:
>>> Before looking into it in detail I think we should first figure out if this
>>> feature really has a benefit.
>
>> the use cases for this thing are quite simple: we are currently porting
>> hundreds (!) of complex Informix terminal applications to PostgreSQL.
>> [ and need to optimize them ]
>
> What you didn't explain is why you need client-side tracing rather than
> using the rather extensive facilities that already exist server-side.
> In particular, have you looked at CVS tip contrib/auto_explain?  It
> seems like you are duplicating a lot of what that can do.  If that needs
> some additional features, you could work on that.  From the big picture
> standpoint I think it makes a lot more sense to add instrumentation
> server-side than client-side.  Any features you add client-side are only
> available to ecpg users, and you have to cope with ensuring there's a
> way to collect the data out of the application (which may be running in
> an environment where that's hard).

The OP might even want to think about just turning on
log_min_duration_statement for all queries. auto_explain might even
be more than is needed.

...Robert

On Wed, Jan 13, 2010 at 10:30:32PM +0100, Hans-Juergen Schoenig wrote:
> performance tune your precompiler application. in PostgreSQL it is
> currently a little hard to get from the log what is executed how
> often by which application in which speed and so on. so, we came up

Hard or impossible? I agree with the other replies that this looks more like a
functionality you'd want in the server rather than the client.

> why for prepared queries: we found out that Informix is heavily
> using prepared queries internally. we already fixed something in

If you want a general feature why do you only implement it for one case?

> this area (patch sent some time ago) and we were finally able to
> catch up with Informix performance-wise in this area (mostly cursor
> work). before this auto_prepare fix, we were sometimes 2-3 times

Which fix are you talking about? I don't really remember a performance
improvement fix. Did I simply forget it or did I miss something important?

> slower than Informix. saving on network time solved the job. now we
> are left with many many programs performing somehow strange and we
> need to check for every program why. a decent summary on exit wouldA

Well I guess this is what you get paid for.

> to make it short: it is impossible to port hundreds of applications
> to PostgreSQL without having the chance to trace what the
> precompiler is doing how often in which program via which
> connection. it is simply impossible. so, we really and desparately
> need this patch in.

I'm sorry, but this is neither true (we've done it before) nor a valid point
(project decisions are independant from your contracts). You can surely
implement whatever you want for your customer but for your patch to make it
into our source tree there should be an advantage fore more people.

Michael

--
Michael Meskes
Michael at Fam-Meskes dot De, Michael at Meskes dot (De|Com|Net|Org)
Michael at BorussiaFan dot De, Meskes at (Debian|Postgresql) dot Org
ICQ: 179140304, AIM/Yahoo/Skype: michaelmeskes, Jabber: meskes(at)jabber(dot)org
VfL Borussia! Forca Barca! Go SF 49ers! Use: Debian GNU/Linux, PostgreSQL

Michael Meskes <meskes(at)postgresql(dot)org> writes:
> On Wed, Jan 13, 2010 at 10:30:32PM +0100, Hans-Juergen Schoenig wrote:
>> performance tune your precompiler application. in PostgreSQL it is
>> currently a little hard to get from the log what is executed how
>> often by which application in which speed and so on. so, we came up
>
> Hard or impossible? I agree with the other replies that this looks more like a
> functionality you'd want in the server rather than the client.

PgFouine partly answers that, and with application_name in 8.5 it should
further improve:

http://pgfouine.projects.postgresql.org/

Regards,
--
dim

Michael Meskes írta:
>> this area (patch sent some time ago) and we were finally able to
>> catch up with Informix performance-wise in this area (mostly cursor
>> work). before this auto_prepare fix, we were sometimes 2-3 times
>>
>
> Which fix are you talking about? I don't really remember a performance
> improvement fix. Did I simply forget it or did I miss something important?
>

Hans meant the auto-prepare fix. Being able to use it
is an important performance improvement for small queries. :-)

--
Bible has answers for everything. Proof:
"But let your communication be, Yea, yea; Nay, nay: for whatsoever is more
than these cometh of evil." (Matthew 5:37) - basics of digital technology.
"May your kingdom come" - superficial description of plate tectonics

----------------------------------
Zoltán Böszörményi
Cybertec Schönig & Schönig GmbH
http://www.postgresql.at/

Hans-Jürgen Schönig wrote:
> hi,
>
> this patch implements SQL side tracing / tracking of statements and
> statement execution times.
> it is primarily intended to allow programmers to gather information
> about the runtime behavior of a program and to figure out easily
> where the bottlenecks are.
> i used the ECPG prepared statement infrastructure to implement this.
> the goal of this code is allow people to port code from databases
> such as Informix to PostgreSQL more easily and to figure out as fast
> as possible which types of queries are fast and which ones are slow.

What happened to this patch? Was it abandoned in favor of server-side
tracing?

--
Alvaro Herrera http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.

On Wed, Feb 10, 2010 at 01:12:31PM -0300, Alvaro Herrera wrote:
> What happened to this patch? Was it abandoned in favor of server-side
> tracing?

I think it was abandoned but I don't remember seeing any patch/suggestion to
improve server-side tracing. This might come from server-side tracing already
being sufficient though.

Michael
--
Michael Meskes
Michael at Fam-Meskes dot De, Michael at Meskes dot (De|Com|Net|Org)
Michael at BorussiaFan dot De, Meskes at (Debian|Postgresql) dot Org
ICQ 179140304, AIM/Yahoo/Skype michaelmeskes, Jabber meskes(at)jabber(dot)org
VfL Borussia! Força Barça! Go SF 49ers! Use Debian GNU/Linux, PostgreSQL