震惊，MySQL数据库cp复制500GB数据文件，膨胀到1TB多？

本文链接：https://blog.youkuaiyun.com/chenoracle/article/details/144151854

图片.png
MySQL数据库透明压缩问题分析

问题现象:

在进行MySQL 8.0.x升级操作时，执行停库，备份(cp -r )，将 /mysqldata/3308 整个目录备份到 /mysqldata/dbtmpfile/20241111/目录下，
备份过程中发现 /mysqldata磁盘使用率超过80%，升级前评估不会超过80%。
发现备份后的文件比原文件大了600GB。

原文件: 500G /mysqldata/3308
备份文件: 1.1T /mysqldata/dbtmpfile/20241111/3308

问题分析:

对比原文件和备份后的文件

原目录大小 100G

root@cjc-db-01:/mysqldata/3308/data#du -sh cjc/
100G cjc/

cp备份后的目录大小300GB

root@cjc-db-01:/mysqldata/dbtmpfile/20241111/3308/data#du -sh cjc/
300G cjc/

原文件

mysql@cjc-db-01:/home/mysql$ls -lrth /mysqldata/3308/data/cjc/mysql_cjc_t1.ibd
-rw-r----- 1 mysql mysql **80.9G** Nov 20 12:00 /mysqldata/3308/data/cjc/mysql_cjc_t1.ibd

mysql@cjc-db-01:/home/mysql$du -sh /mysqldata/3308/data/cjc/mysql_cjc_t1.ibd
23.7G /mysqldata/3308/data/cjc/mysql_cjc_t1.ibd

du 比 ls 查看文件大，一般是稀疏文件：

什么稀疏文件:


