进阶教程 | MySQL表空间碎片整理操作指导

vlambda
2020-11-27

进阶教程 | MySQL表空间碎片整理操作指导

背景介绍

MySQL 数据运行过程中，业务对数据表不断的进行增删改查操作，时间长后会导致数据表表空间碎片率较高，在重整表空间前这些碎片空间无法使用，造成磁盘空间使用率低，容易到底磁盘配置限额。

变更方法

MySQL 表空间碎片整理大致步骤如下：

1. 检查某个schema下所有表的表空间碎片情况。

2. 选择碎片空间较大的表进行操作。

3. 停止该表上的业务操作。

4. 执行命令重整表空间。

变更影响分析

对其他系统的影响

该变更操作使用ONLINE DDL 特性可以不锁表，允许在执行碎片整理的过程中进行数据DML 操作。

另外一种操作方法可锁表，业务无法对执行碎片整理的表进行DML 操作。

对自身系统的影响

无，该变更无需停止MySQL 数据库服务。

对日志系统的影响

不涉及

变更前检查及操作

可用空间检查

1. 登录MySQL 数据库所在主机，查看磁盘可用空间。

#df –h

2. 登录数据库查看磁盘碎片率较高的表。

Mysql>use information_schema;

Mysql>select TABLE_SCHEMA,TABLE_NAME,DATA_LENGTH/1024/1024/1024 TOTAL_SIZE_GB, round(INDEX_LENGTH/1024/1024/1024,2) index_size,DATA_FREE/1024/1024/1024 DATA_FREE_GB from tables where TABLE_SCHEMA='mytest' and TABLE_TYPE='BASE TABLE' order by DATA_FREE_GB desc;

+--------------+------------+----------------+---------------+----------------+

+--------------+------------+----------------+---------------+----------------+

| mytest | sbtest1 | 0.969573974609 | 0.03 | 0.179687500000 |

| mytest | sbtest2 | 1.145507812500 | 0.04 | 0.005859375000 |

| mytest | t1 | 0.000015258789 | 0.00 | 0.000000000000 |

| mytest | t2 | 0.606445312500 | 0.06 | 0.000000000000 |

| mytest | t3 | 0.000015258789 | 0.00 | 0.000000000000 |

| mytest | t4 | 0.000015258789 | 0.00 | 0.000000000000 |

+--------------+------------+----------------+---------------+----------------+

注：如上SQL 语句中schema名称请根据实际情况进行修改。

3. 开启Performane_shema相关统计参数，以便观察DDL 进度。

Mysql>user performance_schema;

Mysql> update setup_instruments set ENABLED='YES' where NAME like 'stage/innodb/alter%';

Mysql> update setup_consumers set ENABLED='YES' where NAME like '%stages%%';

修改详细操步骤

在线表空间磁盘碎片空间重整可以使用两种方法，一种是停止业务后进行操作，该操作速度较快。另外一种方法是online DDL ，无需停止业务，允许在表空间碎片重整的过程中业务进行DML操作，速度较慢。

具体步骤如下:

线上环境修改(停止业务）

(1) 停止业务。

(2) 登录数据库执行命令对表进行碎片空间重整。

#su – mysql

Mysql>alter table xxx.xxxx engine=innodb ALGORITHM=INPLACE，LOCK=EXCLUSIVE;

注：加入了LOCK=EXECLUSIVE语句，在整个DDL 执行期间，对该表的查询、写入更新都会被阻塞。由于在DDL 期间没有业务数据写入，该操作速度较快。

(3) 查看重整表空间进度。

Mysql>use performance_schema;

Mysql> select stmt.SQL_TEXT as sql_text, concat(WORK_COMPLETED, '/' , WORK_ESTIMATED) as progress, (stage.TIMER_END - stmt.TIMER_START) / 1e12 as current_seconds, (stage.TIMER_END - stmt.TIMER_START) / 1e12 * (WORK_ESTIMATED-WORK_COMPLETED) / WORK_COMPLETED as remaining_seconds from events_stages_current stage, events_statements_current stmt where stage.THREAD_ID = stmt.THREAD_ID and stage.NESTING_EVENT_ID = stmt.EVENT_ID;

(4) 查看进展情况。

Mysql>select * from information_schema.processlist where command like ‘alter table xxx%’;

注意：执行步骤2可能会由于业务大事务的存在或为及时提交的事务而导致alter 语句无法获取到meta lock，此时应找出该大事务kill掉。

Mysql>select * from information_schema.processlist order by TIME desc limit 20;

Mysql>kill 会话号

(5) 依次剩余其他表的磁盘空间碎片整理。

线上环境修改(不停止业务）

(1) 登录数据库执行命令设置innodb_online_alter_log_max_size为5GB ，即允许在表空间碎片整理过程中DML 事务临时日志的最大大小，需要根据业务繁忙程度进行设置。

#su – mysql

$mylogin

Mysql>set gloal innodb_online_alter_log_max_size=5*1024*1024*1024;

(2) 执行命令重整表空间。

MySQL> alter table myschema.xxxx engine=innodb;

注：默认情况下mysql数据库执行DDL 操作时，会自动判断最大限度的允许DML 操作，即默认自动加入参数ALGORITHM=INPLACE, LOCK=NONE;

(3) 查看重整表空间进度。

Mysql>use performance_schema;

(4) 查看进展情况。

Mysql>select * from information_schema.processlist where command like ‘alter table xxx%’;

注意：执行步骤2可能会由于业务大事务的存在或为及时提交的事务而导致alter 语句无法获取到meta lock，此时应找出该大事务kill掉。

Mysql>select * from information_schema.processlist order by TIME desc limit 20;

Mysql>kill 会话号

(5) 依次剩余其他表的磁盘空间碎片整理。

END

关注获取更多MySQL资讯教程

期待关注

Head picture by Markus Spiske on Unsplash

你有在看吗↓

vlambda博客
学习文章列表