MySQL启用透明页压缩，碎片率高，导致宕机！

最新推荐文章于 2025-11-24 20:48:20 发布

原创最新推荐文章于 2025-11-24 20:48:20 发布 · 765 阅读

19 ·

CC 4.0 BY-SA版权

文章标签：

#mysql #数据库

MySQL 专栏收录该内容

28 篇文章

订阅专栏

问题现象:

接到如下告警信息:

告警:MYSQL-192.168.1.101,监控进程数等于0,the current value is 0,故障发生时间:2025-11-01 17:56:03

应急过程:

检查mysqld进程,进程数为0。

ps -ef|grep mysqld|grep -v grep

检查mysql error.log日志:

2025-11-01T05:34:49.800668+08:00 4463923 [Warning] [MY-014054] [Server] Could not purge binary logs since another session is executing LOCK INSTANCE FOR B
ACKUP. Wait for that session to release the lock.
2025-11-01T17:50:27.963412+08:00 4495341 [ERROR] [MY-012644] [InnoDB] Encountered a problem with file './cjc/t1_his_137#p#p202512.ibd'
2025-11-01T17:50:27.991256+08:00 4495341 [ERROR] [MY-012645] [InnoDB] Disk is full. Try to clean the disk to free space.
2025-11-01T17:50:27.991295+08:00 4495341 [ERROR] [MY-012592] [InnoDB] Operating system error number 28 in a file operation.
2025-11-01T17:50:28.138000+08:00 4495341 [ERROR] [MY-012596] [InnoDB] Error number 28 means 'No space left on device'
2025-11-01T17:50:28.138071+08:00 4495341 [ERROR] [MY-012126] [InnoDB] Cannot create file './cjc/t1_his_137#p#p202512.ibd'
2025-11-01T17:50:28.138102+08:00 4495341 [ERROR] [MY-013133] [Server] Create table/tablespace 't1_his_137' failed, as disk is full.
2025-11-01T17:50:28.174857+08:00 4495341 [ERROR] [MY-012592] [InnoDB] Operating system error number 28 in a file operation.
2025-11-01T17:50:28.174895+08:00 4495341 [ERROR] [MY-012596] [InnoDB] Error number 28 means 'No space left on device'
2025-11-01T17:50:28.174913+08:00 4495341 [ERROR] [MY-012126] [InnoDB] Cannot create file './cjc/t2_counters_139#p#p202512.ibd'
2025-11-01T17:50:28.174925+08:00 4495341 [ERROR] [MY-013133] [Server] Create table/tablespace 't2_counters_139' failed, as disk is full.
2025-11-01T17:50:28.182995+08:00 4495341 [ERROR] [MY-012592] [InnoDB] Operating system error number 28 in a file operation.
......
2025-11-01T17:55:39.664131+08:00 4490936 [ERROR] [MY-010846] [Server] MYSQL_BIN_LOG::open_purge_index_file failed to open register file.
2025-11-01T17:55:39.664167+08:00 4490936 [ERROR] [MY-010817] [Server] MYSQL_BIN_LOG::open_index_file failed to sync the index file.
2025-11-01T17:55:39.664183+08:00 4490936 [ERROR] [MY-011072] [Server] Binary logging not possible. Message: Can't open file: '/mysqldata/3306/binlog/mysql-bin.index' (errno: 1 - Operation not permitted), while rotating the binlog. Aborting the server.
2025-11-01T09:55:39Z UTC - mysqld got signal 6 ;
Most likely, you have hit a bug, but this error can also be caused by malfunctioning hardware.
BuildID[sha1]=2a02d4f7b22f7a0ed0694d7881e4af7148d3f4d0
Thread pointer: 0x7f951c0dccc0
Attempting backtrace. You can use the following information to find out
where mysqld died. If you see no messages after this, something went
terribly wrong...
stack_bottom = 7f94ffa8ac10 thread_stack 0x100000
/mysqldata/app/8.0.36/bin/mysqld(my_print_stacktrace(unsigned char const*, unsigned long)+0x41) [0x213aa51]
/mysqldata/app/8.0.36/bin/mysqld(print_fatal_signal(int)+0x2a2) [0xff71f2]
/mysqldata/app/8.0.36/bin/mysqld(my_server_abort()+0x75) [0xff7435]
/mysqldata/app/8.0.36/bin/mysqld(my_abort()+0xe) [0x21348ee]
/mysqldata/app/8.0.36/bin/mysqld() [0x1d48089]
/mysqldata/app/8.0.36/bin/mysqld(MYSQL_BIN_LOG::new_file_impl(bool, Format_description_log_event*)+0x676) [0x1d59f36]
/mysqldata/app/8.0.36/bin/mysqld(MYSQL_BIN_LOG::rotate(bool, bool*)+0x39) [0x1d5ac69]
/mysqldata/app/8.0.36/bin/mysqld(MYSQL_BIN_LOG::ordered_commit(THD*, bool, bool)+0x767) [0x1d643d7]
/mysqldata/app/8.0.36/bin/mysqld(MYSQL_BIN_LOG::commit(THD*, bool)+0x5ca) [0x1d653ba]
/mysqldata/app/8.0.36/bin/mysqld(ha_commit_trans(THD*, bool, bool)+0x302) [0x1101fd2]
/mysqldata/app/8.0.36/bin/mysqld(trans_commit_stmt(THD*, bool)+0x40) [0xfaaed0]
/mysqldata/app/8.0.36/bin/mysqld(mysql_execute_command(THD*, bool)+0x1641) [0xe880c1]
/mysqldata/app/8.0.36/bin/mysqld(dispatch_sql_command(THD*, Parser_state*)+0x51b) [0xe8ad6b]
/mysqldata/app/8.0.36/bin/mysqld(dispatch_command(THD*, COM_DATA const*, enum_server_command)+0x2351) [0xe8d6b1]
/mysqldata/app/8.0.36/bin/mysqld(do_command(THD*)+0x15b) [0xe8e22b]
/mysqldata/app/8.0.36/bin/mysqld() [0xfe7408]
/mysqldata/app/8.0.36/bin/mysqld() [0x284ef54]
/lib64/libpthread.so.0(+0x8fed) [0x7f978d869fed]
/lib64/libc.so.6(clone+0x3f) [0x7f978caa616f]

Trying to get some variables.
Some pointers may be invalid and cause the dump to abort.
Query (7f951cb37360): is an invalid pointer
Connection ID (thread ID): 4490936
Status: NOT_KILLED

The manual page at http://dev.mysql.com/doc/mysql/en/crashing.html contains
information that should help you find out what is causing the crash.

检查磁盘空间:

