Debian Codegraph @

mysql-test/suite/ndb/t/ndb_read_location_domain.inc

raw
1 # ndb_read_location_domain.inc
2 # - Assumes a 2 node, NoOfReplicas=2 cluster
3 # - Runs various types of table accesses and joins using ReadCommitted
4 #
5
6 # Fetch object ids for tables, unique index, and, blob tables.
7 select id into @t1_id from ndbinfo.ndb$dict_obj_info where fq_name = 'test/def/t1';
8 select id into @t1ui_id from ndbinfo.ndb$dict_obj_info where parent_obj_id = @t1_id and type = 3;
9 select id into @t2_id from ndbinfo.ndb$dict_obj_info where fq_name = 'test/def/t2';
10 select id into @t2ui_id from ndbinfo.ndb$dict_obj_info where parent_obj_id = @t2_id and type = 3;
11 select id into @t2bt_id from ndbinfo.ndb$dict_obj_info where parent_obj_id = @t2_id and fq_name like 'test/def/NDB$BLOB%';
12
13 # Prepare table t1 with data
14 --echo Put some data in.
15 --let $idx=$KEYS
16 --disable_query_log
17 while ($idx)
18 {
19 --eval insert into test.t1 values ($idx,$idx,$idx,$idx)
20 --dec $idx
21 }
22 --enable_query_log
23
24 --echo Make it more interesting for joining
25 update test.t1 set c=c+1, d=d+1;
26
27 # Count nodes belonging to some nodegroup
28 select count(distinct node_id) into @nodes
29 from ndbinfo.ndb$membership where group_id < 0xFFFFFF00;
30
31 --echo Check replicas
32 select count(distinct node_id)
33 from ndbinfo.operations_per_fragment
34 where fq_name='test/def/t1'
35 and fragment_num=0;
36
37 --echo Check nodes with table data
38 select count(distinct node_id)
39 from ndbinfo.operations_per_fragment
40 where fq_name='test/def/t1';
41
42 if (`select count(distinct node_id) <> @nodes from ndbinfo.operations_per_fragment where table_id = @t1_id`) {
43 die "Table t1 have not data on all nodegroups!";
44 }
45
46 --echo Check UI replicas
47 select count(distinct node_id)
48 from ndbinfo.operations_per_fragment
49 where table_id = @t1ui_id
50 and fragment_num=0;
51
52 --echo Check nodes with UI data
53 select count(distinct node_id)
54 from ndbinfo.operations_per_fragment
55 where table_id = @t1ui_id;
56
57 if (!$bug_ui) {
58 if (`select count(distinct node_id) <> @nodes from ndbinfo.operations_per_fragment where table_id = @t1ui_id`) {
59 die "Unique index for table t1 have not data on all nodegroups!";
60 } }
61
62 # Prepare table t2 with data
63
64 --echo Put some data in.
65 insert into test.t2 select a,b,c,repeat('BJC',300) from test.t1;
66
67 --echo Check blob table replicas
68 select count(distinct node_id)
69 from ndbinfo.operations_per_fragment
70 where table_id = @t2bt_id
71 and fragment_num=0;
72
73 --echo Check nodes with blob data
74 select count(distinct node_id)
75 from ndbinfo.operations_per_fragment
76 where table_id = @t2bt_id;
77
78 if (!$bug_blob) {
79 if (`select count(distinct node_id) <> @nodes from ndbinfo.operations_per_fragment where table_id = @t2bt_id`) {
80 die "Blob table for table t2 have not data on all nodegroups!";
81 } }
82
83 #
84 # Create a table 'hints' with one row and partition per node in some node group.
85 # Note, there are no relation between the row values and node ids.
86 # All partitions are expected to have its primary replica on different nodes.
87 # Each partition will have one row with values for i from 1 to number of nodes.
88 #
89 let $nodes = `select @nodes`;
90 let $i = $nodes;
91 eval set @partitions = 'PARTITION p$i VALUES IN ($i)';
92 dec $i;
93 while ($i)
94 {
95 eval set @partitions = concat(@partitions,',PARTITION p$i VALUES IN ($i)');
96 dec $i;
97 }
98 let $partitions = `select @partitions`;
99 set @@new = 1;
100 eval create table hints (i int primary key) engine=ndb partition by list(i) ($partitions);
101 let $i = $nodes;
102 while($i)
103 {
104 eval insert into hints values ($i);
105 # Use 'eval select * from hints where i = $i;' first in a transaction to force
106 # connect to corresponding nodes DBTC.
107
108 --echo Hint node by $i of $nodes
109 --echo Tests on T1 (!Blobs)
110 --echo Test some basic NdbApi
111
112 select parent_fq_name, fq_name, type, node_id, sum(tot_key_reads), sum(tot_frag_scans)
113 from ndbinfo.operations_per_fragment
114 where (parent_fq_name='test/def/t1' OR fq_name='test/def/t1') and
115 (tot_key_reads > 0 or tot_frag_scans > 0)
116 group by parent_fq_name, fq_name, type, node_id;
117
118 --echo PK lookups
119 --replace_column 4 # 10 # 11 #
120 explain select * from test.t1 where t1.a=6;
121
122 select node_id, counter_name, sum(val) from ndbinfo.counters
123 where counter_name = 'READS' or counter_name = 'LOCAL_READS'
124 group by node_id, counter_name;
125
126 --let $idx=$KEYS
127 --disable_query_log
128 --disable_result_log
129 while ($idx)
130 {
131 --eval select * from test.t1 where a=$idx
132 --dec $idx
133 }
134 --enable_result_log
135 --enable_query_log
136 select parent_fq_name, fq_name, type, node_id, sum(tot_key_reads), sum(tot_frag_scans)
137 from ndbinfo.operations_per_fragment
138 where (parent_fq_name='test/def/t1' OR fq_name='test/def/t1') and
139 (tot_key_reads > 0 or tot_frag_scans > 0)
140 group by parent_fq_name, fq_name, type, node_id;
141
142 select node_id, counter_name, sum(val) from ndbinfo.counters
143 where counter_name = 'READS' or counter_name = 'LOCAL_READS'
144 group by node_id, counter_name;
145
146 if (!$bug_ui) {
147 --echo UI lookups
148 --replace_column 4 # 10 # 11 #
149 explain select * from test.t1 where t1.b=6;
150
151 --let $idx=$KEYS
152 --disable_query_log
153 --disable_result_log
154 while ($idx)
155 {
156 --eval select * from test.t1 where b=$idx
157 --dec $idx
158 }
159 --enable_result_log
160 --enable_query_log
161 select parent_fq_name, fq_name, type, node_id, sum(tot_key_reads), sum(tot_frag_scans)
162 from ndbinfo.operations_per_fragment
163 where (parent_fq_name='test/def/t1' OR fq_name='test/def/t1') and
164 (tot_key_reads > 0 or tot_frag_scans > 0)
165 group by parent_fq_name, fq_name, type, node_id;
166
167 select node_id, counter_name, sum(val) from ndbinfo.counters
168 where counter_name = 'READS' or counter_name = 'LOCAL_READS'
169 group by node_id, counter_name;
170 }
171
172 --echo OI scan
173 --replace_column 4 # 10 # 11 #
174 explain select * from test.t1 where c > 6;
175
176 --disable_query_log
177 --disable_result_log
178 select * from test.t1 where c > 6;
179 --enable_result_log
180 --enable_query_log
181 select parent_fq_name, fq_name, type, node_id, sum(tot_key_reads), sum(tot_frag_scans)
182 from ndbinfo.operations_per_fragment
183 where (parent_fq_name='test/def/t1' OR fq_name='test/def/t1') and
184 (tot_key_reads > 0 or tot_frag_scans > 0)
185 group by parent_fq_name, fq_name, type, node_id;
186
187 --echo Table scan
188 --replace_column 4 # 10 # 11 #
189 explain select * from test.t1;
190
191 --disable_query_log
192 --disable_result_log
193 select * from test.t1;
194 --enable_result_log
195 --enable_query_log
196 select parent_fq_name, fq_name, type, node_id, sum(tot_key_reads), sum(tot_frag_scans)
197 from ndbinfo.operations_per_fragment
198 where (parent_fq_name='test/def/t1' OR fq_name='test/def/t1') and
199 (tot_key_reads > 0 or tot_frag_scans > 0)
200 group by parent_fq_name, fq_name, type, node_id;
201
202 --echo Test pushed queries (SPJ)
203
204 --echo Pushed pk->pk
205 --echo Lookup to get a.d, feeding pk lookup of b.a
206 --replace_column 4 # 10 # 11 #
207 explain select a.a, a.d, b.d
208 from
209 test.t1 a
210 join
211 test.t1 b
212 on
213 a.d = b.a
214 where a.a = 6;
215
216 --let $idx=$KEYS
217 --disable_query_log
218 --disable_result_log
219 while ($idx)
220 {
221 --eval select a.a, a.d, b.d from test.t1 a join test.t1 b on a.d = b.a where a.a = $idx
222 --dec $idx
223 }
224 --enable_result_log
225 --enable_query_log
226 select parent_fq_name, fq_name, type, node_id, sum(tot_key_reads), sum(tot_frag_scans)
227 from ndbinfo.operations_per_fragment
228 where (parent_fq_name='test/def/t1' OR fq_name='test/def/t1') and
229 (tot_key_reads > 0 or tot_frag_scans > 0)
230 group by parent_fq_name, fq_name, type, node_id;
231
232 --echo Pushed uk->uk
233 --echo Lookup to get a.d, feeding uk lookup of b.b
234 --replace_column 4 # 10 # 11 #
235 explain select a.a, a.d, b.d
236 from
237 test.t1 a
238 join
239 test.t1 b
240 on
241 a.d = b.b
242 where a.b = 6;
243
244 --let $idx=$KEYS
245 --disable_query_log
246 --disable_result_log
247 while ($idx)
248 {
249 --eval select a.a, a.d, b.d from test.t1 a join test.t1 b on a.d = b.b where a.b = $idx
250 --dec $idx
251 }
252 --enable_result_log
253 --enable_query_log
254 select parent_fq_name, fq_name, type, node_id, sum(tot_key_reads), sum(tot_frag_scans)
255 from ndbinfo.operations_per_fragment
256 where (parent_fq_name='test/def/t1' OR fq_name='test/def/t1') and
257 (tot_key_reads > 0 or tot_frag_scans > 0)
258 group by parent_fq_name, fq_name, type, node_id;
259
260 --echo Pushed uk->pk
261 --echo Lookup to get a.d, feeding pk lookup of b.a
262 --replace_column 4 # 10 # 11 #
263 explain select a.a, a.d, b.d
264 from
265 test.t1 a
266 join
267 test.t1 b
268 on
269 a.d = b.a
270 where a.b = 6;
271
272 --let $idx=$KEYS
273 --disable_query_log
274 --disable_result_log
275 while ($idx)
276 {
277 --eval select a.a, a.d, b.d from test.t1 a join test.t1 b on a.d = b.a where a.b = $idx
278 --dec $idx
279 }
280 --enable_result_log
281 --enable_query_log
282 select parent_fq_name, fq_name, type, node_id, sum(tot_key_reads), sum(tot_frag_scans)
283 from ndbinfo.operations_per_fragment
284 where (parent_fq_name='test/def/t1' OR fq_name='test/def/t1') and
285 (tot_key_reads > 0 or tot_frag_scans > 0)
286 group by parent_fq_name, fq_name, type, node_id;
287
288 --echo Pushed pk->uk
289 --echo Lookup to get a.d, feeding uk lookup of b.b
290 --replace_column 4 # 10 # 11 #
291 explain select a.a, a.d, b.d
292 from
293 test.t1 a
294 join
295 test.t1 b
296 on
297 a.d = b.b
298 where a.a = 6;
299
300 --let $idx=$KEYS
301 --disable_query_log
302 --disable_result_log
303 while ($idx)
304 {
305 --eval select a.a, a.d, b.d from test.t1 a join test.t1 b on a.d = b.b where a.a = $idx
306 --dec $idx
307 }
308 --enable_result_log
309 --enable_query_log
310 select parent_fq_name, fq_name, type, node_id, sum(tot_key_reads), sum(tot_frag_scans)
311 from ndbinfo.operations_per_fragment
312 where (parent_fq_name='test/def/t1' OR fq_name='test/def/t1') and
313 (tot_key_reads > 0 or tot_frag_scans > 0)
314 group by parent_fq_name, fq_name, type, node_id;
315
316 --echo Pushed scan originating sub-lookups
317 --echo Table scan to get a.d, feeding pk lookups of b.a
318 --replace_column 4 # 10 # 11 #
319 explain select a.a, a.d, b.d
320 from
321 test.t1 a
322 join
323 test.t1 b
324 on
325 a.d = b.a;
326
327 --disable_result_log
328 select a.a, a.d, b.d
329 from
330 test.t1 a
331 join
332 test.t1 b
333 on
334 a.d = b.a;
335
336 --enable_result_log
337 select parent_fq_name, fq_name, type, node_id, sum(tot_key_reads), sum(tot_frag_scans)
338 from ndbinfo.operations_per_fragment
339 where (parent_fq_name='test/def/t1' OR fq_name='test/def/t1') and
340 (tot_key_reads > 0 or tot_frag_scans > 0)
341 group by parent_fq_name, fq_name, type, node_id;
342
343 --echo Pushed scan originating sub-lookups
344 --echo Table scan to get a.d, feeding uk lookups of b.b
345 --replace_column 4 # 10 # 11 #
346 explain select a.a, a.d, b.d
347 from
348 test.t1 a
349 join
350 test.t1 b
351 on
352 a.d = b.b;
353
354 --disable_result_log
355 select a.a, a.d, b.d
356 from
357 test.t1 a
358 join
359 test.t1 b
360 on
361 a.d = b.b;
362
363 --enable_result_log
364 select parent_fq_name, fq_name, type, node_id, sum(tot_key_reads), sum(tot_frag_scans)
365 from ndbinfo.operations_per_fragment
366 where (parent_fq_name='test/def/t1' OR fq_name='test/def/t1') and
367 (tot_key_reads > 0 or tot_frag_scans > 0)
368 group by parent_fq_name, fq_name, type, node_id;
369
370 --echo Pushed scan originating sub-scans
371 --echo Range scan on a.a > 10 getting a.d, feeding range scans on b.c = a.d
372
373 --replace_column 4 # 10 # 11 #
374 explain select a.a, a.d, b.d
375 from
376 test.t1 a
377 join
378 test.t1 b
379 on
380 b.c=a.d
381 where
382 a.a>10;
383
384 --disable_result_log
385 select a.a, a.d, b.d
386 from
387 test.t1 a
388 join
389 test.t1 b
390 on
391 b.c=a.d
392 where
393 a.a>10;
394
395 --enable_result_log
396 select parent_fq_name, fq_name, type, node_id, sum(tot_key_reads), sum(tot_frag_scans)
397 from ndbinfo.operations_per_fragment
398 where (parent_fq_name='test/def/t1' OR fq_name='test/def/t1') and
399 (tot_key_reads > 0 or tot_frag_scans > 0)
400 group by parent_fq_name, fq_name, type, node_id;
401
402 --echo Tests on T2 (Blobs)
403
404 select parent_fq_name, fq_name, type, node_id, sum(tot_key_reads), sum(tot_frag_scans)
405 from ndbinfo.operations_per_fragment
406 where (parent_fq_name='test/def/t2' OR fq_name='test/def/t2') and
407 (tot_key_reads > 0 or tot_frag_scans > 0)
408 group by parent_fq_name, fq_name, type, node_id;
409
410 select node_id, counter_name, sum(val) from ndbinfo.counters
411 where counter_name = 'READS' or counter_name = 'LOCAL_READS'
412 group by node_id, counter_name;
413
414 if (!$bug_blob)
415 {
416
417 --echo Test some basic NdbApi
418
419 --replace_column 4 # 10 # 11 #
420 explain select * from test.t2 where t2.a=6;
421
422 --let $idx=$KEYS
423 --disable_query_log
424 --disable_result_log
425 while ($idx)
426 {
427 --eval select * from test.t2 where a=$idx
428 --dec $idx
429 }
430 --enable_result_log
431 --enable_query_log
432 select parent_fq_name, fq_name, type, node_id, sum(tot_key_reads), sum(tot_frag_scans)
433 from ndbinfo.operations_per_fragment
434 where (parent_fq_name='test/def/t2' OR fq_name='test/def/t2') and
435 (tot_key_reads > 0 or tot_frag_scans > 0)
436 group by parent_fq_name, fq_name, type, node_id;
437
438 select node_id, counter_name, sum(val) from ndbinfo.counters
439 where counter_name = 'READS' or counter_name = 'LOCAL_READS'
440 group by node_id, counter_name;
441
442 --echo UI lookups
443 --replace_column 4 # 10 # 11 #
444 explain select * from test.t2 where t2.b=6;
445
446 --let $idx=$KEYS
447 --disable_query_log
448 --disable_result_log
449 while ($idx)
450 {
451 --eval select * from test.t2 where b=$idx
452 --dec $idx
453 }
454 --enable_result_log
455 --enable_query_log
456 select parent_fq_name, fq_name, type, node_id, sum(tot_key_reads), sum(tot_frag_scans)
457 from ndbinfo.operations_per_fragment
458 where (parent_fq_name='test/def/t2' OR fq_name='test/def/t2') and
459 (tot_key_reads > 0 or tot_frag_scans > 0)
460 group by parent_fq_name, fq_name, type, node_id;
461
462 select node_id, counter_name, sum(val) from ndbinfo.counters
463 where counter_name = 'READS' or counter_name = 'LOCAL_READS'
464 group by node_id, counter_name;
465
466 --echo OI scan
467 --replace_column 4 # 10 # 11 #
468 explain select * from test.t2 where c > 6;
469
470 --disable_query_log
471 --disable_result_log
472 select * from test.t2 where c > 6;
473 --enable_result_log
474 --enable_query_log
475 select parent_fq_name, fq_name, type, node_id, sum(tot_key_reads), sum(tot_frag_scans)
476 from ndbinfo.operations_per_fragment
477 where (parent_fq_name='test/def/t2' OR fq_name='test/def/t2') and
478 (tot_key_reads > 0 or tot_frag_scans > 0)
479 group by parent_fq_name, fq_name, type, node_id;
480
481 --echo Table scan
482 --replace_column 4 # 10 # 11 #
483 explain select * from test.t2;
484
485 --disable_query_log
486 --disable_result_log
487 select * from test.t2;
488 --enable_result_log
489 --enable_query_log
490 select parent_fq_name, fq_name, type, node_id, sum(tot_key_reads), sum(tot_frag_scans)
491 from ndbinfo.operations_per_fragment
492 where (parent_fq_name='test/def/t2' OR fq_name='test/def/t2') and
493 (tot_key_reads > 0 or tot_frag_scans > 0)
494 group by parent_fq_name, fq_name, type, node_id;
495 }
496
497 # Decrement $i to next node hint value and rerun all tests against a new node
498 let $i=0;
499 }
500 drop table hints;
501
502 delete from t1;
503 delete from t2;