ShowQueriesStatsAnalysis

queryanalysis.php

#!/usr/bin/php
<?php

/**
 * To use - view source of a page with ?showqueries=1&isDev=1, save queries to a file, pass the file as an arg to the script
 */
function output() {
	foreach (func_get_args() as $message) {
		echo $message . PHP_EOL;
	}
}

$file = array_key_exists(1, $argv) ? $argv[1] : null;

if (!$file) {
	output("No file path supplied");
	exit(1);
}
if (!file_exists($file)) {
	output("Can't open file");
	exit(1);
}

$fh = fopen($file, 'r');

$inquery = false;
$query = '';
$queries = [];
$noQueries = 0;
$seenQ = [];
$uniQueries = 0;
$totalDBTime = 0;

while (false !== ($line = fgets($fh))) {
	$line = htmlspecialchars_decode(trim($line));
	if (!$line) {
		continue;
	}
	if (strpos($line, '<') === 0) {
		$inquery = !$inquery;
		if (!$inquery && $query) {
			$noQueries++;
			$query = trim($query);
			$words = explode(' ', $query);
			$type = reset($words);
			$time = array_pop($words);
			$totalDBTime += $time;
			$time = substr($time, 0, -1);
			if (!array_key_exists($type, $queries)) {
				$queries[$type] = [];
			}
			$query = implode(' ', $words);
			$queryHash = md5($query);
			if (!array_key_exists($queryHash, $queries[$type])) {
				++$uniQueries;
				$seenQ[$queryHash] = $query;
				$queries[$type][$queryHash] = [
					'query' => $query,
					'count' => 1,
					'time' => $time
				];
			} else {
				$queries[$type][$queryHash]['count']++;
				$queries[$type][$queryHash]['time']+= $time;
			}

			$query = '';
		}
	}

	else if ($inquery) {
		$query .= $line . ' ';
	}
}

$bestQueryHash = '';
$bestQueryCount = 0;
$bestNSQueryHash = '';
$bestNSQueryCount = 0;

foreach ($queries as $type => &$qbt) {
	$count = 0;
	output("No unique $type queries: " . count($qbt));
	foreach ($qbt as $hash => &$details) {
		$count += $details['count'];
		$details['average'] = $details['time'] / $details['count'];
		if ($details['count'] > $bestQueryCount) {
			$bestQueryCount = $details['count'];
			$bestQueryHash = $hash;
		}
		if ($type !== 'SELECT' && $details['count'] > $bestNSQueryCount) {
			$bestNSQueryCount = $details['count'];
			$bestNSQueryHash = $hash;
		}
	}
	output("No $type queries total: " . $count);
	if (!in_array($type, ['SELECT'])) {
//		var_dump($qbt);
	}
}

$selectQs = $queries['SELECT'];
$counts = [];
$avtimes = [];
$totimes = [];
foreach ($selectQs as $hash => $details) {
	$counts[] = $details['count'];
	$avtimes[] = $details['average'];
	$totimes[] = $details['time'];
}
array_multisort($counts, SORT_ASC, $selectQs);
output("5 most called:");
var_dump(array_slice($selectQs, -5, 5));
$selectQs = $queries['SELECT'];


array_multisort($avtimes, SORT_ASC, $selectQs);
output("5 most timey:");
var_dump(array_slice($selectQs, -5, 5));
$selectQs = $queries['SELECT'];

array_multisort($totimes, SORT_ASC, $selectQs);
output("5 most total timey:");
var_dump(array_slice($selectQs, -5, 5));
$selectQs = $queries['SELECT'];

output("Number of queries: " . $noQueries);
output("Number of unique queries: " . $uniQueries);
output("Most called query: " . $seenQ[$bestQueryHash] . ' - count: ' . $bestQueryCount);
output("Most called NS query: " . $seenQ[$bestNSQueryHash] . ' - count: ' . $bestNSQueryCount);
output("Total DB time: $totalDBTime");

exit(0);