Thursday, June 19, 2014

Fixing InnoDB error "the age of the last checkpoint is..."

I recently switched a production database from MyISAM to InnoDB and I noticed the next day my logwatch email had a bunch of errors like this:
[140617 02:04:22] 140617  2:04:22  InnoDB: ERROR: the age of the last checkpoint is 9433709,
[140617 02:03:40] 140617  2:03:40  InnoDB: ERROR: the age of the last checkpoint is 9433931,
[140617 02:01:58] 140617  2:01:58  InnoDB: ERROR: the age of the last checkpoint is 9433673,
Well, what does all this mean? First, as the comma at the end indicates, logwatch truncated the error message. I found that there is more information in the actual mysql.log file had more info:
InnoDB: which exceeds the log group capacity 9433498.
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.
So, as it seems, the InnoDB log file settings need to be updated. I found a listing of the steps to take on MySQL's website. Here are those steps fleshed out a little.

1) Make sure your innodb_fast_shutdown settings is not 2To fix this, set it to one by runnning the following query:
SET GLOBAL innodb_fast_shutdown = 1;
2) Shut down mysql and look for errors in the log to make sure nothing went wrong.
service mysqld stopcat /var/log/mysql.log
3) Copy the old log files to a new place in case something goes wrong.
mv /var/lib/mysql/ib_logfile* ..
4) Next edit your /etc/my.cnf file to increase your InnoDB log size:
innodb_log_file_size = 128M
You may see people suggesting really large values for the log file size -- I saw a value of 768M in one StackOverflow answer. If that seems like a suspiciously large, random value to you (especially considering the default is 5M) then you're on to something. But that begs the question, what should the value be? Here is a helpful article on how to properly size your log files.

5) Lastly, watch your /var/log/mysql.log file and start MySQL back up.
service mysqld start
One helpful tip, if you see this error in your log file,
/usr/libexec/mysqld: Incorrect information in file: './schema_name/table_name.frm'
then you probably tried to skip step 3, like I did. Turns out, this is an important step and your MySQL server may not even start up, without it. It's easy to fix though, either go back and do step 3 or remove your edits made in step 4 and restart mysqld. You should be all set.