MySQL表碎片

原文链接:
https://www.percona.com/blog/2020/06/24/mysql-table-fragmentation-beware-of-bulk-insert-with-failure-or-rollback/

当心批量插入失败或者回滚时带来的 MySQL 表碎片,通常,DBA 都了解使用 DELETE 语句会产生表碎片。在大多数情况下,当执行大量的删除时,DBA 总会重新构建表以回收磁盘空间。但是,您是否认为只有删除才会导致表碎片?(答案:并不是)。
在这篇博文中,我将解释插入如何会带来碎片。

在讨论这个主题之前,我们需要了解 MySQL,有两种碎片:

  • 在表中的 InnoDB 页完全空闲引起的碎片。
  • InnoDB 页未填充满(页中还有一些空闲空间)引起的碎片。

主要有三种由插入引起的碎片场景:

  • 插入,然后回滚
  • 插入语句失败
  • 页分裂引起的碎片

测试环境

我创建了自己的测试环境来测试这些案例。

  • DB:Percona 版分支
  • Table:frag,ins_frag,frag_page_spl
  • 表大小:2G

场景 1:插入后回滚

首先,我创建了一个新表”ins_flag”。然后我开启一个事务(使用 BEGIN),如下所示开始拷贝”frag”表中数据到”ins_flag”中。

mysql> create table ins_frag like frag;
Query OK, 0 rows affected (0.01 sec)

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into ins_frag select * from frag;
Query OK, 47521280 rows affected (3 min 7.45 sec)
Records: 47521280  Duplicates: 0  Warnings: 0

#Linux shell
sakthi-3.2# ls -lrth
total 8261632
-rw-r-----  1 _mysql  _mysql   2.0G Jun 17 02:43 frag.ibd
-rw-r-----  1 _mysql  _mysql   2.0G Jun 17 03:00 ins_frag.ibd

如上所示,您可以看到已经执行了插入,但是我还没有提交或者回滚插入操作。您注意到 2 张表都已经占用 2G 磁盘空间。

现在我将回滚插入操作。

mysql> select count(*) from ins_frag;
+----------+
| count(*) |
+----------+
| 47521280 |
+----------+
1 row in set (1.87 sec)

mysql> rollback;
Query OK, 0 rows affected (5 min 45.21 sec)

mysql> select count(*) from ins_frag;
+----------+
| count(*) |
+----------+
|        0 |
+----------+
1 row in set (0.00 sec)


#Linux shell
sakthi-3.2# ls -lrth
total 8261632
-rw-r-----  1 _mysql  _mysql   2.0G Jun 17 02:43 frag.ibd
-rw-r-----  1 _mysql  _mysql   2.0G Jun 17 03:09 ins_frag.ibd

当插入回滚后,”ins_frag”表仍然占有相同的 2GB 的磁盘空间。让我们在 MySQL 客户端看看碎片空间。

mysql> SELECT
-> table_schema as 'DATABASE',
-> table_name as 'TABLE',
-> CONCAT(ROUND(( data_length + index_length ) / ( 1024 * 1024 * 1024 ), 2), 'G') 'TOTAL',
-> CONCAT(ROUND(data_free / ( 1024 * 1024 * 1024 ), 2), 'G') 'DATAFREE'
-> FROM information_schema.TABLES
-> where table_schema='percona' and table_name='ins_frag';
+----------+----------+-------+----------+
| DATABASE | TABLE.   | TOTAL | DATAFREE |
+----------+----------+-------+----------+
| percona  | ins_frag | 0.00G | 1.96G    |
+----------+----------+-------+----------+
1 row in set (0.01 sec)

这清楚的显示了插入之后回滚会产生碎片。我们需要重建表来回收磁盘空间。

mysql> alter table ins_frag engine=innodb;
Query OK, 0 rows affected (2.63 sec)
Records: 0  Duplicates: 0  Warnings: 0

#Linux shell

sakthi-3.2# ls -lrth
total 4131040
-rw-r-----  1 _mysql  _mysql   2.0G Jun 17 02:43 frag.ibd
-rw-r-----  1 _mysql  _mysql   112K Jun 17 03:11 ins_frag.ibd

场景 2: 插入语句失败

在会话 1 中,我将在事务中执行相同的插入语句。但是这次我会在会话 2 中中断并杀掉这个插入语句。
会话 1

#Linux shell

sakthi-3.2# ls -lrth
total 4131040
-rw-r-----  1 _mysql  _mysql   2.0G Jun 17 02:43 frag.ibd
-rw-r-----  1 _mysql  _mysql   112K Jun 17 04:02 ins_frag.ibd

#MySQL shell

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into ins_frag select * from frag;   #is running

会话 2

mysql> pager grep -i insert ; show processlist;
PAGER set to 'grep -i insert'
| 33 | root            | localhost | percona | Query   |    14 | executing              | insert into ins_frag select * from frag |
4 rows in set (0.00 sec)

mysql> kill 33;
Query OK, 0 rows affected (0.00 sec)

插入中断并失败了。在会话 1 查看:

mysql> insert into ins_frag select * from frag;
ERROR 2013 (HY000): Lost connection to MySQL server during query

#Linux shell

