Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

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

Posted on 2010-09-01
1
Medium Priority
?
2,998 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

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

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

This post contains step-by-step instructions for setting up alerting in Percona Monitoring and Management (PMM) using Grafana.
In this article, we’ll look at how to deploy ProxySQL.
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
Suggested Courses

604 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