Debian Codegraph @

mysql-test/suite/sys_vars/t/innodb_monitor_enable_basic.test

raw
1 --source include/not_hypergraph.inc # Depends on choosing very specific query plans.
2
3 # This is the test for Metrics Monitor Table feature.
4 # Test the metrics monitor system's control system
5 # and counter accuracy.
6 set global innodb_monitor_disable = All;
7 # Test turn on/off the monitor counter with "all" option
8 # By default, they will be off
9 select name, status from information_schema.innodb_metrics;
10
11 # Turn on all monitor counters
12 set global innodb_monitor_enable = all;
13
14 # Create and drop a table, to force the Global DD tables into the cache.
15 # TODO: In the unlikely event that Global DD tables are evicted from
16 # the InnoDB dict_sys cache during the rest of this test, we may get
17 # mismatch on the metadata_table_handles_opened counter.
18 create table monitor_test(col int) engine = innodb;
19 drop table monitor_test;
20
21 # status should all change to "enabled"
22 select name from information_schema.innodb_metrics where status!='enabled';
23
24 # Test wrong argument to the global configure option
25 --error ER_WRONG_VALUE_FOR_VAR
26 set global innodb_monitor_enable = aaa;
27
28 # We require a valid monitor counter/module name. There is no default
29 # counter name or module. A warning will be printed asking user to
30 # specify a valid counter name.
31 #--disable_warnings
32 #set global innodb_monitor_enable = default;
33 #--enable_warnings
34
35 # Turn off all monitor counters, option name should be case
36 # insensitive
37 set global innodb_monitor_disable = All;
38
39 # status should all change to "disabled"
40 select name from information_schema.innodb_metrics where status!='disabled';
41
42 # Reset all counter values
43 set global innodb_monitor_reset_all = all;
44
45 # count should all change to 0
46 select name from information_schema.innodb_metrics where count!=0;
47
48 # Test wildcard match, turn on all counters contain string "lock"
49 set global innodb_monitor_enable = "%lock%";
50
51 # All lock related counter should be enabled
52 select name from information_schema.innodb_metrics
53 where status != IF(name like "%lock%", 'enabled', 'disabled');
54
55 # Disable them
56 set global innodb_monitor_disable = "%lock%";
57
58 # All lock related counter should be disabled
59 select name, status from information_schema.innodb_metrics
60 where name like "%lock%";
61
62 # No match for "%lock*"
63 --error ER_WRONG_VALUE_FOR_VAR
64 set global innodb_monitor_enable = "%lock*";
65
66 # All counters will be turned on with wildcard match string with all "%"
67 set global innodb_monitor_enable="%%%%%%%%%%%%%%%%%%%%%%%%%%%";
68
69 select name from information_schema.innodb_metrics where status!='enabled';
70
71 # Turn off all counters
72 set global innodb_monitor_disable="%%%%%";
73
74 select name from information_schema.innodb_metrics where status!='disabled';
75
76 # One more round testing. All counters will be turned on with
77 # single wildcard character "%"
78 set global innodb_monitor_enable="%";
79
80 select name from information_schema.innodb_metrics where status!='enabled';
81
82 # Turn off all the counters with "%_%"
83 set global innodb_monitor_disable="%_%";
84
85 select name from information_schema.innodb_metrics where status!='disabled';
86
87 # Turn on all counters start with "log"
88 set global innodb_monitor_enable="log%%%%";
89
90 select name from information_schema.innodb_metrics
91 where status != IF(name like "log%", 'enabled', 'disabled');
92
93 # Turn on counters "os_data_fsync" with wildcard match "os_%a_fs_ncs", "_"
94 # is single character wildcard match word
95 set global innodb_monitor_enable="os_%a_fs_ncs";
96
97 # Turn on counters whose name contains "os" and "pending" with
98 # wildcard match "os%pending%"
99 set global innodb_monitor_enable="os%pending%";
100
101 select name, status from information_schema.innodb_metrics
102 where name like "os%";
103
104 # Empty string is an invalid option
105 --error ER_WRONG_VALUE_FOR_VAR
106 set global innodb_monitor_enable="";
107
108 --error ER_WRONG_VALUE_FOR_VAR
109 set global innodb_monitor_enable="_";
110
111 SET global innodb_monitor_disable = module_metadata;
112 SET global innodb_monitor_reset_all = module_metadata;
113
114 # Only turn on "table_open" counter
115 set global innodb_monitor_enable = metadata_table_handles_opened;
116
117 # Create a new table to test "metadata_table_handles_opened" counter
118 create table monitor_test(col int) engine = innodb;
119
120 # This will open the monitor_test table
121 select * from monitor_test;
122
123 # "metadata_table_handles_opened" should increment by 1
124 select name, max_count, min_count, count,
125 max_count_reset, min_count_reset, count_reset, status
126 from information_schema.innodb_metrics
127 where name = "metadata_table_handles_opened";
128
129 # Reset the counter value while counter is still on (started)
130 # This will reset value "count_reset" but not
131 # "count"
132 set global innodb_monitor_reset = metadata_table_handles_opened;
133
134 select name, max_count, min_count, count,
135 max_count_reset, min_count_reset, count_reset, status
136 from information_schema.innodb_metrics
137 where name = "metadata_table_handles_opened";
138
139 # re-create table again to increment "metadata_table_handles_opened" again
140 drop table monitor_test;
141
142 # Create a new table to test "metadata_table_handles_opened" counter
143 create table monitor_test(col int) engine = innodb;
144
145 select * from monitor_test;
146
147 # "metadata_table_handles_opened" should increment
148 select name, max_count, min_count, count,
149 max_count_reset, min_count_reset, count_reset, status
150 from information_schema.innodb_metrics
151 where name = "metadata_table_handles_opened";
152
153 # Cannot reset all monitor value while the counter is on
154 set global innodb_monitor_reset_all = metadata_table_handles_opened;
155
156 select name, max_count, min_count, count,
157 max_count_reset, min_count_reset, count_reset, status
158 from information_schema.innodb_metrics
159 where name = "metadata_table_handles_opened";
160
161 # Turn off the counter "metadata_table_handles_opened"
162 set global innodb_monitor_disable = metadata_table_handles_opened;
163
164 # Reset the counter value while counter is off (disabled)
165 set global innodb_monitor_reset = metadata_table_handles_opened;
166
167 select name, max_count, min_count, count,
168 max_count_reset, min_count_reset, count_reset, status
169 from information_schema.innodb_metrics
170 where name = "metadata_table_handles_opened";
171
172 # re-create table again. Since monitor is off, "metadata_table_handles_opened"
173 # should not be incremented
174 drop table monitor_test;
175
176 # Create a new table to test "metadata_table_handles_opened" counter
177 create table monitor_test(col int) engine = innodb;
178
179 # "metadata_table_handles_opened" should increment
180 select * from monitor_test;
181
182 select name, max_count, min_count, count,
183 max_count_reset, min_count_reset, count_reset, status
184 from information_schema.innodb_metrics
185 where name = "metadata_table_handles_opened";
186
187 # Reset all the counters, include those counter *_since_start
188 set global innodb_monitor_reset_all = metadata_table_handles_opened;
189
190 select name, max_count, min_count, count,
191 max_count_reset, min_count_reset, count_reset, status
192 from information_schema.innodb_metrics
193 where name = "metadata_table_handles_opened";
194
195 # Turn on "table_open" counter again
196 set global innodb_monitor_enable = metadata_table_handles_opened;
197
198 # Test metadata_table_handles_opened again to see if it is working correctly
199 # after above round of turning on/off/reset
200 drop table monitor_test;
201
202 # Create a new table to test "metadata_table_handles_opened" counter
203 create table monitor_test(col int) engine = innodb stats_persistent=0;
204
205 select * from monitor_test;
206
207 # "metadata_table_handles_opened" should increment
208 select name, max_count, min_count, count,
209 max_count_reset, min_count_reset, count_reset, status
210 from information_schema.innodb_metrics
211 where name = "metadata_table_handles_opened";
212
213 # Test counter "metadata_table_handles_closed",
214 # create index will close the old handle
215 set global innodb_monitor_enable = metadata_table_handles_closed;
216
217 create index idx on monitor_test(col);
218
219 select name, max_count, min_count, count,
220 max_count_reset, min_count_reset, count_reset, status
221 from information_schema.innodb_metrics
222 where name = "metadata_table_handles_closed";
223
224 select name, max_count, min_count, count,
225 max_count_reset, min_count_reset, count_reset, status
226 from information_schema.innodb_metrics
227 where name like "metadata%";
228
229 # Reset counters only in "module_metadata" module
230 set global innodb_monitor_disable = module_metadata;
231
232 set global innodb_monitor_reset = module_metadata;
233
234 select name, max_count, min_count, count,
235 max_count_reset, min_count_reset, count_reset, status
236 from information_schema.innodb_metrics
237 where name like "metadata%";
238
239 set global innodb_monitor_reset_all = module_metadata;
240
241 select name, max_count, min_count, count,
242 max_count_reset, min_count_reset, count_reset, status
243 from information_schema.innodb_metrics
244 where name like "metadata%";
245
246 # Test Transaction Module
247 set global innodb_monitor_enable = module_trx;
248
249 begin;
250 insert into monitor_test values(9);
251 commit;
252
253 begin;
254 insert into monitor_test values(9);
255 rollback;
256
257 select name, max_count, min_count, count,
258 max_count_reset, min_count_reset, count_reset, status
259 from information_schema.innodb_metrics
260 where name like "trx_rollbacks" or name like "trx_active_transactions";
261
262 set global innodb_monitor_disable = module_trx;
263
264 # Test DML Module
265 set global innodb_monitor_enable = module_dml;
266
267 insert into monitor_test values(9);
268
269 update monitor_test set col = 10 where col = 9;
270
271 select name, max_count, min_count, count,
272 max_count_reset, min_count_reset, count_reset, status
273 from information_schema.innodb_metrics
274 where name like "dml%";
275
276 delete from monitor_test;
277
278 select name, max_count, min_count, count,
279 max_count_reset, min_count_reset, count_reset, status
280 from information_schema.innodb_metrics
281 where name like "dml%";
282
283 # test reset counter while the counter is on
284 set global innodb_monitor_reset = module_dml;
285
286 select name, max_count, min_count, count,
287 max_count_reset, min_count_reset, count_reset, status
288 from information_schema.innodb_metrics
289 where name like "dml%";
290
291 # insert/delete some rows after the reset
292 insert into monitor_test values(9);
293 insert into monitor_test values(1);
294
295 delete from monitor_test;
296
297 select name, max_count, min_count, count,
298 max_count_reset, min_count_reset, count_reset, status
299 from information_schema.innodb_metrics
300 where name like "dml%";
301
302 # We do not allow reset_all while the counter is on, nothing
303 # should be reset here
304 set global innodb_monitor_reset_all = module_dml;
305
306 select name, max_count, min_count, count,
307 max_count_reset, min_count_reset, count_reset, status
308 from information_schema.innodb_metrics
309 where name like "dml%";
310
311 # Turn off the counter
312 set global innodb_monitor_disable = module_dml;
313
314 select name, max_count, min_count, count,
315 max_count_reset, min_count_reset, count_reset, status
316 from information_schema.innodb_metrics
317 where name like "dml%";
318
319 # Reset all counter values
320 set global innodb_monitor_reset_all = module_dml;
321
322 select name, max_count, min_count, count,
323 max_count_reset, min_count_reset, count_reset, status
324 from information_schema.innodb_metrics
325 where name like "dml%";
326
327 # Open individual counter "dml_inserts"
328 set global innodb_monitor_enable = dml_inserts;
329
330 insert into monitor_test values(9);
331 insert into monitor_test values(1);
332
333 delete from monitor_test;
334
335 # Only counter "dml_inserts" should be updated
336 select name, max_count, min_count, count,
337 max_count_reset, min_count_reset, count_reset, status
338 from information_schema.innodb_metrics
339 where name like "dml%";
340
341 set global innodb_monitor_disable = module_dml;
342
343 drop table monitor_test;
344
345 set global innodb_monitor_enable = file_num_open_files;
346
347 # Counters are unpredictable when innodb-file-per-table is on
348 --replace_column 2 # 3 # 4 # 5 # 6 # 7 #
349 select name, max_count, min_count, count,
350 max_count_reset, min_count_reset, count_reset, status
351 from information_schema.innodb_metrics
352 where name like "file_num_open_files";
353
354 set global innodb_monitor_disable = file_num_open_files;
355
356 # Test ICP module counters
357 set global innodb_monitor_enable = "icp%";
358
359 create table monitor_test(a char(3), b int, c char(2),
360 primary key (a(1), c(1)), key(b)) engine = innodb;
361
362 insert into monitor_test values("13", 2, "aa");
363
364 select a from monitor_test where b < 1 for update;
365
366 # should have icp_attempts = 1 and icp_out_of_range = 1
367 select name, count from information_schema.innodb_metrics
368 where name like "icp%";
369
370 # should have icp_attempts = 2 and icp_match = 1
371 select a from monitor_test where b < 3 for update;
372
373 select name, count from information_schema.innodb_metrics
374 where name like "icp%";
375
376 drop table monitor_test;
377 set global innodb_monitor_disable = All;
378 set global innodb_monitor_reset_all = all;
379
380 --source include/innodb_monitor_restore.inc