Solved

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

Posted on 2010-09-01
1
2,823 Views
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.
0
Comment
Question by:learningtechnologies
1 Comment
 
LVL 2

Accepted Solution

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

Thanks!

/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:

http://dev.mysql.com/doc/refman/5.1/en/adding-and-removing.html

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:

http://dev.mysql.com/doc/refman/5.1/en/innodb-checkpoints.html

The innodb buffer is explained here:

http://dev.mysql.com/doc/refman/5.1/en/innodb-parameters.html#option_mysqld_innodb_log_buffer_size

The server variable innodb_log_file_size is explained here:

http://dev.mysql.com/doc/refman/5.1/en/innodb-parameters.html#option_mysqld_innodb_log_file_size

Excerpt:
---
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 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.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

APEX (Application Express) is used to develop a web application from Oracle. SQL Workshop is one of the tools that comes with Oracle APEX to query or modify the database objects or to make any changes to the structure.
Entity Framework is a powerful tool to help you interact with the DataBase but still doesn't help much when we have a Stored Procedure that returns more than one resultset. The solution takes some of out-of-the-box thinking; read on!
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…

920 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now