Lessons Learned

Ah, the sometimes bitter pill of experience.

Recently I got "the call" about a server that was having problems. I began standard troubleshooting procedures but didn't see anything abnormal. After about 15 minutes of getting irritated I opened up the my.cnf and looked through it. Imagine my suprise when I saw the innodb_log_file_size parameter was set to 5M (five megabytes). Even though it was hard-coded in the my.cnf, this is actually the default size (at least through MySQL 5.1). A log file size of five megabytes is pitifully small and was proving to be a severe bottleneck for the system. The two log files were being , filled up and flushed more frequently than once a second.

Once the source of the trouble was discovered it was a simple matter of filing a RFC and resolving the issue. We increased the log file size to 2000 megabytes so flushing of the transaction logs occured when the server had the time/resources and not when the log ran out of space.

The problem really wasn't the five megabyte innodb log file. The problem was that I made an assumption that the my.cnf for this server (and the several hundred other servers under my care) had a reasonable configuration.  You know what they say about assumptions. I should have taken the time to "baseline" things and determine if servers were configured reasonably. If I had take the time to do so this issue would have never happened.

Using tools such as percona toolkit it would be fairly easy to gather this information. Lesson learned -- never trust that things were done properly before you where there.