innodb 日志机制及优化

一、innodb 重做日志

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

  1. 将数据读入 innodb buffer pool,并对相关记录加独占锁;
  2. 将 undo 信息写入 undo 表空间的回滚段中;
  3. 更改缓存页中的数据,并将更新记录写入 redo buffer中;
  4. 提交时,根据 innodb_flush_log_at_trx_commit 的设置,用不同的方式将 redo buffer 中的更新记录刷新到 innodb redo log file 中,然后释放独占锁;
  5. 最后,后台 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,则实际写入过程如下:

  1. 求出偏移量:由于 lsn 数值远大于日志文件大小,因此通过取余方式,得到偏移量为 400MB
  2. 写入日志:找到偏移 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 来减少日志写磁盘操作,提高事务处理性能。


落棋不悔
share my coding life

落棋不悔

1.3k 声望
77 粉丝
0 条评论
推荐阅读
Redis 数据类型及应用场景
一、 redis 特点 所有数据存储在内存中,高速读写 提供丰富多样的数据类型:string、 hash、 set、 sorted set、bitmap、hyperloglog 提供了 AOF 和 RDB 两种数据的持久化保存方式,保证了 Redis 重启后数据不丢...

waterandair53阅读 34.1k评论 8

分布式高可用Mysql数据库Percona XtraDB Cluster 8.0 与 Proxysql 史上最详尽用法指南
PXC是Percona XtraDB Cluster的缩写,是 Percona 公司出品的免费MySQL集群产品。PXC的作用是通过mysql自带的Galera集群技术,将不同的mysql实例连接起来,实现多主集群。在PXC集群中每个mysql节点都是可读可写的...

apollo0084阅读 7.2k评论 2

一次偶然机会发现的MySQL“负优化”
今天要讲的这件事和上述的两个sql有关,是数年前遇到的一个关于MySQL查询性能的问题。主要是最近刷到了一些关于MySQL查询性能的文章,大部分文章中讲到的都只是一些常见的索引失效场合,于是我回想起了当初被那个...

骑牛上青山6阅读 1.4k评论 2

MongoDB 插入时间与更新时间(create_time/update_time)
MongoDB 在数据库层面不能像 MySQL 一样设置自动创建 create_time/update_time,自动更新 update_time

qbit阅读 13.9k评论 2

Mysql索引覆盖
通常情况下,我们创建索引的时候只关注where条件,不过这只是索引优化的一个方向。优秀的索引设计应该纵观整个查询,而不仅仅是where条件部分,还应该关注查询所包含的列。索引确实是一种高效的查找数据方式,但...

京东云开发者2阅读 674

封面图
SegmentFault 思否技术周刊 Vol.70 — 深入 MySQL 实战
MySQL 软件采用了 GPL( GNU 通用公共许可证),由于其体积小、速度快、总体拥有成本低,尤其是开放源码这一特点,许多中小型网站为了降低网站总体拥有成本而选择了 MySQL 作为网站数据库。

Beverly2阅读 1.4k

封面图
MySQL 数据库索引技术原理初探
一本书 500 页的书,如果没有目录,直接去找某个知识点,可能需要找一会儿,但是借助前面的目录,就可以快速找到对应知识点在书的哪一页。这里的目录就是索引。

mylxsw1阅读 1.2k

落棋不悔

1.3k 声望
77 粉丝
宣传栏