Details
-
Bug
-
Status: Stalled (View Workflow)
-
Critical
-
Resolution: Unresolved
-
11.4.2, 11.1.6
Description
Hello,
Attached you will find a database dump that contains a part of my database.
With MariaDB version 11, I am experiencing a significant performance loss with a specific query.
With version 10.11, the following query takes only 0.036 seconds:
SELECT
|
S.*, B.*
|
FROM Teilnehmer_1 S
|
INNER JOIN Buchholzpunkte B ON B.TerID = S.TerID AND B.TnNr = S.TnNr;
|
134 rows in set (0.036 sec)
With version 11.4.2, the same query takes over 11 seconds.
134 rows in set (11.431 sec)
I tested this in a virtual machine using Docker with the images: mariadb:11.4.2 and mariadb:10.11.
If you need any further information, please let me know.
Best regards,
F.K
Attachments
Activity
Now we are ready for an analysis of the bug. We are going to debug the execution of our query using InnoDB tables.
When processing the query at first the optimizer proceeds like this:
1. The best non splitting plan is chosen for the derived table dt_i and its cost C_i is calculated.
2. The optimizer looks for the best plan non-splitting plan for the derived table dt_o evaluating costs of different possible join orders of the tables t3c,t2c,dt_i. As dt_i is joined by a left join operation with ON condition (dt_i.id=t3c.id) the optimizer considers only the following join orders: t3c->t2c->dt_i, t3c->dt_i->t2c, t2.c->t3c->dt_i.
3. When evaluating a certain join order where dt_i is joined to the partial join PJ the optimizer considers two options: to join dt_i as it is specified or to join dt_i with pushed equalities depending on tables from PJ that are useful for split. In our case for join order t3c->dt_i->t2c the pushed equality is dt_i.id = t3c.id, for the other two orders the pushed equalities are dt_i.id = t3c.id and and an inferred equality dt_i.id = t2c.id. The optimizer chooses the cheapest option here.
4. After the best non-splitting plan has been chosen for dt_o the optimizer fixes the the split choice for dt_i. It is done in the function JOIN_TAB::fix_splitting().
Let's set a breakpoint in JOIN_TAB::fix_splitting(). When we come here for the first time we see
(gdb) p table->alias
|
$35 = {<Charset> = {m_charset = 0x5555579975e0 <my_charset_bin>}, <Binary_string> = {<Static_binary_string> = {<Sql_alloc> = {<No data fields>}, Ptr = 0x7fff6401b800 "dt_i", str_length = 4}, Alloced_length = 0, extra_alloc = 0, alloced = false, thread_specific = false}, <No data fields>}
|
(gdb) p spl_plan
|
$36 = (SplM_plan_info *) 0x0
|
It means that no splitting has been chosen for dt_i in the best non-splitting planfor dt_o. This perfectly complies with the following:
MariaDB [test]> EXPLAIN
|
-> SELECT t3c.id, t3c.b, sum(dt_i.siege)
|
-> FROM
|
-> (
|
-> ( t3c JOIN t2c ON (t2c.id = t3c.id))
|
-> LEFT JOIN
|
-> (
|
-> SELECT t2.id, sum(CASE WHEN t2.a = t1.manr THEN 1 ELSE 0 END) AS siege
|
-> FROM
|
-> t1
|
-> JOIN
|
-> t2 ON t1.paid = t2.paid
|
-> GROUP BY t2.id
|
-> ) dt_i
|
-> ON (dt_i.id = t3c.id)
|
-> )
|
-> ;
|
+------+-------------+------------+--------+---------------------------+---------+---------+--------------+-------+----------------------------------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+------------+--------+---------------------------+---------+---------+--------------+-------+----------------------------------------------+
|
| 1 | PRIMARY | t3c | index | id,id_2 | id | 8 | NULL | 550 | Using index |
|
| 1 | PRIMARY | t2c | ref | id | id | 4 | test.t3c.id | 38 | Using index |
|
| 1 | PRIMARY | <derived2> | ref | key0 | key0 | 5 | test.t3c.id | 40 | |
|
| 2 | DERIVED | t1 | index | paid,paid_2,paid_3,paid_4 | paid_4 | 5 | NULL | 22338 | Using index; Using temporary; Using filesort |
|
| 2 | DERIVED | t2 | eq_ref | PRIMARY,id | PRIMARY | 4 | test.t1.paid | 1 | |
|
+------+-------------+------------+--------+---------------------------+---------+---------+--------------+-------+----------------------------------------------+
|
(Analysis of the bug continues).
5. Next the optimizer searches for the best plan for the top select. The possible join orders are t3->dt_o and dt_o->t3.
When the join order t3->dt_o is evaluated there are two options to join dt_o without split and with split of the derived table. The cost of the option without split C_o has been already calculated. Now the option with possible split is evaluated. The equalities that is useful for split of dt_o are dt_o.id = t3.id and dt_o.b = t3.b. Now the optimizer looks for the best splitting plan when these equalities are pushed into dt_o. Evaluating a certain join order of the tables from dt_o t3,t3,dt_i the optimizer calls JOIN_TAB::choose_best_splitting() for dt_i that leaves a pointer to the best split of dt_i for any split of dt_o. This pointer is set in the POSITION structure for dt_i in any splitting plan for dt_o. We see that finally the optimizer chooses a split for dt_o and JOIN_TAB::fix_splitting() called for dt_o preserves the pointer to a split of dt_i in the final chosen plan for dt_o.
So we see an inconsistency here: the execution plan for dt_i isn't splitting, but the plan for dt_o in the position for dt_i points to some splitting plan for dt_i.
As a consequence of this when at the invocation of JOIN::optimize_stage2() for dt_i the function JOIN::inject_best_splitting_cond() is called and it marks the select specifying dt_i as UNCACHEABLE_DEPENDENT_INJECTED. This causes refill of dt_i for every row of t3c that has been reached from a row of t3.
Here's how the functions
JOIN::optimize(),
|
mysql_derived_optimize(),
|
choose_plan(),
|
JOIN_TAB::choose_best_splitting(),
|
JOIN_TAB::fix_splitting()
|
interplay when the above query is being processed:
JOIN::optimize is called for the select of the whole query Q.
|
mysql_derived_optimize is called for the derived table dt_o
|
JOIN::optimize is called for the select of the derived table dt_o
|
mysql_derived_optimize is called for the derived table dt_i
|
JOIN::optimize is called for the select of the derived table dt_i
|
choose_plan is called for the select of the derived table dt_i
|
(to choose the best non-splitting plan for the select of dt_i)
|
choose_plan is called for the select of the derived table dt_o
|
(to choose the best non-splitting plan for the select of dt_o)
|
JOIN_TAB::choose_best_splitting is called for the derived table dt_i
|
(to determine the best splitting plan for the select of dt_i
|
for the join order prefix t3c,t2c)
|
choose_plan is called for the select of the derived table dt_i again
|
(to choose the best splitting plan for the select of dt_i
|
for the join order prefix t3c,t2c)
|
JOIN_TAB::choose_best_splitting is called for the derived table dt_i
|
(to determine the best splitting plan for the select of dt_i
|
for the join order prefix t3c)
|
JOIN_TAB::choose_best_splitting is called for the derived table dt_i
|
(to determine the best splitting plan for the select of dt_i
|
for the join order prefix t2c,t3c)
|
JOIN_TAB::fix_splitting is called for the derived table dt_i
|
(it determines that non-splitting plan of dt_i should be chosen
|
for non-splitting plan of dt_o)
|
choose_plan is called for the select of the query Q
|
(to choose choose the best plan for the select of Q)
|
JOIN_TAB::choose_best_splitting is called for the derived table dt_o
|
(to determine the best splitting plan for the select of dt_o
|
for the join order prefix t3)
|
choose_plan is called for the select of the derived table dt_o again
|
(to choose the best splitting plan for the select of dt_o
|
for the join order prefix t3)
|
( * ) choose_plan is called for the derived table dt_i again
|
(to choose the best splitting plan for the select of dt_i
|
for the join order prefix t3c,t2c,
|
now only for one split partition of dt_o)
|
( ** ) JOIN_TAB::choose_best_splitting is called for the derived table dt_i
|
(to determine the best splitting plan for the select of dt_o
|
for the join order prefix t3c
|
now only for one split partition of dt_o)
|
JOIN_TAB::choose_best_splitting is called for the derived table dt_i
|
(to determine the best splitting plan for the select of dt_i
|
for the join order prefix t2c,t3c,
|
now only for one split partition of dt_o)
|
JOIN_TAB::choose_best_splitting is called for the derived table dt_o
|
JOIN_TAB::fix_splitting is called for the derived table dt_o
|
(it determines that a splitting plan of dt_o should be chosen
|
in plan for the select of Q)
|
mysql_derived_optimize is called for the derived table dt_i
|
JOIN::optimize is called for the select of the derived table dt_i
|
(to refine the chosen plan for the select of the derived table dt_i)
|
mysql_derived_optimize is called for the derived table dt_o
|
JOIN::optimize is called for the select of the derived table dt_o
|
(to refine the chosen plan for the select of the derived table dt_o)
|
We see that at the step ( * ) the optimizer tries to get the best plan fot dt_i when evaluating a split partition of dt_o. Yet when it comes to ( ** ) it compares the cost of a splitting plan for dt_i used for the evaluated split partition of dt_o with the cost C_u of non-split plan for dt_i. This is not correct because materialization of dt_i can be used by each split partition of dt_o. So the comparison should be done only with the portion of C_u, more exactly with C_u/P where P is the the number of partition in the evaluated split plan for dt_o. As a result of this incorrect comparison the optimizer now chooses a splitting plan for dt_i in the final chosen plan for dt_o that happens to be a splitting plan. However this time no JOIN_TAB::fix_splitting() is called for the dt_i and the final execution plan for dt_i remains the same as it was chosen before i.e. an non-splitting plan in spite of the fact that the plan for dt_o says that it uses a splitting plan for dt_i. This forces the second stage of the optimization (JOIN::optimize_stage2() to mark the select of dt_i as uncachedble and it causes re-materialization of dt_i for each partition of dt_o.
Hello,
Unfortunately, there still doesn't seem to be a solution for this issue.
Today, I tested again with mariadb:11.8.1-rc and observed an interesting behavior. Perhaps this will help resolve the problem.
When I import the data using:
|
mariadb -uroot -p test < dump.sql
|
and immediately execute the query:
|
SELECT
|
S.*, B.*
|
FROM Teilnehmer_1 S
|
INNER JOIN Buchholzpunkte B ON B.TerID = S.TerID AND B.TnNr = S.TnNr;
|
the result is fast:
134 row(s) returned | 0.031 sec / 0.000 sec
For the next 10 seconds, I can repeatedly execute the query, and it remains fast. After these 10 seconds, the query becomes slow again:
134 row(s) returned | 10.625 sec / 0.000 sec
It seems that after a certain period, an "optimization" occurs, triggering the issue.
If I immediately run:
|
EXPLAIN SELECT
|
S.*, B.*
|
FROM Teilnehmer_1 S
|
INNER JOIN Buchholzpunkte B ON B.TerID = S.TerID AND B.TnNr = S.TnNr;
|
I get the following output:
+ ------- + ---------------- + ---------- + --------- + ------------------ + -------- + ------------ + -------- + --------- + ---------- +
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+ ------- + ---------------- + ---------- + --------- + ------------------ + -------- + ------------ + -------- + --------- + ---------- +
|
| 1 | PRIMARY | S | ALL | Key_TeaID_TerID_Teilnehmer_1,Key_TnNr_TerID_Teilnehmer_1,TerID_Teilnehmer_1,Ter_Tea_Teilnehmer_1 | | | | 1700 | |
|
| 1 | PRIMARY | <derived2> | ref | key0 | key0 | 8 | test.S.TerID,test.S.TnNr | 10 | |
|
| 2 | DERIVED | Te | ALL | PRIMARY | | | | 1 | Using where; Using temporary; Using filesort |
|
| 2 | DERIVED | P | ref | PRIMARY,TerID,TerRunde | TerID | 4 | test.Te.TerID | 39 | Using where |
|
| 2 | DERIVED | M1 | ref | PaID_LiMaNr_MaNr_TnNr,PaID,TnNr,idx_PaID_TnNr,idx_PaID_MaNr | idx_PaID_TnNr | 4 | test.P.PaID | 4 | Using index |
|
| 2 | DERIVED | Tn | eq_ref | Key_TeaID_TerID_Teilnehmer_1,Key_TnNr_TerID_Teilnehmer_1,TerID_Teilnehmer_1,Ter_Tea_Teilnehmer_1 | Key_TnNr_TerID_Teilnehmer_1 | 8 | test.Te.TerID,test.M1.TnNr | 1 | |
|
| 2 | DERIVED | M2 | ref | PaID_LiMaNr_MaNr_TnNr,PaID,idx_PaID_TnNr,idx_PaID_MaNr | idx_PaID_TnNr | 4 | test.P.PaID | 4 | Using where; Using index |
|
| 2 | DERIVED | <derived3> | ref | key0 | key0 | 5 | test.Te.TerID | 10 | Using where |
|
| 3 | DERIVED | T | index | PRIMARY | TuSID | 4 | | 1 | Using index; Using temporary; Using filesort |
|
| 3 | DERIVED | P | ref | PRIMARY,TerID,TerRunde | TerID | 4 | test.T.TerID | 39 | Using where |
|
| 3 | DERIVED | M | ref | PaID_LiMaNr_MaNr_TnNr,PaID,TnNr,idx_PaID_TnNr,idx_PaID_MaNr | PaID_LiMaNr_MaNr_TnNr | 4 | test.P.PaID | 4 | Using index |
|
+ ------- + ---------------- + ---------- + --------- + ------------------ + -------- + ------------ + -------- + --------- + ---------- +
|
11 rows
|
After 10 seconds (without any changes), the EXPLAIN output becomes:
+ ------- + ---------------- + ---------- + --------- + ------------------ + -------- + ------------ + -------- + --------- + ---------- +
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+ ------- + ---------------- + ---------- + --------- + ------------------ + -------- + ------------ + -------- + --------- + ---------- +
|
| 1 | PRIMARY | S | ALL | Key_TeaID_TerID_Teilnehmer_1,Key_TnNr_TerID_Teilnehmer_1,TerID_Teilnehmer_1,Ter_Tea_Teilnehmer_1 | | | | 1700 | |
|
| 1 | PRIMARY | <derived2> | ref | key0 | key0 | 8 | test.S.TerID,test.S.TnNr | 1 | |
|
| 2 | LATERAL DERIVED | Tn | eq_ref | Key_TeaID_TerID_Teilnehmer_1,Key_TnNr_TerID_Teilnehmer_1,TerID_Teilnehmer_1,Ter_Tea_Teilnehmer_1 | Key_TnNr_TerID_Teilnehmer_1 | 8 | test.S.TerID,test.S.TnNr | 1 | |
|
| 2 | LATERAL DERIVED | Te | eq_ref | PRIMARY | PRIMARY | 4 | test.Tn.TerID | 1 | Using where |
|
| 2 | LATERAL DERIVED | P | ref | PRIMARY,TerID,TerRunde | TerID | 4 | test.Tn.TerID | 39 | Using where |
|
| 2 | LATERAL DERIVED | M1 | ref | PaID_LiMaNr_MaNr_TnNr,PaID,TnNr,idx_PaID_TnNr,idx_PaID_MaNr | idx_PaID_TnNr | 8 | test.P.PaID,test.Tn.TnNr | 1 | Using index |
|
| 2 | LATERAL DERIVED | M2 | ref | PaID_LiMaNr_MaNr_TnNr,PaID,idx_PaID_TnNr,idx_PaID_MaNr | PaID_LiMaNr_MaNr_TnNr | 4 | test.P.PaID | 4 | Using where; Using index |
|
| 2 | LATERAL DERIVED | <derived3> | ref | key0 | key0 | 5 | test.Tn.TerID | 3 | Using where |
|
| 3 | DERIVED | P | ALL | PRIMARY,TerID,TerRunde | | | | 10135 | Using where; Using temporary; Using filesort |
|
| 3 | DERIVED | T | eq_ref | PRIMARY | PRIMARY | 4 | test.P.TerID | 1 | |
|
| 3 | DERIVED | M | ref | PaID_LiMaNr_MaNr_TnNr,PaID,TnNr,idx_PaID_TnNr,idx_PaID_MaNr | PaID_LiMaNr_MaNr_TnNr | 4 | test.P.PaID | 4 | Using index |
|
+ ------- + ---------------- + ---------- + --------- + ------------------ + -------- + ------------ + -------- + --------- + ---------- +
|
11 rows
|
Best regards,
F.K.
We got the above results for InnoDB tables t1,t2,t3,t2c,t3c after having run ANALYZE TABLE ... PERSISTENT FOR ALL for each of these tables. If we convert these tables into MyISAM tables and run ANALYZE TABLE ... PERSISTENT FOR ALL for them we see the same problem as with InnoDB tables. The execution plans remain practically the same
MariaDB [test]> set optimizer_switch='split_materialized=on';Query OK, 0 rows affected (0.000 sec)MariaDB [test]> EXPLAIN-> SELECT 1-> FROM-> t3-> JOIN-> (-> SELECT t3c.id, t3c.b, sum(dt_i.siege)-> FROM-> (-> ( t3c JOIN t2c ON (t2c.id = t3c.id))-> LEFT JOIN-> (-> SELECT t2.id, sum(CASE WHEN t2.a = t1.manr THEN 1 ELSE 0 END) AS siege-> FROM-> t1-> JOIN-> t2 on t1.paid = t2.paid-> GROUP BY t2.id-> ) dt_i-> ON (dt_i.id = t3c.id)-> )-> GROUP BY t3c.id,t3c.b-> ) dt_o-> ON dt_o.id = t3.id AND dt_o.b = t3.b-> ;+------+-----------------+------------+--------+---------------------------+---------+---------+----------------------+-------+----------------------------------------------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |+------+-----------------+------------+--------+---------------------------+---------+---------+----------------------+-------+----------------------------------------------+| 1 | PRIMARY | t3 | index | id,id_2 | id | 8 | NULL | 550 | Using index || 1 | PRIMARY | <derived2> | ref | key0 | key0 | 8 | test.t3.id,test.t3.b | 2 | || 2 | LATERAL DERIVED | t3c | eq_ref | id,id_2 | id | 8 | test.t3.id,test.t3.b | 1 | Using index || 2 | LATERAL DERIVED | <derived3> | ref | key0 | key0 | 5 | test.t3c.id | 3 | || 2 | LATERAL DERIVED | t2c | ref | id | id | 4 | test.t3c.id | 38 | Using index || 3 | DERIVED | t1 | index | paid,paid_2,paid_3,paid_4 | paid_4 | 5 | NULL | 22338 | Using index; Using temporary; Using filesort || 3 | DERIVED | t2 | eq_ref | PRIMARY,id | PRIMARY | 4 | test.t1.paid | 1 | |+------+-----------------+------------+--------+---------------------------+---------+---------+----------------------+-------+----------------------------------------------+7 rows in set (0.001 sec)MariaDB [test]> set optimizer_switch='split_materialized=off';Query OK, 0 rows affected (0.000 sec)MariaDB [test]> EXPLAIN-> SELECT 1-> FROM-> t3-> JOIN-> (-> SELECT t3c.id, t3c.b, sum(dt_i.siege)-> FROM-> (-> ( t3c JOIN t2c ON (t2c.id = t3c.id))-> LEFT JOIN-> (-> SELECT t2.id, sum(CASE WHEN t2.a = t1.manr THEN 1 ELSE 0 END) AS siege-> FROM-> t1-> JOIN-> t2 on t1.paid = t2.paid-> GROUP BY t2.id-> ) dt_i-> ON (dt_i.id = t3c.id)-> )-> GROUP BY t3c.id,t3c.b-> ) dt_o-> ON dt_o.id = t3.id AND dt_o.b = t3.b-> ;+------+-------------+------------+--------+---------------------------+---------+---------+----------------------+-------+----------------------------------------------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |+------+-------------+------------+--------+---------------------------+---------+---------+----------------------+-------+----------------------------------------------+| 1 | PRIMARY | t3 | index | id,id_2 | id | 8 | NULL | 550 | Using index || 1 | PRIMARY | <derived2> | ref | key0 | key0 | 8 | test.t3.id,test.t3.b | 1577 | || 2 | DERIVED | t3c | index | id,id_2 | id | 8 | NULL | 550 | Using index || 2 | DERIVED | t2c | ref | id | id | 4 | test.t3c.id | 38 | Using index || 2 | DERIVED | <derived3> | ref | key0 | key0 | 5 | test.t3c.id | 40 | || 3 | DERIVED | t1 | index | paid,paid_2,paid_3,paid_4 | paid_4 | 5 | NULL | 22338 | Using index; Using temporary; Using filesort || 3 | DERIVED | t2 | eq_ref | PRIMARY | PRIMARY | 4 | test.t1.paid | 1 | |+------+-------------+------------+--------+---------------------------+---------+---------+----------------------+-------+----------------------------------------------+7 rows in set (0.004 sec)The execution times become better : 48.7 sec and 0.3 sec correspondingly , but with the ratio between them is pretty close to what we had for InnoDB tables.