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

This article will explain in detail what is the efficiency difference case on different machines with the same sql. The editor thinks it is very practical, so I share it with you as a reference. I hope you can get something after reading this article.

A user problem, data migration from ECS to RDS, the same sentence, query performance degraded dozens of times. In fact, the memory configuration of the RDS instance is similar to that of the original ECS instance.

This article briefly explains the reasons and suggestions for this case. The statement that user feedback slows down is (real table name and column name modified) select count (1) from HR hr join H h on h.hid = hr.hid join An e on e.aid = h.eid join A t on t.aid = e.pid join A c on c.aid = t.pid join A p on p.aid = c.pid left join U u on u.uid = hr.uId left join E emp on emp.eid = hr.oid where (hr.s in (1mem2) 3) 4) and hr.cn = 0)

Background

There are two major processes involved in the execution of statements in MySQL: optimizer and executor. Among them, the most important task of the optimizer is to select the index and the join order when joining multiple tables. In this case, the choice of join order affects execution performance. Determining the order in which join is executed requires an estimate of the cost of all join operations. By default, MySQL estimates all possible combinations. MySQL Tips: the maximum number of join tables for a query in MySQL is 61. 1. For a join operation with 61 tables participating, it theoretically requires 61! (factorial) evaluation of times. Of course, this is the worst-case scenario, and the branch reduction algorithm actually makes the number look a little better, but it's still scary. In the multi-table join scenario, in order to avoid the optimizer taking up too much time, MySQL provides a parameter optimizer_search_depth to control the recursion depth. The control of this parameter to the algorithm can be described simply as follows: for all permutations, only the first join table of the current optimizer_search_depth order is taken to estimate the cost. For example, for 20 tables, assuming that optimizer_search_depth is 4, then the number of evaluations is 20: 19: 18: 17, although it is also very large (so we particularly do not recommend so many tables join), than 20! Much better. So the choice of optimizer_search_depth becomes a problem. MySQL Tips: the default value of optimizer_search_depth in MySQL is 62. That is to say, the default is full permutation calculation. This ensures an optimal execution plan, but in some scenarios, the time to decide to execute the plan is much longer than the execution time itself.

Quantitative analysis

On ECS, the MySQL is maintained by the user, and optimizer_search_depth is not set, so it is the default 62. 0.

On RDS, our configuration is 4.

From the analysis here, you can guess that the reason is that the 4 configuration of RDS leads to the failure to get the optimal execution plan.

The following figure shows the explain results of optimizer_search_depth=4 (hiding business-related table names and field names). The following figure shows the scenario of optimizer_search_depth=62. Of course, the join table of this case is 8, so 62 and 8 are equivalent here. As you can see from figure 1, because of optimizer_search_depth=4, the optimizer considers itself to have chosen the best join order (22039), which is better than (41360), when in fact the latter is the global optimal. Interestingly, if you look at one more layer in this case, you will get the most solution, because the fifth table in the first join order has an evaluation rows of 82720. This means that in this case, setting to 5 will get the same execution plan as setting 62, although the optimizer will cost less to execute when set to 5. In fact, this is the original intention of providing optimizer_search_depth: reduce the execution time of the optimizer, and there is a probability that the local optimal is the global optimal solution.

About practice

While the configurable parameters provide flexibility, it also raises the headache question of how much should be set to be appropriate.

In fact, when a user executes a multi-table join, the expectation of the overall RT of the statement is not high. Therefore, you can define an expectation first, for example, the optimizer cannot decide the join order for longer than the 500ms.

The user specification is related to cpu, so this can only be a recommended value.

User practice

In fact, what is more important is for users:

1) when there is a big difference in the execution results of multi-table join after instance migration, you should consider adjusting this value. This parameter allows threads to set separately, so for the application layer, each connection should get a better value. 2) conversely, when set to the default optimizer_search_depth=62, how do we evaluate whether our setting is too large? MySQL Tips:MySQL profiling can be used to view the time consumed by each execution. The following is a 60-table join query constructed by the author, using profiling to view the process of execution consumption. Set profiling=1; set optimizer_search_depth=4; explain select. Show profile for query 2; as a result, continue to execute set optimizer_search_depth=40; explain select. Show profile for query 4; the red section of the figure shows the difference in execution time between the two optimizers.

Summary

1) estimate an acceptable time based on the machine configuration for the optimizer to select the join order. 2) use profiling to determine whether too large optimizer_search_depth is set. 3) Business optimization, try not to use multi-table join with more than 10 tables. 4) PS: don't trust the silver bullet. The MySQL document says that a setting of 0 means that the reasonable value of optimizer_search_depth can be automatically selected. In fact, the code strategy is that if the number of table N in join

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.