Interpreting MySQL 5.7 Performance and data security improvements from the source code

Guide

It is well known that in MySQL, the Binlog log of a transaction is written to the Binlog file before the transaction actually commits, and in the 5.7 version of MySQL, the function of so-called lossless replication is provided-that is, before the transaction of the main library is visible to the other session threads, The log of the transaction is synchronized to the slave library, ensuring that the transaction can be copied safely and without loss to the slave library.

Below we analyze the MySQL transaction commit from the source and when the transaction copies the Binlog to the slave library.

Mysql_bin_log::ordered_commit, this is the core function that the transaction commits in the Binlog phase, through which a transaction log is implemented to write the Binlog file, and the trigger dump thread sends Binlog to slave, in the final step, Sets the transaction to the commit state.

Let's analyze the core process of the Mysql_bin_log::ordered_commit function, which is located in the binlog.cc file.

SOURCE parsing

Mysql_bin_log::ordered_commit, this function, the core steps are as follows:

First step: Flush

stage#1:flushing transactions to binary log:

Step 1: Write the log of the transaction into the buffer of the Binlog file, the function is as follows:

Process_flush_stage_queue (&total_bytes,&do_rotate, &wait_queue);

Starting with 5.6, MySQL introduced the concept of group commit, which prevents each transaction commit from locking one binlog at a time. In addition, there is a use of MySQL 5.7 based on logical_clock parallel replication. In a group (in fact a queue), the head transaction of this group of queues is the same, so the transaction of last_committed (the last committed transaction of the previous group) is the same for this group of transactions. We all know that last_committed the same transaction is possible in parallel relay (replay) from the library. The function of Process_flush_stage_queue is to take the thread in the commit queue out one at a time and then execute the Sub function flush_thread_caches (head); The loop code is as follows: Binlog in the respective thread The cache is written to the Binlog.

