Re: [zeta-dev] Profiler for db component

[Andreas Schamberger <ma...@andreass.net>]

Hi,

Am 29.08.2010 14:16, schrieb Gaetano Giunta:
> Andreas Schamberger wrote:
>> Hi,
>>
>> I already posted this a year ago on the old ezc mailing list. There
>> was no feedback. So I'll start another attempt here ...
>>
>> I was annoyed by not being able to analyze my query execution times.
>> What I want is a profiler that transparently collects the data without
>> adding anything to my code base. My solution: A new ezcDbHandler
>> implementation as a proxy for the real db handler instance. The new db
>> handler configures a custom PDOStatement that collects the profiling
>> information and a backtrace of the query.
>>
>> I also added the ability to get a prepared SQL string by caching the
>> bindValue/bindParam data in the custom statement class to get a
>> complete query for debugging. This is also great for doing EXPLAIN by
>> just copy/pasting the queries.
>>
>> It was developed with the objective of zero changes to the existing db
>> component code.
>>
>> The profiler can simply be added like this:
>>
>> $dsn = 'mysql://....';
>> if ( ezcBase::inDevMode() )
>> {
>> ezcDbInstance::set( ezcDbFactory::create( $dsn ),
>> 'mysql_for_profiler' );
>> $dbh = ezcDbFactory::create( 'profiler://mysql_for_profiler' );
>> // uses ezcDbInstance::get( 'mysql_for_profiler' ) internally
>> }
>> else
>> {
>> $dbh = ezcDbFactory::create( $dsn );
>> }
>> ezcDbInstance::set( $dbh );
>>
>> // ...
>>
>> $dbh = ezcDbInstance::get();
>> if ( ezcBase::inDevMode() && $dbh instanceof ezcDbProfiler )
>> {
>> // dump the array of ezcDbProfilerData structs
>> var_dump( $dbh->getProfilingInformation() );
>> }
>>
>> The new handler implements:
>>
>> interface ezcDbProfiler
>> {
>> public function getProfilingInformation();
>> public function logProfilingInformation( $query, $duration,
>> $group=null );
>> // group is used to group by unprepared statements
>> }
>>
>> I'd like to contribute the attached profiler code if there is interest
>> in it.
>>
>> Regards,
>> Andreas
>
> A couple of notes:
>
> . the replacing of 'SELECT' in mysql selects in db_handler_profiler.php
> might be too greedy: what about columns called SELECTOR?
>
> . baking in support for EXPLAIN PLAN into this class (or a subclass)
> would make it more interesting. You can take a look at ezdb: it already
> implements 'explain' for both mysql and oracle
>
> bye
> Gaetano
>

I added proper regular expressions to replace the SELECT and I had a 
look at the Oracle explain plan (although I only added the queries 
without testing them).

There are two rendering possibilities now (screenshots attached):
- HTML
- FirePHP/DeveloperCompanion

The FirePHP/DeveloperCompanion is very appealing as it requests data 
only on demand when it is activated and the output doesn't interfere 
with the webpage.

The DeveloperCompanion (commercial) has additional possibilities to 
inspect the backtraces and supports conditional logging which allows to 
activate/deactivate displaying the explain result and the backtraces.

In the attached ZIP you can find the latest code. The index.php is the 
example script used for the screenshots.

Regards,
Andreas