mysql@CJC-DB-001:/mysqldata/3306/binlog$df -h
Filesystem                        Size  Used Avail Use% Mounted on
devtmpfs                          9.4G     0  9.4G   0% /dev
tmpfs                             9.5G   12K  9.5G   1% /dev/shm
tmpfs                             9.5G  994M  8.5G  11% /run
tmpfs                             9.5G     0  9.5G   0% /sys/fs/cgroup
/dev/mapper/vg_os-lv_root          42G   18G   25G  42% /
tmpfs                             9.5G   40K  9.5G   1% /tmp
/dev/sda1                        1014M  224M  791M  23% /boot
/dev/mapper/vg_data-lv_mysqldata  199G  143G   57G  72% /mysqldata
/dev/mapper/vg_os-lv_itump        5.0G  298M  4.7G   6% /opt/itump
tmpfs                             1.9G     0  1.9G   0% /run/user/0
tmpfs                             1.9G     0  1.9G   0% /run/user/995

df -i
/dev/mapper/vg_data-lv_mysqldata 104329216  11264 104317952    1% /mysqldata

尝试启动mysql，失败:

mysql@CJC-DB-001:/mysqldata/3306$mysqld --defaults-file=/etc/my.cnf --user=mysql &

报错如下:

2025-11-01T19:55:06.403126+08:00 0 [Warning] [MY-010091] [Server] Can't create test file /mysqldata/3306/data/mysqld_tmp_file_case_insensitive_test.lower-test
2025-11-01T19:55:06.403236+08:00 0 [System] [MY-010116] [Server] /mysqldata/app/8.0.36/bin/mysqld (mysqld 8.0.36) starting as process 3023407
2025-11-01T19:55:06.533607+08:00 0 [Warning] [MY-010091] [Server] Can't create test file /mysqldata/3306/data/mysqld_tmp_file_case_insensitive_test.lower-test
2025-11-01T19:55:06.533629+08:00 0 [Warning] [MY-010091] [Server] Can't create test file /mysqldata/3306/data/mysqld_tmp_file_case_insensitive_test.lower-test
mysqld: File '/mysqldata/3306/binlog/mysql-bin.~rec~' not found (OS errno 28 - No space left on device)
2025-11-01T19:55:06.598866+08:00 0 [ERROR] [MY-010846] [Server] MYSQL_BIN_LOG::open_purge_index_file failed to open register file.
2025-11-01T19:55:06.598888+08:00 0 [ERROR] [MY-010817] [Server] MYSQL_BIN_LOG::open_index_file failed to sync the index file.
2025-11-01T19:55:06.598899+08:00 0 [ERROR] [MY-010119] [Server] Aborting
2025-11-01T19:55:06.599134+08:00 0 [System] [MY-010910] [Server] /mysqldata/app/8.0.36/bin/mysqld: Shutdown complete (mysqld 8.0.36)  MySQL Community Server - GPL.

手动创建文件，进行测试:
拷贝一个513MB的文件，失败。

mysql@CJC-DB-001:/mysqldata/3306/binlog$cp mysql-bin.006528 mysql-bin.006528.bak
cp: cannot create regular file 'mysql-bin.006528.bak': No space left on device

df -h
/dev/mapper/vg_data-lv_mysqldata  199G  143G   57G  72% /mysqldata

df -i
/dev/mapper/vg_data-lv_mysqldata 104329216  11264 104317952    1% /mysqldata

查看binlog日志:

mysql@CJC-DB-001:/mysqldata/3306/binlog$ls -lrth
total 13G
-rw-r----- 1 mysql mysql 223M Oct 31 05:34 mysql-bin.006500
-rw-r----- 1 mysql mysql 6.1M Oct 31 05:35 mysql-bin.006501
-rw-r----- 1 mysql mysql 513M Oct 31 07:10 mysql-bin.006502
-rw-r----- 1 mysql mysql 513M Oct 31 08:38 mysql-bin.006503
-rw-r----- 1 mysql mysql 513M Oct 31 10:10 mysql-bin.006504
-rw-r----- 1 mysql mysql 513M Oct 31 11:41 mysql-bin.006505
-rw-r----- 1 mysql mysql 513M Oct 31 13:10 mysql-bin.006506
-rw-r----- 1 mysql mysql 513M Oct 31 14:47 mysql-bin.006507
-rw-r----- 1 mysql mysql 513M Oct 31 16:13 mysql-bin.006508
-rw-r----- 1 mysql mysql 513M Oct 31 17:48 mysql-bin.006509
-rw-r----- 1 mysql mysql 513M Oct 31 18:57 mysql-bin.006510
-rw-r----- 1 mysql mysql 513M Oct 31 20:23 mysql-bin.006511
-rw-r----- 1 mysql mysql 513M Oct 31 22:04 mysql-bin.006512
-rw-r----- 1 mysql mysql 513M Oct 31 23:31 mysql-bin.006513
-rw-r----- 1 mysql mysql 513M Nov  1 00:46 mysql-bin.006514
-rw-r----- 1 mysql mysql 513M Nov  1 01:40 mysql-bin.006515
-rw-r----- 1 mysql mysql 513M Nov  1 03:05 mysql-bin.006516
-rw-r----- 1 mysql mysql 513M Nov  1 04:28 mysql-bin.006517
-rw-r----- 1 mysql mysql 140M Nov  1 05:00 mysql-bin.006518
-rw-r----- 1 mysql mysql 223M Nov  1 05:34 mysql-bin.006519
-rw-r----- 1 mysql mysql 4.8M Nov  1 05:35 mysql-bin.006520
-rw-r----- 1 mysql mysql 513M Nov  1 07:10 mysql-bin.006521
-rw-r----- 1 mysql mysql 513M Nov  1 08:38 mysql-bin.006522
-rw-r----- 1 mysql mysql 513M Nov  1 10:10 mysql-bin.006523
-rw-r----- 1 mysql mysql 513M Nov  1 11:46 mysql-bin.006524
-rw-r----- 1 mysql mysql 513M Nov  1 13:15 mysql-bin.006525
-rw-r----- 1 mysql mysql 513M Nov  1 14:56 mysql-bin.006526
-rw-r----- 1 mysql mysql 513M Nov  1 16:23 mysql-bin.006527
-rw-r----- 1 mysql mysql 1.6K Nov  1 16:23 mysql-bin.index
-rw-r----- 1 mysql mysql 513M Nov  1 17:55 mysql-bin.006528

删除文件:

mysql@CJC-DB-001:/mysqldata/3306/binlog$rm -rf mysql-bin.00649*

再次启动数据库，启动成功:

mysql@CJC-DB-001:/mysqldata/3306$mysqld --defaults-file=/etc/my.cnf --user=mysql &
[1] 3024537

修改binlog保留参数，由保留2天，改成保留6小时。

---my.cnf
#binlog_expire_logs_seconds=172800
binlog_expire_logs_seconds=21600

