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

This article mainly introduces the problem analysis of insert in MySQL, which is very detailed and has certain reference value. Friends who are interested must finish it!

Image.png

Image.png

2. Profile display

Image.png

In fact, the query end here is a very useful information, and the basic confirmation is waiting on the order_commit function.

Second, the first analysis of the problem

In the case I encountered, there is a situation where the commit of small things caused by big things is slow, and the state is also query end, but the problem here is obviously different. If it is caused by big things, there will be occasional slow commit situations, while here there is a steady wait of 1 second. But I still asked my friends to collect information about big things about binlog and use one of my tools as follows:

Gadgets can analyze some information about binlog, such as: 1, whether there are things that have not been submitted for a long time, 2, whether there are big things 3, how many logs have been generated from each table, 4, and the speed of generation. Use:. / infobin mysql-bin.001793 20 10-t > log1793.log the first 20 is the number of fragments, the second 2000000 is something greater than 2m, the third 10 is defined as a big thing, and the third 10 is defined as a long-term uncommitted thing. Download address: http://pan.baidu.com/s/1jHIWUN0 can only be used for binlog, not for relaylog. It is best to copy the binlog to other machines and do not run on the production server. It is best to use 5.65.7 row format binlog.

This tool is a tool that I wrote in C without relying on other tools to parse binlog to get useful information, and many friends are using it. Time is not open source, in fact, it is very simple to analyze binlog's event to get useful information. The simplified results are as follows:

-Now begin-Check Mysql Version is:5.7.19-logCheck Mysql binlog format ver is:V4Warning:Check This binlog is not closedhands check This binlog total size:87546667 (bytes) Note:load data infile not checkpoint total now-Trx total [counts]: 42771Event total [counts]: 251792Max trx event size: 9268 (bytes) Pos: 78378238 [0X4ABF4FE] Avg binlog size (/ sec): 16745.729 (bytes) [16.353 (kb)] Avg binlog size (/ min): 1004743.688 (bytes) [981.195 (kb)]...-- Large than 2000000 (bytes) trx: (1) Trx_size:54586527 (bytes) [53307.156 (kb)] trx_begin_p: 359790 [0X57D6E] trx_end_p: 54946317[ 0X3466A0D] Total large trx count size (kb): # 53307.156 Kb)-(79) Current Table:froad_cbank_anhui.cb_sms_log:: Insert:binlog size (824224 (Bytes)) times (3135) Update:binlog size (2046042 (Bytes)) times (3841) Delete:binlog size (0 (Bytes)) times (0) Total:binlog size (2870266 (Bytes)) times (6976)-(80) Current Table:test.2018products:: Insert:binlog size (54586359 (Bytes)) times (6647) Update : binlog size (0 (Bytes)) times (0) Delete:binlog size (0 (Bytes)) times (0) Total:binlog size (54586359 (Bytes)) times (6647)-Total binlog dml event size:73212228 (Bytes) times (65090)

In fact, it is easy to see that the whole binlog is only about 80m and does contain a big thing as follows, which takes up more than 50m.

-- Large than 2000000 (bytes) trx: (1) Trx_size:54586527 (bytes) [53307.156 (kb)] trx_begin_p: 359790 [0X57D6E] trx_end_p: 54946317 [0X3466A0D] Total large trx count size (kb): # 53307.156 (kb)

But big things only affect the submission of other things at the moment of submission, and the status is query end refer to my earlier article http://blog.itpub.net/7728585/viewspace-2133674/.

Let's first rule out the problems caused by big things. So what exactly is the problem? some friends say that it may be semi-synchronous, but it is also the same if semi-synchronization is not used. And I think the slow state caused by semi-synchronization should not be query end time-consuming untested.

3. Confirm the problem

There is no way but to use pstack for analysis. Fortunately, this problem is really simple as follows: pstack stack frame:

Image.png

Unexpectedly, binlog_group_commit_sync_delay is set to a maximum of 1000000, which is 1 second, which explains why simple insert will wait 1 second, and the status is query end.

The above is all the content of this article "problem Analysis of insert in MySQL". Thank you for reading! Hope to share the content to help you, more related knowledge, welcome to follow the industry information channel!

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.