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?
Doesn’t the measuring method itself (SHOW PROCESSLIST) increase the number of queries and bias the results?
The more status information you’ll retrieve with a query the more the performance of these measurement queries will affect the result.
I am not sure if the bias is significant or not, probably it is because the measurement queries need to access and lock global variables (SHOW GLOBAL STATUS).
Jan
27 Nov 08 at 5:32 am
Hi again,
True, this calculation has some minor issues, but no more so than the ‘top’ output on a typical *nix system, to be honest. In general, if you start out with the question “what’s the problem you’re trying to solve?”, you can probably get to a better solution than either loadavg or this solution for mysql.
There are two problems here, imho:
1. The calculation is doomed to inaccuracy, though admittedly how accurate you need to be here is also a question worth addressing — I’m sure most people reading this are aware that this is really a “rough idea” kind of figure.
2. The relevance of the information you get back from this is also in question. As both a sysadmin and a DBA, loadavg to me is really only relevant if the server *isn’t* bored, and at that point, it’s just a starting point to determine “ok, the system is busy, but now I need to know what actual resource is being hogged and killing performance”.
With MySQL, my experience has been that the way a lot of people set it up (or… don’t), the problem is IO, which points at all kinds of other issues. The point isn’t what the problem is, here. The point is that loadavg is almost always less relevant than a lot of other data points (like, say, iostat output, for just one example)
So, to that end, I think that the solution presented here is probably “good enough”, and you’d need to question the utility of something that is a whole lot more accurate, unless you just have some downtime to kill between client engagements ;-)
Brian K. Jones
27 Nov 08 at 10:09 pm
I have a QuadCore Server(4 Cores). How can i configure mysql to use all the four cores. Right now mysql process use only one core whose utilization goes upto 95%.
Jain
29 Nov 08 at 6:15 am
[...] más información sobre el script podéis visitar la web del Xapbr Comparte la [...]
Consumo e Ãndices de carga MySQL :: JMGiner.eu
6 Jan 09 at 11:24 am
Hi,
wrote a little perl script which does the above.
Get it here: http://www.thorko.de/thorko/scripts/mysql_stats.pl
thorko
4 Jul 09 at 5:48 am