InnoDB: ERROR: the age of the last checkpoint exceeds the log group capacity

Posted on 2010-09-01
Last Modified: 2012-05-10
InnoDB: ERROR: the age of the last checkpoint exceeds the log group capacity

Found the following in mysql.log:

InnoDB: ERROR: the age of the last checkpoint is 9433910, 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.

1) Is this something to be very concerned about?
2) How do I adjust the "InnoDB: combined size of log files" and how do I determine the largest such row?
3) Anything else I should be considering with regard to this message?

Searches around in other resources seem to turn up very little.  Any thoughts, ideas, suggestions are greatly appreciated.

Thank you!

/David C.
Question by:learningtechnologies
1 Comment

Accepted Solution

learningtechnologies earned 0 total points
ID: 33588834
I was able to obtain an answer to this from another source.

I am leaving this information so that someone else may find it here one day.


/David C.
InnoDB flushes modified database pages from the buffer pool in small batches. All committed modifications that make the database pages in the buffer pool different from the images on disk must be available in the log files in case InnoDB has to do a recovery. Should that volume exceed that which the innodb logs (the iblog files) can contain then a checkpoint is forced.

We exceeded it by only a few hundred bytes (9433910, InnoDB: which exceeds the log group capacity 9433498, so 9433910 - 9433498). It doesn't mean we lost any information, only that a flush was forced early due to exceeding the capacity.

Possible reasons for exceeding the log group capacity size are:

* A large blob or text row insert.
* A high number of concurrent transactions.

If we're inserting large quantities of data or large blobs/text, it can greatly boost performance to increase the size of these log files. The larger the log file size, 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 (longer recovery time) in case of a crash.

However it looks like this is the only log entry so it's nothing to worry about. If this error occurs frequently we should increase the size of the log files as explained here:

Basically stop mysqld move the ib_logfile* (nothing else especially ibdata), make the change in the my.cnf, then restart the server and new files will be created to the new size. If we don't follow that procedure we'll get an InnoDB error about logfile size mismatch and InnoDb will not initialize.

Check points are explained here:

The innodb buffer is explained here:

The server variable innodb_log_file_size is explained here:

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 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.

It looks like we are using the default innodb_log_file_size of 5M (2X5M = 10M) thus the 9433498 log group capacity with some overhead.  I would increase the innodb_log_file size to 32M or 64M. 5M is small on a lot of busy systems or ones dealing with blobs, however it doesn't look like it's affecting us much here with only 1 message so we could also just monitor the log to see if the system begins exceeding it more as well.

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
TypeError: is not a function 3 56
mysql ide 10 40
Querying data from 3 SQL tables 2 32
Coldusion - DATA insert syntax problem 12 38
Shadow IT is coming out of the shadows as more businesses are choosing cloud-based applications. It is now a multi-cloud world for most organizations. Simultaneously, most businesses have yet to consolidate with one cloud provider or define an offic…
As technology users and professionals, we’re always learning. Our universal interest in advancing our knowledge of the trade is unmatched by most industries. It’s a curiosity that makes sense, given the climate of change. Within that, there lies a…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

825 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question