Horton
Menu

Thread

  1. patch to implement ECPG side tracing / tracking ...

    Hans-Juergen Schoenig <hs@cybertec.at> — 2010-01-12T23:07:13Z

    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

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

    Jaime Casanova <jcasanov@systemguards.com.ec> — 2010-01-13T01:10:38Z

    2010/1/12 Hans-Jürgen Schönig <hs@cybertec.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

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

    Michael Meskes <meskes@postgresql.org> — 2010-01-13T13:38:07Z

    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@jabber.org
VfL Borussia! Forca Barca! Go SF 49ers! Use: Debian GNU/Linux, PostgreSQL

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

    Hans-Juergen Schoenig <hs@cybertec.at> — 2010-01-13T21:30:32Z

    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

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

    Tom Lane <tgl@sss.pgh.pa.us> — 2010-01-13T21:42:36Z

    Hans-Juergen Schoenig <hs@cybertec.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

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

    Robert Haas <robertmhaas@gmail.com> — 2010-01-13T22:05:06Z

    On Wed, Jan 13, 2010 at 4:42 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Hans-Juergen Schoenig <hs@cybertec.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

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

    Michael Meskes <meskes@postgresql.org> — 2010-01-14T13:29:19Z

    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@jabber.org
VfL Borussia! Forca Barca! Go SF 49ers! Use: Debian GNU/Linux, PostgreSQL

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

    Dimitri Fontaine <dfontaine@hi-media.com> — 2010-01-14T13:51:37Z

    Michael Meskes <meskes@postgresql.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

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

    Boszormenyi Zoltan <zb@cybertec.at> — 2010-01-14T14:15:49Z

    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/

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

    Alvaro Herrera <alvherre@commandprompt.com> — 2010-02-10T16:12:31Z

    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.

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

    Michael Meskes <meskes@postgresql.org> — 2010-02-11T15:19:07Z

    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@jabber.org
VfL Borussia! Força Barça! Go SF 49ers! Use Debian GNU/Linux, PostgreSQL