Optimize and Increase MySQL Responsiveness and Performance with max_write_lock_count

MySQL Server is one of the most popular relationship database management system (RDBMS) that runs as a server providing multi-user access to databases. MySQL executes INSERT and UPDATE SQL (table write) statements with higher priority when comparing with SELECT (table read) SQL commands. The behavior may cause bottleneck or deadlock on some type of databases, such as MyISAM engine databases, which requires table lock.

Table lock causes all read or write queries to a specific table on a database to be executed and carried out independently and sequentially. When a SQL query is executing, the table is locked and made not available to other SQL commands, causing all subsequent SQL queries to queue to wait for the table to be released.

In most situation, table reads with SELECT SQL statements can be performed quickly, take just a fraction of time when comparing table writes operating with INSERT, UPDATE or DELETE SQL statements. Default table waiting behavior of MySQL database can cause a long delay for pending read lock requests to queue and wait for table release after processing write lock requests. On busy or high load server with thousands of write and read requests, MySQL may slow to crawl, taking up huge CPU processing power and cause server to less responsive, as each request waiting for server thread or process takes up important server’s resources and memory.

By tuning and tweaking the following MySQL Server system variable, system administrator can force the database server to execute and serve read lock requests in higher priority, effectively make the queue processing faster, and make the web pages serving faster.

The system variable is max_write_lock_count, and database administrator can including the following line into MySQL configuration file (typically located in /etc/my.cnf) to configure the value:

max_write_lock_count = 1

Change the numerical value to any number within acceptable range. The acceptable value for max_write_lock_count ranges between 1 to 4294967295 for 32-bit server, or 1 to 18446744073709547520 for 64-bit server. The default max_write_lock_count value for MySQL Server is 4294967295 and 18446744073709547520 for 32-bit and 64-bit server respectively.

max_write_lock_count instruct MySQL server to allow some pending read lock requests to be processed in between write lock request after the specified number of write locks been processed. The low max_write_lock_count value can potentially optimizes and speed up MySQL performance to handle a few times more traffic, by making MySQL quickly serves all read requests (typically when visitors request web pages), while slowly taking its time to insert, update or delete data in databases.