mysql> show global variables like 'binlog_expire_logs_seconds';
+----------------------------+--------+
| Variable_name              | Value  |
+----------------------------+--------+
| binlog_expire_logs_seconds | 172800 |
+----------------------------+--------+
1 row in set (0.00 sec)

mysql> set global binlog_expire_logs_seconds=21600;
Query OK, 0 rows affected (0.00 sec)

mysql> flush logs;
Query OK, 0 rows affected, 10 warnings (8.52 sec)

mysql@CJC-DB-001:/mysqldata/3306/binlog$ls -lrth
total 1.8G
-rw-r----- 1 mysql mysql 513M Nov  1 14:56 mysql-bin.006526
-rw-r----- 1 mysql mysql 513M Nov  1 16:23 mysql-bin.006527
-rw-r----- 1 mysql mysql 513M Nov  1 17:55 mysql-bin.006528
-rw-r----- 1 mysql mysql 208M Nov  1 20:11 mysql-bin.006529
-rw-r----- 1 mysql mysql  205 Nov  1 20:11 mysql-bin.index
-rw-r----- 1 mysql mysql  12M Nov  1 20:12 mysql-bin.006530

磁盘空间：

/dev/mapper/vg_data-lv_mysqldata  199G  128G   72G  64% /mysqldata

因为之前，显示剩余57GB，实际只有0GB

/dev/mapper/vg_data-lv_mysqldata  199G  143G   57G  72% /mysqldata

所有当前显示72GB，实际空闲应该只有15GB。
72G-57G=15G 还有15GB空闲

问题分析:

初步怀疑和MySQL的透明压缩有关:

之前遇到的问题:

https://www.modb.pro/db/1862695874929897472

MySQL 透明页压缩介绍:
透明页压缩TPC（Transparent Page Compression）是 5.7 版本推出的一种新的页压缩功能，其利用文件系统的空洞（Punch Hole）特性进行压缩。
由于空洞是文件系统的一个特性，利用空洞压缩只能压缩到文件系统的最小单位 4K，且其页压缩是 4K 对齐的。比如一个 16K 的页，压缩后为 7K，则实际占用空间 8K；压缩后为 3K，则实际占用空间是 4K；若压缩后是 13K，则占用空间依然为 16K。

查看MySQL哪些表开启了压缩:

mysql> SELECT TABLE_NAME, TABLE_SCHEMA, CREATE_OPTIONS FROM INFORMATION_SCHEMA.TABLES WHERE CREATE_OPTIONS LIKE '%COMPRESSION=%';
+----------------------------------------+--------------+---------------------------------------------------+
| TABLE_NAME                             | TABLE_SCHEMA | CREATE_OPTIONS                                    |
+----------------------------------------+--------------+---------------------------------------------------+
|cjc_backup                             | cjc        | row_format=DYNAMIC COMPRESSION="zlib"             |
|cjc_xxpool                             | cjc        | row_format=DYNAMIC COMPRESSION="zlib"             |
|cjc_xxpool_summary                     | cjc        | row_format=DYNAMIC COMPRESSION="zlib"             |
|cjc_blocked_lock                       | cjc        | row_format=DYNAMIC COMPRESSION="zlib"             |
|cjc_columns                            | cjc        | row_format=DYNAMIC COMPRESSION="zlib"             |
|cjc_columns_history                    | cjc        | row_format=DYNAMIC COMPRESSION="zlib"             |
......
| cjc_xxxxxxx                          | cjc        | row_format=DYNAMIC COMPRESSION="zlib"             |
| cjc_topsql_xx_bak              | cjc        | row_format=DYNAMIC COMPRESSION="zlib"             |
| cjc_topsql_old_bak                      | cjc        | row_format=DYNAMIC COMPRESSION="zlib"             |
+----------------------------------------+--------------+---------------------------------------------------+
313 rows in set (0.04 sec)

计算cp复制额外需要的空间：
需要额外 76 GB:

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              |
+-----------------+
| 76.219657897949 |
+-----------------+
1 row in set (2 min 59.17 sec)

查文件系统空闲空间分布情况:

root@CJC-DB-001:xfs_db -r -c freesp /dev/mapper/vg_data-lv_mysqldata
   from      to extents  blocks    pct
      1       1   78243   78243   0.42
      2       3 6133014 17900717  95.15   
      4       7    1720    7753   0.04
      8      15     982   10669   0.06
     16      31     999   21393   0.11
     32      63     962   42595   0.23
     64     127     974   85896   0.46
    128     255     905  164594   0.87
    256     511     238   90835   0.48
    512    1023     264  188506   1.00
   1024    2047      74  119030   0.63
   2048    4095      47  103383   0.55

查看 deepseek 的解释:
xfs_db: XFS 文件系统的调试和管理工具
-r: 以只读模式打开，防止意外修改
-c freesp: 执行 freesp 命令，显示空闲空间分布
/dev/mapper/vg_data-lv_mysqldata: LVM 逻辑卷设备路径

输出内容详细解析
表头含义

from      to extents  blocks    pct
from: 空闲空间块范围的最小值（块数）。
to: 空闲空间块范围的最大值（块数）。
extents: 该范围内的空闲区段数量，表示连续的数据块组。
blocks: 该范围内的总空闲块数。
pct: 表示该范围内的空闲空间占总可用空间的百分比。

测试环境，新添加一块新盘，没有数据写入时:

[root@cjc-db-01 ~]# xfs_db -r -c freesp /dev/mapper/vg_data03-lv_mysql03
   from      to extents  blocks    pct
      1       1      16      16   0.00
 524288  655104       4 2617820 100.00

新建一块200GB磁盘:空闲空间是连续:
使用MySQL压缩表一段时间后:可能出现的问题。

为什么都是8-12KB的碎片？
—如下内容参考XX OS厂商:
编写bpftrace脚本跟踪文件系统层的操作，确认压缩过程会对文件“打洞”。从跟踪的信息可看出，确认数据库进程会调用fallocate系统调用，对文件进行不连续的“打洞”，并且“打洞”的大小都是长度2或3的块。
通过抓取信息，调用fallocate系统调用的进程为mysqld，操作模式mode为3。当mode指定为3时，文件系统会释放指定范围内的空间，即创建一个“空洞”。“空洞”的长度len为8192字节或12288字节，即释放长度为2或3的块。结合“空洞”的偏移量offset和长度len的变化，符合隔一个小空间打一个洞的规律，即每隔16386字节（16k）打一个长度为2或3的“空洞”块。

call tracepoint:syscalls:sys_enter_fallocate comm:mysqld pid:xxx fd:xx mode 3 offset:xxxxxxxx len:8192
call tracepoint:syscalls:sys_enter_fallocate comm:mysqld pid:xxx fd:xx mode 3 offset:xxxxxxxx len:12288
......

