MySQL透明頁壓縮(TPC)批量取消與磁盤碎片優(yōu)化實戰(zhàn)案例

更新時間：2026年07月22日 09:19:42 作者：Seal^_^

這篇文章主要介紹了MySQL透明頁壓縮(TPC)批量取消與磁盤碎片優(yōu)化實戰(zhàn)的相關(guān)資料,本文通過真實案例,教你安全批量取消TPC并解決磁盤碎片,文中通過代碼介紹的非常詳細,需要的朋友可以參考下

引言：透明頁壓縮帶來的挑戰(zhàn)

MySQL的透明頁壓縮（Transparent Page Compression，簡稱TPC）是InnoDB提供的一種數(shù)據(jù)壓縮技術(shù)，它可以在頁面級別對數(shù)據(jù)進行壓縮，從而減少磁盤空間占用。然而，在生產(chǎn)環(huán)境中，我們經(jīng)常發(fā)現(xiàn)TPC會帶來一些副作用：

磁盤碎片嚴重：頻繁的壓縮和解壓操作導致文件系統(tǒng)碎片增加
性能波動：壓縮/解壓消耗CPU資源，影響查詢性能
空間回收困難：即使刪除數(shù)據(jù)，壓縮頁可能無法完全釋放空間

本文將通過一個實際案例，詳細介紹如何安全、高效地批量取消TPC，并優(yōu)化由此產(chǎn)生的磁盤碎片問題。

一、透明頁壓縮原理與問題分析

1.1 TPC工作原理

-- 創(chuàng)建使用TPC的表
CREATE TABLE tpc_table (
    id INT PRIMARY KEY,
    data VARCHAR(2000)
) COMPRESSION='zlib'  -- 啟用透明頁壓縮
  KEY_BLOCK_SIZE=8;    -- 指定壓縮頁大小

TPC在寫入時壓縮數(shù)據(jù)頁，讀取時解壓。每個壓縮頁都附帶一個"洞"（hole），通過fallocate()系統(tǒng)調(diào)用創(chuàng)建，實現(xiàn)空間節(jié)省。

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效率下降
備份恢復時間增長
磁盤空間虛高

二、實戰(zhàn)案例：批量取消TPC壓縮

2.1 環(huán)境準備與風險評估

案例背景：

MySQL 8.0.28，InnoDB引擎
數(shù)據(jù)庫大?。?TB，其中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狀態(tài)
SHOW ENGINE INNODB STATUS\G

