10

In a production MySQL environment the following error message is written to /var/log/mysql/error.log every fourth minute:

110723 18:36:02  InnoDB: ERROR: the age of the last checkpoint is 9433856,
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.

I'm not sure how to parse the error message.

More specifically I don't understand how the age of the checkpoint relates to the "log group capacity", and how that in turn relates to the size of rows with large BLOB/TEXT columns.

Basically I'm lost on how to troubleshoot and proceed fixing this problem. My questions are:

  • What does the error message mean?
  • What am I doing wrong?
  • How can I fix it?
knorv
  • 1,799
  • 6
  • 19
  • 29

1 Answers1

11

The error message means that you're trying to insert too much data into InnoDB too quickly, and the InnoDB log is filling up before the data can be flushed into the main data files.

To solve it, you need to stop MySQL cleanly (very important), delete the existing InnoDB log files (probably lb_logfile* in your MySQL data directory, unless you've moved them), then adjust the innodb_log_file_size to suit your needs, and then start MySQL again. This article from the MySQL performance blog might be instructive.

womble
  • 96,255
  • 29
  • 175
  • 230
  • 1
    What are the consequences of just ignoring the error messages? – Matt Healy Jul 24 '11 at 02:23
  • 1
    You're joking, right? You're seriously considering ignoring an ERROR message regarding the STORAGE OF YOUR DATABASE? When the fix takes about 10 seconds of downtime? – womble Jul 24 '11 at 02:25
  • 8
    No, it actually wasn't a joke, it was a genuine question. Will the server crash? Will data be lost? Will performance be degraded? All of the above? – Matt Healy Jul 24 '11 at 02:39
  • 1
    womble, great answer! I've already fixed the problem, but I'm curious to what consequences the error had when the configuration was incorrect. The thing is that I saw some intermittent slowdowns and I'm curious to if that was related to this error or if it was something different. Thanks! – knorv Jul 24 '11 at 10:11
  • 5
    Yes, the database server will effectively hang for any updates to InnoDB tables when the log fills up. It can cripple a site. – womble Jul 24 '11 at 10:15
  • A detailed step-by-step guide: http://dba.stackexchange.com/questions/16510/mysql-innodb-innodb-error-the-age-of-the-last-checkpoint-is-innodb-which-exc – Larsen Jan 16 '15 at 10:03