Debian Codegraph @

mysql-test/suite/perfschema/include/table_aggregate_load.inc

raw
1 # Tests for the performance schema
2 #
3
4 # See comments in include/table_aggregate_setup.inc
5 #
6 # Notes about the optimizer and query plans:
7 # The following statement
8 # SELECT * from t1 where b=5;
9 # can be executed either:
10 # - by using the index "index_b" on column b
11 # - by using a full table scan and the where clause.
12 # Which plan is used can be unpredictable.
13 # To ensure that the index is really used,
14 # so that table io against the index is measured,
15 # the payload in this test uses:
16 # SELECT * from t1 force index(index_b) where b=5;
17 #
18
19 # Display the current setup used
20
21 select * from performance_schema.setup_actors
22 order by USER, HOST, ROLE;
23
24 select * from performance_schema.setup_objects
25 order by object_type, object_schema, object_name;
26
27 select * from performance_schema.setup_consumers;
28
29 # General cleanup
30
31 flush tables;
32
33 truncate performance_schema.objects_summary_global_by_type;
34 truncate performance_schema.table_io_waits_summary_by_index_usage;
35 truncate performance_schema.table_io_waits_summary_by_table;
36 truncate performance_schema.table_lock_waits_summary_by_table;
37 truncate performance_schema.events_waits_summary_by_thread_by_event_name;
38 truncate performance_schema.events_waits_summary_by_account_by_event_name;
39 truncate performance_schema.events_waits_summary_by_user_by_event_name;
40 truncate performance_schema.events_waits_summary_by_host_by_event_name;
41 truncate performance_schema.events_waits_summary_global_by_event_name;
42 truncate performance_schema.events_waits_history_long;
43
44 # Check the configuration is ok
45 # We don't print this as it causes too many changes in test cases if
46 # one variable changes value
47 #show variables where
48 # `Variable_name` != "performance_schema_max_statement_classes" and
49 # `Variable_name` like "performance_schema%";
50 #show status like "performance_schema%";
51
52 echo "================== Step 1 ==================";
53 call dump_thread();
54 execute dump_waits_account;
55 execute dump_waits_user;
56 execute dump_waits_host;
57 execute dump_waits_global;
58 execute dump_waits_history;
59 execute dump_waits_index_io;
60 execute dump_waits_table_io;
61 execute dump_waits_table_lock;
62 execute dump_objects_summary;
63
64 # Notes about this test
65 #
66
67 connect (con1, localhost, user1, , );
68 select concat(current_user(), " is connected") as status;
69
70 --connection default
71
72 echo "================== Step 2 ==================";
73 call dump_thread();
74 execute dump_waits_account;
75 execute dump_waits_user;
76 execute dump_waits_host;
77 execute dump_waits_global;
78 execute dump_waits_history;
79 execute dump_waits_index_io;
80 execute dump_waits_table_io;
81 execute dump_waits_table_lock;
82 execute dump_objects_summary;
83
84 --connection con1
85
86 insert into test.t1 set a=101, b=1, c=1;
87 insert into test.t2 set a=102, b=2, c=2;
88 insert into test.t2 set a=103, b=3, c=3;
89 insert into test.t3 set a=104, b=4, c=4;
90 insert into test.t3 set a=105, b=5, c=5;
91 insert into test.t3 set a=106, b=6, c=6;
92 select * from test.t1;
93 select * from test.t2;
94 select * from test.t3;
95 # Full table scan
96 update test.t1 set d=d+1;
97 update test.t2 set d=d+1;
98 update test.t3 set d=d+1;
99 # Update with PK
100 update test.t1 set d=d+1 where a=101;
101 update test.t2 set d=d+1 where a=101;
102 update test.t3 set d=d+1 where a=101;
103 # select with index
104 select * from test.t1 force index(index_b) where b=5;
105 select * from test.t2 force index(index_b) where b=5;
106 select * from test.t3 force index(index_b) where b=5;
107
108 --connection default
109
110 echo "================== Step 3-A ==================";
111 call dump_thread();
112 execute dump_waits_account;
113 execute dump_waits_user;
114 execute dump_waits_host;
115 execute dump_waits_global;
116 execute dump_waits_history;
117 execute dump_waits_index_io;
118 execute dump_waits_table_io;
119 execute dump_waits_table_lock;
120 execute dump_objects_summary;
121
122 --connection con1
123
124 # This cause aggregation, so that index names are finally recorded
125 flush tables;
126
127 echo "================== con1 FLUSH ==================";
128
129 --connection default
130
131 echo "================== Step 3-B ==================";
132 call dump_thread();
133 execute dump_waits_account;
134 execute dump_waits_user;
135 execute dump_waits_host;
136 execute dump_waits_global;
137 execute dump_waits_history;
138 execute dump_waits_index_io;
139 execute dump_waits_table_io;
140 execute dump_waits_table_lock;
141 execute dump_objects_summary;
142
143 # Debugging helpers
144 # select * from performance_schema.events_waits_history_long;
145 # select PROCESSLIST_USER, PROCESSLIST_HOST, INSTRUMENTED from performance_schema.threads;
146
147 connect (con2, localhost, user2, , );
148 select concat(current_user(), " is connected") as status;
149
150 --connection default
151
152 echo "================== Step 4 ==================";
153 call dump_thread();
154 execute dump_waits_account;
155 execute dump_waits_user;
156 execute dump_waits_host;
157 execute dump_waits_global;
158 execute dump_waits_history;
159 execute dump_waits_index_io;
160 execute dump_waits_table_io;
161 execute dump_waits_table_lock;
162 execute dump_objects_summary;
163
164 --connection con2
165
166 insert into test.t1 set a=201, b=1, c=1;
167 insert into test.t2 set a=202, b=2, c=2;
168 insert into test.t2 set a=203, b=3, c=3;
169 insert into test.t3 set a=204, b=4, c=4;
170 insert into test.t3 set a=205, b=5, c=5;
171 insert into test.t3 set a=206, b=6, c=6;
172 select * from test.t1;
173 select * from test.t2;
174 select * from test.t3;
175 # Full table scan
176 update test.t1 set d=d+1;
177 update test.t2 set d=d+1;
178 update test.t3 set d=d+1;
179 # Update with PK
180 update test.t1 set d=d+1 where a=201;
181 update test.t2 set d=d+1 where a=201;
182 update test.t3 set d=d+1 where a=201;
183 # select with index
184 select * from test.t1 force index(index_b) where b=5;
185 select * from test.t2 force index(index_b) where b=5;
186 select * from test.t3 force index(index_b) where b=5;
187
188 --connection default
189
190 echo "================== Step 5 ==================";
191 call dump_thread();
192 execute dump_waits_account;
193 execute dump_waits_user;
194 execute dump_waits_host;
195 execute dump_waits_global;
196 execute dump_waits_history;
197 execute dump_waits_index_io;
198 execute dump_waits_table_io;
199 execute dump_waits_table_lock;
200 execute dump_objects_summary;
201
202 connect (con3, localhost, user3, , );
203 select concat(current_user(), " is connected") as status;
204
205 --connection default
206
207 echo "================== Step 6 ==================";
208 call dump_thread();
209 execute dump_waits_account;
210 execute dump_waits_user;
211 execute dump_waits_host;
212 execute dump_waits_global;
213 execute dump_waits_history;
214 execute dump_waits_index_io;
215 execute dump_waits_table_io;
216 execute dump_waits_table_lock;
217 execute dump_objects_summary;
218
219 --connection con3
220
221 insert into test.t1 set a=301, b=1, c=1;
222 insert into test.t2 set a=302, b=2, c=2;
223 insert into test.t2 set a=303, b=3, c=3;
224 insert into test.t3 set a=304, b=4, c=4;
225 insert into test.t3 set a=305, b=5, c=5;
226 insert into test.t3 set a=306, b=6, c=6;
227 select * from test.t1;
228 select * from test.t2;
229 select * from test.t3;
230 # Full table scan
231 update test.t1 set d=d+1;
232 update test.t2 set d=d+1;
233 update test.t3 set d=d+1;
234 # Update with PK
235 update test.t1 set d=d+1 where a=301;
236 update test.t2 set d=d+1 where a=301;
237 update test.t3 set d=d+1 where a=301;
238 # select with index
239 select * from test.t1 force index(index_b) where b=5;
240 select * from test.t2 force index(index_b) where b=5;
241 select * from test.t3 force index(index_b) where b=5;
242
243 --connection default
244
245 echo "================== Step 7 ==================";
246 call dump_thread();
247 execute dump_waits_account;
248 execute dump_waits_user;
249 execute dump_waits_host;
250 execute dump_waits_global;
251 execute dump_waits_history;
252 execute dump_waits_index_io;
253 execute dump_waits_table_io;
254 execute dump_waits_table_lock;
255 execute dump_objects_summary;
256
257 connect (con4, localhost, user4, , );
258 select concat(current_user(), " is connected") as status;
259
260 --connection default
261
262 echo "================== Step 8 ==================";
263 call dump_thread();
264 execute dump_waits_account;
265 execute dump_waits_user;
266 execute dump_waits_host;
267 execute dump_waits_global;
268 execute dump_waits_history;
269 execute dump_waits_index_io;
270 execute dump_waits_table_io;
271 execute dump_waits_table_lock;
272 execute dump_objects_summary;
273
274 --connection con4
275
276 insert into test.t1 set a=401, b=1, c=1;
277 insert into test.t2 set a=402, b=2, c=2;
278 insert into test.t2 set a=403, b=3, c=3;
279 insert into test.t3 set a=404, b=4, c=4;
280 insert into test.t3 set a=405, b=5, c=5;
281 insert into test.t3 set a=406, b=6, c=6;
282 select * from test.t1;
283 select * from test.t2;
284 select * from test.t3;
285 # Full table scan
286 update test.t1 set d=d+1;
287 update test.t2 set d=d+1;
288 update test.t3 set d=d+1;
289 # Update with PK
290 update test.t1 set d=d+1 where a=401;
291 update test.t2 set d=d+1 where a=401;
292 update test.t3 set d=d+1 where a=401;
293 # select with index
294 select * from test.t1 force index(index_b) where b=5;
295 select * from test.t2 force index(index_b) where b=5;
296 select * from test.t3 force index(index_b) where b=5;
297
298 --connection default
299
300 echo "================== Step 9 ==================";
301 call dump_thread();
302 execute dump_waits_account;
303 execute dump_waits_user;
304 execute dump_waits_host;
305 execute dump_waits_global;
306 execute dump_waits_history;
307 execute dump_waits_index_io;
308 execute dump_waits_table_io;
309 execute dump_waits_table_lock;
310 execute dump_objects_summary;
311
312 --connection con1
313
314 lock tables test.t1 read, test.t2 read, test.t3 read;
315 unlock tables;
316 lock tables test.t1 write, test.t2 write, test.t3 write;
317 unlock tables;
318
319 --connection default
320
321 echo "================== Step 10 ==================";
322 call dump_thread();
323 execute dump_waits_account;
324 execute dump_waits_user;
325 execute dump_waits_host;
326 execute dump_waits_global;
327 execute dump_waits_history;
328 execute dump_waits_index_io;
329 execute dump_waits_table_io;
330 execute dump_waits_table_lock;
331 execute dump_objects_summary;
332
333 --connection default
334
335 flush tables;
336
337 echo "================== flush marker ==================";
338
339 echo "================== Step 11 ==================";
340 call dump_thread();
341 execute dump_waits_account;
342 execute dump_waits_user;
343 execute dump_waits_host;
344 execute dump_waits_global;
345 execute dump_waits_history;
346 execute dump_waits_index_io;
347 execute dump_waits_table_io;
348 execute dump_waits_table_lock;
349 execute dump_objects_summary;
350
351 set global read_only=1;
352 set global read_only=0;
353
354 echo "================== global read_only marker ==================";
355
356 echo "================== Step 12 ==================";
357 call dump_thread();
358 execute dump_waits_account;
359 execute dump_waits_user;
360 execute dump_waits_host;
361 execute dump_waits_global;
362 execute dump_waits_history;
363 execute dump_waits_index_io;
364 execute dump_waits_table_io;
365 execute dump_waits_table_lock;
366 execute dump_objects_summary;
367
368 --disconnect con1
369
370 # Wait for the disconnect to complete
371 let $wait_condition=
372 select count(*) = 0 from performance_schema.threads
373 where `TYPE`='FOREGROUND' and PROCESSLIST_USER= 'user1';
374 --source include/wait_condition.inc
375
376 echo "================== Step 13 ==================";
377 call dump_thread();
378 execute dump_waits_account;
379 execute dump_waits_user;
380 execute dump_waits_host;
381 execute dump_waits_global;
382 execute dump_waits_history;
383 execute dump_waits_index_io;
384 execute dump_waits_table_io;
385 execute dump_waits_table_lock;
386 execute dump_objects_summary;
387
388 --disconnect con2
389
390 # Wait for the disconnect to complete
391 let $wait_condition=
392 select count(*) = 0 from performance_schema.threads
393 where `TYPE`='FOREGROUND' and PROCESSLIST_USER= 'user2';
394 --source include/wait_condition.inc
395
396 echo "================== Step 14 ==================";
397 call dump_thread();
398 execute dump_waits_account;
399 execute dump_waits_user;
400 execute dump_waits_host;
401 execute dump_waits_global;
402 execute dump_waits_history;
403 execute dump_waits_index_io;
404 execute dump_waits_table_io;
405 execute dump_waits_table_lock;
406 execute dump_objects_summary;
407
408 --disconnect con3
409
410 # Wait for the disconnect to complete
411 let $wait_condition=
412 select count(*) = 0 from performance_schema.threads
413 where `TYPE`='FOREGROUND' and PROCESSLIST_USER= 'user3';
414 --source include/wait_condition.inc
415
416 echo "================== Step 15 ==================";
417 call dump_thread();
418 execute dump_waits_account;
419 execute dump_waits_user;
420 execute dump_waits_host;
421 execute dump_waits_global;
422 execute dump_waits_history;
423 execute dump_waits_index_io;
424 execute dump_waits_table_io;
425 execute dump_waits_table_lock;
426 execute dump_objects_summary;
427
428 --disconnect con4
429
430 # Wait for the disconnect to complete
431 let $wait_condition=
432 select count(*) = 0 from performance_schema.threads
433 where `TYPE`='FOREGROUND' and PROCESSLIST_USER= 'user4';
434 --source include/wait_condition.inc
435
436 echo "================== Step 16 ==================";
437 call dump_thread();
438 execute dump_waits_account;
439 execute dump_waits_user;
440 execute dump_waits_host;
441 execute dump_waits_global;
442 execute dump_waits_history;
443 execute dump_waits_index_io;
444 execute dump_waits_table_io;
445 execute dump_waits_table_lock;
446 execute dump_objects_summary;
447
448 --connection default
449
450 truncate performance_schema.events_waits_summary_by_thread_by_event_name;
451
452 echo "================== BY_THREAD truncated ==================";
453
454 echo "================== Step 17 ==================";
455 call dump_thread();
456 execute dump_waits_account;
457 execute dump_waits_user;
458 execute dump_waits_host;
459 execute dump_waits_global;
460 execute dump_waits_history;
461 execute dump_waits_index_io;
462 execute dump_waits_table_io;
463 execute dump_waits_table_lock;
464 execute dump_objects_summary;
465
466 truncate performance_schema.events_waits_summary_by_account_by_event_name;
467
468 echo "================== BY_ACCOUNT truncated ==================";
469
470 echo "================== Step 18 ==================";
471 call dump_thread();
472 execute dump_waits_account;
473 execute dump_waits_user;
474 execute dump_waits_host;
475 execute dump_waits_global;
476 execute dump_waits_history;
477 execute dump_waits_index_io;
478 execute dump_waits_table_io;
479 execute dump_waits_table_lock;
480 execute dump_objects_summary;
481
482 truncate performance_schema.events_waits_summary_by_user_by_event_name;
483
484 echo "================== BY_USER truncated ==================";
485
486 echo "================== Step 19 ==================";
487 call dump_thread();
488 execute dump_waits_account;
489 execute dump_waits_user;
490 execute dump_waits_host;
491 execute dump_waits_global;
492 execute dump_waits_history;
493 execute dump_waits_index_io;
494 execute dump_waits_table_io;
495 execute dump_waits_table_lock;
496 execute dump_objects_summary;
497
498 truncate performance_schema.events_waits_summary_by_host_by_event_name;
499
500 echo "================== BY_HOST truncated ==================";
501
502 echo "================== Step 21 ==================";
503 call dump_thread();
504 execute dump_waits_account;
505 execute dump_waits_user;
506 execute dump_waits_host;
507 execute dump_waits_global;
508 execute dump_waits_history;
509 execute dump_waits_index_io;
510 execute dump_waits_table_io;
511 execute dump_waits_table_lock;
512 execute dump_objects_summary;
513
514 truncate performance_schema.events_waits_summary_global_by_event_name;
515
516 echo "================== GLOBAL truncated ==================";
517
518 echo "================== Step 21 ==================";
519 call dump_thread();
520 execute dump_waits_account;
521 execute dump_waits_user;
522 execute dump_waits_host;
523 execute dump_waits_global;
524 execute dump_waits_history;
525 execute dump_waits_index_io;
526 execute dump_waits_table_io;
527 execute dump_waits_table_lock;
528 execute dump_objects_summary;
529
530 # On test failures, may help to track the root cause
531 show global status like "performance_schema%";
532