Debian Codegraph @

mysql-test/suite/innodb/t/monitor.test

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