通过数据分析，数据库会持续、有规律的执行“打洞”操作，并且“打洞”的块集中在长度为2和3的块。长期运行后，最终会导致文件系统中所有空闲块的长度都集中在2和3区间。这就是文件系统“碎片化”程度随着数据量的上升而不断增加的原因。

为什么写入文件会失败:
—如下内容参考XX OS厂商:
在创建文件时，文件系统会先检查分配组（AG）中是否有预分配的inode空间。如果有预分配inode空间，则使用预分配inode空间来创建文件。如果没有，则需要分配连续的4个块以增加inode空间。当无法找到连续的4个块时，就会报错，提示空间不足。
在高“碎片化”场景下，通过不断的创建空文件来消耗预分配的inode空间，可以复现创建文件失败的情况。通过代码追踪和开启调试模式可知，错误的原因是整个文件系统都不能找到一个长度为4的连续块。其中，ineed值为4，表示需要分配长度为4的连续块。longest值为2或3，表示当前分配组（AG）只存在长度为2或3的连续块。

nospace:agno:0,freeblks:1000xxx,longest:3,ineed:4,needspace:1
nospace:agno:0,freeblks:1000yyy,longest:3,ineed:4,needspace:1
nospace:agno:0,freeblks:1000zzz,longest:3,ineed:4,needspace:1
......

为什么 95.15% 就无法写入了？

root@CJC-DB-001:xfs_db -r -c freesp /dev/mapper/vg_data-lv_mysqldata
   from      to extents  blocks    pct
      1       1   78243   78243   0.42
      2       3 6133014 17900717  95.15   
      4       7    1720    7753   0.04

因为故障当天，为了启动MySQL,临时删除了15GB的binlog文件，经测试，删除文件后，[2,3] 区的空闲百分比会有明显下降，也就是故障当天> 95.15%，应该达到了99%甚至100%。

对比查看数据量较大、insert和delete频繁的另一套MySQL，没有启动透明压缩:

root@CHEN:/root#xfs_db -r -c freesp /dev/mapper/vg_data-lv_mysqldata
   from      to extents  blocks    pct
      1       1      77      77   0.00
      2       3     124     315   0.00
      4       7     101     414   0.00
      8      15      31     321   0.00
     16      31      43     955   0.01
     32      63      40    1856   0.02
     64     127      54    4872   0.06
    128     255      98   20277   0.23
    256     511      54   20234   0.23
    512    1023      93   74061   0.85
   1024    2047      22   32420   0.37
   2048    4095      12   42871   0.49
   4096    8191      11   67811   0.78
   8192   16383      10  133762   1.53
  16384   32767      12  316221   3.62
  32768   65535       3  107676   1.23
  65536  131071       3  298138   3.41
 262144  524287       1  391022   4.48
 524288 1048575       1  872043   9.99
1048576 2097151       1 1095240  12.54
4194304 6483200       1 5251058  60.14

1.MySQL压缩表测试

XFS磁盘空间分配、碎片测试:

测试MySQL透明压缩对碎片的影响:

[root@cjc-db-01 ~]# lsblk 
NAME                   MAJ:MIN RM  SIZE RO TYPE MOUNTPOINT
sda                      8:0    0   50G  0 disk 
├─sda1                   8:1    0    1G  0 part /boot
└─sda2                   8:2    0   49G  0 part 
  ├─ol-root            249:0    0   46G  0 lvm  /
  └─ol-swap            249:1    0    3G  0 lvm  [SWAP]
sdb                      8:16   0   30G  0 disk 
└─vg_data-lv_mysqldata 249:2    0   30G  0 lvm  /mysqldata
sdc                      8:32   0   10G  0 disk 
sdd                      8:48   0   10G  0 disk 
sde                      8:64   0   10G  0 disk 
sdf                      8:80   0   10G  0 disk 
sr0                     11:0    1  4.1G  0 rom

挂载:

/dev/mapper/vg_data01-lv_mysql01   /mysql01       xfs     defaults     0   0
/dev/mapper/vg_data02-lv_mysql02   /mysql02       xfs     defaults     0   0
/dev/mapper/vg_data03-lv_mysql03   /mysql03       xfs     defaults     0   0
/dev/mapper/vg_data04-lv_mysql04   /mysql04       ext4    defaults     0   0

启动数据库：

mysqld --defaults-file=/mysql01/3307/conf/my.cnf --user=mysql &
mysqld --defaults-file=/mysql02/3308/conf/my.cnf --user=mysql &
mysqld --defaults-file=/mysql04/3309/conf/my.cnf --user=mysql &

mysql01：创建压缩表，插入大量数据:

use cjc;
create table t1(id int,name varchar(20)) COMPRESSION='zlib';
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');
insert into cjc.t1 select * from t1;
......
insert into cjc.t1 select * from t1;
Query OK, 41943040 rows affected (4 min 48.78 sec)
Records: 41943040  Duplicates: 0  Warnings: 0

mysql02：创建普通表，插入大量数据:

use cjc;
create table t1(id int,name varchar(20));
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');
insert into cjc.t1 select * from t1;
......
insert into cjc.t1 select * from t1;
Query OK, 41943040 rows affected (4 min 44.74 sec)
Records: 41943040  Duplicates: 0  Warnings: 0

mysql04：创建压缩表，插入大量数据:

use cjc;
create table t1(id int,name varchar(20)) COMPRESSION='zlib';
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');
insert into cjc.t1 select * from t1;
......
insert into cjc.t1 select * from t1;
Query OK, 41943040 rows affected (4 min 20.74 sec)
Records: 41943040  Duplicates: 0  Warnings: 0

mysql04：创建普通表，插入大量数据:

use cjc;
create table t1(id int,name varchar(20)) ;
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');
insert into cjc.t1 select * from t1;
......
mysql> insert into cjc.t1 select * from t1;
Query OK, 41943040 rows affected (2 min 36.98 sec)
Records: 41943040  Duplicates: 0  Warnings: 0

mysql01查看碎片情况:

[root@cjc-db-01 ~]# xfs_db -r -c freesp /dev/mapper/vg_data01-lv_mysql01
   from      to extents  blocks    pct
      1       1      64      64   0.01
      2       3  172909  374832  42.86
    512    1023       2    1474   0.17
  16384   32767       1   29531   3.38
  65536  131071       1  130771  14.95
 262144  524287       1  337943  38.64

mysql02查看碎片情况:

[root@cjc-db-01 ~]#  xfs_db -r -c freesp /dev/mapper/vg_data02-lv_mysql02
   from      to extents  blocks    pct
      1       1      20      20   0.00
      2       3       1       3   0.00
      4       7       9      36   0.01
     16      31       1      24   0.00
    128     255       1     246   0.05
    512    1023       2    1815   0.36
  16384   32767       1   29531   5.90
  65536  131071       1  130771  26.13
 262144  524287       1  337949  67.54

