Analyzing MySQL usage; finding and fixing bottlenecks

MyTop and Mtop are nice tools to analyze MySQL load and queries, however long running systems require another system, so I present a simple script to analyze longer running multi user databases for abusers and bottlenecks. The code is very easy to alter to retrieve other stats.
Otherwise you can always mail me to fix it for what you might need.


#!/usr/bin/perl

while(1) {
# load all previous data, if any
%users = ();
if (-f "./sql.log") {
open(F, "./sql.log");
while() {
chomp;
# user no_queries tot_length
/(.*?) (.*?) (.*)/;
$users{$1} = "$2 $3";
}
close F;
}

@p = `mysql -u yyy --password=xxx --execute='show full processlist'`;
for($i=3;$i<scalar(@p)-1;$i++) {
$r = $p[$i];
chomp($r);
@cols = split(/t/, $r);

next if $cols[4] eq "Sleep";

$q = "";
for($j=7;$j<scalar(@cols);$j++) {
$q .= $cols[$j]." ";
}
next if $q=~/show full processlist/;
next if $q eq "NULL";

$u = $cols[1];
$l = $cols[5];

$res = "";
if (!$users{$u}) {
$res = "1 $l";
} else {
$res = $users{$u};
$res =~ /(.*?) (.*)/;
$l += $2;
$t = $1 + 1;
$res = "$t $l";
}
$users{$u} = $res;
}

open(F, ">./sql.log");
foreach(keys(%users)) {
$res = $users{$_};
print F "$_ $resn";
}
close F;
}

Be the first to leave a comment. Don’t be shy.

Join the Discussion

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>