Stay Curious!

A metric for MySQL load average

If you were to measure MySQL’s “loadavg,” how would you do it?

The following metric (in pseudocode) is borrowed from Trevor Price at Aggregate Knowledge. It’s a way to calculate average query response time. What do you think of it?

$start = microtime();
$status1 = SHOW GLOBAL STATUS LIKE Questions;
for ( 1 .. 100 ) {
   $num_running += SELECT COUNT(*) FROM SHOW PROCESSLIST WHERE Command = Query;
}
$time = microtime() - $start;
return 0 unless $time;
$status2 = SHOW GLOBAL STATUS LIKE Questions;
$qps = ($status2 - $status1) / $time;
return 0 unless $qps;
return ($num_running / 100) / $qps;

If you do the dimensional analysis, if I’m not mistaken, it works. You get seconds. There are problems – for example, SHOW PROCESSLIST doesn’t show you what sampling theory predicts it will, for various reasons. What improvements can you suggest in order to solve the shortcomings? What other shortcomings do you see?

What other metrics can you suggest to get a dimensionless number that can give you an idea of the server’s load at a given time?

Posted on Wed, Nov 26, 2008. Approximately 200 Words.

Databases