CQ that matches SELECT INTO not working

#1

I am trying to make a continuous query (CQ) work to match an SELECT INTO query that works correctly. Specifically, I’m trying to make a CQ to accurately calculate the send and recv bandwidths from the net input in such a way that aggregate queries/visualizations don’t change based in the size of the aggregation window size.

To backfill the data, this SELECT works perfectly (formatted for readability):

SELECT
    8*non_negative_derivative(max("bytes_recv"),1s) AS rx_rate_bps
   ,8*non_negative_derivative(max("bytes_sent"),1s) AS tx_rate_bps
INTO "net_bandwidth_cq"
FROM "net"
WHERE time > now() - 5d
  AND interface = 'eth0'
GROUP BY time(1s), host, *

The matching CQ that I make to keep this up to date is done this way:

CREATE CONTINUOUS QUERY "cq_net_bandwidth"
ON telegraf
BEGIN
    SELECT
        8*non_negative_derivative(max("bytes_recv"),1s) AS rx_rate_bps
       ,8*non_negative_derivative(max("bytes_sent"),1s) AS tx_rate_bps
    INTO "net_bandwidth_cq"
    FROM "net"
    WHERE interface = "eth0"
    GROUP BY time(1s), host, *
END

This seems to create ok, and CQ logging (in syslog) seems to indicate that it does run successfully, but it definitely does not add to the same measurement that was created with the SELECT INTO above, nor can I figure out where the CQ results end up.

Here is how it registers itself:

> SHOW CONTINUOUS QUERIES
name: _internal
name query
---- -----


name: telegraf
name             query
----             -----
cq_net_bandwidth CREATE CONTINUOUS QUERY cq_net_bandwidth ON telegraf BEGIN SELECT 8 * non_negative_derivative(max(bytes_recv), 1s) AS rx_rate_bps, 8 * non_negative_derivative(max(bytes_sent), 1s) AS tx_rate_bps INTO telegraf.autogen.net_bandwidth_cq FROM telegraf.autogen.net WHERE interface = eth0 GROUP BY time(1s), host, * END

What am I doing wrong here? Why doesn’t this work?

#2

@russ I would imagine with the CQ running every second there may not be data for that second in the database. I would suggest looking into adding an EVERY ... FOR ... clause to your CQ to make it run for a long time period less frequently.

#3

Thanks, @jackzampolin. I will look into the EVERY ... FOR ... clause.

However, I’ve recently figured ou that if I drop the WHERE clause from the query it works perfectly. I’m actually not sure how the derivative function is working properly for all the devices… but it is. ie: I can filter the CQ result on interface = 'eth0' and it gives me the numbers I expect.

#4

@russ Thats because you are GROUP BY *! This will separate the series and derivative() will work on each one.

#5

Getting the independent derivatives for all interfaces is much better than filtering on eth0 only! Now that I know that the GROUP BY * does this series splitting, I added it to the backfilling INSERT query as well. The interface = eth0 filter was to get around the smushing of interfaces that was happening, and the GROUP BY * result is MUCH more satisfying. Thanks!

This is not relevant any more, but… any idea why the WHERE clause would have wrecked the query, then? With it in place there was no data being added to the targeted cq_net_bandwidth measurement.

Also, after the RESAMPLE EVERY .. FOR .. tip, I’m now using this query, which seems to be working very well now:

CREATE CONTINUOUS QUERY "cq_net_bandwidth" ON "telegraf"
RESAMPLE EVERY 60s for 60s
BEGIN SELECT
    8*non_negative_derivative(max("bytes_recv"),1s) AS "rx_rate_bps"
   ,8*non_negative_derivative(max("bytes_sent"),1s) AS "tx_rate_bps"
INTO "net_bandwidth_cq"
FROM "net"
GROUP BY time(10s), host, * END

Now when I view a chart of the max() of this data in grafana, it now behaves rationally, with charted (peak) values being independent of zoom scaling. This was the end objective, so… woohoo!

Thanks!

1 Like
#6

Actually, I see I left host in the GROUP BY clause. It is working as-is, but I’m wondering if (since host is a tag) the GROUP BY * bit causes the host part to be ignored. Is it needed, or can it be dropped?

1 Like
#7

@russ You can pull out the host there. It is ignored in the current query. Glad you were able to get that working!

#8

Thanks… it makes sense to me now that it is ignored.

For anyone else who lands here, the final continuous query is:

CREATE CONTINUOUS QUERY "cq_net_bandwidth" ON "telegraf"
RESAMPLE EVERY 60s for 60s
BEGIN SELECT
    8*non_negative_derivative(max("bytes_recv"),1s) AS "rx_rate_bps"
   ,8*non_negative_derivative(max("bytes_sent"),1s) AS "tx_rate_bps"
INTO "net_bandwidth_cq"
FROM "net"
GROUP BY time(10s), * END
1 Like