One MySQL Configuration Tip That Can Dramatically Improve MySQL Performance

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.

In default MySQL configuration (with MyISAM engine which is the default engine) even fast running INSERT or UPDATE statements can cause serious bottlenecks in tables with lots of reads (SELECT statements).

MySQL executes INSERT & UPDATE statements with higher priority. Also INSERT & UPDATE statements require table lock (for MYISAM engines) which requires even table reads (SELECT statements) to be completed before INSERT & UPDATES are executed. This can cause long delays for SELECT statements waiting behind an INSERT or UPDATE statement, which may itself take minimal time to execute, which is waiting for existing long running SQL Select statements to be completed. So even a single INSERT or UPDATE can slow-down a heavily loaded database at unpredictable times.

One solution is to use INSERT DELAYED statement to cause INSERT statements to be run at lower priority in a queue. However similar statement for UPDATE isn’t available. Also in our experiments it proved to be significantly inferior to the solution I will describe next. So are you ready?

I recommend that you add the following line in /etc/my.cnf (MYSQL configuration file in Linux; search for my.cnf in Windows) to drastically reduce the possibility of such bottlenecks, as described above, and improve the performance of heavily loaded servers:

max_write_lock_count = 1

By starting mysqld with a low value for the max_write_lock_count system variable you are forcing MySQL to temporarily elevate the priority of all SELECT statements that are waiting for a table after a specific number of inserts to the table occur. This allows READ locks after a certain number of WRITE locks. Is that clear?

The bottomline is that this simple configuration can drastically improve your MySQL performance especially if your server is heavily loaded. You should try this for optimizing any heavily loaded MySQL server including but not limited to WordPress site databases for example.

Update: Some people suggested using InnoDB. However InnoDB is not a magic bullet. It can be slower in many situations. MyISAM is tradionally the faster database with two caveats - table locking issue as explained above and lack of transactions. With the above fix we are addressing the core performance issue of MyISAM making it again the better choice when you don’t need transactions. Also some software like WordPress do not support InnoDB.