Skip to content

Create a Custom MySQL Time Series Query

Video Lecture

Create a Custom MySQL Time Series Query Create a Custom MySQL Time Series Query

Description

We create a custom MySQL time series query that reads data from a table in our MySQL database and formats the result set in a way that Grafana can use as a time series result set, and present that data in a graph along with the ability to filter that data using the Grafana user interface time filter drop down.

Create a Custom MySQL Time Series Query

The collector that we've installed on our database is running several queries every 10 minutes and one of those queries is

show global status

The response from the query is saved into the my2.status table as time series data. Time series data, at minimum should contain a value and a time stamp.

This my2.status table has 3 columns. It also has an extra column that we can use for the series name.

View a small sample from the my2.status table.

select * from my2.status order by timest desc limit 10;

Example Output

+-------------------------+----------------+---------------------+
| VARIABLE_NAME           | VARIABLE_VALUE | TIMEST              |
+-------------------------+----------------+---------------------+
| ABORTED_CLIENTS         | 0              | 2020-08-21 08:45:54 |
| ACL_CACHE_ITEMS_COUNT   | 0              | 2020-08-21 08:45:54 |
| BINLOG_CACHE_DISK_USE   | 0              | 2020-08-21 08:45:54 |
| ABORTED_CLIENTS-d       | 0              | 2020-08-21 08:45:54 |
| BINLOG_CACHE_DISK_USE-d | 0              | 2020-08-21 08:45:54 |
| BINLOG_CACHE_USE        | 10             | 2020-08-21 08:45:54 |
| ABORTED_CONNECTS-d      | 0              | 2020-08-21 08:45:54 |
| BINLOG_CACHE_USE-d      | 0              | 2020-08-21 08:45:54 |
| ABORTED_CONNECTS        | 0              | 2020-08-21 08:45:54 |
| ACL_CACHE_ITEMS_COUNT-d | 0              | 2020-08-21 08:45:54 |
+-------------------------+----------------+---------------------+

Typically, for any database you have, the column names will vary. We will write a query in Grafana that will read from this custom table and reformat the data into the format that Grafana can use for the visualization.

So create a new panel, select the MySQL data source, and press the Edit SQL button.

It will present you with a template that you can edit to describe your custom column names from your source table.

SELECT
  UNIX_TIMESTAMP(<time_column>) as time_sec,
  <value column> as value,
  <series name column> as metric
FROM <table name>
WHERE $__timeFilter(time_column)
ORDER BY <time_column> ASC

See the values <time_column>, <value column>, <series name column> and <table name>. We should replace all occurrences of these with the names of the corresponding columns from our test query.

E.g.,

SELECT
  UNIX_TIMESTAMP(TIMEST) as time_sec,
  VARIABLE_VALUE as value,
  VARIABLE_NAME  as metric
FROM my2.status
WHERE $__timeFilter(TIMEST)
ORDER BY TIMEST ASC

I changed all the template placeholder values from/to

Template Placeholder Value Source Table Column Name
<time_column> TIMEST
<value column> VARIABLE_VALUE
<series name column> VARIABLE_NAME
<table name> my2.status

After doing this you will get another error indicating

Value column must have numeric datatype, column: value type: string value: #

You need to cast the VARIABLE_VALUE as a numeric datatype. The quickest way to do this is to append +0 after the VARIABLE_VALUE column name. So that your query resembles

SELECT
  UNIX_TIMESTAMP(TIMEST) as time_sec,
  VARIABLE_VALUE+0 as value,
  VARIABLE_NAME  as metric
FROM my2.status
WHERE $__timeFilter(TIMEST)
ORDER BY TIMEST ASC

In this example, the my2.status query returns a lot of data which you could consider too much data to show in one table. We can alter the query to return only the values that we want for this particular visualization. I would like to see only the series for THREADS_RUNNING and THREADS_CONNECTED

E.g.,

SELECT
  UNIX_TIMESTAMP(timest) as time_sec,
  variable_value+0 as value,
  variable_name as metric
FROM my2.status
WHERE $__timeFilter(timest)
AND variable_name in ('THREADS_CACHED', 'THREADS_CONNECTED', 'THREADS_RUNNING', 'THREADS_CREATED')
ORDER BY timest ASC

You can now save your dashboard with your new visualization that contains your custom query. You can adapt this example many ways you wish. Furthermore, you can view the queries for all the dashboard visualizations to understand how some more advanced tasks were achieved.

Grafana 10 and Ubuntu 22.04 Notes

The MySQL query editor can be found by pressing the [Code] button at the right of the screen.

MySQL Query Editor

To view the generated SQL statement, press the [Inspector] button → [Query] button → [Refresh].

Comments