Debian Codegraph @

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

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