Marco.org

Google wasn’t much of a help while researching this, so I’m lending some of my PageRank to it. There isn’t enough good Linux server administration help out there — or if there is, it’s buried under a million unanswered newsgroup postings (copied to 3 million different sites).

The problem: On MySQL 5.0 (in our case, version 5.0.22 from the stock RHEL5/CentOS 5 x86-64 yum repository), concurrent InnoDB queries take much longer than they did under MySQL 4.1. A query that normally takes 1-2 seconds might balloon to 20-40 seconds when a few are executing simultaneously. No amount of hardware upgrades or configuration-variable tweaking solves the problem. CPU usage is moderate to high, but disk activity is low. Eventually, the server gets heavily burdened by these queries.

The cause: This MySQL bug.

The solution: Upgrade to a newer version of MySQL, at least 5.0.30 (that’s when they claim it was fixed). We upgraded to the latest stable RPM from MySQL’s site (version 5.0.51a) and the problem was immediately and completely fixed.

Make sure you get all three RPMs: client, server, and common-libs. I would not suggest using yum with third-party repositories for this — I tried, and it screwed up very badly with a million file conflicts that ended up breaking my use of yum for MySQL.

Hopefully, the RHEL5/CentOS5 repositories will be updated soon with an official version of MySQL that contains this bugfix.