InfluxDB环境搭建04

本节讲解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

Leave a Reply

Your email address will not be published. Required fields are marked *

*