Debian Codegraph @

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