I have a measurement with counters and use derivative() to get a value to chart in Grafana. The measurement has multiple series (identified by tag) like CPU metrics from Linux. I like sum() the values because the mean() makes less sense (the number of series may vary over time), like this
derivative(sum(total_counter),1s)
I have configured the data source in Grafana with 1m resolution, so typically the query time time(1m) does fine. However, when the time range gets very big, the number of points is reduced with a time(2m) that suddenly makes my values twice as high.
If InfluxDB is computing the derivative() as the difference divided by the time interval, I don’t expect the values to double.
Hello @rvdheij,
What do you mean the number of points is reduced with a time(2m)? Does grafana automatically group by 2m? And you can’t specify the groupy?
My apologies for disappearing. My spare time project suffered from real work again… I think this is more generic than just the derivative() function. It may be a Grafana issue after all.
Take for example my attempt to show the InfluxDB 1.8 disk usage by database. I create a simple panel in Grafana like this: 
So I sum() the size of each shard, grouped over time by database. Since InfluxDB writes the statistics every 10s, I defined the datasource in Grafana like that. As long as the time range for my chart is short enough, Grafana uses the minimum granularity from the datasource.
SELECT sum("diskBytes") FROM "shard" WHERE ("hostname" = '9d4cf1a1b261') AND time >= now() - 1h and time <= now() GROUP BY time(10s), "database" fill(null)
That looks good, but when I step back and look at 24 hours for example, the query from Grafana changes to a lower resolution like this:
SELECT sum("diskBytes") FROM "shard" WHERE ("hostname" = '9d4cf1a1b261') AND time >= now() - 24h and time <= now() GROUP BY time(1m), "database" fill(null)
Since this is computing the sum() over 6 samples every minute, the total is off by a factor 6. I can’t use mean() since that gives me the average size of the shards rather than the total. And computing the total doesn’t work either because count() is off as well.
It seems the only way to resolve this is with a nested query like this where I need to know the schema well enough to sum by “id” as that is the primary key for this series:
select sum(mean) from (select mean(diskBytes) as mean from "shard" where time > now() - 10m group by time(2m), "database", "id" ) where time > now() - 10m group by time(2m), "database"
Alternatively, I could do it inside-out and first compute the sum() with 10s resolution and then compute the mean(), but that is more affected by missing samples.
Don’t get me wrong: I really appreciate being able to do nested queries like this, but finding that I have to do that for almost every panel in my dashboards makes the panel editor ineffective. I suppose Grafana could fix that by keeping the minimum interval even for large ranges, but that means it can’t be used for very long time ranges.
Hmm. I wonder whether I can avoid the issue by not using the Grafana computed $__interval but hard coded time(10s) instead.
