Pablo Viquez Blog

Mi vida y cosas relacionadas

Skip to: Content | Sidebar | Footer

MySQL Profiling and Zend Framework

16 August, 2010 (19:48) | debug, mysql, PHP, Web Development | By: Pablo Viquez

You can do profiling on PHP using XDebug or Zend Debugger ad both works beautifully, however what about MySQL? Can you do something similar?

The response is yes you can. Now the best part is that you can take advantage of the tools MySQL give you, if you want to take the profiler to a new level.

ZF built-in profiler.

If you just care about the time that takes a certain query to execute and retrieve the data on your PHP application, then this might be the best choice for you.

Zend Framework provides a built in profiler for MySQL that keep tracks of the time that took a query to execute on your page and as a plus you can get the results displayed using Firebug (see: how to enable Firebug on ZF), the setup is quite simple and does not require rocket science to get it up and running.

To enable the MySQL profiling built in on ZF + FirePHP just modified the config file (assuming you’re using a ZF with Zend_Application):

config.ini

database.params.profiler.enabled = true
database.params.profiler.class = Zend_Db_Profiler_Firebug

That’s it! easy enough huh?

ZF built-in profiler preview

This is how it should look

MySQL Profiler Advanced

If you care about deep performance and want to see “real” MySQL profiling, which tells you what did MySQL engine actually did, and the time it took to performed each particular step, then this is your best choice.

You can enable profiling on MySQL itself, which you can later query and see what did the database actually did in order to get your data.

Applying the same plugin architecture, you need to write a new plugin and register it. In this case I’ll assume the following directory structure:

lib/
   MyApp/
      Controller/
         Plugin/
            MySQLProfiling.php
   zend/
    *

The MySQLProfiling.php will contain the necessary code to communicate with the MySQL DB server and get the data I want:

MySQLProfiling.php

<?php
/**
 * MySQL Profiling plugin
 *
 * Enables the profiling feature on MySQL server, queries it and
 * retrieves the nescesary data for each individual query.
 *
 * @category MyApp
 * @package MyApp_Controller
 * @subpackage MyApp_Controller_Plugin
 * @author Pablo Viquez <pviquez@pabloviquez.com>
 */
