本节讲解InfluxDB的部分高级功能
时间序列数据处理的数据量会十分大，而且很多数据只是在一定时期内意义会更大一些，比如服务器性能日志。
所以，一般来说近期数据会按采样间隔全量储存，越远的数据，需要保存的采样间隔就会越大。
比如，服务器性能日志：
近一周的，可以按10s采样一次
近一月的，可以按5m采样一次
近三个月的，可以按1h采样一次
近半年的，可以按1d采样一次
近一年的，可以按1w采样一次
再之前的，可以删除
这样降低采样率的操作，在时间序列数据库中的应用场景很多，所以一般都会进行支持。
InfluxDB，采用了两个方法来解决这个问题：
通过Continuous Query定期降低采样频率
通过Retention Policy定期删除高频采样数据
1、Continuous Query
> SHOW CONTINUOUS QUERIES
> CREATE CONTINUOUS QUERY "my_cq" ON "NOAA_water_database"
RESAMPLE EVERY 1w
BEGIN
SELECT MEAN("water_level") INTO "water_level_averages_coyote_creek_4w" FROM "h2o_feet" WHERE "location" = 'coyote_creek' GROUP BY time(4w)
END
> CREATE CONTINUOUS QUERY "my_cq" ON "NOAA_water_database"
BEGIN
SELECT MEAN("water_level") INTO "transportation"."weeks24"."water_level_averages_coyote_creek_4w" FROM "h2o_feet" WHERE "location" = 'coyote_creek' GROUP BY time(4w)
END
> DROP CONTINUOUS QUERY "my_cq" ON "NOAA_water_database"
2、Retention Policy
> CREATE RETENTION POLICY "my_rp" ON "my_database" DURATION 3d REPLICATION 1 SHARD DURATION 1h > CREATE RETENTION POLICY "my_rp" ON "my_database" DURATION 3d REPLICATION 1 SHARD DURATION 1h default > DROP RETENTION POLICY "my_rp" ON "my_database"
3、新建数据库
# 默认RP是autogen > CREATE DATABASE "my_database" # 新建数据库，默认RP是my_rp # my_rp：数据库数据保留3天，shard保留1份数据，shard group按1小时保留数据 > CREATE DATABASE "my_database" WITH DURATION 3d REPLICATION 1 SHARD DURATION 1h NAME "my_rp"
4、删除数据库
> drop database NOAA_water_database
5、删除series
> DROP SERIES FROM "h2o_feet" > DROP SERIES FROM "h2o_feet" WHERE "location" = 'santa_monica' > DROP SERIES WHERE "location" = 'santa_monica'
6、删除series中数据
> DELETE FROM "h2o_feet" > DELETE FROM "h2o_quality" WHERE "randtag" = '3' > DELETE WHERE time < '2016-01-01'
7、删除measurement
> DROP MEASUREMENT "h2o_feet"
8、删除shard
> show shards name: _internal id database retention_policy shard_group start_time end_time expiry_time owners -- -------- ---------------- ----------- ---------- -------- ----------- ------ 18 _internal monitor 18 2020-07-01T00:00:00Z 2020-07-02T00:00:00Z 2020-07-09T00:00:00Z name: NOAA_water_database id database retention_policy shard_group start_time end_time expiry_time owners -- -------- ---------------- ----------- ---------- -------- ----------- ------ 19 NOAA_water_database autogen 19 2019-08-05T00:00:00Z 2019-08-12T00:00:00Z 2019-08-12T00:00:00Z 10 NOAA_water_database autogen 10 2019-08-12T00:00:00Z 2019-08-19T00:00:00Z 2019-08-19T00:00:00Z 11 NOAA_water_database autogen 11 2019-08-19T00:00:00Z 2019-08-26T00:00:00Z 2019-08-26T00:00:00Z 12 NOAA_water_database autogen 12 2019-08-26T00:00:00Z 2019-09-02T00:00:00Z 2019-09-02T00:00:00Z 13 NOAA_water_database autogen 13 2019-09-02T00:00:00Z 2019-09-09T00:00:00Z 2019-09-09T00:00:00Z 14 NOAA_water_database autogen 14 2019-09-09T00:00:00Z 2019-09-16T00:00:00Z 2019-09-16T00:00:00Z 15 NOAA_water_database autogen 15 2019-09-16T00:00:00Z 2019-09-23T00:00:00Z 2019-09-23T00:00:00Z > drop shard 10
9、删除measurement
> DROP MEASUREMENT "h2o_feet"
10、删除measurement
> DROP MEASUREMENT "h2o_feet"
11、杀死慢查询
> show queries qid query database duration status --- ----- -------- -------- ------ 78 SHOW QUERIES NOAA_water_database 58µs running > kill query 78