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

This article is about the differences between sync_binlog parameters in MySQL 5.7 and between after_commit and after_sync in semi-synchronization. The editor thinks it is very practical, so share it with you as a reference and follow the editor to have a look.

The role of sync_binlog parameters in 5.7is discussed below.

I. the representation of the sync_binlog parameter setting in the source code

This parameter is known to control the timing of binlog flushing, but there is another function in 5.7.I will explain its two functions here. I extracted the illustrative part of the source code and showed it as follows:

Flush phase:

Flush_error= process_flush_stage_queue (& total_bytes, & do_rotate,&wait_queue); / / write binlog from binlog buffer or temporary file to binlog file (note that kernel buffer has not done fsync yet), and trigger the group submission logic of innodb. The logic code submitted by innodb group is written by Ali's Yinfeng brother. I have consulted him.

Update_binlog_end_pos_after_sync= (get_sync_period () = = 1); / / sync_binlog parameter is true if it is 1 or false if it is not 1

If (! update_binlog_end_pos_after_sync) / / if sync_binlog=1, there is no signal to dump. If it is not 1, signal dump.

Update_binlog_end_pos ()

Where the get_sync_period () function returns the setting of sync_binlog, and you can clearly see that if sync_binlog! = 1, the signal will be sent to the dump thread in the flush phase.

Sync stage

If (flush_error = = 0 & & total_bytes > 0) / / sync binlog here

{

DEBUG_SYNC (thd, "before_sync_binlog_file")

Std::pair result= sync_binlog_file (false)

Sync_error= result.first

}

If (update_binlog_end_pos_after_sync) / / if sync_binlog = 1, signal is sent to the dump thread to send binlog

{

THD * tmp_thd= final_queue

While (tmp_thd- > next_to_commit! = NULL)

Tmp_thd= tmp_thd- > next_to_commit

If (flush_error = = 0 & & sync_error = = 0)

Update_binlog_end_pos (tmp_thd- > get_trans_pos ())

}

If we open the logic of the sync_binlog_file function, we will find this logic:

If (force | | (sync_period & & + + sync_counter > = sync_period))

