Projects/InfluxDB Setup: Difference between revisions
| 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