mysql数据迁移influxdb_mysql数据库空间的influxDB增量统计

业务系统中RDS的mysql的空间捉襟见肘时就会想起来需要做一下容量的统计和趋势监控

实施的方案是：

定时用python从mysql的information_schema库中的TABLES表中抽取DATA_LENGTH+INDEX_LENGTH的数据以及DATA_FREE的数据来做统计项

将数据抽取到influxDB中备查

核心的查询语句：

123456789101112131415161718# 1048576 字节 等于 1MB

# 监控门槛：

# DATA_FREE>= 6MB (可优化空间大于6MB)

# TABLE_ROWS>= 100k (10万行以上)

# DATA_LENGTH>= 20MB (数据空间占用大于20MB)

# INDEX_LENGTH>= 20MB (索引空间占用大于20MB)

# `TABLE_SCHEMA` IN ('{"','".join(self.check_dbs)}')

sql = f""" SELECT `TABLE_SCHEMA`,`TABLE_NAME`, CONCAT_WS('.',`TABLE_SCHEMA`,`TABLE_NAME`) AS DBTABLENAME,(`DATA_FREE`/1048576) AS free_len_mb,(`DATA_LENGTH`+ `INDEX_LENGTH`)/1048576 AS row_len_mb,(`TABLE_ROWS`/1000) AS rows_kb ,(`DATA_LENGTH`/1048576) AS dat_len_mb,(`INDEX_LENGTH`/1048576) AS idx_len_mbFROM `information_schema`.`TABLES` WHERE`TABLE_SCHEMA` IN ('{"','".join(self.check_dbs)}')AND (`DATA_FREE`>=6291456 OR TABLE_ROWS >= 100000 OR DATA_LENGTH>=20971520 OR INDEX_LENGTH>=15728640 )ORDER BY free_len_mb DESC,row_len_mb DESC,dat_len_mb DESC,idx_len_mb DESC, `TABLE_SCHEMA` ASC,`TABLE_NAME` ASC"""

然后单行的influxdb的表结构：

12345678910111213141516tsdb_item_record = {

'measurement': 'dbtable_stats',

'tags': {

'db': db,

'table':table,

'dbtable':dbtable,

},

'time': f'{utcnow.strftime("%Y-%m-%dT%H:%M:%S")}Z',

'fields': {

'free_len_mb':free_len_mb,

'row_len_mb':row_len_mb,

'rows':rows_kb,

'dat_len_mb':dat_len_mb,

'idx_len_mb':idx_len_mb

}

}

这样在influxdb中我们就有了每个埋点间隔下统计的数据信息，接下来在grafana中可以用InfluxDB的查询函数统计出对应的信息并展示图表

按时间查询各个表的空间占用(数据+索引)的增长量：

1SELECT cumulative_sum(difference(mean("row_len_mb"))) FROM "dbspace_watches_rp"."dbtable_stats" WHERE $timeFilter GROUP BY time(5m), "dbtable" fill(null)

再加上一下查询起始时的值就是当下的总数据量的表数据空间排名：

1SELECT first("row_len_mb") + cumulative_sum(difference(mean("row_len_mb"))) FROM "dbspace_watches_rp"."dbtable_stats" WHERE $timeFilter GROUP BY time(5m), "dbtable" fill(null)

同理也可以得到库表的DATA_FREE也就是可优化空间的增长量和排名在此不再赘述

另外提一句，mysql在做optmize释放空间的时候会锁表，所以还是在业务空闲期做这些释放空间的活比较好

另外可以顺便求一下倒数得到查询时间区间内的各表的增长率，方便发现特别高增长的表做对应的优化

1SELECT derivative(cumulative_sum(difference(mean("row_len_mb"))), 10m) FROM "dbspace_watches_rp"."dbtable_stats" WHERE $timeFilter GROUP BY time(1h), "dbtable" fill(null)