Queries Per Second

It’s obviously very important to know how your application and DBs are performing during business as usual. Otherwise you might find yourself in a situation where you’re facing performance degradation, and you have no clue what it’s down to. One important aspect of this is knowing how many queries per second your DBs are processing. If you know that it normally does 8000 queries per second, and you all of a sudden see an average of 12000, you know where to start looking. But if you don’t know what your normal load is, 12000 might strike you as normal and you initially overlook the cause for the slow-down.

I’ve written a perl script which gathers the average amount of queries per second over a given period of time. For the heck of it, I've written it in Perl, and I make no claims of being extremely knowledgeable in this language, so you might be horrified at what you’re about to see:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46

#!/usr/bin/perl
use POSIX;
use integer;
use DBI;
$user="root";
$pass="";
@hosts = qw \
10.0.0.201
10.0.0.202
10.0.0.203
10.0.0.204
10.0.0.205
\;
###################################################################
sub getQueries {
      foreach (@hosts) {
        $db="test";
        $cs="dbi:mysql:$db;$_";
        $dbh = DBI->connect($cs,$user,$pass);
        $sth = $dbh->prepare("SHOW STATUS LIKE 'questions'");
        $sth->execute();
        $sth->bind_columns(\$var, \$queries);
        $sth->fetch();
        chomp($queries);
        push(@queries, $queries);
        $sth->finish();
        $dbh->disconnect;
      }
}
$interval = 5;
getQueries();
@queries2 = @queries;
@queries = ();
sleep $interval;
getQueries();
$i = 0;
$tot = 0;
print "==========================================\n";
while ($i != $#queries+1){
     $res = ($queries[$i]-$queries2[$i])/$interval;
     $tot = $tot + ceil($res);
     print @hosts[$i] . " = ". $res."\n";
     $i++;
}
print "Total: " . $tot;
print "\n==========================================\n";

 You might want to change the user, pass and IP addresses in the top of the code. The output of this code looks like this:

1
2
3
4
5
6
7
8
9

# perl qps.pl
==========================================
10.0.0.201 = 1752
10.0.0.202 = 1908
10.0.0.203 = 1719
10.0.0.204 = 2031
10.0.0.205 = 1809
Total: 9219
==========================================

I might post a bit later on other tools you can use to get to know your setup a bit better.

Apr 1st, 2008
mysql,, sysadmin