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