Open PHP-MyProfiler

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.

Update (Feb 12)
As Wimr has commented, I did miss out on the constant PROFILE_ONLY, and this was just to make sure we got only profiles from a single IP. And we could make sure that the profiling was done on the areas which we needed it to be done.

<?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$logDataFILE_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.
Download

News logged at php|architect
Beth Tucker Long, the Editor In Chief of php|architect magazine has logged a News about PhpMyProfiler on March 12, 2010. Read the words from the news page.

Profile Sampler
Due to a couple of compromising situations on my godaddy hosting, I was forced to disable some features of php and it seems that the hosted version of profile-sampler does not work. To facilitate the sampling, one can download the sampler and use it on his own web server or www.jijutm.com. Download the sampler Download

WordPress Plugin
The profiler and sampler has been bundled into a single download, but is now two wordpress plugins. Though this has issues that this will be plugged into the wordpress flow after init and that logging is done on shutdown hook, some of the database calls are never logged. Still that is not an issue as mostly slowness would be caused by database calls from the theme and plugins. Note that logging may make system more slow and this is for identifying the bottlenecks and should never be left on for long times. Features like Log requests from IP, Log requests from IP to specific URL, Enable/Disable, Log Frequency are in the wishlist. Currently when you enable, the profiler starts logging, and creates new file for every hour. This is stored into the UPLOAD_DIR/wp_php_my_profiler with <Year-month-date-hour>.log as filename, and posted into the $wpdb-posts table with post_status = private, and post_type = profiler_log, Once logging is done, and before going for analysis, better deactivate the Profiler. Log Analyzer creates a menu entry in the admin side, and shows the logs from the $wpdb->posts table with the above properties in a tabular form, with actions ‘Analyze’ and ‘Delete’. The plugin is provided for advanced users and debuggers to find issues and bottlenecks in their plugins or themes. Download the plugin Download

CodeIgniter Adoption
Recently at Saturn we have started to use CodeIgniter due to developers as well as clients. Some projects have been transferred to us for further maintenance which was also developed on top of CodeIgniter, which needed auditing. The outcome is that I could not resist creating a port of the popular Open PHP-MyProfiler such that it can be included into the CodeIgniter workflow without any hindrance. CodeIgniter gurus may be skeptical about extending the CI_Model for this purpose whereas the overall function would have been better off as a library. This override from best practices are just for utilizing the configuration from the system as well as creating a connection to the database with the same credentials. Also the model can be loaded well ahead of system initialization through autoloader configuration. This will require one custom configuration profiler_log which can be added to any of the config files like $config['profiler_log'] = '/tmp/';. Only that this should be a valid directory where the webserver can write files onto. On shared hosts, a new directory can be created for the same purpose. Analyzer is not built as a CodeIgniter port since the standard one is enough for analyzing the logs created by this one also. Though the download is named as CI_phpmyprofiler.php.zip, the model class is phpmyprofiler, so please be aware to rename the file if the unzip did not get it right. Download