One MySQL Configuration Tip That Can Dramatically Improve MySQL Performance

By Angsuman Chakraborty, Gaea News Network
Wednesday, August 13, 2008

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 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.

May 31, 2010: 11:59 pm

good info, thanks

January 21, 2010: 6:14 pm

This is a bad idea. Keep the original setting. Switch to InnoDB if you have a sensible write load.

Angsuman Chakraborty
September 15, 2009: 9:08 am

Hi Daniel,
Sorry for my late response.

As I understand, delaying reads for insert / update is MySQL ISAM’s way of preventing display of older data and potentially inaccurate data. I wouldn’t recommend it without testing for say financial applications. However for WordPress, I wouldn’t worry too much about it.

Thanks for the link.

August 28, 2009: 5:30 am

Really a great post.Thanks for sharing such an important information with us.Thanks for the post.

March 5, 2009: 11:43 am

I was quite curious when I read another post of yours that linked to your article. And well, it seems that you really found an easy fix to the MyIsam issue under load. Though I am wondering whether following your advice will actually delay the INSERT and UPDATE queries? In that case I don’t want to have an error on my DB or server that basically gets rid of all those stacked entries waiting to make it into the tables. Probably not a big issue for a wordpress installation, but beware you’re running serious applications… Anyway, just wondering, maybe you could enlighten me on that. Take it as a purely academic note. Another post that you might find interesting (and that destroys the MyIsam performance advantage):


PS: I found heaps of good info on your blog. Keep it up!

January 21, 2009: 6:27 am

Welcome. Previously I unsuccessfully tried to convert my blog to use InnoDB. Do I need to make any changes to make it work?
How does InnoDB perform with WordPress compared to MyISAM?


January 20, 2009: 5:01 pm

Uh, WordPress works just fine with InnoDB tables, I use that on my blog.

August 14, 2008: 5:38 am

[...] I recommend that you add the following line to your MySQL configuration file (my.cnf): max_write_lock_count = 1 Read the technical details & explanation here. [...]

will not be displayed