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.

The initial query we used was:

select value_stat_name name, value_stat_timestamp ti, value_stat_number val, value_stat_delta delta 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"
order by 1, 2
+------------+---------------------+-----------+-------+
| name       | ti                  | val       | delta |
+------------+---------------------+-----------+-------+
| Com_insert | 2009-08-22 13:00:01 | 163777289 | 16051 |
| Com_insert | 2009-08-22 13:10:01 | 163794262 | 16973 |
| Com_insert | 2009-08-22 13:20:01 | 163810327 | 16065 |
| Com_insert | 2009-08-22 13:30:01 | 163826242 | 15915 |
....
| Com_insert | 2009-08-25 15:20:01 | 168362221 | 13835 |
| Com_insert | 2009-08-25 15:30:01 | 168376888 | 14667 |
| Com_insert | 2009-08-25 15:40:01 | 168392410 | 15522 |
| Com_insert | 2009-08-25 15:50:02 | 168408476 | 16066 |
+------------+---------------------+-----------+-------+
72 rows in set (1.56 sec)

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:

<img src="//chart.apis.google.com/chart?cht=lc&chs=400x200&
chd=t:57.8714,80.3000,81.2571,94.3857,85.2571,66.0857,94.6429">

Resulting in:

In the URL string, cht, chs, and chd represent chart type, chart size, and chart data respectively; you can check the details in the Google Chart API URL I quoted above.

You can copy and paste the <img> tag above into an HTML document (no need to add any text) and open it with any browser. For Firefox, the URL to open a file on disk would look similar to this:

file:///path/to/file/file_name.html

Go ahead, open any text editor, copy and paste the example above in any file, save it with extension .html and open it with your browser.

The chart with the complete data set and and some decorations looks like this—each color represents a different day from the dataset above:

Using the function CONCAT,  it is possible to create the complete <img src=”…”> tag directly from the SQL statement by adding the proper sub-strings.

Conclusion

Using MySAR, it is possible to to collect the data, query the data for diagnosis. and create graphics to visualize those values.

By the way, there was nothing significant happening in the database at 2:30pm.