Click here to Skip to main content
Click here to Skip to main content
Technical Blog

Tagged as

Debugging and Profiling MySQL Performance with PHP

, 16 Jun 2014 CPOL
Rate this:
Please Sign up or sign in to vote.
Debugging and Profiling MySQL Performance with PHP

We already talked about PHP performance profiling by using the handy built-in xdebug profiler and passing the XDEBUG_PROFILE parameter. You can find the original blog article here. But, that method does not give you an easy way to view the SQL calls you are making or the individual performance of those calls. To add a SQL performance profile to your database calls, we will create a wrapper class that will intercept each query and store the timing information and call that is being made. This wrapper is a drop in replacement for the original mysqli calls.

<?php

/** - Database debug helper

 * This is a wrapper class for FirePHP or other logging system
 * 

 * USAGE: use debug_mysql in place of mysqli:

 * global $is_dev;
 * $is_dev = true;
 * global $debug_sql_flag;
 * $debug_sql_flag = true;
 * $dbHandle = new debug_mysql("host_ip", "user", "pw", "dbname");
 * $dbHandle->set_charset("utf8"); //optional
 * $sql ="SELECT * FROM tablename";
 * $query_result = $dbHandle->query($sql, __FILE__, __METHOD__, __LINE__);

 *
 * @author sbossen
 */

class debug_mysql extends mysqli {

    const log_file = "/var/log/firephp.log";

    public function query($sql, $file = "", $method = "", $line = "") {

        global $is_dev; //indicates if this is a development system
        global $debug_sql_flag; //indicates if we are processing debug log events

        if ($is_dev) {
            $query_start = microtime(true);
            $result = parent::query($sql);
            $query_end = microtime(true);
            $query_time = $query_end - $query_start;

            if ($debug_sql_flag) {
                if ($query_time > 10) {
                    $speed = "slow";
                } else {
                    $speed = "normal";
                }
                if (!$result) {
                    $err = "ERROR: $this->error";
                } else {
                    $err = "Ok";
                }
                $db = $this->get_database_name();
                self::log_to_file("QUERY: $query_time ms, $speed, $db, $sql, $err", $file,$method,$line);
            }
            return $result;
        } else {
            //pass original query through if this is a production system
            return parent::query($sql);
        }
    }

    /**
     * Get name of the database that is currently selected
     * @return string - database name
     */
    public function get_database_name() {
        $str = "{unknown db}";
        if ($result = parent::query("SELECT DATABASE()")) {
            $row = $result->fetch_row();
            $str = $row[0];
            $result->close();
        }
        return $str;
    }

    /**
     * @param type $str - message to output
     * @param type $file - file that the method was called from
     * @param type $method - method that was called
     * @param type $line - line method was called from
     */
    public static function log_to_file($str, $file = "", $method = "", $line = "") {
        file_put_contents(self::log_file, $file."::".$method."[".$line."] - \"".$str."\"\n", FILE_APPEND);
    }

}
?>

This gives you a log file that stores all of your SQL commands and can be imported as a CSV file into Excel for sorting or other analysis. SQL error messages are captured along with the location in the code the call was made from (provided you pass them along).

If you are running the server on Linux, you can simply open a shell session and run:

tail -f /var/log/firephp.log

You can watch this terminal window show all your queries along with the time it takes to execute each query. Any SQL errors that are found will display along with the error information. This helps for quick debugging if you just want to find out if your queries are running properly.

Instead of displaying to a log file, it can be useful to output the log to FirePHP. FirePHP allows you to display debug information to your Firebug console through the Firefox web browser. It can certainly be handy if you don't want to or can't open up a shell connection. Install FirePHP using pear as described on the FirePHP website and install the FirePHP Firefox extension to your browser.

To display to FirePHP instead of a file, replace the log_to_file() function with the code below. This way, you can have SQL code show right in your browser.

public static function log_to_file
($str, $file = "", $method = "", $line = "") {
    $firephp = FirePHP::getInstance(true);

    $firephp->log($file."::".$method."[".$line."] - \"".$str."\"");
} 

Always be careful not to show debug information on a production system. At best, it is a sloppy practice and at worst, it can reveal sensitive system data.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Right Handed Monkey
Software Developer WorxForUs
United States United States
I am a programmer who posts rambling on about java, Android, PHP, or whatever I am motivated to type on my charcoal colored Kinesis Freestyle2 keyboard. Please send +1's, shared links, warm thoughts of encouragement, or emasculating flames of internet fury to my blog. Why not? In fact, say anything... but please don't say 'thank'. I don't know, but something about it makes my skin crawl like an electric eel is asking to give me a kiss. No, thanks. (See there's an 's' in there. Was that really so hard?
 
right-handed-monkey.blogspot.com

Comments and Discussions

 
GeneralMy vote of 1 Pinmemberoliver.stockleyOpenGI17-Jun-14 21:50 
GeneralRe: My vote of 1 PinmemberRight Handed Monkey27-Jun-14 1:21 
QuestionI think you posted the wrong body text? Pinmemberdemotis17-Jun-14 6:38 
AnswerRe: I think you posted the wrong body text? PinmemberRight Handed Monkey17-Jun-14 10:20 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Mobile
Web04 | 2.8.141022.2 | Last Updated 16 Jun 2014
Article Copyright 2014 by Right Handed Monkey
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid