Debian Codegraph @

mysql-test/suite/ndb_opt/bushy_joins.test

raw
1 ########################################################
2 # ndb_bushy_joins.test
3 #
4 # This test is intended to check system behaviour when SPJ
5 # executes large bushy-joins, aka 'star-joins' -> joins with
6 # multiple childs being dependent on the same parent.
7 #
8 # These types of joins has previously caused
9 # job buffer overflow as too many requests was
10 # sent in parallel from the SPJ block.
11 # (bug#14709490)
12 #
13 # '--replace_column 10 ### 11 ###' is used for explains throughout
14 # the test as experience has shown that 'rows' estimates
15 # may vary depending on OS, compiler and .. phase of moon.
16 #
17 ########################################################
18
19 # Test takes too long and fails with valgrind, see
20 # Bug#28247833 NDB_BUSHY_JOINS FAILS IN VALGRIND RUNS
21 --source include/not_valgrind.inc
22
23 --source include/have_ndb.inc
24
25 # Turn on DumpPageMemoryOnFail to dump page usage statistics on page allocation
26 # failure, giving extra information for debugging if test fails due to out of
27 # page memory.
28 exec $NDB_MGM --execute="ALL DUMP 1001 1";
29
30 connect (ddl,localhost,root,,test);
31 connect (j1,localhost,root,,test);
32 connect (j2,localhost,root,,test);
33 connect (j3,localhost,root,,test);
34
35 connection ddl;
36
37 create table parent(a int primary key, b int, c int, d int) engine=ndb;
38 create table eq_child(a int, b int, c int, d int, primary key(a,b)) engine=ndb;
39 create table ref_child(a int, b int, c int, d int, primary key(a,b)) engine=ndb;
40
41 alter table parent partition by key(a) partitions 32;
42 alter table eq_child partition by key(a) partitions 32;
43 alter table ref_child partition by key(a) partitions 32;
44
45 insert into parent values (1,1,1,1);
46
47 let $factor = 1;
48 let $loops = 13;
49
50 # Fill tables with 2^loops rows
51 while ($loops)
52 {
53 eval insert into parent select a+$factor, b+$factor, c+$factor, d+$factor from parent;
54 let $factor = $factor*2;
55 dec $loops;
56 }
57 select count(*) from parent;
58
59 insert into eq_child select * from parent;
60 insert into ref_child select * from parent;
61
62 # Cheat statistics: update rows actually not refered in joins below
63 # (Cheat required in order to make 'ref-joins' bushy)
64 update ref_child set a = a-(a%16) where a > 4000;
65
66 analyze table parent, eq_child, ref_child;
67
68 let $query1 =
69 select straight_join count(*) from parent
70 join eq_child as c1 on c1.a = parent.b and c1.b = parent.b
71 join eq_child as c2 on c2.a = parent.b and c2.b = parent.b
72 join eq_child as c3 on c3.a = parent.b and c3.b = parent.b
73 join eq_child as c4 on c4.a = parent.b and c4.b = parent.b
74 join eq_child as c5 on c5.a = parent.b and c5.b = parent.b
75 join eq_child as c6 on c6.a = parent.b and c6.b = parent.b
76 join eq_child as c7 on c7.a = parent.b and c7.b = parent.b
77 join eq_child as c8 on c8.a = parent.b and c8.b = parent.b
78 join eq_child as c9 on c9.a = parent.b and c9.b = parent.b
79 join eq_child as c10 on c10.a = parent.b and c10.b = parent.b
80 join eq_child as c11 on c11.a = parent.b and c11.b = parent.b
81 join eq_child as c12 on c12.a = parent.b and c12.b = parent.b
82 join eq_child as c13 on c13.a = parent.b and c13.b = parent.b
83 join eq_child as c14 on c14.a = parent.b and c14.b = parent.b
84 join eq_child as c15 on c15.a = parent.b and c15.b = parent.b
85 join eq_child as c16 on c16.a = parent.b and c16.b = parent.b
86 ;
87
88 let $query2 =
89 select straight_join count(*) from parent
90 join eq_child as c1 on c1.a = parent.b and c1.b = parent.b
91 join eq_child as c5 on c5.a = c1.c and c5.b = c1.c
92 join eq_child as c6 on c6.a = c1.c and c6.b = c1.c
93 join eq_child as c7 on c7.a = c1.c and c7.b = c1.c
94 join eq_child as c8 on c8.a = c1.c and c8.b = c1.c
95 join eq_child as c2 on c2.a = parent.b and c2.b = parent.b
96 join eq_child as c9 on c9.a = c2.c and c9.b = c2.c
97 join eq_child as c10 on c10.a = c2.c and c10.b = c2.c
98 join eq_child as c11 on c11.a = c2.c and c11.b = c2.c
99 join eq_child as c12 on c12.a = c2.c and c12.b = c2.c
100 join eq_child as c3 on c3.a = parent.b and c3.b = parent.b
101 join eq_child as c13 on c13.a = c3.c and c13.b = c3.c
102 join eq_child as c14 on c14.a = c3.c and c14.b = c3.c
103 join eq_child as c15 on c15.a = c3.c and c15.b = c3.c
104 join eq_child as c16 on c16.a = c3.c and c16.b = c3.c
105 join eq_child as c4 on c4.a = parent.b and c4.b = parent.b
106 join eq_child as c17 on c17.a = c4.c and c17.b = c4.c
107 join eq_child as c18 on c18.a = c4.c and c18.b = c4.c
108 join eq_child as c19 on c19.a = c4.c and c19.b = c4.c
109 join eq_child as c20 on c20.a = c4.c and c20.b = c4.c
110 ;
111
112 let $query3 =
113 select straight_join count(*) from parent
114 join ref_child as c1 on c1.a = parent.b
115 join ref_child as c2 on c2.a = parent.b
116 join ref_child as c3 on c3.a = parent.b
117 join ref_child as c4 on c4.a = parent.b
118 join ref_child as c5 on c5.a = parent.b
119 join ref_child as c6 on c6.a = parent.b
120 join ref_child as c7 on c7.a = parent.b
121 join ref_child as c8 on c8.a = parent.b
122 join ref_child as c9 on c9.a = parent.b
123 join ref_child as c10 on c10.a = parent.b
124 join ref_child as c11 on c11.a = parent.b
125 join ref_child as c12 on c12.a = parent.b
126 join ref_child as c13 on c13.a = parent.b
127 join ref_child as c14 on c14.a = parent.b
128 join ref_child as c15 on c15.a = parent.b
129 join ref_child as c16 on c16.a = parent.b
130 where parent.a < 1000
131 ;
132
133 let $query4 =
134 select straight_join count(*) from parent
135 join ref_child as c1 on c1.a = parent.b
136 join eq_child as c1eq on c1eq.a = c1.a and c1eq.b = c1.b
137 join ref_child as c2 on c2.a = parent.b
138 join eq_child as c2eq on c2eq.a = c2.a and c2eq.b = c2.b
139 join ref_child as c3 on c3.a = parent.b
140 join eq_child as c3eq on c3eq.a = c3.a and c3eq.b = c3.b
141 join ref_child as c4 on c4.a = parent.b
142 join eq_child as c4eq on c4eq.a = c4.a and c4eq.b = c4.b
143 join ref_child as c5 on c5.a = parent.b
144 join eq_child as c5eq on c5eq.a = c5.a and c5eq.b = c5.b
145 join ref_child as c6 on c6.a = parent.b
146 join eq_child as c6eq on c6eq.a = c6.a and c6eq.b = c6.b
147 join ref_child as c7 on c7.a = parent.b
148 join eq_child as c7eq on c7eq.a = c7.a and c7eq.b = c7.b
149 join ref_child as c8 on c8.a = parent.b
150 join eq_child as c8eq on c8eq.a = c8.a and c8eq.b = c8.b
151 join ref_child as c9 on c9.a = parent.b
152 join eq_child as c9eq on c9eq.a = c9.a and c9eq.b = c9.b
153 join ref_child as c10 on c10.a = parent.b
154 join eq_child as c10eq on c10eq.a = c10.a and c10eq.b = c10.b
155 join ref_child as c11 on c11.a = parent.b
156 join eq_child as c11eq on c11eq.a = c11.a and c11eq.b = c11.b
157 join ref_child as c12 on c12.a = parent.b
158 join eq_child as c12eq on c12eq.a = c12.a and c12eq.b = c12.b
159 join ref_child as c13 on c13.a = parent.b
160 join eq_child as c13eq on c13eq.a = c13.a and c13eq.b = c13.b
161 join ref_child as c14 on c14.a = parent.b
162 join eq_child as c14eq on c14eq.a = c14.a and c14eq.b = c14.b
163 join ref_child as c15 on c15.a = parent.b
164 join eq_child as c15eq on c15eq.a = c15.a and c15eq.b = c15.b
165 join ref_child as c16 on c16.a = parent.b
166 join eq_child as c16eq on c16eq.a = c16.a and c16eq.b = c16.b
167 where parent.a < 1000
168 ;
169
170 connection j1;
171 set ndb_join_pushdown = on;
172
173 -- echo ===============================
174 -- echo Run single instance of 'query1'
175 -- echo ===============================
176 eval explain $query1;
177 eval $query1;
178
179 -- echo ===============================
180 -- echo Run single instance of 'query2'
181 -- echo ===============================
182 eval explain $query2;
183 eval $query2;
184
185 -- echo ===============================
186 -- echo Run single instance of 'query3'
187 -- echo ===============================
188 --replace_column 10 ### 11 ###
189 eval explain $query3;
190 eval $query3;
191
192 -- echo ===============================
193 -- echo Run single instance of 'query4'
194 -- echo ===============================
195 --replace_column 10 ### 11 ###
196 eval explain $query4;
197 eval $query4;
198
199
200 -- echo =================================
201 -- echo Run multiple 'query1' in parallel
202 -- echo =================================
203 let $query = $query1;
204 eval explain $query;
205 connection j1;
206 send_eval $query;
207 connection j2;
208 send_eval $query;
209 connection j3;
210 send_eval $query;
211
212 -- echo Await completion or failure
213 connection j1;
214 reap;
215 connection j2;
216 reap;
217 connection j3;
218 reap;
219
220 -- echo =================================
221 -- echo Run multiple 'query2' in parallel
222 -- echo =================================
223 let $query = $query2;
224 eval explain $query;
225 connection j1;
226 send_eval $query;
227 connection j2;
228 send_eval $query;
229 connection j3;
230 send_eval $query;
231
232 -- echo Await completion or failure
233 connection j1;
234 reap;
235 connection j2;
236 reap;
237 connection j3;
238 reap;
239
240 -- echo =================================
241 -- echo Run multiple 'query3' in parallel
242 -- echo =================================
243 let $query = $query3;
244 --replace_column 10 ### 11 ###
245 eval explain $query;
246 connection j1;
247 send_eval $query;
248 connection j2;
249 send_eval $query;
250 connection j3;
251 send_eval $query;
252
253 -- echo Await completion or failure
254 connection j1;
255 reap;
256 connection j2;
257 reap;
258 connection j3;
259 reap;
260
261 -- echo =================================
262 -- echo Run multiple 'query4' in parallel
263 -- echo =================================
264 let $query = $query4;
265 --replace_column 10 ### 11 ###
266 eval explain $query;
267 connection j1;
268 send_eval $query;
269 connection j2;
270 send_eval $query;
271 connection j3;
272 send_eval $query;
273
274 -- echo Await completion or failure
275 connection j1;
276 reap;
277 connection j2;
278 reap;
279 connection j3;
280 reap;
281
282
283 ######################################################
284 # wl#11164 will sequentialize the execution of the
285 # above inner joins.
286 #
287 # This introduced a new execution strategy in the SPJ
288 # block where the query 'RESUME' logic developed for
289 # bug#14709490 was not used.
290 #
291 # However, specifying the same queries as outer joins
292 # will cause the original RESUME strategy to be used.
293 ######################################################
294
295 -- echo =================================
296 -- echo Repeat most of testing with
297 -- echo outer join variants of the query.
298 -- echo =================================
299
300 let $query1 =
301 select straight_join count(*) from parent
302 left join eq_child as c1 on c1.a = parent.b and c1.b = parent.b
303 left join eq_child as c2 on c2.a = parent.b and c2.b = parent.b
304 left join eq_child as c3 on c3.a = parent.b and c3.b = parent.b
305 left join eq_child as c4 on c4.a = parent.b and c4.b = parent.b
306 left join eq_child as c5 on c5.a = parent.b and c5.b = parent.b
307 left join eq_child as c6 on c6.a = parent.b and c6.b = parent.b
308 left join eq_child as c7 on c7.a = parent.b and c7.b = parent.b
309 left join eq_child as c8 on c8.a = parent.b and c8.b = parent.b
310 left join eq_child as c9 on c9.a = parent.b and c9.b = parent.b
311 left join eq_child as c10 on c10.a = parent.b and c10.b = parent.b
312 left join eq_child as c11 on c11.a = parent.b and c11.b = parent.b
313 left join eq_child as c12 on c12.a = parent.b and c12.b = parent.b
314 left join eq_child as c13 on c13.a = parent.b and c13.b = parent.b
315 left join eq_child as c14 on c14.a = parent.b and c14.b = parent.b
316 left join eq_child as c15 on c15.a = parent.b and c15.b = parent.b
317 left join eq_child as c16 on c16.a = parent.b and c16.b = parent.b
318 ;
319
320 let $query2 =
321 select straight_join count(*) from parent
322 left join eq_child as c1 on c1.a = parent.b and c1.b = parent.b
323 left join eq_child as c5 on c5.a = c1.c and c5.b = c1.c
324 left join eq_child as c6 on c6.a = c1.c and c6.b = c1.c
325 left join eq_child as c7 on c7.a = c1.c and c7.b = c1.c
326 left join eq_child as c8 on c8.a = c1.c and c8.b = c1.c
327 left join eq_child as c2 on c2.a = parent.b and c2.b = parent.b
328 left join eq_child as c9 on c9.a = c2.c and c9.b = c2.c
329 left join eq_child as c10 on c10.a = c2.c and c10.b = c2.c
330 left join eq_child as c11 on c11.a = c2.c and c11.b = c2.c
331 left join eq_child as c12 on c12.a = c2.c and c12.b = c2.c
332 left join eq_child as c3 on c3.a = parent.b and c3.b = parent.b
333 left join eq_child as c13 on c13.a = c3.c and c13.b = c3.c
334 left join eq_child as c14 on c14.a = c3.c and c14.b = c3.c
335 left join eq_child as c15 on c15.a = c3.c and c15.b = c3.c
336 left join eq_child as c16 on c16.a = c3.c and c16.b = c3.c
337 left join eq_child as c4 on c4.a = parent.b and c4.b = parent.b
338 left join eq_child as c17 on c17.a = c4.c and c17.b = c4.c
339 left join eq_child as c18 on c18.a = c4.c and c18.b = c4.c
340 left join eq_child as c19 on c19.a = c4.c and c19.b = c4.c
341 left join eq_child as c20 on c20.a = c4.c and c20.b = c4.c
342 ;
343
344 let $query3 =
345 select straight_join count(*) from parent
346 join ref_child as c1 on c1.a = parent.b
347 join ref_child as c2 on c2.a = parent.b
348 join ref_child as c3 on c3.a = parent.b
349 join ref_child as c4 on c4.a = parent.b
350 join ref_child as c5 on c5.a = parent.b
351 join ref_child as c6 on c6.a = parent.b
352 join ref_child as c7 on c7.a = parent.b
353 join ref_child as c8 on c8.a = parent.b
354 join ref_child as c9 on c9.a = parent.b
355 join ref_child as c10 on c10.a = parent.b
356 join ref_child as c11 on c11.a = parent.b
357 join ref_child as c12 on c12.a = parent.b
358 join ref_child as c13 on c13.a = parent.b
359 join ref_child as c14 on c14.a = parent.b
360 join ref_child as c15 on c15.a = parent.b
361 join ref_child as c16 on c16.a = parent.b
362 where parent.a < 1000
363 ;
364
365 let $query4 =
366 select straight_join count(*) from parent
367 join ref_child as c1 on c1.a = parent.b
368 left join eq_child as c1eq on c1eq.a = c1.a and c1eq.b = c1.b
369 join ref_child as c2 on c2.a = parent.b
370 left join eq_child as c2eq on c2eq.a = c2.a and c2eq.b = c2.b
371 join ref_child as c3 on c3.a = parent.b
372 left join eq_child as c3eq on c3eq.a = c3.a and c3eq.b = c3.b
373 join ref_child as c4 on c4.a = parent.b
374 left join eq_child as c4eq on c4eq.a = c4.a and c4eq.b = c4.b
375 join ref_child as c5 on c5.a = parent.b
376 left join eq_child as c5eq on c5eq.a = c5.a and c5eq.b = c5.b
377 join ref_child as c6 on c6.a = parent.b
378 left join eq_child as c6eq on c6eq.a = c6.a and c6eq.b = c6.b
379 join ref_child as c7 on c7.a = parent.b
380 left join eq_child as c7eq on c7eq.a = c7.a and c7eq.b = c7.b
381 join ref_child as c8 on c8.a = parent.b
382 left join eq_child as c8eq on c8eq.a = c8.a and c8eq.b = c8.b
383 join ref_child as c9 on c9.a = parent.b
384 left join eq_child as c9eq on c9eq.a = c9.a and c9eq.b = c9.b
385 join ref_child as c10 on c10.a = parent.b
386 left join eq_child as c10eq on c10eq.a = c10.a and c10eq.b = c10.b
387 join ref_child as c11 on c11.a = parent.b
388 left join eq_child as c11eq on c11eq.a = c11.a and c11eq.b = c11.b
389 join ref_child as c12 on c12.a = parent.b
390 left join eq_child as c12eq on c12eq.a = c12.a and c12eq.b = c12.b
391 join ref_child as c13 on c13.a = parent.b
392 left join eq_child as c13eq on c13eq.a = c13.a and c13eq.b = c13.b
393 join ref_child as c14 on c14.a = parent.b
394 left join eq_child as c14eq on c14eq.a = c14.a and c14eq.b = c14.b
395 join ref_child as c15 on c15.a = parent.b
396 left join eq_child as c15eq on c15eq.a = c15.a and c15eq.b = c15.b
397 join ref_child as c16 on c16.a = parent.b
398 left join eq_child as c16eq on c16eq.a = c16.a and c16eq.b = c16.b
399 where parent.a < 1000
400 ;
401
402 connection j1;
403 set ndb_join_pushdown = on;
404
405 -- echo ===============================
406 -- echo Run single instance of 'query1'
407 -- echo ===============================
408 eval explain $query1;
409 eval $query1;
410
411 -- echo ===============================
412 -- echo Run single instance of 'query2'
413 -- echo ===============================
414 eval explain $query2;
415 eval $query2;
416
417 -- echo ===============================
418 -- echo Run single instance of 'query4'
419 -- echo ===============================
420 --replace_column 10 ### 11 ###
421 eval explain $query4;
422 eval $query4;
423
424
425 -- echo =================================
426 -- echo Run multiple 'query1' in parallel
427 -- echo =================================
428 let $query = $query1;
429 eval explain $query;
430 connection j1;
431 send_eval $query;
432 connection j2;
433 send_eval $query;
434 connection j3;
435 send_eval $query;
436
437 -- echo Await completion or failure
438 connection j1;
439 reap;
440 connection j2;
441 reap;
442 connection j3;
443 reap;
444
445 -- echo =================================
446 -- echo Run multiple 'query2' in parallel
447 -- echo =================================
448 let $query = $query2;
449 eval explain $query;
450 connection j1;
451 send_eval $query;
452 connection j2;
453 send_eval $query;
454 connection j3;
455 send_eval $query;
456
457 -- echo Await completion or failure
458 connection j1;
459 reap;
460 connection j2;
461 reap;
462 connection j3;
463 reap;
464
465 -- echo =================================
466 -- echo Run multiple 'query4' in parallel
467 -- echo =================================
468 let $query = $query4;
469 --replace_column 10 ### 11 ###
470 eval explain $query;
471 connection j1;
472 send_eval $query;
473 connection j2;
474 send_eval $query;
475 connection j3;
476 send_eval $query;
477
478 -- echo Await completion or failure
479 connection j1;
480 reap;
481 connection j2;
482 reap;
483 connection j3;
484 reap;
485
486 #
487 # Bug#31343524: SPJ QUERIES HIT 'JOB BUFFER FULL' WHILE PROCESSING LQHKEYCONF
488 #
489 # It turned out that WL#10234 'Use multiple fragments in SCAN_FRAGREQ to SPJ'
490 # reintroduced the possibility to hit 'job buffer full' during SPJ execution.
491 # Root cause for this was that the effective batchSize was multiplied by
492 # the 'multiple fragment' factor from this WL. Thus effectively allowing
493 # the SPJ-workers internal batchsize to grow to 4096 rows
494 # (limited by maxCorrelationId). Thus, even a non-bushy scan-lookup query
495 # could send upto 4096 LQHKEYREQ's. A few such queries in parallel could
496 # then easily saturate the job buffers if signaling isn't limited.
497 #
498
499 -- echo ========================================================
500 -- echo Bug#31343524: 'Job buffer full' reintroduced by WL#10234
501 -- echo Even a non-bushy query could fail due to this.
502 -- echo ========================================================
503
504 let $non_bushy_query =
505 select straight_join count(*) from parent
506 left join eq_child as c on c.a = parent.b and c.b = parent.b
507 ;
508
509 # As all joins are 'on c.a = parent.b and c.b = parent.b', setting parent.b
510 # to a fixed value will cause all child-lookups KEYREQ's to the same child.
511 # Thus increasing the chance for hitting a job buffer congestion.
512 update parent set b = 1;
513
514 -- echo ==========================================
515 -- echo Run multiple 'non_bushy_query' in parallel
516 -- echo ==========================================
517 let $query = $non_bushy_query;
518
519 connection j1;
520 send_eval $query;
521 connection j2;
522 send_eval $query;
523 connection j3;
524 send_eval $query;
525
526 -- echo Await completion or failure
527 connection j1;
528 reap;
529 connection j2;
530 reap;
531 connection j3;
532 reap;
533
534
535 # If the above passed, also test the full 'bushy' query1:
536 # (With modified parent data relative to previous test)
537
538 -- echo =========================================
539 -- echo Run multiple 'query1' (bushy) in parallel
540 -- echo =========================================
541
542 let $query = $query1;
543
544 connection j1;
545 send_eval $query;
546 connection j2;
547 send_eval $query;
548 connection j3;
549 send_eval $query;
550
551 -- echo Await completion or failure
552 connection j1;
553 reap;
554 connection j2;
555 reap;
556 connection j3;
557 reap;
558
559 connection ddl;
560 drop table parent, eq_child, ref_child;
561
562 exec $NDB_MGM --execute="ALL DUMP 1001 0";