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 show global variables like "performance_schema%table%";
46 show global status like "performance_schema%table%_lost";
47
48 echo "================== Step 1 ==================";
49 call dump_thread();
50 execute dump_waits_account;
51 execute dump_waits_user;
52 execute dump_waits_host;
53 execute dump_waits_global;
54 execute dump_waits_history;
55 execute dump_waits_index_io;
56 execute dump_waits_table_io;
57 execute dump_waits_table_lock;
58 execute dump_objects_summary;
59
60 # Notes about this test
61 #
62
63 connect (con1, localhost, user1, , );
64 select concat(current_user(), " is connected") as status;
65
66 --connection default
67
68 echo "================== Step 2 ==================";
69 call dump_thread();
70 execute dump_waits_account;
71 execute dump_waits_user;
72 execute dump_waits_host;
73 execute dump_waits_global;
74 execute dump_waits_history;
75 execute dump_waits_index_io;
76 execute dump_waits_table_io;
77 execute dump_waits_table_lock;
78 execute dump_objects_summary;
79
80 --connection con1
81
82 insert into test.t1 set a=101, b=1, c=1;
83 insert into test.t2 set a=102, b=2, c=2;
84 insert into test.t2 set a=103, b=3, c=3;
85 insert into test.t3 set a=104, b=4, c=4;
86 insert into test.t3 set a=105, b=5, c=5;
87 insert into test.t3 set a=106, b=6, c=6;
88 select * from test.t1;
89 select * from test.t2;
90 select * from test.t3;
91 # Full table scan
92 update test.t1 set d=d+1;
93 update test.t2 set d=d+1;
94 update test.t3 set d=d+1;
95 # Update with PK
96 update test.t1 set d=d+1 where a=101;
97 update test.t2 set d=d+1 where a=101;
98 update test.t3 set d=d+1 where a=101;
99 # select with index
100 select * from test.t1 force index(index_b) where b=5;
101 select * from test.t2 force index(index_b) where b=5;
102 select * from test.t3 force index(index_b) where b=5;
103
104 echo "================== con1 marker ==================";
105
106 --connection default
107
108 echo "================== Step 3-A ==================";
109 call dump_thread();
110 execute dump_waits_account;
111 execute dump_waits_user;
112 execute dump_waits_host;
113 execute dump_waits_global;
114 execute dump_waits_history;
115 execute dump_waits_index_io;
116 execute dump_waits_table_io;
117 execute dump_waits_table_lock;
118 execute dump_objects_summary;
119
120 --connection con1
121
122 # This cause aggregation, so that index names are finally recorded
123 flush tables;
124
125 echo "================== con1 FLUSH ==================";
126
127 --connection default
128
129 echo "================== Step 3-B ==================";
130 call dump_thread();
131 execute dump_waits_account;
132 execute dump_waits_user;
133 execute dump_waits_host;
134 execute dump_waits_global;
135 execute dump_waits_history;
136 execute dump_waits_index_io;
137 execute dump_waits_table_io;
138 execute dump_waits_table_lock;
139 execute dump_objects_summary;
140
141 # Debugging helpers
142 # select * from performance_schema.events_waits_history_long;
143 # select PROCESSLIST_USER, PROCESSLIST_HOST, INSTRUMENTED from performance_schema.threads;
144
145 connect (con2, localhost, user2, , );
146 select concat(current_user(), " is connected") as status;
147
148 --connection default
149
150 echo "================== Step 4 ==================";
151 call dump_thread();
152 execute dump_waits_account;
153 execute dump_waits_user;
154 execute dump_waits_host;
155 execute dump_waits_global;
156 execute dump_waits_history;
157 execute dump_waits_index_io;
158 execute dump_waits_table_io;
159 execute dump_waits_table_lock;
160 execute dump_objects_summary;
161
162 --connection con2
163
164 insert into test.t1 set a=201, b=1, c=1;
165 insert into test.t2 set a=202, b=2, c=2;
166 insert into test.t2 set a=203, b=3, c=3;
167 insert into test.t3 set a=204, b=4, c=4;
168 insert into test.t3 set a=205, b=5, c=5;
169 insert into test.t3 set a=206, b=6, c=6;
170 select * from test.t1;
171 select * from test.t2;
172 select * from test.t3;
173 # Full table scan
174 update test.t1 set d=d+1;
175 update test.t2 set d=d+1;
176 update test.t3 set d=d+1;
177 # Update with PK
178 update test.t1 set d=d+1 where a=201;
179 update test.t2 set d=d+1 where a=201;
180 update test.t3 set d=d+1 where a=201;
181 # select with index
182 select * from test.t1 force index(index_b) where b=5;
183 select * from test.t2 force index(index_b) where b=5;
184 select * from test.t3 force index(index_b) where b=5;
185
186 echo "================== con2 marker ==================";
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 echo "================== con3 marker ==================";
244
245 --connection default
246
247 echo "================== Step 7 ==================";
248 call dump_thread();
249 execute dump_waits_account;
250 execute dump_waits_user;
251 execute dump_waits_host;
252 execute dump_waits_global;
253 execute dump_waits_history;
254 execute dump_waits_index_io;
255 execute dump_waits_table_io;
256 execute dump_waits_table_lock;
257 execute dump_objects_summary;
258
259 connect (con4, localhost, user4, , );
260 select concat(current_user(), " is connected") as status;
261
262 --connection default
263
264 echo "================== Step 8 ==================";
265 call dump_thread();
266 execute dump_waits_account;
267 execute dump_waits_user;
268 execute dump_waits_host;
269 execute dump_waits_global;
270 execute dump_waits_history;
271 execute dump_waits_index_io;
272 execute dump_waits_table_io;
273 execute dump_waits_table_lock;
274 execute dump_objects_summary;
275
276 --connection con4
277
278 insert into test.t1 set a=401, b=1, c=1;
279 insert into test.t2 set a=402, b=2, c=2;
280 insert into test.t2 set a=403, b=3, c=3;
281 insert into test.t3 set a=404, b=4, c=4;
282 insert into test.t3 set a=405, b=5, c=5;
283 insert into test.t3 set a=406, b=6, c=6;
284 select * from test.t1;
285 select * from test.t2;
286 select * from test.t3;
287 # Full table scan
288 update test.t1 set d=d+1;
289 update test.t2 set d=d+1;
290 update test.t3 set d=d+1;
291 # Update with PK
292 update test.t1 set d=d+1 where a=401;
293 update test.t2 set d=d+1 where a=401;
294 update test.t3 set d=d+1 where a=401;
295 # select with index
296 select * from test.t1 force index(index_b) where b=5;
297 select * from test.t2 force index(index_b) where b=5;
298 select * from test.t3 force index(index_b) where b=5;
299
300 echo "================== con4 marker ==================";
301
302 --connection default
303
304 echo "================== Step 9 ==================";
305 call dump_thread();
306 execute dump_waits_account;
307 execute dump_waits_user;
308 execute dump_waits_host;
309 execute dump_waits_global;
310 execute dump_waits_history;
311 execute dump_waits_index_io;
312 execute dump_waits_table_io;
313 execute dump_waits_table_lock;
314 execute dump_objects_summary;
315
316 --connection con1
317
318 lock tables test.t1 read, test.t2 read, test.t3 read;
319 unlock tables;
320 lock tables test.t1 write, test.t2 write, test.t3 write;
321 unlock tables;
322
323 echo "================== con1 marker ==================";
324
325 --connection default
326
327 echo "================== Step 10 ==================";
328 call dump_thread();
329 execute dump_waits_account;
330 execute dump_waits_user;
331 execute dump_waits_host;
332 execute dump_waits_global;
333 execute dump_waits_history;
334 execute dump_waits_index_io;
335 execute dump_waits_table_io;
336 execute dump_waits_table_lock;
337 execute dump_objects_summary;
338
339 --connection default
340
341 flush tables;
342
343 echo "================== flush marker ==================";
344
345 echo "================== Step 11 ==================";
346 call dump_thread();
347 execute dump_waits_account;
348 execute dump_waits_user;
349 execute dump_waits_host;
350 execute dump_waits_global;
351 execute dump_waits_history;
352 execute dump_waits_index_io;
353 execute dump_waits_table_io;
354 execute dump_waits_table_lock;
355 execute dump_objects_summary;
356
357 set global read_only=1;
358 set global read_only=0;
359
360 echo "================== global read_only marker ==================";
361
362 echo "================== Step 12 ==================";
363 call dump_thread();
364 execute dump_waits_account;
365 execute dump_waits_user;
366 execute dump_waits_host;
367 execute dump_waits_global;
368 execute dump_waits_history;
369 execute dump_waits_index_io;
370 execute dump_waits_table_io;
371 execute dump_waits_table_lock;
372 execute dump_objects_summary;
373
374 --disconnect con1
375
376 # Wait for the disconnect to complete
377 let $wait_condition=
378 select count(*) = 0 from performance_schema.threads
379 where `TYPE`='FOREGROUND' and PROCESSLIST_USER= 'user1';
380 --source include/wait_condition.inc
381
382 echo "================== con1 disconnected ==================";
383
384 echo "================== Step 13 ==================";
385 call dump_thread();
386 execute dump_waits_account;
387 execute dump_waits_user;
388 execute dump_waits_host;
389 execute dump_waits_global;
390 execute dump_waits_history;
391 execute dump_waits_index_io;
392 execute dump_waits_table_io;
393 execute dump_waits_table_lock;
394 execute dump_objects_summary;
395
396 --disconnect con2
397
398 # Wait for the disconnect to complete
399 let $wait_condition=
400 select count(*) = 0 from performance_schema.threads
401 where `TYPE`='FOREGROUND' and PROCESSLIST_USER= 'user2';
402 --source include/wait_condition.inc
403
404 echo "================== con2 disconnected ==================";
405
406 echo "================== Step 14 ==================";
407 call dump_thread();
408 execute dump_waits_account;
409 execute dump_waits_user;
410 execute dump_waits_host;
411 execute dump_waits_global;
412 execute dump_waits_history;
413 execute dump_waits_index_io;
414 execute dump_waits_table_io;
415 execute dump_waits_table_lock;
416 execute dump_objects_summary;
417
418 --disconnect con3
419
420 # Wait for the disconnect to complete
421 let $wait_condition=
422 select count(*) = 0 from performance_schema.threads
423 where `TYPE`='FOREGROUND' and PROCESSLIST_USER= 'user3';
424 --source include/wait_condition.inc
425
426 echo "================== con3 disconnected ==================";
427
428 echo "================== Step 15 ==================";
429 call dump_thread();
430 execute dump_waits_account;
431 execute dump_waits_user;
432 execute dump_waits_host;
433 execute dump_waits_global;
434 execute dump_waits_history;
435 execute dump_waits_index_io;
436 execute dump_waits_table_io;
437 execute dump_waits_table_lock;
438 execute dump_objects_summary;
439
440 --disconnect con4
441
442 # Wait for the disconnect to complete
443 let $wait_condition=
444 select count(*) = 0 from performance_schema.threads
445 where `TYPE`='FOREGROUND' and PROCESSLIST_USER= 'user4';
446 --source include/wait_condition.inc
447
448 echo "================== con4 disconnected ==================";
449
450 echo "================== Step 16 ==================";
451 call dump_thread();
452 execute dump_waits_account;
453 execute dump_waits_user;
454 execute dump_waits_host;
455 execute dump_waits_global;
456 execute dump_waits_history;
457 execute dump_waits_index_io;
458 execute dump_waits_table_io;
459 execute dump_waits_table_lock;
460 execute dump_objects_summary;
461
462 --connection default
463
464 truncate performance_schema.events_waits_summary_by_thread_by_event_name;
465
466 echo "================== BY_THREAD truncated ==================";
467
468 echo "================== Step 17 ==================";
469 call dump_thread();
470 execute dump_waits_account;
471 execute dump_waits_user;
472 execute dump_waits_host;
473 execute dump_waits_global;
474 execute dump_waits_history;
475 execute dump_waits_index_io;
476 execute dump_waits_table_io;
477 execute dump_waits_table_lock;
478 execute dump_objects_summary;
479
480 truncate performance_schema.events_waits_summary_by_account_by_event_name;
481
482 echo "================== BY_ACCOUNT truncated ==================";
483
484 echo "================== Step 18 ==================";
485 call dump_thread();
486 execute dump_waits_account;
487 execute dump_waits_user;
488 execute dump_waits_host;
489 execute dump_waits_global;
490 execute dump_waits_history;
491 execute dump_waits_index_io;
492 execute dump_waits_table_io;
493 execute dump_waits_table_lock;
494 execute dump_objects_summary;
495
496 truncate performance_schema.events_waits_summary_by_user_by_event_name;
497
498 echo "================== BY_USER truncated ==================";
499
500 echo "================== Step 19 ==================";
501 call dump_thread();
502 execute dump_waits_account;
503 execute dump_waits_user;
504 execute dump_waits_host;
505 execute dump_waits_global;
506 execute dump_waits_history;
507 execute dump_waits_index_io;
508 execute dump_waits_table_io;
509 execute dump_waits_table_lock;
510 execute dump_objects_summary;
511
512 truncate performance_schema.events_waits_summary_by_host_by_event_name;
513
514 echo "================== BY_HOST truncated ==================";
515
516 echo "================== Step 21 ==================";
517 call dump_thread();
518 execute dump_waits_account;
519 execute dump_waits_user;
520 execute dump_waits_host;
521 execute dump_waits_global;
522 execute dump_waits_history;
523 execute dump_waits_index_io;
524 execute dump_waits_table_io;
525 execute dump_waits_table_lock;
526 execute dump_objects_summary;
527
528 truncate performance_schema.events_waits_summary_global_by_event_name;
529
530 echo "================== GLOBAL truncated ==================";
531
532 echo "================== Step 21 ==================";
533 call dump_thread();
534 execute dump_waits_account;
535 execute dump_waits_user;
536 execute dump_waits_host;
537 execute dump_waits_global;
538 execute dump_waits_history;
539 execute dump_waits_index_io;
540 execute dump_waits_table_io;
541 execute dump_waits_table_lock;
542 execute dump_objects_summary;
543
544 # On test failures, may help to track the root cause
545 show global status like "performance_schema%table%_lost";
546