class MyApp_Controller_Plugin_MySQLProfiling
    extends Zend_Controller_Plugin_Abstract
{
    /**
     * Constant to use to diferentiate the queries that dont require
     * profiling
     *
     * @var string
     */
    const DONT_PROFILE = '/*DN_PROFILE*/ ';

    /**
     * Predispatch loop.
     *
     * Is called before an action is dispatched by the dispatcher. This
     * callback allows for proxy or filter behavior. By altering the
     * request and resetting its dispatched flag (via
     * Zend_Controller_Request_Abstract::setDispatched(false)), the
     * current action may be skipped and/or replaced.
     *
     * @return null
     */
    public function preDispatch(Zend_Controller_Request_Abstract $request)
    {
        // Init the profiler
        $db = GET_DB_CONNECTION_INSTANCE
        $db->query(self::DONT_PROFILE . 'set profiling=1');
        $db->query(self::DONT_PROFILE . 'set profiling_history_size=100');
    }

    /**
     * Postdispatch Loop.
     *
     * Is called after an action is dispatched by the dispatcher. This
     * callback allows for proxy or filter behavior.
     */
    public function postDispatch(
        Zend_Controller_Request_Abstract $request)
    {
        $columns =
            array(
                'Query ID',
                'Duration',
                'Executed Query');

        $profiles =
            $this->_constructFirePhp(
                $columns,
                'MySQL Profiling - Profiles');

        $columns =
            array(
                'Query ID',
                'SEQ',
                'State',
                'numb ops',
                'Total duration',
                'Avg duration',
                'Total CPU',
                'Avg CPU',
                'Avg CPU Sys',
                'Block Ops In',
                'Block Ops Out');

        // Do the MySQL profiling
        $queryIds = $this->_setProfiles($profiles);

        // Get the profiling for each individual query
        foreach ($queryIds as $id) {
            $queriesProfile =
                $this->_constructFirePhp(
                    $columns,
                    "MySQL Profiling - Query ID: {$id}");
            $this->_getProfileForQueryId($queriesProfile, $id);
        }
    }

    /**
     * Retrieves the query profile for each indidividual query
     *
     * @param Zend_Wildfire_Plugin_FirePhp_TableMessage $profiles
     * @param int $queryId
     * @return void
     */
    private function _getProfileForQueryId(
        Zend_Wildfire_Plugin_FirePhp_TableMessage $profiles,
        $queryId)
    {
        if (!ctype_digit((string)$queryId)) {
            return;
        }

        $query = self::DONT_PROFILE .
            "SELECT {$queryId},
                    MIN(seq),
                    state,
                    COUNT(*),
                    ROUND(SUM(duration),5),
                    ROUND(AVG(duration),5),
                    ROUND(SUM(cpu_user),5),
                    ROUND(AVG(cpu_user),5),
                    ROUND(AVG(cpu_system),5) ,
                    ROUND(AVG(block_ops_in),5),
                    ROUND(AVG(block_ops_out),5)
               FROM information_schema.profiling
              WHERE query_id = {$queryId}
           GROUP BY state
           ORDER BY seq";

        $db = GET_DB_CONNECTION_INSTANCE
        $sth = $db->query($query);
        $rows = $sth->fetchAll();

        foreach($rows as $row) {
            $values = array_values($row);
            $profiles->addRow($values);
        }
    }

    /**
     * Constructs and send the profiles table.
     *
     * @param Zend_Wildfire_Plugin_FirePhp_TableMessage $profiles
     * @return array Retrieves an array with the queries profiles
     */
    private function _setProfiles(
        Zend_Wildfire_Plugin_FirePhp_TableMessage $profiles)
    {
        // Get the profiles
        $db = GET_DB_CONNECTION_INSTANCE
        $sth = $db->query(self::DONT_PROFILE . 'show profiles');
        $rows = $sth->fetchAll();

        $queryIds = array();
        foreach($rows as $row) {
            // Profile only queries marked as profile
            if (!(strpos($row['Query'], self::DONT_PROFILE) === false)) {
                continue;
            }

            $queryIds[] = $row['Query_ID'];
            $values = array_values($row);
            $profiles->addRow($values);
        }

        return $queryIds;
    }

    /**
     * Constructs the FirePHP messages.
     *
     * @param array $profiles
     * @param string $label
     * @return Zend_Wildfire_Plugin_FirePhp_TableMessage
     */
    private function _constructFirePhp(
        array $columns,
        $label,
        $setBuffer = true)
    {
        $msg = new Zend_Wildfire_Plugin_FirePhp_TableMessage($label);

        // If a message is buffered it can be updated for the duration
        // of the request and is only flushed at the end of the request.
        $msg->setBuffered($setBuffer);
        $msg->setHeader($columns);

        // Destroy the message to prevent delivery
        $msg->setOption('includeLineNumbers', false);
        Zend_Wildfire_Plugin_FirePhp::getInstance()->send($msg);
        return $msg;
    }
}

Now, you need to modify 3 lines of code, in the same file “MySQLProfiling.php” look in the lines 38, 122 and 142.

$db = GET_DB_CONNECTION_INSTANCE

Modify those lines and instead of “GET_DB_CONNECTION_INSTANCE” replace it with a call to your DB handler that you created with “Zend_Db::factory”.

Once that’s done, you can enable the plugin by either registering it on your bootstrap, or enabling it on your config file:

config.ini

resources.frontController.plugins.MySQLProfiling = MyApp_Controller_Plugin_MySQLProfiling

Done! you should have your MySQL profiler running!

MySQL profiling plugin preview

Keep in mind…

Always keep in mind…

  • DB profile tools SHOULD BE USE on development environments only.
  • The plugin is set to profile 100 queries (line 40), I HOPE your page contains much less than 100 queries, however be aware that for every query performed on your page, the plugin will generate one table with all the data plus the profiles table.