Debian Codegraph @

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

raw
1 --echo
2 --echo
3 --echo
4 --echo *******************************************************
5 --echo ndb_read_balance.inc
6 --echo Table comment : $comment
7 --echo Keys : $KEYS
8 --echo Test_step : $TEST_STEP
9 --echo Expect : $expect
10 --echo *******************************************************
11 --echo
12
13 --echo Create table with int pk, unique secondary int and key int columns
14 --echo Expect that secondary unique is distributed in the same way as main table
15 --eval CREATE TABLE test.t1 (a int, b int, c int, d int, primary key (a), unique(b) using hash, key(c)) comment='$comment' engine=ndb;
16
17
18 --disable_query_log
19 --disable_result_log
20 --let $idx=$KEYS
21 while ($idx)
22 {
23 --eval insert into test.t1 values ($idx, $idx, $idx, $idx)
24 --dec $idx
25 }
26 --enable_result_log
27 --enable_query_log
28
29 #select * from ndbinfo.memory_per_fragment where fq_name='test/def/t1';
30 --echo Check that all fragments are in-use, else raise key count
31 select count(1) from ndbinfo.memory_per_fragment where fq_name='test/def/t1' and fixed_elem_count=0;
32
33 # Debug Solaris failure
34 # Some empty fragments remain
35 --disable_query_log
36 --disable_result_log
37 select @ef:=count(1) from ndbinfo.memory_per_fragment where fq_name='test/def/t1' and fixed_elem_count=0;
38 let $empty_frags=query_get_value(select @ef as ef, ef, 1);
39
40 if ($empty_frags)
41 {
42 --enable_query_log
43 --enable_result_log
44 select * from ndbinfo.memory_per_fragment where fq_name='test/def/t1';
45 select * from test.t1;
46 --disable_result_log
47 --disable_query_log
48 }
49
50 --enable_query_log
51 --enable_result_log
52 # /Debug Solaris failure
53
54
55 --echo Build fragment identity table
56 --disable_query_log
57 --disable_result_log
58 create table fragments (node_id int,
59 block_instance int,
60 table_id int,
61 fragment_num int,
62 obj_type varchar(10),
63 prim_frag int,
64 primary key (node_id, block_instance, table_id, fragment_num));
65
66 # select * from ndbinfo.operations_per_fragment where parent_fq_name='test/def/t1';
67
68 insert into fragments select node_id, block_instance, table_id, fragment_num, "TABLE", 0
69 from ndbinfo.operations_per_fragment where fq_name='test/def/t1';
70 insert into fragments select node_id, block_instance, table_id, fragment_num, "OI_PRIMARY", 0
71 from ndbinfo.operations_per_fragment
72 where parent_fq_name='test/def/t1' and
73 fq_name like '%PRIMARY';
74 insert into fragments select node_id, block_instance, table_id, fragment_num, "UI_B", 0
75 from ndbinfo.operations_per_fragment
76 where parent_fq_name='test/def/t1' and
77 fq_name like '%b$unique';
78 insert into fragments select node_id, block_instance, table_id, fragment_num, "OI_C", 0
79 from ndbinfo.operations_per_fragment
80 where parent_fq_name='test/def/t1' and
81 fq_name like '%c';
82
83 #select * from fragments order by fragment_num;
84
85 --let $idx=$KEYS
86 while ($idx)
87 {
88 # Assume that FOR UPDATE reads primary replica
89 begin;
90 --eval select * from test.t1 where a=$idx for update;
91 select @n:=node_id, @b:=block_instance, @f:=fragmentid from ndbinfo.server_locks;
92 commit;
93 update fragments set prim_frag=1 where node_id=@n and block_instance=@b and fragment_num=@f;
94 --dec $idx
95 }
96
97 #select * from fragments order by fragment_num, node_id, table_id;
98 # Note that some fragments may be unrepresented in the data, and so have
99 # no primary marked
100
101
102 #select @pk:=ceiling(rand() * 10);
103
104 create view counters_of_interest as
105 select node_id, block_instance, table_id, fragment_num,
106 tot_key_reads,
107 tot_key_inserts, tot_key_writes, tot_key_updates, tot_key_deletes,
108 tot_frag_scans
109 from ndbinfo.operations_per_fragment
110 where fq_name='test/def/t1' or
111 parent_fq_name='test/def/t1';
112
113 create table baseline as select * from counters_of_interest;
114
115 create view baseline_diff as
116 select a.node_id as node_id,
117 a.block_instance as block_instance,
118 a.table_id as table_id,
119 a.fragment_num as fragment_num,
120 a.tot_key_reads - b.tot_key_reads as key_reads,
121 a.tot_key_inserts - b.tot_key_inserts as key_inserts,
122 a.tot_key_writes - b.tot_key_writes as key_writes,
123 a.tot_key_updates - b.tot_key_updates as key_updates,
124 a.tot_key_deletes - b.tot_key_deletes as key_deletes,
125 a.tot_frag_scans - b.tot_frag_scans as frag_scans
126 from
127 counters_of_interest a
128 join
129 baseline b
130 on
131 a.node_id = b.node_id and
132 a.block_instance = b.block_instance and
133 a.fragment_num = b.fragment_num and
134 a.table_id = b.table_id;
135
136 create view min_baseline_diff as
137 select * from baseline_diff
138 where key_reads > 0 or
139 key_inserts > 0 or
140 key_writes > 0 or
141 key_updates > 0 or
142 key_deletes > 0 or
143 frag_scans > 0;
144
145 create view min_baseline_diff_type as
146 select t.obj_type,
147 if(t.prim_frag=1, 'PRIMARY', 'BACKUP') as fragment_type,
148 bl.key_reads,
149 bl.key_inserts,
150 bl.key_writes,
151 bl.key_updates,
152 bl.key_deletes,
153 bl.frag_scans
154 from
155 min_baseline_diff bl
156 join
157 fragments t
158 on
159 bl.node_id = t.node_id and
160 bl.block_instance = t.block_instance and
161 bl.table_id = t.table_id and
162 bl.fragment_num = t.fragment_num;
163
164 create view min_baseline_diff_typesonly as
165 select obj_type, fragment_type
166 from min_baseline_diff_type
167 order by obj_type, fragment_type;
168
169 create table unrelated(a int primary key) engine=ndb;
170
171 # Randomise offsets of hinted + unhinted TC selection
172 let $offset=query_get_value('select floor(rand() * 10) as r', r, 1);
173 while ($offset)
174 {
175 begin; select * from unrelated;
176 begin; select * from unrelated where a=7;
177 dec $offset;
178 }
179
180 --enable_result_log
181 --enable_query_log
182
183 --echo Test lock free pk reads to show fragment types read
184 --let $idx=$KEYS
185
186 --disable_query_log
187 --disable_result_log
188 delete from baseline;
189 insert into baseline select * from counters_of_interest;
190 # Using a transaction, the first will hint to some node, so that some of
191 # the other reads will be remote
192 begin;
193 while ($idx)
194 {
195 --eval select * from test.t1 where a=$idx;
196 --dec $idx
197 }
198 commit;
199 --enable_result_log
200 --enable_query_log
201
202 select distinct obj_type, fragment_type from min_baseline_diff_type order by obj_type, fragment_type;
203
204 --echo Test a number of scenarios repeatedly to show read routing variants
205
206 --let $reps=10
207
208 --let $idx=$KEYS
209 while ($idx)
210 {
211 --disable_query_log
212 --echo
213 --echo ****
214 --echo Row with key : $idx
215 --echo
216 --echo Unhinted PK read, no lock
217 --disable_result_log
218 delete from baseline;
219 insert into baseline select * from counters_of_interest;
220 --let $r=$reps
221 while ($r)
222 {
223 begin;
224 select * from unrelated;
225 --eval select * from test.t1 where a=$idx;
226 commit;
227 --dec $r
228 }
229 --enable_result_log
230
231 select * from min_baseline_diff_typesonly;
232
233 --echo
234 --echo Hinted PK read, no lock
235 --disable_result_log
236 delete from baseline;
237 insert into baseline select * from counters_of_interest;
238 --let $r=$reps
239 while ($r)
240 {
241 --eval select * from test.t1 where a=$idx;
242 --dec $r
243 }
244 --enable_result_log
245
246 select * from min_baseline_diff_typesonly;
247
248 --echo
249 --echo Unhinted PK read, shared lock
250 --disable_result_log
251 delete from baseline;
252 insert into baseline select * from counters_of_interest;
253 --let $r=$reps
254 while ($r)
255 {
256 begin;
257 select * from unrelated;
258 --eval select * from test.t1 where a=$idx lock in share mode;
259 commit;
260 --dec $r
261 }
262 --enable_result_log
263
264 select * from min_baseline_diff_typesonly;
265
266 --echo
267 --echo Unhinted PK read, exclusive lock
268 --disable_result_log
269 delete from baseline;
270 insert into baseline select * from counters_of_interest;
271 --let $r=$reps
272 while ($r)
273 {
274 begin;
275 select * from unrelated;
276 --eval select * from test.t1 where a=$idx for update;
277 commit;
278 --dec $r
279 }
280 --enable_result_log
281
282 select * from min_baseline_diff_typesonly;
283
284 --echo
285 --echo Unhinted UI read, no lock
286 --disable_result_log
287 delete from baseline;
288 insert into baseline select * from counters_of_interest;
289 --let $r=$reps
290 while ($r)
291 {
292 begin;
293 select * from unrelated;
294 --eval select * from test.t1 where b=$idx;
295 commit;
296 --dec $r
297 }
298 --enable_result_log
299
300 select * from min_baseline_diff_typesonly;
301
302 --echo
303 --echo Unhinted UI read, shared lock
304 --disable_result_log
305 delete from baseline;
306 insert into baseline select * from counters_of_interest;
307 --let $r=$reps
308 while ($r)
309 {
310 begin;
311 select * from unrelated;
312 --eval select * from test.t1 where b=$idx lock in share mode;
313 commit;
314 --dec $r
315 }
316 --enable_result_log
317
318 select * from min_baseline_diff_typesonly;
319
320 --echo
321 --echo Unhinted UI read, exclusive lock
322 --disable_result_log
323 delete from baseline;
324 insert into baseline select * from counters_of_interest;
325 --let $r=$reps
326 while ($r)
327 {
328 begin;
329 select * from unrelated;
330 --eval select * from test.t1 where b=$idx for update;
331 commit;
332 --dec $r
333 }
334 --enable_result_log
335
336 select * from min_baseline_diff_typesonly;
337
338 --enable_query_log
339
340 # Skip over some of the keys
341 --let $i=$TEST_STEP
342 while($i)
343 {
344 --dec $idx
345 --dec $i
346 }
347 }
348 --echo
349 --echo *********
350 --echo Scans
351
352 --disable_query_log
353 --echo
354 --echo Unhinted PK OI read, no lock
355 --disable_result_log
356 delete from baseline;
357 insert into baseline select * from counters_of_interest;
358 begin;
359 select * from unrelated;
360 --eval select * from test.t1 where a > 0;
361 commit;
362 --enable_result_log
363
364 select distinct obj_type, fragment_type from min_baseline_diff_type order by obj_type, fragment_type;
365
366 --echo
367 --echo Unhinted PK OI read, shared lock
368 --disable_result_log
369 delete from baseline;
370 insert into baseline select * from counters_of_interest;
371 begin;
372 select * from unrelated;
373 --eval select * from test.t1 where a > 0 lock in share mode;
374 commit;
375 --enable_result_log
376
377 select distinct obj_type, fragment_type from min_baseline_diff_type order by obj_type, fragment_type;
378
379 --echo
380 --echo Unhinted PK OI read, exclusive lock
381 --disable_result_log
382 delete from baseline;
383 insert into baseline select * from counters_of_interest;
384 begin;
385 select * from unrelated;
386 --eval select * from test.t1 where a > 0 for update;
387 commit;
388 --enable_result_log
389
390 select distinct obj_type, fragment_type from min_baseline_diff_type order by obj_type, fragment_type;
391
392
393 --echo
394 --echo Table scan, no lock
395 --disable_result_log
396 delete from baseline;
397 insert into baseline select * from counters_of_interest;
398 --eval select * from test.t1;
399 --enable_result_log
400
401 select distinct obj_type, fragment_type from min_baseline_diff_type order by obj_type, fragment_type;
402
403 --echo
404 --echo Table scan, shared lock
405 --disable_result_log
406 delete from baseline;
407 insert into baseline select * from counters_of_interest;
408 --eval select * from test.t1 lock in share mode;
409 --enable_result_log
410
411 select distinct obj_type, fragment_type from min_baseline_diff_type order by obj_type, fragment_type;
412
413 --echo
414 --echo Table scan, exclusive lock
415 --disable_result_log
416 delete from baseline;
417 insert into baseline select * from counters_of_interest;
418 --eval select * from test.t1 for update;
419 --enable_result_log
420
421 select distinct obj_type, fragment_type from min_baseline_diff_type order by obj_type, fragment_type;
422
423
424 drop table unrelated;
425 drop table fragments;
426 drop view counters_of_interest;
427 drop table baseline;
428 drop view baseline_diff;
429 drop view min_baseline_diff;
430 drop view min_baseline_diff_type;
431 drop view min_baseline_diff_typesonly;
432 drop table test.t1;
433 --enable_query_log