mysql04压缩表查看碎片情况:

[root@cjc-db-01 mysql04]# e2freefrag /dev/mapper/vg_data04-lv_mysql04
Device: /dev/mapper/vg_data04-lv_mysql04
Blocksize: 4096 bytes
Total blocks: 2620416
Free blocks: 2537279 (96.8%)

Min. free extent: 4 KB 
Max. free extent: 258036 KB
Avg. free extent: 20 KB
Num. free extent: 153238

HISTOGRAM OF FREE EXTENT SIZES:
Extent Size Range :  Free extents   Free Blocks  Percent
    4K...    8K-  :           169           169    0.01%
    8K...   16K-  :        153016        332007   13.09%
   16K...   32K-  :             2             8    0.00%
   32K...   64K-  :             1             8    0.00%
   64K...  128K-  :             2            59    0.00%
  128K...  256K-  :             1            32    0.00%
  512K... 1024K-  :             5          1168    0.05%
    1M...    2M-  :             5          1767    0.07%
    2M...    4M-  :            15         14063    0.55%
    4M...    8M-  :             2          2784    0.11%
    8M...   16M-  :             3          9218    0.36%
   32M...   64M-  :             5         65284    2.57%
   64M...  128M-  :             9        218777    8.62%
  128M...  256M-  :             3        156413    6.16%

mysql04普通表查看碎片情况:

[root@cjc-db-01 mysql04]# e2freefrag /dev/mapper/vg_data04-lv_mysql04
Device: /dev/mapper/vg_data04-lv_mysql04
Blocksize: 4096 bytes
Total blocks: 2620416
Free blocks: 2537279 (96.8%)

Min. free extent: 4 KB 
Max. free extent: 98176 KB
Avg. free extent: 17248 KB
Num. free extent: 88

HISTOGRAM OF FREE EXTENT SIZES:
Extent Size Range :  Free extents   Free Blocks  Percent
    4K...    8K-  :             5             5    0.00%
    8K...   16K-  :             3             7    0.00%
   16K...   32K-  :             3            12    0.00%
   32K...   64K-  :             1             8    0.00%
   64K...  128K-  :             1            29    0.00%
  128K...  256K-  :             2            94    0.00%
  256K...  512K-  :             1            92    0.00%
  512K... 1024K-  :             6          1330    0.05%
    1M...    2M-  :             7          2279    0.09%
    2M...    4M-  :            18         15119    0.60%
    4M...    8M-  :             5          6368    0.25%
    8M...   16M-  :             7         23905    0.94%
   16M...   32M-  :             6         25600    1.01%
   32M...   64M-  :            16        142080    5.60%
   64M...  128M-  :             7        162548    6.41%

对比:

新增小数据测试:

insert into cjc.t1 values(1,'a');
insert into cjc.t1 values(1,'a');
insert into cjc.t1 values(1,'a');
......

压缩表:

[root@cjc-db-01 ~]# xfs_db -r -c freesp /dev/mapper/vg_data01-lv_mysql01
   from      to extents  blocks    pct
      1       1      64      64   0.01
      2       3  173389  375882  42.93 ----有上涨
    512    1023       2    1472   0.17
  16384   32767       1   29531   3.37
  65536  131071       1  130771  14.93
 262144  524287       1  337943  38.59

非压缩表

[root@cjc-db-01 ~]#  xfs_db -r -c freesp /dev/mapper/vg_data02-lv_mysql02
   from      to extents  blocks    pct
      1       1      20      20   0.00
      2       3       1       3   0.00
      4       7       9      36   0.01
     16      31       1      24   0.00
    128     255       1     246   0.05
    512    1023       2    1815   0.36
  16384   32767       1   29531   5.90
  65536  131071       1  130771  26.13
 262144  524287       1  337949  67.54

继续进行压缩表的测试:批量创建1万张压缩表
写一个shell脚本，在mysql创建1万张表，表名是a00001 … a10000，其中一张表如下:

create table a00001(id int,name varchar(20),time time,primary key(id)) COMPRESSION='zlib';
vi create_table_10000.sh

#!/bin/bash

# MySQL连接配置
MYSQL_USER="root"
MYSQL_PASSWORD="1"
MYSQL_HOST="localhost"
MYSQL_DATABASE="cjc"

# 检查MySQL连接
mysql -h $MYSQL_HOST -u $MYSQL_USER -p$MYSQL_PASSWORD -e "USE $MYSQL_DATABASE" --socket=/mysql01/3307/socket/mysql.sock 2>/dev/null
if [ $? -ne 0 ]; then
    echo "无法连接到MySQL数据库，请检查配置"
    exit 1
fi

echo "开始创建表..."

# 循环创建10000张表
for i in $(seq -f "%05g" 1 10000); do
    TABLE_NAME="a${i}"
    
    # 构建SQL语句
    SQL="CREATE TABLE $TABLE_NAME (
        id INT,
        name VARCHAR(20),
        time TIME,
        PRIMARY KEY(id)
    ) COMPRESSION='zlib';"
    
    # 执行SQL
    mysql -h $MYSQL_HOST -u $MYSQL_USER -p$MYSQL_PASSWORD $MYSQL_DATABASE -e "$SQL" --socket=/mysql01/3307/socket/mysql.sock
    
    # 显示进度 - 去除前导零
    num=$(echo $i | sed 's/^0*//')  # 去除前导零
    if [ $((num % 1000)) -eq 0 ]; then
        echo "已创建 ${num} 张表..."
    fi
done

echo "所有表创建完成！"

[root@cjc-db-01 tmp]# tail -10f create_table_10000.log 
开始创建表...
已创建 1000 张表...
已创建 2000 张表...
已创建 3000 张表...
已创建 4000 张表...
已创建 5000 张表...
已创建 6000 张表...
已创建 7000 张表...
已创建 8000 张表...
已创建 9000 张表...
已创建 10000 张表...
所有表创建完成！

创建前:

   from      to extents  blocks    pct
      1       1      21      21   0.00
      2       3       3       9   0.00

创建后:

   from      to extents  blocks    pct
      1       1      22      22   0.00
      2       3   39902  119705   9.14

继续对新生成的1万张表里，插入1万条数据:

#!/bin/bash

# MySQL连接配置
MYSQL_USER="root"
MYSQL_PASSWORD="1"
MYSQL_HOST="localhost"
MYSQL_DATABASE="cjc"

# 检查MySQL连接
mysql -h $MYSQL_HOST -u $MYSQL_USER -p$MYSQL_PASSWORD -e "USE $MYSQL_DATABASE" --socket=/mysql01/3307/socket/mysql.sock  2>/dev/null
if [ $? -ne 0 ]; then
    echo "无法连接到MySQL数据库，请检查配置"
    exit 1
