前言

InfluxDB 是专为时序数据设计的数据库,能支撑大量的读写负载,是一个高性能的时序数据 datastore。

InfluxDB is the Time Series Database in the TICK Stack

更新历史

2019年07月25日 - 更新influx命令使用技巧
2019年04月05日 - 初稿

阅读原文 - https://wsgzao.github.io/post/influxdb/

扩展阅读

InfluxDB - https://www.influxdata.com/time-series-platform/influxdb/


InfluxDB 简介

InfluxDB is the Time Series Database in the TICK Stack

InfluxData’s TICK Stack is built around InfluxDB to handle massive amounts of time-stamped information. This time series database provides support for your metrics analysis needs, from DevOps Monitoring, IoT Sensor data, and Real-Time Analytics. Users can adapt their SQL skills with InfluxQL, so they can get up to speed on this time series database.

默认预留端口:

8086,HTTP API
8088,RPC 端口,用于备份和恢复

NTP 服务:
InfluxDB 使用机器本地时间作为 timestamp,需要机器之间使用 NTP 进行同步;如果没有同步的话,写入的时间序列数据可能会不准确

InfluxDB 安装

InfluxDB 里存储的数据被称为时间序列数据, InfluxDB 存储方式跟传统关系型数据库不同的是:传统关系型数据库通过数据库 + 表 + 字段组织数据,InfluxDB 通过指标、标签、字段组织数据,时间戳是默认的索引列,标签跟字段其实就相当于关系型数据库中的字段,只不过标签会被索引,而字段不会。

Grafana 默认支持的数据源:Graphite,InfluxDB,OpenTSDB,Prometheus,Elasticsearch,CloudWatch
Grafana 支持同时绑定多套数据源,根据自己需求管理即可,这里以InfluxDB为例。

https://portal.influxdata.com/downloads

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
# install influxdb
cat > /etc/yum.repos.d/influxdb.repo << 'EOF'
[influxdb]
name = InfluxDB Repository - RHEL \$releasever
baseurl = https://repos.influxdata.com/rhel/\$releasever/\$basearch/stable
enabled = 1
gpgcheck = 1
gpgkey = https://repos.influxdata.com/influxdb.key
EOF

# yum install influxdb
yum install -y influxdb

# start and enable influxdb
sudo systemctl start influxdb
sudo systemctl enable influxdb
sudo systemctl status influxdb

# 通过influx命令进入cli命令行
influx
Connected to http://localhost:8086 version 1.4.2
InfluxDB shell version: 1.4.2
>

# 查看用户
SHOW USERS

# 创建用户
CREATE USER influx WITH PASSWORD 'influx' WITH ALL PRIVILEGES

# 查看用户
SHOW USERS

# 创建数据库
CREATE DATABASE test

# 查看数据库
SHOW DATABASES

# Using 数据库
USE test

# 插入数据
INSERT cpu,host=192.168.1.1 load=0.1,usage=0.2

# 查询所有数据
SELECT * FROM "cpu"
SELECT "host","load","usage" FROM "cpu"

# 根据条件查询
SELECT "host","load","usage" FROM "cpu" WHERE "host" = '192.168.1.1'
SELECT "host","load","usage" FROM "cpu" WHERE "usage" > 0.1

# 创建数据库
CREATE DATABASE "db_name"
# 显示所有数据库
SHOW DATABASES
# 删除数据库
DROP DATABASE "db_name"

# 使用数据库
USE mydb
# 显示该数据库中的表
SHOW MEASUREMENTS
# 删除表
DROP MEASUREMENT "t_name"

# 简单查询
SELECT * FROM codis_usage ORDER BY time DESC LIMIT 3
# 最近60min内的数据
SELECT * FROM codis_usage WHERE time >= now() - 60m;
# 获取最近更新数据,并转换为当前时间
precision rfc3339
select * from codis_usage order by time desc limit 10;


# 查询保存策略
show retention policies on codis
name duration shardGroupDuration replicaN default
---- -------- ------------------ -------- -------
autogen 0s 168h0m0s 1 true

