Skip to content

Instantly share code, notes, and snippets.

@mslabko
Last active August 17, 2021 21:43
Show Gist options
  • Save mslabko/06b8dd2e80670f8e1abbf19f70ffd99f to your computer and use it in GitHub Desktop.
Save mslabko/06b8dd2e80670f8e1abbf19f70ffd99f to your computer and use it in GitHub Desktop.
Enable sql profiling for Magento2
<?php
/**
* Enable sql profiling for each page.
*
* Put code to index file in two parts
*/
// place it BEFORE application run
$profiler = \Magento\Framework\App\ObjectManager::getInstance()
->get('Magento\Framework\App\ResourceConnection')
->getConnection('read')
->getProfiler()
->setEnabled(true);
$bootstrap->run($app);
// Place After application run
if (!isset($_SERVER['HTTP_X_REQUESTED_WITH'])) {
/* @var \Magento\Framework\App\Resource $adapter */
$adapter = \Magento\Framework\App\ObjectManager::getInstance()
->get('Magento\Framework\App\ResourceConnection');
// composer.phar require "jdorn/sql-formatter:1.3.*@dev"
// require_once '/home/user/.composer/vendor/jdorn/sql-formatter/lib/SqlFormatter.php';
/* @var Zend_Db_Profiler $profiler */
$profiler = $adapter->getConnection('read')->getProfiler();
$queryPull = [];
if ($profiler->getEnabled()) {
echo "<table cellpadding='0' cellspacing='0' border='0'>";
echo '<tr><th>', $profiler->getTotalElapsedSecs(), ' s ', '</th><th>', $profiler->getTotalNumQueries(), 'queries', '</th><th>', microtime(1) - $_SERVER['REQUEST_TIME_FLOAT'], '</th></tr>';
foreach ($profiler->getQueryProfiles() as $query) {
$queryTime = 1000 * $query->getElapsedSecs();
$rowStyle = $queryTime > 1 ? 'color: red' : '';
$hash = md5($query->getQuery());
if (!isset($queryPull[$hash])) {
$queryPull[$hash] = 1;
} else {
$queryPull[$hash] += 1;
}
/** @var Zend_Db_Profiler_Query $query */
echo '<tr style="'. $rowStyle . '">';
echo '<td>', number_format($queryTime, 2), 'ms', '</td>';
echo '<td><span style="color:grey; size: 8px">', $hash , '</span> ' , $query->getQuery(), '</td>';
echo '</tr>';
}
$blackList = array_map('md5', ['begin', 'commit']);
$queryPull = array_filter($queryPull, function ($v, $k) use ($blackList) {
return $v > 1 && !in_array($k, $blackList);
}, ARRAY_FILTER_USE_BOTH);
asort($queryPull);
echo '<pre> Called more than once';
print_r($queryPull);
}
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment