Solved

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

Posted on 2010-09-01
1
2,912 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

Question has a verified solution.

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

Azure Functions is a solution for easily running small pieces of code, or "functions," in the cloud. This article shows how to create one of these functions to write directly to Azure Table Storage.
This article shows the steps required to install WordPress on Azure. Web Apps, Mobile Apps, API Apps, or Functions, in Azure all these run in an App Service plan. WordPress is no exception and requires an App Service Plan and Database to install
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…
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…

724 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