# 创建新的 Retention Policies 并设置为默认值
# duration 保留多少天
# replication 副本数
create retention policy "rp_14d" ON "codis" duration 14d replication 1 default

# 恢复默认策略(永久保存)
alter retention policy "autogen" on "codis" duration 0s replication 1 default


# 创建数据库 API
curl -i -XPOST http://localhost:8086/query --data-urlencode "q=CREATE DATABASE test"

# 写入数据 API

# 写入单条
curl -i -XPOST http://localhost:8086/write?db=test --data-binary "cpu,host=192.168.1.3 load=0.1,usage=0.33"
curl -i -XPOST http://localhost:8086/write?db=test --data-binary "cpu,host=192.168.1.3 load=0.1,usage=0.33 6666666666666666666"

# 写入多条
curl -i -XPOST http://localhost:8086/write?db=test --data-binary "cpu,host=192.168.1.2 load=0.1,usage=0.22 1666666666666666661
cpu,host=192.168.1.3 load=0.1,usage=0.33 1666666666666666661
cpu,host=192.168.1.2 load=0.2,usage=0.22 1666666666666666662
cpu,host=192.168.1.3 load=0.2,usage=0.33 1666666666666666662"

# 查询数据 API
curl -G http://localhost:8086/query?db=test --data-urlencode "q=SELECT * FROM \"cpu\""

influx命令

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
[root@sg-gop-10-71-12-145 wangao]# influx
Connected to http://localhost:8086 version 1.7.7
InfluxDB shell version: 1.7.7
> help
Usage:
connect <host:port> connects to another node specified by host:port
auth prompts for username and password
pretty toggles pretty print for the json format
chunked turns on chunked responses from server
chunk size <size> sets the size of the chunked responses. Set to 0 to reset to the default chunked size
use <db_name> sets current database
format <format> specifies the format of the server responses: json, csv, or column
precision <format> specifies the format of the timestamp: rfc3339, h, m, s, ms, u or ns
consistency <level> sets write consistency level: any, one, quorum, or all
history displays command history
settings outputs the current settings for the shell
clear clears settings such as database or retention policy. run 'clear' for help
exit/quit/ctrl+d quits the influx shell

show databases show database names
show series show series information
show measurements show measurement information
show tag keys show tag key information
show field keys show field key information

A full list of influxql commands can be found at:
https://docs.influxdata.com/influxdb/latest/query_language/spec/
>

用户权限管理

InfluxDB 的权限设置比较简单,只有读、写、ALL 三种,详细参考 官方文档 。默认不开启用户认证,需要修改配置文件:

1
2
[http]
auth-enabled = true

https://docs.influxdata.com/influxdb/v1.7/administration/authentication_and_authorization/

常见命令如下:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
# 授权
GRANT [READ,WRITE,ALL] ON <database_name> TO <username>
GRANT ALL PRIVILEGES TO "username"

# 撤销权限
REVOKE [READ,WRITE,ALL] ON <database_name> FROM <username>
REVOKE ALL PRIVILEGES FROM "username"

# 查看权限
SHOW GRANTS FOR <user_name>

# 显示用户
SHOW USERS

# 创建用户
CREATE USER "readonly" WITH PASSWORD 'password'

# 创建管理员权限的用户
CREATE USER "readonly" WITH PASSWORD 'password' WITH ALL PRIVILEGES

# 删除用户
DROP USER "readonly"

# 修改密码
SET PASSWORD FOR <username> = '<password>'

数据保存策略

也就是 Retention Policies,可以设置保存的时间,例如保存 30 天
https://docs.influxdata.com/influxdb/v1.7/guides/downsampling_and_retention/

1
2
3
4
5
6
7
8
9
10
11
# 查询
SHOW RETENTION POLICIES ON "database_name";

# 新建
CREATE RETENTION POLICY "rp_name" ON "db_name" DURATION 30d REPLICATION 1 DEFAULT;

# 修改
ALTER RETENTION POLICY "rp_name" ON db_name DURATION 3w DEFAULT;

# 删除
DROP RETENTION POLICY "rp_name" ON "db_name";

连续查询