sakthi-3.2# ls -lrth
total 4591616
-rw-r-----  1 _mysql  _mysql   2.0G Jun 17 02:43 frag.ibd
-rw-r-----  1 _mysql  _mysql   212M Jun 17 04:21 ins_frag.ibd

#MySQL shell

mysql> select count(*) from ins_frag;
+----------+
| count(*) |
+----------+
|        0 |
+----------+
1 row in set (0.10 sec)

插入并未完成,表中无数据。但是仍然,这个表的 ibd 文件已经涨到 212M。通过 MySQL 客户端查看表空间碎片。

mysql> SELECT
-> table_schema as 'DATABASE',
-> table_name as 'TABLE',
-> CONCAT(ROUND(( data_length + index_length ) / ( 1024 * 1024 ), 2), 'M') 'TOTAL',
-> CONCAT(ROUND(data_free / ( 1024 * 1024 ), 2), 'M') 'DATAFREE'
-> FROM information_schema.TABLES
-> where table_schema='percona' and table_name='ins_frag';
+----------+----------+---------+----------+
| DATABASE | TABLE    | TOTAL   | DATAFREE |
+----------+----------+---------+----------+
| percona  | ins_frag | 0.03M   | 210.56M  |
+----------+----------+---------+----------+
1 row in set (0.01 sec)

表中有碎片,需要重建表回收这些空间。

mysql> alter table ins_frag engine='innodb';
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

#Linux shell

sakthi-3.2# ls -lrth
total 4131040
-rw-r-----  1 _mysql  _mysql   2.0G Jun 17 02:43 frag.ibd
-rw-r-----  1 _mysql  _mysql   112K Jun 17 04:32 ins_frag.ibd

场景 3: 页分裂引起的碎片

我们知道,InnoDB 记录存储在 InnoDB 页中。默认情况下,每个页大小是 16K,但是您可以选择更改页大小。

如果 InnoDB 页没有足够的空间容纳新的记录或索引条目,它将被分成 2 页,每页约占 50%。这意味着,即使对表只有插入,没有回滚和删除,最终也可能只有平均 75%的页利用率——因此这种页内部损失为 25%。

当按排序建立索引,它们会有更多的拥塞,如果表很多插入到索引中随机位置,就会导致页分裂。

参阅 Marco Tusa 写的博客 InnoDB Page Merging and Page Splitting,详细介绍了页分裂和 InnoDB 页结构/操作。

为了实验,我创建了一个具有排序索引的表(降序)

mysql> show create table frag_page_splG
*************************** 1. row ***************************
Table: frag_page_spl
Create Table: CREATE TABLE `frag_page_spl` (
`id` int NOT NULL AUTO_INCREMENT,
`name` varchar(16) DEFAULT NULL,
`messages` varchar(600) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `idx_spl` (`messages` DESC)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.07 sec)

我们可以通过表 INFORMATION_SCHEMA.INNODB_METRICS 监控页分裂情况。对此,您需要启用 InnoDB monitor。

mysql> SET GLOBAL innodb_monitor_enable=all;
Query OK, 0 rows affected (0.09 sec)

我写了一个 6 个并发随机插入的脚本。脚本执行结束后:

mysql> select name,count,type,status,comment from information_schema.innodb_metrics where name like '%index_page_spl%'G
*************************** 1. row ***************************
name: index_page_splits
count: 52186
type: counter
status: enabled
comment: Number of index page splits
1 row in set (0.05 sec)

mysql> SELECT
-> table_schema as 'DATABASE',
-> table_name as 'TABLE',
-> CONCAT(ROUND(( data_length + index_length ) / ( 1024 * 1024 ), 2), 'M') 'TOTAL',
-> CONCAT(ROUND(data_free / ( 1024 * 1024 ), 2), 'M') 'DATAFREE'
-> FROM information_schema.TABLES
-> where table_schema='percona' and table_name='frag_page_spl';
+----------+---------------+----------+----------+
| DATABASE | TABLE.        | TOTAL    | DATAFREE |
+----------+---------------+----------+----------+
| percona  | frag_page_spl | 2667.55M | 127.92M  |
+----------+---------------+----------+----------+
1 row in set (0.00 sec)

从指标上看,我们看到页分裂次数在增加。输出显示有 52186 次页分裂,产生了 127.92MB 的碎片。
一旦发生页分裂,唯一的方法是将创建的页降至合并阈值之下。当这种情况发生时,InnoDB 通过合并操作将数据从分裂的页中移出。对表和特定的索引合并阈值是可配置的。
另一种重新组织数据的方法是 OPTIMIZE TABLE。这是一个非常重和漫长的过程,但通常这是解决过多页比较稀疏的唯一方法。

总结

  • 前面两种情况很少见。因为大多数应用程序都不会设计在表中写入大量数据。
  • 在执行批量插入时(INSERT INTO SELECT * FROM, 加载 mysqldump 的数据, INSERT with huge data 等)需要注意这些问题。
  • 碎片占用的磁盘空间始终是可重用的。
https://alicharles.oss-cn-hangzhou.aliyuncs.com/static/images/mp_qrcode.jpg
文章目录
  1. 测试环境
  2. 场景 1:插入后回滚
  3. 场景 2: 插入语句失败
  4. 场景 3: 页分裂引起的碎片
  5. 总结