After switching from the Better Cacti Templates to the Percona Monitoring Plugins we suffered from the Query Response Time and the Query Time Histogram not graphing at all.
It took me a while to figure out why our new instance wasn’t graphing: the response time query was performing and the script was picking up its values. Also the rrd files were created but for some bizarre reason all values were set to “NaN”, in rrd/cacti terms: Not a Number. If you search on that subject you will come across a lot of (forum) postings stating you need to change your graph type to “GAUGE” or change the MIN/MAX values for your data templates. Strange as this was already set to a minimum of 0 and a maximum of an unsigned 64 bit integer.
After running the ss_get_mysql_stats.php script manually for these graphs I got the error stating that a -1 value was not allowed. Indeed the output of the script contained a -1 value for the last measurement and I quickly found the culprit: an uninitialized array key inside the script will cause it to return a -1 value. Now why was this array key not initialized? Simply because the query filling the array was capped to 13 rows instead of the expected 14 rows.
This left me with three options:
1. Change cacti templates to allow -1 value
2. Change cacti templates to only contain 13 data points instead of 14
3. Change the query in the ss_get_mysql_stats.php script
Naturally I patched the script and after 10 minutes the graphs started to get colorful again! 😉
So if you have the same problem as we do, you can find the patch attached to my bugreport:
Query reponse time and Query time histogram not graphing
Art will save you from MySQL quicksand! 