如何设置innodb_log_file_size

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

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

MySQL的InnoDB 存储引擎使用一个指定大小的Redo log空间(一个环形的数据结构)。Redo log的空间通过innodb_log_file_sizeinnodb_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”仪表盘,如果你看到如下的图像:
image
图中 Uncheckpointed Bytes 已经非常接近 Max Checkpoint Age,那么你几乎可以确定当前的 innodb_log_file_size 值因为太小已经某种程度上限制了系统性能。增加该值可以较为显著的提升系统性能。

而如果你看到的类似下图:
img
该图中 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空间的使用情况:
img
这张图片展示了每小时写入日志文件的总数据量和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。

没有更多推荐了,返回首页