diff options
Diffstat (limited to 'mysql-test/suite/innodb/t/innodb_monitor.test')
-rw-r--r-- | mysql-test/suite/innodb/t/innodb_monitor.test | 387 |
1 files changed, 387 insertions, 0 deletions
diff --git a/mysql-test/suite/innodb/t/innodb_monitor.test b/mysql-test/suite/innodb/t/innodb_monitor.test new file mode 100644 index 00000000000..864e0cae862 --- /dev/null +++ b/mysql-test/suite/innodb/t/innodb_monitor.test @@ -0,0 +1,387 @@ +# This is the test for Metrics Monitor Table feature. +# Test the metrics monitor system's control system +# and counter accuracy. + +--source include/have_innodb.inc + +set global innodb_monitor_disable = All; + +# Test turn on/off the monitor counter with "all" option +# By default, they will be off +select name, status from information_schema.innodb_metrics; + +# Turn on all monitor counters +set global innodb_monitor_enable = all; + +# status should all change to "enabled" +select name from information_schema.innodb_metrics where status!='enabled'; + +# Test wrong argument to the global configure option +--error ER_WRONG_VALUE_FOR_VAR +set global innodb_monitor_enable = aaa; + +# We require a valid monitor counter/module name. There is no default +# counter name or module. A warning will be printed asking user to +# specify a valid counter name. +#--disable_warnings +#set global innodb_monitor_enable = default; +#--enable_warnings + +# Turn off all monitor counters, option name should be case +# insensitive +set global innodb_monitor_disable = All; + +# status should all change to "disabled" +select name from information_schema.innodb_metrics where status!='disabled'; + +# Reset all counter values +set global innodb_monitor_reset_all = all; + +# count should all change to 0 +select name from information_schema.innodb_metrics where count!=0; + +# Test wildcard match, turn on all counters contain string "lock" +set global innodb_monitor_enable = "%lock%"; + +# All lock related counter should be enabled +select name from information_schema.innodb_metrics +where status != IF(name like "%lock%", 'enabled', 'disabled'); + +# Disable them +set global innodb_monitor_disable = "%lock%"; + +# All lock related counter should be disabled +select name, status from information_schema.innodb_metrics +where name like "%lock%"; + +# No match for "%lock*" +--error ER_WRONG_VALUE_FOR_VAR +set global innodb_monitor_enable = "%lock*"; + +# All counters will be turned on with wildcard match string with all "%" +set global innodb_monitor_enable="%%%%%%%%%%%%%%%%%%%%%%%%%%%"; + +select name from information_schema.innodb_metrics where status!='enabled'; + +# Turn off all counters +set global innodb_monitor_disable="%%%%%"; + +select name from information_schema.innodb_metrics where status!='disabled'; + +# One more round testing. All counters will be turned on with +# single wildcard character "%" +set global innodb_monitor_enable="%"; + +select name from information_schema.innodb_metrics where status!='enabled'; + +# Turn off all the counters with "%_%" +set global innodb_monitor_disable="%_%"; + +select name from information_schema.innodb_metrics where status!='disabled'; + +# Turn on all counters start with "log" +set global innodb_monitor_enable="log%%%%"; + +select name from information_schema.innodb_metrics +where status != IF(name like "log%", 'enabled', 'disabled'); + +# Turn on counters "os_data_fsync" with wildcard match "os_%a_fs_ncs", "_" +# is single character wildcard match word +set global innodb_monitor_enable="os_%a_fs_ncs"; + +# Turn on counters whose name contains "os" and "pending" with +# wildcard match "os%pending%" +set global innodb_monitor_enable="os%pending%"; + +select name, status from information_schema.innodb_metrics +where name like "os%"; + +# Empty string is an invalid option +--error ER_WRONG_VALUE_FOR_VAR +set global innodb_monitor_enable=""; + +--error ER_WRONG_VALUE_FOR_VAR +set global innodb_monitor_enable="_"; + +# Reset counters only in "module_metadata" module +set global innodb_monitor_disable = module_metadata; + +set global innodb_monitor_reset_all = module_metadata; + +# Only turn on "table_open" counter +set global innodb_monitor_enable = metadata_table_handles_opened; + +# Create a new table to test "metadata_table_handles_opened" counter +create table monitor_test(col int) engine = innodb; + +# This will open the monitor_test table +select * from monitor_test; + +# "metadata_table_handles_opened" should increment by 1 +select name, max_count, min_count, count, + max_count_reset, min_count_reset, count_reset, status +from information_schema.innodb_metrics +where name = "metadata_table_handles_opened"; + +# Reset the counter value while counter is still on (started) +# This will reset value "count_reset" but not +# "count" +set global innodb_monitor_reset = metadata_table_handles_opened; + +select name, max_count, min_count, count, + max_count_reset, min_count_reset, count_reset, status +from information_schema.innodb_metrics +where name = "metadata_table_handles_opened"; + +# re-create table again to increment "metadata_table_handles_opened" again +drop table monitor_test; + +# Create a new table to test "metadata_table_handles_opened" counter +create table monitor_test(col int) engine = innodb; + +select * from monitor_test; + +# "metadata_table_handles_opened" should increment +select name, max_count, min_count, count, + max_count_reset, min_count_reset, count_reset, status +from information_schema.innodb_metrics +where name = "metadata_table_handles_opened"; + +# Cannot reset all monitor value while the counter is on +set global innodb_monitor_reset_all = metadata_table_handles_opened; + +select name, max_count, min_count, count, + max_count_reset, min_count_reset, count_reset, status +from information_schema.innodb_metrics +where name = "metadata_table_handles_opened"; + +# Turn off the counter "metadata_table_handles_opened" +set global innodb_monitor_disable = metadata_table_handles_opened; + +# Reset the counter value while counter is off (disabled) +set global innodb_monitor_reset = metadata_table_handles_opened; + +select name, max_count, min_count, count, + max_count_reset, min_count_reset, count_reset, status +from information_schema.innodb_metrics +where name = "metadata_table_handles_opened"; + +# re-create table again. Since monitor is off, "metadata_table_handles_opened" +# should not be incremented +drop table monitor_test; + +# Create a new table to test "metadata_table_handles_opened" counter +create table monitor_test(col int) engine = innodb; + +# "metadata_table_handles_opened" should increment +select * from monitor_test; + +select name, max_count, min_count, count, + max_count_reset, min_count_reset, count_reset, status +from information_schema.innodb_metrics +where name = "metadata_table_handles_opened"; + +# Reset all the counters, include those counter *_since_start +set global innodb_monitor_reset_all = metadata_table_handles_opened; + +select name, max_count, min_count, count, + max_count_reset, min_count_reset, count_reset, status +from information_schema.innodb_metrics +where name = "metadata_table_handles_opened"; + +# Turn on "table_open" counter again +set global innodb_monitor_enable = metadata_table_handles_opened; + +# Test metadata_table_handles_opened again to see if it is working correctly +# after above round of turning on/off/reset +drop table monitor_test; + +# Create a new table to test "metadata_table_handles_opened" counter +create table monitor_test(col int) engine = innodb stats_persistent=0; + +select * from monitor_test; + +# "metadata_table_handles_opened" should increment +select name, max_count, min_count, count, + max_count_reset, min_count_reset, count_reset, status +from information_schema.innodb_metrics +where name = "metadata_table_handles_opened"; + +# Test counter "metadata_table_handles_closed", +# create index will close the old handle +set global innodb_monitor_enable = metadata_table_handles_closed; + +create index idx on monitor_test(col); + +select name, max_count, min_count, count, + max_count_reset, min_count_reset, count_reset, status +from information_schema.innodb_metrics +where name = "metadata_table_handles_closed"; + +select name, max_count, min_count, count, + max_count_reset, min_count_reset, count_reset, status +from information_schema.innodb_metrics +where name like "metadata%"; + +# Reset counters only in "module_metadata" module +set global innodb_monitor_disable = module_metadata; + +set global innodb_monitor_reset = module_metadata; + +select name, max_count, min_count, count, + max_count_reset, min_count_reset, count_reset, status +from information_schema.innodb_metrics +where name like "metadata%"; + +set global innodb_monitor_reset_all = module_metadata; + +select name, max_count, min_count, count, + max_count_reset, min_count_reset, count_reset, status +from information_schema.innodb_metrics +where name like "metadata%"; + +# Test Transaction Module +set global innodb_monitor_enable = module_trx; + +begin; +insert into monitor_test values(9); +commit; + +begin; +insert into monitor_test values(9); +rollback; + +select name, max_count, min_count, count, + max_count_reset, min_count_reset, count_reset, status +from information_schema.innodb_metrics +where name like "trx_rollbacks" or name like "trx_active_transactions"; + +set global innodb_monitor_disable = module_trx; + +# Test DML Module +set global innodb_monitor_enable = module_dml; + +insert into monitor_test values(9); + +update monitor_test set col = 10 where col = 9; + +select name, max_count, min_count, count, + max_count_reset, min_count_reset, count_reset, status +from information_schema.innodb_metrics +where name like "dml%"; + +delete from monitor_test; + +select name, max_count, min_count, count, + max_count_reset, min_count_reset, count_reset, status + from information_schema.innodb_metrics + where name like "dml%"; + +# test reset counter while the counter is on +set global innodb_monitor_reset = module_dml; + +select name, max_count, min_count, count, + max_count_reset, min_count_reset, count_reset, status +from information_schema.innodb_metrics +where name like "dml%"; + +# insert/delete some rows after the reset +insert into monitor_test values(9); +insert into monitor_test values(1); + +delete from monitor_test; + +select name, max_count, min_count, count, + max_count_reset, min_count_reset, count_reset, status +from information_schema.innodb_metrics +where name like "dml%"; + +# We do not allow reset_all while the counter is on, nothing +# should be reset here +set global innodb_monitor_reset_all = module_dml; + +select name, max_count, min_count, count, + max_count_reset, min_count_reset, count_reset, status +from information_schema.innodb_metrics +where name like "dml%"; + +# Turn off the counter +set global innodb_monitor_disable = module_dml; + +select name, max_count, min_count, count, + max_count_reset, min_count_reset, count_reset, status +from information_schema.innodb_metrics +where name like "dml%"; + +# Reset all counter values +set global innodb_monitor_reset_all = module_dml; + +select name, max_count, min_count, count, + max_count_reset, min_count_reset, count_reset, status +from information_schema.innodb_metrics +where name like "dml%"; + +# Open individual counter "dml_inserts" +set global innodb_monitor_enable = dml_inserts; + +insert into monitor_test values(9); +insert into monitor_test values(1); + +delete from monitor_test; + +# Only counter "dml_inserts" should be updated +select name, max_count, min_count, count, + max_count_reset, min_count_reset, count_reset, status +from information_schema.innodb_metrics +where name like "dml%"; + +set global innodb_monitor_disable = module_dml; + +drop table monitor_test; + +set global innodb_monitor_enable = file_num_open_files; + +# Counters are unpredictable when innodb-file-per-table is on +--replace_column 2 # 3 # 4 # 5 # 6 # 7 # +select name, max_count, min_count, count, + max_count_reset, min_count_reset, count_reset, status +from information_schema.innodb_metrics +where name like "file_num_open_files"; + +set global innodb_monitor_disable = file_num_open_files; + +# Test ICP module counters +set global innodb_monitor_enable = "icp%"; + +create table monitor_test(a char(3), b int, c char(2), +primary key (a(1), c(1)), key(b)) engine = innodb; + +insert into monitor_test values("13", 2, "aa"); + +select a from monitor_test where b < 1 for update; + +# should have icp_attempts = 1 and icp_out_of_range = 1 +select name, count from information_schema.innodb_metrics +where name like "icp%"; + +# should have icp_attempts = 2 and icp_match = 1 +select a from monitor_test where b < 3 for update; + +select name, count from information_schema.innodb_metrics +where name like "icp%"; + +drop table monitor_test; + +set global innodb_monitor_disable = all; +set global innodb_monitor_reset_all = all; + +# Test for bug #13966091 +select 1 from `information_schema`.`INNODB_METRICS` +where case (1) when (1) then (AVG_COUNT_RESET) else (1) end; + +-- disable_warnings +set global innodb_monitor_enable = default; +set global innodb_monitor_disable = default; +set global innodb_monitor_reset = default; +set global innodb_monitor_reset_all = default; +-- enable_warnings |