Debian Codegraph @

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

raw
1 # This test used to be duplicated in the following:
2 # sys_vars.innodb_monitor_reset_basic
3 # sys_vars.innodb_monitor_reset_all_basic
4 # sys_vars.innodb_monitor_disable_basic
5 # sys_vars.innodb_monitor_enable_basic
6
7 --source include/have_innodb.inc
8
9 --disable_query_log
10 call mtr.add_suppression("InnoDB: Cannot reset all values for monitor counter");
11 --enable_query_log
12
13 select name, if(enabled,'enabled','disabled') status
14 from information_schema.innodb_metrics;
15
16 create temporary table orig_innodb_metrics as select name, enabled from information_schema.innodb_metrics;
17
18 set global innodb_monitor_disable = All;
19 select name from information_schema.innodb_metrics where enabled;
20
21 # Turn on all monitor counters
22 set global innodb_monitor_enable = all;
23
24 # status should all change to "enabled"
25 select name from information_schema.innodb_metrics where not enabled;
26
27 # Test wrong argument to the global configure option
28 --error ER_WRONG_VALUE_FOR_VAR
29 set global innodb_monitor_enable = aaa;
30
31 # We require a valid monitor counter/module name. There is no default
32 # counter name or module. A warning will be printed asking user to
33 # specify a valid counter name.
34 #--disable_warnings
35 #set global innodb_monitor_enable = default;
36 #--enable_warnings
37
38 # Turn off all monitor counters, option name should be case
39 # insensitive
40 set global innodb_monitor_disable = All;
41
42 # status should all change to "disabled"
43 select name from information_schema.innodb_metrics where enabled;
44
45 # Reset all counter values
46 set global innodb_monitor_reset_all = all;
47
48 # count should all change to 0
49 select name from information_schema.innodb_metrics where count!=0;
50
51 # Test wildcard match, turn on all counters contain string "lock"
52 set global innodb_monitor_enable = "%lock%";
53
54 # All lock related counter should be enabled
55 select name from information_schema.innodb_metrics
56 where enabled != (name like "%lock%");
57
58 # Disable them
59 set global innodb_monitor_disable = "%lock%";
60
61 # All lock related counter should be disabled
62 select name, if(enabled,'enabled','disabled') status
63 from information_schema.innodb_metrics
64 where name like "%lock%";
65
66 # No match for "%lock*"
67 --error ER_WRONG_VALUE_FOR_VAR
68 set global innodb_monitor_enable = "%lock*";
69
70 # All counters will be turned on with wildcard match string with all "%"
71 set global innodb_monitor_enable="%%%%%%%%%%%%%%%%%%%%%%%%%%%";
72
73 select name from information_schema.innodb_metrics where not enabled;
74
75 # Turn off all counters
76 set global innodb_monitor_disable="%%%%%";
77
78 select name from information_schema.innodb_metrics where enabled;
79
80 # One more round testing. All counters will be turned on with
81 # single wildcard character "%"
82 set global innodb_monitor_enable="%";
83
84 select name from information_schema.innodb_metrics where not enabled;
85
86 # Turn off all the counters with "%_%"
87 set global innodb_monitor_disable="%_%";
88
89 select name from information_schema.innodb_metrics where enabled;
90
91 # Turn on all counters start with "log"
92 set global innodb_monitor_enable="log%%%%";
93
94 select name from information_schema.innodb_metrics
95 where enabled != (name like "log%");
96
97 # Turn on counters "os_data_fsync" with wildcard match "os_%a_fs_ncs", "_"
98 # is single character wildcard match word
99 set global innodb_monitor_enable="os_%a_fs_ncs";
100
101 # Turn on counters whose name contains "os" and "pending" with
102 # wildcard match "os%pending%"
103 set global innodb_monitor_enable="os%pending%";
104
105 select name, if(enabled,'enabled','disabled') status
106 from information_schema.innodb_metrics
107 where name like "os%";
108
109 # Empty string is an invalid option
110 --error ER_WRONG_VALUE_FOR_VAR
111 set global innodb_monitor_enable="";
112
113 --error ER_WRONG_VALUE_FOR_VAR
114 set global innodb_monitor_enable="_";
115
116 SET global innodb_monitor_disable = module_metadata;
117 SET global innodb_monitor_reset_all = module_metadata;
118
119 # Only turn on "table_open" counter
120 set global innodb_monitor_enable = metadata_table_handles_opened;
121
122 # Create a new table to test "metadata_table_handles_opened" counter
123 create table monitor_test(col int) engine = innodb;
124
125 # This will open the monitor_test table
126 select * from monitor_test;
127
128 # "metadata_table_handles_opened" should increment by 1
129 select name, max_count, min_count, count,
130 max_count_reset, min_count_reset, count_reset,
131 if(enabled,'enabled','disabled') status
132 from information_schema.innodb_metrics
133 where name = "metadata_table_handles_opened";
134
135 # Reset the counter value while counter is still on (started)
136 # This will reset value "count_reset" but not
137 # "count"
138 set global innodb_monitor_reset = metadata_table_handles_opened;
139
140 select name, max_count, min_count, count,
141 max_count_reset, min_count_reset, count_reset,
142 if(enabled,'enabled','disabled') status
143 from information_schema.innodb_metrics
144 where name = "metadata_table_handles_opened";
145
146 # re-create table again to increment "metadata_table_handles_opened" again
147 drop table monitor_test;
148
149 # Create a new table to test "metadata_table_handles_opened" counter
150 create table monitor_test(col int) engine = innodb;
151
152 select * from monitor_test;
153
154 # "metadata_table_handles_opened" should increment
155 select name, max_count, min_count, count,
156 max_count_reset, min_count_reset, count_reset,
157 if(enabled,'enabled','disabled') status
158 from information_schema.innodb_metrics
159 where name = "metadata_table_handles_opened";
160
161 # Cannot reset all monitor value while the counter is on
162 set global innodb_monitor_reset_all = metadata_table_handles_opened;
163
164 select name, max_count, min_count, count,
165 max_count_reset, min_count_reset, count_reset,
166 if(enabled,'enabled','disabled') status
167 from information_schema.innodb_metrics
168 where name = "metadata_table_handles_opened";
169
170 # Turn off the counter "metadata_table_handles_opened"
171 set global innodb_monitor_disable = metadata_table_handles_opened;
172
173 # Reset the counter value while counter is off (disabled)
174 set global innodb_monitor_reset = metadata_table_handles_opened;
175
176 select name, max_count, min_count, count,
177 max_count_reset, min_count_reset, count_reset,
178 if(enabled,'enabled','disabled') status
179 from information_schema.innodb_metrics
180 where name = "metadata_table_handles_opened";
181
182 # re-create table again. Since monitor is off, "metadata_table_handles_opened"
183 # should not be incremented
184 drop table monitor_test;
185
186 # Create a new table to test "metadata_table_handles_opened" counter
187 create table monitor_test(col int) engine = innodb;
188
189 # "metadata_table_handles_opened" should increment
190 select * from monitor_test;
191
192 select name, max_count, min_count, count,
193 max_count_reset, min_count_reset, count_reset,
194 if(enabled,'enabled','disabled') status
195 from information_schema.innodb_metrics
196 where name = "metadata_table_handles_opened";
197
198 # Reset all the counters, include those counter *_since_start
199 set global innodb_monitor_reset_all = metadata_table_handles_opened;
200
201 select name, max_count, min_count, count,
202 max_count_reset, min_count_reset, count_reset,
203 if(enabled,'enabled','disabled') status
204 from information_schema.innodb_metrics
205 where name = "metadata_table_handles_opened";
206
207 # Turn on "table_open" counter again
208 set global innodb_monitor_enable = metadata_table_handles_opened;
209
210 # Test metadata_table_handles_opened again to see if it is working correctly
211 # after above round of turning on/off/reset
212 drop table monitor_test;
213
214 # Create a new table to test "metadata_table_handles_opened" counter
215 create table monitor_test(col int) engine = innodb stats_persistent=0;
216
217 select * from monitor_test;
218
219 # "metadata_table_handles_opened" should increment
220 select name, max_count, min_count, count,
221 max_count_reset, min_count_reset, count_reset,
222 if(enabled,'enabled','disabled') status
223 from information_schema.innodb_metrics
224 where name like "metadata%";
225
226 # Reset counters only in "module_metadata" module
227 set global innodb_monitor_disable = module_metadata;
228
229 set global innodb_monitor_reset = module_metadata;
230
231 select name, max_count, min_count, count,
232 max_count_reset, min_count_reset, count_reset,
233 if(enabled,'enabled','disabled') status
234 from information_schema.innodb_metrics
235 where name like "metadata%";
236
237 set global innodb_monitor_reset_all = module_metadata;
238
239 select name, max_count, min_count, count,
240 max_count_reset, min_count_reset, count_reset,
241 if(enabled,'enabled','disabled') status
242 from information_schema.innodb_metrics
243 where name like "metadata%";
244
245 # Test Transaction Module
246 set global innodb_monitor_enable = module_trx;
247
248 begin;
249 insert into monitor_test values(9);
250 commit;
251
252 begin;
253 insert into monitor_test values(9);
254 rollback;
255
256 select name, max_count, min_count, count,
257 max_count_reset, min_count_reset, count_reset,
258 if(enabled,'enabled','disabled') status
259 from information_schema.innodb_metrics
260 where name='trx_rollbacks';
261
262 set global innodb_monitor_disable = module_trx;
263
264 drop table monitor_test;
265
266 set global innodb_monitor_enable = file_num_open_files;
267
268 # Counters are unpredictable
269 --replace_column 2 # 3 # 4 # 5 # 6 # 7 #
270 select name, max_count, min_count, count,
271 max_count_reset, min_count_reset, count_reset,
272 if(enabled,'enabled','disabled') status
273 from information_schema.innodb_metrics
274 where name like "file_num_open_files";
275
276 set global innodb_monitor_disable = file_num_open_files;
277
278 # Test ICP module counters
279 set global innodb_monitor_enable = "icp%";
280
281 create table monitor_test(a char(3), b int, c char(2),
282 primary key (a(1), c(1)), key(b)) engine = innodb;
283
284 insert into monitor_test values("13", 2, "aa");
285
286 --disable_ps2_protocol
287 select a from monitor_test where b < 1 for update;
288
289 # should have icp_attempts = 1 and icp_out_of_range = 1
290 select name, count from information_schema.innodb_metrics
291 where name like "icp%";
292
293 # should have icp_attempts = 2 and icp_match = 1
294 select a from monitor_test where b < 3 for update;
295
296 select name, count from information_schema.innodb_metrics
297 where name like "icp%";
298 --enable_ps2_protocol
299
300 drop table monitor_test;
301 set global innodb_monitor_disable = All;
302 set global innodb_monitor_reset_all = all;
303
304 # Test for bug #13966091
305 select 1 from `information_schema`.`INNODB_METRICS`
306 where case (1) when (1) then (AVG_COUNT_RESET) else (1) end;
307
308 -- disable_warnings
309 set global innodb_monitor_enable = default;
310 set global innodb_monitor_disable = default;
311 set global innodb_monitor_reset = default;
312 set global innodb_monitor_reset_all = default;
313 -- enable_warnings
314
315 --echo #
316 --echo # Bug#22576241 SETTING INNODB_MONITOR_ENABLE TO ALL DOES NOT ENABLE ALL
317 --echo # MONITORS
318 --echo #
319 CREATE TABLE t1 (a INT PRIMARY KEY) ENGINE=InnoDB STATS_PERSISTENT=0;
320
321 let $innodb_monitor_enable = `SELECT @@innodb_monitor_enable`;
322
323 SELECT /*1*/ NAME, COUNT > 0 FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE NAME
324 LIKE 'buffer_page_written_index_leaf';
325
326 SET GLOBAL innodb_monitor_enable='module_buffer_page';
327 INSERT INTO t1 VALUES (1), (2), (3), (4); FLUSH TABLES t1 FOR EXPORT;
328 UNLOCK TABLES;
329
330 let $wait_condition= select count > 0 from information_schema.innodb_metrics where name like 'buffer_page_written_index_leaf';
331 source include/wait_condition.inc;
332
333 SELECT /*2*/ NAME, COUNT > 0 FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE NAME
334 LIKE 'buffer_page_written_index_leaf';
335
336 SET GLOBAL innodb_monitor_disable='module_buffer_page';
337 SET GLOBAL innodb_monitor_reset_all='module_buffer_page';
338 SELECT /*3*/ NAME, COUNT > 0 FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE NAME
339 LIKE 'buffer_page_written_index_leaf';
340
341 SET GLOBAL innodb_monitor_enable='%';
342 --error ER_WRONG_VALUE_FOR_VAR
343 SET GLOBAL innodb_monitor_reset_all= '%', innodb_compression_algorithm= foo;
344 INSERT INTO t1 VALUES (5), (6), (7), (8); FLUSH TABLES t1 FOR EXPORT;
345 UNLOCK TABLES;
346
347 let $wait_condition= select count > 0 from information_schema.innodb_metrics where name like 'buffer_page_written_index_leaf';
348 source include/wait_condition.inc;
349
350 SELECT /*4*/ NAME, COUNT > 0 FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE NAME
351 LIKE 'buffer_page_written_index_leaf';
352
353 SET GLOBAL innodb_monitor_disable='%';
354 SET GLOBAL innodb_monitor_reset_all='%';
355 SELECT /*5*/ NAME, COUNT > 0 FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE NAME
356 LIKE 'buffer_page_written_index_leaf';
357
358 SET GLOBAL innodb_monitor_enable='ALL';
359 INSERT INTO t1 VALUES (9), (10), (11), (12); FLUSH TABLES t1 FOR EXPORT;
360 UNLOCK TABLES;
361 SELECT /*6*/ NAME, COUNT > 0 FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE NAME
362 LIKE 'buffer_page_written_index_leaf';
363
364 DROP TABLE t1;
365
366 CREATE TABLE fl0 (
367 id INT NOT NULL PRIMARY KEY
368 ) ENGINE = InnoDB;
369
370 CREATE TABLE fl1 (
371 id INT NOT NULL PRIMARY KEY,
372 fl0_id INT,
373 CONSTRAINT `fkl0`
374 FOREIGN KEY (fl0_id) REFERENCES fl0 (id)
375 ON DELETE CASCADE
376 ON UPDATE RESTRICT
377 ) ENGINE = InnoDB;
378
379 CREATE TABLE fl2 (
380 id INT NOT NULL PRIMARY KEY,
381 fl1_id INT,
382 CONSTRAINT `fkl1`
383 FOREIGN KEY (fl1_id) REFERENCES fl1 (id)
384 ON DELETE CASCADE
385 ON UPDATE SET NULL
386 ) ENGINE = InnoDB;
387
388 INSERT INTO fl0 VALUES (1000);
389 INSERT INTO fl1 VALUES (500, 1000), (1500, 1000);
390 INSERT INTO fl2 VALUES (200, 500), (800, 500), (1200, 1500), (1800, 1500);
391
392 CREATE TABLE t1(id INT PRIMARY KEY, a INT, b CHAR(1), UNIQUE KEY u(a,b))
393 ENGINE=InnoDB;
394
395 SET @start = (SELECT COUNT FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE NAME
396 = 'lock_rec_lock_created');
397
398 BEGIN;
399 INSERT INTO t1 VALUES(1,1,'a'),(2,9999,'b'),(3,10000,'c'),(4,4,'d');
400 DELETE FROM t1 WHERE a = 9999 AND b='b';
401 COMMIT;
402
403 # After MDEV-30225 is fixed, the above DELETE creates next-key lock during
404 # secondary index unique search. That's why the result of the following must
405 # be 1.
406 SET @end = (SELECT COUNT FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE NAME
407 = 'lock_rec_lock_created');
408 SELECT @end - @start;
409
410 --echo # Records must not be S/X-locked if a table is X-locked.
411 SET @start = @end;
412 # Tables will not be locked if autocommit is not 0.
413 # See OPTION_NOT_AUTOCOMMIT in ha_innobase::external_lock().
414 SET autocommit = 0;
415 BEGIN;
416 LOCK TABLE t1 WRITE;
417 SELECT * FROM t1;
418 SELECT * FROM t1 WHERE a>=10000;
419 SELECT * FROM t1 FOR UPDATE;
420 SELECT * FROM t1 WHERE a>=10000 FOR UPDATE;
421 UPDATE t1 SET b = 'b' WHERE id = 4;
422 UPDATE t1 SET b = 'b' WHERE a = 10000;
423 REPLACE INTO t1 VALUES (4,3,'a');
424 INSERT INTO t1 VALUES (3,1,'e') ON DUPLICATE KEY UPDATE b = 'b';
425 INSERT INTO t1 VALUES (5,5,'e');
426 DELETE FROM t1 WHERE a = 1 AND b='a';
427 DELETE FROM t1;
428 COMMIT;
429 UNLOCK TABLES;
430 SET @end = (SELECT COUNT FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE NAME = 'lock_rec_lock_created');
431 SELECT @end - @start;
432
433 --echo # Records must not be S-locked if a table is S-locked.
434 SET @start = @end;
435 BEGIN;
436 LOCK TABLE t1 WRITE;
437 INSERT INTO t1 VALUES(1,1,'a'),(2,9999,'b'),(3,10000,'c'),(4,4,'d');
438 DELETE FROM t1 WHERE a = 9999 AND b='b';
439 COMMIT;
440 UNLOCK TABLES;
441 BEGIN;
442 LOCK TABLE t1 READ;
443 SELECT * FROM t1 LOCK IN SHARE MODE;
444 SELECT * FROM t1 WHERE a>=10000 LOCK IN SHARE MODE;
445 COMMIT;
446 UNLOCK TABLES;
447 SET @end = (SELECT COUNT FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE NAME = 'lock_rec_lock_created');
448 SELECT @end - @start;
449
450 --echo # Records must not be S-locked for foreign keys enforcement
451 SET @start = @end;
452 BEGIN;
453 LOCK TABLE fl0 READ, fl1 READ, fl2 WRITE;
454 INSERT INTO fl2 VALUES (300, 500), (700, 500), (1300, 1500), (1700, 1500);
455 SELECT * FROM fl1 LOCK IN SHARE MODE;
456 COMMIT;
457 UNLOCK TABLES;
458 SET @end = (SELECT COUNT FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE NAME = 'lock_rec_lock_created');
459 SELECT @end - @start;
460
461 --echo # Records must not be X-locked for foreign keys cascade
462 SET @start = @end;
463 BEGIN;
464 LOCK TABLE fl0 READ, fl1 WRITE, fl2 WRITE;
465 DELETE FROM fl1 WHERE id = 1500;
466 UPDATE fl1 SET id = 2500 WHERE id = 500;
467 COMMIT;
468 UNLOCK TABLES;
469 SET @end = (SELECT COUNT FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE NAME = 'lock_rec_lock_created');
470 SELECT @end - @start;
471
472 SET autocommit = default;
473 DROP TABLE t1;
474 DROP TABLE fl2;
475 DROP TABLE fl1;
476 DROP TABLE fl0;
477
478 set global innodb_monitor_disable = 'adaptive\\_hash\\_p%';
479 set global innodb_monitor_disable = 'adaptive\\_hash\\_r%';
480 set global innodb_monitor_disable = 'buffer\\_LRU\\_batch\\_n%';
481 set global innodb_monitor_disable = 'buffer\\_LRU\\_batch\\_s%';
482 set global innodb_monitor_disable = 'buffer\\_LRU\\_g%';
483 set global innodb_monitor_disable = 'buffer\\_LRU\\_s%';
484 set global innodb_monitor_disable = 'buffer\\_LRU\\_u%';
485 set global innodb_monitor_disable = 'buffer\\_f%';
486 set global innodb_monitor_disable = 'buffer\\_page\\_%';
487 set global innodb_monitor_disable = 'c%';
488 set global innodb_monitor_disable = 'ddl%';
489 set global innodb_monitor_disable = 'icp%';
490 set global innodb_monitor_disable = 'index\\_p%';
491 set global innodb_monitor_disable = 'innodb\\_di%';
492 set global innodb_monitor_disable = 'innodb\\_l%';
493 set global innodb_monitor_disable = 'innodb\\_m%';
494 set global innodb_monitor_disable = 'lock\\_re%';
495 set global innodb_monitor_disable = 'lock\\_ta%';
496 set global innodb_monitor_disable = 'log%';
497 set global innodb_monitor_disable = 'm%';
498 set global innodb_monitor_disable = 'p%';
499 set global innodb_monitor_disable = 't%';
500 set global innodb_monitor_enable = 'log\\_w%';
501 set global innodb_monitor_enable = 'trx_rseg_history_len';
502 set global innodb_monitor_enable = 'trx_undo_slots_cached';
503
504 set global innodb_monitor_enable=default;
505 set global innodb_monitor_disable=default;
506 set global innodb_monitor_reset_all=default;
507
508 select name, orig.enabled, new.enabled from
509 orig_innodb_metrics orig join information_schema.innodb_metrics new using(name)
510 where orig.enabled != new.enabled;