How do I get consistent values with influxdb non_negative_derivative?

Question

Using grafana with influxdb, I am trying to show the per-second rate of some value that is a counter. If I use the non_negative_derivative(1s) function, the value of the rate seems to change dramatically depending on the time width of the grafana view. I'm using the last selector (but could also use max which is the same value since it is a counter).

Specifically, I'm using:

SELECT non_negative_derivative(last("my_counter"), 1s) FROM ...

According to the influxdb docs non-negative-derivative:

InfluxDB calculates the difference between chronological field values and converts those results into the rate of change per unit.

So to me, that means that the value at a given point should not change that much when expanding the time view, since the value should be rate of change per unit (1s in my example query above).

In graphite, they have the specific perSecond function, which works much better:

perSecond(consolidateBy(my_counter, 'max'))

Any ideas on what I'm doing wrong with the influx query above?

Answer 1

If you want per second results that don't vary, you'll want to GROUP BY time(1s). This will give you accurate perSecond results.

Consider the following example:

Suppose that the value of the counter at each second changes like so

0s → 1s → 2s → 3s → 4s
1  → 2  → 5  → 8  → 11

Depending on how we group the sequence above, we'll see different results.

Consider the case where we group things into 2s buckets.

 0s-2s   →    2s-4s
(5-1)/2  →  (11-5)/2
   2     →      3

versus the 1s buckets

 0s-1s  →  1s-2s  →  2s-3s  →  3s-4s
(2-1)/1 → (5-2)/1 → (8-5)/1 → (11-8)/1
   1    →    3    →    3    →    3

Addressing

So to me, that means that the value at a given point should not change that much when expanding the time view, since the value should be rate of change per unit (1s in my example query above).

The rate of change per unit is a normalizing factor, independent of the GROUP BY time unit. Interpreting our previous example when we change the derivative interval to 2s may offer some insight.

The exact equation is

∆y/(∆x/tu)

Consider the case where we group things into 1s buckets with a derivative interval of 2s. The result we should see is

 0s-1s    →  1s-2s    →  2s-3s    →  3s-4s
2*(2-1)/1 → 2*(5-2)/1 → 2*(8-5)/1 → (11-8)/1
   2      →    6      →    6      →    6

This may seem a bit odd, but if you consider what this says it should make sense. When we specify a derivative interval of 2s what we're asking for is what the 2s rate of change is for the 1s GROUP BY bucket.

If we apply similar reasoning to the case of 2s buckets with a derivative interval of 2s is then

 0s-2s     →    2s-4s
2*(5-1)/2  →  2*(11-5)/2
   4       →      6

What we're asking for here is what the 2s rate of change is for the 2s GROUP BY bucket and in the first interval the 2s rate of change would be 4 and the second interval the 2s rate of change would be 6.