[https://kimi.moonshot.cn/chat/ct4jpplstq1p2tfmgs5g](https://kimi.moonshot.cn/chat/ct4jpplstq1p2tfmgs5g)

稀疏文件是一种文件系统特性，它允许文件系统中创建一个非常大的文件，而实际上并不立即占用磁盘上相应的存储空间。这种文件通常被称为“稀疏”或“空洞”，因为它们在磁盘上占用的空间远小于文件的逻辑大小。

[https://blog.51cto.com/u_9843231/4871402](https://blog.51cto.com/u_9843231/4871402)

稀疏文件(Sparse File)主要由 0 构成：

在这里插入图片描述

常见的稀疏文件有:

数据库文件:
1.Oracle数据库Tempoary Tablespace tempfile;
2.MS SQL Backup file;

虚拟机文件:
VMDK等；
比如向虚拟机添加虚拟磁盘，可以选择不立即分配
创建了一个 60GB 的虚拟机磁盘文件，在正常情况下，该文件要占用 60GB 的物理磁盘空间。

创建稀疏文件测试:
/mysqldata剩余1.3GB

mysql@CJC-DB-007:/mysqldata$df -h /mysqldata/
Filesystem Size Used Avail Use% Mounted on
/dev/mapper/vg_data-lv_mysqldata 10G 8.8G 1.3G 88% /mysqldata

创建10GB大小的稀疏文件

mysql@CJC-DB-007:/mysqldata$dd if=/dev/zero of=sparsefile bs=1 count=1 seek=10240M
1+0 records in
1+0 records out
1 byte copied, 9.1148e-05 s, 11.0 kB/s

查看大小

mysql@CJC-DB-007:/mysqldata$ls -lrth sparsefile
-rw-r----- 1 mysql mysql **11**G Nov 29 12:36 sparsefile

mysql@CJC-DB-007:/mysqldata$du -sh sparsefile
4.0K** sparsefile

对稀疏文件执行cp命令，为什么变成了原大小？
cp后文件的大小:
原文件: ls -lrth 查看大小23.7GB，du -sh 查看80.9GB。
cp后文件:ls -lrth 和 du -sh 查看都是80.9GB。

再次测试将文件备份到其他目录:

mysql@cjc-db-01:/home/mysql$cp /mysqldata/3308/data/cjc/mysql_cjc_t1.ibd /mysqldata/dbtmpfile/20241111/
mysql@cjc-db-01:/home/mysql$ls -lrth /mysqldata/dbtmpfile/20241111/mysql_cjc_t1.ibd
-rw-r----- 1 mysql mysql **80.9G** Nov 20 12:03 /mysqldata/dbtmpfile/20241111/mysql_cjc_t1.ibd

mysql@cjc-db-01:/home/mysql$du -sh /mysqldata/dbtmpfile/20241111/mysql_cjc_t1.ibd
80.9G /mysqldata/dbtmpfile/20241111/mysql_cjc_t1.ibd

查看表信息
/mysqldata/3308/data/cjc/mysql_cjc_t1.ibd
通过目录名和文件名可以知道，这个文件对应的是:
cjc库，mysql_perf_tabsummarybytable表的数据文件。

mysql> show create table mysql_perf_tabsummarybytableG;
*************************** 1. row ***************************
Table: mysql_perf_tabsummarybytable
Create Table: CREATE TABLE `mysql_perf_tabsummarybytable` (
`DBID` int NOT NULL,
`DBNAME` varchar(200) NOT NULL,
`CHECKTIME` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`OBJECT_TYPE` varchar(64) NOT NULL,
......
`VIP` varchar(200) DEFAULT NULL,
PRIMARY KEY (`DBID`,`CHECKTIME`,`DBNAME`,`OBJECT_TYPE`,`OBJECT_SCHEMA`,`OBJECT_NAME`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 ROW_FORMAT=DYNAMIC **COMPRESSION='zlib'**
1 row in set (0.00 sec)
ERROR:
No query specified

其中:COMPRESSION=‘zlib’ 对应MySQL 透明页压缩TPC（Transparent Page Compression）技术。
什么是透明页压缩TPC:
https://www.modb.pro/db/1717345539694616576
透明页压缩TPC（Transparent Page Compression）是 5.7 版本推出的一种新的页压缩功能，其利用文件系统的空洞（Punch Hole）特性进行压缩。
由于空洞是文件系统的一个特性，利用空洞压缩只能压缩到文件系统的最小单位 4K，且其页压缩是 4K 对齐的。比如一个 16K 的页，压缩后为 7K，则实际占用空间 8K；压缩后为 3K，则实际占用空间是 4K；若压缩后是 13K，则占用空间依然为 16K。
TPC 压缩的具体实现如下所示：
在这里插入图片描述

上图可以看到，一个 16K 的页压缩后是 8K，接着数据库会对这 16K 的页剩余的 8K 填充0x00，这样当这个 16K 的页写入到磁盘时，利用文件系统空洞特性，则实际将仅占用 8K 的物理存储空间。
一个 16K 的页压缩后是 12K 示例:

[https://cloud.tencent.com/developer/article/2452279](https://cloud.tencent.com/developer/article/2452279)

在这里插入图片描述

为什么cp后表压缩失效？
查看官网文档，哪些操作系统支持透明页压缩：

https://dev.mysql.com/doc/refman/8.0/en/innodb-page-compression.html

Supported Platforms
Page compression requires sparse file and hole punching support. Page compression is supported on Windows with NTFS, and on the following subset of MySQL-supported Linux platforms where the kernel level provides hole punching support:
页面压缩需要稀疏文件和打孔支持。使用NTFS的Windows以及以下MySQL支持的Linux平台子集支持页面压缩，在这些平台上，内核级别提供打孔支持：
RHEL 7 and derived distributions that use kernel version 3.10.0-123 or higher
OEL 5.10 (UEK2) kernel version 2.6.39 or higher
OEL 6.5 (UEK3) kernel version 3.8.13 or higher
OEL 7.0 kernel version 3.8.13 or higher
SLE11 kernel version 3.0-x
SLE12 kernel version 3.12-x
OES11 kernel version 3.0-x
Ubuntu 14.0.4 LTS kernel version 3.13 or higher
Ubuntu 12.0.4 LTS kernel version 3.2 or higher
Debian 7 kernel version 3.2 or higher

可以看到,Redhat 7.9 是支持的，但xfs文件系统下还是有问题？
在这里插入图片描述

Kylin 没显示支持，也是xfs文件系统下有问题，ext4文件系统没问题。
在这里插入图片描述

MySQL原厂技术支持工程师答复如下:
表压缩是在page里使用hole punching技术，这样产生了稀疏文件，XFS系统应该是不释放这些稀疏空间。在Oracle Linux上也可以稳定重现这个问题，内部确认下有什么可以提高的地方。
可以看到，目前针对这个问题，可以稳定重现，但还没有解决方案！

问题重现:

经测试 kylin v10 sp1 和 redhat 7.9 操作系统的xfs文件系统，可以稳定重现此问题(ext4文件系统无此问题)。

MySQL:8.0.33
mysql> create table t1(id int,name varchar(20)) COMPRESSION='zlib';
mysql> insert into cjc.t1 values(1,'a'),(2,'b'),(3,'c'),(4,'d'),(5,'e'),(6,'f'),(7,'g'),(8,'h'),(9,'i'),(10,'j');
mysql> insert into cjc.t1 select * from t1;
......
mysql> insert into cjc.t1 select * from t1;
Query OK, 41943040 rows affected (4 min 53.77 sec)
Records: 41943040 Duplicates: 0 Warnings: 0
mysql> select count(*) from cjc.t1;
+----------+
| count(*) |
+----------+
| 83886080 |
+----------+
1 row in set (38.34 sec)

查看对应的表文件:

mysql@CJC-DB-004:/db/mysqldata/3308/data/cjc$ls -lrth t1.ibd
-rw-r--r-- 1 mysql mysql **23.7G** Nov 26 18:26 t1.ibd

mysql@CJC-DB-004:/db/mysqldata/3308/data/cjc$du -sh t1.ibd
1.3G t1.ibd

复制文件:

mysql@CJC-DB-004:/db/mysqldata/3308/data/cjc$cp t1.ibd t1.ibd.bak
mysql@CJC-DB-004:/db/mysqldata/3308/data/cjc$ls -lrth t1.ibd*
-rw-r--r-- 1 mysql mysql 23.7G Nov 26 18:26 t1.ibd
-rw-r----- 1 mysql mysql 23.7G Nov 26 18:30 t1.ibd.bak

mysql@CJC-DB-004:/db/mysqldata/3308/data/cjc$du -sh t1.ibd*
1.3G t1.ibd
23.7G t1.ibd.bak ---压缩失效了

从MySQL层面，查看t1.ibd文件压缩前大小和实际大小:

mysql> SELECT SPACE, NAME, FS_BLOCK_SIZE, FILE_SIZE, ALLOCATED_SIZE FROM INFORMATION_SCHEMA.INNODB_TABLESPACES WHERE NAME='cjc/t1'G;
*************************** 1. row ***************************
SPACE: 745
NAME: cjc/t1
FS_BLOCK_SIZE: 4096
FILE_SIZE: 2889875456
ALLOCATED_SIZE: 1351684096
1 row in set (0.01 sec)

其中 SPACE: 745 对应的就是 ./cjc/t1.ibd

mysql> select * from INNODB_DATAFILES where space='745';
+--------------+--------------+
| SPACE | PATH |
+--------------+--------------+
| 0x373435 | ./cjc/t1.ibd |
+--------------+--------------+
1 row in set (0.00 sec)

FS_BLOCK_SIZE: 文件系统块 4KB
The file system block size, which is the unit size used for hole punching.
FILE_SIZE: t1.ibd文件未压缩的大小2889875456/1024/1024/1024=2.69GB**
The apparent size of the file, which represents the maximum size of the file, uncompressed.
ALLOCATED_SIZE: 实际磁盘分配的大小 1351684096/1024/1024/1024=1.26GB**
The actual size of the file, which is the amount of space allocated on disk.

解决方案:

首先让我们再回顾下问题，MySQL表使用TPC技术进行了压缩，数据文件占用磁盘空间大幅度降低，但是在XFS文件系统下，对数据文件进行cp复制，复制后的文件压缩失效，变回了原大小，那么此问题最大的影响就是在进行类似数据文件备份的操作前无法正确的评估磁盘所需空间，可能会导致磁盘使用率过高或不可用，而且原厂反馈目前还没提供优化方案，我想到的临时解决方案只能是在执行类似cp数据文件前，先通过如下SQL评估哪些表进行了TPC压缩，如果存在，继续计算压缩失效后新增的磁盘空间大小。
1.查询哪些表配置了页压缩属性

mysql> SELECT TABLE_NAME, TABLE_SCHEMA, CREATE_OPTIONS FROM INFORMATION_SCHEMA.TABLES WHERE CREATE_OPTIONS LIKE '%COMPRESSION=%';
+------------+--------------+--------------------+
| TABLE_NAME | TABLE_SCHEMA | CREATE_OPTIONS |
+------------+--------------+--------------------+
| t1 | cjc | COMPRESSION="zlib" |
| t1120 | cjc | COMPRESSION="ZLIB" |
| t2 | cjc | COMPRESSION="ZLIB" |
+------------+--------------+--------------------+
3 rows in set (0.01 sec)

2.拼接出表空间格式名称，例如:cjc/t1

SELECT concat(TABLE_SCHEMA,"/",TABLE_NAME) FROM INFORMATION_SCHEMA.TABLES WHERE CREATE_OPTIONS LIKE '%COMPRESSION=%';
+-------------------------------------+
| concat(TABLE_SCHEMA,"/",TABLE_NAME) |
+-------------------------------------+
| cjc/t1 |
| cjc/t1120 |
| cjc/t2 |
+-------------------------------------+
3 rows in set (0.01 sec)

3.计算cp复制额外需要的空间

SELECT NAME, FILE_SIZE, ALLOCATED_SIZE,(FILE_SIZE-ALLOCATED_SIZE)/1024/1024/1024 "GB" FROM INFORMATION_SCHEMA.INNODB_TABLESPACES WHERE NAME in
(SELECT concat(TABLE_SCHEMA,"/",TABLE_NAME) FROM INFORMATION_SCHEMA.TABLES WHERE CREATE_OPTIONS LIKE '%COMPRESSION=%');
+-----------+------------+----------------+----------------+
| NAME | FILE_SIZE | ALLOCATED_SIZE | GB |
+-----------+------------+----------------+----------------+
| cjc/t1 | 2889875456 | 1351684096 | 1.432552337646 |
| cjc/t2 | 114688 | 53248 | 0.000057220459 |
| cjc/t1120 | 3296722944 | 1759059968 | 1.432060241699 |
+-----------+------------+----------------+----------------+
3 rows in set (5.41 sec)

总大小

SELECT SUM(FILE_SIZE-ALLOCATED_SIZE)/1024/1024/1024 "GB" FROM INFORMATION_SCHEMA.INNODB_TABLESPACES WHERE NAME in
(SELECT concat(TABLE_SCHEMA,"/",TABLE_NAME) FROM INFORMATION_SCHEMA.TABLES WHERE CREATE_OPTIONS LIKE '%COMPRESSION=%');
+----------------+
| GB |
+----------------+
| 2.864669799805 |
+----------------+
1 row in set (5.49 sec)

###chenjuchao 20241130###
欢迎关注我的公众号《IT小Chen》