mysql innodb_log_buffer_size_innodb 日志机制及优化

一、innodb 重做日志

当更新数据时，innodb 内部的操作流程大致是：

将数据读入 innodb buffer pool，并对相关记录加独占锁；

将 undo 信息写入 undo 表空间的回滚段中；

更改缓存页中的数据，并将更新记录写入 redo buffer中；

提交时，根据 innodb_flush_log_at_trx_commit 的设置，用不同的方式将 redo buffer 中的更新记录刷新到 innodb redo log file 中，然后释放独占锁；

最后，后台 IO 线程根据需要择机将缓存中更新过的数据刷新到磁盘文件中。

---

LOG

---

Log sequence number 1708635750 // 上次数据页的修改，还没有刷新到日志文件的lsn号

Log flushed up to 1708635750 // 上次成功操作，已经刷新到日志文件中的lsn号

Pages flushed up to 1708635750

Last checkpoint at 1708635741 // 上次检查点成功完成时的lsn号，以为着恢复的起点

其中 lsn(log sequence number) 称为日志序列号,它实际上对应日志文件的偏移量，其生成公式是：

新的 lsn = 旧的 lsn + 写入的日志大小

例如，日志文件大小为 600 MB,目前的 lsn 是 1GB，现在要将 512 字节的更新记录写入 redo log，则实际写入过程如下：

求出偏移量：由于 lsn 数值远大于日志文件大小，因此通过取余方式，得到偏移量为 400MB

写入日志：找到偏移 400MB 的位置，写入 512 字节日志内容，下一个事务的 lsn 就是 1000000512。

除 innodb buffer pool，innodb log buffer 的大小，redo 日志文件的大小以及 innodb_flush_log_at_trx_commit 参数的设置等，都会影响 innodb 的性能。

二、innodb_flush_log_at_trx_commit 的设置

innodb_flush_log_at_trx_commit 参数可以控制将 redo buffer 中的更新记录写入到日志文件以及将日志文件数据刷新到磁盘的操作时机。通过调整这个参数，可以在性能和数据安全之间做取舍。

0：在事务提交时，innodb 不会立即触发将缓存日志写到磁盘文件的操作，而是每秒触发一次缓存日志回写磁盘操作，并调用系统函数 fsync 刷新 IO 缓存。这种方式效率最高，也最不安全。

1：在每个事务提交时，innodb 立即将缓存中的 redo 日志回写到日志文件，并调用 fsync 刷新 IO 缓存。

2：在每个事务提交时，innodb 立即将缓存中的 redo 日志回写到日志文件，但并不马上调用 fsync 来刷新 IO 缓存，而是每秒只做一次磁盘IO 缓存刷新操作。只要操作系统不发生崩溃，数据就不会丢失，这种方式是对性能和数据安全的折中，其性能和数据安全性介于其他两种方式之间。

innodb_flush_log_at_trx_commit 参数的默认值是 1，即每个事务提交时都会从 log buffer 写更新记录到日志文件，而且会实际刷新磁盘缓存，显然，这完全能满足事务的持久化要求，是最安全的，但这样会有较大的性能损失。

在某些需要尽量提高性能，并且可以容忍在数据库崩溃时丢失小部分数据，那么通过将参数 innodb_flush_log_at_trx_commit 设置成 0 或 2 都能明显减少日志同步 IO，加快事务提交，从而改善性能。

三、设置 log file size ，控制检查点

当一个日志文件写满后，innodb 会自动切换到另一个日志文件，但切换时会触发数据库检查点(checkpoint),这将导致 innodb 缓存脏页的小批量刷新，会明显降低 innodb 的性能。

可以通过增大 log file size 避免一个日志文件过快的被写满，但如果日志文件设置的过大，恢复时将需要更长的时间，同时也不便于管理，一般来说，平均每半个小时写满一个日志文件比较合适。

可以通过下面的方式来计算 innodb 每小时产生的日志量并估算合适的 innodb_log_file_size 的值：

// 1. 计算 innodb 每分钟产生的日志量

MySQL [(none)]> pager grep -i "log sequence number"

PAGER set to 'grep -i "log sequence number"'

MySQL [(none)]> show engine innodb status\G select sleep(60);show engine innodb status\G

Log sequence number 1706853570

1 row in set (0.00 sec)

1 row in set (1 min 0.00 sec)

Log sequence number 1708635750

1 row in set (0.00 sec)

MySQL [(none)]> nopager

PAGER set to stdout

MySQL [(none)]> select round ((1708635750 - 1706853570) /1024/1024) as MB;

+------+

| MB |

+------+

| 2 |

+------+

1 row in set (0.00 sec)

通过上述操作得到 innodb 每分钟产生的日志量是 2 MB。然后计算没半小时的日志量：

半小时日志量 = 30 * 2MB = 60MB

这样，就可以得出 innodb_log_file_size 的大小至少应该是 60MB。

四、调整 innodb_log_buffer_size

innodb_log_buffer_size 决定 innodb 重做日志缓存池的大小，默认是 8MB。对于可能产生大量更新记录的大事务，增加 innodb_log_buffer_size 的大小，可以避免 innodb 在事务提交前就执行不必要的日志写入磁盘操作。因此，对于会在一个事务中更新，插入或删除大量记录的应用，可以通过增大 innodb_log_buffer_size 来减少日志写磁盘操作，提高事务处理性能。