Last active
August 17, 2021 21:43
-
-
Save mslabko/06b8dd2e80670f8e1abbf19f70ffd99f to your computer and use it in GitHub Desktop.
Enable sql profiling for Magento2
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
<?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