Shulou(Shulou.com)05/31 Report--

This article mainly introduces how to slow the Insert of MySQL due to big affairs. It has a certain reference value. Interested friends can refer to it. I hope you can learn a lot after reading this article. Let's take a look at it.

[question]

INSERT statement is one of the most common SQL statements. Recently, there are alarms of concurrent threads on a MySQL server from time to time. From the recording information, there are a large number of slow queries of insert, executing for tens of seconds, waiting for flushing log, and status query end.

[preliminary analysis]

From the perspective of waiting resources, most of the time was spent in the innodb_log_file phase. It is suspected that it may be caused by disk problems. After investigation, it is not found that there is a hardware problem in the server itself.

Later, turn on the automatic collection of pstack when the thread rises, and locate the location where the MySQL thread is waiting.

[analysis process]

After the automatic crawling of pstack is deployed, there have been 6 alarms with thread concurrency > = 50 (a large number of slow queries will be generated in each alarm), and 3 times have been caught on the spot.

When the concurrent thread rises, there are more than 50 thread cards in the Stage_manager::enroll_for function, which is in the group commit phase.

The SQL statement for thread 0x519c5940 is as follows, which has been executed for 18 seconds

The Stage_manager::enroll_for function implements the queuing of multiple threads in the flush_stage phase. Simply put, for a grouped transaction, it is committed by the leader thread, while other threads are queued waiting for the leader thread to commit the transaction for that thread to complete.

If the first thread executes slowly, the subsequent threads are waiting and the entire set of transactions cannot be committed.

The process can also be understood as follows

Session A COMMIT-- > get the lock-- > do binlog write-- > commit complete

Session B COMMIT-- > wait for Lock-- > get Lock-- > perform binlog write-- > commit complete

Why is the execution of the first thread so slow? after analyzing the log file of the alarm period, it is found that there are two large transactions of 15m and 20m in the log.

To view the log details, there is a large transaction deletion statement in delete from, which contains about 23W records. Deleting 23W records in ROW mode will result in about 20m log files, which takes a long time to flush and blocks the commit of other transactions under the same grouping.

The start time of the transaction matches the alarm time

Under the backlog of grouping, transactions concentrate on brushing the disk, and as reflected in the disk index, we can see a significant increase in the disk_write_kbytes index during the problem period.

[optimization scheme]

1. It is recommended to develop large transaction delete statements that avoid using delete from entire tables.

[other workarounds]

2. A large number of logs will be generated under the ROW mode recorded by Binlog, which can be changed to MIXED mode, which can also solve the problem in theory.

3. Replace the disk with good performance

Thank you for reading this article carefully. I hope the article "what to do with Insert due to MySQL due to big affairs" shared by the editor will be helpful to everyone. At the same time, I also hope that you will support us and pay attention to the industry information channel. More related knowledge is waiting for you to learn!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.