Hi there,
I’m collecting raw counters from a group of hosts in a database and use a CQ to transform it to something meaningful to plot. Everything works properly for all hosts but one which gets the CQ result every 5 minutes instead of one. What could be the issue here?
The CQ is as follows:
CREATE CONTINUOUS QUERY cq1 ON stats BEGIN SELECT non_negative_derivative(mean("class:local:out:bytes")) / (non_negative_derivative(mean("class:local:out:bytes")) + non_negative_derivative(mean("class:inet:in:bytes")) - non_negative_derivative(mean("class:bypass:in:bytes"))) AS network INTO public.yearly.ratios FROM stats.autogen.nft GROUP BY time(1m), host END
The simplest answer is that the values are negative or missing, and therefore not returned or not nulls. this will lead to having no data.
For a quick “negative” check just run the very same query using a simple derivative as it also returns the negative values, it should look like this:
SELECT derivative(mean("class:local:out:bytes")) / (derivative(mean("class:local:out:bytes")) + derivative(mean("class:inet:in:bytes")) - derivative(mean("class:bypass:in:bytes"))) AS network FROM stats.autogen.nft GROUP BY time(1m), host WHERE time >= __timeFilter__ AND time <= __timeFilter__ AND host = "__YourHost__"
if you get nulls or negatives I suggest you have a look at the source data before the non_negative_derivative, just get the means of the different counters or even the raw data points.
Hi Giovanni,
Thanks for your answer. The data is there. In fact, if I put the entire query into Grafana it works as expected, but I don’t like having to compute the same stuff over and over again, hence I use a CQ to calculate once and store into another measurement. I think it might be some time period alignment issues as the data for all servers is collected at :00, but the one with issues reports at :09. This is the only difference I found so far and I’m going to extract the data and align it by hand and see how it goes from there.
