MySAR, a Sidekick for Other Monitoring Tools

I’m sure that most people have at least one of the tools listed in Ronald Bradford’s article: Monitoring MySQL Options. Many of these tools, such as Nagios and Cacti, also monitor the operating system. However, in the same way that a quick look at sar‘s output can give you some insight on the OS, with MySAR you can do the same for the MySQL server. This is especially useful when it is not possible to access a monitoring tool’s graphic interfaces.

What Was Going On Around 2:30pm?

This is a question a customer asked us. To answer it we ran MySAR for a few days and queried the results for analysis. Looking at the data, we determined that the number of INSERT operations was significantly higher than any other, so we queried for the Com_insert status values. Com_insert is a counter that accumulates the number of INSERTs issued since the last server start (or since the last FLUSH STATUS command). For details on the variables available check Chapter 1. mysqld Options/Variables Reference.

72 rows of numbers are not very easy to interpret, so the next step was to create a graphic to visualize the results. Taking the ideas from Shlomi Noach’s blog, SQL pie chart, I decided to try and create the charts using the Google Charts API.

Easy Graphics How-To

The first step was to create the string of values needed for the chart. I achieved this using GROUP_CONCAT, adding some formulas to scale the values properly:

select day(value_stat_timestamp) as day,
GROUP_CONCAT((value_stat_delta-12000)/70) as series from value_stat
where value_stat_name like "Com_insert" and HOUR(value_stat_timestamp) between 13 and 15
and date(value_stat_timestamp) between "2009-08-22" and "2009-08-25"
group by day(value_stat_timestamp);
+------+------------------------------------------------------------------------------------------
| day | series
+------+-----------------------------------------------------------------------------------
| 22 | 57.8714,80.3000,81.2571,94.3857,85.2571,66.0857,94.6429,74.2857,91.1000,...
| 23 | 74.4857,95.5857,103.4571,101.3000,75.9429,75.3571,79.3000,83.0571,94.7714,...
| 24 | 25.6286,38.8429,31.7000,36.3857,50.0143,44.3286,46.1571,39.4714,34.3857,21.5571,...
| 25 | 6.8000,12.9714,33.0429,13.8286,26.2143,38.1000,50.3143,19.4429,13.0857,3.2143,7.9143,...
+------+------------------------------------------------------------------------------------------
4 rows in set (0.34 sec)

(To make the output more readable, I’m not including the whole lines.) Using the values corresponding day 22, the most simple line graphic can be created with following HTML tag:

PYTHIAN®, LOVE YOUR DATA®, and ADMINISCOPE® are trademarks and registered trademarks owned by Pythian in North America and certain other countries, and are valuable assets of our company. Other brands, product and company names on this website may be trademarks or registered trademarks of Pythian or of third parties. Use of trademarks without permission is strictly prohibited.