MySQL之innodb_log_buffer_size和innodb_log_file_size

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

评论 2
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值