Debian Codegraph @

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

raw
1 # Tests for the performance schema
2
3 # =============
4 # DOCUMENTATION
5 # =============
6
7 # Verify how table io is aggregated into various tables
8 #
9 # In the instance dimension:
10 # - table_io_waits_summary_by_index_usage
11 # - table_io_waits_summary_by_table
12 # - table_lock_waits_summary_by_table
13 # - objects_summary_global_by_type
14 #
15 # In the thread dimension:
16 # - events_waits_summary_by_thread_by_event_name
17 # - events_waits_summary_by_account_by_event_name
18 # - events_waits_summary_by_user_by_event_name
19 # - events_waits_summary_by_host_by_event_name
20 #
21 # Globally:
22 # - events_waits_summary_global_by_event_name
23 #
24 # The tests are written with the following helpers:
25 # - include/table_aggregate_setup.inc
26 # - include/table_aggregate_load.inc
27 # - include/table_aggregate_cleanup.inc
28 #
29 # Helpers are intended to be used as follows.
30 #
31 # A Typical test t/table_aggregate_xxx.test will consist of:
32 # --source ../include/table_aggregate_setup.inc
33 #
34 # ... statements to modify the default configuration ...
35 #
36 # --source ../include/table_aggregate_load.inc
37 # --source ../include/table_aggregate_cleanup.inc
38 #
39 # Naming conventions for t/table_aggregate_xxx.test are as follows:
40 # t/<instrument>aggregate_<mode><actors><objects>
41 #
42 # <instrument> corresponds to different instruments settings
43 # - table: both table io and table lock are instrumented
44 # - table_io: only table io is instrumented
45 # - table_lock: only table lock is instrumented
46 #
47 # <mode> corresponds to different consumers settings
48 # - off: global_instrumentation OFF
49 # - global: global_instrumentation ON, thread_instrumentation OFF
50 # - thread: global_instrumentation ON, thread_instrumentation ON,
51 # events_* consumers OFF
52 # - history: global_instrumentation ON, thread_instrumentation ON,
53 # events_* consumers ON
54 #
55 # <actors> corresponds to different setup_actors settings
56 # - 4u: every test user (user1, user2, user3, user4) is ON
57 # - 2u: (user1, user3) are ON, (user2, user4) are OFF
58 #
59 # <objects> corresponds to different setup_objects settings
60 # - 3t: tables t1, t2 and t3 are ON
61 # - 2t: tables t1 and t3 are ON, table t2 is OFF
62 #
63
64 # ========================================
65 # HELPER include/table_aggregate_setup.inc
66 # ========================================
67
68 # Test requires: sp-protocol/ps-protocol/view-protocol/cursor-protocol disabled
69 --source include/no_protocol.inc
70 --source ../include/wait_for_pfs_thread_count.inc
71
72 --disable_query_log
73 create user user1@localhost, user2@localhost,
74 user3@localhost, user4@localhost;
75 grant ALL on *.* to user1@localhost;
76 grant ALL on *.* to user2@localhost;
77 grant ALL on *.* to user3@localhost;
78 grant ALL on *.* to user4@localhost;
79
80 flush privileges;
81
82 # Purge old users, hosts, user/host from previous tests
83 truncate table performance_schema.accounts;
84 truncate table performance_schema.users;
85 truncate table performance_schema.hosts;
86
87 # Save the setup
88
89 --disable_warnings
90 drop table if exists test.setup_actors;
91 drop table if exists test.setup_objects;
92 --enable_warnings
93
94 create table test.setup_actors as
95 select * from performance_schema.setup_actors;
96
97 create table test.setup_objects as
98 select * from performance_schema.setup_objects;
99
100 # Only instrument the user connections (by default)
101 truncate table performance_schema.setup_actors;
102 insert into performance_schema.setup_actors
103 set host= 'localhost', user= 'user1', `role`= '%';
104 insert into performance_schema.setup_actors
105 set host= 'localhost', user= 'user2', `role`= '%';
106 insert into performance_schema.setup_actors
107 set host= 'localhost', user= 'user3', `role`= '%';
108 insert into performance_schema.setup_actors
109 set host= 'localhost', user= 'user4', `role`= '%';
110
111 # Only instrument test.t% tables (by default)
112 truncate table performance_schema.setup_objects;
113 insert into performance_schema.setup_objects
114 set object_type='TABLE', object_schema='test', object_name='t1', timed='YES';
115 insert into performance_schema.setup_objects
116 set object_type='TABLE', object_schema='test', object_name='t2', timed='NO';
117 insert into performance_schema.setup_objects
118 set object_type='TABLE', object_schema='test', object_name='t3', timed='NO';
119
120 update performance_schema.threads set instrumented='NO';
121
122 # Only instrument table io and lock (by default)
123 update performance_schema.setup_instruments set enabled='NO', timed='NO';
124 update performance_schema.setup_instruments set enabled='YES', timed='YES'
125 where name in ('wait/io/table/sql/handler',
126 'wait/lock/table/sql/handler');
127
128 # Enable all consumers (by default)
129 update performance_schema.setup_consumers set enabled='YES';
130
131 # Start from a known clean state, to avoid noise from previous tests
132 flush tables;
133 flush status;
134
135 create table test.t1(a int, b int, c int, d int default 0,
136 primary key(a),
137 index index_b(b),
138 index index_cb(c, b));
139 create table test.t2 like test.t1;
140 create table test.t3 like test.t1;
141
142 #
143 # Note:
144 # For test robustness and to avoid picking up noise from other tests scripts,
145 # it is better to use:
146 # in ('t1', 't2', 't3)
147 # explicitly instead of:
148 # like 't%'
149
150 --disable_warnings
151 drop procedure if exists dump_thread;
152 drop procedure if exists dump_one_thread;
153 --enable_warnings
154
155 delimiter $$;
156
157 create procedure dump_thread()
158 begin
159 call dump_one_thread('user1');
160 call dump_one_thread('user2');
161 call dump_one_thread('user3');
162 call dump_one_thread('user4');
163 end
164 $$
165
166 create procedure dump_one_thread(in username varchar(64))
167 begin
168 declare my_thread_id int;
169
170 set my_thread_id = (select thread_id from performance_schema.threads
171 where processlist_user=username);
172
173 if (my_thread_id is not null) then
174 select username, event_name, count_star
175 from performance_schema.events_waits_summary_by_thread_by_event_name
176 where event_name in
177 ('wait/io/table/sql/handler',
178 'wait/lock/table/sql/handler')
179 and thread_id = my_thread_id
180 order by event_name;
181 else
182 select username, "not found" as status;
183 end if;
184 end
185 $$
186
187 delimiter ;$$
188
189 prepare dump_waits_user from
190 "select user, event_name, count_star
191 from performance_schema.events_waits_summary_by_user_by_event_name
192 where user like \'user%\' and event_name in
193 (\'wait/io/table/sql/handler\',
194 \'wait/lock/table/sql/handler\')
195 order by user, event_name;";
196
197 prepare dump_waits_account from
198 "select user, host, event_name, count_star
199 from performance_schema.events_waits_summary_by_account_by_event_name
200 where user like \'user%\' and event_name in
201 (\'wait/io/table/sql/handler\',
202 \'wait/lock/table/sql/handler\')
203 order by user, host, event_name;";
204
205 prepare dump_waits_host from
206 "select host, event_name, count_star
207 from performance_schema.events_waits_summary_by_host_by_event_name
208 where host=\'localhost\' and event_name in
209 (\'wait/io/table/sql/handler\',
210 \'wait/lock/table/sql/handler\')
211 order by host, event_name;";
212
213 prepare dump_waits_global from
214 "select event_name, count_star
215 from performance_schema.events_waits_summary_global_by_event_name
216 where event_name in
217 (\'wait/io/table/sql/handler\',
218 \'wait/lock/table/sql/handler\')
219 order by event_name;";
220
221 prepare dump_waits_history from
222 "select event_name,
223 sum(if(number_of_bytes is null, 1, number_of_bytes)) as 'count(event_name)',
224 object_type, object_schema, object_name
225 from performance_schema.events_waits_history_long
226 where event_name in
227 (\'wait/io/table/sql/handler\',
228 \'wait/lock/table/sql/handler\')
229 group by object_type, object_schema, object_name, event_name
230 order by object_type, object_schema, object_name, event_name;";
231
232 prepare dump_waits_index_io from
233 "select object_type, object_schema, object_name, index_name,
234 count_star, count_read, count_write,
235 count_fetch, count_insert, count_update, count_delete
236 from performance_schema.table_io_waits_summary_by_index_usage
237 where object_type='TABLE' and object_schema='test'
238 and object_name in ('t1', 't2', 't3')
239 order by object_type, object_schema, object_name, index_name;";
240
241 prepare dump_waits_table_io from
242 "select object_type, object_schema, object_name,
243 count_star, count_read, count_write,
244 count_fetch, count_insert, count_update, count_delete
245 from performance_schema.table_io_waits_summary_by_table
246 where object_type='TABLE' and object_schema='test'
247 and object_name in ('t1', 't2', 't3')
248 order by object_type, object_schema, object_name";
249
250 prepare dump_waits_table_lock from
251 "select object_type, object_schema, object_name,
252 count_star, count_read, count_write,
253 count_read_normal, count_read_with_shared_locks,
254 count_read_high_priority, count_read_no_insert,
255 count_read_external,
256 count_write_low_priority,
257 count_write_external
258 from performance_schema.table_lock_waits_summary_by_table
259 where object_type='TABLE' and object_schema='test'
260 and object_name in ('t1', 't2', 't3')
261 order by object_type, object_schema, object_name";
262
263 prepare dump_objects_summary from
264 "select object_type, object_schema, object_name, count_star
265 from performance_schema.objects_summary_global_by_type
266 where object_type='TABLE' and object_schema='test'
267 and object_name in ('t1', 't2', 't3')
268 order by object_type, object_schema, object_name";
269
270 --enable_query_log