-
-
Save AVoskoboinikov/6c094220301b723cdcf552215a585ad7 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>'; | |
$profiles = $profiler->getQueryProfiles(); | |
usort($profiles, function ($a, $b) { | |
if ($a->getElapsedSecs() == $b->getElapsedSecs()) { | |
return 0; | |
} | |
return ($a->getElapsedSecs() < $b->getElapsedSecs()) ? -1 : 1; | |
}); | |
foreach ($profiles as $query) { | |
$queryTime = 1000 * $query->getElapsedSecs(); | |
// if ($queryTime < 70) continue; | |
$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