0

I've just seen several mysql errors in my log:

InnoDB: ERROR: the age of the last checkpoint is X

InnoDB: which exceeds the log group capacity Y

I understand, that I do have to increase the innodb_log_file/buffer_size setting in order to fix the issue. What I don't understand is, what happens to my data when this error occurs? Does MySql skip the log buffer file and write the corresponding data to the bin log directly?

migrated from serverfault.com Jun 7 '13 at 16:29

This question came from our site for system and network administrators.

0

MySQL can still write the changes to the binlogs (completed SQL commands) without concern for the results making it to disk.

InnoDB is responsible for flushing two-phase commits of data changes from logs to final destination (.ibd for innodb_file_per_table or ibdata1 otherwise). If InnoDB cannot flush data from its logs, there will be some housecleaning info stuck in the Redo Logs (ib_logfile0,ib_logfile1), the Undo Logs (1023 of them inside ibdata1) and the InnoDB Buffer Pool. Please see the InnoDB Infrastructure Map. Consequently, new transactions cannot never be written (or even started).

This is very possible if

Please run the following

Flush everything from all logs and shutdown mysql

mysql -uroot -p -Ae"SET GLOBAL innodb_fast_shutdown = 0;"
service mysql stop

Next, change the logs to have a larger size along with a larger log buffer

[mysqld]
innodb_log_file_size = 1G
innodb_log_buffer_size = 32M

Then, remove logs and start mysql

cd /var/lib/mysql
mv ib_logfile0 ib_logfile0.bak
mv ib_logfile1 ib_logfile1.bak
service mysql start

I discussed this before : Mysql Innodb: InnoDB: ERROR: the age of the last checkpoint is InnoDB: which exceeds the log group capacity

Give it a Try !!!

1

The InnoDB Logfile is not related to your Binlog. The InnoDB log is used in case of crash recovery. So you may loose some transactions in case of a crash. In addition to that a small InnoDB log can impact your overal MySQL performance because MySQL has to flush the data very often to disk if you have a small iblog.

In short: there is no direct impact impact when this error happens, but if you encounter a mysql crash you may loose some transactions. In addition to that you can optimized your IO performance if you increase the size because the logfiles are sequential IO which is much faster than the random IO if you have to actually write your data to the tablespace.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.