MySQL之innodb_log_buffer_size和innodb_log_file_size

最新推荐文章于 2024-09-19 23:41:13 发布

小王格子

最新推荐文章于 2024-09-19 23:41:13 发布

阅读量2.6w

点赞数 4

分类专栏： MySQL 文章标签： mysql

本文链接：https://blog.csdn.net/qq_25854057/article/details/114698620

版权

MySQL 专栏收录该内容

14 篇文章 0 订阅

订阅专栏

Innodb_log_buffer_size 定义InnoDB用于写入磁盘上的日志文件的缓冲区的大小（以字节为单位）。默认大小为16MB。较大的日志缓冲区使大型事务可以运行，而无需在事务提交之前将日志写入磁盘。

一、日志缓冲池相关参数说明

1）系统变量参数

innodb_log_files_in_group
日志文件中日志组。 InnoDB以循环方式写入文件。默认值（推荐值）为2,也就是两个ib_logfile文件。
innodb_log_file_size
日志组中每个日志文件的字节大小。日志文件的组合大小（(innodb_log_file_size * innodb_log_files_in_group) ）不能超过略小于512GB的最大值。通常，日志文件的组合大小应该足够大，以便服务器能够消除工作负载活动的高峰和低谷，这通常意味着有足够的重做日志空间来处理一个多小时的写活动。值越大，缓冲池中所需的检查点刷新活动就越少，从而节省磁盘I/O。日志文件越大，崩溃恢复速度也越慢。在MySQL 5.7.11中，(innodb_log_file_size 的最小值从1MB增加到4MB。
innodb_flush_log_at_trx_commit
控制如何将日志缓冲区的内容写入磁盘并刷新到磁盘。
innodb_flush_log_at_timeout
控制日志刷新频率

1）运行状态变量

Innodb_log_waits
日志缓冲区太小并且需要等待刷新才能继续的次数。该值等于0或者接近0，说明Innodb_log_buffer_size设置合适。

二、合理设置日志缓存池相关参数

1、Innodb_log_file_size 一般设置128M，实际不够可根据如下规则调整。

# 具体依据如下：我经常设置为 64-512MB
# 一般来说，日志文件的全部大小，应该足够容纳服务器一个小时的活动内容。
# 首先在业务高峰期，计算出1分钟写入的redo量，然后评估出一个小时的redo量

mysql> pager grep  Log       #使用page之后，执行的命令只显示 Log 开头的
PAGER set to 'grep  Log'

mysql> show engine innodb status\G select sleep(60); show engine innodb status\G;
select UserID , IMEI , regtime,LoginName,PlatID,PlatType,Mobile from register_info where LoginName = '931011910@365you'
Log sequence number 12701144821588
Log flushed up to   12701144603368
1 row in set (0.08 sec)

1 row in set (1 min 0.00 sec)

Log sequence number 12701162651514
Log flushed up to   12701162471514
1 row in set (0.00 sec)

mysql> select (12701162651514-12701144821588)/1024/1024 as MB;
+-------------+
| MB          |
+-------------+
| 17.00394249 |
+-------------+
1 row in set (0.00 sec)

Log sequence number，这是写入事务日志的总字节数。所以，现在你可以看到每分钟有多少MB日志写入，通过计算后得到每分钟有17M的日志写入。根据经验法则。通常我们设置redo log size足够大，能够容纳1个小时的日志写入量。

1小时日志写入量=17M * 60=1 020M，由于默认有两个日志重做日志文件ib_logfile0和ib_logfile1。在日志组中的每个重做日志文件的大小一致，并以循环的方式写入。innodb存储引擎先写重做日志文件0，当达到文件的最后时，会切换到重做日志1，并checkpoint。以此循环。

所以我们可以大约设置innodb_log_file_size=512M。注意：在innodb1.2.x版本之前，重做日志文件总的大小不得大于等于4G，而1.2.x版本将该限制扩大到了521G。

总结：

innodb_log_file_size 设置太小，当一个日志文件写满后，innodb会自动切换到另外一个日志文件，而且会触发数据库的检查点（checkpoint），这会导致innodb缓存脏页的批量刷新，会明显降低innodb的性能。
innodb_log_file_size 设置太大，减少了checkpoint，并且由于redo log是顺序I/O，大大提高了I/O性能。但是如果数据库意外出现了问题，比如意外宕机，那么需要重放日志并且恢复已经提交的事务。如果日志很大，那么将会导致恢复时间很长，甚至到我们不能接受的程度。

Reference