MySqlDump导致数据页缓存失效，如何避免失效与业务响应下降？

问题背景

运营反馈服务的某个页面响应偶尔特别慢，响应时间大概是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';

• 1.
• 2.
• 3.
• 4.
• 5.
• 6.
• 7.
• 8.
• 9.
• 10.
• 11.
• 12.
• 13.

2）explain显示使用到索引了，只会扫描32万数据

3）语句执行耗时0.34s

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

登录后复制

ANALYZE TABLE amortized_consumptiont ;

• 1.

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';

• 1.
• 2.
• 3.
• 4.
• 5.
• 6.
• 7.
• 8.
• 9.
• 10.
• 11.
• 12.
• 13.
• 14.
• 15.
• 16.
• 17.
• 18.
• 19.
• 20.
• 21.
• 22.

通过对比最近几个月的慢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

• 1.

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`

• 1.
• 2.
• 3.
• 4.
• 5.
• 6.
• 7.
• 8.
• 9.
• 10.
• 11.

###手动查询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';

• 1.
• 2.
• 3.
• 4.
• 5.
• 6.
• 7.
• 8.
• 9.
• 10.
• 11.
• 12.
• 13.
• 14.

问题复现了，备份完之后手动执行语句，在慢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存储引擎的数据页缓存机制有关，备份操作可能导致部分数据页从内存中移除，需要重新加载。通过优化查询、预热数据页、优化服务器配置和备份策略，可以减少这种情况的发生，提升查询性能的稳定性和可预测性。

原创作者: u_12678428 转载于: https://blog.51cto.com/u_12678428/11792822