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 # 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
74 create user user1@localhost, user2@localhost,
75 user3@localhost, user4@localhost;
76 grant ALL on *.* to user1@localhost;
77 grant ALL on *.* to user2@localhost;
78 grant ALL on *.* to user3@localhost;
79 grant ALL on *.* to user4@localhost;
80
81 flush privileges;
82
83 # Purge old users, hosts, user/host from previous tests
84 truncate table performance_schema.accounts;
85 truncate table performance_schema.users;
86 truncate table performance_schema.hosts;
87
88 # Save the setup
89
90 --disable_warnings
91 drop table if exists test.setup_actors;
92 drop table if exists test.t1;
93 --enable_warnings
94
95 create table test.t1(a varchar(64));
96
97 create function test.f(a int, b int) returns int
98 return a+b;
99
100 # Warm-up data-dictionary cache
101 --disable_result_log
102 show create table test.t1;
103 show create function test.f;
104 --enable_result_log
105
106 create table test.setup_actors as
107 select * from performance_schema.setup_actors;
108
109 # Only instrument the user connections
110 truncate table performance_schema.setup_actors;
111 insert into performance_schema.setup_actors
112 set host= 'localhost', user= 'user1', `role`= '%';
113 insert into performance_schema.setup_actors
114 set host= 'localhost', user= 'user2', `role`= '%';
115 insert into performance_schema.setup_actors
116 set host= 'localhost', user= 'user3', `role`= '%';
117 insert into performance_schema.setup_actors
118 set host= 'localhost', user= 'user4', `role`= '%';
119
120 update performance_schema.threads set instrumented='NO';
121
122 # Only instrument a few events of each kind
123 update performance_schema.setup_instruments set enabled='NO', timed='NO';
124
125 update performance_schema.setup_instruments set enabled='YES', timed='YES'
126 where name in ('wait/synch/mutex/sql/LOCK_connection_count',
127 'wait/synch/mutex/sql/LOCK_uuid_generator',
128 'wait/synch/rwlock/sql/LOCK_grant',
129 'wait/io/file/sql/query_log',
130 'idle');
131
132 update performance_schema.setup_instruments set enabled='YES', timed='YES'
133 where name in ('stage/sql/starting',
134 'stage/sql/init',
135 'stage/sql/checking permissions',
136 'stage/sql/Opening tables',
137 'stage/sql/closing tables');
138
139 update performance_schema.setup_instruments set enabled='YES', timed='YES'
140 where name in ('statement/sql/select',
141 'statement/sql/insert',
142 'statement/abstract/new_packet',
143 'statement/abstract/Query',
144 'statement/com/Quit',
145 'statement/com/error',
146 'statement/sp/freturn');
147
148 update performance_schema.setup_instruments set enabled='YES', timed='YES'
149 where name in ('transaction');
150
151 # Start from a known clean state, to avoid noise from previous tests
152 flush tables;
153 flush status;
154 truncate performance_schema.events_waits_summary_by_thread_by_event_name;
155 truncate performance_schema.events_waits_summary_by_account_by_event_name;
156 truncate performance_schema.events_waits_summary_by_user_by_event_name;
157 truncate performance_schema.events_waits_summary_by_host_by_event_name;
158 truncate performance_schema.events_waits_summary_global_by_event_name;
159 truncate performance_schema.events_waits_history_long;
160
161 truncate performance_schema.events_stages_summary_by_thread_by_event_name;
162 truncate performance_schema.events_stages_summary_by_account_by_event_name;
163 truncate performance_schema.events_stages_summary_by_user_by_event_name;
164 truncate performance_schema.events_stages_summary_by_host_by_event_name;
165 truncate performance_schema.events_stages_summary_global_by_event_name;
166 truncate performance_schema.events_stages_history_long;
167
168 truncate performance_schema.events_statements_summary_by_thread_by_event_name;
169 truncate performance_schema.events_statements_summary_by_account_by_event_name;
170 truncate performance_schema.events_statements_summary_by_user_by_event_name;
171 truncate performance_schema.events_statements_summary_by_host_by_event_name;
172 truncate performance_schema.events_statements_summary_global_by_event_name;
173 truncate performance_schema.events_statements_history_long;
174
175 truncate performance_schema.events_transactions_summary_by_thread_by_event_name;
176 truncate performance_schema.events_transactions_summary_by_account_by_event_name;
177 truncate performance_schema.events_transactions_summary_by_user_by_event_name;
178 truncate performance_schema.events_transactions_summary_by_host_by_event_name;
179 truncate performance_schema.events_transactions_summary_global_by_event_name;
180 truncate performance_schema.events_transactions_history_long;
181
182 --disable_warnings
183 drop procedure if exists dump_thread;
184 drop procedure if exists dump_one_thread;
185 --enable_warnings
186
187 delimiter $$;
188
189 create procedure dump_thread()
190 begin
191 call dump_one_thread('user1');
192 call dump_one_thread('user2');
193 call dump_one_thread('user3');
194 call dump_one_thread('user4');
195 end
196 $$
197
198 create procedure dump_one_thread(in username varchar(64))
199 begin
200 declare my_thread_id int;
201
202 set my_thread_id = (select thread_id from performance_schema.threads
203 where processlist_user=username);
204
205 if (my_thread_id is not null) then
206 select username, event_name, count_star
207 from performance_schema.events_waits_summary_by_thread_by_event_name
208 where event_name in ('wait/synch/mutex/sql/LOCK_connection_count',
209 'wait/synch/mutex/sql/LOCK_uuid_generator',
210 'wait/synch/rwlock/sql/LOCK_grant',
211 'wait/io/file/sql/query_log')
212 and thread_id = my_thread_id
213 order by event_name;
214 else
215 select username, "not found" as status;
216 end if;
217 end
218 $$
219
220 delimiter ;$$
221
222 prepare dump_waits_account from
223 "select user, host, event_name, count_star
224 from performance_schema.events_waits_summary_by_account_by_event_name
225 where user like \'user%\'
226 and event_name in ('wait/synch/mutex/sql/LOCK_connection_count',
227 'wait/synch/mutex/sql/LOCK_uuid_generator',
228 'wait/synch/rwlock/sql/LOCK_grant',
229 'wait/io/file/sql/query_log')
230 order by user, host, event_name;";
231
232 prepare dump_waits_user from
233 "select user, event_name, count_star
234 from performance_schema.events_waits_summary_by_user_by_event_name
235 where user like \'user%\'
236 and event_name in ('wait/synch/mutex/sql/LOCK_connection_count',
237 'wait/synch/mutex/sql/LOCK_uuid_generator',
238 'wait/synch/rwlock/sql/LOCK_grant',
239 'wait/io/file/sql/query_log')
240 order by user, event_name;";
241
242 prepare dump_waits_host from
243 "select host, event_name, count_star
244 from performance_schema.events_waits_summary_by_host_by_event_name
245 where host=\'localhost\'
246 and event_name in ('wait/synch/mutex/sql/LOCK_connection_count',
247 'wait/synch/mutex/sql/LOCK_uuid_generator',
248 'wait/synch/rwlock/sql/LOCK_grant',
249 'wait/io/file/sql/query_log')
250 order by host, event_name;";
251
252 prepare dump_waits_global from
253 "select event_name, count_star
254 from performance_schema.events_waits_summary_global_by_event_name
255 where event_name in ('wait/synch/mutex/sql/LOCK_connection_count',
256 'wait/synch/mutex/sql/LOCK_uuid_generator',
257 'wait/synch/rwlock/sql/LOCK_grant',
258 'wait/io/file/sql/query_log')
259 order by event_name;";
260
261 prepare dump_waits_history from
262 "select event_name, count(event_name)
263 from performance_schema.events_waits_history_long
264 where event_name in ('wait/synch/mutex/sql/LOCK_connection_count',
265 'wait/synch/mutex/sql/LOCK_uuid_generator',
266 'wait/synch/rwlock/sql/LOCK_grant',
267 'wait/io/file/sql/query_log')
268 group by event_name order by event_name;";
269
270 prepare dump_stages_account from
271 "select user, host, event_name, count_star
272 from performance_schema.events_stages_summary_by_account_by_event_name
273 where user like \'user%\'
274 and event_name in ('stage/sql/starting',
275 'stage/sql/init',
276 'stage/sql/checking permissions',
277 'stage/sql/Opening tables',
278 'stage/sql/closing tables')
279 order by user, host, event_name;";
280
281 prepare dump_stages_user from
282 "select user, event_name, count_star
283 from performance_schema.events_stages_summary_by_user_by_event_name
284 where user like \'user%\'
285 and event_name in ('stage/sql/starting',
286 'stage/sql/init',
287 'stage/sql/checking permissions',
288 'stage/sql/Opening tables',
289 'stage/sql/closing tables')
290 order by user, event_name;";
291
292 prepare dump_stages_host from
293 "select host, event_name, count_star
294 from performance_schema.events_stages_summary_by_host_by_event_name
295 where host=\'localhost\'
296 and event_name in ('stage/sql/starting',
297 'stage/sql/init',
298 'stage/sql/checking permissions',
299 'stage/sql/Opening tables',
300 'stage/sql/closing tables')
301 order by host, event_name;";
302
303 prepare dump_stages_global from
304 "select event_name, count_star
305 from performance_schema.events_stages_summary_global_by_event_name
306 where event_name in ('stage/sql/starting',
307 'stage/sql/init',
308 'stage/sql/checking permissions',
309 'stage/sql/Opening tables',
310 'stage/sql/closing tables')
311 order by event_name;";
312
313 prepare dump_stages_history from
314 "select event_name, count(event_name)
315 from performance_schema.events_stages_history_long
316 where event_name in ('stage/sql/starting',
317 'stage/sql/init',
318 'stage/sql/checking permissions',
319 'stage/sql/Opening tables',
320 'stage/sql/closing tables')
321 group by event_name order by event_name;";
322
323 prepare dump_statements_account from
324 "select user, host, event_name, count_star
325 from performance_schema.events_statements_summary_by_account_by_event_name
326 where user like \'user%\'
327 and event_name in ('statement/sql/select',
328 'statement/sql/insert',
329 'statement/com/Quit',
330 'statement/com/error',
331 'statement/sp/freturn')
332 order by user, host, event_name;";
333
334 prepare dump_statements_user from
335 "select user, event_name, count_star
336 from performance_schema.events_statements_summary_by_user_by_event_name
337 where user like \'user%\'
338 and event_name in ('statement/sql/select',
339 'statement/sql/insert',
340 'statement/com/Quit',
341 'statement/com/error',
342 'statement/sp/freturn')
343 order by user, event_name;";
344
345 prepare dump_statements_host from
346 "select host, event_name, count_star
347 from performance_schema.events_statements_summary_by_host_by_event_name
348 where host=\'localhost\'
349 and event_name in ('statement/sql/select',
350 'statement/sql/insert',
351 'statement/com/Quit',
352 'statement/com/error',
353 'statement/sp/freturn')
354 order by host, event_name;";
355
356 prepare dump_statements_global from
357 "select event_name, count_star
358 from performance_schema.events_statements_summary_global_by_event_name
359 where event_name in ('statement/sql/select',
360 'statement/sql/insert',
361 'statement/com/Quit',
362 'statement/com/error',
363 'statement/sp/freturn')
364 order by event_name;";
365
366 prepare dump_statements_history from
367 "select event_name, count(event_name)
368 from performance_schema.events_statements_history_long
369 where event_name in ('statement/sql/select',
370 'statement/sql/insert',
371 'statement/com/Quit',
372 'statement/com/error',
373 'statement/sp/freturn')
374 group by event_name order by event_name;";
375
376 prepare dump_transactions_account from
377 "select user, host, event_name, count_star
378 from performance_schema.events_transactions_summary_by_account_by_event_name
379 where user like \'user%\'
380 and event_name in ('transaction')
381 order by user, host, event_name;";
382
383 prepare dump_transactions_user from
384 "select user, event_name, count_star
385 from performance_schema.events_transactions_summary_by_user_by_event_name
386 where user like \'user%\'
387 and event_name in ('transaction')
388 order by user, event_name;";
389
390 prepare dump_transactions_host from
391 "select host, event_name, count_star
392 from performance_schema.events_transactions_summary_by_host_by_event_name
393 where host=\'localhost\'
394 and event_name in ('transaction')
395 order by host, event_name;";
396
397 prepare dump_transactions_global from
398 "select event_name, count_star
399 from performance_schema.events_transactions_summary_global_by_event_name
400 where event_name in ('transaction')
401 order by event_name;";
402
403 prepare dump_transactions_history from
404 "select event_name, count(event_name)
405 from performance_schema.events_transactions_history_long
406 where event_name in ('transaction')
407 group by event_name order by event_name;";
408
409 prepare dump_users from
410 "select USER, CURRENT_CONNECTIONS, TOTAL_CONNECTIONS from performance_schema.users where user is not null order by user;";
411
412 prepare dump_hosts from
413 "select HOST, CURRENT_CONNECTIONS, TOTAL_CONNECTIONS from performance_schema.hosts where host is not null order by host;";
414
415 prepare dump_accounts from
416 "select USER, HOST, CURRENT_CONNECTIONS, TOTAL_CONNECTIONS from performance_schema.accounts where (user is not null) and (host is not null) order by user, host;";
417
418 --enable_query_log