InnoDB: which exceeds the log group capacity

最新推荐文章于 2023-09-09 10:47:11 发布

kf_panda

最新推荐文章于 2023-09-09 10:47:11 发布

阅读量933

点赞数

分类专栏： MYSQL Troubleshooting

本文链接：https://blog.csdn.net/gua___gua/article/details/52858689

版权

MYSQL Troubleshooting 专栏收录该内容

30 篇文章 0 订阅

订阅专栏

当遇到InnoDB错误提示'which exceeds the log group capacity'时，原因是较大的事务与较小的innodb_log_file_size设置冲突。解决方案是增大redo日志大小。需要修改my.cnf配置，考虑innodb_log_file_size与innodb_buffer_pool_size的关系，并在关闭数据库前将innodb_fast_shutdown设为0以确保干净关闭。调整日志文件大小会减少磁盘I/O，但可能延长崩溃恢复时间。

摘要由CSDN通过智能技术生成

有时你会遇到类似下面的错误：

120414  2:20:34  InnoDB: ERROR: the age of the last checkpoint is 241588252,
InnoDB: which exceeds the log group capacity 241588224.
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.

这个错误的原因：

系统中存在较大的事物，但是innodb_log_file_size设置太小。

解决方法：

加大redo日志大小。

01) 修改my.cnf

这个需要根据实际情况调整。下面给个例子

[mysqld]
innodb_log_buffer_size          = 32M
innodb_buffer_pool_size         = 3G
innodb_log_file_size            = 768M

注意：innodb_log_file_size在5.5时默认是5M,在5.7时默认是48M。并且必须大于4M，小于innodb_buffer_pool_size/N(这个N为一个日志组中的日志数)。

并且innodb_log_file_size * innodb_log_files_in_group不能大于512G

这里的目的是要加大，innodb_log_file_size，但是也必须考虑innodb_buffer_pool_size对其的影响。

02)设置innodb_fast_shutdown

mysql> SET GLOBAL innodb_fast_shutdown = 0;

在关闭数据量前将innodb_fast_shutdown设置为0是为了完全干净的关闭数据库，具体可以看下面关于innodb_fast_shutdown的解释。

03) 关闭数据库

   service mysql stop

04)删除当前的redo文件（ib_logfile*）
05)启动数据库

service mysql start

5.7官方文档：

innodb_log_file_size

Command-Line Format	`--innodb_log_file_size=#`
System Variable	Name	`innodb_log_file_size`
	Variable Scope	Global
	Dynamic Variable	No
Permitted Values (<= 5.7.10)	Type	`integer`
	Default	`50331648`
	Min Value	`1048576`
	Max Value	`512GB / innodb_log_files_in_group`
Permitted Values (>= 5.7.11)	Type	`integer`
	Default	`50331648`
	Min Value	`4194304`
	Max Value	`512GB / innodb_log_files_in_group`

The size in bytes of each log file in a log group. The combined size of log files (innodb_log_file_size * innodb_log_files_in_group) cannot exceed a maximum value that is slightly less than 512GB. A pair of 255 GB log files, for example, would allow you to approach the limit but not exceed it. The default value is 48MB. Sensible values range from 4MB 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. Larger log files also make crash recovery slower, although improvements to recovery performance in MySQL 5.5 and higher make the log file size less of a consideration. For general I/O tuning advice, see Section 9.5.8, “Optimizing InnoDB Disk I/O”.

innodb_fast_shutdown

Command-Line Format	`--innodb_fast_shutdown[=#]`
System Variable	Name	`innodb_fast_shutdown`
	Variable Scope	Global
	Dynamic Variable	Yes
Permitted Values	Type	`integer`
	Default	`1`
	Valid Values	`0`
		`1`
		`2`

The InnoDB shutdown mode. If the value is 0, InnoDB does a slow shutdown, a full purge and a change buffer merge before shutting down. If the value is 1 (the default), InnoDB skips these operations at shutdown, a process known as a fast shutdown. If the value is 2, InnoDB flushes its logs and shuts down cold, as if MySQL had crashed; no committed transactions are lost, but the crash recovery operation makes the next startup take longer.

The slow shutdown can take minutes, or even hours in extreme cases where substantial amounts of data are still buffered. Use the slow shutdown technique before upgrading or downgrading between MySQL major releases, so that all data files are fully prepared in case the upgrade process updates the file format.