Derivative on multiple fields?

[dmke]

Hey,

I'm storing raw NIC byte/packet counter (among other fields) into InfluxDB 0.11 and would like to generate a plain old traffic graph, using a single query:

select
  non_negative_derivative(max("rx_bytes")) as "rx_bytes",
  non_negative_derivative(max("tx_bytes")) as "tx_bytes",
  non_negative_derivative(max("rx_pkt"))   as "rx_pkt",
  non_negative_derivative(max("tx_pkt"))   as "tx_pkt"
from "nodes"
where "id"='42' and time > now() - 7d
group by time(1h)

This however fails with

error parsing query: derivative cannot be used with other fields

Am I doing it wrong™? Is there a way around this limitation?

[jsternberg]

There is no way to take a derivative of multiple fields at the same time. You can only use one query for each derivative.

Is it possible for you to use multiple queries to get the graph you want?

@pauldix should we consider allowing multiple derivative calls for a single query?

[pauldix]

@jsternberg you definitely should be able to do derivatives on different fields in the same query. Is there a reason why it wouldn't work?

[dmke]

Is it possible for you to use multiple queries to get the graph you want?

Sure, but I'd figure, since influxd already iterates over the data points for the single derivative, it would be a waste of CPU cycles to do that for every derivative I'm interested in, wouldn't it?

[jsternberg]

For derivatives over aggregates, they may have different time intervals as derivatives currently skip over nil values. Derivatives over aggregates are at least easier. Derivatives over raw fields may return some pretty random times with nil values being interspersed through the data, but it's technically easy for us to allow.

The validation is the part that bans it so it may be an artifact of the old query engine. We can lift that restriction if desired.

@dmke it's possible, but the TSM engine for InfluxDB is column based so I don't think iterating over multiple fields ends up being any worse if done as part of multiple queries. Even if it's the same field, we haven't done the optimization for duplicate fields being shared in aggregates as it's harder to do than you would think, so each reference to a field iterates over the data separately (only when an aggregate is used). Raw field queries are optimized for sharing references to each other, but there's not a lot of benefit to utilizing that.

[pauldix]

If it's just a matter of lifting the validation ban, we should make sure there's a test that has multiple fields and derivatives against the raw values where some of the values are nil.

[dmke]

so I don't think iterating over multiple fields ends up being any worse if done as part of multiple queries

I see, thanks for the explanation.

[julian7]

There is another very likely scenario. Read/write wait times can be calculated by non_negative_derivative(max("read_times")) / non_negative_derivative(max("reads")) (from /proc/diskstats). Similarly, user / system CPU usage can be calculated by their derivatives divided by the derivative of number of ticks. Usually these values are gathered at the same time, recorded by a single write operation.

[cnelissen]

+1 Yes please. Even if there is no tangible performance benefit involved (1 query that iterates over the dataset twice vs two queries that both iterate over the dataset once) it is still better from an API standpoint. Doing two queries to get two derivatives from the same measurement is just bad.

[toddboom]

@jsternberg putting this one into v0.13.0 assuming it's an easy fix. let me know if that ends up not being the case.

[jsternberg]

I'm going to use this ticket for allowing derivatives to be used alone as part of multiple fields like the original request as it should be reasonably easy to make that change. The derivative math that @julian7 mentioned is a really good use case, but it's also much more complicated. The entirety of performing math on raw fields is already really complicated and I'm going to try and tackle that as a different issue just to keep this ticket sane.

The main reason derivative math ends up complicated is because we don't know the time of the points output by the derivative ahead of time since it could iterate over nil points. The math iterators already get out of sync really easily. We could feasibly interpolate values between the different derivatives, but that's the point where I think the ticket becomes more complex.

@toddboom does that sound ok?

[hahnjo]

Are there possibly some more restrictions within Chronograf? When looking at the requests, I keep seeing derivative cannot be used with other fields in a request to api/v0/visualizations/1/statements/3/text.

(Interestingly, this is not shown in the UI which only tells me This query returned no results)