MySQL批量取消透明页压缩，解决磁盘碎片了高的问题

最新推荐文章于 2025-12-14 11:38:58 发布

原创最新推荐文章于 2025-12-14 11:38:58 发布 · 257 阅读

5 ·

CC 4.0 BY-SA版权

文章标签：

#mysql #adb #数据库

MySQL 专栏收录该内容

28 篇文章

订阅专栏

在这里插入图片描述

说明

上文介绍了《MySQL启用透明页压缩，导致宕机！》

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

本文介绍如何批量取消MySQL透明页压缩，测定解决此问题。

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

1.数据库全备

nohup sh /mysqldata/dbscripts/dump.sh 2>>/mysqldata/back/mysql_dump_err_20251111.log &

2.预估扩容磁盘空间

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=%');

3.磁盘扩容(200GB)

pvcreate /dev/sdd
vgextend vg_data /dev/sdd
lvextend -l +100%FREE /dev/mapper/vg_data-lv_mysqldata
blkid /dev/mapper/vg_data-lv_mysqldata
xfs_growfs /dev/mapper/vg_data-lv_mysqldata

4.生成存在表压缩属性的表名

SELECT concat(TABLE_SCHEMA,'.',TABLE_NAME) FROM INFORMATION_SCHEMA.TABLES WHERE CREATE_OPTIONS LIKE '%COMPRESSION=%' order by DATA_LENGTH desc;

vi /mysqldata/dbtmpfile/tmp/table_name.sql
cjc.t1                     
cjc.t2                   
cjc.t3
...313张表...

5.去掉取消表压缩的脚本:

vi /mysqldata/dbtmpfile/tmp/alert_nocompression.sh
#!/bin/bash

# MySQL连接信息
MYSQL_USER="root"
MYSQL_PASSWORD="******"
MYSQL_DATABASE="cjc"

# 表名文件
TABLE_FILE="table_name.sql"

# 日志文件
LOG_FILE="/mysqldata/dbtmpfile/tmp/alert_nocompression.log"

# 记录日志函数
log() {
    echo "$(date '+%Y-%m-%d %H:%M:%S') $1" >> $LOG_FILE
}

# 检查表名文件是否存在
if [ ! -f "$TABLE_FILE" ]; then
    echo "错误: 表名文件 $TABLE_FILE 不存在"
    exit 1
fi

# 记录开始时间
start_time=$(date '+%Y-%m-%d %H:%M:%S')
log "=== 开始执行表引擎转换 ==="
log "开始时间: $start_time"
log "表名文件: $TABLE_FILE"

# 逐行读取表名并执行ALTER TABLE
success_count=0
failed_count=0

while IFS= read -r table_name; do
    # 跳过空行
    if [[ -z "$table_name" ]]; then
        continue
    fi
    
    # 去除前后空白字符
    table_name=$(echo "$table_name" | xargs)
    
    log "开始转换表: $table_name"
    start_table=$(date '+%Y-%m-%d %H:%M:%S')
    
    # 执行ALTER TABLE语句
    if mysql -u$MYSQL_USER -p$MYSQL_PASSWORD $MYSQL_DATABASE -e "ALTER TABLE $table_name COMPRESSION='NONE';" 2>> $LOG_FILE; then
        log "成功: 表 $table_name 转换完成"
        ((success_count++))
    else
        log "失败: 表 $table_name 转换失败"
        ((failed_count++))
    fi
    
    end_table=$(date '+%Y-%m-%d %H:%M:%S')
    log "表 $table_name 执行时间: $start_table - $end_table"
    echo "" >> $LOG_FILE
    
done < "$TABLE_FILE"

# 记录结束时间
end_time=$(date '+%Y-%m-%d %H:%M:%S')
log "结束时间: $end_time"
log "执行结果: 成功 $success_count 个表，失败 $failed_count 个表"
log "=== 执行完成 ==="

echo "执行完成，成功 $success_count 个表，失败 $failed_count 个表"
echo "详情请查看日志: $LOG_FILE"

后台运行:

nohup /mysqldata/dbtmpfile/tmp/alert_nocompression.sh 2>err_inno.log &

检查执行结果：

root@CJC-DB-01:/mysqldata/dbtmpfile/tmp#cat alert_nocompression.log |grep -v "password"
2025-11-11 18:28:23 === 开始执行表引擎转换 ===
2025-11-11 18:28:23 开始时间: 2025-11-11 18:28:23
2025-11-11 18:28:23 表名文件: table_name.sql
2025-11-11 18:28:23 开始转换表: cjc.t2
2025-11-11 18:28:23 成功: 表 cjc.t2 转换完成
2025-11-11 18:28:23 表 cjc.t2 执行时间: 2025-11-11 18:28:23 - 2025-11-11 18:28:23

2025-11-11 18:28:23 开始转换表: cjc.t3
2025-11-11 18:28:23 成功: 表 cjc.t3转换完成
2025-11-11 18:28:23 表 cjc.t3执行时间: 2025-11-11 18:28:23 - 2025-11-11 18:28:23
......
2025-11-11 18:28:33 结束时间: 2025-11-11 18:28:33
2025-11-11 18:28:33 执行结果: 成功 312 个表，失败 0 个表
2025-11-11 18:28:33 === 执行完成 ===

6.重组表

取消表压缩属性后，只对新数据生效，原数据压缩压缩状态，需要执行重组表
通过脚本进行重组。

