Retention policy/continuous queries not collecting data correctly

#1

Hello everyone! I had a question about continuous queries and retention policies. Our team can’t seem to get ours to work properly. Ideally we’d like to have a default policy that keeps metrics at a 1s retention for two days. This policy seems to be working well. Then we’d like a policy that keeps metrics at a 10s retention for two weeks that queries from our default. Then we’d have a longterm policies for quarterly and longterm retention for 1m and 5m respectively. However, the non-default policies are not getting populated with any data. We have also noticed that we have some server write errors, which may be related to failing queries.

These are our retention policies:

name      duration   shardGroupDuration replicaN default
----      --------   ------------------ -------- -------
two_days  48h0m0s    24h0m0s            1        true
two_weeks 336h0m0s   24h0m0s            1        false
quarterly 2160h0m0s  24h0m0s            1        false
long_term 26280h0m0s 168h0m0s           1        false

These are our continuous queries:

name: telegraf
name                query
----                -----
tg_two_weeks_10s_cq CREATE CONTINUOUS QUERY tg_two_weeks_10s_cq ON telegraf BEGIN SELECT mean(*) INTO telegraf.two_weeks.:MEASUREMENT FROM telegraf.two_days./.*/ GROUP BY time(10s), * END
tg_quarterly_1m_cq  CREATE CONTINUOUS QUERY tg_quarterly_1m_cq ON telegraf BEGIN SELECT mean(*) INTO telegraf.quarterly.:MEASUREMENT FROM telegraf.two_days./.*/ GROUP BY time(1m), * END
tg_long_term_5m_cq  CREATE CONTINUOUS QUERY tg_long_term_5m_cq ON telegraf BEGIN SELECT mean(*) INTO telegraf.long_term.:MEASUREMENT FROM telegraf.two_days./.*/ GROUP BY time(5m), * END

If anyone has any pointers or places to look, I’d really appreciate it!

#2

Continuous queries as they’re currently designed will most likely have a problem trying to aggregate everything across the entire database. If you’re looking to aggregate all measurements and fields you’ll be better off using Kapacitor as an aggregator. See the example here: https://docs.influxdata.com/kapacitor/v1.2/examples/continuous_queries/

We have an open issue in the database to add better support for aggregating everything. See these issues called Intelligent Rollups:


#3

Thank you so much for your speedy response. I really appreciate it. I’m looking into Kapacitor, but having a hard time finding examples of how this would be used in my case, beyond single recordings. Can I use wildcards, *, with Kapactior to downsample all measurements to my various retention policies with only single TICKscripts for each policy? Something like this has not worked so far for my first 10s retention policy:

stream
    |from()
        .database('myDatabase')
        .measurement('*')
        .groupBy(*)
    |window()
        .period(10s)
        .every(10s)
        .align()
    |mean('*')
    |influxDBOut()
        .database('myDatabase')
        .retentionPolicy('myTenSecondRP')
        .measurement(*)
        .precision('s')