Projects/InfluxDB Setup: Difference between revisions

From Playful Exploration
Jump to navigation Jump to search
Line 9: Line 9:
{| class="wikitable"
{| class="wikitable"
|-
|-
! sample rate/aggregate !! retention !! # of values !! InfluxDB Retention and Continuous Query
! sample rate/aggregate !! retention !! # of values !! InfluxDB Retention and Continuous Query Setup
|-
|-
| 0.25 seconds (raw data) || 1 hour || 14400 ||  
| 0.25 seconds (raw data) || 1 hour || 14400 ||  
Line 17: Line 17:
CREATE RETENTION POLICY _1day ON weather DURATION 1d REPLICATION 1
CREATE RETENTION POLICY _1day ON weather DURATION 1d REPLICATION 1


CREATE CONTINUOUS QUERY CQ_5sec ON weather BEGIN SELECT median(value) as value INTO weather._1day.:measurement FROM /^[a-z].*/ GROUP BY time(5s) END
CREATE CONTINUOUS QUERY CQ_5sec ON weather BEGIN SELECT median(value) as value INTO weather._1day.:measurement FROM /^[a-z].*/ GROUP BY time(5s) fill(none) END
|-
|-
| 5 minute || 1 month || 8640 ||
| 5 minute || 1 month || 8640 ||
CREATE RETENTION POLICY _1month ON weather DURATION 30d REPLICATION 1
CREATE RETENTION POLICY _1month ON weather DURATION 30d REPLICATION 1


CREATE CONTINUOUS QUERY CQ_5min ON weather BEGIN SELECT median(value) as value INTO weather._1month.:measurement FROM _1day./^[a-z].*/ GROUP BY time(5m) END  
CREATE CONTINUOUS QUERY CQ_5min ON weather BEGIN SELECT median(value) as value INTO weather._1month.:measurement FROM _1day./^[a-z].*/ GROUP BY time(5m) fill(none) END  
|-
|-
| 30 minute || 1 year || 17280 ||
| 30 minute || 1 year || 17280 ||
CREATE RETENTION POLICY _1year ON weather DURATION 366d REPLICATION 1
CREATE RETENTION POLICY _1year ON weather DURATION 366d REPLICATION 1


CREATE CONTINUOUS QUERY CQ_30min ON weather BEGIN SELECT median(value) as value INTO weather._1year.:measurement FROM _1month./^[a-z].*/ GROUP BY time(30m) END
CREATE CONTINUOUS QUERY CQ_30min ON weather BEGIN SELECT median(value) as value INTO weather._1year.:measurement FROM _1month./^[a-z].*/ GROUP BY time(30m) fill(none) END
|-
|-
| 1 hour || 10 years || 86400 ||
| 1 hour || 10 years || 86400 ||
CREATE RETENTION POLICY _10years ON weather DURATION INF REPLICATION 1
CREATE RETENTION POLICY _10years ON weather DURATION INF REPLICATION 1


CREATE CONTINUOUS QUERY CQ_1hour ON weather BEGIN SELECT median(value) as value INTO weather._10years.:measurement FROM _1year./^[a-z].*/ GROUP BY time(1h) END
CREATE CONTINUOUS QUERY CQ_1hour ON weather BEGIN SELECT median(value) as value INTO weather._10years.:measurement FROM _1year./^[a-z].*/ GROUP BY time(1h) fill(none) END
|}
|}
* 144000 measurement, 2.3MB at 16Byte per measurement
* 144000 measurement, 2.3MB at 16Byte per measurement
== Retention and Down-Sampling (Option 2) ==
{| class="wikitable"
|-
! sample rate/aggregate !! retention !! # of values !! InfluxDB Retention and Continuous Query Setup
|-
| 0.25 seconds (raw data) || 1 hour || 14400 ||
CREATE RETENTION POLICY default ON weather DURATION 1h REPLICATION 1 DEFAULT
|-
| 15 minutes || 10 years || 345600 ||
CREATE RETENTION POLICY _10years ON weather DURATION 1d REPLICATION 1
CREATE CONTINUOUS QUERY CQ_15min ON weather BEGIN SELECT median(value) as value INTO weather._10years.:measurement FROM /^[a-z].*/ GROUP BY time(15m) fill(none) END
|}
* 360000 measurement, 5.76MB at 16Byte per measurement

Revision as of 07:52, 4 June 2016

Objective

  • Provide a Database for measurements
  • Manage memory smartly:
    • Allow for high sampling rates e.g. 4 values/second
    • Short retention for high sampling rate data e.g. 1 hour
    • Multiple levels of automatic down-sampling using aggregation with increasing retention for each level

Retention and Down-Sampling

sample rate/aggregate retention # of values InfluxDB Retention and Continuous Query Setup
0.25 seconds (raw data) 1 hour 14400

CREATE RETENTION POLICY default ON weather DURATION 1h REPLICATION 1 DEFAULT

5 seconds 1 day 17280

CREATE RETENTION POLICY _1day ON weather DURATION 1d REPLICATION 1

CREATE CONTINUOUS QUERY CQ_5sec ON weather BEGIN SELECT median(value) as value INTO weather._1day.:measurement FROM /^[a-z].*/ GROUP BY time(5s) fill(none) END

5 minute 1 month 8640

CREATE RETENTION POLICY _1month ON weather DURATION 30d REPLICATION 1

CREATE CONTINUOUS QUERY CQ_5min ON weather BEGIN SELECT median(value) as value INTO weather._1month.:measurement FROM _1day./^[a-z].*/ GROUP BY time(5m) fill(none) END

30 minute 1 year 17280

CREATE RETENTION POLICY _1year ON weather DURATION 366d REPLICATION 1

CREATE CONTINUOUS QUERY CQ_30min ON weather BEGIN SELECT median(value) as value INTO weather._1year.:measurement FROM _1month./^[a-z].*/ GROUP BY time(30m) fill(none) END

1 hour 10 years 86400

CREATE RETENTION POLICY _10years ON weather DURATION INF REPLICATION 1

CREATE CONTINUOUS QUERY CQ_1hour ON weather BEGIN SELECT median(value) as value INTO weather._10years.:measurement FROM _1year./^[a-z].*/ GROUP BY time(1h) fill(none) END

  • 144000 measurement, 2.3MB at 16Byte per measurement

Retention and Down-Sampling (Option 2)

sample rate/aggregate retention # of values InfluxDB Retention and Continuous Query Setup
0.25 seconds (raw data) 1 hour 14400

CREATE RETENTION POLICY default ON weather DURATION 1h REPLICATION 1 DEFAULT

15 minutes 10 years 345600

CREATE RETENTION POLICY _10years ON weather DURATION 1d REPLICATION 1

CREATE CONTINUOUS QUERY CQ_15min ON weather BEGIN SELECT median(value) as value INTO weather._10years.:measurement FROM /^[a-z].*/ GROUP BY time(15m) fill(none) END

  • 360000 measurement, 5.76MB at 16Byte per measurement