vi /mysqldata/dbtmpfile/tmp/alert_innodb.sh

#!/bin/bash

# MySQL连接信息
MYSQL_USER="root"
MYSQL_PASSWORD="******"
MYSQL_DATABASE="cjc"

# 表名文件
TABLE_FILE="table_name.sql"

# 日志文件
LOG_FILE="/mysqldata/dbtmpfile/tmp/alert_innodb.log"

# 记录日志函数
log() {
    echo "$(date '+%Y-%m-%d %H:%M:%S') $1" >> $LOG_FILE
}

# 检查表名文件是否存在
if [ ! -f "$TABLE_FILE" ]; then
    echo "错误: 表名文件 $TABLE_FILE 不存在"
    exit 1
fi

# 记录开始时间
start_time=$(date '+%Y-%m-%d %H:%M:%S')
log "=== 开始执行表引擎转换 ==="
log "开始时间: $start_time"
log "表名文件: $TABLE_FILE"

# 逐行读取表名并执行ALTER TABLE
success_count=0
failed_count=0

while IFS= read -r table_name; do
    # 跳过空行
    if [[ -z "$table_name" ]]; then
        continue
    fi
    
    # 去除前后空白字符
    table_name=$(echo "$table_name" | xargs)
    
    log "开始转换表: $table_name"
    start_table=$(date '+%Y-%m-%d %H:%M:%S')
    
    # 执行ALTER TABLE语句
    if mysql -u$MYSQL_USER -p$MYSQL_PASSWORD $MYSQL_DATABASE -e "ALTER TABLE $table_name ENGINE=InnoDB;" 2>> $LOG_FILE; then
        log "成功: 表 $table_name 转换完成"
        ((success_count++))
    else
        log "失败: 表 $table_name 转换失败"
        ((failed_count++))
    fi
    
    end_table=$(date '+%Y-%m-%d %H:%M:%S')
    log "表 $table_name 执行时间: $start_table - $end_table"
    echo "" >> $LOG_FILE
    
done < "$TABLE_FILE"

# 记录结束时间
end_time=$(date '+%Y-%m-%d %H:%M:%S')
log "结束时间: $end_time"
log "执行结果: 成功 $success_count 个表，失败 $failed_count 个表"
log "=== 执行完成 ==="

echo "执行完成，成功 $success_count 个表，失败 $failed_count 个表"
echo "详情请查看日志: $LOG_FILE"

执行表重组:

nohup /mysqldata/dbtmpfile/tmp/alert_innodb.sh 2>err_inno.log &

检查执行日志:

root@CJC-DB-01:/mysqldata/dbtmpfile/tmp#cat alert_innodb.log |grep -v "password"
2025-11-11 18:30:39 === 开始执行表引擎转换 ===
2025-11-11 18:30:39 开始时间: 2025-11-11 18:30:39
2025-11-11 18:30:39 表名文件: table_name.sql
2025-11-11 18:30:39 开始转换表: cjc.t2
2025-11-11 18:45:49 成功: 表 cjc.t2 转换完成
2025-11-11 18:45:49 表 cjc.t2 执行时间: 2025-11-11 18:30:39 - 2025-11-11 18:45:49
.....
2025-11-11 20:16:21 结束时间: 2025-11-11 20:16:21
2025-11-11 20:16:21 执行结果: 成功 312 个表，失败 0 个表
2025-11-11 20:16:21 === 执行完成 ===

变更效果:

变更前:

   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

执行碎片整理后:

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

扩容、取消表压缩后:

   from      to extents  blocks    pct
      1       1  309523  309523   0.57
      2       3    6578   17596   0.03
      4       7   11368   56522   0.10
      8      15    5130   55333   0.10
     16      31    2239   49754   0.09
     32      63    1579   72482   0.13
     64     127    1440  133795   0.24
    128     255    2586  521127   0.95
    256     511    2232  825685   1.51
    512    1023    2743 2110568   3.86
   1024    2047     548  736744   1.35
   2048    4095     230  622375   1.14
   4096    8191      84  521528   0.95
   8192   16383      36  414925   0.76
  16384   32767      21  509656   0.93
  32768   65535      15  654667   1.20
  65536  131071       6  585718   1.07
 131072  262143       1  155918   0.29
 262144  524287       1  358666   0.66
1048576 2097151       2 3260465   5.97
2097152 4194303       1 4122288   7.55
4194304 6422528       6 38535132  70.54

监控预防:

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_t1                             | cjc        | row_format=DYNAMIC COMPRESSION="zlib"             |
|cjc_t2                             | 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区间空闲百分比；

(1)添加MySQL监控:对表压缩的监控
存在启用透明压缩属性的表时告警。

(2)添加对文件系统的监控:检查是否存在[2,3]区间的碎片

root@CJC-DB-01:/mysqldata/dbtmpfile/tmp#xfs_db -r -c freesp /dev/mapper/vg_data-lv_mysqldata|grep "2       3"
      2       3    6580   17597   0.03

(3)如果存在，继续检查[2,3]区间碎片的占比，超过70%，告3级警

root@CJC-DB-01:/mysqldata/dbtmpfile/tmp#xfs_db -r -c freesp /dev/mapper/vg_data-lv_mysqldata|grep "2       3"|awk -F " " '{print $5}'
0.03

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