Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Dead Simple Query Sampler
<?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);
}
@donatj

This comment has been minimized.

Copy link
Owner Author

@donatj donatj commented Dec 15, 2014

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

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment