Debian Codegraph @

mysql-test/suite/perfschema/t/batch_table_io_func.test

raw
1 --source include/not_embedded.inc
2 --source include/have_perfschema.inc
3
4 UPDATE performance_schema.setup_instruments SET enabled = 'NO', timed = 'YES';
5
6 UPDATE performance_schema.setup_instruments SET enabled = 'YES'
7 WHERE name in ('wait/io/table/sql/handler',
8 'wait/lock/table/sql/handler');
9
10 --disable_warnings
11 drop procedure if exists before_payload;
12 drop procedure if exists after_payload;
13 --enable_warnings
14
15 delimiter $$;
16
17 create procedure before_payload()
18 begin
19 TRUNCATE TABLE performance_schema.table_io_waits_summary_by_index_usage;
20 TRUNCATE TABLE performance_schema.table_io_waits_summary_by_table;
21 TRUNCATE TABLE performance_schema.events_waits_history_long;
22 TRUNCATE TABLE performance_schema.events_waits_history;
23 TRUNCATE TABLE performance_schema.events_waits_current;
24 end
25 $$
26
27 create procedure after_payload()
28 begin
29 select count(1) as number_seen,
30 OBJECT_TYPE, OBJECT_SCHEMA, OBJECT_NAME, INDEX_NAME,
31 OPERATION, NUMBER_OF_BYTES
32 from performance_schema.events_waits_history_long
33 where OBJECT_SCHEMA = "test"
34 group by OBJECT_TYPE, OBJECT_SCHEMA, OBJECT_NAME, INDEX_NAME, OPERATION, NUMBER_OF_BYTES;
35
36 select OBJECT_TYPE, OBJECT_SCHEMA, OBJECT_NAME, INDEX_NAME,
37 COUNT_STAR, COUNT_READ, COUNT_WRITE
38 from performance_schema.table_io_waits_summary_by_index_usage
39 where OBJECT_SCHEMA = "test"
40 order by OBJECT_TYPE, OBJECT_SCHEMA, OBJECT_NAME, INDEX_NAME;
41
42 select OBJECT_TYPE, OBJECT_SCHEMA, OBJECT_NAME,
43 COUNT_STAR, COUNT_READ, COUNT_WRITE
44 from performance_schema.table_io_waits_summary_by_table
45 where OBJECT_SCHEMA = "test"
46 order by OBJECT_TYPE, OBJECT_SCHEMA, OBJECT_NAME;
47 end
48 $$
49
50 delimiter ;$$
51
52
53 --disable_warnings
54 drop table if exists t1;
55 drop table if exists t2;
56 drop table if exists t3;
57 --enable_warnings
58
59 create table t0(v int);
60 create table t1(id1 int, a int);
61 create table t2(id1 int, id2 int, b int);
62 create table t3(id2 int, id3 int, c int);
63
64 insert into t0 values
65 (0), (1), (2), (3), (4),
66 (5), (6), (7), (8), (9);
67
68 insert into t1(id1, a)
69 select v, 100*v from t0;
70
71 insert into t2(id1, id2, b)
72 select X.v, 10*X.v + Y.v, 100*X.v + 10*Y.v
73 from t0 X, t0 Y;
74
75 insert into t3(id2, id3, c)
76 select 10*X.v + Y.v, 100*X.v + 10*Y.v + Z.v, 100*X.v + 10*Y.v + Z.v
77 from t0 X, t0 Y, t0 Z;
78
79 analyze table t1;
80 analyze table t2;
81 analyze table t3;
82
83 select * from t1 order by a;
84
85 # Only dump a small part
86 select * from t2
87 where (b >= 180) and (b <= 220)
88 order by b;
89
90 # Only dump a small part
91 select * from t3
92 where (c >= 587) and (c <= 612)
93 order by c;
94
95 #
96 # TEST 1 (join, no index)
97 #
98
99 explain extended select t1.*, t2.*, t3.*
100 from t1 join t2 using (id1) join t3 using (id2);
101
102 call before_payload();
103
104 # Payload query to analyse: should do batch io on t3
105
106 --disable_ps2_protocol
107 --disable_result_log
108 select t1.*, t2.*, t3.*
109 from t1 join t2 using (id1) join t3 using (id2);
110 --enable_result_log
111 --enable_ps2_protocol
112
113 call after_payload();
114
115 #
116 # TEST 2 (join, with index)
117 #
118
119 alter table t1 add unique index(id1);
120 alter table t2 add unique index(id2);
121 alter table t2 add index(id1);
122 alter table t3 add unique index(id3);
123 alter table t3 add index(id2);
124
125 explain extended select t1.*, t2.*, t3.*
126 from t1 join t2 using (id1) join t3 using (id2);
127
128 call before_payload();
129
130 # Payload query to analyse: should do batch io on t3
131
132 --disable_ps2_protocol
133 --disable_result_log
134 select t1.*, t2.*, t3.*
135 from t1 join t2 using (id1) join t3 using (id2);
136 --enable_result_log
137 --enable_ps2_protocol
138
139 call after_payload();
140
141 # Cleanup
142
143 drop table t0;
144 drop table t1;
145 drop table t2;
146 drop table t3;
147
148 drop procedure before_payload;
149 drop procedure after_payload;
150
151 UPDATE performance_schema.setup_instruments SET enabled = 'YES', timed = 'YES';
152