getProfiler();
if (!$profiler->getEnabled()) {
throw new \Exception("To display the profiler you should enable enable_sql_profiler on your config/config.ini.php file");
}
$infoIndexedByQuery = array();
foreach ($profiler->getQueryProfiles() as $query) {
if (isset($infoIndexedByQuery[$query->getQuery()])) {
$existing = $infoIndexedByQuery[$query->getQuery()];
} else {
$existing = array('count' => 0, 'sumTimeMs' => 0);
}
$new = array('count' => $existing['count'] + 1,
'sumTimeMs' => $existing['count'] + $query->getElapsedSecs() * 1000);
$infoIndexedByQuery[$query->getQuery()] = $new;
}
uasort($infoIndexedByQuery, 'self::sortTimeDesc');
$str = '
SQL Profiler
Summary
';
$totalTime = $profiler->getTotalElapsedSecs();
$queryCount = $profiler->getTotalNumQueries();
$longestTime = 0;
$longestQuery = null;
foreach ($profiler->getQueryProfiles() as $query) {
if ($query->getElapsedSecs() > $longestTime) {
$longestTime = $query->getElapsedSecs();
$longestQuery = $query->getQuery();
}
}
$str .= 'Executed ' . $queryCount . ' queries in ' . round($totalTime, 3) . ' seconds';
$str .= '(Average query length: ' . round($totalTime / $queryCount, 3) . ' seconds)';
$str .= '
Queries per second: ' . round($queryCount / $totalTime, 1);
$str .= '
Longest query length: ' . round($longestTime, 3) . " seconds ($longestQuery)";
Piwik::log($str);
self::getSqlProfilingQueryBreakdownOutput($infoIndexedByQuery);
}
private static function maxSumMsFirst($a, $b)
{
return $a['sum_time_ms'] < $b['sum_time_ms'];
}
static private function sortTimeDesc($a, $b)
{
return $a['sumTimeMs'] < $b['sumTimeMs'];
}
/**
* Print profiling report for the tracker
*
* @param \Piwik\Db $db Tracker database object (or null)
*/
public static function displayDbTrackerProfile($db = null)
{
if (is_null($db)) {
$db = Tracker::getDatabase();
}
$tableName = Common::prefixTable('log_profiling');
$all = $db->fetchAll('SELECT * FROM ' . $tableName);
if ($all === false) {
return;
}
uasort($all, 'self::maxSumMsFirst');
$infoIndexedByQuery = array();
foreach ($all as $infoQuery) {
$query = $infoQuery['query'];
$count = $infoQuery['count'];
$sum_time_ms = $infoQuery['sum_time_ms'];
$infoIndexedByQuery[$query] = array('count' => $count, 'sumTimeMs' => $sum_time_ms);
}
self::getSqlProfilingQueryBreakdownOutput($infoIndexedByQuery);
}
/**
* Print number of queries and elapsed time
*/
public static function printQueryCount()
{
$totalTime = self::getDbElapsedSecs();
$queryCount = Profiler::getQueryCount();
Piwik::log(sprintf("Total queries = %d (total sql time = %.2fs)", $queryCount, $totalTime));
}
/**
* Get total elapsed time (in seconds)
*
* @return int elapsed time
*/
public static function getDbElapsedSecs()
{
$profiler = \Zend_Registry::get('db')->getProfiler();
return $profiler->getTotalElapsedSecs();
}
/**
* Get total number of queries
*
* @return int number of queries
*/
public static function getQueryCount()
{
$profiler = \Zend_Registry::get('db')->getProfiler();
return $profiler->getTotalNumQueries();
}
/**
* Log a breakdown by query
*
* @param array $infoIndexedByQuery
*/
static private function getSqlProfilingQueryBreakdownOutput($infoIndexedByQuery)
{
$output = '
Breakdown by query
';
foreach ($infoIndexedByQuery as $query => $queryInfo) {
$timeMs = round($queryInfo['sumTimeMs'], 1);
$count = $queryInfo['count'];
$avgTimeString = '';
if ($count > 1) {
$avgTimeMs = $timeMs / $count;
$avgTimeString = " (average = " . round($avgTimeMs, 1) . "ms)";
}
$query = preg_replace('/([\t\n\r ]+)/', ' ', $query);
$output .= "Executed $count time" . ($count == 1 ? '' : 's') . " in " . $timeMs . "ms $avgTimeString \t$query
";
}
Piwik::log($output);
}
}