Debian Codegraph @

mysql-test/suite/secondary_engine/t/cost_threshold.test

raw
1 --echo #
2 --echo # Test cases for the secondary_engine_cost_threshold variable.
3 --echo #
4
5 --disable_query_log
6 --eval INSTALL PLUGIN mock SONAME '$MOCK_PLUGIN'
7 --enable_query_log
8
9 # Remember the original threshold and restore it at the end of the test.
10 SELECT @@secondary_engine_cost_threshold,
11 @@global.secondary_engine_cost_threshold;
12 SET @saved_threshold = @@secondary_engine_cost_threshold;
13
14 # Negative threshold values give a warning and are adjusted to 0.
15 SET @@secondary_engine_cost_threshold = -100;
16 SELECT @@secondary_engine_cost_threshold;
17
18 CREATE TABLE t1(x INT, y INT);
19 INSERT INTO t1 VALUES (1, 2), (2, 3), (3, 4);
20
21 # A costly Cartesian product to help create plans with high cost.
22 CREATE VIEW v1 AS
23 SELECT t1.x x1, t2.x x2, t3.x x3, t4.x x4, t5.x x5, t6.x x6, t7.x x7,
24 t8.x x8, t9.x x9, t10.x x10, t11.x x11, t12.x x12, t13.x x13
25 FROM t1, t1 t2, t1 t3, t1 t4, t1 t5, t1 t6, t1 t7, t1 t8, t1 t9,
26 t1 t10, t1 t11, t1 t12, t1 t13;
27
28 ANALYZE TABLE t1;
29
30 # Some queries with different costs.
31 let $q1 = SELECT * FROM t1;
32 let $q2 = SELECT (SELECT y FROM t1 WHERE x = 1);
33 let $q3 = SELECT * FROM v1;
34 let $q4 = SELECT * FROM t1 WHERE x = (SELECT COUNT(*) FROM v1);
35 let $q5 = SELECT * FROM t1 tt WHERE x = (SELECT SUM(x) FROM t1);
36
37 # When debugging, the statements below can be uncommented to see what
38 # their estimated cost is in the primary storage engine. The actual
39 # cost is different in the old optimizer and in the hypergraph join
40 # optimizer, so they are commented out to allow testing against both
41 # optimizers. At the time of writing, the first three queries have
42 # costs less than 100 in both optimizers, and the last two queries
43 # have cost greater than 100.
44 #--eval EXPLAIN $q1
45 #SHOW STATUS LIKE 'Last_query_cost';
46 #--eval EXPLAIN $q2
47 #SHOW STATUS LIKE 'Last_query_cost';
48 #--eval EXPLAIN $q3
49 #SHOW STATUS LIKE 'Last_query_cost';
50 #--eval EXPLAIN $q4
51 #SHOW STATUS LIKE 'Last_query_cost';
52 #--eval EXPLAIN $q5
53 #SHOW STATUS LIKE 'Last_query_cost';
54
55 ALTER TABLE t1 SECONDARY_ENGINE MOCK;
56 ALTER TABLE t1 SECONDARY_LOAD;
57 ANALYZE TABLE t1;
58
59 # The threshold is 0. Queries with non-zero cost are offloaded.
60 SET @@secondary_engine_cost_threshold = 0;
61 SELECT @@secondary_engine_cost_threshold,
62 @@global.secondary_engine_cost_threshold;
63
64 FLUSH STATUS;
65 --eval $q1
66 --eval $q2
67 --eval $q3
68 --eval $q4
69 --eval $q5
70 --echo # All five queries are offloaded to the secondary engine.
71 SHOW STATUS LIKE 'Secondary_engine_execution_count';
72
73 # Increase the threshold. Only the two most expensive queries are offloaded.
74 SET @@secondary_engine_cost_threshold = 100;
75 SELECT @@secondary_engine_cost_threshold,
76 @@global.secondary_engine_cost_threshold;
77
78 FLUSH STATUS;
79 --eval $q1
80 --eval $q2
81 --eval $q3
82 --echo # The first three queries are not offloaded to the secondary engine.
83 SHOW STATUS LIKE 'Secondary_engine_execution_count';
84 --eval $q4
85 --eval $q5
86 --echo # The last two queries are offloaded to the secondary engine.
87 SHOW STATUS LIKE 'Secondary_engine_execution_count';
88
89 # Increase the threshold even more. All the queries should be executed
90 # in the primary storage engine.
91 SET @@secondary_engine_cost_threshold = 1000000;
92 SELECT @@secondary_engine_cost_threshold,
93 @@global.secondary_engine_cost_threshold;
94 FLUSH STATUS;
95 --disable_result_log
96 --eval $q1
97 --eval $q2
98 --eval $q3
99 --eval $q4
100 --eval $q5
101 --enable_result_log
102 --echo # All queries are executed in the primary storage engine.
103 SHOW STATUS LIKE 'Secondary_engine_execution_count';
104
105 --echo # Set the threshold in a hint.
106
107 FLUSH STATUS;
108 SELECT /*+ SET_VAR(secondary_engine_cost_threshold = 0) */ * FROM t1;
109 SHOW STATUS LIKE 'Secondary_engine_execution_count';
110
111 FLUSH STATUS;
112 --sorted_result
113 SELECT /*+ SET_VAR(secondary_engine_cost_threshold = 100) */ * FROM t1;
114 SHOW STATUS LIKE 'Secondary_engine_execution_count';
115
116 --echo # The use_secondary_engine variable overrides the threshold.
117
118 FLUSH STATUS;
119 SELECT /*+ SET_VAR(secondary_engine_cost_threshold = 1000000)
120 SET_VAR(use_secondary_engine = FORCED) */
121 * FROM t1;
122 SHOW STATUS LIKE 'Secondary_engine_execution_count';
123
124 FLUSH STATUS;
125 --sorted_result
126 SELECT /*+ SET_VAR(secondary_engine_cost_threshold = 0)
127 SET_VAR(use_secondary_engine = OFF) */
128 * FROM t1;
129 SHOW STATUS LIKE 'Secondary_engine_execution_count';
130
131 --echo # The threshold is evaluated on each execution.
132 FLUSH STATUS;
133 --eval PREPARE ps FROM '$q1'
134 SET @@secondary_engine_cost_threshold = 0;
135 EXECUTE ps;
136 SHOW STATUS LIKE 'Secondary_engine_execution_count';
137 SET @@secondary_engine_cost_threshold = 100;
138 EXECUTE ps;
139 SHOW STATUS LIKE 'Secondary_engine_execution_count';
140 SET @@secondary_engine_cost_threshold = 0;
141 EXECUTE ps;
142 SHOW STATUS LIKE 'Secondary_engine_execution_count';
143 SET @@secondary_engine_cost_threshold = 100;
144 EXECUTE ps;
145 SHOW STATUS LIKE 'Secondary_engine_execution_count';
146 DROP PREPARE ps;
147
148 DROP TABLE t1;
149 DROP VIEW v1;
150
151 # Restore the original value of the threshold.
152 SET @@secondary_engine_cost_threshold = @saved_threshold;
153 SELECT @@secondary_engine_cost_threshold,
154 @@global.secondary_engine_cost_threshold;
155
156 UNINSTALL PLUGIN mock;