fi

echo "开始生成通用数据模板..."
START_TIME=$(date +%s)

# 创建临时SQL文件
SQL_FILE="/mysql01/3307/tmp/bulk_insert.sql"
> $SQL_FILE

# 预先生成10000行固定数据（使用更高效的方法）
echo "START TRANSACTION;" >> $SQL_FILE

echo "生成10000行固定数据模板..."
# 使用高效的方法生成固定数据模板
{
    for ((row_num=1; row_num<=10000; row_num++)); do
        USER_NAME="user$(printf "%05d" $row_num)"
        HOUR=$(( (row_num / 3600) % 24 ))
        MINUTE=$(( (row_num / 60) % 60 ))
        SECOND=$(( row_num % 60 ))
        TIME_VALUE=$(printf "%02d:%02d:%02d" $HOUR $MINUTE $SECOND)
        echo "($row_num, '$USER_NAME', '$TIME_VALUE')"
    done
} > /tmp/data_template.txt

echo "数据模板生成完成，开始构建插入语句..."

# 读取数据模板文件
DATA_TEMPLATE=$(cat /tmp/data_template.txt | tr '\n' ',' | sed 's/,$//')

# 为所有表生成INSERT语句（使用相同的数据模板）
for ((table_num=1; table_num<=10000; table_num++)); do
    TABLE_NAME=$(printf "a%05d" $table_num)
    echo "INSERT INTO $TABLE_NAME (id, name, time) VALUES $DATA_TEMPLATE;" >> $SQL_FILE
    
    # 每500张表提交一次，减少事务大小
    if [ $((table_num % 500)) -eq 0 ]; then
        echo "COMMIT;" >> $SQL_FILE
        echo "START TRANSACTION;" >> $SQL_FILE
        
        # 显示进度
        CURRENT_TIME=$(date +%s)
        ELAPSED_TIME=$((CURRENT_TIME - START_TIME))
        echo "已生成 $table_num/10000 张表的插入语句"
        echo "已用时: $((ELAPSED_TIME / 60)) 分钟 $((ELAPSED_TIME % 60)) 秒"
    fi
done

echo "COMMIT;" >> $SQL_FILE

echo "开始执行批量插入..."
BULK_START_TIME=$(date +%s)

# 使用更高效的导入方式
mysql -h $MYSQL_HOST -u $MYSQL_USER -p$MYSQL_PASSWORD $MYSQL_DATABASE --socket=/mysql01/3307/socket/mysql.sock  < $SQL_FILE

BULK_END_TIME=$(date +%s)
BULK_TIME=$((BULK_END_TIME - BULK_START_TIME))

# 清理临时文件
rm -f $SQL_FILE /tmp/data_template.txt

END_TIME=$(date +%s)
TOTAL_TIME=$((END_TIME - START_TIME))

echo "所有数据插入完成！"
echo "SQL生成用时: $(( (TOTAL_TIME - BULK_TIME) / 60 )) 分钟 $(( (TOTAL_TIME - BULK_TIME) % 60 )) 秒"
echo "数据库插入用时: $((BULK_TIME / 60)) 分钟 $((BULK_TIME % 60)) 秒"
echo "总用时: $((TOTAL_TIME / 3600)) 小时 $((TOTAL_TIME % 3600 / 60)) 分钟 $((TOTAL_TIME % 60)) 秒"
echo "总数据量: 10000 张表 × 10000 行 = 1亿行数据"


[root@cjc-db-01 tmp]# nohup sh insert_table_10000.sh > insert_table_10000.log 2>insert.log &
[2] 30558

插入数据过程中:
[root@cjc-db-01 ~]# xfs_db -r -c freesp /dev/mapper/vg_data01-lv_mysql01
   from      to extents  blocks    pct
      1       1      96      96   0.03
      2       3  148065  328487  99.97
......

插入完成后:会自动下降一些？

2.操作系统写入数据测试:

1.频繁创建和删除小文件(8-12KB)，循环100万次。
2.大文件和小文件频繁交叉创建和删除，循环100万次。
3.创建10万个小文件，gzip压缩。

1.频繁创建和删除小文件(8-12KB)，循环100万次。

#!/bin/bash
vi simple_frag.sh
WORK_DIR="/mysql03/frag_test"
mkdir -p "$WORK_DIR"

echo "开始创建和删除小文件..."

