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

Today, the editor shares with you a detailed explanation of the parallel execution of MySQL8.0 InnoDB, which many people don't know very well. today, in order to let you know more about MySQL8.0, I have summarized the following contents for you. Let's look down together. I'm sure you'll get something.

Overview

After years of development, MySQL has become the most popular database, widely used in the Internet industry, and gradually infiltrated into various traditional industries. On the one hand, it is popular because of its excellent ability to handle highly concurrent transactions, and on the other hand, it also benefits from the rich ecology of MySQL. MySQL is very effective in dealing with short queries in OLTP scenarios, but its ability is limited for complex large queries. The most straightforward point is that for a SQL statement, MySQL can only use one CPU core at most. In this scenario, the multi-core capability of the host CPU cannot be brought into full play. MySQL is not stagnant and has been developing all the time. The new version 8.0.14 introduces parallel queries for the first time, which makes the performance of statements of check table and select count (*) types increase exponentially. Although the current use scenario is still limited, but the follow-up development is worth looking forward to.

Mode of use

By configuring the parameter innodb_parallel_read_threads to set the number of concurrent threads, you can start parallel scanning, which defaults to 4. I do a simple experiment here, import 200 million pieces of data through sysbench, respectively configure innodb_parallel_read_threads to 1Magne, 2meme, 4meme, 8meme, 32pint 64, to test the effect of parallel execution. The test statement is select count (*) from sbtest1

The horizontal axis is the number of configured concurrent threads, and the vertical axis is the statement execution time. From the test results, the whole parallel performance is good, scanning 200 million records, from 18 seconds of single thread to 1 second of 32 threads. Due to the limited amount of data, the management consumption of multithreading exceeds the performance improvement brought about by concurrency, and the SQL execution time can no longer be shortened.

MySQL parallel execution

In fact, the current parallel execution of MySQL is still in a very early stage, as shown in the following figure, on the left is the previous MySQL serial processing of a single SQL form; the middle is the parallel capability provided by the current MySQL version, the form of parallel scanning of the InnoDB engine; the rightmost is the form of future MySQL development, the optimizer generates a parallel plan according to the system load and SQL, and sends the partition plan to the executor for parallel execution. Parallel execution is not only parallel scanning, but also parallel aggregation, parallel join, parallel grouping, parallel sorting and so on. The optimizer and actuator in the upper layer of the current version of MySQL have not been modified. Therefore, the following discussion focuses on how the InnoDB engine implements parallel scanning, including partitioning, parallel scanning, pre-reading, and adapter classes that interact with executors.

Zoning

One of the core steps of parallel scanning is partitioning, dividing the scanned data into multiple parts and allowing multiple threads to scan in parallel. The InnoDB engine is an index organization table, and the data is stored on disk in the form of B+tree, and the unit of the node is the page (block/page). At the same time, hot pages are cached in the buffer pool and eliminated by LRU algorithm. The logic of partitioning is to scan down layer by layer from the root node page, and stop splitting when it is determined that the number of branches in a layer exceeds the number of configured threads. In implementation, there are actually two partitions, the first is partitioned according to the number of branches of the root node page, the leftmost leaf node of each branch is recorded as the lower left bound, and this record is recorded as the upper right bound of the adjacent previous branch. In this way, the B+tree is divided into several subtrees, each of which is a scan partition. After the first partition, there may be a problem that the number of partitions cannot be fully utilized. For example, if the parallel scanning thread is configured as 3, and after the first partition, four partitions are generated, then after the first three partitions are done in parallel, the fourth partition has at most one thread scan, and the final result is that multi-core resources cannot be fully utilized.

Secondary zoning

In order to solve this problem, version 8.0.17 introduces secondary partitions, and for the fourth partition, continue to drill down and split, so that multiple sub-partitions can be scanned concurrently, and the minimum granularity of concurrent scanning by the InnoDB engine is the page level. The logic of judging the secondary partition is that after the primary partition, if the number of partitions is greater than the number of threads, the number of partitions greater than the number of threads needs to continue with the secondary partition; if the number of partitions is less than the number of threads and the B+tree level is very deep, then all partitions need secondary partitions.

The related code is as follows:

Split_point = 0 if (ranges.size () > max_threads ()) {/ / the last partition makes a secondary partition split_point = (ranges.size () / max_threads ()) * max_threads ();} else if (m_depth)