也就是 Continuous Queries,当数据超过保存策略里指定的时间之后,就会被删除;可以通过连续查询把原来的秒级数据,保存为分钟级或者小时级的数据,从而减小数据的占用空间。
https://docs.influxdata.com/influxdb/v1.7/query_language/continuous_queries/

1
2
3
4
5
6
7
8
9
#查看
SHOW CONTINUOUS QUERIES;

# 创建
CREATE CONTINUOUS QUERY cq-name ON db-name BEGIN
SELECT mean(tbl-name) INTO newtbl-name FROM tbl-name GROUP BY time(30m) END;

# 删除
DROP CONTINUOUS QUERY <cq-name> ON <db-name>;

备份恢复

只支持全量备份,不支持增量,包括了元数据以及增量数据的备份,可以参考 官方文档
https://docs.influxdata.com/influxdb/v1.7/administration/backup_and_restore/

1
2
3
4
5
6
7
8
9
10
11
# 元数据备份
influxd backup <path-to-backup>

# 数据备份
influxd backup -database <mydatabase> <path-to-backup>
influxd backup -database telegraf -retention autogen -since 2016-02-01T00:00:00Z /tmp/backup
influxd backup -database mydatabase -host 10.0.0.1:8088 /tmp/remote-backup

# 恢复
influxd restore -metadir /var/lib/influxdb/meta /tmp/backup
influxd restore -database telegraf -datadir /var/lib/influxdb/data /tmp/backup

time 格式显示设置

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
# 方法1
$ influx -precision rfc3339

# 方法2
$ influx
Connected to http://localhost:8086 version 0.xx.x
InfluxDB shell 0.xx.x
> precision rfc3339

> precision rfc3339
> select * from codis_usage order by time desc limit 10;
name: codis_usage
time admin_addr hostname ops_fails ops_qps ops_redis_errors ops_total product_name proxy_addr runtime_gc_num runtime_gc_total_pausems runtime_num_cgo_call runtime_num_goroutines runtime_num_mem_offheap runtime_num_procs rusage_cpu rusage_mem sessions_alive sessions_total token
---- ---------- -------- --------- ------- ---------------- --------- ------------ ---------- -------------- ------------------------ -------------------- ---------------------- ----------------------- ----------------- ---------- ---------- -------------- -------------- -----
2019-07-25T10:48:43.186693914Z 10.71.15.97:11080 sg-gop-xxx-xxx-xxx-xx gop-codis-misc 10.71.15.97:6389 2455469 49217536 8 456200192 58 2bdaa076222fec82f9204310255cc2e3
2019-07-25T10:48:43.160702877Z 10.71.15.98:11080 sg-gop-xxx-xxx-xxx-xx 0 44 12258 8381660 gop-codis-misc 10.71.15.98:6389 5099 426 2455685 266 48037888 8 0 390696960 56 12404 b2d20caf9f3c9af555cb9a9fa5c07844
2019-07-25T10:48:42.838745142Z 10.71.15.96:11080 sg-gop-xxx-xxx-xxx-xx 0 0 0 0 gop-codis-data 10.71.15.96:6389 79 6 37247 26 0 8 0 142471168 0 0 47629170465564037278443f7fc0a2b0
2019-07-25T10:48:42.786535306Z 10.71.14.113:11080 localhost.localdomain 0 9740 4 32159612367 gop-codis 10.71.15.112:6389 27922 10471 76744489 8754 1073741824 8 0.809847412599755 4961280000 4304 45663317 e72634f3a81e4edcf24dca9454e22c1b
2019-07-25T10:48:42.727826769Z 10.71.14.112:11080 localhost.localdomain 0 9602 2 33816047337 gop-codis 10.71.15.112:6389 31458 11034 77809963 8806 1073741824 8 0.7998623956727604 4950290432 4330 45663036 646f70b8381ca449cad4bb1316e17b78
2019-07-25T10:48:42.598859131Z 10.71.15.95:11080 sg-gop-xxx-xxx-xxx-xx 0 0 0 0 gop-codis-data 10.71.15.95:6389 80 5 37771 26 0 8 0 140034048 0 0 939a496343900150dc87238b953bb14b
2019-07-25T10:48:42.333392816Z 10.71.15.91:11080 sg-gop-xxx-xxx-xxx-xx 0 4034 0 93719633 gop-codis-pay 10.71.15.91:6389 5122 445 2463785 4406 876150784 8 0.36992110507631487 1682124800 2126 4836 64ebb14da86e74313c0f5dca10336a4d
2019-07-25T10:48:42.322101493Z 10.71.15.92:11080 sg-gop-xxx-xxx-xxx-xx 0 3721 0 89276911 gop-codis-pay 10.71.15.92:6389 5122 450 2463671 4388 895614976 8 0.3399291832331154 1677733888 2117 4750 7b76feefc30f93483d7b8e5432084f34
2019-07-25T10:48:42.227508795Z 10.71.15.93:11080 sg-gop-xxx-xxx-xxx-xx 0 405 0 15300438 gop-codis-auth 10.71.15.93:6389 5096 434 2446269 1504 414908416 8 0.04998992792934094 613736448 675 1592 7a320ee2bb9fe9cecfdd685a5fbfbc1c
2019-07-25T10:48:42.194695137Z 10.71.15.94:11080 sg-gop-xxx-xxx-xxx-xx 0 524 0 15128150 gop-codis-auth 10.71.15.94:6389 5096 421 2446079 1486 409600000 8 0.04998935201807339 492064768 666 1546 6b516b3b0344063a6936fc13c40fc40a
>

