Over a million developers have joined DZone.
{{announcement.body}}
{{announcement.title}}

Plot MySQL Data in Real-Time Using Percona Monitoring and Management

DZone's Guide to

Plot MySQL Data in Real-Time Using Percona Monitoring and Management

With this knowledge, you'll have no reason to not visualize and plot your MySQL data in real-time.

· Big Data Zone ·
Free Resource

Hortonworks Sandbox for HDP and HDF is your chance to get started on learning, developing, testing and trying out new features. Each download comes preconfigured with interactive tutorials, sample data and developments from the Apache community.

In this post, we'll show how you can plot MySQL data in real-time using Percona Monitoring and Management (PMM).

In my previous post, I showed how we could load into any metrics and benchmarks into MySQL and visualize them with PMM. But that's not all! We can even visualize most any kind of data from MySQL in real time. I am falling in love with the MySQL plugin for Grafana — it just makes things so easy and smooth.

This graph shows us the number of visitors to a website in real time (refreshing in every five seconds).

We have the following table in MySQL:

CREATE TABLE `page_stats` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `visitors` int(11) unsigned DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `time` (`time`)
) ENGINE=InnoDB AUTO_INCREMENT=9232 DEFAULT CHARSET=latin1

We store the number of visitors every second. I am not saying you have to update this table hundreds or thousands of times — it depends on how many visitors you have. You could use the example of Redis to store and increase this counter and save it into MySQL every second. Here are my metrics:

mysql> select * from page_stats order by id desc limit 10;
+------+---------------------+----------+
| id   | time                | visitors |
+------+---------------------+----------+
| 9446 | 2018-02-27 21:44:12 |      744 |
| 9445 | 2018-02-27 21:44:11 |      703 |
| 9444 | 2018-02-27 21:44:10 |      791 |
| 9443 | 2018-02-27 21:44:09 |      734 |
| 9442 | 2018-02-27 21:44:08 |      632 |
| 9441 | 2018-02-27 21:44:07 |      646 |
| 9440 | 2018-02-27 21:44:06 |      656 |
| 9439 | 2018-02-27 21:44:05 |      678 |
| 9438 | 2018-02-27 21:44:04 |      673 |
| 9437 | 2018-02-27 21:44:03 |      660 |
+------+---------------------+----------+

We can easily add my MySQL query to Grafana and it will visualize it for us:

You might ask, "What is $__timeFilter?" I discussed that in the previous post, but let me copy the manual here, as well:

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

What can you visualize?

It's true! Basically, if you can write a query, you can graph it. For example, let's count all the visitors in every minute. Here is the query:

select
      UNIX_TIMESTAMP(ps.time) as time_sec,
      sum(visitors) as value,
      'visitors' as metric
   from
   page_stats as ps
   WHERE $__timeFilter(time)
   GROUP BY DATE_FORMAT(`time`, '%Y-%m-%d %H:%i')
    ORDER BY ps.time ASC;

And it gives us the following graph:

See, it's easy!

Conclusion

There is no more excuse why you can not visualize your data! Percona Monitoring and Management lets you plot MySQL data in real-time. You do not have to move it anywhere or change anything! Just grant read access from PMM, and you can start to create your own graphs!

Hortonworks Community Connection (HCC) is an online collaboration destination for developers, DevOps, customers and partners to get answers to questions, collaborate on technical articles and share code examples from GitHub.  Join the discussion.

Topics:
big data ,mysql ,real-time data ,percona monitoring and management ,tutorial ,data analytics ,data visualization

Published at DZone with permission of

Opinions expressed by DZone contributors are their own.

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}