如何选择配置 MySQL innodb_log_file_size

配置 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 分钟,一小时更好。

 

手动估算，可以使用以下脚本估算一下：

#!/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;"