作者 | 吳守陽(yáng)
審校 | 重樓
問(wèn)題背景
運(yùn)營(yíng)反饋服務(wù)的某個(gè)頁(yè)面響應(yīng)偶爾特別慢，響應(yīng)時(shí)間大概是16S，重新刷新或關(guān)閉頁(yè)面再次打開(kāi)秒級(jí)響應(yīng)。同事詢問(wèn)：這是什么情況？
問(wèn)題排查
1）查看慢SQL日志，該語(yǔ)句掃描264萬(wàn)數(shù)據(jù)，執(zhí)行耗時(shí)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顯示使用到索引了，只會(huì)掃描32萬(wàn)數(shù)據(jù)
3）語(yǔ)句執(zhí)行耗時(shí)0.34s
4)執(zhí)行ANALYZE TABLE（未起到作用）
ANALYZE TABLE amortized_consumptiont ;5)梳理慢SQL日志
備份慢SQL記錄：
# Time: 2024-05-23T16:04:11.489126Z //加8小時(shí)
# 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小時(shí)
# 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';通過(guò)對(duì)比最近幾個(gè)月的慢SQL記錄，每天的凌晨開(kāi)始全庫(kù)備份，轉(zhuǎn)天早上9點(diǎn)開(kāi)始服務(wù)有人使用，就會(huì)觸發(fā)慢SQL。初步懷疑是備份導(dǎo)致InnoDB緩沖池的數(shù)據(jù)頁(yè)緩存失效，部分?jǐn)?shù)據(jù)頁(yè)可能會(huì)從內(nèi)存中移除，導(dǎo)致首次執(zhí)行查詢時(shí)需要重新從磁盤加載數(shù)據(jù)頁(yè)到內(nèi)存，造成查詢較慢。
問(wèn)題復(fù)現(xiàn)
1）手動(dòng)執(zhí)行數(shù)據(jù)庫(kù)備份
/usr/bin/mysqldump -h $HOST -u user -P$PORT -p******R52 --single-transaction --no-tablespaces --hex-blob ${DB4}| gzip > $DIR/${DB4}_${DATE}.sql.gz2）觀察慢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`;手動(dòng)查詢SQL語(yǔ)句后記錄的慢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';問(wèn)題復(fù)現(xiàn)了，備份完之后手動(dòng)執(zhí)行語(yǔ)句，在慢SQL日志里記錄了該語(yǔ)句，掃描264萬(wàn)數(shù)據(jù)，執(zhí)行耗時(shí)17S，問(wèn)題原因是備份造成。
解決方案
在MySQL 8.0.23版本中，使用 mysqldump 進(jìn)行全庫(kù)備份后，執(zhí)行某些查詢可能會(huì)出現(xiàn)首次執(zhí)行較慢的情況，這可能與InnoDB存儲(chǔ)引擎的數(shù)據(jù)頁(yè)緩存機(jī)制有關(guān)。讓我們?cè)敿?xì)解釋可能的原因和解決方法：
數(shù)據(jù)頁(yè)緩存失效：MySQL的InnoDB存儲(chǔ)引擎使用數(shù)據(jù)頁(yè)緩存來(lái)存儲(chǔ)最近訪問(wèn)的數(shù)據(jù)頁(yè)，以提高查詢性能。如果備份過(guò)程中有大量的表數(shù)據(jù)被修改或者重新加載，部分?jǐn)?shù)據(jù)頁(yè)可能會(huì)從內(nèi)存中移除，導(dǎo)致首次執(zhí)行查詢時(shí)需要重新從磁盤加載數(shù)據(jù)頁(yè)到內(nèi)存，造成查詢較慢。
解決方法：
- 查詢優(yōu)化：確保查詢語(yǔ)句本身是優(yōu)化過(guò)的，包括使用合適的索引和查詢條件，以盡量減少掃描的數(shù)據(jù)量。
- 數(shù)據(jù)頁(yè)預(yù)熱：考慮在備份后的低負(fù)載時(shí)間內(nèi)執(zhí)行一些預(yù)熱操作，例如執(zhí)行一些簡(jiǎn)單的查詢，以幫助MySQL重新加載常用的數(shù)據(jù)頁(yè)到內(nèi)存中。
- 服務(wù)器資源優(yōu)化：確保MySQL服務(wù)器的配置和資源充足，例如適當(dāng)分配內(nèi)存給InnoDB緩沖池，以提高數(shù)據(jù)頁(yè)緩存的效率。
- 定期優(yōu)化表：定期執(zhí)行OPTIMIZE TABLE 或者ANALYZE TABLE 可以幫助MySQL優(yōu)化表的存儲(chǔ)布局和統(tǒng)計(jì)信息，進(jìn)而改善查詢性能。
- 備份策略調(diào)整：盡量在數(shù)據(jù)庫(kù)負(fù)載較低的時(shí)候進(jìn)行備份操作，以減少備份對(duì)業(yè)務(wù)查詢性能的影響。
考慮使用--single-transaction 參數(shù)來(lái)執(zhí)行mysqldump，以避免對(duì)表進(jìn)行全局鎖定，從而減少備份操作對(duì)數(shù)據(jù)頁(yè)緩存的影響。
最終采用方案
數(shù)據(jù)頁(yè)預(yù)熱方案，即每次數(shù)據(jù)備份后，手動(dòng)查詢相關(guān)SQL語(yǔ)句，將熱數(shù)據(jù)寫入到InnoDB緩沖池。由于我們?cè)撎篆h(huán)境業(yè)務(wù)量較小，還能滿足日常業(yè)務(wù)需求，就不采取配置擴(kuò)容，增加InnoDB緩沖池。
腳本如下：
綜上所述，首次執(zhí)行查詢較慢可能與MySQL InnoDB存儲(chǔ)引擎的數(shù)據(jù)頁(yè)緩存機(jī)制有關(guān)，備份操作可能導(dǎo)致部分?jǐn)?shù)據(jù)頁(yè)從內(nèi)存中移除，需要重新加載。通過(guò)優(yōu)化查詢、預(yù)熱數(shù)據(jù)頁(yè)、優(yōu)化服務(wù)器配置和備份策略，可以減少這種情況的發(fā)生，提升查詢性能的穩(wěn)定性和可預(yù)測(cè)性。
作者介紹
吳守陽(yáng)，51CTO社區(qū)編輯，擁有8年DBA工作經(jīng)驗(yàn)，熟練管理MySQL、Redis、MongoDB等開(kāi)源數(shù)據(jù)庫(kù)。精通性能優(yōu)化、備份恢復(fù)和高可用性架構(gòu)設(shè)計(jì)。善于故障排除和自動(dòng)化運(yùn)維，保障系統(tǒng)穩(wěn)定可靠。具備良好的團(tuán)隊(duì)合作和溝通能力，致力于為企業(yè)提供高效可靠的數(shù)據(jù)庫(kù)解決方案。
