Open PHP-MyProfiler

October 15th, 2014 Leave a comment Go to comments

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.
phpMyProfiler(i) (1058)

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 localhost. Download the sampler PHP-MyProfiler Sampler (358)

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 PHP MyProfiler Wordpress Plugin (649)

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. Open PHP-MyProfiler (26)

  1. April 14th, 2009 at 19:32 | #1

    Hey there,

    Very interesting project. I’m working on a lua script for mysql-proxy that has some overlapping functionality but requires the installation of (obviously) mysql-proxy.

    Your alternative is excellent for people working on the LAMP stack in shared hosting environments, where installation of non-standard software is usually impossible.

    Keep up the good work and let’s keep an eye on each other!

  2. May 31st, 2009 at 11:01 | #2

    http://www.php-trivandrum.org/profile-sampler/?localfile=www.articleclick.com-20090525.log

    The system helped me to identify one bottleneck in slow generating pages, though with the help of the system author Jiju.

  3. soussou97
    March 10th, 2010 at 20:39 | #3

    Hi;

    I realize of the load tests and would like to know how I can used this profiler ?

    regards

  4. March 10th, 2010 at 23:38 | #4

    @soussou97
    The answer is written in my post, anyway to repeat the stuff.. “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” with the same parameters that your scripts use to connect to the database. Should make sure that the connection your existing code makes is not forcing a new connection on each invokation.

  5. Ed
    October 28th, 2010 at 22:29 | #5

    Hi there,

    I had a go with this class but I can’t get any info at all from ‘show profiles’ query… if I do a var_dump($data) in function log() I would get this:
    array(2) { ["instance"]=> array(2) { ["timestamp"]=> int(1288283475) ["request"]=> string(18) “/profiler/test.php” } ["profiles"]=> NULL }

    at the end of __contructor I added:
    mysqli_query($this->link,”SELECT 1+1″);
    just to make sure there is an executed query

    and my test.php is

    I have mysql 5.1.37 / php 5.3.0

    Are there any other settings I must look after to enable profiling??

    Thank you

  6. December 31st, 2010 at 13:17 | #6

    It seems that your mysql is not supporting the profiler, and may by I did not verify that in my script. Will check this up when I get the time.

  7. zan
    May 3rd, 2011 at 16:27 | #7

    I can’t get the code to log.
    I declare it in the top section of my page:

    require_once SiteDIR.’class/profiler/class.profiler.php';
    $profiler = new phpMyProfiler(“localhost”,”pmng_db”,”NhUSfGsUtRuG2uFr”);

    Then in the footer:
    log(“/class/profiler/”) ?>

    No errors until this point, but the code seems not to go further than the first if statements. If I delete these if/return lines, the code gives me this error:
    Warning: mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource in /usr/local/hosting/isgpmng/incoming/class/profiler/class.profiler.php on line 35

    What to do?

  8. May 7th, 2011 at 22:07 | #8

    @zan
    Anyway no need to put the log() at the footer, though the logfile path should be passed to the constructor, as

    $profiler = new phpMyProfiler(”localhost”,”pmng_db”,”NhUSfGsUtRuG2uFr”, “/path/to/logfile.log”);

    Logging will be taken care of by the destructor

    And the file or path should be writable by the webserver process. But none of this is your error case, I think the mysql does not provide profiling support or may be it is started with profiler disabled.

  9. February 16th, 2012 at 23:27 | #9

    I was puzzled by the “PROFILE_ONLY” variable at the beginning of the script that is explained nowhere. I had to delete its tests.

    Then when I uploaded on the sampler page I got a puzzling screen as wasn’t sure about the difference between the three input fields Upload File, Remote File and Local File. I tried upload and local but I got only empty screens as a result.

    If I can help to maintain this code please tell me. This could be a very useful tool for many people.

  10. February 17th, 2012 at 07:39 | #10

    @WimR
    Sorry that I missed it, PROFILE_ONLY was defined when we first had to use it on a production environment, and did not want all the requests to be profiled. Just defined the PROFILE_ONLY with our own public IP and did the sampling.
    I will send you the sampler code, the file uploads does not work here, since I had a scary hack some time back, and certain features of php is being restricted now.

  11. February 20th, 2012 at 00:20 | #11

    @jijutm
    Thank you for the code and the explanation. I have two more questions:
    – long url’s are not rendered completely. They seem to be cut off at around 280 characters. Is there anything one can do about that.
    – the profiling table in Information_schema has also fields source_file and source_line. I wondered if these can be gotten somewhere too in the output.
    I had a look for these things myself but I found it a bit too complicated for the moment.
    Thanks, Wim

  12. February 21st, 2012 at 09:19 | #12

    Thanks for pointing out the things.. Will try to find time over the weekend and get back to you.

  13. February 26th, 2012 at 20:57 | #13

    @WimR
    It seems that the source_file and source_line are there along with that the source_function is also seen, without all these I thought it would not be quite useful. The source file is relative in some version, and in some other the width of this field is varchar(20). As is said here http://bugs.mysql.com/bug.php?id=59273, these were corrected and updated in 5.5 and 5.6. As of now it does not seem to be worth the trouble to pick these.
    Meanwhile I could not reproduce the issue about long urls, since the url is captured from REQUEST_URI, all the url should be there. Could you send me a downloadable link to a profile log, or send me one as attachment, make it as con-size as you can. You should have my email id.

  14. Mark
    April 2nd, 2013 at 23:10 | #14

    Easy way to profile site is using special tool Neor Profile SQL http://www.profilesql.com

  15. April 4th, 2013 at 08:54 | #15

    But what if profiling on a shared host?

  1. September 30th, 2009 at 22:43 | #1
  2. November 2nd, 2009 at 00:04 | #2

− seven = one