InnoDB log file 设置多大合适？

时间：2022-03-10 17:09

简介：

数据库的东西，往往一个参数就牵涉N多知识点。所以简单的说一下。大家都知道innodb是支持事务的存储引擎。事务的四个特性ACID即原子性（atomicity），一致性（consistency），隔离性（isolation），持久性（durability）。其中原子性，一致性，持久性通过redo log 和 undo来实现。redo log称为重做日志，用来保证事务的原子性和持久性。undo log用来保证事务的一致性。当事务提交时，必须先将该事务的所有日志写入到重做日志文件（redo log）进行持久化（当然先写到日志缓冲区，而不是直接写文件，关于什么时候刷新到磁盘文件，是一个比较复杂的问题，同学们自行查阅资料），待事务提交操作才算完成。innodb的redo log是顺序I/O，所以设置合适的值能够大大提高数据库性能。那么是不是设置的越大越好呢？

设置的太小：当一个日志文件写满后，innodb会自动切换到另外一个日志文件，而且会触发数据库的检查点（Checkpoint），这会导致innodb缓存脏页的小批量刷新，会明显降低innodb的性能。

设置的太大：设置很大以后减少了checkpoint，并且由于redo log是顺序I/O，大大提高了I/O性能。但是如果数据库意外出现了问题，比如意外宕机，那么需要重放日志并且恢复已经提交的事务，如果日志很大，那么将会导致恢复时间很长。甚至到我们不能接受的程度。

那么我们如何估算log file该设置为多大？通常我们可以通过观察show status中innodb_os_log_written状态变量来查看innodb对日志文件写出了多少数据。一个好用的经验是，查看10-100秒间隔的数字，然后记录峰值。可以用这个判断日志缓冲是否设置的正好。例如，若看到峰值是每秒写100kb数据到日志，那么1MB的日志缓冲已经足够了。也可以使用这个衡量标准来决定日志文件设置多大会比较好。如果峰值是100KB/s，那么256M的日志文件足够存储至少2560秒的日志记录。一般来说，日志文件的全部大小，应该足够容纳服务器一个小时的活动内容。

下面看实际的测试（由于是虚拟机，所以使用sysbench模拟据量写入，生产环境选择数据库最繁忙的时候测试）：

[root@yayun-mysql-server ~]# sysbench --test=oltp --oltp-table-size=100000 --oltp-read-only=off --init-rng=on --num-threads=16 --max-requests=0 --oltp-dist-type=uniform --max-time=180 --mysql-user=root --mysql-socket=/tmp/mysqld.sock --mysql-password=123456 --db-driver=mysql --mysql-table-engine=innodb --oltp-test-mode=complex prepare

1.首先计算innodb每分钟产生的日志量：