其它

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
# 获取最近更新数据,并转换为当前时间
select threads_running from mysql order by time desc limit 1;
date -d @`echo 1483441750000000000 | awk '{print substr($0,1,10)}'` +"%Y-%m-%d %H:%M:%S"

# 检查系统是否存活
$ curl -sl -I localhost:8086/ping

# 简单查询
SELECT * FROM weather ORDER BY time DESC LIMIT 3;

# 指定时间范围,时间格式也可以为'2017-01-03 00:00:00'
SELECT usage_idle FROM cpu WHERE time >= '2017-01-03T12:40:38.708Z' AND time <= '2017-01-03T12:40:50.708Z';

# 最近40min内的数据
SELECT * FROM mysql WHERE time >= now() - 40m;

# 最近5分钟的秒级差值
SELECT derivative("queries", 1s) AS "queries" from "mysql" where time > now() - 5m;

# 最近5min的秒级写入
$ influx -database '_internal' -precision 'rfc3339'
-execute 'select derivative(pointReq, 1s) from "write" where time > now() - 5m'

# 也可以通过日志查看
$ grep 'POST' /var/log/influxdb/influxd.log | awk '{ print $10 }' | sort | uniq -c
$ journalctl -u influxdb.service | awk '/POST/ { print $10 }' | sort | uniq -c

InfluxDB 配置优化

配置文件默认全部注释掉,使用默认的配置项,可以根据需要配置。每个配置项有对应的、相同功能的环境变量。

配置文件:/etc/influxdb/influxdb.conf

查看配置:influxd config

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
[meta]                                # 元数据
dir = /var/lib/influxdb/meta

[data] # 数据/WAL
dir = /var/lib/influxdb/data
wal-dir = /var/lib/influxdb/wal
cache-max-memory-size = "1g" # Cache最大可用内存

[coordinator] # 查询相关
query-timeout = "0s" # 查询最大执行时间
log-queries-after = "0s" # 打印慢查询

[http] # HTTP服务
auth-enabled = false # 启用安全认证
max-connection-limit = 0 # 最大连接数

更多帮助信息请参考官网
InfluxDB documentation - https://docs.influxdata.com/platform/introduction

文章目录
  1. 1. 前言
  2. 2. 更新历史
  3. 3. InfluxDB 简介
  4. 4. InfluxDB 安装
  5. 5. influx命令
    1. 5.1. 用户权限管理
    2. 5.2. 数据保存策略
    3. 5.3. 连续查询
    4. 5.4. 备份恢复
    5. 5.5. time 格式显示设置
    6. 5.6. 其它
  6. 6. InfluxDB 配置优化