Influxdb磁盘IO突然飙高处理记录
Influxdb磁盘IO突然负载飙高处理记录
相关参考：https://github.com/influxdata/telegraf/blob/master/plugins/inputs/mysql/README.md
Influxdb异常表现：grafana大量空白告警，登录查看发现“操作系统”仪表盘/面板没有数据（生产环境grafana由多个数据源组成，这里仪表盘——“操作系统”的数据源是influxdb）
登录堡垒机top查看发现机器：
① 内存飙高
② 负载飙高至10以上
但CPU使用率相对稳定
那这里的负载高应该是磁盘IO高导致负载飙高
机器自身内存/磁盘IO异常排查
# iostat -x -N 3
avg-cpu: %user %nice %system %iowait %steal %idle
8.43 0.00 0.42 10.52 0.00 80.63
Device: rrqm/s wrqm/s r/s w/s rkB/s wkB/s avgrq-sz avgqu-sz await r_await w_await svctm %util
sdd 0.00 0.00 0.00 35.33 0.00 17498.67 990.49 12.18 334.14 0.00 334.14 22.61 98.90
sda 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
sdb 0.00 0.00 0.00 4.67 0.00 17.33 7.43 0.07 14.50 0.00 14.50 9.00 4.20
sdc 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
centos-root 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
centos-swap 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
zlsd-lvdata 0.00 0.00 0.00 40.33 0.00 17686.67 877.02 12.25 294.40 0.00 294.40 20.79 99.87
发现确实是 磁盘写IO问题，且位于数据分区data上，那基本就是influxdb查询过于复杂或数据量过大导致的IO负载飙高
对于时序性数据库Influxdb来说，猜测是数据量上来了导致的
# 登录influxdb
influx -username 'telegraf' -password 'telegraf' -port 58086 -database 'telegraf' -host 10.30.250.11
# 确认了下influxdb的数据保留策略：
> show retention policies
name duration shardGroupDuration replicaN default
---- -------- ------------------ -------- -------
autogen 0s 168h0m0s 1 false
default 720h0m0s 24h0m0s 1 true
数据保留30天，是没动过的，查了下相关资料：
influxdb内存占用高：influxdb默认为 inmem内存型索引——influxdb在启动时，需要将索引加入到内存中
处理方案：
将influxdb内存索引改为磁盘索引，重启influxdb；改为磁盘索引后内存问题似乎内存暂时正常了（也可能不是配置有效，只是因为重启了内存才降下来）但磁盘IO在运行之后仍然高居不下
针对influxdb磁盘读写IO问题：
① 尝试调整influxdb落盘的吞吐量，配置异步刷盘————降低磁盘io压力
vim /etc/influxdb/influxdb.conf
... ...
wal-fsync-delay = "1s" # 默认为0，每次都落盘，修改为1s，等待一秒再一起写入——异步刷盘
compact-throughput = "64m" # 默认为 48m，修改为 64m
② 批量写入策略：目前由telegraf采集数据写入到influxdb，该方案无法施行
重启发现也是运行了一段时间后磁盘IO飙高，仍然没有实质解决问题
influxdb数据量/series排查
暂时没办法从磁盘IO入手，尝试从influxdb的数据量入手排查：查看监控发现原数据量稳定在20~40G（依据数据保留策略，保留15/30天），目前竟已经飙升到80G
查看influxdb的库表数据量占用，发现Influxdb无法通过类似mysql库表直接查询统计各库表的数据量，但了解到influxdb有另一个概念：series
series是influxdb的序列，可以通过series的数量大致了解库表的数据情况
influx -username 'telegraf' -password 'telegraf' -port 58086 -database 'telegraf' -host 10.30.250.11
> use telegraf
简单查看下telegraf库中的监控数据列表
> show measurements
----
bond
bond_slave
cpu
disk
elasticsearch_cluster_health
elasticsearch_cluster_health_indices
elasticsearch_http
elasticsearch_indices
elasticsearch_jvm
elasticsearch_process
elasticsearch_transport
filestat
hugepages
inode
internal_agent
internal_gather
internal_memstats
internal_write
io
javajvm
javajvmutil
jenkins
kernel
kernel_vmstat
mem
mysql
mysql_innodb
mysql_perf_schema
mysql_process_list
mysql_table_schema
mysql_table_schema_version
mysql_users
mysql_variables
net
netstat
paging
processes
procstat
procstat_lookup
prometheus
queue
redis
redis_keyspace
sdk
sensors
smart_device
snmp_interfaces_statistics
snmp_snmp
swap
system
task
> show series cardinality
cardinality estimation
----------------------
12047420
发现telegraf 有1200万series，相当于创建了100万条索引,说明拿了一个基数很大的字段作为索引，也就是有个表字段（被用作索引的字段）的基数突然激增
因为influxdb无法直接对表级别进行分析，这里使用influx_inspect工具对shard进行分析
[root@tmp_241025 default]# influx_inspect report -detailed 4
... ...
Statistics
Series:
- telegraf (est): 12047420 (100%)
Total (est): 12047420
Measurements (est):
- bond: 24 (0%)
- kernel: 1091 (0%)
- kernel_vmstat: 25625 (0%)
- mem: 7215 (0%)
- mysql: 9616 (0%)
- mysql_innodb: 1626 (0%)
- mysql_perf_schema: 10995813 (91%) # 这里发现mysql_perf_schema表有1000万的series
尝试切进去看看mysql_perf_schema表的数据长什么样，查询FILED/TAG情况
> SHOW FIELD KEYS FROM mysql_perf_schema
name: mysql_perf_schema
fieldKey fieldType
-------- ---------
events_statements_errors_total float
events_statements_no_index_used_total float
events_statements_rows_affected_total float
events_statements_rows_examined_total float
events_statements_rows_sent_total float
events_statements_seconds_total float
events_statements_sort_merge_passes_total float
events_statements_sort_rows_total float
events_statements_tmp_disk_tables_total float
events_statements_tmp_tables_total float
events_statements_total float
events_statements_warnings_total float
events_waits_seconds_total float
events_waits_total float
file_events_bytes_totals float
file_events_seconds_total float
file_events_total float
index_io_waits_seconds_total_delete float
index_io_waits_seconds_total_fetch float
index_io_waits_seconds_total_insert float
index_io_waits_seconds_total_update float
index_io_waits_total_delete float
index_io_waits_total_fetch float
index_io_waits_total_insert float
index_io_waits_total_update float
read float
read_high_priority float
read_no_insert float
read_normal float
read_with_shared_locks float
table_io_waits_seconds_total_delete float
table_io_waits_seconds_total_fetch float
table_io_waits_seconds_total_insert float
table_io_waits_seconds_total_update float
table_io_waits_total_delete float
table_io_waits_total_fetch float
table_io_waits_total_insert float
table_io_waits_total_update float
write float
write_allow_write float
write_concurrent_insert float
write_low_priority float
write_normal float
> SHOW TAG KEYS FROM mysql_perf_schema
name: mysql_perf_schema
tagKey
------
digest
digest_text
event_name
host
hostenv
hostip
hosttag
index
masterip
mode
name
nodeid
perf_query
schema
server
table
发现表结构正常，字段写入应该没问题，尝试查一条数据
> select * from mysql_perf_schema limit 1
跑了一会发现跑不出来，再一看发现grafana又空白告警了——拿不到influxdb的数据，influxdb又挂了
锁定可疑表：mysql_perf_schema
Influxdb表异常排查
select一条数据都跑挂了，把数据从生产环境（cpu8核，机械硬盘，只跑监控的生产机器）导到测试环境数据库机器（cpu16核，固态硬盘）跑，以免影响生产环境监控系统：
① 尝试过先导到自己虚拟机发现根本跑不动
② 快速部署influxdb，压缩完数据才几个G，没有那么大了，快速导入导出
结合mysql_perf_schema表名，是mysql部分的监控项，先咨询DBA最近生产mysql是否有大SQL或大动作导致mysql监控数据/索引飙升——但答案是没有
再翻查了下资料：telegraf采集的mysql_perf_schema监控数据表的内容：表的结构（列名、列类型、列长度等）；表的索引信息。表的存储引擎、行数、数据大小等
那就在测试环境继续跑查询
> select * from mysql_perf_schema limit 1
趁有空看了下监控面板，发现是个不常用的仪表盘“数据库详情”，主要展示行数和延时的插入/更新/查询/删除情况，使用频率不高
继续翻查资料，发现是由 telegraf 中某个配置项启用的
[[inputs.mysql]]
gather_table_schema = true
这里就先预留着，如果没办法就把这个关了，让influxdb正常运行先
SQL跑了很久出来发现一条数据中，只有部分字段有值，很多字段没有值，猜测可能不是数据量激增的问题
重新返回查索引的问题——把mysql_perf_schema的索引全部导出来看（这里两条语句，好像部分版本不支持show series from telegraf.mysql_perf_schema这种写法？）
influx -host 192.168.18.140 -port 58086 "show series from telegraf.mysql_perf_schema" > /data1/influxdb/mysql_perf_schema.series
influx -host 192.168.18.140 -port 58086 -database 'telegraf' -execute "show series from mysql_perf_schema" > /data1/influxdb/mysql_perf_schema.series
跑了一段时间，ll查看发现有415M的索引！（刚跑数据一直卡着为0，属于正常现象）
# ll mysql_perf_schema.series
-rw-r--r--. 1 root root 415M Jan 17 14:49 mysql_perf_schema.series
# cat mysql_perf_schema.series | wc -l
2202215
查看行数有220万条series，有点太多了。。。
# less mysql_perf_schema.series 看看
发现有很多表相关的索引，尝试过滤table
# grep "table" mysql_perf_schema.series > tables.txt
# cat tables.txt | wc -l
统计表series行数，发现竟然还有100万
尝试将其中的表去重
# awk -F, '!seen[$NF]++' tables.txt > table_num.txt # 我这里直接导出来多个文件对比查看了
# wc -l table_num.txt
110477 table_num.txt
⭐mysql_perf_schema将（采集mysql数据库的表名——table）table字段作为索引，而table现在有10万；那排查下这里series中的table字段
# less table_num.txt
mysql_perf_schema,host=cti23n2,hostenv=callcenter,hostip=10.30.2.23,hosttag=cti23n2,nodeid=2,perf_query=external_lock_waits_seconds_total,schema=sdp,server=/tmp/mysql.sock,table=cti_taskphone_00000180000211082658049
发现有很多都是cti_taskphone_0000018000021开头的表，且host都是cti23n2；再次过滤
# grep cti23n2 num.txt | wc -l
50698
到这里大致知道是哪里的问题了
数据库—表 跟踪
咨询DBA，这是呼叫中心的表
# 登录到对应机器（呼叫中心）的mysql数据目录
cd /home/mysql/data/sdp
# ll | wc -l
107010
发现确实有 10 万个表
再次查看发现是应该是上个月月底开始疯狂创表的
# ll | grep Jan | wc -l
95352
# ll | grep Dec | wc -l
11451
找到开发，确认问题根本：
自上个月月底上线功能“渐进式外呼”，每个外呼都会自动创建一张表，但创建完没有删除，导致呼叫中心表累计，对于拿table作为索引的influxdb来说，10万的表需要创建220万的索引，而数据量却实际没有那么多，导致influxdb在涉及到mysql_perf_schema的操作时都会被大量表拖累
后续处理:
问题：业务会产生大量一次性表，这些表会当天删除，但是对应监控来说，这些表会被存于influxdb，因为influxdb根据表名做索引，因此在插入/查数据时会因为这一点被拖累
直接处理方式：通知开发调用接口定时清理/删除这些任务表（无法有效解决问题），以及是否可以将表数量控制下来（根治办法）
但删除表只是不存在于现在，对于时序性数据库influxdb来说，这些油瓶表仍然存在，直到保留策略过期
方法一：在Influxdb上定期清理无用/垃圾监控数据（难以实现，因为涉及mysql_perf_schema表的标记别操作都非常难跑出来）
# 从简单的方式入手，定期删除 mysql_perf_schema 这个（influxdb监控数据表，也就是反向索引表）表中以cti_taskphone开头的数据（也就是油瓶表的统一表名前缀）
cat > /data/influxdb/influxdb_clean.sh << EOF
#!/bin/bash
/usr/bin/influx -host 10.30.250.11 -port 58086 -database 'telegraf' -execute "DELETE FROM mysql_perf_schema WHERE table =~ /^cti_taskphone/ AND time < now() - 10m"
EOF
chmod +x /data/influxdb/influxdb_clean.sh
crontab -e
*/10 * * * * /bin/bash /data/influxdb/influxdb_clean.sh
# 早于当前时间10分钟的数据
influx -host 10.30.250.11 -port 58086 -database 'telegraf' -execute "DELETE FROM mysql_perf_schema WHERE table =~ /^cti_taskphone/ AND time < now() - 10m"
# 但是手动试了一下，发现有关mysql_perf_schema表的操作都相当慢，完全跑不出来，select limit 1 都跑不出来，行不通
方法二：选择性监控
# 参考资料：https://github.com/influxdata/telegraf/blob/master/plugins/inputs/mysql/README.md （注意按telegraf实际版本查看）
# 这里查阅了 telegraf相关资料，考虑是否可以配置不将 ^cti_taskphone 表， 纳入监控，但是似乎telegraf只支持库级别的监控选择，不支持表级别的
# 协调开发人员将表创建在单独的数据库，例如，cti库统一存放 ^cti_taskphone 表，这样就可以实现 跟标记别选择性监控同样效果，但似乎table_schema_databases配置项不生效？
# 这里实验性的处理方式是 不将cti的sdp库和smp的smp库纳入监控，仅监控 mysql 库和 sys库，但似乎不生效？或者说mysql和sys库这边也有受一次性表的影响
vim /etc/telegraf/telegraf.conf
[[inputs.mysql]]
metric_version = 2
servers = ["telegraf:DBAmon4zlsd2019@unix(/tmp/mysql.sock)/?loc=Local"]
perf_events_statements_digest_text_limit = 120
perf_events_statements_limit = 250
perf_events_statements_time_limit = 86400
table_schema_databases = ["mysql","sys"]
gather_table_schema = false
gather_process_list = false
gather_user_statistics = false
gather_info_schema_auto_inc = false
gather_innodb_metrics = false
gather_slave_status = false
gather_binary_logs = false
gather_table_io_waits = false
gather_table_lock_waits = false
gather_index_io_waits = false
gather_event_waits = false
gather_file_events_stats = false
gather_perf_events_statements = false
interval_slow = "60m"
总结，可能的处理方案
目前能想到的可能的解决方案：
① 协调开发人员将表创建在单独的数据库，即可通过 table_schema_databases 配置过滤库
② 二开telegraf，调整inputs.mysql 中对 INFORMATION_SCHEMA.TABLES 数据的处理
③ 二开influxdb，调整 outputs.mysql 中 namedrop 能够按 series/单条数据 的级别来过滤
③ 二开mysql，实现让mysql中的information_schema不采集部分用于任务的一次性表
我这里临时处理方案： 暂时关停脏机器mysql的schema采集
[[inputs.mysql]]
gather_table_schema = false
# systemctl restart telegraf && systemctl status telegraf
其他相关讨论：
① 关于在 outputs.influxdb段配置 namedrop 来过滤采集数据 为何无法实现 “telegraf不采集一次性表的监控数据”
① 首先inputs.mysql配置段只能用table_schema_databases来进行库级别的选择性监控
② namedrop只能对写入到influxdb的数据进行表级别的选择性输出，但那时，input.mysql已经将mysql的一个表变成四条series了，汇集到 mysql_perf_schema 指标
所以telegraf在output时，namedrop面向的是 mysql、mysql_perf_schema、cpu、memory这几个指标，已经找不到cti_taskphone*表了，因为他是已经作为数据存进mysql_perf_schema指标/measurement了；
简而言之，inputs.mysql需要的表级别选择性监控，但只有库级别table_schema_databases，而outputs.influxdb需要series/数据值级别的选择性输出，而不是表级别namedrop
流程简述：
说明：因为 gather_table_schema 是从 mysql的 information_schema库中的tables表拿的数据，所以本质上，inputs.mysql 根本就是拿的information_schema库中的表数据信息
参考链接：https://github.com/influxdata/telegraf/blob/v1.34.0/plugins/inputs/mysql/README.md
这是官方文档对相关配置的注释/说明
## if the list is empty, then metrics are gathered from all database tables
# table_schema_databases = []
## gather metrics from INFORMATION_SCHEMA.TABLES for databases provided
## in the list above
# gather_table_schema = false
浙公网安备 33010602011771号