This is part of The Pile, a partial archive of some open source mailing lists and newsgroups.
Subject: Re: RFC: DBI::Prof From: Stas Bekman <stas@stason.org> Date: Tue, 28 Nov 2000 22:39:13 +0100 (CET) On Tue, 28 Nov 2000, Stas Bekman wrote: > > I have a huge project with lots of tables, and the performance wasn't that > > well. So I've started to review the tables definitions and have found that > > some indices were missing. I was sick from doing the tracing of all > > possible SQL calls manually, so I wrote this simple profiler. Take a look > > and tell me if you think it worths releasing on CPAN... > > Try DBIx::Profile. I've had great success with it. Ouch, I was checking the wrong namespace DBI:: and that's why I've missed it. Why DBIx? Looks like it does pretty much the same but returns too much info, which makes it quite hard to use when you have 100+ queries in some requests :) And fetch()es are quite irrelevant for performance improvements since they never change unless you compare TCP/IP vs UNIX sockets or one driver against the other. My aim was to have one to tune the code when I'm in a given environment... The only problem with DBIx::Profile is that you have to turn Apache::DBI off, since DBIx::Profile overrides disconnect() as well. I suppose if it's still desirable for my hack to go in, it should be at least ProfSimple or ProfQuickDirty :) I also think that it should go into DBI:: tree, since it doesn't use the framework of DBIx:: classes. Or is there any reason for not using DBI::? === Subject: Re: RFC: DBI::Prof From: Matt Sergeant <matt@sergeant.org> Date: Tue, 28 Nov 2000 21:46:25 +0000 (GMT) On Tue, 28 Nov 2000, Stas Bekman wrote: > Or is there any reason for not using DBI::? Tim mandates it. DBI:: is reserved for DBI only. DBD::* is reserved for DBD drivers only, anything else goes in DBIx. === Subject: Re: RFC: DBI::Prof From: Matt Sergeant <matt@sergeant.org> Date: Tue, 28 Nov 2000 22:26:54 +0000 (GMT) On Wed, 29 Nov 2000, Fabrice Scemama wrote: > It would be nice if Tim Bunce simply added it as a parameter > to DBI's existing methods. Why not ask him? I think most people would prefer to see it as a separate module. Generally people do their query optimisations outside of DBI (and Perl), using the database's in-built profilers. === Subject: Re: RFC: DBI::Prof From: Perrin Harkins <perrin@primenet.com> Date: Tue, 28 Nov 2000 14:41:22 -0800 (PST) On Tue, 28 Nov 2000, Stas Bekman wrote: > Looks like it does pretty much the same but returns too much info, which > makes it quite hard to use when you have 100+ queries in some requests :) I suspect it would be pretty easy to add in a threshold like the one in your module. > And fetch()es are quite irrelevant for performance improvements since they > never change unless you compare TCP/IP vs UNIX sockets or one driver > against the other. I find the fetch information useful when deciding whether to do a more complex query that retrieves fewer results or a simple one that retrieves extra data and then sift through it in perl. You could probably modify DBIx::Profile to support a flag for turning this off. > The only problem with DBIx::Profile is that you have to turn Apache::DBI > off, since DBIx::Profile overrides disconnect() as well. I didn't turn Apache::DBI off and things still seemed to work. === Subject: Re: RFC: DBI::Prof From: Stas Bekman <stas@stason.org> Date: Tue, 28 Nov 2000 23:44:28 +0100 (CET) On Tue, 28 Nov 2000, Matt Sergeant wrote: > On Wed, 29 Nov 2000, Fabrice Scemama wrote: > > > It would be nice if Tim Bunce simply added it as a parameter > > to DBI's existing methods. Why not ask him? > > I think most people would prefer to see it as a separate module. Generally > people do their query optimisations outside of DBI (and Perl), using the > database's in-built profilers. This one is not about optimizing the database, but finding the missing indices mostly and seeing which queries might need to be rewritten to make a better use of the driver/db. I don't know how can you do that without actually running your application, which means DBI/Perl. I suppose that if someone will send a necessary patch to Tim he might put it in or not... I'm fine with any of Tim's decisions. === Subject: RE: RFC: DBI::Prof From: Henrik Tougaard <ht000@foa.dk> Date: Wed, 29 Nov 2000 08:50:22 +0100 From: Perrin Harkins [mailto:perrin@primenet.com] > On Tue, 28 Nov 2000, Stas Bekman wrote: > > And fetch()es are quite irrelevant for performance > improvements since they > > never change unless you compare TCP/IP vs UNIX sockets or one driver > > against the other. > > I find the fetch information useful when deciding whether to do a more > complex query that retrieves fewer results or a simple one > that retrieves > extra data and then sift through it in perl. For some drivers (DBD::Ingres for one) the $sth->execute only optimizes the query, the data is fetched in the first call to fetch. Fetching the first row does all the "real" work, joining and sorting etc. So you will se a very fast prepare time, a not-too-long execute time, and (in some cases) a horribly long fetch-time for the first fetch. === Subject: Re: RFC: DBI::Prof From: Tim Bunce <Tim.Bunce@ig.co.uk> Date: Wed, 29 Nov 2000 12:16:18 +0000 On Tue, Nov 28, 2000 at 11:44:28PM +0100, Stas Bekman wrote: > On Tue, 28 Nov 2000, Matt Sergeant wrote: > > > On Wed, 29 Nov 2000, Fabrice Scemama wrote: > > > > > It would be nice if Tim Bunce simply added it as a parameter > > > to DBI's existing methods. Why not ask him? > > > > I think most people would prefer to see it as a separate module. Generally > > people do their query optimisations outside of DBI (and Perl), using the > > database's in-built profilers. > > This one is not about optimizing the database, but finding the missing > indices mostly and seeing which queries might need to be rewritten to make > a better use of the driver/db. I don't know how can you do that without > actually running your application, which means DBI/Perl. > > I suppose that if someone will send a necessary patch to Tim he might put > it in or not... I'm fine with any of Tim's decisions. I've been planning to add some internal stats gathering triggered by a simple global int (so the cost when turned off would be effectively zero). Code bloat would also be tiny if done right (far smaller than existing trace code for example). Basically just recording a high-res timestamp for the start and end of a small set of significant events (prepare, execute, fetch first row, last row, ...) and some very basic reporting if trace is enabled. Meanwhile I'm happy for people to experiment with the practical issues in the DBIx::* namespace. Modules could be updated to use the new stats when they're available. === Subject: Antwort: RFC: DBI::Prof From: Michael.Jacob@gad.de Date: Thu, 30 Nov 2000 14:37:59 +0100 0__=wqB5KRc5bGakGIOTv1T728HggmJVq4HD0u7ek49Hy6bIvhPhOSX0Mdkx Content-type: text/plain; charset=us-ascii Content-Disposition: inline Hi, I'm not quite sure, but I think the following would produce wrong results, wouldn't it? $sth1 = $dbh->prepare(...); $sth2 = $dbh->prepare(...); $sth1->execute(); $sth3 = $dbh->prepare(...); $sth2->execute(); $sth3->execute(); Michael Jacob Datum: 28.11.2000 21:12 An: mod_perl list <modperl@apache.org> Betreff: RFC: DBI::Prof Nachrichtentext: I have a huge project with lots of tables, and the performance wasn't that well. So I've started to review the tables definitions and have found that some indices were missing. I was sick from doing the tracing of all possible SQL calls manually, so I wrote this simple profiler. Take a look and tell me if you think it worths releasing on CPAN... hmm, why mod_perl list... because it works under mod_perl :) In fact I didn't test it under non mod_perl but it should work as well :) Anyway, enjoy :) === Subject: Re: Antwort: RFC: DBI::Prof From: Stas Bekman <stas@stason.org> Date: Fri, 1 Dec 2000 02:37:47 +0100 (CET) On Thu, 30 Nov 2000 Michael.Jacob@gad.de wrote: > Hi, > > I'm not quite sure, but I think the following would produce wrong results, > wouldn't it? > > $sth1 = $dbh->prepare(...); > $sth2 = $dbh->prepare(...); > $sth1->execute(); > $sth3 = $dbh->prepare(...); > $sth2->execute(); > $sth3->execute(); That's correct. So it's kinda disqualifies my hack to be placed on CPAN. At this moment I don't have the tuits to make it a non-hack and work for everybody, so I'll just leave it as it is in mod-perl list archive. May be I'll put it into the guide... It would be much easier for Tim to do it from the inside than any of us doing the overloading hacking, but that's up to Tim to decide when if ever this should go in :) === Subject: Re: Antwort: RFC: DBI::Prof From: Tim Bunce <Tim.Bunce@ig.co.uk> Date: Fri, 1 Dec 2000 09:29:33 +0000 On Fri, Dec 01, 2000 at 02:37:47AM +0100, Stas Bekman wrote: > > It would be much easier for Tim to do it from the inside than any of us > doing the overloading hacking, but that's up to Tim to decide when if ever > this should go in :) Things are changing for the better workwise now and I hope to get back to regular DBI and DBD::Oracle (and Oracle::OCI) work early next year. Meanwhile, I'll happily guide someone who's willing and mostly able to create a patch for DBI internals. It's shouldn't be too hard. ===