PHP Trivandrum

Robin Schumacher, MySQL’s Director of Product Management explains about the MySQL Query Profiler through his article on Using the New MySQL Query Profiler. At first glance I saw it was a good thing, and when I went deep to my dismay I found that the profile information was stored into an in memory table. Which means profile information would be lost when a session is closed. Well I was boiling my brains over the desire to profile applications which were live. And about a couple of days later, it struck my brains, that in any php application, if we open a connection to a database, normally the subsequent requests with the same credentials would return the active connection than opening a new connection. Though a new connection can be forced. And with all these information and the documentation on MySQL Developer Resources, I thought about writing one php snippet which would help me do sql profiling, and get the analysis in a colourful format.

Open PHP MyProfiler is just a trial to run query profiling on a php-mysql application, without changing the architecture too much. The profiler is open and downloadable, though the analzer, is just a mockup or a bare one and not yet ripe to be opened up. Any one who needs to do the analysis could make use of the same by downloading the profiler, and implementing with their code. The profiler would create logs depending on hostname and date. upload the profile logs to our Profile Sampler and you should be able to see the full profile of your application. Analyze your profile logs by going here.

Integration into an application should be quite simple, as including into the header or config, or just any where after the database configuration is defined, and then immediately creating a global $profiler. In the footer, or where any database activities are over, just call the $profiler->log() method. Though for some open packages there are some quirks, most of the applications this should be enough. Normally php would be getting the last open connection with the same parameters. Like for instance the wordpress, in wp-db.php, the connection request forces a new connection always. Just for profiling in some cases, I had to change this code.

Basically we start a connection to the database, and set the session variable profiling=1, and the profiling_history_size=100 which is the maximum. Means the profiler will work only for applications which do a maximum of 100 queries per instance of execution. The wordpress suite with some simple plugins can attain upto 75 queries and above. Then just before tearing down the connection, we collect all the profile information, serialize and store into a text file. There is a commercial version planned which would use curl to post each connection information directly to a central server, where the analysis and parsing would be done. The text file should be submitted to our profile sampler to view the profile information with color coding, though the color scale is a bit too colorful. Wrote it as a php5 class, since that would be eaiser to maintain.

Update
As Fernando Ipar has summarized in his comment, we have a resemblance in the final outcome, where as his solution which is described in his article Using MySQL Proxy to benchmark query performance, inclines more towards having greater system administration knowledge, and requires installation of MySQL Proxy. And as he has commented, my project Open PHP-MyProfiler would make it simple to be used on shared hosting accounts. While this simplicity does make way for a handicap, and that is the limitation of the profiles. Page instances which run more than 100 queries would end up providing us with the last 100 query performance only. As of today the coders in our lab has found one feature, since the profile entries are logged related to a single url, they could identify a lot of unnecessary queries in many pages.

<?php
/***
 * parameter $log should be a directory with the trailing slash, since the logs would be real heavy
 * and as of now the directory should be writable by the webserver process.. 
 * a file will be created under the directory foreach host with value in $_SERVER['HTTP_HOST']
 */
class phpMyProfiler
{
    private $link;
    private $error;
    private $log;
    
    function __construct($host, $user, $pass, $log = false){
        $this->log = $log;
        if(!defined('PROFILE_ONLY')) return;
        if($_SERVER['HTTP_X_FORWARDED_FOR'] != PROFILE_ONLY and $_SERVER['REMOTE_ADDR'] != PROFILE_ONLY) return;
        if(!$this->log) return;
        $this->link = mysql_connect($host, $user, $pass) or die(mysql_error());
        $this->startProfiling();
    }
    function __destruct(){
        $this->log();
    }
    private function startProfiling(){
        mysql_query("set profiling_history_size=100", $this->link);
           mysql_query("set profiling=1", $this->link);
    }
    private function stopProfiling(){
        mysql_query("set profiling=0", $this->link);
    }
    
    private function collectData(){
        $rv = array();
        $rs = mysql_query("show profiles", $this->link);
        while($rd = mysql_fetch_assoc($rs)){
            if($rd['Query_ID'] == 0) continue;
            if($detail = $this->getDetails($rd['Query_ID']))
                $rd['detail'] = $detail;
            $rv[] = $rd;
        }
        return $rv;
    }
    
    private function getDetails($qid){
            $rsd = mysql_query("select min(seq) seq,state,count(*) numb_ops, "
                . "round(sum(duration),5) sum_dur, round(avg(duration),5) avg_dur, "
                . "round(sum(cpu_user),5) sum_cpu, round(avg(cpu_user),5) avg_cpu "
                . "from information_schema.profiling "
                . "where query_id = " . $qid
                . " group by state order by seq", $this->link);
            $rsv = array();
            while($rdd = mysql_fetch_assoc($rsd))
                $rsv[] = $rdd;
            return $rsv;
    }
    
    public function log(){
        if(!$this->link or !$this->log)
            return;
        $this->stopProfiling();
        $data['instance'] = array('timestamp' => time(), 'request' => $_SERVER['REQUEST_URI' ]);
        $data['profiles'] = $this->collectData();
        if(empty($data['profiles']) or count($data['profiles']) == 0) return;
        $logFile = $this->log . $_SERVER['HTTP_HOST'] .'-' . date("Ymd") . '.log';
        $logData = base64_encode(gzcompress(serialize($data))) . "\n";
        if(!file_exists($path)){
            file_put_contents($path, '#PhpMyProfiler' . "\n");
        }
        file_put_contents($logFile, $logData, FILE_APPEND | LOCK_EX);
        $this->log = false; // dont want to call a second time
    }
}
?>

phpMyProfiler now supports mysqli

Since a lot of developers have switched to the improved library, mysqli the old version did not support the mysqli. Due to popular demand, and requirement we updated the profiler with necessary mods and the improved version is available for download here. For the benefit of usage, the profiling should happen on the same connection resource, and a new method phpMyProfiler::setLink() is provided which takes one parameter byRef the mysqli connection resource. We had to retain the compatiablity, since most of our coders at Saturn were familiar with the last version.
phpMyProfiler(i) (21)