PHP Developer News

Poorman’s MySQL monitoring/trending

I could have also called this article When Pandas meet Dolphins in MySQL Shell.



Some time ago, I wrote a post related on how to collect initial relevant data when trying to seek help for MySQL.



Since then, the MySQL Shell engineering team implemented another powerful native utility that collect all the essential information and more and store them in a single zip file.



This zip archive contains TSV and YAML files that, for example, the MySQL Support Team could use to solve your eventual issue.



For more information regarding MySQL Shell’s util.debug.collectDiagnostics(), I encourage you to check the manual.



This tool is especially powerful to collect data over all the members of an InnoDB Cluster in one single command.



Trending



As most of the old (read experienced) MySQL DBAs, when I want to understand a MySQL system, I really prefer to check the available trending information.



These graphics usually illustrate perfectly the old adage: a picture is worth a thousand words.



But what can we do when we don’t have a nice trending solution implemented to monitor our MySQL database ?



A plugin for MySQL Shell



Once again, a plugin written in Python for MySQL Shell could be a sufficient solution.



I’ve wrote an easily extensible plugin that is able to collect metrics for some time (default is 10 minutes) and generate some useful graphs to understand the workload and the eventual contention of the MySQL Server.



The plugin is available in my GitHub repo with all the others plugins I use for MySQL Shell: https://github.com/lefred/mysqlshell-plugins/.



The plugin is very easy to use and if the required modules are available you can generate the trending graphs using pandas and mathplotlib.







I was very surprised how cool pandas is. And surprise by how easy it’s to parse and play with the collected data.



If you prefer, you can collect the data and plot it later on an other system.







For the moment, the plugins collect much more data than it uses to plot those graphs.



If your MySQL servers is a MySQL HeatWave instance, some extra metrics are also collected.



Each collection module can specify the granularity. The minimum is 1 second.



Adding a new graph is easy and doesn’t require more development skills.



This is for example how the graph illustrating the threads is generated:



The last parameter is the kind of graph and the 4th parameter is a list of list containing all the variables the plot. (if there is a 1, it means that the value is absolute and no delta needs to be computed).



Graphs



Let’s have a look at some examples of generated plots while some different sysbench processes were running:



As you can see the graphs are already a very good starting point to understand better the workload. I will keep adding new graphs as needed.



Enjoy MySQL, MySQL Shell and Pandas !

Most Popular in Database