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;
}