I mean every word of it. I found this simple configuration tip after days of continuously looking at MySQL logs (mytop), top, slow log queries, debugging the hell out of applications, reading tons of MySQL optimization tips (and pulling my remaining hairs in frustration) on the web. Even MySQL optimization tips from MySQL doesn't mention it. And yet this single tip solved all my MySQL headaches and performance problems. Here are some of the problems I faced:

My powerful dedicated server was frequently consuming 100% of the CPU even with moderate load.
Even with tons of optimization and indexes, I found my server idle CPU going to 0%. The key resource consumer was MySQL. The worst part was that MySQL refused to serve new request as all threads were exhausted waiting (for some miracle to happen?).

Does any of that sound familiar? Then read on for the gory technical explanations and the tip.

BTW: My initial reaction to such problems was the standard one. I looked at MySQL master-master replication (this is better than the master-slave replication which WordPress.com does for its sites) to take care of the increased load. Fortunately this single configuration change made my server take at least 10 times more load than before.

Full article (591 words) »