for i in {1..1000000}; do
    # 创建小文件（8-12KB）
    dd if=/dev/urandom of="$WORK_DIR/file_$i.dat" bs=1K count=$((8 + RANDOM % 5)) status=none 2>/dev/null
    
    # 70%概率立即删除
    [ $((RANDOM % 10)) -lt 7 ] && rm -f "$WORK_DIR/file_$i.dat"
    
    # 每1万次显示进度
    [ $((i % 10000)) -eq 0 ] && echo "进度: $i/1000000"
    
    # 控制文件数量不超过500个
    [ $(ls "$WORK_DIR"/*.dat 2>/dev/null | wc -l) -gt 500 ] && ls -t "$WORK_DIR"/*.dat | tail -50 | xargs rm -f 2>/dev/null
done

echo "完成！"

后台运行（推荐）：

nohup ./simple_frag.sh > frag.log 2>&1 &

执行脚本之前:

[root@cjc-db-01 ~]# xfs_db -r -c freesp /dev/mapper/vg_data03-lv_mysql03
   from      to extents  blocks    pct
      1       1      16      16   0.00
 524288  655104       4 2617562 100.00

执行脚本之后:

[root@cjc-db-01 frag_test]# xfs_db -r -c freesp /dev/mapper/vg_data03-lv_mysql03
   from      to extents  blocks    pct
      1       1      18      18   0.00
      2       3       1       2   0.00
     32      63       1      53   0.00
    128     255       1     225   0.01
 524288  655104       4 2616161  99.99

2.大文件和小文件频繁交叉创建和删除

#!/bin/bash

# 改进版碎片生成脚本
# 通过多种技术强制产生2-3块碎片

WORK_DIR="/mysql03/frag_test"
mkdir -p "$WORK_DIR"

echo "开始强制产生碎片..."

# 第一阶段：先填充磁盘，减少连续空间
echo "第一阶段：填充磁盘减少连续空间..."
for i in {1..500}; do
    # 创建中等文件占用连续空间
    dd if=/dev/urandom of="$WORK_DIR/filler_$i.dat" bs=1M count=10 status=none 2>/dev/null
    [ $((i % 50)) -eq 0 ] && echo "填充进度: $i/500"
done

# 第二阶段：交错创建不同大小的文件
echo "第二阶段：交错创建不同大小文件..."
for i in {1..50000}; do
    # 随机选择文件大小
    case $((RANDOM % 4)) in
        0)
            # 小文件 (2-3块)
            dd if=/dev/urandom of="$WORK_DIR/small_$i.dat" bs=1K count=$((8 + RANDOM % 5)) status=none 2>/dev/null
            ;;
        1)
            # 中等文件
            dd if=/dev/urandom of="$WORK_DIR/medium_$i.dat" bs=1K count=$((50 + RANDOM % 100)) status=none 2>/dev/null
            ;;
        2)
            # 大文件
            dd if=/dev/urandom of="$WORK_DIR/large_$i.dat" bs=1M count=$((1 + RANDOM % 5)) status=none 2>/dev/null
            ;;
        3)
            # 极小文件 (1块左右)
            dd if=/dev/urandom of="$WORK_DIR/tiny_$i.dat" bs=512 count=$((1 + RANDOM % 4)) status=none 2>/dev/null
            ;;
    esac
    
    # 随机删除一些文件，制造空洞
    if [ $((RANDOM % 3)) -eq 0 ]; then
        # 随机删除一个小文件
        find "$WORK_DIR" -name "small_*.dat" -type f | shuf -n 1 | xargs rm -f 2>/dev/null || true
    fi
    
    if [ $((RANDOM % 5)) -eq 0 ]; then
        # 随机删除一个中等文件
        find "$WORK_DIR" -name "medium_*.dat" -type f | shuf -n 1 | xargs rm -f 2>/dev/null || true
    fi
    
    # 显示进度
    [ $((i % 1000)) -eq 0 ] && echo "交错创建进度: $i/50000"
    
    # 控制总文件数
    total_files=$(find "$WORK_DIR" -name "*.dat" | wc -l)
    if [ $total_files -gt 2000 ]; then
        # 删除最旧的文件
        find "$WORK_DIR" -name "*.dat" -type f | head -500 | xargs rm -f 2>/dev/null
    fi
done

# 第三阶段：专门产生小文件碎片
echo "第三阶段：专门产生小文件碎片..."
for i in {1..100000}; do
    # 只创建小文件
    file_size=$((4096 + RANDOM % 8192))  # 4-12KB
    dd if=/dev/urandom of="$WORK_DIR/frag_$i.dat" bs=1 count=$file_size status=none 2>/dev/null
    
    # 高概率立即删除（制造碎片）
    if [ $((RANDOM % 10)) -lt 8 ]; then
        rm -f "$WORK_DIR/frag_$i.dat"
    fi
    
    # 定期检查碎片状态
    if [ $((i % 5000)) -eq 0 ]; then
        echo "小文件碎片进度: $i/100000"
        if command -v xfs_db >/dev/null 2>&1; then
            device=$(df /mysql03 | awk 'NR==2 {print $1}')
            echo "当前碎片状态:"
            xfs_db -r -c "freesp" "$device" 2>/dev/null | head -10
        fi
    fi
    
    # 控制文件数量
    [ $(find "$WORK_DIR" -name "*.dat" | wc -l) -gt 1000 ] && find "$WORK_DIR" -name "*.dat" | head -200 | xargs rm -f 2>/dev/null
done

echo "完成！最终碎片状态:"
if command -v xfs_db >/dev/null 2>&1; then
    device=$(df /mysql03 | awk 'NR==2 {print $1}')
    xfs_db -r -c "freesp" "$device" 2>/dev/null
fi

也没有复现问题:

[root@cjc-db-01 frag_test]# xfs_db -r -c freesp /dev/mapper/vg_data03-lv_mysql03
   from      to extents  blocks    pct
      1       1      33      33   0.00
      2       3       9      19   0.00
      4       7       2       8   0.00
      8      15      13     179   0.01
     16      31      75    1780   0.09
     32      63      55    2259   0.11
     64     127      12     858   0.04
    128     255       3     617   0.03
    256     511       4    1024   0.05
    512    1023       8    4864   0.24
   1024    2047       8    9229   0.46
   2048    4095       3    9081   0.45
   4096    8191       4   23001   1.14
   8192   16383       4   42379   2.10
  16384   32767       1   18161   0.90
  65536  131071       2  204283  10.14
 131072  262143       2  401815  19.95
 524288  655104       2 1294993  64.28

3.创建10万个小文件，并执行gzip压缩

[root@cjc-db-01 tmp]# cat zip.sh 
#!/bin/bash

#TEMP_DIR=$(/mysql03/tmp)
cd /mysql03/tmp/

for i in $(seq 1 100000); do
    # 使用fallocate快速分配空间并填充数据
    fallocate -l 102400 file_${i}.tmp
    # 压缩并删除原文件
    gzip file_${i}.tmp
done

echo "完成！文件保存在: $TEMP_DIR"

创建文件:

[root@cjc-db-01 tmp]# xfs_db -r -c freesp /dev/mapper/vg_data03-lv_mysql03
   from      to extents  blocks    pct
      1       1      16      16   0.00
      8      15       1      10   0.00
     16      31       1      25   0.00
     32      63       1      45   0.00
 524288  655104       4 2487020 100.00

删除文件:

[root@cjc-db-01 mysql03]# rm -rf tmp/
[root@cjc-db-01 mysql03]# xfs_db -r -c freesp /dev/mapper/vg_data03-lv_mysql03
   from      to extents  blocks    pct
      1       1      22      22   0.00
      2       3     775    1951   0.08
      4       7      53     254   0.01
      8      15       1       8   0.00
  65536  131071       1  104267   4.02
 524288  655104       4 2487937  95.89

临时解决方案:

磁盘碎片整理:

root@CJC-DB-001:/root#df -h
Filesystem                        Size  Used Avail Use% Mounted on
devtmpfs                          9.4G     0  9.4G   0% /dev
tmpfs                             9.5G   12K  9.5G   1% /dev/shm
tmpfs                             9.5G  1.1G  8.5G  11% /run
tmpfs                             9.5G     0  9.5G   0% /sys/fs/cgroup
/dev/mapper/vg_os-lv_root          42G   18G   25G  42% /
tmpfs                             9.5G   68K  9.5G   1% /tmp
/dev/sda1                        1014M  224M  791M  23% /boot
/dev/mapper/vg_data-lv_mysqldata  199G  130G   70G  65% /mysqldata
/dev/mapper/vg_os-lv_itump        5.0G  314M  4.7G   7% /opt/itump
tmpfs                             1.9G     0  1.9G   0% /run/user/0
tmpfs                             1.9G     0  1.9G   0% /run/user/995

查文件碎片率:

root@CJC-DB-001:/root#xfs_db -r -c freesp /dev/mapper/vg_data-lv_mysqldata
   from      to extents  blocks    pct
      1       1   77813   77813   0.42
      2       3 6136321 17907585  96.14
      4       7    1712    7718   0.04
      8      15    1009   10956   0.06
     16      31    1017   21747   0.12
     32      63    1035   46101   0.25
     64     127    1014   89876   0.48
    128     255     987  181766   0.98
    256     511     179   69206   0.37
    512    1023     193  144328   0.77
   1024    2047      26   36951   0.20
   2048    4095      16   33202   0.18

检查mysql并停库:

mysql -uroot -p
select user,SUBSTRING_INDEX(host,':',1) as ip,db,count(*) as num from information_schema.processlist group by user,ip,db order by num desc;
show processlist;
shutdown;
exit;

停库后，再次检查磁盘碎片,磁盘碎片基本没变化:

xfs_db -r -c freesp /dev/mapper/vg_data-lv_mysqldata
   from      to extents  blocks    pct
      1       1   77814   77814   0.42
      2       3 6136311 17907577  96.10
      4       7    1691    7626   0.04
      8      15    1004   10900   0.06
     16      31    1087   23208   0.12
     32      63    1044   46435   0.25
     64     127    1019   90266   0.48
    128     255     986  181521   0.97
    256     511     180   69706   0.37
    512    1023     193  144328   0.77
   1024    2047      29   41270   0.22
   2048    4095      16   33202   0.18

整理磁盘碎片
耗时1小时52分钟:
root用户:

root@CJC-DB-001:/root#xfs_fsr /dev/mapper/vg_data-lv_mysqldata
/mysqldata start inode=0

17:07 --- 18:59

磁盘整理过程中，通过iotop查看I/O情况:
最开始:

Total DISK READ :      96.70 M/s | Total DISK WRITE :      93.86 M/s
Actual DISK READ:      96.70 M/s | Actual DISK WRITE:      95.79 M/s
    TID  PRIO  USER     DISK READ  DISK WRITE  SWAPIN     IO>    COMMAND                                                                                                                    
4003537 be/4 root       96.70 M/s   93.85 M/s  0.00 % 87.59 % xfs_fsr /dev/mapper/vg_xxx-lv_mysql
   1067 be/3 root        0.00 B/s   11.67 K/s  0.00 %  0.00 % auditd
      1 be/4 root        0.00 B/s    0.00 B/s  0.00 %  0.00 % systemd --switched-root --system --deserialize 18
      2 be/4 root        0.00 B/s    0.00 B/s  0.00 %  0.00 % [kthreadd]
      3 be/0 root        0.00 B/s    0.00 B/s  0.00 %  0.00 % [rcu_gp]

后面越来越慢:

Total DISK READ :       4.36 M/s | Total DISK WRITE :       4.36 M/s
Actual DISK READ:       4.36 M/s | Actual DISK WRITE:       4.35 M/s
    TID  PRIO  USER     DISK READ  DISK WRITE  SWAPIN     IO>    COMMAND                                                                                                                    
4003537 be/4 root        4.36 M/s    4.36 M/s  0.00 % 93.22 % xfs_fsr /dev/mapper/vg_data-lv_mysqldata

整理后检查:
[2,3]区空闲块占比从 96.10 下降到 10.52。

root@CJC-DB-001:/root#xfs_db -r -c freesp /dev/mapper/vg_data-lv_mysqldata
   from      to extents  blocks    pct
      1       1 1469638 1469638   7.85
      2       3  710299 1970369  10.52
      4       7   13175   65655   0.35
      8      15    4198   45183   0.24
     16      31    1753   39029   0.21
     32      63    1170   53812   0.29
     64     127     968   89391   0.48
    128     255    1292  254951   1.36
    256     511    1236  434760   2.32
    512    1023    1195  908070   4.85
   1024    2047    1341 1741724   9.30
   2048    4095     559 1523051   8.13
   4096    8191     333 1944766  10.39
   8192   16383     223 2440221  13.03
  16384   32767      69 1572975   8.40
  32768   65535      44 1880745  10.04
  65536  131071      11  984776   5.26
 131072  262143       5 1015372   5.42
 262144  524287       1  289538   1.55

彻底解决:

申请磁盘扩容，并取消MySQL表压缩。

监控预防:

1.检查MySQL是否查看压缩表;

mysql> SELECT TABLE_NAME, TABLE_SCHEMA, CREATE_OPTIONS FROM INFORMATION_SCHEMA.TABLES WHERE CREATE_OPTIONS LIKE '%COMPRESSION=%';
+----------------------------------------+--------------+---------------------------------------------------+
| TABLE_NAME                             | TABLE_SCHEMA | CREATE_OPTIONS                                    |
+----------------------------------------+--------------+---------------------------------------------------+
|cjc_backup                             | cjc        | row_format=DYNAMIC COMPRESSION="zlib"             |
|cjc_bfpool                             | cjc        | row_format=DYNAMIC COMPRESSION="zlib"             |
......

2.检查磁盘空闲分布情况:
XFS文件系统:

[root@cjc-db-01 ~]# xfs_db -r -c freesp /dev/mapper/vg_data01-lv_mysql01
   from      to extents  blocks    pct
      1       1      64      64   0.01
      2       3  172909  374832  42.86
    512    1023       2    1474   0.17
  16384   32767       1   29531   3.38
  65536  131071       1  130771  14.95
 262144  524287       1  337943  38.64

EXT4文件系统:

[root@cjc-db-01 mysql04]# e2freefrag /dev/mapper/vg_data04-lv_mysql04
Device: /dev/mapper/vg_data04-lv_mysql04
Blocksize: 4096 bytes
Total blocks: 2620416
Free blocks: 2537279 (96.8%)

Min. free extent: 4 KB 
Max. free extent: 258036 KB
Avg. free extent: 20 KB
Num. free extent: 153238

HISTOGRAM OF FREE EXTENT SIZES:
Extent Size Range :  Free extents   Free Blocks  Percent
    4K...    8K-  :           169           169    0.01%
    8K...   16K-  :        153016        332007   13.09%
   16K...   32K-  :             2             8    0.00%
   32K...   64K-  :             1             8    0.00%
   64K...  128K-  :             2            59    0.00%
  128K...  256K-  :             1            32    0.00%
  512K... 1024K-  :             5          1168    0.05%
    1M...    2M-  :             5          1767    0.07%
    2M...    4M-  :            15         14063    0.55%
    4M...    8M-  :             2          2784    0.11%
    8M...   16M-  :             3          9218    0.36%
   32M...   64M-  :             5         65284    2.57%
   64M...  128M-  :             9        218777    8.62%
  128M...  256M-  :             3        156413    6.16%

定期检查或监控:
1.XFS 文件系统:[2,3]区间空闲百分比；
2.EXT4文件系统:8k-16k区间空闲百分比；

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