Graphing a delta or counter metric with mysql data source

Hello,

I was wondering if anyone had any luck with getting delta or counter metrics to graph properly with the beta mysql data source. I have a large database in mysql with all my metrics and about have of them are additive but I want to graph the delta.

5 minute intervals:

200
210
225
250
251

but I want the graph to plot:

0
10
15
25
1

I saw that you can do this easy with graphite or influxdb, but im having a hard time getting it to work in mysql.

Thanks

Not does MySQL have a derivative function?

As far as I know, no. Also my google-fu is failing me on providing a solid answer.

Self-join of table with itself on dateColumn t=t+step (plus other keys to identify series) may work for you if time steps between data points is known and fixed. Query preformance will depend strongly on table size and indexing though.

For example, if data points in series are taken at 5 minutes interval, your query may look like:

FROM tbl as A INNER JOIN tbl as B
ON A.dateColumn=B.dateColumn+300 AND A.metricID=B.metricID
WHERE …
ORDER BY time_sec ASC```

Thanks Yuyu,

I did work up a few self joins but the performance is terrible as the table I have is quite large. I find it kind of boggling that grafana doesn’t have the ability to simply produce a counter graph. I guess this is a holdover from working with graphite which includes a derivative function.

Another option to consider is changing your MySQL table scheme - add extra fields to store previous step value and timestamp and modify data insertion accordingly.
This will double data storage requirements but will allow to perform delta calculations easily. Not elegant of course.

I agree. Would be nice to have some series data postprocessing functions like difference or derivative on Grafana server or browser(client) side to aid with less capable datasources. Optional (defined somewhere in dashboard) custom JS callback function triggered after datasource responce might be even more flexible. This is a feature request to developers.

Another option to consider is changing your MySQL table scheme - add extra fields to store previous step value and timestamp and modify data insertion accordingly.

Probably not a really viable option. My table schema right now is designed to be pretty sparse and the table is already 1tb.