MySQL透明页压缩(TPC)批量取消与磁盘碎片优化实战案例_Mysql

引言：透明页压缩带来的挑战

mysql的透明页压缩（transparent page compression，简称tpc）是innodb提供的一种数据压缩技术，它可以在页面级别对数据进行压缩，从而减少磁盘空间占用。然而，在生产环境中，我们经常发现tpc会带来一些副作用：

磁盘碎片严重：频繁的压缩和解压操作导致文件系统碎片增加
性能波动：压缩/解压消耗cpu资源，影响查询性能
空间回收困难：即使删除数据，压缩页可能无法完全释放空间

本文将通过一个实际案例，详细介绍如何安全、高效地批量取消tpc，并优化由此产生的磁盘碎片问题。

一、透明页压缩原理与问题分析

1.1 tpc工作原理

-- 创建使用tpc的表
create table tpc_table (
    id int primary key,
    data varchar(2000)
) compression='zlib'  -- 启用透明页压缩
  key_block_size=8;    -- 指定压缩页大小

tpc在写入时压缩数据页，读取时解压。每个压缩页都附带一个"洞"（hole），通过fallocate()系统调用创建，实现空间节省。

1.2 常见问题症状

-- 检查表空间碎片情况
select 
    table_schema,
    table_name,
    data_length,
    index_length,
    data_free,
    round((data_free / (data_length + index_length)) * 100, 2) as fragmentation_percent
from information_schema.tables 
where data_free > 1024 * 1024 * 100  -- 大于100mb的碎片
order by data_free desc
limit 10;

高碎片化会导致：

磁盘i/o效率下降
备份恢复时间增长
磁盘空间虚高

二、实战案例：批量取消tpc压缩

2.1 环境准备与风险评估

案例背景：

mysql 8.0.28，innodb引擎
数据库大小：2tb，其中1.5tb使用tpc
磁盘：nvme ssd，但碎片率超过40%

风险评估清单：

# 1. 检查当前tpc使用情况
select 
    count(*) as tpc_tables,
    sum(data_length/1024/1024/1024) as tpc_size_gb
from information_schema.tables 
where create_options like '%compression=%';

# 2. 检查innodb状态
show engine innodb status\g

