Great Metrics Graphs: Percona Monitoring and Management vs. gnuplot

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:

CREATE TABLE `my_metrics_table` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `digest` varchar(20) DEFAULT NULL,
  `count` int(11) unsigned DEFAULT NULL,
  `avg_time` int(11) unsigned DEFAULT NULL,
  `query` varchar(200) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_time` (`time`)
) ENGINE=InnoDB AUTO_INCREMENT=512 DEFAULT CHARSET=latin1

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

LOAD DATA LOCAL INFILE "/var/lib/mysql-files/metrics_import.csv"
INTO TABLE my_metrics_table
COLUMNS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
ESCAPED BY '"'
LINES TERMINATED BY 'n'
IGNORE 1 LINES
(time,digest,count,avg_time,query);

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

mysql> select * from my_metrics_table limit 10;
+----+---------------------+--------------------+-------+----------+----------------------------------+
| id | time                | digest             | count | avg_time | query                            |
+----+---------------------+--------------------+-------+----------+----------------------------------+
|  1 | 2018-02-26 08:43:32 | 0x80F0AE01FE6EFF1B |     3 |   443220 | UPDATE sbtest1 SET c=? WHERE k=? |
|  2 | 2018-02-26 08:43:33 | 0x80F0AE01FE6EFF1B |     5 |   481545 | UPDATE sbtest1 SET c=? WHERE k=? |
|  3 | 2018-02-26 08:43:34 | 0x80F0AE01FE6EFF1B |     7 |   711151 | UPDATE sbtest1 SET c=? WHERE k=? |
|  4 | 2018-02-26 08:43:35 | 0x80F0AE01FE6EFF1B |    10 |   745683 | UPDATE sbtest1 SET c=? WHERE k=? |
|  5 | 2018-02-26 08:43:36 | 0x80F0AE01FE6EFF1B |    13 |   696490 | UPDATE sbtest1 SET c=? WHERE k=? |
|  6 | 2018-02-26 08:43:37 | 0x80F0AE01FE6EFF1B |    17 |   657790 | UPDATE sbtest1 SET c=? WHERE k=? |
|  7 | 2018-02-26 08:43:38 | 0x80F0AE01FE6EFF1B |    21 |   635473 | UPDATE sbtest1 SET c=? WHERE k=? |
|  8 | 2018-02-26 08:43:39 | 0x80F0AE01FE6EFF1B |    24 |   638480 | UPDATE sbtest1 SET c=? WHERE k=? |
|  9 | 2018-02-26 08:43:40 | 0x80F0AE01FE6EFF1B |    26 |   661415 | UPDATE sbtest1 SET c=? WHERE k=? |
| 10 | 2018-02-26 08:43:41 | 0x80F0AE01FE6EFF1B |    30 |   644853 | UPDATE sbtest1 SET c=? WHERE k=? |
+----+---------------------+--------------------+-------+----------+----------------------------------+

Creating the graphs

Just with simple MySQL queries, here is an example:

SELECT
UNIX_TIMESTAMP(time) as time_sec,
`avg_time` as value,
concat(digest,' - ', query) as metric
FROM my_metrics_table
WHERE $__timeFilter(time)
ORDER BY time ASC

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:

Time series:
- return column named time_sec (UTC in seconds), use UNIX_TIMESTAMP(column)
- return column named value for the time point value
- return column named metric to represent the series name
Table:
- return any set of columns
Macros:
- $__time(column) -> UNIX_TIMESTAMP(column) as time_sec
- $__timeFilter(column) ->  UNIX_TIMESTAMP(time_date_time) ≥ 1492750877 AND UNIX_TIMESTAMP(time_date_time) ≤ 1492750877
- $__unixEpochFilter(column) ->  time_unix_epoch > 1492750877 AND time_unix_epoch < 1492750877
- $__timeGroup(column,'5m') -> (extract(epoch from "dateColumn")/extract(epoch from '5m'::interval))::int
Or build your own conditionals using these macros which just return the values:
- $__timeFrom() ->  FROM_UNIXTIME(1492750877)
- $__timeTo() ->  FROM_UNIXTIME(1492750877)
- $__unixEpochFrom() ->  1492750877
- $__unixEpochTo() ->  1492750877

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:

select
      UNIX_TIMESTAMP(mt.time) as time_sec,
      if( @lastDigest = mt.digest, mt.count - @lastCount, 0 ) as value,
      concat(digest,' - ', query) as metric,
      @lastDigest := mt.digest,
      @lastCount := mt.count
   from
      my_metrics_table mt,
      ( select @lastDigest := 0,
               @lastCount := 0 ) SQLVars
     WHERE $__timeFilter(mt.time)
    ORDER BY mt.time ASC;

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. 😉

Conclusion

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. 😉

关注dbDao.com的新浪微博

扫码加入微信Oracle小密圈,了解Oracle最新技术下载分享资源

TEL/電話+86 13764045638
Email service@parnassusdata.com
QQ 47079569