MySQLDump導致數據頁緩存失效，如何避免失效與業務響應下降？

原創精選

作者：吳守陽 2024-07-17 08:00:00

數據庫

運營反饋服務的某個頁面響應偶爾特別慢，響應時間大概是16S，重新刷新或關閉頁面再次打開秒級響應。同事詢問：這是什么情況？

作者 | 吳守陽

審校 | 重樓

問題背景

運營反饋服務的某個頁面響應偶爾特別慢，響應時間大概是16S，重新刷新或關閉頁面再次打開秒級響應。同事詢問：這是什么情況？

問題排查

1）查看慢SQL日志，該語句掃描264萬數據，執行耗時17S

# Query_time: 17.499659 Lock_time: 0.000091 Rows_sent: 1 Rows_examined: 2646949
SELECT
 IFNULL( SUM( deducted_total_amount ), 0 ) deductedTotalAmount,
 IFNULL( SUM( deducted_total_quantity ), 0 ) deductedTotalQuantity,
 IFNULL( SUM( recharge_cash_amount ), 0 ) rechargeCashAmount,
 IFNULL( SUM( recharge_cash_quantity ), 0 ) rechargeCashQuantity,
 IFNULL( SUM( recharge_total_amount ), 0 ) rechargeTotalAmount,
 IFNULL( SUM( recharge_total_quantity ), 0 ) rechargeTotalQuantity,
 IFNULL( SUM( refund_amount ), 0 ) refundAmount,
 IFNULL( SUM( refund_quantity ), 0 ) refundQuantity,
 IFNULL( SUM( should_deducted_amount ), 0 ) shouldDeductedAmount 
FROM amortized_consumptiont 
WHERE DAY >= '2024-06-02' AND DAY <= '2024-07-02';

2）explain顯示使用到索引了，只會掃描32萬數據

3）語句執行耗時0.34s

4)執行ANALYZE TABLE（未起到作用）

ANALYZE TABLE amortized_consumptiont ;

5)梳理慢SQL日志

備份慢SQL記錄：

# Time: 2024-05-23T16:04:11.489126Z //加8小時
# User@Host: bor] @ [********] Id: 1758371
# Query_time: 25.592015 Lock_time: 0.000038 Rows_sent: 2442465 Rows_examined: 2442465
SET timestamp=1716483825;
SELECT /*!40001 SQL_NO_CACHE */ * FROM `amortized_consumptiont `;

慢SQL：
# Time: 2024-05-24T00:46:41.584582Z //加8小時
# User@Host: bossuser[bossuser] @ [10.28.28.109] Id: 1762223
# Query_time: 9.129744 Lock_time: 0.000084 Rows_sent: 1 Rows_examined: 2442465
SELECT
 IFNULL( SUM( deducted_total_amount ), 0 ) deductedTotalAmount,
 IFNULL( SUM( deducted_total_quantity ), 0 ) deductedTotalQuantity,
 IFNULL( SUM( recharge_cash_amount ), 0 ) rechargeCashAmount,
 IFNULL( SUM( recharge_cash_quantity ), 0 ) rechargeCashQuantity,
 IFNULL( SUM( recharge_total_amount ), 0 ) rechargeTotalAmount,
 IFNULL( SUM( recharge_total_quantity ), 0 ) rechargeTotalQuantity,
 IFNULL( SUM( refund_amount ), 0 ) refundAmount,
 IFNULL( SUM( refund_quantity ), 0 ) refundQuantity,
 IFNULL( SUM( should_deducted_amount ), 0 ) shouldDeductedAmount 
FROM amortized_consumptiont 
WHERE DAY >= '2024-04-23' AND DAY <= '2024-05-23';

通過對比最近幾個月的慢SQL記錄，每天的凌晨開始全庫備份，轉天早上9點開始服務有人使用，就會觸發慢SQL。初步懷疑是備份導致InnoDB緩沖池的數據頁緩存失效，部分數據頁可能會從內存中移除，導致首次執行查詢時需要重新從磁盤加載數據頁到內存，造成查詢較慢。

問題復現

1）手動執行數據庫備份

/usr/bin/mysqldump -h $HOST -u user -P$PORT -p******R52 --single-transaction --no-tablespaces --hex-blob ${DB4}| gzip > $DIR/${DB4}_${DATE}.sql.gz

2）觀察慢SQL記錄

