问题现象 :
在进行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://blog.51cto.com/u_9843231/4871402 稀疏文件(Sparse File)主要由 0 构成：
常见的稀疏文件有
:
数据库文件: 1.Oracle数据库Tempoary Tablespace tempfile; 2.MS SQL Backup file; 虚拟机文件: VMDK等； 比如向虚拟机添加虚拟磁盘，可以选择不立即分配
创建稀疏文件测试 : /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 11G 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_tabsummarybytable\G; *************************** 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
的页压缩后是
12
K
示例
:
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》
