MySQL空间回收测试验证方案

概述

MySQL数据库的表存在高水位问题，即：表数据被频繁的清理操作后，会出现空洞，这种操作并不会回收数据文件的大小，同时数据查询时也会增加CPU/IO上的消耗，因此建议定期对一些大表，尤其是频繁清理的表做空间回收。

MySQL官方提供两种方式来释放空间：

1
2


1. Alter table <table_name> engine=innodb; + analyze table <table_name>;
2. Optimize table <table_name>;

第一种方式是MySQL官方推荐的回收方式，工作原理如下：

采用 Inplace 方式重建表，Alter 期间，支持 DML 查询和更新操作，语句为 alter table t engine=innodb, ALGORITHM=inplace;之所以支持 DML 更新操作，是因为数据拷贝期间会将 DML 更新操作记录到 Row log 中。

重建过程中最耗时的就是拷贝数据的过程，这个过程中支持 DML 查询和更新操作，对于整个 DDL 来说，锁时间很短，就可以近似认为是 Online DDL。

执行过程：

1
2
3
4
5
6


1、获取 MDL（Meta Data Lock）写锁，innodb 内部创建与原表结构相同的临时文件 
2、拷贝数据之前，MDL 写锁退化成 MDL 读锁，支持 DML 更新操作
3、根据主键递增顺序，将一行一行的数据读出并写入到临时文件，直至全部写入完成。并且，会将拷贝期间的 DML 更新操作记录到 Row log 中 
4、上锁，再将 Row log 中的数据应用到临时文件 
5、互换原表和临时表表名 
6、删除临时表

第二种方式是通过重新组织表、索引的物理存储，减少存储空间，提高访问的I/O效率。

用法：OPTIMIZE [LOCAL | NO_WRITE_TO_BINLOG] TABLE tbl_n说ame [, tbl_name] …
工作原理：OPTIMIZE TABLE在InnoDB表中等价 ALTER TABLE … FORCE + analyze ，它重建表以更新索引统计信息并释放聚簇索引中未使用的空间。

OPTIMIZE TABLE只对独立表空间有作用（innodb_file_per_table=1），对于共享空间，只能通过导出数据，删掉IBDATA1。对于主从架构，如果不想传递回收空间这个动作，OPTIMIZE TABLE是更好的选择，可以用选项LOCAL或者NO_WRITE_TO_BINLOG。

另外：第三方也有一些工具，这里只测试了pt-online-schema-change，从表现上来看和官方工具差异不大。

约定：这里的操作均为InnoDB引擎的表。

测试环境

软硬件配置情况

服务器	虚拟机
OS版本	SUSE 12sp5
数据库软件	MySQL 5.7
架构	单机

测试方法

使用sysbench创建两张100w的表，通过delete删除表数据，通过三种方式分别去回收空间，观察效果。

步骤验证

准备工作

通过SYSBENCH 初始化两张表sbtest1，sbtest2. 每张表100w数据。

1

sysbench oltp_read_write --mysql-host=192.168.56.223 --mysql-port=3306 --mysql-user=root --mysql-password=root --mysql-db=testdb --tables=2 --table-size=1000000 --threads=2 prepare

2）数据库隔离级别设置为read commited

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17


mysql> show variables like '%iso%';

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

| Variable_name     | Value     |

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

| transaction_isolation | READ-COMMITTED |

| tx_isolation     | READ-COMMITTED |

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

-- # 临时生效：

set global transaction isolation level READ COMMITTED;

测试场景一

Alter table <table_name> engine=innodb; + analyze table <table_name>;

开两个SESSION，其中一个SESSION（session 1）设置更新表sbtest1，另一个SESSION（session 2）执行回收命令

# session 1先设置为非自动提交，执行更新表

# session 2执行回收命令，可以看到一直是卡住的状态。

# 再开一个会话，可以看线程在等待Waiting for table metadata lock（即MDL锁）

# session 1提交后，MDL锁消失，但这个锁会间断出现两次：第一次是锁住创建临时表，第二次是锁住删除临时表。

# 小结：

整个过程对表进行DML是不受阻塞的，但有DML操作会影响回收命令获取MDL锁。因为需要复制临时表，所以空间要预留出大于当前回收空间的表。

测试场景二

Optimize table <table_name>;

# session 1先设置为非自动提交，执行更新表

# session 2执行回收命令，可以看到一直是卡住的状态。

# 再开一个会话，可以看线程在等待Waiting for table metadata lock（即MDL锁）

# session 1提交后，MDL锁消失，但这个锁会间断出现两次：第一次是锁住创建临时表，第二次是锁住删除临时表。

# 小结：

与第一种场景一样，整个过程对表进行DML是不受阻塞的，但有DML操作会影响回收命令获取MDL锁。因为需要复制临时表，所以空间要预留出大于当前回收空间的表。

测试场景三

pt-online-schema-change

# session 1先设置为非自动提交，执行更新表

# session 2执行回收命令，可以看到一直是卡住的状态。

有可能会报如下错误

2023-12-24T15:58:25 Error copying rows from testdb.sbtest1 to testdb._sbtest1_new: 2023-12-24T15:58:25 DBD::mysql::st execute failed: Lock wait timeout exceeded; try restarting transaction [for Statement "INSERT LOW_PRIORITY IGNORE INTO testdb._sbtest1_new (id, k, c, pad) SELECT id, k, c, pad FROM testdb.sbtest1 FORCE INDEX(PRIMARY) WHERE ((id >= ?)) AND ((id <= ?)) LOCK IN SHARE MODE /pt-online-schema-change 7242 copy nibble/" with ParamValues: 0=512852, 1=519260] at /usr/bin/pt-online-schema-change line 10614.

# 再开一个会话，可以看线程在等待Waiting for table metadata lock（即MDL锁）

# 小结：

与官方不同的是，采用触发器的方式来实现row log，验证时发现会触发锁超时。其他的表现则无差别，对DML无影响，空间需要2倍。

总结

从MySQL 5.7开始，官方的两种方式均已实现ONLINE，不会影响DML操作，但频繁的DML容易导致空间回收命令卡住，无法获取MDL锁，进而可能引起超时。因此执行空间回收命令时，尽量避开业务高峰期！！！

日常进行空间回收，建议采用第一种方式：Alter table <table_name> engine=innodb; + analyze table <table_name>;

Optimize table 对于共享空间是无效的，对于主从架构，如果想空间回收不被传递到从库，建议用此命令。

第三方工具pt-online-schema-change，测试时并未看到优势，反而会出现锁超时的现象。其他的gh-ost,pt-osc等工具未尝试，以后有时间再去验证。

MySQL空间回收测试验证方案

相关文章：