# 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="_";
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;
-- 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