Posts tagged ‘innodb_log_file_size’

exceeds the log group capacity

做数据订正,需要更新了一批表数据。
数据库是采用的分库结构,也就是说一个MySQL instance里面有多个分库(一共有8个MySQL instance),我的数据库instance里面是16个分库,现在需要更新每个分库里面的一张表(即16张表)的所有数据。
我是用脚本更新的,串行操作,每个分库执行:
update vas_eq_enquiries set ENABLE_VIEW=1;
结果就收到报警,应用部门也过来说他们很多语句执行不了,报错说lock timeout:
100506 19:51:16  InnoDB: ERROR: the age of the last checkpoint is 966364350,
InnoDB: which exceeds the log group capacity 966363956.
InnoDB: If you are using big BLOB or TEXT rows, you must set the
InnoDB: combined size of log files at least 10 times bigger than the
InnoDB: largest such row.
100506 19:51:32  InnoDB: ERROR: the age of the last checkpoint is 1028687928,
InnoDB: which exceeds the log group capacity 966363956.
InnoDB: If you are using big BLOB or TEXT rows, you must set the
InnoDB: combined size of log files at least 10 times bigger than the
InnoDB: largest such row.

认真看了一下错误描述,是说log group的容量不够了。MySQL的innodb引擎日志比较重要,所有数据变更都共享着这个日志undo log。看样子一下子更新的数据太多了,于是我修改脚本,每次间隔5s,10s,30s去操作每个分库,最后发现间隔30s就不会有问题了。
当然,这个30s只对我的这个情况有意义。我这里也查看了一下MySQL instance的logfile大小:
innodb_log_file_size = 512M
innodb_log_files_in_group = 2

也就是说,它只允许最大1G的变更。
查看了我变更的表vas_eq_enquiries,在各个分库上大小不一,25M~77M的都有,平均算50M,16个分库,那就有16*50=800M了,在加上数据库自己的变化量1G空间确实会捉襟见肘。

这个问题的最终解决方案有两个:
1、减少短时间内变更的数据量,避免更新造成log日志的容量不足。对于分库更新量的情况特别需要注意,
2、增加log日志的容量。log日志的容量,目前MySQL最大不允许超过4G。这里需要注意的是,修改log日志的容量必须要在MySQL将所有日志文件都应用完毕的情况下,先停机,然后将日志文件移到另外的目录备份,重启MySQL数据库。MySQL数据库启动以后,会自动生成新的log日志。否则可能会有如下的错误:
InnoDB: Error: log file /data/mysqldata/innodb_log/ib_logfile0 is of different size 0 1073741824 bytes
InnoDB: than specified in the .cnf file 0 1283457024 bytes!
100506 21:57:41 [ERROR] Plugin ‘InnoDB’ init function returned error.
100506 21:57:41 [ERROR] Plugin ‘InnoDB’ registration as a STORAGE ENGINE failed.
100506 21:57:41 [ERROR] Unknown/unsupported table type: INNODB
100506 21:57:41 [ERROR] Aborting

导致MySQL数据库启动不了。

顺便把MySQL这两个参数解释抄在下面:
innodb_log_file_size
The size in bytes of each log file in a log group. The combined size of log files must be less than 4GB. The default value is 5MB. Sensible values range from 1MB to 1/N-th of the size of the buffer pool, where N is the number of log files in the group. The larger the value, the less checkpoint flush activity is needed in the buffer pool, saving disk I/O. But larger log files also mean that recovery is slower in case of a crash.

innodb_log_files_in_group
The number of log files in the log group. InnoDB writes to the files in a circular fashion. The default (and recommended) value is 2.

这里log日志的容量可能大家会觉得越大越好。innodb_log_file_size里面也说了这个值越大,把checkpoint从buffer pool中刷到磁盘的频率也越少,减少了很多磁盘I/O。同时,crash情况下需要恢复的时间也越多。

Baron Schwartz 在MySQL Performance Blog里面提到一种怎样设置innodb_log_file_size的方法。http://www.mysqlperformanceblog.com/2008/11/21/how-to-calculate-a-good-innodb-log-file-size/
他根据每分钟变化的数据量*60分钟,来粗略的得到一个innodb_log_file_size的值。
mysql> pager grep sequence
PAGER SET TO ‘grep sequence’
mysql> SHOW engine innodb STATUS\G SELECT sleep(60); SHOW engine innodb STATUS\G
Log sequence number 84 3836410803
1 row IN SET (0.06 sec)
1 row IN SET (1 min 0.00 sec)
Log sequence number 84 3838334638
1 row IN SET (0.05 sec)
然后:
mysql> SELECT (3838334638 – 3836410803) / 1024 / 1024 AS MB_per_min;
+————+
| MB_per_min
+————+
| 1.83471203
+————+

于是:1.8*60 约等于110M,那么就取128M把,如果innodb_log_files_in_group=2,那么innodb_log_file_size=128/2=64M。
这个当然取决于你这一分钟应用程序修改的量的多少,如果取的时间不对,那么这个值当然不精确。

3楼的3. Sheeri K. Cabral提出了他自己的方法:登录系统,直接看ib_logfile切换的速度。
/var/lib/mysql> ls -lrth ib_logfile*
-rw-r–r– 1 mysql mysql 400M Nov 22 09:00 ib_logfile1
-rw-r–r– 1 mysql mysql 400M Nov 23 10:51 ib_logfile0

(这里26个小时切换了一次)然后再根据你的要求:你需要logfile中保存多久的日志,来调整logfile的大小。当然,这个也不精确。取决于你什么时候去看这个文件,白天数据量变化多,晚上变化少,这个间隔时间就完全不同。

总的来说,需要注意这个innodb_log_file_size的大小设置,每个应用都有自己的特色,大小设置需要根据应用的不同来设置。

may you success.