MySQL slave library Seconds_Behind_Master delay summary

MySQL slave library Seconds_Behind_Master delay summary

1. Delay Classification

We can divide latency into two categories:

1. The first category (the server has a high load)

This type of delay may cause the server to have a higher load, possibly CPU/IO load. Because the slave library actually executes the Event, if the load on our server is high, we should consider these situations. For information on how to view the load of the thread, please refer to Section 29.

The delay caused by large transactions does not start from 0, but directly starts from how long the main database has been executed. For example, if the main database takes 20 seconds to execute this transaction, the delay will start from 20, which can be easily seen by careful observation. This is because there is no accurate execution time in the Query Event, which is described in detail in the calculation formula in the previous section. Please refer to Sections 8 and 27.

The delay caused by large table DDL will increase from 0 because Query Event records the exact execution time. This has been described in detail in the calculation formula in the previous section. Please refer to Sections 8 and 27.

The table does not properly use primary keys or unique keys to cause delays. In this case, do not think that setting the slave_rows_search_algorithms parameter to INDEX_SCAN, HASH_SCAN can completely solve the problem. The reason is described in Section 24.

This is caused by unreasonable parameters such as sync_relay_log, sync_master_info, and sync_relay_log_info. In particular, sync_relay_log will greatly affect the performance of the slave library. We have described the reason in Section 26, because setting sync_relay_log to 1 will cause a large number of relay log disk flushing operations.

Check whether the binary log function is enabled in the slave database, that is, the log_slave_updates parameter is enabled. If it is not necessary, it can be turned off. I have encountered this situation many times.

2. The second category (will not cause a high load on the server)

This type of delay does not usually cause a high load on the server. They either do not actually execute the Event, or they are caused by special operations.

• Long-term uncommitted transactions may cause a momentary increase in latency, because GTID_EVENT and XID_EVENT are commit times and other events are command initiation times. We have described this with examples in Section 27.
• The delay caused by the row lock of the Innodb layer is caused when there is a modification operation on the slave database and there is a conflict with the data modified by the SQL thread. As we said in Section 23 above, the SQL thread will also start a transaction and obtain a row lock when executing an event. Let's test it below.
• The delay caused by MDL LOCK at the MySQL layer may be caused by the SQL thread executing certain DDL operations but locking the table on the database. The reason has been described in Section 23. Let's test it below.
• This is caused by the unreasonable setting of the slave_checkpoint_period parameter in MTS, which has been tested in Section 27.
• The slave server time was manually increased during the slave operation, which has also been tested in Section 27.

2. Related tests

Because we have already tested and described many of the above delay situations. Next we test the delays caused by locks.

1. Delay caused by row locks at the Innodb layer

This is easy to test. I just need to make a transaction in the slave database and modify the same data as the SQL thread. The test is as follows:

From the 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 submit the same statement to the main database: mysql> delete from tmpk;
Query OK, 4 rows affected (0.30 sec)

At this point you will observe the following delay:

If you check sys.innodb_lock_waits, you can see the following results:

Of course, if you check INNODB_TRX, you can also observe the existence of transactions. I won’t take a screenshot here, you can try it yourself.

2. Delay caused by MDL LOCK at the MySQL layer

This situation is also very easy to test. We only need to open a transaction and do a select, and then the main database performs DDL on the same table, and the following results will appear:

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

At this time you will see the following information:

We can see from the state that this is the delay caused by waiting for MDL lock acquisition. For more information about MDL lock, please refer to the article:

https://www.jb51.net/article/221412.htm

Conclusion

Through the entire series, we should be clear about the calculation method of Seconds_Behind_Master. At the same time, if there is a delay, we first check whether the slave library has load, and treat it differently depending on whether it has load. Note that the load here must use top -H to view the load of io/sql/worker threads. I have met friends who asked me about latency issues more than once. When I asked them about the load, they told me that the load was not high and the overall load was less than 2. What we should note here is that only one CPU core can be used for one線程. Although the overall load is less than 2, the io/sql/worker threads may be full. In fact, the load is already very high. Let's take a look at the following screenshot, which is a screenshot of the sql thread with high load:

From this screenshot, we can see that although the overall load is not high at just over 1, the thread with Lwp number 20092 is already fully loaded. This線程is our SQL thread, and it is very likely that there will be a delay at this time. This screenshot is from a case of delay caused by improper use of primary keys or unique keys. The case is as follows:

https://www.jb51.net/article/221396.htm

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

The above is the details of the MySQL slave library Seconds_Behind_Master delay summary. For more information about the slave library Seconds_Behind_Master delay summary, please pay attention to other related articles on 123WORDPRESS.COM!