Plot delta with MySQL DB

Hallo,

for my Bachelor’s thesis I want to evaluate a photovoltaic system.
I got a MySQL DB, wich is updated every minute by the values the inverters deliver. A lot of values are represented as total Wh. To compare them I would like to plot the delta between the current and the previous value.
I really hope someone can help me, because I’m a bit stuck with it.

I reached this post looking for the same answers. However, after a lot of googling and stackoverflowing this is the solution I came for my particular problem. I hope that it helps others and if there are errors please let me know.

Let me start by saying that If you are using MySQL version 8 (or greater), you should use the LAG function rather than this contraption. Documentation is available here (LAG - MariaDB Knowledge Base) and feel free to skip all this.

The setup

I had an old pogo plug that I converted to Arch Linux. I used it in the past to monitor other systems using Hobbit, but I changed to Grafana to learn about its capabilities. The pogo plug is used now for data collection rather than presentation.

The system cannot upgraded it, but it already had installed MySQL 5.1 and several programming languages. I just worked with what it had.

I wrote a collectd UDP collector using NewLisp. The collector gets UDP data from the collectd deamon and inserts the data into a MySQL table. The table looks like this, which matches much of what collectd produces (intervals are skipped):

create table collectd_data (
       collectd_host varchar(20),
       collectd_unixtime timestamp,
       collectd_plugin varchar(20),
       collectd_plugin_instance varchar(20),
       collectd_type varchar(20),
       collectd_type_instance varchar(100),
       collectd_type_values text
);

There are indexes as well, but I’ll skip those for sake of brevity. All the data is inserted as it comes from collectd and the values are either inserted as a a single value or a comma separated list. These are parsed when select is executed using regexp_substr.

Options

One common option is to use a variable outside the the select statement.

set @x=0;
select * rest of query

However Grafana will throw an error on when this is used as a single query box. I tried to use two queries, one for the set and another for the select, but there were problems with query serialization. I tried to use the left join approach that is also mentioned, but the performance was just atrocious (old Pogo, 256 Mb, well, you understand).

How it ended up.

The following is the select statement that I used to collect MySQL handler data from the Collectd MySQL plugin, however all my selects follow a similar pattern. The performance is good for the modest set up. I’ll annotate the select statement, but it’s not too complicated once you get it. (I think…)

---
--- The external select statement is used to filter the values that
---  I want to plot being that intermediate values will be plotted
---  if not removed from the final query.  
---
select subquery.time_sec,

--- This is a safeguard for the first value although in retrospect
--- it may be unnecessary. Negative numbers will turn to zero
--- probably better with an if() rather than a case.
--- 

case
when (subquery.value <= 0) then 0
else subquery.value
end, subquery.metric from (

---
--- Time periods to plot and assign to time_sec. 
---

select UNIX_TIMESTAMP(collectd_unixtime) as time_sec,

--- 
--- metric, in this case the mysql_handlers (icp_attempts, icp_match, etc.) stored
--- in collectd_type_instance. 
---

collectd_type_instance as metric, 

---
--- Original value which is ever increasing
---

cast(collectd_type_values as signed) as original,

---
--- This is where the delta is computed. It should read like this. 
--- If the @x = 0 (first time iterating) or if the @y is different to the collectd_type_instance
--- which refers to the metric that we are plotting, return zero. This is to avoid having
--- a large first value that skews the whole plot. If @x is not zero and 
--- @y is the same as collectd_type_instance, meaning that we're still processing a serie
--- of the same values, then subtract the value to the previous one stored in @x to obtain
--- the delta. 

if (@x = 0 or @y <> collectd_type_instance, 0, cast(collectd_type_values as signed) - @x) as value,

---
--- Update @x with the new value, for the next row
---

@x := cast(collectd_type_values as signed) as nop,

--- 
--- Update @y with the new collectd_type_instance for the next row
---

@y := collectd_type_instance

---
--- The aforementioned table
---

from collectd_data,

--- Initial values for @x and @y. 

(SELECT @x := 0,@y:="") as r 

--- filter by time. 

where $__timeFilter(collectd_unixtime)

---
--- Key selection for the value we're plotting against for. 
---

and collectd_plugin = 'mysql'
and collectd_type like 'mysql_handler'

---
--- Order by it is very important to ensure calculations are correct
--- type instance goes first being that we are going to process one
--- type at the time and for the @y != collectd_type_instance to return
--- zero when a new value is produced. 
--- The values need also to be ordered by the time period to ensure
--- that the deltas are processed correctly and that the plot is in order. 
---

order by collectd_type_instance,collectd_unixtime) as subquery;

This is the stand alone query without annotations.

select subquery.time_sec,
case
when (subquery.value <= 0) then 0
else subquery.value
end, 
subquery.metric from (
   select UNIX_TIMESTAMP(collectd_unixtime) as time_sec,
   collectd_type_instance as metric, 
   cast(collectd_type_values as signed) as original,
   if (@x = 0 or @y <> collectd_type_instance, 0, cast(collectd_type_values as signed) - @x) as value,
   @x := cast(collectd_type_values as signed) as nop, 
   @y := collectd_type_instance
   from collectd_data,(SELECT @x := 0,@y:="") as r 
   where $__timeFilter(collectd_unixtime)
   and collectd_plugin = 'mysql'
   and collectd_type like 'mysql_handler'
   order by collectd_type_instance,collectd_unixtime) as subquery;

This is an excerpt on how my plot looks like in Grafana.

I hope that this helps.