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.

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;