2

How to enable Magento2's database profiler ?

11

Had to do a little digging in the core code base found the following details:

First in app/etc/env.php we have to add "profiler" key in the connection array:

return array (
  ....
  'db' => 
  array (
    .....
    array (
      'default' => 
      array (
        ....
        'profiler' => [
            'class' => '\Magento\Framework\DB\Profiler',
            'enabled' => true,
        ],
      ),
    ),
  ),
  ......
);

Secondly in the index.php or pub/index.php (depending on the server Vhost configuration) of the application have to add this extra html code:

$bootstrap->run($app); //This should be the last line
//After Than add these
/** @var \Magento\Framework\App\ResourceConnection $res */
$res = \Magento\Framework\App\ObjectManager::getInstance()->get('Magento\Framework\App\ResourceConnection');
/** @var Magento\Framework\DB\Profiler $profiler */
$profiler = $res->getConnection('read')->getProfiler();
echo "<table cellpadding='0' cellspacing='0' border='1'>";
echo "<tr>";
echo "<th>Time <br/>[Total Time: ".$profiler->getTotalElapsedSecs()." secs]</th>";
echo "<th>SQL [Total: ".$profiler->getTotalNumQueries()." queries]</th>";
echo "<th>Query Params</th>";
echo "</tr>";
foreach ($profiler->getQueryProfiles() as $query) {
    /** @var Zend_Db_Profiler_Query $query*/
    echo '<tr>';
    echo '<td>', number_format(1000 * $query->getElapsedSecs(), 2), 'ms', '</td>';
    echo '<td>', $query->getQuery(), '</td>';
    echo '<td>', json_encode($query->getQueryParams()), '</td>';
    echo '</tr>';
}
echo "</table>";

The output will look something like this:

enter image description here

Warning: This will mess up any ajax request due to the profiler output being rendered

|improve this answer|||||
  • Hi, what do you use the database profiler for? – Steve Johnson Jan 15 '16 at 12:57
  • Basically just to see what all query were executed for the page, query time and query params – Atish Goswami Jan 15 '16 at 15:08
  • Thank you. I created internal issue MAGETWO-47970 to track creating documentation for the database profiler. If you have any real-world observations or advice, please let us know. (IOW, is there a trick or advice to interpreting results? Anything to look for in particular?) – Steve Johnson Jan 16 '16 at 15:17
  • I have added a few modification to my answer and a screen shot of the output, hope this will help better interpret the result of the DB profiler. – Atish Goswami Jan 18 '16 at 9:07
  • Atish, I finally created a topic for this. I verified it on 2.1; I'm sure it works on 2.0.x as well. We'd like to give you credit for it seeing as how you figured it out. Do you have a web site? We could link to that. Thanks – Steve Johnson Jul 11 '16 at 20:21
1

This module already have ability to enable magento and database profiler without changing code manually: https://github.com/mirasvit/module-profiler

|improve this answer|||||

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.