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?

Improve this question
4
  • 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. Commented Oct 12, 2011 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. Commented Oct 12, 2011 at 15:01
  • It is running in just one EC2 instance or are there multiple database servers communicating with each other? Commented Oct 12, 2011 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? Commented Oct 12, 2011 at 18:25

1 Answer 1

Reset to default
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.

Improve this answer
2
  • 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? Commented Oct 13, 2011 at 3:08
  • A very large log file size will slow some database operations such as startup and recovery. A larger buffer pool will improve performance if you have the resources and the data set is larger than your current buffer pool. Commented Oct 13, 2011 at 10:53

You must log in to answer this question.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.