Thread: 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
--
Cybertec Schönig & Schönig GmbH
Reyergasse 9 / 2
A-2700 Wiener Neustadt
Web: www.postgresql-support.de
Attachment
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
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
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@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
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
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
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
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@jabber.org VfL Borussia! Força Barça! Go SF 49ers! Use Debian GNU/Linux, PostgreSQL