/* Flush thread caches to binary log. */for (THD *head= first_seen; head; head = head->next_to_commit) {std::p air<int,my_off_t>result= Flush_thread_ Caches (head); total_bytes+= result.second;if (Flush_error = = 1) flush_error= result.first; #ifndef dbug_offno_flushes++ ; #endif}

Second Step sync to disk

Stage#2:syncing binary log file to disk

Step two: Write the cache portion of the Binlog file to disk. But this step parameter Sync_binlog plays a decisive role.

We take a look at the source code, in addition to these details of the steps, after reading the source analysis, you should have a new harvest and understanding.

Before performing a real binlog write to disk, a wait is performed, with the following function:

Stage_manager.wait_count_or_timeout (opt_binlog_group_commit_sync_no_delay_count,opt_binlog_group_commit_sync_ Delay,stage_manager::sync_stage);

The time to wait is determined by the two parameters in the MySQL parameter file, Binlog_group_commit_sync_delay,binlog_group_commit_sync_no_delay_count, The first represents the total number of transactions to wait before the transaction group commits, and the second parameter indicates how long the transaction group waits for a total of one commit, followed by any condition that satisfies it. Because of this wait, more transactions can be binlog by writing the Binlog file disk once to complete the commit, thus achieving higher throughput.

Next, is the execution of Sync_binlog_file, the function will use the MySQL parameter file in the value of the Sync_binlog parameter, if it is 0, do not write disk operations, the operating system determines when the disk, if 1, the disk is forced to write operations.

Next, execute the UPDATE_BINLOG_END_POS function to update the last position of the Binlog file Binlog_end_pos, the Binlog_end_pos is a global variable. Before performing an update of the location, the last thread to commit the transaction (because it is a mechanism for group commit, multiple transactions queued commits) is first found. Because the thread that is committing the transaction is made up of a list, the last thread can be found by looking through it from the beginning to the end. The code is as follows:

if (update_binlog_end_pos_after_sync) {thd*tmp_thd= Final_queue;while (tmp_thd->next_to_commit! = NULL) tmp_thd= Tmp_thd->next_to_commit;update_binlog_end_pos (Tmp_thd->get_trans_pos ());}

Next, let's take a look at this function Update_binlog_end_pos, this function is very simple, passing in a POS, and then assigning it to the global variable Binlog_end_pos, followed by the core of the line of Code, Signal_update (), Sends the Binlog update signal, so the binlog from the main library to the dump thread from the library receives a signal that the Binlog has been updated and then initiates the dump Binlog process.

The complete code for the function Update_binlog_end_pos is as follows.

Semisync

Through the above steps, we see that, in the latest location of the Binlog file update, the Signal_update function has been sent to the Binlog dump thread, the thread can synchronize the binlog of the transaction to the slave library, from the library to receive the log, Can relay log, realize the master-slave synchronization. Therefore, repeat again, as explained above, before the transaction really commits to complete the start of sending the Binlog has been updated signal, the dump thread received a signal, that can be Binlog synchronization. And what is the role of Semisync?

In fact, there is no semisync mechanism, the above description of the MySQL transaction related to the Binlog process is the same, the role of Semisync, just a confirmation process between the master and slave, the main library waiting to return from the library relevant location Binlog has been synchronized to the confirmation from the library, (While the actual implementation is to wait for the dump thread to give a reply to the user's session thread), before it is confirmed (or the wait time reaches a timeout), the transaction commit waits on the function (step) until it gets returned. The confirmation function for the specific execution Binlog has been synchronized to a location is repl_semi_report_binlog_sync, and the function is as follows:

int Repl_semi_report_binlog_sync (Binlog_storage_param *param,constchar *log_file,my_off_t log_pos) {if (rpl_semi_ Sync_master_wait_point = = Wait_after_sync) return Repl_semisync.committrx (Log_file, log_pos); return 0;}

By observing the above function, we can see that there is a rpl_semi_sync_master_wait_point variable compared to the Wait_after_sync, if not equal, then return directly, the direct return indicates that it is not necessary at this moment to confirm that the Binlog is synchronized , and the value of this variable comes from the initial setting of the semi-synchronous parameter semi_sync_master_wait_point, which we can set to After_sync and After_commit. The difference between the two parameters means that After_sync is a log synchronization confirmation after Binlog sync to disk (specifically whether the true sync is determined by the value of the parameter Sync_binlog), and After_ Commit is a synchronous acknowledgment of the Binlog after the transaction is completed in InnoDB. The time difference between the two is confirmed, After_sync is earlier than After_commit.

Next, let's take a look at the Repl_semisync.committrx function, which has two incoming parameters, one is the Binlog file, and the displacement of a binlog file. Let's take a look at the meaning of this function. Forget it, or just use the source code to explain the comments.

650) this.width=650; "class=" Alignnone size-full wp-image-68421 "src=" http://www.linuxprobe.com/wp-content/uploads/ 2017/05/mysql5-performance-1.png "width=" 632 "height=" 597 "style=" Height:auto; "/>

The comment above makes it quite clear that the Commitrx function waits for Binlog-dump to return a report that has been synchronized to that location, and if it has not yet synchronized to that location, continue waiting until the timeout is returned.

When the session thread receives the return of the function, the commit process of the transaction continues down until the InnoDB is actually committed.

Summarize

Through the preceding analysis of the transaction submission process of MySQL, we should be able to understand the difference between the synchronization mechanism and the asynchronous mechanism of semi-sync, and there is no difference between the master-slave synchronization mechanism and the asynchronous mechanism in the synchronous processing mode of Semi-sync. The only difference is that semi-sync in the middle of a transaction commit (if set to After_sync) or after the commit phase (AFTER_COMMIT), there is a verification that the transaction involved in the Binlog has been synchronized to the slave library, and this synchronization validation, will lengthen the entire transaction commit time , because transactional commits are almost serial in the database (if the group commit is a unit, even if it is completely serial), it is a key point that affects MySQL throughput, and when the key is stretched, the impact on the global is magnified. While there is only such a confirmed action, the synchronization state of the main library in Semisync is several times different than the throughput of the asynchronous state. The explanation above is the real reason.

Original address: http://www.linuxprobe.com/mysql5-performance-improvement.html

This article is from the "blog" blog, please be sure to keep this source http://coderhsf.blog.51cto.com/12629645/1929622

Interpreting MySQL 5.7 Performance and data security improvements from the source code