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

This article mainly explains "the reasons for the delay in analyzing the database Seconds_Behind_Master". Interested friends may wish to take a look. The method introduced in this paper is simple, fast and practical. Let Xiaobian take you to learn "Analyze the causes of database Seconds_Behind_Master delay"!

Summary (1) Category I:

This type of latency can cause a high load on the server, possibly CPU/IO load. Because the slave library is actually executing events, we should consider these situations if our server load is relatively high.

Latency caused by large transactions does not increase from 0, but directly from how long the main library has been executing. For example, the 20 seconds it takes for the main library to execute this transaction, or the delay will start from 20, which can be easily seen by careful observation. This is because there is no exact execution time in Query Events.

Latency caused by large table DDL increases from 0 because Query Events record accurate execution times

Latency caused by tables not properly using primary or unique keys. Do not assume that setting slave_rows_search_algorithms to INDEX_SCAN,HASH_SCAN will solve the problem completely.

Because the parameters sync_relay_log, sync_master_info, sync_relay_log_info are unreasonable, especially sync_relay_log will greatly affect the performance of the slave library. The reason is described in Section 26, because sync_relay_log set to 1 causes a lot of relay log swipes.

Whether the binary log function is enabled from the library, i.e. log_slave_updates parameter is enabled, and can be disabled if not necessary. I have encountered this situation many times.

(2) Category II:

This type of latency often does not result in a high load on the server. They either didn't actually execute the Event, or they did something special.

Long-outstanding transactions may cause a momentary increase in latency because GTID_EVENT and XID_EVENT are commit times and the other Events are command initiation times. This is illustrated in verse 27.

The delay caused by the row lock of the Innodb layer is caused by the modification operation from the library and the conflict with the data modified by the SQL thread, because we said in Section 23 above that the SQL thread will also open the transaction and obtain the row lock. Let's test it below.

Delay caused by MySQL layer MDL LOCK, which may be caused by SQL thread performing some DDL operations but locking table operations from the library.

The slave_checkpoint_period parameter is incorrectly set in MTS.

Manually increased slave server time during slave run.

II. Relevant tests

Because many of the above delays have been tested and described. Let's test the delay caused by the lock.

(1) Delay caused by row lock of Innodb layer

This is very easy to test, I just need to do a transaction from the library and SQL thread to modify the same data can appear, roughly test as follows:

From Library: mysql> begin; Query OK, 0 rows affected (0.00 sec)mysql> delete from tmpk;Query OK, 4 rows affected (0.00 sec) Do not commit the main library execute the same statements mysql> delete from tmpk;Query OK, 4 rows affected (0.30 sec)

At this point you will observe the delay as follows:

If you look at sys.innodb_lock_waits, you see something like this:

Of course, if you look at INNODB_TRX, you can also observe the existence of transactions. There is no screenshot here. You can try it yourself.

(2) Delay caused by MySQL layer MDL LOCK

This situation is also very easy to test, we only need to open a transaction to do a select, and then the main library on the same table to do DDL can appear as follows:

From library: mysql> begin;Query OK, 0 rows affected (0.00 sec)mysql> mysql> select * from tkkk limit 1;+-----+----+----+| a | b | c |+------+------+------+| 3 | 3 | 100 |+------+----+----+----+1 row in set (0.00 sec) Do not commit, MDL LOCK on table will not release main library Execute statement: mysql> alter table tmpk add testc int ;Query OK, 0 rows affected (1.14 sec)Records: 0 Duplicates: 0 Warnings: 0

At this point you will see the following message:

We can see from the state that this is the delay caused by waiting for the MDL lock to be acquired.

III. SUMMARY

Through the whole series, we should be clear about the calculation method of Seconds_Behind_Master, and if there is a delay, we first check whether there is a load from the library, and treat it differently according to whether there is a load. Note that the load here must use top -H to view the load of io/sql/walker threads. I have encountered more than once a friend asked me the delay problem, when I asked him how the load he told me that the load is not high ah overall load is less than 2, here we should note that for a thread can only use a CPU core, although the overall load is less than 2 but io/sql/worker thread may have run full, in fact, the load is already very high, we look at the following screenshot is sql thread high load screenshot as follows:

This screenshot we found that although the overall load is not high at a little more than 1, but Lwp number 20092 thread has run full, this thread is our sql thread, this time delay is very likely, this screenshot is from a reasonable use of primary key or unique key caused by delay case.

We should use top-H to view CPU load, and iotop, iostat and other tools can be used to view io load. I need to emphasize that when looking at MySQL load we have to look at it from the perspective of threads.

At this point, I believe that everyone has a deeper understanding of the "reasons for the delay in analyzing the database Seconds_Behind_Master," so let's actually operate it! Here is the website, more related content can enter the relevant channels for inquiry, pay attention to 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.