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