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

What is the impact of MYSQL sync_relay_log on I thread? in view of this problem, this article introduces the corresponding analysis and solution in detail, hoping to help more partners who want to solve this problem to find a more simple and feasible method.

After setting up a set of slave libraries, it was found that the delay was very high and could not catch up all the time. The bin_log of the slave library was not opened and the flush_log_at_trx_commit was set to 0.

The state of simplification is as follows:

Mysql > show slave status\ G

1. Row

Slave_IO_State: Queueing master event to the relay log

Master_Log_File: mysql-bin.000533

Read_Master_Log_Pos: 101151159

Relay_Log_File: relaylog.000012

Relay_Log_Pos: 897176

Relay_Master_Log_File: mysql-bin.000533

Exec_Master_Log_Pos: 99357144

Seconds_Behind_Master: 11313

It is found that the progress of Master_Log_File,Read_Master_Log_Pos has been relatively slow, and generally speaking, the bottleneck of the intranet will not be in the network, so it is only possible in Icano.

Check the server Icano as follows:

It is found that the thread of MYSQL thread LWP number 44706 is very high, but the write is only about 600K, which is obviously abnormal. Generally speaking, LINUX

It would be nice to have KERNEL BUFFER/CACHE,write just to write to KERNEL BUFFER/CACHE, with the exception of writing to dirctor, this way.

It relies on user-mode caching, as well as writing system calls that call a large number of synchronous kernel cache/buffer to disk, such as fsync.

Then check to see if the LWP number is as follows, because it is very easy to find the relationship between MYSQL conn_id and system LWP as follows:

It is really found that this large number of iMager is indeed MYSQL from the library's Imando O thread, then the next step is to strace to see why it is so slow, strace.

The clip is as follows:

We find that the file with file descriptor FD = 50 has a large number of writes and frequently calls fdatasync to synchronize the disk, which takes a lot of time, such as MUTEX calls and write.

N times of operation

So let's see what the file descriptor 50 is as follows:

It is indeed our replay log.

So the problem is determined, it is because the write of replay log calls a large number of fdatasync so that the I _ THREAD is very slow, so which parameter is it?

In fact, the parameter is sync_relay_log, which is used to ensure the security of relay log. The official document shows the following figure:

GTID | sync_relay_log | MASTER_AUTO_POSITION | relay_log_recovery | relay_log_info_repository | Crash type | Recovery guaranteed | Relay log impact

OFF 1 Any 1 TABLE Any Yes Lost

OFF > 1 Any 1 TABLE Server Yes Lost

OFF > 1 Any 1 Any OS No Lost

OFF 1 Any 0 TABLE Server Yes Remains

OFF 1 Any 0 TABLE OS No Remains

ON Any ON Any Any Any Yes Lost

ON 1 OFF 0 TABLE Server Yes Remains

ON 1 OFF 0 Any OS No Remains

We can see that if we do not set sync_relay_log, there may be a risk of losing relay log. In fact, the above analysis has seen that fdatasync is called to complete this function, but

Such a price is basically unacceptable. The official document states as follows:

It is important to note the impact of sync_relay_log=1, which requires a write of to the relay log

Per transaction. Although this setting is the most resilient to an unexpected halt, with at most one

Unwritten transaction being lost, it also has the potential to greatly increase the load on storage. Without

Sync_relay_log=1, the effect of an unexpected halt depends on how the relay log is handled by the

Operating system.

A value of 1 is the safest choice because in the event of a crash you lose at most one event from the

Relay log. However, it is also the slowest choice (unless the disk has a battery-backed cache, which

Makes synchronization very fast).

Every thing calls fdatasync, which is too expensive. So there is no way to change the setting of sync_relay_log. The default value is 10000, that is, 10000 things at a time.

Fdatasync .

This is the answer to the question about the impact of MYSQL sync_relay_log on MYSQL sync_relay_log O thread. I hope the above content can be of some help to you. If you still have a lot of doubts to be solved, you can follow the industry information channel for more related knowledge.

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.