{

Sync_counter= 0

Obviously, there is a counter sync_counter. If sync_binlog > 1, fsync binlog will not be triggered until sync_counter is greater than the value of sync_binlog you set (note that + + sync_counter increments and then compares). This also explains what the value represents when sync_binlog > 1, which represents the number of group submissions.

2. Summary of the function of sync_binlog parameters in 5. 7.

Sync_binlog=0:binlog does not FSYNC flushing, but depends on the OS flushing mechanism. At the same time, the dump thread will be transmitted by binlog after the flush phase.

Sync_binlog=1:binlog performs FSYNC flushing, while the dump thread is transmitted by binlog after the sync phase.

Sync_binlog > 1:binlog will refresh FSYNC after the specified number of group submissions, and the binlog thread will be transmitted after the flush phase.

Third, why is it so modified?

This is also a question that a friend asked me: if the master library is restarted abnormally, whether the slave library has the risk of doing more things than the master database. In fact, this question is the question of at what stage the dump thread transmits binlog. In fact, if this problem does occur in the transfer after the flush phase, and the binlog has been closed by the time the transfer after the sync phase, there will be no such risk. If such an error occurs, the error will be reported as follows, which has also been encountered by some friends:

ER_SLAVE_HAS_MORE_GTIDS_THAN_MASTER

"Slave has more GTIDs than the master has, using the master's SERVER_UUID. This may indicate that the end of the binary log was truncated or that the last binary log file was lost, e.g., after a power or disk failure when sync_binlog! = 1. The master may or may not have rolled back transactions that were already replicated to the slave. Suggest to replicate any transactions that master has rolled back from slave to master, and/or commit empty transactions on master to account for transactions that have been committed on master but are not included in GTID_EXECUTED."

Let's start to discuss the difference between after_commit and after_sync in semi-synchronization, which has always been the focus of discussion.

Fourth, discuss from a problem.

Many friends have asked me, and one of the questions is as follows:

Semi-synchronous: after_sync mode

Test: the timeout time is set to 1 hour to prevent the main library from switching async, while stopping the slave Icano thread.

Main library:

Session1: insert a piece of data hang to live

Session2: insert a piece of data hang to live

Session3: insert a piece of data hang to live

The session1 status is waiting for ACK, and the other session status is query end

Ask why other session states are not waiting for ACK but query end. If it is after_commit mode, it is all waiting for ACK state.

In fact, after getting this friend's pstack, you can probably identify what the problem is as follows:

Wait for the ACK thread:

Thread 7 (Thread 0x7f44607aa700 (LWP 24897)):

# 0 0x00007f4475b02a5e in pthread_cond_timedwait@@GLIBC_2.3.2 () from / lib64/libpthread.so.0

# 1 0x00007f44603e35d3 in ReplSemiSyncMaster::commitTrx (char const*, unsigned long long) () from / usr/local/mysql/lib/plugin/semisync_master.so

# 2 0x0000000000c8197a in Binlog_storage_delegate::after_sync (THD*, char const*, unsigned long long) ()

# 3 0x0000000000edd46b in call_after_sync_hook (THD*) ()

# 4 0x0000000000eed935 in MYSQL_BIN_LOG::ordered_commit (THD*, bool, bool) ()

# 5 0x0000000000eedf55 in MYSQL_BIN_LOG::commit (THD*, bool) ()

# 6 0x000000000081e494 in ha_commit_trans (THD*, bool, bool) ()

# 7 0x0000000000dce032 in trans_commit_stmt (THD*) ()

# 8 0x0000000000d134e7 in mysql_execute_command (THD*, bool) ()

Wait for the LOCK_commit mutex thread

Thread 6 (Thread 0x7f4460769700 (LWP 25017)):

# 0 0x00007f4475b05334 in _ _ lll_lock_wait () from / lib64/libpthread.so.0

# 1 0x00007f4475b0060e in _ L_lock_995 () from / lib64/libpthread.so.0

# 2 0x00007f4475b00576 in pthread_mutex_lock () from / lib64/libpthread.so.0

# 3 0x0000000000eed31f in MYSQL_BIN_LOG::change_stage (THD*, Stage_manager::StageID, THD*, st_mysql_mutex*, st_mysql_mutex*) ()

# 4 0x0000000000eed5e8 in MYSQL_BIN_LOG::ordered_commit (THD*, bool, bool) ()

# 5 0x0000000000eedf55 in MYSQL_BIN_LOG::commit (THD*, bool) ()

# 6 0x000000000081e494 in ha_commit_trans (THD*, bool, bool) ()

# 7 0x0000000000dce032 in trans_commit_stmt (THD*) ()

It is clear here that other submitters (in this case, Thread 6) are stuck on the MYSQL_BIN_LOG::change_stage function, which is used to get the Mutex at a certain stage. This thread (in this case, Thread 7) is blocked on a Mutex on ReplSemiSyncMaster::commitTrx.

V. Code location and difference between after_commit and after_sync

This is given directly in the code description, but of course I just extracted the code snippet that illustrates the problem:

Commit phase:

1. Change_stage (thd, Stage_manager::COMMIT_STAGE,final_queue, leave_mutex_before_commit_stage,&LOCK_commit) / / enter the commit phase by holding LOCK_commit mutext

2. Sync_error= call_after_sync_hook (commit_queue); / / call after sync hook here, which has not yet done the engine layer commit under the protection of LOCK_commit.

3. Process_commit_stage_queue (thd, commit_queue); / / submit operation in engine layer. Details will be studied later.

4. Mysql_mutex_unlock (& LOCK_commit); / / submit the unlock queue here

5. Stage_manager.signal_done (final_queue); / / wake up all the follower threads blocked in the flush phase to commit respectively, but if the order commit submission has been done, nothing needs to be done here

6. (void) finish_commit (thd); / / finish_commit will call atfer commit hook, which is not under LOCK_commit protection

If you put aside the code, the summary is as follows:

1. Leader holds the LOCK_commit lock and enters the commit phase.

2. If after_sync is set, use the after sync hook to confirm the ack.

3. Submit the engine layer, and unlock the LOCK_commit lock after completion.

4. Wake up all follwer threads.

5. If the setting is after_commit, use the after commit hook to confirm ack.

Here we can clearly see that the difference between them, as their name is, is to confirm the completion of log transfer at that step, before or after the actual engine layer submission, under the protection of mutex LOCK_commit, and if later, there is no need to hold LOCK_commit mutex, which is the reason for the above congestion case. It is obviously more secure when after_sync is set to after_sync by default in 5.7. in extreme cases, it is possible that the engine layer has been committed and things are visible to the master library, but the slave library has not been transferred yet. there may be fewer things if the slave library collapses.

Thank you for reading! This is the end of the article on "what are the differences between sync_binlog parameters in MySQL 5.7 and after_commit and after_sync in semi-synchronization". I hope the above content can be of some help to you, so that you can learn more knowledge. if you think the article is good, you can share it for more people to see!

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.