Solve the read/write mutex lock problem of MySQL (MyISAM)

Recently, due to the increase in database read requests, there was a serious read/write lock problem. Due to Master/Slave separation, the master server quickly completed the write operation, however, a large number of select queries in the slave database will be severely blocked by the update and insert statements synchronized from the master database and the slave database, resulting in a rapid increase in the load of all MySQL databases. Because there is no way to add a read server in a short period of time, we have made some configuration for MySQL to sacrifice the real-time data, in exchange for the life security of all servers. The specific adjustments and ideas are as follows:

MyISAM reading operations are very efficient. Once a large number of read/write concurrency occurs, the efficiency of MyISAM will decrease in comparison with InnoDB, and MyISAM and
The Data Storage Methods of InnoDB are also significantly different: In MyISAM, new data is appended to the end of the data file. If you do some update and delete operations
Data files are no longer consecutive. In the image aspect, there are many holes in the data file. When new data is inserted, by default, we will first check whether the size of these holes can accommodate new data,
If yes, save the new data directly to the hole. Otherwise, save the new data to the end of the data file. This is done to reduce the size of data files and reduce the generation of file fragments. However
This is not the case in InnoDB. In InnoDB, because the primary key is cluster, data files are always sorted by primary key. If auto-incrementing ID is used as the primary key, new data is always
Is located at the end of the data file.

After learning about these basic knowledge, let's talk about Several configuration options that are easily overlooked by MyISAM:

Concurrent_insert:

In general, read/write operations in MyISAM are serial, but when querying and inserting the same table, to reduce the frequency of lock competition, according to the settings of concurrent_insert, myISAM can process queries and inserts in parallel:

When concurrent_insert = 0, concurrent insertion is not allowed.

When concurrent_insert = 1, concurrent insertion is allowed for tables without holes. The new data is at the end of the data file (default ).

When concurrent_insert = 2, concurrent insertion at the end of the data file is allowed regardless of whether the table has holes.

In this case, it is very cost-effective to set concurrent_insert to 2. As for the resulting file fragments, You can regularly use optimize
Table syntax optimization.

Max_write_lock_count:

By default, the write operation has a higher priority than the read operation. Even if the read request is sent first and then the write request is sent, the write request is processed first, then process the Read Request. This causes a problem: Once I send several write requests, all read requests will be blocked until all write requests are processed. Max_write_lock_count:

Max_write_lock_count = 1

With this setting, when the system processes a write operation, the write operation will be suspended to give the read operation a chance.

Low-priority-Updates:

We can also simply reduce the write operation Priority and give the read operation a higher priority.

Low-priority-Updates = 1

In summary, concurrent_insert = 2 is absolutely recommended. As for max_write_lock_count = 1 and low-priority-Updates = 1, it depends on the situation. If you can reduce the priority of write operations, use low-priority-Updates = 1; otherwise, use max_write_lock_count = 1.