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 --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.setup_objects;
95 --enable_warnings
96
97 create table test.setup_actors as
98 select * from performance_schema.setup_actors;
99
100 create table test.setup_objects as
101 select * from performance_schema.setup_objects;
102
103 # Only instrument the user connections (by default)
104 truncate table performance_schema.setup_actors;
105 insert into performance_schema.setup_actors
106 set host= 'localhost', user= 'user1', role= '%';
107 insert into performance_schema.setup_actors
108 set host= 'localhost', user= 'user2', role= '%';
109 insert into performance_schema.setup_actors
110 set host= 'localhost', user= 'user3', role= '%';
111 insert into performance_schema.setup_actors
112 set host= 'localhost', user= 'user4', role= '%';
113
114 # Only instrument test.t% tables (by default)
115 truncate table performance_schema.setup_objects;
116 insert into performance_schema.setup_objects
117 set object_type='TABLE', object_schema='test', object_name='t1', timed='YES';
118 insert into performance_schema.setup_objects
119 set object_type='TABLE', object_schema='test', object_name='t2', timed='NO';
120 insert into performance_schema.setup_objects
121 set object_type='TABLE', object_schema='test', object_name='t3', timed='NO';
122
123 update performance_schema.threads set instrumented='NO';
124
125 # Only instrument table io and lock (by default)
126 update performance_schema.setup_instruments set enabled='NO', timed='NO';
127 update performance_schema.setup_instruments set enabled='YES', timed='YES'
128 where name in ('wait/io/table/sql/handler',
129 'wait/lock/table/sql/handler');
130
131 # Enable all consumers (by default)
132 update performance_schema.setup_consumers set enabled='YES';
133
134 # Start from a known clean state, to avoid noise from previous tests
135 flush tables;
136 flush global status;
137
138 create table test.t1(a int, b int, c int, d int default 0,
139 primary key(a),
140 index index_b(b),
141 index index_cb(c, b));
142 create table test.t2 like test.t1;
143 create table test.t3 like test.t1;
144
145 #
146 # Note:
147 # For test robustness and to avoid picking up noise from other tests scripts,
148 # it is better to use:
149 # in ('t1', 't2', 't3)
150 # explicitly instead of:
151 # like 't%'
152
153 --disable_warnings
154 drop procedure if exists dump_thread;
155 drop procedure if exists dump_one_thread;
156 --enable_warnings
157
158 delimiter $$;
159
160 create procedure dump_thread()
161 begin
162 call dump_one_thread('user1');
163 call dump_one_thread('user2');
164 call dump_one_thread('user3');
165 call dump_one_thread('user4');
166 end
167 $$
168
169 create procedure dump_one_thread(in username varchar(64))
170 begin
171 declare my_thread_id int;
172
173 set my_thread_id = (select thread_id from performance_schema.threads
174 where processlist_user=username);
175
176 if (my_thread_id is not null) then
177 select username, event_name, count_star
178 from performance_schema.events_waits_summary_by_thread_by_event_name
179 where event_name in
180 ('wait/io/table/sql/handler',
181 'wait/lock/table/sql/handler')
182 and thread_id = my_thread_id
183 order by event_name;
184 else
185 select username, "not found" as status;
186 end if;
187 end
188 $$
189
190 delimiter ;$$
191
192 prepare dump_waits_user from
193 "select user, event_name, count_star
194 from performance_schema.events_waits_summary_by_user_by_event_name
195 where user like \'user%\' and event_name in
196 (\'wait/io/table/sql/handler\',
197 \'wait/lock/table/sql/handler\')
198 order by user, event_name;";
199
200 prepare dump_waits_account from
201 "select user, host, event_name, count_star
202 from performance_schema.events_waits_summary_by_account_by_event_name
203 where user like \'user%\' and event_name in
204 (\'wait/io/table/sql/handler\',
205 \'wait/lock/table/sql/handler\')
206 order by user, host, event_name;";
207
208 prepare dump_waits_host from
209 "select host, event_name, count_star
210 from performance_schema.events_waits_summary_by_host_by_event_name
211 where host=\'localhost\' and event_name in
212 (\'wait/io/table/sql/handler\',
213 \'wait/lock/table/sql/handler\')
214 order by host, event_name;";
215
216 prepare dump_waits_global from
217 "select event_name, count_star
218 from performance_schema.events_waits_summary_global_by_event_name
219 where event_name in
220 (\'wait/io/table/sql/handler\',
221 \'wait/lock/table/sql/handler\')
222 order by event_name;";
223
224 prepare dump_waits_history from
225 "select event_name,
226 sum(if(number_of_bytes is null, 1, number_of_bytes)) as 'count(event_name)',
227 object_type, object_schema, object_name
228 from performance_schema.events_waits_history_long
229 where event_name in
230 (\'wait/io/table/sql/handler\',
231 \'wait/lock/table/sql/handler\')
232 group by object_type, object_schema, object_name, event_name
233 order by object_type, object_schema, object_name, event_name;";
234
235 prepare dump_waits_index_io from
236 "select object_type, object_schema, object_name, index_name,
237 count_star, count_read, count_write,
238 count_fetch, count_insert, count_update, count_delete
239 from performance_schema.table_io_waits_summary_by_index_usage
240 where object_type='TABLE' and object_schema='test'
241 and object_name in ('t1', 't2', 't3')
242 order by object_type, object_schema, object_name, index_name;";
243
244 prepare dump_waits_table_io from
245 "select object_type, object_schema, object_name,
246 count_star, count_read, count_write,
247 count_fetch, count_insert, count_update, count_delete
248 from performance_schema.table_io_waits_summary_by_table
249 where object_type='TABLE' and object_schema='test'
250 and object_name in ('t1', 't2', 't3')
251 order by object_type, object_schema, object_name";
252
253 prepare dump_waits_table_lock from
254 "select object_type, object_schema, object_name,
255 count_star, count_read, count_write,
256 count_read_normal, count_read_with_shared_locks,
257 count_read_high_priority, count_read_no_insert,
258 count_read_external,
259 count_write_low_priority,
260 count_write_external
261 from performance_schema.table_lock_waits_summary_by_table
262 where object_type='TABLE' and object_schema='test'
263 and object_name in ('t1', 't2', 't3')
264 order by object_type, object_schema, object_name";
265
266 prepare dump_objects_summary from
267 "select object_type, object_schema, object_name, count_star
268 from performance_schema.objects_summary_global_by_type
269 where object_type='TABLE' and object_schema='test'
270 and object_name in ('t1', 't2', 't3')
271 order by object_type, object_schema, object_name";
272
273 --enable_query_log