How to solve the problem of MySQL congestion with INSERT DELAYED
In this issue, the editor will bring you about how to use INSERT DELAYED to solve the problem of MySQL congestion. The article is rich in content and analyzes and narrates it from a professional point of view. I hope you can get something after reading this article.
Because of the lack of a good evaluation of MySQL's ability to insert data concurrently, the blocking problem of MySQL is ignored in some multi-process concurrent programs, and sometimes because of the blocking of MySQL, the child process has been waiting for MySQL to release the blockage and complete the INSERT instruction.
Failure phenomenon:
Blocked child processes are all in sbwait state
The parent process, which has been waiting for the child process to finish, is a wait state
If you do not manually kill blocked child processes, these processes exist all the time
Cause investigation: began to suspect that it was part of the socket problem. I thought it was due to the jam caused by waiting for the other party to close the connection when connecting to the server.
It took a long time to check and debug the socket part of the code, and several times when I thought it had been resolved, there was another failure, all of which ended in failure. This weekend, reconnect the entire socket and check the database connection one by one. It is found that the state of sbwait is caused by the blockage of MySQL. In the case of multiple processes concurrently, preempt the resources of MySQL at the same time. MySQL defaults to table types, which are locked by table. When child An is locked for insertion, child B can only wait. As a result, blockage occurs when it is concurrent.
Solution:
Optimize table structure and data structure
Change INSERT INTO to INSERT DELAYED INTO
Change the program structure so that each child process opens a MySQL connection
Description: INSERT DELAYED INTO, the client submits data to MySQL,MySQL and returns the OK status to the client. Instead of inserting data into the table, it is stored in memory waiting to be queued. When mysql is available, insert it again.
The advantage of this is that the client does not have to wait too long to increase the speed of insertion. The downside is that the auto-incrementing ID cannot be returned, and if the MySQL doesn't have time to insert data when the system crashes, the data will be lost.
Observation: after making these adjustments, there was no blockage after running for a day. And the running time is also shortened. Observe the process of MySQL through phpMyAdmin. After submission, there will be some processes whose user is DELAYED and status is Waiting for INSERT. After a while, the data was completely inserted and then disappeared.
The above is the editor for you to share how to use INSERT DELAYED to solve the MySQL congestion problem, if you happen to have similar doubts, you might as well refer to the above analysis to understand. If you want to know more about it, you are welcome to follow the industry information channel.
