On a recent project, some application changes meant the application could more intelligently cache its data. (A good application architect, with intimate knowledge of how the application uses data, should generally be able to devise a better memory use (caching) system than a general purpose one, such as the OS file system cache or a database query cache. It’s often not worth it to do so, though.)
The app was tested, and rolled out to some live servers.
Success! All the database monitoring metrics were looking good! Except for those that were worse!
Looking at the various MySQL monitoring graphs below, you can see the “Hmm.” The release was on 8/5.
CPU Load dropped:
Query Cache had less queries hitting it:
All the graphed MySQL operations dropped:
But..
Total Mysql Questions went up…
OK, so we weren’t terribly worried – all the important metrics went the right way. There were less Innodb cache misses; so less disk IO, better query response time, etc.
But what was with the increase in total Questions per second? How could that even be when all the operations graphed went down?
That was exactly the right question to ask – given all the counters that were graphed decreased, it must be one that was not graphed by the monitoring system.
A quick ‘show global status’ showed the only counter with a big value that was not graphed was com_set_options.
The new application code sets options on every transaction – and the monitoring system didn’t (by default) graph com_set_options as it’s not usually a statement that affects performance. We added that counter to the data collection of the Mysql monitoring for this client, so we’ll be able to see it clearly when we update the code to not set so many options – and it just makes diagnosis easier. The more you know, the more you know.