云数据库Mysql磁盘使用率高排查优化建议

【场景】

MySQL实例可能长时间运行，未进行磁盘及数据管理，导致磁盘使用率升高。尤其当实例显示“磁盘空间满”状态，且数据库不可进行写入操作，会导致实例异常、数据库备份失败潜在风险、数据库实例只读状态。从而影响业务正常运行。

本文档通过分析及解决以下引起磁盘使用率高的场景，提前预防磁盘使用率高导致实例异常，只读状态的问题。

1)        磁盘利用率月/季度平均增长过快。

2)        delete删除数据后磁盘空间使用率无变化

3)        冷数据迁移

4)        大事务快速生成Binlog文件

5)        查询产生临时文件过大

6)   大事务未提交前产生binlog cache文件

【场景1】每月/每季度磁盘利用率平均增长过快

例如MySql数据库总容量560G，根据近期3个月的磁盘监控数据分析每月的数据增长率占磁盘容量约10%（56G），当前磁盘使用率81.06%，按每月10%的增长率，当前容量2月后磁盘使用率到100%，存在磁盘满后数据库备份失败潜在风险，数据库状态只读风险。

【分析】：查询数据库磁盘监控指标

【解决方法】：建议扩容磁盘容量，扩容大小=（当前磁盘总容量）560G*10%（月数据增量率）*X（月），根据实际情况制定扩容计划（按月扩容、按季度扩容）

操作步骤:https://support.huaweicloud.com/usermanual-rds/zh-cn_topic_scale_cluster.html

【场景2】delete删除数据后磁盘空间使用率无变化

【分析】：DELETE只是将数据标识位删除，并没有整理或者清理数据文件，当插入新数据后，会再次使用这些被置为删除标识的记录空间

【解决方法】：innodb引擎的表通过以下方法回收空间：

步骤一：show variables like 'innodb_file_per_table'; --返回值ON时使用步骤二、返回值OFF时使用步骤三

步骤二：

ALTER TABLE '表名' ENGINE=InnoDB;

ANALYZE TABLE '表名';

步骤三、OPTIMIZE TABLE '表名';或者如图所示。

Mysql官网参考https://dev.mysql.com/doc/refman/5.7/en/optimize-table.html

步骤四：通过以下SQL命令返回结果，分析查看数据库的文件大小，分析其中可以删除的历史数据文件或无用数据文件。

SELECT file_name, concat(TOTAL_EXTENTS,'M') as 'FIle_size' FROM INFORMATION_SCHEMA.FILES  order by TOTAL_EXTENTS

注：如果您使用的delete from tablename语句清空当前表的所有记录时，建议使用truncate table tablename语句可以清空表空间、索引文件。大批量删除事务会使表长时间锁定，建议在维护时间清理空间。

【场景3】冷数据迁移

冷数据是对于离线类不经常访问的数据，比如企业备份数据、业务与操作日志数据、话单与统计数据怎么迁移。

【解决方法】使用DAS工具导出功能或者mysqldump工具，将数据备份到其他服务器上。参考如下链接。

方法一：https://support.huaweicloud.com/usermanual-das/das_04_0041.html

方法二：https://support.huaweicloud.com/eu-west-0-usermanual-rds/zh-cn_topic_0042423727.html

【场景4】大事务快速生成Binlog文件，导致磁盘占满

【分析】：查询磁盘监控指标，在1个小时内的磁盘使用率明显异常时，查询近期binglog日志文件个数及大小。

【解决方法】修改binlog日志文件保存周期。

设置MySQL本地Binlog:https://support.huaweicloud.com/usermanual-rds/rds_05_0037.html

【场景5】查询产生临时文件过大

【分析】查询数据库慢日志，根据慢日志分析数据量大，行数多，响应时间长的sql语句分析这类sql查询，易产生临时文件。通过Explain解析sql的额外信息，当返回Extra字段当出现using index时，说明SQL使用覆盖索引，性能较好；而当出现 using filesort、using temporary、using where时，说明查询需要优化。

【解决方法】应该优化SQL语句。避免频繁使用 order by、group by 操作，可以适当调大tmp_table_size和max_heap_table_size，但是为了减少磁盘使用而调高 tmp_table_size 和 max_heap_table_size，导致内存使用率升高不划算，因为内存资源远比磁盘资源宝贵；可以通过explain+SQL语句/或者使用DAS “SQL诊断”工具查看是否使用内部临时表，在 Extra 字段中有 Using temporary 字样的代表会使用内部临时表 例如：

explain select * from test_table group by created_on order by default;

【场景6】大事务未提交前产生binlog cache文件，导致磁盘满，主实例异常

【分析】       未提交事务，指开启事务后，长时间未向MySQL发出SQL执行请求或事务处理(COMMIT/ROLLBACK)请求，在系统表`information_schema`.`INNODB_TRX` 中状态为RUNNING，而在`information_schema`.`PROCESSLIST`中状态为SLEEP。

这类问题一般产生原因都是应用一次性批量插入或者批量删除大量的数据，事务执行时间长，binglog cache缓存不足，产生大量临时缓存文件。如果一个会话(连接)里面有一个未提交事务，然后不做任何操作，那么这个线程处于SLEEP态。例如通过下列SQL语句查询当前数据库中

SELECT t.trx_mysql_thread_id

,t.trx_state

,t.trx_tables_in_use

,t.trx_tables_locked

,t.trx_query

,t.trx_rows_locked

,t.trx_rows_modified

,t.trx_lock_structs

,t.trx_started

,t.trx_isolation_level

,p.time

,p.user

,p.host

,p.db

,p.command

FROM   information_schema.innodb_trx t

INNER JOIN information_schema.processlist p

ON t.trx_mysql_thread_id = p.id

WHERE  t.trx_state = 'RUNNING'

AND p.time > 10

AND p.command = 'Sleep'

【解决方法】

临时方法：Kill 进程ID 如上图查询中事务ID，trx_mysql_thread_id=6。 即Kill 6

长期解决方法：大事务拆分、分时、分批处理。

MySQL

版权声明：本文内容由网络用户投稿，版权归原作者所有，本站不拥有其著作权，亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容，请联系我们jiasou666@gmail.com 处理，核实后本网站将在24小时内删除侵权内容。