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

The main content of this article is "how to understand the delay beat of MySQL data". Interested friends may wish to take a look. The method introduced in this paper is simple, fast and practical. Let's let the editor take you to learn "how to understand the problem of MySQL data delay beating".

First of all, in the high availability test, there is a set of intermittent testing of the environment. After troubleshooting, it is found that there is a delay in the database. When logging in to the slave library show slave status, you will find that the value of Seconds_behind_master is constantly jumping, that is, it keeps jumping from the frequency of 0: 39 to 0: 39, which is very irritating.

Looking at the relevant logs of the database, I found that there were no log records for reference. How to analyze this problem? let's reproduce it first. So I grabbed the log of the problem three times according to the rhythm, that is, through show slave status continuous monitoring, the result of grabbing the show slave status output was saved, so we got the offset change in the process of a problem. This change is a problem in the playback process of SQLThread.

For example, in the following output, I intercepted the relay log of Slave for analysis, and the corresponding field is Relay_Log_Pos.

Slave_IO_State: Waiting for master to send event Master_Host: xxxx Master_User: dba_repl Master_Port: 4306 Connect_Retry: 60 Master_Log_File: mysqlbin.000044 Read_Master_Log_Pos: 386125369 Relay_Log_File: slave-relay-bin.000066 Relay_Log_Pos: 386125580 Relay_Master_Log_File: mysqlbin.000044

So we quickly got the change of the offset: 385983806, 386062813, 386125580.

Then I use mysqlbinlog to start analyzing the details of these logs, and I can quickly get three related tables in the dumped logs according to the following command.

# grep INSERT relaylog_xxxx.dump | awk'{print $3 "" $4}'| sed's sort | uniq act_action_exec_info act_join_desc dic_subsidy_marketing_querylog_202008

I gradually analyzed the data operations of each table, and the information I got was still limited, so I continued to do further analysis, for example, let's analyze the number of transactions in the entire log:

# mysqlbinlog slave-relay-bin.000066 | grep "GTID$ (printf'\ t') last_committed"-B1\ > | grep-E'^ # at' | awk'{print $3}'\ > | awk 'NR==1 {tmp=$1} NR > 1 {print ($1-tmp) Tmp=$1}'> | sort-n-r | head-n 100 mysqlbinlog: [Warning] unknown variable 'loose-default-character-set=utf8' 5278 5268 5268 5268 5253 5253 5253

You can see that it is about 5K, which is quite large, but where can I get this extra information? I opened general_log in the main library so that I can get a more fine-grained operation log.

Further analysis shows that the whole business uses the way of displaying transactions: SET autocommit=0, the whole transaction contains several large SQL, which stores a lot of operation log details, and the operation of select count (1) from xxx is called many times based on the Mybatis framework during the transaction operation.

After communicating with the business, the above problems are basically clarified.

At this point, I believe you have a deeper understanding of "how to understand the problem of MySQL data delay jump". You might as well do it in practice. Here is the website, more related content can enter the relevant channels to inquire, follow us, continue 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.