...

Open source softwares - Grafana

Back to Course

Lesson Description


Lession - #736 Create a Custom MySQL Time Series Query


Create a Custom MySQL Time Series Query

We make a custom MySQL time series question that peruses information from an our table in our MySQL data set and configurations the outcome set such that Grafana can use as a period series result set, and present that information in a diagram alongside the capacity to channel that information utilizing the Grafana UI time channel drop down.



The authority that we've introduced on our information base is running a few questions at regular intervals and one of those inquiries is

show worldwide status

The reaction from the question is saved into the my2.status table as time series information. Time series information, at least ought to contain a worth and a period stamp.

This my2.status table has 3 sections. It additionally has an additional a colum that we can use for the series name.

View a little example from the my2.status table.

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

Regularly, for any data set you have, the section names will differ. We will compose a question in Grafana that will peruse from this custom table and reformat the information into the organization that Grafana can use for the perception.

So make another board, select the MySQL information source, and press the Edit SQL button.

Eg,

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


You really want to give the VARIABLE_VALUE a role as a numeric datatype. The speediest method for doing this is to attach +0 after the VARIABLE_VALUE section name. So your question looks like

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 question returns a great deal of information which you could consider an excess of information to show in one table. We can modify the inquiry to return just our desired qualities for this specific perception. I might want to see just the series for THREADS_RUNNING and THREADS_CONNECTED Eg,

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