代码改变世界

如何选择配置 MySQL innodb_log_file_size

  abce  阅读(506)  评论(0编辑  收藏  举报

配置 InnoDB 的 redo 空间大小是写密集型工作负载最重要的配置选项之一。不过,这需要权衡利弊。配置的 redo 空间越大,InnoDB 就能更好地优化写 IO。不过,增加 redo 空间也意味着在系统断电或因其他原因崩溃时需要更长的恢复时间。

 

对于特定的 innodb_log_file_size 值,要预测系统崩溃恢复所需的时间既不容易也不直接--这取决于硬件、MySQL 版本和工作负载。差异可能很大(视具体情况而定,相差 10 倍或更多)。不过,每 1GB innodb_log_file_size大约需要 5 分钟是一个合适的粗略数字。如果这对你的环境真的很重要,建议在满负荷情况下(数据库完全预热后)通过模拟系统崩溃来测试。

 

虽然恢复时间可以作为 InnoDB 日志文件大小限制的指导原则,但你还可以通过其他一些方法来查看这个值--尤其是在安装了 PMM的情况下。

 

查看 PMM的 "MySQL InnoDB Metrics" 控制面板。如果你看到这样的图表

如果“Uncheckpointed Bytes”非常接近 "Max Checkpoint Age",那么几乎可以肯定当前的 innodb_log_file_size 限制了系统的性能。增大innodb_log_file_size可以显著提高性能。

 

如果看到的是这样的内容:

“Uncheckpointed Bytes”数远低于“Max Checkpoint Age”,那么增加日志文件大小不会有明显改善。

 

注意:许多 MySQL 设置是相互关联的。对于较小的 innodb_buffer_pool_size 而言,特定的日志文件大小可能足够大,但对于较大的 InnoDB Buffer Poo 值而言,可能需要更大的日志文件才能获得最佳性能。

 

还有一点要记住:之前提到的恢复时间实际上取决于 Unchepointed Bytes,而不是日志文件的总大小。如果你没有发现恢复时间随着 innodb_log_file_size 的增大而增加,请查看"InnoDB Checkpoint Age “图--可能只是你的工作负载和配置无法充分利用大型日志文件。

 

另一种查看日志文件大小的方法是查看日志空间使用情况:

此图显示了每小时写入 InnoDB 日志文件的数据量以及 InnoDB 日志文件的总大小。在上图中,我们有 2GB 的日志空间,每小时写入日志文件的数据约为 12GB。这意味着我们每十分钟就要循环处理一次日志。

 

InnoDB 必须在每个日志文件周期内至少刷新一次缓冲池中的每个脏页面。

 

如果刷新频率较低,InnoDB 的性能会更好,对 SSD 设备的磨损也会更小。我希望刷新时间不少于 15 分钟,一小时更好。

 

手动估算,可以使用以下脚本估算一下:

1
2
3
4
5
6
7
8
9
10
11
12
13
#!/bin/bash
 
USER="root"
PASS="root"
 
# Use command substitution to assign the output to a variable
a=$(mysql -u$USER -p$PASS -e"show engine innodb status\G" | grep "Log sequence number" | awk '{print $4}')
# The semicolon is not necessary when you move to a new line
mysql -u$USER -p$PASS -e"select sleep(60);"
 
b=$(mysql -u$USER -p$PASS -e"show engine innodb status\G" | grep "Log sequence number" | awk '{print $4}')
# Make sure to use the correct minus sign and not a dash character. Also, enclose the arithmetic operation inside $(( ))
mysql -u$USER -p$PASS -e"SELECT ((${b} - ${a}))*60/1024/1024 AS MB_Per_Hour;"

 

相关博文:
阅读排行:
· DeepSeek 开源周回顾「GitHub 热点速览」
· 物流快递公司核心技术能力-地址解析分单基础技术分享
· .NET 10首个预览版发布:重大改进与新特性概览!
· AI与.NET技术实操系列(二):开始使用ML.NET
· .NET10 - 预览版1新功能体验(一)
历史上的今天:
2023-05-04 SQLServer配置远程访问连接
2020-05-04 金融学原理(第四章)--金融市场与金融机构
点击右上角即可分享
微信分享提示