Shulou(Shulou.com)06/02 Report--

How is MYSQL 8 log system better than MYSQL 5.X? I believe many inexperienced people don't know what to do about it. Therefore, this article summarizes the causes and solutions of the problem. Through this article, I hope you can solve this problem.

In fact, there are two problems in the log system of MYSQL 5.x, which lead to some performance problems, especially in the case of high concurrent writes and data modification. the bottleneck lies in two bottlenecks. When multi-thread access data falls into log_buffer, it still needs to acquire locks to make writes sequential. at the same time, after the acquisition redo has written the data page There is also a rearrangement of the order written to the log in log_buffer, which also requires sequencing.

If there is sequence, there will be lock requirements, otherwise the hard requirements of the system can not be guaranteed, so performance problems will arise in this part. The following source code, log_sys- > mutex, log_sys- > flush_order_mutex, creates two locks for log writes.

The main reason why MYSQL 8 wants to solve this problem is that the use of multi-CPU in MYSQL is more and more supported, and the bottleneck lies in the contradiction between concurrency and sequence.

MYSQL 8 solves the problem of locks in a different way, because records must be sequenced, and locks are not added. Here MYSQL 8 uses the method of reserving space to reserve space for unwritten logs, and then

Through the way of reservation, the log_sys- > mutex data lock is removed, and the space reserved can be written concurrently, which ensures the order of the previous part, but there is no mandatory order of writing.

But this does not completely solve the problem, the log_sys- > flush_order_mutex lock is to solve the lock eventually brushed into the log. How to avoid this lock.

MYSQL 8 proposes a new data structure to solve the following problems

1 the filled data is not completely filled, and there are empty problems in it

2 how can you continue to brush the information into the log in an orderly manner without using locks

Here, MYSQL 8 uses log_ write threads to detect whether there is a hole in the LOG BUFFER in the log. If there is a write, it will wait and use union to confirm whether there is a hole in the log written by LOG BUFFER.

The above figure is the official diagram, through the union can quickly know whether a block is empty, write data to log buffer from left to right, and it seems to be disordered, but the disorder is ultimately orderly writing, but no longer use the original two locks to control writing data.

Through a more detailed design, the bottleneck in the original data can be resolved, which has something in common with MYSQL GROUP REPLICATION's idea of resolving the bottleneck of original data replication.

The corresponding dirty page in log buffer is not added to flush list in order. After writing the log buffer, users can add the corresponding dirty page corresponding to the log buffer to the flush list list. InnoDB uses a recent_closed to record whether the log buffer added to the flush list is continuous, so it is easy to find out that the lsn obtained by page lsn-recent_closed.size () on the flush list is used for checkpoint while the Log_closer thread in the InnoDB background periodically checks whether the recent_closed is continuous, and if it is continuous, push the recent_closed buffer forward. Then the checkpoint message can also be pushed forward.

Here is a summary of the overall data writing process after the improvement of MYSQL 8 from a post.

After going through the improvements related to MYSQL 8 as a whole, the following problems have been found

1 the design with locks is relatively simple, but the design with locks has poor concurrency.

(2) the lock-free design is complex, in which the middle section is the aspect of optimization, which changes the original orderly writing into unordered writing, which increases the performance bottleneck of writing caused by the sequential + lock mode.

Although it is a lock-free design, the final data writing is no different from the original design, and it is orderly.

It is felt that the relevant data processing mode has changed from whole to decentralized processing. In repositioning the whole, the way of multi-thread concurrent writing is added, and part of the data is refreshed to disk to improve the writing speed.

After reading the above, do you know how MYSQL 8 logging system is better than MYSQL 5.X? If you want to learn more skills or want to know more about it, you are welcome to follow the industry information channel, thank you for reading!

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.