diff options
author | Marko Mäkelä <marko.makela@mariadb.com> | 2017-02-23 23:05:12 +0200 |
---|---|---|
committer | Marko Mäkelä <marko.makela@mariadb.com> | 2017-03-03 16:55:50 +0200 |
commit | 27b9989d316163d00177bfee8fceb10995c2ba9b (patch) | |
tree | b30f3170e4ab4b70019bb89e8172d1c46b2b2210 /mysql-test/suite/innodb | |
parent | 545f49dac361aa80aea26625233f9856ef4e16f5 (diff) | |
download | mariadb-git-27b9989d316163d00177bfee8fceb10995c2ba9b.tar.gz |
MDEV-12121 Introduce build option WITH_INNODB_AHI to disable innodb_adaptive_hash_index
The InnoDB adaptive hash index is sometimes degrading the performance of
InnoDB, and it is sometimes disabled to get more consistent performance.
We should have a compile-time option to disable the adaptive hash index.
Let us introduce two options:
OPTION(WITH_INNODB_AHI "Include innodb_adaptive_hash_index" ON)
OPTION(WITH_INNODB_ROOT_GUESS "Cache index root block descriptors" ON)
where WITH_INNODB_AHI always implies WITH_INNODB_ROOT_GUESS.
As part of this change, the misleadingly named function
trx_search_latch_release_if_reserved(trx) will be replaced with the macro
trx_assert_no_search_latch(trx) that will be empty unless
BTR_CUR_HASH_ADAPT is defined (cmake -DWITH_INNODB_AHI=ON).
We will also remove the unused column
INFORMATION_SCHEMA.INNODB_TRX.TRX_ADAPTIVE_HASH_TIMEOUT.
In MariaDB Server 10.1, it used to reflect the value of
trx_t::search_latch_timeout which could be adjusted during
row_search_for_mysql(). In 10.2, there is no such field.
Other than the removal of the unused column TRX_ADAPTIVE_HASH_TIMEOUT,
this is an almost non-functional change to the server when using the
default build options.
Some tests are adjusted so that they will work with both
-DWITH_INNODB_AHI=ON and -DWITH_INNODB_AHI=OFF. The test
innodb.innodb_monitor has been renamed to innodb.monitor
in order to track MySQL 5.7, and the duplicate tests
sys_vars.innodb_monitor_* are removed.
Diffstat (limited to 'mysql-test/suite/innodb')
-rw-r--r-- | mysql-test/suite/innodb/r/innodb_information_schema.result | 2 | ||||
-rw-r--r-- | mysql-test/suite/innodb/r/innodb_skip_innodb_is_tables.result | 26 | ||||
-rw-r--r-- | mysql-test/suite/innodb/r/monitor.result (renamed from mysql-test/suite/innodb/r/innodb_monitor.result) | 66 | ||||
-rw-r--r-- | mysql-test/suite/innodb/t/innodb_information_schema.test | 14 | ||||
-rw-r--r-- | mysql-test/suite/innodb/t/innodb_skip_innodb_is_tables.test | 9 | ||||
-rw-r--r-- | mysql-test/suite/innodb/t/monitor.test (renamed from mysql-test/suite/innodb/t/innodb_monitor.test) | 78 |
6 files changed, 140 insertions, 55 deletions
diff --git a/mysql-test/suite/innodb/r/innodb_information_schema.result b/mysql-test/suite/innodb/r/innodb_information_schema.result index 15c3af325ad..c1625f2bc3c 100644 --- a/mysql-test/suite/innodb/r/innodb_information_schema.result +++ b/mysql-test/suite/innodb/r/innodb_information_schema.result @@ -42,8 +42,6 @@ trx_isolation_level varchar(16) NO trx_unique_checks int(1) NO 0 trx_foreign_key_checks int(1) NO 0 trx_last_foreign_key_error varchar(256) YES NULL -trx_adaptive_hash_latched int(1) NO 0 -trx_adaptive_hash_timeout bigint(21) unsigned NO 0 trx_is_read_only int(1) NO 0 trx_autocommit_non_locking int(1) NO 0 trx_state trx_weight trx_tables_in_use trx_tables_locked trx_rows_locked trx_rows_modified trx_concurrency_tickets trx_isolation_level trx_unique_checks trx_foreign_key_checks diff --git a/mysql-test/suite/innodb/r/innodb_skip_innodb_is_tables.result b/mysql-test/suite/innodb/r/innodb_skip_innodb_is_tables.result index f515cb047f1..0e12f1bccf9 100644 --- a/mysql-test/suite/innodb/r/innodb_skip_innodb_is_tables.result +++ b/mysql-test/suite/innodb/r/innodb_skip_innodb_is_tables.result @@ -1,5 +1,6 @@ -select * from information_schema.innodb_trx; -trx_id trx_state trx_started trx_requested_lock_id trx_wait_started trx_weight trx_mysql_thread_id trx_query trx_operation_state trx_tables_in_use trx_tables_locked trx_lock_structs trx_lock_memory_bytes trx_rows_locked trx_rows_modified trx_concurrency_tickets trx_isolation_level trx_unique_checks trx_foreign_key_checks trx_last_foreign_key_error trx_adaptive_hash_latched trx_adaptive_hash_timeout trx_is_read_only trx_autocommit_non_locking +select count(*) from information_schema.innodb_trx; +count(*) +0 Warnings: Warning 1012 InnoDB: SELECTing from INFORMATION_SCHEMA.innodb_trx but the InnoDB storage engine is not installed select * from information_schema.innodb_locks; @@ -34,7 +35,8 @@ select * from information_schema.innodb_cmpmem_reset; page_size buffer_pool_instance pages_used pages_free relocation_ops relocation_time Warnings: Warning 1012 InnoDB: SELECTing from INFORMATION_SCHEMA.innodb_cmpmem_reset but the InnoDB storage engine is not installed -select * from information_schema.innodb_metrics; +select * from information_schema.innodb_metrics +WHERE name NOT LIKE 'adaptive_hash_%'; NAME SUBSYSTEM COUNT MAX_COUNT MIN_COUNT AVG_COUNT COUNT_RESET MAX_COUNT_RESET MIN_COUNT_RESET AVG_COUNT_RESET TIME_ENABLED TIME_DISABLED TIME_ELAPSED TIME_RESET STATUS TYPE COMMENT metadata_table_handles_opened metadata 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL disabled counter Number of table handles opened metadata_table_handles_closed metadata 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL disabled counter Number of table handles closed @@ -228,14 +230,6 @@ index_page_merge_successful index 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL NU index_page_reorg_attempts index 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL disabled counter Number of index page reorganization attempts index_page_reorg_successful index 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL disabled counter Number of successful index page reorganizations index_page_discards index 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL disabled counter Number of index pages discarded -adaptive_hash_searches adaptive_hash_index 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL disabled status_counter Number of successful searches using Adaptive Hash Index -adaptive_hash_searches_btree adaptive_hash_index 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL disabled status_counter Number of searches using B-tree on an index search -adaptive_hash_pages_added adaptive_hash_index 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL disabled counter Number of index pages on which the Adaptive Hash Index is built -adaptive_hash_pages_removed adaptive_hash_index 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL disabled counter Number of index pages whose corresponding Adaptive Hash Index entries were removed -adaptive_hash_rows_added adaptive_hash_index 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL disabled counter Number of Adaptive Hash Index rows added -adaptive_hash_rows_removed adaptive_hash_index 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL disabled counter Number of Adaptive Hash Index rows removed -adaptive_hash_rows_deleted_no_hash_entry adaptive_hash_index 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL disabled counter Number of rows deleted that did not have corresponding Adaptive Hash Index entries -adaptive_hash_rows_updated adaptive_hash_index 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL disabled counter Number of Adaptive Hash Index rows updated file_num_open_files file_system 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL disabled value Number of files currently open (innodb_num_open_files) ibuf_merges_insert change_buffer 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL disabled status_counter Number of inserted records merged by change buffering ibuf_merges_delete_mark change_buffer 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL disabled status_counter Number of deleted records merged by change buffering @@ -336,12 +330,14 @@ select * from information_schema.innodb_ft_index_table; WORD FIRST_DOC_ID LAST_DOC_ID DOC_COUNT DOC_ID POSITION select * from information_schema.innodb_ft_config; KEY VALUE -select * from information_schema.innodb_buffer_page; -POOL_ID BLOCK_ID SPACE PAGE_NUMBER PAGE_TYPE FLUSH_TYPE FIX_COUNT IS_HASHED NEWEST_MODIFICATION OLDEST_MODIFICATION ACCESS_TIME TABLE_NAME INDEX_NAME NUMBER_RECORDS DATA_SIZE COMPRESSED_SIZE PAGE_STATE IO_FIX IS_OLD FREE_PAGE_CLOCK +select count(*) from information_schema.innodb_buffer_page; +count(*) +0 Warnings: Warning 1012 InnoDB: SELECTing from INFORMATION_SCHEMA.innodb_buffer_page but the InnoDB storage engine is not installed -select * from information_schema.innodb_buffer_page_lru; -POOL_ID LRU_POSITION SPACE PAGE_NUMBER PAGE_TYPE FLUSH_TYPE FIX_COUNT IS_HASHED NEWEST_MODIFICATION OLDEST_MODIFICATION ACCESS_TIME TABLE_NAME INDEX_NAME NUMBER_RECORDS DATA_SIZE COMPRESSED_SIZE COMPRESSED IO_FIX IS_OLD FREE_PAGE_CLOCK +select count(*) from information_schema.innodb_buffer_page_lru; +count(*) +0 Warnings: Warning 1012 InnoDB: SELECTing from INFORMATION_SCHEMA.innodb_buffer_page_lru but the InnoDB storage engine is not installed select * from information_schema.innodb_buffer_stats; diff --git a/mysql-test/suite/innodb/r/innodb_monitor.result b/mysql-test/suite/innodb/r/monitor.result index 263da6070b2..f81234c7d42 100644 --- a/mysql-test/suite/innodb/r/innodb_monitor.result +++ b/mysql-test/suite/innodb/r/monitor.result @@ -1,5 +1,6 @@ set global innodb_monitor_disable = All; -select name, status from information_schema.innodb_metrics; +select name, status from information_schema.innodb_metrics +where name NOT LIKE 'adaptive_hash_%'; name status metadata_table_handles_opened disabled metadata_table_handles_closed disabled @@ -193,14 +194,6 @@ index_page_merge_successful disabled index_page_reorg_attempts disabled index_page_reorg_successful disabled index_page_discards disabled -adaptive_hash_searches disabled -adaptive_hash_searches_btree disabled -adaptive_hash_pages_added disabled -adaptive_hash_pages_removed disabled -adaptive_hash_rows_added disabled -adaptive_hash_rows_removed disabled -adaptive_hash_rows_deleted_no_hash_entry disabled -adaptive_hash_rows_updated disabled file_num_open_files disabled ibuf_merges_insert disabled ibuf_merges_delete_mark disabled @@ -333,8 +326,8 @@ set global innodb_monitor_enable=""; ERROR 42000: Variable 'innodb_monitor_enable' can't be set to the value of '' set global innodb_monitor_enable="_"; ERROR 42000: Variable 'innodb_monitor_enable' can't be set to the value of '_' -set global innodb_monitor_disable = module_metadata; -set global innodb_monitor_reset_all = module_metadata; +SET global innodb_monitor_disable = module_metadata; +SET global innodb_monitor_reset_all = module_metadata; set global innodb_monitor_enable = metadata_table_handles_opened; create table monitor_test(col int) engine = innodb; select * from monitor_test; @@ -608,7 +601,7 @@ icp_no_match 0 icp_out_of_range 1 icp_match 1 drop table monitor_test; -set global innodb_monitor_disable = all; +set global innodb_monitor_disable = All; set global innodb_monitor_reset_all = all; select 1 from `information_schema`.`INNODB_METRICS` where case (1) when (1) then (AVG_COUNT_RESET) else (1) end; @@ -617,3 +610,52 @@ 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; +# +# Bug#22576241 SETTING INNODB_MONITOR_ENABLE TO ALL DOES NOT ENABLE ALL +# MONITORS +# +CREATE TABLE t1 (a INT PRIMARY KEY) ENGINE=InnoDB; +SELECT NAME, COUNT FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE NAME +LIKE 'buffer_page_written_index_leaf'; +NAME COUNT +buffer_page_written_index_leaf 0 +SET GLOBAL innodb_monitor_enable='module_buffer_page'; +INSERT INTO t1 VALUES (1), (2), (3), (4); +FLUSH TABLES t1 FOR EXPORT; +UNLOCK TABLES; +SELECT NAME, COUNT FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE NAME +LIKE 'buffer_page_written_index_leaf'; +NAME COUNT +buffer_page_written_index_leaf NNNN +SET GLOBAL innodb_monitor_disable='module_buffer_page'; +SET GLOBAL innodb_monitor_reset_all='module_buffer_page'; +SELECT NAME, COUNT FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE NAME +LIKE 'buffer_page_written_index_leaf'; +NAME COUNT +buffer_page_written_index_leaf 0 +SET GLOBAL innodb_monitor_enable='%'; +INSERT INTO t1 VALUES (5), (6), (7), (8); +FLUSH TABLES t1 FOR EXPORT; +UNLOCK TABLES; +SELECT NAME, COUNT FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE NAME +LIKE 'buffer_page_written_index_leaf'; +NAME COUNT +buffer_page_written_index_leaf NNNN +SET GLOBAL innodb_monitor_disable='%'; +SET GLOBAL innodb_monitor_reset_all='%'; +SELECT NAME, COUNT FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE NAME +LIKE 'buffer_page_written_index_leaf'; +NAME COUNT +buffer_page_written_index_leaf 0 +SET GLOBAL innodb_monitor_enable='ALL'; +INSERT INTO t1 VALUES (9), (10), (11), (12); +FLUSH TABLES t1 FOR EXPORT; +UNLOCK TABLES; +SELECT NAME, COUNT FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE NAME +LIKE 'buffer_page_written_index_leaf'; +NAME COUNT +buffer_page_written_index_leaf NNNN +SET GLOBAL innodb_monitor_enable=default; +SET GLOBAL innodb_monitor_disable=default; +SET GLOBAL innodb_monitor_reset_all=default; +DROP TABLE t1; diff --git a/mysql-test/suite/innodb/t/innodb_information_schema.test b/mysql-test/suite/innodb/t/innodb_information_schema.test index da7ee016f29..375348efdd0 100644 --- a/mysql-test/suite/innodb/t/innodb_information_schema.test +++ b/mysql-test/suite/innodb/t/innodb_information_schema.test @@ -17,10 +17,6 @@ SET storage_engine=InnoDB; --- disable_warnings -DROP TABLE IF EXISTS t_min, t_max; --- enable_warnings - let $table_def = ( c01 TINYINT, @@ -182,13 +178,13 @@ DROP TABLE t_min, t_max, ```t'\"_str`; # INFORMATION_SCHEMA.INNODB_TRX # +CREATE TABLE t1 LIKE INFORMATION_SCHEMA.INNODB_TRX; +-- error 0,ER_CANT_DROP_FIELD_OR_KEY +ALTER TABLE t1 DROP COLUMN trx_adaptive_hash_latched; -- enable_result_log -DESCRIBE INFORMATION_SCHEMA.INNODB_TRX; +DESCRIBE t1; -- disable_result_log - --- disable_warnings -DROP TABLE IF EXISTS t1; --- enable_warnings +DROP TABLE t1; CREATE TABLE t1 ( c01 INT, diff --git a/mysql-test/suite/innodb/t/innodb_skip_innodb_is_tables.test b/mysql-test/suite/innodb/t/innodb_skip_innodb_is_tables.test index 01ced047302..812dcc60477 100644 --- a/mysql-test/suite/innodb/t/innodb_skip_innodb_is_tables.test +++ b/mysql-test/suite/innodb/t/innodb_skip_innodb_is_tables.test @@ -1,6 +1,6 @@ --source include/not_embedded.inc -select * from information_schema.innodb_trx; +select count(*) from information_schema.innodb_trx; select * from information_schema.innodb_locks; select * from information_schema.innodb_lock_waits; select * from information_schema.innodb_cmp; @@ -9,15 +9,16 @@ select * from information_schema.innodb_cmp_per_index; select * from information_schema.innodb_cmp_per_index_reset; select * from information_schema.innodb_cmpmem; select * from information_schema.innodb_cmpmem_reset; -select * from information_schema.innodb_metrics; +select * from information_schema.innodb_metrics +WHERE name NOT LIKE 'adaptive_hash_%'; select * from information_schema.innodb_ft_default_stopword; select * from information_schema.innodb_ft_deleted; select * from information_schema.innodb_ft_being_deleted; select * from information_schema.innodb_ft_index_cache; select * from information_schema.innodb_ft_index_table; select * from information_schema.innodb_ft_config; -select * from information_schema.innodb_buffer_page; -select * from information_schema.innodb_buffer_page_lru; +select count(*) from information_schema.innodb_buffer_page; +select count(*) from information_schema.innodb_buffer_page_lru; --error 0,1109 select * from information_schema.innodb_buffer_stats; select * from information_schema.innodb_sys_tables; diff --git a/mysql-test/suite/innodb/t/innodb_monitor.test b/mysql-test/suite/innodb/t/monitor.test index 864e0cae862..da33a720839 100644 --- a/mysql-test/suite/innodb/t/innodb_monitor.test +++ b/mysql-test/suite/innodb/t/monitor.test @@ -1,14 +1,16 @@ -# This is the test for Metrics Monitor Table feature. -# Test the metrics monitor system's control system -# and counter accuracy. +# This test used to be duplicated in the following: +# sys_vars.innodb_monitor_reset_basic +# sys_vars.innodb_monitor_reset_all_basic +# sys_vars.innodb_monitor_disable_basic +# sys_vars.innodb_monitor_enable_basic --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; +# By default, they will be off. +# Skip the adaptive_hash fields, because they depend on WITH_INNODB_AHI. +select name, status from information_schema.innodb_metrics +where name NOT LIKE 'adaptive_hash_%'; # Turn on all monitor counters set global innodb_monitor_enable = all; @@ -103,10 +105,8 @@ 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; +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; @@ -371,8 +371,7 @@ 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_disable = All; set global innodb_monitor_reset_all = all; # Test for bug #13966091 @@ -385,3 +384,56 @@ set global innodb_monitor_disable = default; set global innodb_monitor_reset = default; set global innodb_monitor_reset_all = default; -- enable_warnings + +--echo # +--echo # Bug#22576241 SETTING INNODB_MONITOR_ENABLE TO ALL DOES NOT ENABLE ALL +--echo # MONITORS +--echo # +CREATE TABLE t1 (a INT PRIMARY KEY) ENGINE=InnoDB; + +let $innodb_monitor_enable = `SELECT @@innodb_monitor_enable`; + +--replace_regex /[1-9]/NNNN/ +SELECT NAME, COUNT FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE NAME +LIKE 'buffer_page_written_index_leaf'; + +SET GLOBAL innodb_monitor_enable='module_buffer_page'; +INSERT INTO t1 VALUES (1), (2), (3), (4); FLUSH TABLES t1 FOR EXPORT; +UNLOCK TABLES; +--replace_regex /[1-9]/NNNN/ +SELECT NAME, COUNT FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE NAME +LIKE 'buffer_page_written_index_leaf'; + +SET GLOBAL innodb_monitor_disable='module_buffer_page'; +SET GLOBAL innodb_monitor_reset_all='module_buffer_page'; +--replace_regex /[1-9]/NNNN/ +SELECT NAME, COUNT FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE NAME +LIKE 'buffer_page_written_index_leaf'; + +SET GLOBAL innodb_monitor_enable='%'; +INSERT INTO t1 VALUES (5), (6), (7), (8); FLUSH TABLES t1 FOR EXPORT; +UNLOCK TABLES; +--replace_regex /[1-9]/NNNN/ +SELECT NAME, COUNT FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE NAME +LIKE 'buffer_page_written_index_leaf'; + +SET GLOBAL innodb_monitor_disable='%'; +SET GLOBAL innodb_monitor_reset_all='%'; +--replace_regex /[1-9]/NNNN/ +SELECT NAME, COUNT FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE NAME +LIKE 'buffer_page_written_index_leaf'; + +SET GLOBAL innodb_monitor_enable='ALL'; +INSERT INTO t1 VALUES (9), (10), (11), (12); FLUSH TABLES t1 FOR EXPORT; +UNLOCK TABLES; +--replace_regex /[1-9]/NNNN/ +SELECT NAME, COUNT FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE NAME +LIKE 'buffer_page_written_index_leaf'; + +--disable_warnings +SET GLOBAL innodb_monitor_enable=default; +SET GLOBAL innodb_monitor_disable=default; +SET GLOBAL innodb_monitor_reset_all=default; +--enable_warnings + +DROP TABLE t1; |