MySQL - Use Proper Storage Engine

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.

I recommend that you add the following line in /etc/my.cnf:

 [mysqld]
  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.

This simple configuration can drastically improve your MySQL performance especially if your server is heavily loaded.

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. See http://blog.taragana.com/index.php/archive/one-mysql-configuration-tip-that-can-dramatically-improve-mysql-performance/

Unless otherwise stated, the content of this page is licensed under Creative Commons Attribution-ShareAlike 3.0 License