Debian Codegraph @

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

raw
1 # Tests for the performance schema
2
3 # =============
4 # DOCUMENTATION
5 # =============
6
7 # Verify how events are aggregated into various tables
8 #
9 # In the thread dimension:
10 # - events_waits_summary_by_thread_by_event_name
11 # - events_waits_summary_by_account_by_event_name
12 # - events_waits_summary_by_user_by_event_name
13 # - events_waits_summary_by_host_by_event_name
14 # - events_stages_summary_by_thread_by_event_name
15 # - events_stages_summary_by_account_by_event_name
16 # - events_stages_summary_by_user_by_event_name
17 # - events_stages_summary_by_host_by_event_name
18 # - events_statements_summary_by_thread_by_event_name
19 # - events_statements_summary_by_account_by_event_name
20 # - events_statements_summary_by_user_by_event_name
21 # - events_statements_summary_by_host_by_event_name
22 # - events_transactions_summary_by_thread_by_event_name
23 # - events_transactions_summary_by_account_by_event_name
24 # - events_transactions_summary_by_user_by_event_name
25 # - events_transactions_summary_by_host_by_event_name
26 #
27 # Globally:
28 # - events_waits_summary_global_by_event_name
29 # - events_stages_summary_global_by_event_name
30 # - events_statements_summary_global_by_event_name
31 # - events_transactions_summary_global_by_event_name
32 #
33 # The tests are written with the following helpers:
34 # - include/event_aggregate_setup.inc
35 # - include/event_aggregate_load.inc
36 # - include/event_aggregate_cleanup.inc
37 #
38 # Helpers are intended to be used as follows.
39 #
40 # A Typical test t/event_aggregate_xxx.test will consist of:
41 # --source ../include/event_aggregate_setup.inc
42 # --source ../include/event_aggregate_load.inc
43 # --source ../include/event_aggregate_cleanup.inc
44 # and a t/event_aggregate_xxx-master.opt file
45 #
46 # Naming conventions for t/event_aggregate_xxx.test are as follows:
47 # t/event_aggregate_<account><user><host>
48 #
49 # <account> corresponds to different sizing settings for
50 # the variable performance-schema-accounts-size
51 # - (blank): accounts-size sufficient to represent all records
52 # - no_a: accounts-size set to 0
53 #
54 # <user> corresponds to different sizing settings for
55 # the variable performance-schema-users-size
56 # - (blank): users-size sufficient to represent all records
57 # - no_u: users-size set to 0
58 #
59 # <host> corresponds to different sizing settings for
60 # the variable performance-schema-hosts-size
61 # - (blank): hosts-size sufficient to represent all records
62 # - no_h: hosts-size set to 0
63
64 # ========================================
65 # HELPER include/event_aggregate_setup.inc
66 # ========================================
67
68 --source include/not_embedded.inc
69 --source include/have_perfschema.inc
70 --source include/no_protocol.inc
71 --source ../include/wait_for_pfs_thread_count.inc
72
73 --disable_query_log
74 create user user1@localhost;
75 grant ALL on *.* to user1@localhost;
76 create user user2@localhost;
77 grant ALL on *.* to user2@localhost;
78 create user user3@localhost;
79 grant ALL on *.* to user3@localhost;
80 create user user4@localhost;
81 grant ALL on *.* to user4@localhost;
82
83 flush privileges;
84
85 # Purge old users, hosts, user/host from previous tests
86 truncate table performance_schema.accounts;
87 truncate table performance_schema.users;
88 truncate table performance_schema.hosts;
89
90 # Save the setup
91
92 --disable_warnings
93 drop table if exists test.setup_actors;
94 drop table if exists test.t1;
95 --enable_warnings
96
97 create table test.t1(a varchar(64));
98 create function test.f(a int, b int) returns int
99 return a+b;
100
101 create table test.setup_actors as
102 select * from performance_schema.setup_actors;
103
104 # Only instrument the user connections
105 truncate table performance_schema.setup_actors;
106 insert into performance_schema.setup_actors
107 set host= 'localhost', user= 'user1', role= '%';
108 insert into performance_schema.setup_actors
109 set host= 'localhost', user= 'user2', role= '%';
110 insert into performance_schema.setup_actors
111 set host= 'localhost', user= 'user3', role= '%';
112 insert into performance_schema.setup_actors
113 set host= 'localhost', user= 'user4', role= '%';
114
115 update performance_schema.threads set instrumented='NO';
116
117 # Only instrument a few events of each kind
118 update performance_schema.setup_instruments set enabled='NO', timed='NO';
119
120 update performance_schema.setup_instruments set enabled='YES', timed='YES'
121 where name in ('wait/synch/mutex/sql/LOCK_connection_count',
122 'wait/synch/mutex/sql/LOCK_uuid_generator',
123 'wait/synch/rwlock/sql/LOCK_grant',
124 'wait/io/file/sql/query_log',
125 'idle');
126
127 update performance_schema.setup_instruments set enabled='YES', timed='YES'
128 where name in ('stage/sql/starting',
129 'stage/sql/init',
130 'stage/sql/checking permissions',
131 'stage/sql/Opening tables',
132 'stage/sql/Closing tables');
133
134 update performance_schema.setup_instruments set enabled='YES', timed='YES'
135 where name in ('statement/sql/select',
136 'statement/sql/insert',
137 'statement/abstract/new_packet',
138 'statement/abstract/Query',
139 'statement/com/Quit',
140 'statement/com/error',
141 'statement/sp/freturn');
142
143 update performance_schema.setup_instruments set enabled='YES', timed='YES'
144 where name in ('transaction');
145
146 # Start from a known clean state, to avoid noise from previous tests
147 flush tables;
148 flush global status;
149 truncate performance_schema.events_waits_summary_by_thread_by_event_name;
150 truncate performance_schema.events_waits_summary_by_account_by_event_name;
151 truncate performance_schema.events_waits_summary_by_user_by_event_name;
152 truncate performance_schema.events_waits_summary_by_host_by_event_name;
153 truncate performance_schema.events_waits_summary_global_by_event_name;
154 truncate performance_schema.events_waits_history_long;
155
156 truncate performance_schema.events_stages_summary_by_thread_by_event_name;
157 truncate performance_schema.events_stages_summary_by_account_by_event_name;
158 truncate performance_schema.events_stages_summary_by_user_by_event_name;
159 truncate performance_schema.events_stages_summary_by_host_by_event_name;
160 truncate performance_schema.events_stages_summary_global_by_event_name;
161 truncate performance_schema.events_stages_history_long;
162
163 truncate performance_schema.events_statements_summary_by_thread_by_event_name;
164 truncate performance_schema.events_statements_summary_by_account_by_event_name;
165 truncate performance_schema.events_statements_summary_by_user_by_event_name;
166 truncate performance_schema.events_statements_summary_by_host_by_event_name;
167 truncate performance_schema.events_statements_summary_global_by_event_name;
168 truncate performance_schema.events_statements_history_long;
169
170 truncate performance_schema.events_transactions_summary_by_thread_by_event_name;
171 truncate performance_schema.events_transactions_summary_by_account_by_event_name;
172 truncate performance_schema.events_transactions_summary_by_user_by_event_name;
173 truncate performance_schema.events_transactions_summary_by_host_by_event_name;
174 truncate performance_schema.events_transactions_summary_global_by_event_name;
175 truncate performance_schema.events_transactions_history_long;
176
177 --disable_warnings
178 drop procedure if exists dump_thread;
179 drop procedure if exists dump_one_thread;
180 --enable_warnings
181
182 delimiter $$;
183
184 create procedure dump_thread()
185 begin
186 call dump_one_thread('user1');
187 call dump_one_thread('user2');
188 call dump_one_thread('user3');
189 call dump_one_thread('user4');
190 end
191 $$
192
193 create procedure dump_one_thread(in username varchar(64))
194 begin
195 declare my_thread_id int;
196
197 set my_thread_id = (select thread_id from performance_schema.threads
198 where processlist_user=username);
199
200 if (my_thread_id is not null) then
201 select username, event_name, count_star
202 from performance_schema.events_waits_summary_by_thread_by_event_name
203 where event_name in ('wait/synch/mutex/sql/LOCK_connection_count',
204 'wait/synch/mutex/sql/LOCK_uuid_generator',
205 'wait/synch/rwlock/sql/LOCK_grant',
206 'wait/io/file/sql/query_log')
207 and thread_id = my_thread_id
208 order by event_name;
209 else
210 select username, "not found" as status;
211 end if;
212 end
213 $$
214
215 delimiter ;$$
216
217 prepare dump_waits_account from
218 "select user, host, event_name, count_star
219 from performance_schema.events_waits_summary_by_account_by_event_name
220 where user like \'user%\'
221 and event_name in ('wait/synch/mutex/sql/LOCK_connection_count',
222 'wait/synch/mutex/sql/LOCK_uuid_generator',
223 'wait/synch/rwlock/sql/LOCK_grant',
224 'wait/io/file/sql/query_log')
225 order by user, host, event_name;";
226
227 prepare dump_waits_user from
228 "select user, event_name, count_star
229 from performance_schema.events_waits_summary_by_user_by_event_name
230 where user like \'user%\'
231 and event_name in ('wait/synch/mutex/sql/LOCK_connection_count',
232 'wait/synch/mutex/sql/LOCK_uuid_generator',
233 'wait/synch/rwlock/sql/LOCK_grant',
234 'wait/io/file/sql/query_log')
235 order by user, event_name;";
236
237 prepare dump_waits_host from
238 "select host, event_name, count_star
239 from performance_schema.events_waits_summary_by_host_by_event_name
240 where host=\'localhost\'
241 and event_name in ('wait/synch/mutex/sql/LOCK_connection_count',
242 'wait/synch/mutex/sql/LOCK_uuid_generator',
243 'wait/synch/rwlock/sql/LOCK_grant',
244 'wait/io/file/sql/query_log')
245 order by host, event_name;";
246
247 prepare dump_waits_global from
248 "select event_name, count_star
249 from performance_schema.events_waits_summary_global_by_event_name
250 where event_name in ('wait/synch/mutex/sql/LOCK_connection_count',
251 'wait/synch/mutex/sql/LOCK_uuid_generator',
252 'wait/synch/rwlock/sql/LOCK_grant',
253 'wait/io/file/sql/query_log')
254 order by event_name;";
255
256 prepare dump_waits_history from
257 "select event_name, count(event_name)
258 from performance_schema.events_waits_history_long
259 where event_name in ('wait/synch/mutex/sql/LOCK_connection_count',
260 'wait/synch/mutex/sql/LOCK_uuid_generator',
261 'wait/synch/rwlock/sql/LOCK_grant',
262 'wait/io/file/sql/query_log')
263 group by event_name order by event_name;";
264
265 prepare dump_stages_account from
266 "select user, host, event_name, count_star
267 from performance_schema.events_stages_summary_by_account_by_event_name
268 where user like \'user%\'
269 and event_name in ('stage/sql/starting',
270 'stage/sql/init',
271 'stage/sql/checking permissions',
272 'stage/sql/Opening tables',
273 'stage/sql/closing tables')
274 order by user, host, event_name;";
275
276 prepare dump_stages_user from
277 "select user, event_name, count_star
278 from performance_schema.events_stages_summary_by_user_by_event_name
279 where user like \'user%\'
280 and event_name in ('stage/sql/starting',
281 'stage/sql/init',
282 'stage/sql/checking permissions',
283 'stage/sql/Opening tables',
284 'stage/sql/closing tables')
285 order by user, event_name;";
286
287 prepare dump_stages_host from
288 "select host, event_name, count_star
289 from performance_schema.events_stages_summary_by_host_by_event_name
290 where host=\'localhost\'
291 and event_name in ('stage/sql/starting',
292 'stage/sql/init',
293 'stage/sql/checking permissions',
294 'stage/sql/Opening tables',
295 'stage/sql/closing tables')
296 order by host, event_name;";
297
298 prepare dump_stages_global from
299 "select event_name, count_star
300 from performance_schema.events_stages_summary_global_by_event_name
301 where event_name in ('stage/sql/starting',
302 'stage/sql/init',
303 'stage/sql/checking permissions',
304 'stage/sql/Opening tables',
305 'stage/sql/closing tables')
306 order by event_name;";
307
308 prepare dump_stages_history from
309 "select event_name, count(event_name)
310 from performance_schema.events_stages_history_long
311 where event_name in ('stage/sql/starting',
312 'stage/sql/init',
313 'stage/sql/checking permissions',
314 'stage/sql/Opening tables',
315 'stage/sql/closing tables')
316 group by event_name order by event_name;";
317
318 prepare dump_statements_account from
319 "select user, host, event_name, count_star
320 from performance_schema.events_statements_summary_by_account_by_event_name
321 where user like \'user%\'
322 and event_name in ('statement/sql/select',
323 'statement/sql/insert',
324 'statement/com/Quit',
325 'statement/com/error',
326 'statement/sp/freturn')
327 order by user, host, event_name;";
328
329 prepare dump_statements_user from
330 "select user, event_name, count_star
331 from performance_schema.events_statements_summary_by_user_by_event_name
332 where user like \'user%\'
333 and event_name in ('statement/sql/select',
334 'statement/sql/insert',
335 'statement/com/Quit',
336 'statement/com/error',
337 'statement/sp/freturn')
338 order by user, event_name;";
339
340 prepare dump_statements_host from
341 "select host, event_name, count_star
342 from performance_schema.events_statements_summary_by_host_by_event_name
343 where host=\'localhost\'
344 and event_name in ('statement/sql/select',
345 'statement/sql/insert',
346 'statement/com/Quit',
347 'statement/com/error',
348 'statement/sp/freturn')
349 order by host, event_name;";
350
351 prepare dump_statements_global from
352 "select event_name, count_star
353 from performance_schema.events_statements_summary_global_by_event_name
354 where event_name in ('statement/sql/select',
355 'statement/sql/insert',
356 'statement/com/Quit',
357 'statement/com/error',
358 'statement/sp/freturn')
359 order by event_name;";
360
361 prepare dump_statements_history from
362 "select event_name, count(event_name)
363 from performance_schema.events_statements_history_long
364 where event_name in ('statement/sql/select',
365 'statement/sql/insert',
366 'statement/com/Quit',
367 'statement/com/error',
368 'statement/sp/freturn')
369 group by event_name order by event_name;";
370
371 prepare dump_transactions_account from
372 "select user, host, event_name, count_star
373 from performance_schema.events_transactions_summary_by_account_by_event_name
374 where user like \'user%\'
375 and event_name in ('transaction')
376 order by user, host, event_name;";
377
378 prepare dump_transactions_user from
379 "select user, event_name, count_star
380 from performance_schema.events_transactions_summary_by_user_by_event_name
381 where user like \'user%\'
382 and event_name in ('transaction')
383 order by user, event_name;";
384
385 prepare dump_transactions_host from
386 "select host, event_name, count_star
387 from performance_schema.events_transactions_summary_by_host_by_event_name
388 where host=\'localhost\'
389 and event_name in ('transaction')
390 order by host, event_name;";
391
392 prepare dump_transactions_global from
393 "select event_name, count_star
394 from performance_schema.events_transactions_summary_global_by_event_name
395 where event_name in ('transaction')
396 order by event_name;";
397
398 prepare dump_transactions_history from
399 "select event_name, count(event_name)
400 from performance_schema.events_transactions_history_long
401 where event_name in ('transaction')
402 group by event_name order by event_name;";
403
404 prepare dump_users from
405 "select * from performance_schema.users where user is not null order by user;";
406
407 prepare dump_hosts from
408 "select * from performance_schema.hosts where host is not null order by host;";
409
410 prepare dump_accounts from
411 "select * from performance_schema.accounts where (user is not null) and (host is not null) order by user, host;";
412
413 --enable_query_log