Dead Simple Query Sampler

QuerySampler.php

<?php

$timeout = 1800; //seconds
$sleep   = 100000; //microseconds

$start = time();
$link  = mysql_connect('[[HOST]]', '[[USERNAME]]', '[[PASSWORD]]');
if( !$link ) {
	die('Could not connect: ' . mysql_error());
}

$results  = array();
$last_ids = array();

/**
 * @param $baseQuery
 * @return mixed
 */
function anonymizeSql( $baseQuery ) {
// Remove escaped quotes
	$baseQuery = preg_replace('/\\\\["|\']/si', ' = [[\s]]', $baseQuery);

	// LIKE => '[[%]]'
	$baseQuery = preg_replace('/LIKE\s+"[^"]*?%[^"]*"/si', 'LIKE [[%]]', $baseQuery);
	$baseQuery = preg_replace('/LIKE\s+\'[^\']*?%[^\']*\'/si', 'LIKE [[%]]', $baseQuery);

	$baseQuery = preg_replace('/IN\s+\([\d\s,.]+\)/si', 'IN [list[\d]]', $baseQuery);

	$baseQuery = preg_replace('/\s*([,+-><=][=>]?)\s*\d+/si', ' $1 [[\s]]', $baseQuery);

	$baseQuery = preg_replace('/\s*=\s*"[^"]*"/si', ' = [[\s]]', $baseQuery);
	$baseQuery = preg_replace('/\s*=\s*\'[^\']*\'/si', ' = [[\s]]', $baseQuery);

	//dumb other string replacement
	$baseQuery = preg_replace('/"[^"]*"/si', '[[\s]]', $baseQuery);
	$baseQuery = preg_replace('/\'[^\']*\'/si', '[[\s]]', $baseQuery);

	return $baseQuery;
}

$j = 0;
do {
	$j++;
	$percent = intval(((time() - $start) / $timeout) * 100);
	fwrite(STDERR, "\0337{$percent}% - {$j} samples\0338");
	$qry = mysql_query("SHOW FULL PROCESSLIST");
	while( $row = mysql_fetch_array($qry) ) {

		$ids = array();

		if( $row['Command'] != "Query" ) {
			continue;
		}

		if( in_array($row['Id'], $last_ids) ) {
			continue;
		}

		$baseQuery = $row['Info'];
		$rootQuery = $baseQuery;
		$baseQuery = anonymizeSql($baseQuery);


		$exploded = explode(";", $baseQuery);
		foreach( $exploded as $query ) {
			$query = trim($query);
			if( stripos($query, "PROCESSLIST") !== false || $query == "" ) {
				continue;
			}

			$key = sha1($query);
			if( !isset($results[$key]) ) {
				$results[$key] = array(
					'time'  => 0,
					'count' => 0,
				);
			}

			$results[$key]['count']++;
			$results[$key]['time'] += $row['Time'];
			$results[$key]['Query']       = $query;
			$results[$key]['lastRealQry'] = $rootQuery;


		}

		$ids[] = $row['Id'];
	}

	$last_ids = $ids;
	usleep($sleep);
} while( $start + $timeout > time() );


usort($results, function ( $a, $b ) {
	if( $a['count'] > $b['count'] ) {
		return -1;
	} elseif( $a['count'] < $b['count'] ) {
		return 1;
	} else {
		if( $a['time'] > $b['time'] ) {
			return -1;
		} elseif( $a['time'] < $b['time'] ) {
			return 1;
		} else {
			return 0;
		}
	}
});


$first = reset($results);
fputcsv(STDOUT, array_keys($first));
foreach( $results as $result ) {
	fputcsv(STDOUT, $result);
}

Samples SHOW FULL PROCESS LIST and collects query statistics grouped by anonymized versions of the queries.