排查步驟

監控日志增長情況

-- 查看InnoDB狀態
SHOW ENGINE INNODB STATUS;-- 查看redo log配置和使用情況
SHOW VARIABLES LIKE 'innodb_log_file%';
SHOW VARIABLES LIKE 'innodb_log_buffer_size';-- 查看undo log信息
SHOW VARIABLES LIKE 'innodb_undo%';

檢查長時間運行的事務

-- 查看當前運行的事務
SELECT * FROM information_schema.INNODB_TRX;-- 查看長時間運行的事務
SELECT * FROM information_schema.INNODB_TRX 
WHERE TIME_TO_SEC(TIMEDIFF(NOW(), trx_started)) > 60;

檢查鎖等待情況

-- 查看鎖等待
SELECT * FROM sys.innodb_lock_waits;

檢查系統負載

-- 查看當前連接數
SHOW STATUS LIKE 'Threads_connected';-- 查看活躍連接數
SHOW STATUS LIKE 'Threads_running';

1. SHOW ENGINE INNODB STATUS;
   用途：查看InnoDB存儲引擎的詳細運行狀態信息，包括事務、鎖、緩沖池、日志等關鍵指標。 輸出內容：
   事務信息：活躍事務數量、鎖等待情況
   緩沖池狀態：命中率、臟頁比例
   日志信息：redo log和undo log的寫入狀態
   鎖信息：行鎖/表鎖的持有和等待情況
   線程信息：InnoDB內部線程狀態
2. SHOW VARIABLES LIKE ‘innodb_log_file%’;
   用途：查看redo log（重做日志）的文件配置。 關鍵參數：
   innodb_log_file_size：單個redo log文件大小（默認48M）
   innodb_log_files_in_group：redo log文件數量（默認2個）
3. SHOW VARIABLES LIKE ‘innodb_log_buffer_size’;
   用途：查看redo log緩沖區大小配置。 說明：
   該參數控制內存中redo log緩沖區的大小（默認16M）
   緩沖區滿時會自動刷新到磁盤日志文件
   高寫入場景可適當調大（如32M或64M）
4. SHOW VARIABLES LIKE ‘innodb_undo%’;
   用途：查看undo log（回滾日志）的相關配置。 常見參數：innodb_undo_directory：undo log存儲路徑
   innodb_undo_logs：回滾段數量（默認128）
   innodb_undo_tablespaces：獨立undo表空間數量
   innodb_undo_retention：undo log保留時間（秒）

常見原因

• 長時間運行的事務：未提交的事務會阻止undo log的清理
• 大事務：一次性修改大量數據
• 高并發寫操作：導致redo log快速增長
• 配置不當：日志文件大小或數量配置不合理
• 復制延遲：在復制環境中可能導致日志堆積

解決方案

優化事務處理

• 避免長時間運行的事務，盡快提交或回滾
• 將大事務拆分為小事務
• 設置事務超時時間

調整日志配置

-- 增加redo log文件大小和數量（需要重啟）
SET GLOBAL innodb_log_file_size = 256M;  -- 通常設置為1-2小時的寫入量
SET GLOBAL innodb_log_files_in_group = 4;  -- 通常2-4個-- 調整undo log設置
SET GLOBAL innodb_undo_tablespaces = 4;  -- 多個undo表空間
SET GLOBAL innodb_max_undo_log_size = 1G;  -- 控制undo log最大大小

定期維護

-- 定期清理歷史事務
SET GLOBAL innodb_purge_threads = 4;  -- 增加purge線程數-- 監控并手動觸發purge
SHOW VARIABLES LIKE 'innodb_purge_batch_size';

硬件和系統優化

• 使用更快的存儲設備（如SSD）存放日志文件
• 確保有足夠的I/O帶寬
• 考慮增加服務器內存

監控和預警

設置監控系統，關注以下指標：

• Undo log空間使用率
• Redo log生成速度
• 長時間運行事務數量
• 鎖等待時間

緊急處理

如果日志已經占滿磁盤空間：

• 臨時增加磁盤空間
• 安全地重啟MySQL以重建日志文件（確保數據已同步到磁盤）
• 對于undo log問題，可以嘗試終止長時間運行的事務