如何设置innodb_log_file_size

在这片文章中，我会提供一些关于如何设置 MySQL的 innodb_log_file_size 参数的一些建议。

跟其他数据库管理系统一样，MySQL通过日志来实现数据的持久性（在使用InnoDB存储引擎的前提下）。这确保了当一个事务提交后，其相关数据在崩溃或者服务器掉电的情况下不会丢失。

MySQL的InnoDB 存储引擎使用一个指定大小的Redo log空间（一个环形的数据结构）。Redo log的空间通过innodb_log_file_size和innodb_log_files_in_group（默认2）参数来调节。将这俩参数相乘即可得到总的可用Redo log 空间。尽管技术上并不关心你是通过innodb_log_file_size还是innodb_log_files_in_group来调整Redo log空间，不过多数情况下还是通过innodb_log_file_size 来调节。

为InnoDB引擎设置合适的Redo log空间对于写敏感的工作负载来说是非常重要的。然而，这项工作是要做出权衡的。你配置的Redo空间越大，InnoDB就能更好的优化写操作；然而，增大Redo空间也意味着更长的恢复时间当出现崩溃或掉电等意外时。

关于恢复时间，并不好预测对于一个指定的 innodb_log_file_size 值出现崩溃是需要多长的恢复时间–他取决于硬件能力、MySQL版本以及工作负载等因素。然而，一般情况下我们可以按照每1GB的Redo log的恢复时间大约在5分钟左右来估算。如果恢复时间对于你的使用环境来说很重要，我建议你做一些模拟测试，在正常工作负载下（预热完毕后）模拟系统崩溃，来评估更准确的恢复时间。

虽然恢复时间可以作为一个限制innodb_log_file_size的参考因素，也还有一些别的方式可以观察该参数设置是否“合理”（尤其是如果你安装了PMM： Percona Monitoring and Management）

检查Percona Monitoring and Management的“MySQL InnoDB Metrics”仪表盘，如果你看到如下的图像：

图中 Uncheckpointed Bytes 已经非常接近 Max Checkpoint Age，那么你几乎可以确定当前的 innodb_log_file_size 值因为太小已经某种程度上限制了系统性能。增加该值可以较为显著的提升系统性能。

而如果你看到的类似下图：

该图中 Uncheckpointed Bytes 远小于 Max Checkpoint Age，这种情况下再增加 innodb_log_file_size 就不会有明显性能提升。

注意：很多MySQL设置都是相互关联的，虽然一个特定的Redo log 空间对于一个较小的InnoDB Buffer Pool值来说可能已经足够，但是较大的InnoDB Buffer Pool值还是期望更大的Redo log 空间以达到更好的表现。

另一件需要记住的事：我们之前说的恢复时间，取决于 Uncheckpointed Bytes 而不是总的Redo log空间。如果你在增加了innodb_log_file_size之后并未观察到恢复时间的增加，那可能就是之前的配置在你当前的工作负载下已经够用，你增加的空间并未被完全利用。

另一个观察innodb_log_file_size的途径是 Redo log空间的使用情况：

这张图片展示了每小时写入日志文件的总数据量和innodb_log_file_size的值。上图中，我们有2G的Redo log空间但是每小时却有12G多的数据被写入日志文件。这意味着Redo空间差不多每十分钟就轮转一次。

而InnoDB 在每次Redo log空间轮转时都要将innodb buffer pool中的每个脏页都刷新到磁盘上。当这个操作出现越少时InnoDB越能得到更好的表现（对SSD硬盘的磨损也越少）。我希望看到这个操作的频率能达到至少15分钟一次，当然越少越好。

关于Redo 空间的使用情况，如果没有安装PMM的话，也可以通过下面的命令来观察每小时的写入量（MB）：

a=$(mysql -uuser -p'passwd' -e "show engine innodb status\G" | grep "Log sequence number" | awk '{print $4}'); sleep 60; b=$(mysql -uuser -p'passwd' -e "show engine innodb status\G" | grep "Log sequence number" | awk '{print $4}'); let "res=($b-$a)*60/1024/1024";echo $res

总结：
设置合适的innodb_log_file_file_size对于平衡性能和恢复时间来说非常重要。但是记住，你的场景下的恢复时间由于受所方面因素影响，并不能完全准确的预估出来。我希望本文中讨论的几点能帮助你设置更合理的innodb_log_file_file_size。