# 3. 监控磁盘空间
df -h /var/lib/mysql
ls -lh /var/lib/mysql/*.ibd | sort -k5 -h -r | head -20

2.2 批量取消tpc方案设计

方案选择对比：

方法	优点	缺点	适用场景
alter table … compression=‘none’	在线操作，业务影响小	慢，产生大量redo log	小型表，业务低峰期
逻辑导出导入(mysqldump)	彻底消除碎片	需要停机时间	大型表，有维护窗口
表空间传输(transportable tablespaces)	速度快，锁时间短	需要percona工具	超大表迁移

2.3 分步实施：中小型表在线取消

步骤1：生成批量取消脚本

-- 生成取消压缩的sql语句
select 
    concat(
        'alter table `', table_schema, '`.`', table_name, '` ',
        'compression="none", ',
        'key_block_size=0;'
    ) as alter_sql,
    round((data_length + index_length)/1024/1024, 2) as size_mb
from information_schema.tables 
where create_options like '%compression=%'
    and (data_length + index_length) < 1024 * 1024 * 1024  -- 小于1gb的表
order by size_mb asc;

-- 生成进度监控脚本
select 
    table_schema,
    table_name,
    'select "正在处理: ' || table_schema || '.' || table_name || '" as status; ' ||
    'alter table `' || table_schema || '`.`' || table_name || '` compression="none", key_block_size=0;' ||
    'optimize table `' || table_schema || '`.`' || table_name || '`;' as full_process
from information_schema.tables 
where create_options like '%compression=%';

步骤2：使用pt-online-schema-change平滑执行

#!/bin/bash
# 批量取消tpc的自动化脚本

db_host="localhost"
db_user="admin"
db_pass="your_password"
chunk_size="100k"
max_load="threads_running=50"

# 获取所有tpc表
mysql -h${db_host} -u${db_user} -p${db_pass} -n -e "
select concat(table_schema, '.', table_name) 
from information_schema.tables 
where create_options like '%compression=%'
    and table_schema not in ('mysql', 'information_schema', 'performance_schema')
" > tpc_tables.txt

# 逐表处理
while read table; do
    echo "处理表: $table"
    
    pt-online-schema-change \
        --host=${db_host} \
        --user=${db_user} \
        --password=${db_pass} \
        --alter="compression='none', key_block_size=0" \
        --chunk-size=${chunk_size} \
        --max-load=${max_load} \
        --execute \
        d=${table%.*},t=${table#*.}
    
    # 记录日志
    echo "$(date): 已处理 $table" >> tpc_remove.log
    
    # 暂停60秒，避免对主库影响过大
    sleep 60
done < tpc_tables.txt

2.4 大型表的特殊处理方案

对于超过100gb的大型表，我们采用表空间传输方案：

-- 1. 创建目标表结构（无压缩）
create table orders_new like orders;
alter table orders_new compression='none', key_block_size=0;

-- 2. 丢弃目标表空间
alter table orders_new discard tablespace;

-- 3. 使用percona工具复制表空间文件
# 在操作系统层面执行
sudo innobackupex --compress --export /backup/orders/
sudo cp /backup/orders/orders.ibd /var/lib/mysql/mydb/orders_new.ibd
sudo cp /backup/orders/orders.cfg /var/lib/mysql/mydb/orders_new.cfg

-- 4. 导入表空间
alter table orders_new import tablespace;

-- 5. 验证数据一致性
check table orders_new extended;

-- 6. 原子切换（在维护窗口进行）
rename table orders to orders_old, orders_new to orders;

-- 7. 清理旧表（确认业务正常后）
drop table orders_old;

三、磁盘碎片优化与空间回收

3.1 碎片检测与评估

# 使用filefrag检查物理碎片
sudo filefrag /var/lib/mysql/mydb/*.ibd | grep "extents found"

# mysql内部碎片统计
select 
    table_name,
    engine,
    table_rows,
    data_length,
    index_length,
    data_free,
    round((data_free / (data_length + index_length + data_free)) * 100, 2) as frag_ratio
from information_schema.tables 
where table_schema = 'mydb'
    and data_free > 1024 * 1024 * 10  -- 10mb以上碎片
order by frag_ratio desc;

3.2 优化策略组合拳

策略1：optimize table（需要停机时间）

-- 针对碎片率超过30%的表
set session old_alter_table=1;  -- 使用旧算法，减少内存使用

select 
    concat('optimize table `', table_schema, '`.`', table_name, '`;') as optimize_cmd
from information_schema.tables 
where table_schema = 'mydb'
    and round((data_free / (data_length + index_length + data_free)) * 100, 2) > 30
    and table_rows > 1000000;

策略2：分批重建索引（在线操作）

-- 针对索引碎片
select 
    table_schema,
    table_name,
    index_name,
    round(stat_value * @@innodb_page_size / 1024 / 1024, 2) as index_size_mb
from mysql.innodb_index_stats 
where stat_name = 'size'
    and database_name = 'mydb'
order by stat_value desc
limit 20;

-- 分批重建大索引
alter table large_table drop key idx_large, add key idx_large(column1, column2);
-- 使用algorithm=inplace, lock=none在线重建

策略3：使用innodb_defragment在线整理

-- 启用innodb碎片整理
set global innodb_defragment=1;
set global innodb_defragment_n_pages=7;
set global innodb_defragment_stats_accuracy=0;

-- 监控整理进度
select * from information_schema.innodb_defrag;

3.3 自动化维护脚本

#!/usr/bin/env python3
"""
mysql tpc取消与碎片整理自动化脚本
"""

import pymysql
import subprocess
import logging
from datetime import datetime

class mysqltpcoptimizer:
    def __init__(self, host, user, password):
        self.conn = pymysql.connect(
            host=host,
            user=user,
            password=password,
            charset='utf8mb4'
        )
        self.logger = self.setup_logger()
        
    def setup_logger(self):
        logging.basicconfig(
            level=logging.info,
            format='%(asctime)s - %(levelname)s - %(message)s',
            handlers=[
                logging.filehandler('mysql_tpc_optimization.log'),
                logging.streamhandler()
            ]
        )
        return logging.getlogger(__name__)
    
    def get_tpc_tables(self, min_size_mb=100):
        """获取使用tpc的表"""
        sql = """
        select 
            table_schema,
            table_name,
            round((data_length + index_length) / 1024 / 1024, 2) as size_mb,
            create_options
        from information_schema.tables 
        where create_options like '%compression=%'
            and (data_length + index_length) > %s * 1024 * 1024
        order by size_mb desc
        """
        
        with self.conn.cursor() as cursor:
            cursor.execute(sql, (min_size_mb,))
            return cursor.fetchall()
    
    def estimate_operation_time(self, table_size_mb):
        """估算操作时间（经验公式）"""
        # 导出导入：约 50 mb/s
        # 在线alter：约 20 mb/s
        export_time = table_size_mb / 50
        alter_time = table_size_mb / 20
        
        return {
            'export_import': export_time * 2,  # 导出+导入
            'online_alter': alter_time,
            'recommended': 'export_import' if table_size_mb > 10240 else 'online_alter'
        }
    
    def batch_remove_tpc(self, batch_size=5):
        """批量取消tpc"""
        tables = self.get_tpc_tables()
        
        for i in range(0, len(tables), batch_size):
            batch = tables[i:i+batch_size]
            self.logger.info(f"处理批次 {i//batch_size + 1}: {len(batch)}张表")
            
            for schema, table, size_mb, _ in batch:
                try:
                    self.logger.info(f"开始处理 {schema}.{table} ({size_mb}mb)")
                    
                    # 根据大小选择策略
                    if size_mb > 10240:  # 大于10gb
                        self.handle_large_table(schema, table)
                    else:
                        self.handle_medium_table(schema, table)
                        
                    self.logger.info(f"完成处理 {schema}.{table}")
                    
                except exception as e:
                    self.logger.error(f"处理 {schema}.{table} 失败: {str(e)}")
                    continue
    
    def optimize_fragmentation(self, frag_threshold=20):
        """优化碎片化严重的表"""
        sql = """
        select 
            table_schema,
            table_name,
            round((data_free / (data_length + index_length + data_free)) * 100, 2) as frag_ratio
        from information_schema.tables 
        where table_schema not in ('mysql', 'sys', 'information_schema')
            and data_free > 50 * 1024 * 1024  -- 大于50mb
            and round((data_free / (data_length + index_length + data_free)) * 100, 2) > %s
        order by frag_ratio desc
        """
        
        with self.conn.cursor() as cursor:
            cursor.execute(sql, (frag_threshold,))
            fragmented_tables = cursor.fetchall()
            
            for schema, table, frag_ratio in fragmented_tables:
                self.logger.info(f"优化碎片表 {schema}.{table} (碎片率: {frag_ratio}%)")
                
                # 使用optimize table
                optimize_sql = f"optimize table `{schema}`.`{table}`"
                cursor.execute(optimize_sql)
                result = cursor.fetchone()
                
                self.logger.info(f"优化结果: {result}")

if __name__ == "__main__":
    optimizer = mysqltpcoptimizer(
        host="localhost",
        user="admin",
        password="your_password"
    )
    
    # 执行tpc取消
    optimizer.batch_remove_tpc(batch_size=3)
    
    # 执行碎片整理
    optimizer.optimize_fragmentation(frag_threshold=25)

四、监控与验证

4.1 监控指标设计

-- 监控视图：tpc取消进度
create view tpc_removal_progress as
select 
    'before' as period,
    count(*) as table_count,
    sum(data_length + index_length) as total_size
from information_schema.tables 
where create_options like '%compression=%'
union all
select 
    'after',
    count(*),
    sum(data_length + index_length)
from information_schema.tables 
where create_options not like '%compression=%' 
    or create_options is null;

-- 磁盘空间监控
create view disk_usage_trend as
select 
    date(create_time) as date,
    sum(case when create_options like '%compression=%' then 1 else 0 end) as tpc_tables,
    sum(case when create_options like '%compression=%' then data_length + index_length else 0 end) / 1024 / 1024 / 1024 as tpc_size_gb,
    avg(data_free / (data_length + index_length + data_free)) * 100 as avg_frag_percent
from information_schema.tables 
    cross join (select now() as create_time) as t
where table_schema = 'mydb'
group by date(create_time);

4.2 性能对比测试

-- 测试查询性能变化
select 
    'before_optimization' as phase,
    avg(query_time) as avg_query_time,
    max(query_time) as max_query_time,
    count(*) as query_count
from mysql.slow_log 
where db = 'mydb'
    and start_time < '2024-01-15'
union all
select 
    'after_optimization',
    avg(query_time),
    max(query_time),
    count(*)
from mysql.slow_log 
where db = 'mydb'
    and start_time >= '2024-01-15';

-- i/o性能监控
show global status like 'innodb_data_reads';
show global status like 'innodb_data_writes';
show global status like 'innodb_buffer_pool_reads';

五、经验总结与最佳实践

5.1 关键经验总结

分批处理：不要一次性处理所有表，按大小分批次
监控先行：执行前建立完整监控基线
回滚预案：随时准备停止或回滚
业务影响评估：与业务团队充分沟通时间窗口

5.2 tpc使用建议

适合使用tpc的场景：

只读或读多写少的表
ssd存储成本敏感的环境
数据归档表

不适合使用tpc的场景：

高频更新的oltp表
内存充足，追求极致性能
已经使用其他压缩方案（如innodb表压缩）

5.3 长期维护策略

-- 定期碎片检查任务
create event check_fragmentation
on schedule every 1 week
starts current_timestamp
do
begin
    -- 记录碎片状态
    insert into frag_monitor_history
    select now(), table_schema, table_name,
           round((data_free / (data_length + index_length + data_free)) * 100, 2)
    from information_schema.tables 
    where table_schema not in ('mysql', 'sys')
        and data_free > 100 * 1024 * 1024;
    
    -- 自动优化高碎片表
    call auto_optimize_fragmented_tables(30); -- 30%阈值
end;

六、附录：常用命令速查

# 1. 检查表压缩状态
mysql -e "show table status where comment like '%compressed%'\g"

# 2. 检查文件系统碎片
sudo filefrag -v /var/lib/mysql/dbname/*.ibd | grep "extent"

# 3. 快速估算表大小
select 
    table_name as `table`,
    round(((data_length + index_length) / 1024 / 1024), 2) as `size (mb)`
from information_schema.tables
where table_schema = "your_database"
order by (data_length + index_length) desc;

# 4. 监控alter进度（mysql 8.0+）
select * from performance_schema.events_stages_current 
where event_name like 'stage/innodb/alter%';