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 !
That looks super cool, I will surely try this, thanks for sharing.
i try it
“MySQL 192.168.20.1:3307 ssl Py > common._generate_graph(“c:\\tmp\\mysql_threads.png”, “MysQL Threads”, data, [[“threads_cached”,1], [“threads_connected” , 1], [“threads_created”], [“threads_running”,1]], “line”)
However get error : Traceback (most recent call last):
File “”, line 1, in
NameError: name ‘common’ is not defined
Hi,
This method is not meant to be used like that, it requires some include. If you want to plot the collected data you have, you can use `support.plot()` method.
Cheers,