##備份輸出的慢SQl
# Time: 2024-07-03T02:31:32.154554Z
# User@Host:******** Id: 2274303
# Query_time: 29.449576 Lock_time: 0.000041 Rows_sent: 2646949 Rows_examined: 2646949
SET timestamp=1719973862;
SELECT /*!40001 SQL_NO_CACHE */ * FROM `amortized_consumption`;
# Time: 2024-07-03T02:35:06.435063Z
# User@Host:******** Id: 2274303
# Query_time: 1.433213 Lock_time: 0.000031 Rows_sent: 198468 Rows_examined: 198468
SET timestamp=1719974105;
SELECT /*!40001 SQL_NO_CACHE */ * FROM `oper_log`;

手動查詢SQL語句后記錄的慢SQL

# Query_time: 17.499659  Lock_time: 0.000091 Rows_sent: 1  Rows_examined: 2646949
SET timestamp=1719974485;
SELECT
	IFNULL( SUM( deducted_total_amount ), 0 ) deductedTotalAmount,
	IFNULL( SUM( deducted_total_quantity ), 0 ) deductedTotalQuantity,
	IFNULL( SUM( recharge_cash_amount ), 0 ) rechargeCashAmount,
	IFNULL( SUM( recharge_cash_quantity ), 0 ) rechargeCashQuantity,
	IFNULL( SUM( recharge_total_amount ), 0 ) rechargeTotalAmount,
	IFNULL( SUM( recharge_total_quantity ), 0 ) rechargeTotalQuantity,
	IFNULL( SUM( refund_amount ), 0 ) refundAmount,
	IFNULL( SUM( refund_quantity ), 0 ) refundQuantity,
	IFNULL( SUM( should_deducted_amount ), 0 ) shouldDeductedAmount 
FROM amortized_consumption 
WHERE DAY >= '2024-06-02'  AND DAY <= '2024-07-02';

問題復現了，備份完之后手動執行語句，在慢SQL日志里記錄了該語句，掃描264萬數據，執行耗時17S，問題原因是備份造成。

解決方案

在MySQL 8.0.23版本中，使用 mysqldump 進行全庫備份后，執行某些查詢可能會出現首次執行較慢的情況，這可能與InnoDB存儲引擎的數據頁緩存機制有關。讓我們詳細解釋可能的原因和解決方法：

數據頁緩存失效：MySQL的InnoDB存儲引擎使用數據頁緩存來存儲最近訪問的數據頁，以提高查詢性能。如果備份過程中有大量的表數據被修改或者重新加載，部分數據頁可能會從內存中移除，導致首次執行查詢時需要重新從磁盤加載數據頁到內存，造成查詢較慢。

解決方法：

查詢優化：確保查詢語句本身是優化過的，包括使用合適的索引和查詢條件，以盡量減少掃描的數據量。
數據頁預熱：考慮在備份后的低負載時間內執行一些預熱操作，例如執行一些簡單的查詢，以幫助MySQL重新加載常用的數據頁到內存中。
服務器資源優化：確保MySQL服務器的配置和資源充足，例如適當分配內存給InnoDB緩沖池，以提高數據頁緩存的效率。
定期優化表：定期執行OPTIMIZE TABLE 或者ANALYZE TABLE 可以幫助MySQL優化表的存儲布局和統計信息，進而改善查詢性能。
備份策略調整：盡量在數據庫負載較低的時候進行備份操作，以減少備份對業務查詢性能的影響。

考慮使用--single-transaction 參數來執行mysqldump，以避免對表進行全局鎖定，從而減少備份操作對數據頁緩存的影響。

最終采用方案

數據頁預熱方案，即每次數據備份后，手動查詢相關SQL語句，將熱數據寫入到InnoDB緩沖池。由于我們該套環境業務量較小，還能滿足日常業務需求，就不采取配置擴容，增加InnoDB緩沖池。

腳本如下：

綜上所述，首次執行查詢較慢可能與MySQL InnoDB存儲引擎的數據頁緩存機制有關，備份操作可能導致部分數據頁從內存中移除，需要重新加載。通過優化查詢、預熱數據頁、優化服務器配置和備份策略，可以減少這種情況的發生，提升查詢性能的穩定性和可預測性。

作者介紹

吳守陽，51CTO社區編輯，擁有8年DBA工作經驗，熟練管理MySQL、Redis、MongoDB等開源數據庫。精通性能優化、備份恢復和高可用性架構設計。善于故障排除和自動化運維，保障系統穩定可靠。具備良好的團隊合作和溝通能力，致力于為企業提供高效可靠的數據庫解決方案。

責任編輯：華軒來源： 51CTO

MySQLDump 緩存

成人免费xxxxx在线视频软件_久久精品久久久_亚洲国产精品久久久_天天色天天色_亚洲人成一区_欧美一级欧美三级在线观看