# 3. 監(jiān)控磁盤空間
df -h /var/lib/mysql
ls -lh /var/lib/mysql/*.ibd | sort -k5 -h -r | head -20

2.2 批量取消TPC方案設計

方案選擇對比：

方法	優(yōu)點	缺點	適用場景
ALTER TABLE … COMPRESSION=‘None’	在線操作，業(yè)務影響小	慢，產(chǎn)生大量redo log	小型表，業(yè)務低峰期
邏輯導出導入(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;

-- 生成進度監(jiān)控腳本
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平滑執(zhí)行

#!/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. 創(chuàng)建目標表結(jié)構(gòu)（無壓縮）
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工具復制表空間文件
# 在操作系統(tǒng)層面執(zhí)行
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. 驗證數(shù)據(jù)一致性
CHECK TABLE orders_new EXTENDED;

-- 6. 原子切換（在維護窗口進行）
RENAME TABLE orders TO orders_old, orders_new TO orders;

-- 7. 清理舊表（確認業(yè)務正常后）
DROP TABLE orders_old;

三、磁盤碎片優(yōu)化與空間回收

3.1 碎片檢測與評估

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

# MySQL內(nèi)部碎片統(tǒng)計
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 優(yōu)化策略組合拳

策略1：OPTIMIZE TABLE（需要停機時間）

-- 針對碎片率超過30%的表
SET SESSION old_alter_table=1;  -- 使用舊算法，減少內(nèi)存使用

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;

-- 監(jiān)控整理進度
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):
        """估算操作時間（經(jīng)驗公式）"""
        # 導出導入：約 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)")
                    
                    # 根據(jù)大小選擇策略
                    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):
        """優(yōu)化碎片化嚴重的表"""
        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"優(yōu)化碎片表 {schema}.{table} (碎片率: {frag_ratio}%)")
                
                # 使用OPTIMIZE TABLE
                optimize_sql = f"OPTIMIZE TABLE `{schema}`.`{table}`"
                cursor.execute(optimize_sql)
                result = cursor.fetchone()
                
                self.logger.info(f"優(yōu)化結(jié)果: {result}")

if __name__ == "__main__":
    optimizer = MySQLTPCOptimizer(
        host="localhost",
        user="admin",
        password="your_password"
    )
    
    # 執(zhí)行TPC取消
    optimizer.batch_remove_tpc(batch_size=3)
    
    # 執(zhí)行碎片整理
    optimizer.optimize_fragmentation(frag_threshold=25)

四、監(jiān)控與驗證

4.1 監(jiān)控指標設計

-- 監(jiān)控視圖：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;

-- 磁盤空間監(jiān)控
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性能監(jiān)控
SHOW GLOBAL STATUS LIKE 'Innodb_data_reads';
SHOW GLOBAL STATUS LIKE 'Innodb_data_writes';
SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_reads';

五、經(jīng)驗總結(jié)與最佳實踐

5.1 關(guān)鍵經(jīng)驗總結(jié)

分批處理：不要一次性處理所有表，按大小分批次
監(jiān)控先行：執(zhí)行前建立完整監(jiān)控基線
回滾預案：隨時準備停止或回滾
業(yè)務影響評估：與業(yè)務團隊充分溝通時間窗口

5.2 TPC使用建議

適合使用TPC的場景：

只讀或讀多寫少的表
SSD存儲成本敏感的環(huán)境
數(shù)據(jù)歸檔表

不適合使用TPC的場景：

高頻更新的OLTP表
內(nèi)存充足，追求極致性能
已經(jīng)使用其他壓縮方案（如InnoDB表壓縮）

5.3 長期維護策略

-- 定期碎片檢查任務
CREATE EVENT check_fragmentation
ON SCHEDULE EVERY 1 WEEK
STARTS CURRENT_TIMESTAMP
DO
BEGIN
    -- 記錄碎片狀態(tài)
    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;
    
    -- 自動優(yōu)化高碎片表
    CALL auto_optimize_fragmented_tables(30); -- 30%閾值
END;

六、附錄：常用命令速查

# 1. 檢查表壓縮狀態(tài)
mysql -e "SHOW TABLE STATUS WHERE Comment LIKE '%Compressed%'\G"

# 2. 檢查文件系統(tǒng)碎片
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. 監(jiān)控ALTER進度（MySQL 8.0+）
SELECT * FROM performance_schema.events_stages_current 
WHERE EVENT_NAME LIKE 'stage/innodb/alter%';

總結(jié)

到此這篇關(guān)于MySQL透明頁壓縮(TPC)批量取消與磁盤碎片優(yōu)化實戰(zhàn)案例的文章就介紹到這了,更多相關(guān)MySQL透明頁壓縮批量取消內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

您可能感興趣的文章:

最新国产好看的视频,伊人天堂AV在线,国产Aaaaaa视频,蜜臀视频在线观看一区,人妻av色图,密臀久久久精品影片,青青视频免费观看毛片,久草在线观看视,国产三级精品色情在线

MySQL透明頁壓縮(TPC)批量取消與磁盤碎片優(yōu)化實戰(zhàn)案例

目錄

引言：透明頁壓縮帶來的挑戰(zhàn)

一、透明頁壓縮原理與問題分析

1.1 TPC工作原理

1.2 常見問題癥狀

二、實戰(zhàn)案例：批量取消TPC壓縮

2.1 環(huán)境準備與風險評估

2.2 批量取消TPC方案設計

2.3 分步實施：中小型表在線取消

2.4 大型表的特殊處理方案

三、磁盤碎片優(yōu)化與空間回收

3.1 碎片檢測與評估

3.2 優(yōu)化策略組合拳

3.3 自動化維護腳本

四、監(jiān)控與驗證

4.1 監(jiān)控指標設計

4.2 性能對比測試

五、經(jīng)驗總結(jié)與最佳實踐

5.1 關(guān)鍵經(jīng)驗總結(jié)

5.2 TPC使用建議

5.3 長期維護策略

六、附錄：常用命令速查

總結(jié)

相關(guān)文章

最新評論

大家感興趣的內(nèi)容

最近更新的內(nèi)容

常用在線小工具

最新国产好看的视频,伊人天堂AV在线,国产Aaaaaa视频,蜜臀视频在线观看一区,人妻av色图,密臀久久久精品影片,青青视频免费观看毛片,久草在线观看视,国产三级精品色情在线

MySQL透明頁壓縮(TPC)批量取消與磁盤碎片優(yōu)化實戰(zhàn)案例

目錄

引言：透明頁壓縮帶來的挑戰(zhàn)

一、透明頁壓縮原理與問題分析

1.1 TPC工作原理

1.2 常見問題癥狀

二、實戰(zhàn)案例：批量取消TPC壓縮

2.1 環(huán)境準備與風險評估

2.2 批量取消TPC方案設計

2.3 分步實施：中小型表在線取消

2.4 大型表的特殊處理方案

三、磁盤碎片優(yōu)化與空間回收

3.1 碎片檢測與評估

3.2 優(yōu)化策略組合拳

3.3 自動化維護腳本

四、監(jiān)控與驗證

4.1 監(jiān)控指標設計

4.2 性能對比測試

五、經(jīng)驗總結(jié)與最佳實踐

5.1 關(guān)鍵經(jīng)驗總結(jié)

5.2 TPC使用建議

5.3 長期維護策略

六、附錄：常用命令速查

總結(jié)

相關(guān)文章

最新評論

大家感興趣的內(nèi)容

最近更新的內(nèi)容

常用在線小工具

一、透明頁壓縮原理與問題分析

二、實戰(zhàn)案例：批量取消TPC壓縮

三、磁盤碎片優(yōu)化與空間回收

四、監(jiān)控與驗證

五、經(jīng)驗總結(jié)與最佳實踐