0

We run a MySQL server using 600GB of storage over 4 EBS volumes on Amazon EC2. About every 2-3 weeks, the active InnoDB log file gets corrupted, and MySQL won't allow any access on those tables (saying that the InnoDB engine is disabled) until we shut down MySQL, remove the log files, and restart MySQL.

I've been working with MySQL and InnoDB for almost 10 years now, and I've only run into this issue occasionally; never this frequently. This is on Ubuntu 9, MySQL version 5.1.41.

Any advice on how to avoid getting this error this frequently?

joemastersemison
  • 33
  • 6
  • How are they communicating? You probably need to post more information on what the configuration is to figure out or speculate as to the cause of the corruption. – Bart Silverstrim Oct 12 '11 at 13:55
  • It's a standard MySQL setup. What do you mean by, "how are they communicating?" (Who/what is "they"?) The configuration file we're using is the standard my-large.cnf. – joemastersemison Oct 12 '11 at 15:01
  • It is running in just one EC2 instance or are there multiple database servers communicating with each other? – Bart Silverstrim Oct 12 '11 at 15:11
  • It's a master and there are two slaves, but the error is happening on the master each time, and since the slaves only read, I wouldn't think they would have an impact on this issue? – joemastersemison Oct 12 '11 at 18:25

1 Answers1

1

Significantly raise the innodb_log_file_size -- try 100MB at least. The most common cause of InnoDB log files getting corrupted is a large transaction causing the log files to overflow and overwrite the head of the transaction with its tail.

Make sure to shutdown the server and remove the existing log files first. Take a backup if you're paranoid.

David Schwartz
  • 31,449
  • 2
  • 55
  • 84
  • Thank you very much. The current size is 64MB with an 8MB buffer. Is there any reason not to go to something like 1GB? I have more than enough space. Also, should I increase the buffer from 8MB? – joemastersemison Oct 13 '11 at 03:08
  • A very large log file size will slow some database operations such as startup and recovery. A [larger buffer pool](http://www.mysqlperformanceblog.com/2007/11/03/choosing-innodb_buffer_pool_size/) will improve performance if you have the resources and the data set is larger than your current buffer pool. – David Schwartz Oct 13 '11 at 10:53