Create a Custom MySQL Time Series Query

Video Lecture

Description

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

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 |
+-------------------------+----------------+---------------------+

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 a visualization.

So open the Explore tab. Select the MySQL data source.

Switch to the Code view.

And paste in this query.

SELECT
  TIMEST,
  VARIABLE_VALUE,
  VARIABLE_NAME
FROM
  my2.status
WHERE
  $__timeFilter(TIMEST)
ORDER BY
  TIMEST ASC

Then press the Run query button.

The data returned is a table.

If you try to view the response using the time series option, Grafana may indicate this as an error db has no time column: no time column found.

Despite the presence of time and value columns, Grafana doesn't yet recognize this as time series data.

So we can alias the columns to what Grafana needs.

Update the query to be,

SELECT
  TIMEST AS "time",
  VARIABLE_VALUE AS "",
  VARIABLE_NAME
FROM
  my2.status
WHERE
  $__timeFilter(TIMEST)
ORDER BY
  TIMEST ASC

Now you can select the time series option.

The error is gone, but the data still appears as a table.

We need to cast the value column as numeric. The quickest option is to add the +0 like in the example below.

SELECT
  TIMEST AS "time",
  VARIABLE_VALUE + 0 AS "",
  VARIABLE_NAME
FROM
  my2.status
WHERE
  $__timeFilter(TIMEST)
ORDER BY
  TIMEST ASC

Typically, for any database you have, the column names and data types may vary. You can alias as shown above, and add the +0 to the column you want to use as the value.

Now, the above 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.

We could limit the result set to see only the metrics for THREADS_RUNNING and THREADS_CONNECTED

E.g.,

SELECT
  TIMEST AS "time",
  VARIABLE_VALUE + 0 AS "",
  VARIABLE_NAME
FROM
  my2.status
WHERE
  $__timeFilter(TIMEST)
AND variable_name in ('THREADS_CACHED', 'THREADS_CONNECTED', 'THREADS_RUNNING', 'THREADS_CREATED')
ORDER BY
  TIMEST ASC

We can now create a new visualization in a new dashboard that uses this query.

Comments