Re: Antwort: RFC: DBI::Prof
On Fri, Dec 01, 2000 at 12:23:26PM +, Greg Cope wrote: > Tim Bunce wrote: > > Although a bit OT, but I am sure everyone is interested, what changes > are you planning for DBI ? There's a ToDo file in the dist. I've probably a few others rattling around in my head. Tim. - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: Antwort: RFC: DBI::Prof
Tim Bunce wrote: > > On Fri, Dec 01, 2000 at 09:48:34AM +, Matt Sergeant wrote: > > On Fri, 1 Dec 2000, Tim Bunce wrote: > > > > > 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. > > > > You said that at TPC :-) > > Yeah, well... there are plans and there are plans :) > > I recently gave notice to the company that I've been Technical Director > of for many years that I'll be leaving in March 2001. Such big changes > of direction take time to work up to and work out smoothly. I am > specifically rearranging things so I have time for DBI related work. Very interesting. Although a bit OT, but I am sure everyone is interested, what changes are you planning for DBI ? Greg > > Tim. > > - > To unsubscribe, e-mail: [EMAIL PROTECTED] > For additional commands, e-mail: [EMAIL PROTECTED] - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: Antwort: RFC: DBI::Prof
On Fri, Dec 01, 2000 at 09:48:34AM +, Matt Sergeant wrote: > On Fri, 1 Dec 2000, Tim Bunce wrote: > > > 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. > > You said that at TPC :-) Yeah, well... there are plans and there are plans :) I recently gave notice to the company that I've been Technical Director of for many years that I'll be leaving in March 2001. Such big changes of direction take time to work up to and work out smoothly. I am specifically rearranging things so I have time for DBI related work. Tim. - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: Antwort: RFC: DBI::Prof
On Fri, 1 Dec 2000, Tim Bunce wrote: > 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. You said that at TPC :-) -- /||** Director and CTO ** //||** AxKit.com Ltd ** ** XML Application Serving ** // ||** http://axkit.org ** ** XSLT, XPathScript, XSP ** // \\| // ** Personal Web Site: http://sergeant.org/ ** \\// //\\ // \\ - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: Antwort: RFC: DBI::Prof
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. Tim. - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: Antwort: RFC: DBI::Prof
On Thu, 30 Nov 2000 [EMAIL PROTECTED] 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 :) > Michael Jacob > > > Datum: 28.11.2000 21:12 > An:mod_perl list <[EMAIL PROTECTED]> > > > 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 :) > > > _ > Stas Bekman JAm_pH -- Just Another mod_perl Hacker > http://stason.org/ mod_perl Guide http://perl.apache.org/guide > mailto:[EMAIL PROTECTED] http://apachetoday.com http://jazzvalley.com > http://singlesheaven.com http://perl.apache.org http://perlmonth.com/ > > > _ Stas Bekman JAm_pH -- Just Another mod_perl Hacker http://stason.org/ mod_perl Guide http://perl.apache.org/guide mailto:[EMAIL PROTECTED] http://apachetoday.com http://jazzvalley.com http://singlesheaven.com http://perl.apache.org http://perlmonth.com/ - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Antwort: RFC: DBI::Prof
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 <[EMAIL PROTECTED]> 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 :) _ Stas Bekman JAm_pH -- Just Another mod_perl Hacker http://stason.org/ mod_perl Guide http://perl.apache.org/guide mailto:[EMAIL PROTECTED] http://apachetoday.com http://jazzvalley.com http://singlesheaven.com http://perl.apache.org http://perlmonth.com/ Prof.pm - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: RFC: DBI::Prof
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. Tim. - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
RE: RFC: DBI::Prof
> From: Perrin Harkins [mailto:[EMAIL PROTECTED]] > 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. -- Henrik Tougaard, [EMAIL PROTECTED] DBD::Ingres maintainer. - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: RFC: DBI::Prof
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. _ Stas Bekman JAm_pH -- Just Another mod_perl Hacker http://stason.org/ mod_perl Guide http://perl.apache.org/guide mailto:[EMAIL PROTECTED] http://apachetoday.com http://jazzvalley.com http://singlesheaven.com http://perl.apache.org http://perlmonth.com/ - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: RFC: DBI::Prof
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. - Perrin - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: RFC: DBI::Prof
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. -- /||** Director and CTO ** //||** AxKit.com Ltd ** ** XML Application Serving ** // ||** http://axkit.org ** ** XSLT, XPathScript, XSP ** // \\| // ** Personal Web Site: http://sergeant.org/ ** \\// //\\ // \\ - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: RFC: DBI::Prof
It would be nice if Tim Bunce simply added it as a parameter to DBI's existing methods. Why not ask him? Fabrice Aaron Ross wrote: > > On Tue, 28 Nov 2000, Stas wrote: > > possible SQL calls manually, so I wrote this simple profiler. Take a look > > and tell me if you think it worths releasing on CPAN... > > Definitely release it! It is a very elegant solution to a problem that I'm > guessing many of us have dealt with. I've always _tried_ to write a db > abstraction layer, so this kind of profiling would be easy. But I can easily > think of two cases where i was trying to track down bad queries and this > little trick would have saved me a lot of time. > > [ couldn't you have telepathically told me how to do this a year ago?? ] > > it would be a nice addition to the guide too. > > aaron - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: RFC: DBI::Prof
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. -- /||** Director and CTO ** //||** AxKit.com Ltd ** ** XML Application Serving ** // ||** http://axkit.org ** ** XSLT, XPathScript, XSP ** // \\| // ** Personal Web Site: http://sergeant.org/ ** \\// //\\ // \\ - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: RFC: DBI::Prof
> 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::? Thanks! _ Stas Bekman JAm_pH -- Just Another mod_perl Hacker http://stason.org/ mod_perl Guide http://perl.apache.org/guide mailto:[EMAIL PROTECTED] http://apachetoday.com http://jazzvalley.com http://singlesheaven.com http://perl.apache.org http://perlmonth.com/ - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: RFC: DBI::Prof
On Tue, 28 Nov 2000, Stas wrote: > possible SQL calls manually, so I wrote this simple profiler. Take a look > and tell me if you think it worths releasing on CPAN... Definitely release it! It is a very elegant solution to a problem that I'm guessing many of us have dealt with. I've always _tried_ to write a db abstraction layer, so this kind of profiling would be easy. But I can easily think of two cases where i was trying to track down bad queries and this little trick would have saved me a lot of time. [ couldn't you have telepathically told me how to do this a year ago?? ] it would be a nice addition to the guide too. aaron - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: RFC: DBI::Prof
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. - Perrin - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
RFC: DBI::Prof
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 :)
_
Stas Bekman JAm_pH -- Just Another mod_perl Hacker
http://stason.org/ mod_perl Guide http://perl.apache.org/guide
mailto:[EMAIL PROTECTED] http://apachetoday.com http://jazzvalley.com
http://singlesheaven.com http://perl.apache.org http://perlmonth.com/
package DBI::Prof;
use Apache::Constants qw(DECLINED OK);
use Time::HiRes ();
my %results = ();
my $statement ='';
$DBI::Prof::THRESHOLD = 0.01;
my $sub_execute = \&DBI::st::execute;
eval q{
sub DBI::st::execute{
my $start_time = [ Time::HiRes::gettimeofday ];
my $res = &$sub_execute;
my $end_time = [ Time::HiRes::gettimeofday ];
my $elapsed = Time::HiRes::tv_interval($start_time,$end_time);
$results{$statement} = $elapsed;
$statement = '';
return $res;
}
};
my $sub_prepare = \&DBI::db::prepare;
eval q{
sub DBI::db::prepare{
$statement = $_[1];
&$sub_prepare;
}
};
sub report{
my $r = shift;
print STDERR "Queries with execute() time > $DBI::Prof::THRESHOLD secs\n";
my $total_time= 0;
my $total_queries = 0;
for (sort {$results{$b} <=> $results{$a}} keys %results) {
$total_time += $results{$_};
$total_queries++;
next if $results{$_} < $DBI::Prof::THRESHOLD;
print STDERR "$results{$_} $_;\n"
}
print STDERR "Total elapsed execute() time: $total_time\n";
print STDERR "Total number of queries: $total_queries\n\n";
# reset the values
%results = ();
$statement = '';
return OK;
}
1;
__END__
=head1 NAME
DBI::Prof -- Benchmark the $sth->execute() calls to find slow queries and adjust the
table indices.
=head1 SYNOPSYS
Under normal Perl code:
use DBI ();
use DBI::mysql (); # or another driver
use DBI::Prof ();
...your code that queries some DB...
DBI::Prof::report();
Under mod_perl:
PerlModule DBI
PerlModule DBI::mysql # or another driver
PerlModule DBI::Prof
PerlLogHandler DBI::Prof::report
This module must be loaded after C (or another driver) is
loaded. Not DBI, but the driver, since C overrides the
execute() and prepare() calls.
=head1 DESCRIPTION
This module allows you to measure the execute() time of all the DBI
queries and thus adjust your code/tables/indices to work faster. It
also reports the total number of queries that were executed and the
total execute() time.
You can modify the C<$DBI::Prof::THRESHOLD> variable to print only
SQLs which were taken more than C<$DBI::Prof::THRESHOLD> seconds to
execute. Notice that the measured time is not the exact time that it
took for sql engine to execute the statement, but a little bit higher.
The output goes at the end of each request to the error_log and only
queries that it took longer than C<$DBI::Prof::THRESHOLD> secs will be
listed, sorted from the longest to the slowest.
I repeat, this is the measurement of the execute() and not
execute()+fetch() which is obviosly longer. So it's mostly useful for
finding queries which don't use indices and therefore very slow.
=head1 AUTHOR
Stas Bekman <[EMAIL PROTECTED]>
=cut
-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
