Add $perSecond macro for computing rate for counter-type metrics

[JiriHorky]

First of all, thanks for the great grafana plugin.

What we are missing in our environment is a $perSecond macro that would be very similar to existing $rate macro, but would compute runningDifference of value as well

$rate is now defined as
SELECT t, v/runningDifference(t/1000) vRate FROM

$perSecond should be defined as:
SELECT t, runningDifference(v)/runningDifference(t/1000) vRate FROM

One of the usecase is to see per second network traffic throughput in bytes/s taken from ifconfig (actually collected as a counter type metric by collectd).

Basically, $rate is now usefull for Gauge type metrics, whereas $perSecond would be useful for Counter metrics.

[hagen1778]

Hi @JiriHorky
I'm glad plugin helps you in your work!

I don't understand perSecond macros. The rate already shows values-per-second. Let's check the following example.

I have some test data in table events. The table resolution is 1 minute:

─name──────┬─type─────────────────────────────────┬─default_type─┬─default_expression─────┐
│ EventDate │ Date                                 │ DEFAULT      │ toDate(EventTime)      │
│ EventTime │ DateTime(\'UTC\')                    │ DEFAULT      │ toStartOfMinute(now())

The $timeSeries query:

SELECT
    (intDiv(toUInt32(EventTime), 60) * 60) * 1000 AS t,
    count() AS c
FROM events
GROUP BY t
ORDER BY t ASC

┌─────────────t─┬─────c─┐
│ 1535712660000 │ 34785 │
│ 1535712720000 │ 35009 │
│ 1535712780000 │ 35136 │
│ 1535712840000 │ 34723 │
│ 1535712900000 │ 35458 │

The result shows how many events were collected per minute. To calculate per-second we need to divide result as following:

34785 / 60 = 579.75
35009 / 60 = 583.48
35136 / 60 = 585.6
...

Let's apply $rate:

SELECT
    t,
    c / runningDifference(t / 1000) AS cRate
FROM
(
    SELECT
        (intDiv(toUInt32(EventTime), 60) * 60) * 1000 AS t,
        count() AS c
    FROM events
    GROUP BY t
    ORDER BY t ASC
)

┌─────────────t─┬─────────────cRate─┐
│ 1535712660000 │               inf │
│ 1535712720000 │ 583.4833333333333 │
│ 1535712780000 │             585.6 │

We see the same result (except of first point because runningDifference comparing first point with 0).

The expression SELECT t, runningDifference(v)/runningDifference(t/1000) vRate FROM, as I understand, will show acceleration. I mean, how fast throughput is growing per second, but not the actual throughput.

[JiriHorky]

Hi @hagen1778 ,

thanks for reaching back. For the input that you described, the $rate macro works well. But for "counter" metrics, i.e. metrics that only grow, your input would look like this:

┌─────────────t─┬─────c─┐
│ 1535712660000 │ 34785 │
│ 1535712720000 │ 69794 │
│ 1535712780000 │ 104930 │
│ 1535712840000 │ 139653 │
│ 1535712900000 │ 175111 │

And for this input, you need to "derive" even the value, not just the time. Please note that this is very standard way of collecting metrics - it is called Counter in collectd, and your "bytes transferred" from "ifconfig" output are collected like that.
In standard graphite, function "perSecond()" computes the rate I am looking for, therefore my suggestion for the template name.

[hagen1778]

Hi @JiriHorky ,

Sorry for delay! Now I see the issue. The macros should get the max value from every period, substract the value from previous period and divide by the number of seconds in the period.
Let's introduce new data:

SELECT
    Time,
    Packets
FROM issue_78

┌────────────────Time─┬─Packets─┐
│ 2018-08-31 10:51:00 │    1000 │
│ 2018-08-31 10:52:00 │    2000 │
│ 2018-08-31 10:53:00 │    3000 │
│ 2018-08-31 10:54:00 │    4000 │
│ 2018-08-31 10:55:00 │    5000 │
│ 2018-08-31 10:56:00 │    6000 │
│ 2018-08-31 10:57:00 │    7000 │
│ 2018-08-31 10:58:00 │    8000 │
│ 2018-08-31 10:59:00 │    9000 │
└─────────────────────┴─────────┘

Then $perSecond should be following:

SELECT
    t,
    runningDifference(c) / runningDifference(t) AS cRate
FROM
(
    SELECT
        intDiv(toUInt32(Time), 120) * 120 AS t,
        max(Packets) AS c
    FROM issue_78
    GROUP BY t
    ORDER BY t ASC
)

┌──────────t─┬──────────────cRate─┐
│ 1535712600 │                nan │
│ 1535712720 │ 16.666666666666668 │
│ 1535712840 │ 16.666666666666668 │
│ 1535712960 │ 16.666666666666668 │
│ 1535713080 │ 16.666666666666668 │
└────────────┴────────────────────┘

Is the output correct?

[JiriHorky]

Hi, yes, that's exactly what I was looking for. 👍

Now, the interesting incarnation of this enhancement is #80 - I would like to see what's the solution for that :)

[hagen1778]

Please, see https://github.com/Vertamedia/clickhouse-grafana/releases/tag/1.8.0