Great Metrics Graphs: Percona Monitoring and Management vs. gnuplot

Great Metrics Graphs: Percona Monitoring and Management vs. gnuplot


In this blog post, we’ll checkout Percona Monitoring and Management vs. gnuplot when it comes to creating great metrics graphs. gnuplot is great, but PMM might provide graphs that are easier to read.

Percona Monitoring and Management (PMM) is our free and open-source platform for managing and monitoring MySQL®, MariaDB® and MongoDB® performance, and is based on Grafana and Prometheus. It collects many kinds of metrics: OS metrics, MySQL metrics or MongoDB metrics. But there are times where you just ran a benchmark or collected your specific metrics and you want to visualize them.

For a long time, I used gnuplot for that (and I still like it). But when I was working on a recent blog post, one of my colleagues wrote me:

AHHHHHHH! My eyes are bleeding from those graphs!!

(Guess what, he is working on PMM. 😉 )

So I investigated how can I use PMM to plot my graphs from my collected metrics. Here’s a graph that I was able to generate showing a count of queries based on metrics collected from ProxySQL:

Just for comparison here is the graph I created with gnuplot:

Grafana’s MySQL data source plugin

Prometheus is the default data source in PMM. After some research and testing, I realized loading metrics into Prometheus from a file is just a nightmare! So what can I do?

Luckily, Grafana supports multiple data sources, including MySQL. That’s great news. You can use this functionality to point your PMM server Grafana data source to the MySQL server that contains the data you’d like to plot.

On PMM you can go to “Data Sources” menu and add the MySQL server:

Choose the “type” MySQL and add your MySQL credentials. That’s all! At this point, PMM creates a graph from your “my_metrics” database.

Loading the metrics

I assume you have your metrics in a file (my metrics are in a CSV file). I am going to use “Load data local infile”. First I create a table:

As we can see, just a normal MySQL table. Let’s load the data:

No magic here either, just loading in the rows into the table. The result is:

Creating the graphs

Just with simple MySQL queries, here is an example:

With this query, we are going to graph the “avg_time”, which is the average execution time of my queries. But what is “$__timeFilter”? There are some default macros, let me copy-paste the manual here:

And the result is:

Nice, we created a graph based on a MySQL table. That means if you have almost any kind of metrics or benchmark you can easily create some graphs and analyze them.

Of course, MySQL is not a time series database. With hundred millions of records, it is not going to work, or it will be quite slow. It also does not have features like Prometheus where we can easily use “rate” and “irate”.  If you require metrics series analysis across large datasets, consider ClickHouse.

Rate in MySQL

On my next graph I wanted to show the QPS, but in my table the number of QPS is a counter that’s increasing. I only needed the differences. How can I do that without “rate”? We can do some MySQL magic as well and write a query like this:

This query is going to calculate the differences between the last and the current value, which is what I need. The result is:

Another nice graph what you can show to your boss. 😉


PMM is based on open-source tools and you can modify any parts of it. Just like in this example, I could not use Prometheus (it would have been much more complicated) so I used a MySQL data source. I could have chosen InfluxDB as well, but I already had MySQL so I did not have to install anything at all.

Hopefully, this is going to help you to make some nice graphs for your benchmarks.

I still like gnuplot as well. 😉


Share this post

Leave a Reply