< SPLIT_THRESHOLD) { /* If the tree is not very deep then don't split. For smaller tables it is more expensive to split because we end up traversing more blocks*/ split_point = max_threads(); } else { //如果B+tree的层次很深(层数大于或等于3，数据量很大)，则所有分区都需要进行二次分区 } 无论是一次分区，还是二次分区，分区边界的逻辑都一样，以每个分区的最左叶子节点的记录为左下界，并且将这个记录记为相邻上一个分支的右上界。这样确保分区足够多，粒度足够细，充分并行。下图展示了配置为3的并发线程，扫描进行二次分区的情况。 相关代码如下： create_ranges(size_t depth, size_t level)一次分区：parallel_check_table add_scan partition(scan_range, level=0) /* start at root-page */ create_ranges(scan_range, depth=0, level=0) create_contexts(range, index >

= split_point) Secondary partition: split () partition (scan_range, level=1) create_ranges (depth=0,level)

Parallel scanning

After a partition, each partition scanning task is put into a lock-free queue, and the parallel worker thread gets the task from the queue and executes the scanning task. If the acquired task has a split attribute, worker will split the task twice and put it into the queue. This process mainly includes two core interfaces, one is the worker thread interface, the other is the traversal record interface, the former gets the task from the queue and executes, and maintains the statistical count; the latter obtains the appropriate record according to the visibility, and processes it through the callback function injected by the upper layer, such as counting and so on.

Parallel_reader::worker (size_t thread_id)

{

1. Extract ctx tasks from ctx-queue

two。 Based on the split property of ctx, determine whether the partition needs to be further split (split ())

3. Traverses all records of the partition (traverse ())

4. Maintain m_n_completed count at the end of a partition task

5. If the m_n_compeleted count reaches the number of ctx, wake up all worker threads to end

6. According to the traverse interface, err information is returned.

}

Parallel_reader::Ctx::traverse ()

{

1. Set pcursor according to range

two。 Locate the btree and position the cursor at the start of the range

3. Determine visibility (check_visibility)

4. If visible, calculate according to the callback function (such as statistics)

5. Traverse backwards, and if the last record of the page is reached, start the pre-reading mechanism (submit_read_ahead)

6. Ends when it is out of range

}

At the same time, a pre-reading mechanism is introduced in version 8.0.17 to avoid the problem of poor parallel effect caused by IO bottleneck. Currently, the number of pre-read threads cannot be configured and is hard-coded as 2 threads in the code. Each pre-read unit is a cluster (InnoDB files are managed through a three-level structure of segments, clusters, and pages, and a cluster is a continuous set of pages), which may be 1m or 2m depending on the size of the page configuration. For the common 16k page configuration, read 1m at a time, that is, 64 pages. When scanning, the worker thread first determines whether the next adjacent page is the first page of the cluster, and if so, initiates a read-ahead task. Read-ahead tasks are also cached through the lock-free queue, where the worker thread is the producer and the read-ahead-worker is the consumer. Because all partitioned pages do not overlap, the read-ahead task is not repeated.

Actuator interaction (adapter)

In fact, MySQL has encapsulated an adapter class Parallel_reader_adapter for use by the upper layer in preparation for richer parallel execution. First of all, this class needs to solve the problem of record format by converting the records scanned by the engine layer into MySQL format, so that the upper and lower layers are decoupled, and the executors do not need to perceive the engine layer format and are processed according to the MySQL format. The whole process is a pipeline, through a buffer batch storage MySQL records, worker threads continue to read records from the engine layer, while records are constantly processed by the upper layer, through buffer can balance the difference between reading and processing speed to ensure that the whole process flows. The cache size defaults to 2m, which determines how many MySQL records buffer can cache based on the record length of the table. The core process is mainly in the process_rows interface, and the process is as follows

Process_rows

{

1. Convert engine records to MySQL records

two。 Get the buffer information of this thread (how many mysql records have been converted and how many have been sent to the upper layer)

3. Populate MySQL records into buffer and self-increment statistical m_n_read

4. Call callback function processing (such as statistics, aggregation, sorting, etc.), self-incrementing statistics m_n_send

}

For the caller, you need to set the meta-information of the table, as well as the injection processing record callback function, such as handling aggregation, sorting, and grouping work. The callback function is controlled by setting mendinitshifn massively loadingfn and m_end_fn.

Summary

Although the introduction of parallel query in MySQL8.0 is still relatively primary, it has shown us the potential of MySQL parallel query. From the experiment, we also see that after turning on parallel execution, SQL statement execution gives full play to the multi-core capability, and the response time decreases sharply. It is believed that in the near future, 8.0 will support more parallel operators, including parallel aggregation, parallel join, parallel grouping and parallel sorting.

After reading the above, do you have a general understanding of MySQL8.0 InnoDB parallel execution? If you want to know more, welcome to follow the industry information channel.

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.