summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
authorMarko Mäkelä <marko.makela@mariadb.com>2019-09-09 14:03:03 +0300
committerMarko Mäkelä <marko.makela@mariadb.com>2019-09-09 14:18:24 +0300
commit70bf03765399ea1231b697875ac5c081f9f4a7ef (patch)
treed9f242702295d9b31c728d299a2dba9b1dfca5fd
parent05a5cc117488cb13cccf967769ba84cc24fa270f (diff)
downloadmariadb-git-bb-10.5-MDEV-20487.tar.gz
MDEV-20487 Remove the InnoDB adaptive hash indexbb-10.5-MDEV-20487
Based on the performance testing that was conducted in MDEV-17492, the InnoDB adaptive hash index could only help performance in specific, almost-read-only workloads. It could slow down all kinds of workloads (especially DROP TABLE, TRUNCATE TABLE, ALTER TABLE, or DROP INDEX operations), and it can become corrupted, causing crashes and possibly data corruption. Furthermore, the adaptive hash index consumes space from the InnoDB buffer pool, which could hurt performance when the working set would almost fit in the buffer pool. Given all this, it is best to remove innodb_adaptive_hash_index. For now, we will change the default value of the build option WITH_INNODB_AHI to OFF and adjust tests. The test rpl.rpl_failed_drop_tbl_binlog depends on DROP TABLE being able to be interrupted while it is dropping pages from the adaptive hash index. It will be skipped from now on by default, but it will be tested for builds WITH_INNODB_AHI=ON.
-rw-r--r--mysql-test/main/sp-group.result4
-rw-r--r--mysql-test/suite/innodb/r/innodb_buffer_pool_resize.result2
-rw-r--r--mysql-test/suite/innodb/r/innodb_skip_innodb_is_tables.result11
-rw-r--r--mysql-test/suite/innodb/r/monitor.result7
-rw-r--r--mysql-test/suite/innodb/t/innodb_buffer_pool_resize.test6
-rw-r--r--mysql-test/suite/innodb_i_s/innodb_buffer_page.result1
-rw-r--r--mysql-test/suite/innodb_i_s/innodb_buffer_page_lru.result1
-rw-r--r--mysql-test/suite/rpl/t/rpl_failed_drop_tbl_binlog.test5
-rw-r--r--mysql-test/suite/sys_vars/r/innodb_adaptive_hash_index_basic.result65
-rw-r--r--mysql-test/suite/sys_vars/r/innodb_adaptive_hash_index_parts_basic.result48
-rw-r--r--mysql-test/suite/sys_vars/r/sysvars_innodb,32bit.rdiff17
-rw-r--r--mysql-test/suite/sys_vars/r/sysvars_innodb.result26
-rw-r--r--mysql-test/suite/sys_vars/t/innodb_adaptive_hash_index_basic.test54
-rw-r--r--mysql-test/suite/sys_vars/t/innodb_adaptive_hash_index_parts_basic.test75
-rw-r--r--sql/upgrade_conf_file.cc2
-rw-r--r--storage/innobase/handler/ha_innodb.cc28
-rw-r--r--storage/innobase/innodb.cmake2
-rw-r--r--storage/rocksdb/mysql-test/rocksdb/r/innodb_i_s_tables_disabled.result11
18 files changed, 87 insertions, 278 deletions
diff --git a/mysql-test/main/sp-group.result b/mysql-test/main/sp-group.result
index ce8dccfa2c9..195e65975b4 100644
--- a/mysql-test/main/sp-group.result
+++ b/mysql-test/main/sp-group.result
@@ -22,9 +22,9 @@ ORDER BY table1.NUMBER_RECORDS
LIMIT 0
;
CALL s1;
-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 pk f0 f1 f2 f3 f4 f5 f6 f7 f8 f9
+POOL_ID LRU_POSITION SPACE PAGE_NUMBER PAGE_TYPE FLUSH_TYPE FIX_COUNT NEWEST_MODIFICATION OLDEST_MODIFICATION ACCESS_TIME TABLE_NAME INDEX_NAME NUMBER_RECORDS DATA_SIZE COMPRESSED_SIZE COMPRESSED IO_FIX IS_OLD FREE_PAGE_CLOCK pk f0 f1 f2 f3 f4 f5 f6 f7 f8 f9
CALL s1;
-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 pk f0 f1 f2 f3 f4 f5 f6 f7 f8 f9
+POOL_ID LRU_POSITION SPACE PAGE_NUMBER PAGE_TYPE FLUSH_TYPE FIX_COUNT NEWEST_MODIFICATION OLDEST_MODIFICATION ACCESS_TIME TABLE_NAME INDEX_NAME NUMBER_RECORDS DATA_SIZE COMPRESSED_SIZE COMPRESSED IO_FIX IS_OLD FREE_PAGE_CLOCK pk f0 f1 f2 f3 f4 f5 f6 f7 f8 f9
drop table t1;
drop view view_t1;
drop procedure s1;
diff --git a/mysql-test/suite/innodb/r/innodb_buffer_pool_resize.result b/mysql-test/suite/innodb/r/innodb_buffer_pool_resize.result
index 667d31a0b69..e5981c788e1 100644
--- a/mysql-test/suite/innodb/r/innodb_buffer_pool_resize.result
+++ b/mysql-test/suite/innodb/r/innodb_buffer_pool_resize.result
@@ -1,4 +1,3 @@
-set global innodb_adaptive_hash_index=ON;
select @@innodb_buffer_pool_size;
@@innodb_buffer_pool_size
8388608
@@ -22,7 +21,6 @@ select @@innodb_buffer_pool_size;
select count(val) from t1;
count(val)
262144
-set global innodb_adaptive_hash_index=OFF;
set global innodb_buffer_pool_size = 25165824;
select @@innodb_buffer_pool_size;
@@innodb_buffer_pool_size
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 a2353913b15..243633eb184 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
@@ -226,14 +226,7 @@ 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 0 counter Number of index page reorganization attempts
index_page_reorg_successful index 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL 0 counter Number of successful index page reorganizations
index_page_discards index 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL 0 counter Number of index pages discarded
-adaptive_hash_searches adaptive_hash_index 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL 0 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 0 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 0 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 0 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 0 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 0 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 0 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 0 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 0 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 0 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 0 status_counter Number of deleted records merged by change buffering
@@ -345,11 +338,11 @@ KEY VALUE
Warnings:
Warning 1012 InnoDB: SELECTing from INFORMATION_SCHEMA.innodb_ft_config but the InnoDB storage engine is not installed
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
+POOL_ID BLOCK_ID SPACE PAGE_NUMBER PAGE_TYPE FLUSH_TYPE FIX_COUNT 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
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
+POOL_ID LRU_POSITION SPACE PAGE_NUMBER PAGE_TYPE FLUSH_TYPE FIX_COUNT NEWEST_MODIFICATION OLDEST_MODIFICATION ACCESS_TIME TABLE_NAME INDEX_NAME NUMBER_RECORDS DATA_SIZE COMPRESSED_SIZE COMPRESSED IO_FIX IS_OLD FREE_PAGE_CLOCK
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/monitor.result b/mysql-test/suite/innodb/r/monitor.result
index d6784a71299..45026ad4369 100644
--- a/mysql-test/suite/innodb/r/monitor.result
+++ b/mysql-test/suite/innodb/r/monitor.result
@@ -192,14 +192,7 @@ 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
diff --git a/mysql-test/suite/innodb/t/innodb_buffer_pool_resize.test b/mysql-test/suite/innodb/t/innodb_buffer_pool_resize.test
index 77fdb49a57d..52ce6c68e3a 100644
--- a/mysql-test/suite/innodb/t/innodb_buffer_pool_resize.test
+++ b/mysql-test/suite/innodb/t/innodb_buffer_pool_resize.test
@@ -13,7 +13,6 @@ let $wait_condition =
--disable_query_log
set @old_innodb_buffer_pool_size = @@innodb_buffer_pool_size;
-set @old_innodb_adaptive_hash_index = @@innodb_adaptive_hash_index;
if (`select (version() like '%debug%') > 0`)
{
set @old_innodb_disable_resize = @@innodb_disable_resize_buffer_pool_debug;
@@ -21,8 +20,6 @@ if (`select (version() like '%debug%') > 0`)
}
--enable_query_log
-set global innodb_adaptive_hash_index=ON;
-
select @@innodb_buffer_pool_size;
# Expand buffer pool
@@ -50,8 +47,6 @@ select @@innodb_buffer_pool_size;
select count(val) from t1;
-set global innodb_adaptive_hash_index=OFF;
-
# Expand buffer pool to 24MB
set global innodb_buffer_pool_size = 25165824;
--source include/wait_condition.inc
@@ -64,7 +59,6 @@ drop table t1;
drop view view0;
--disable_query_log
-set global innodb_adaptive_hash_index = @old_innodb_adaptive_hash_index;
set global innodb_buffer_pool_size = @old_innodb_buffer_pool_size;
if (`select (version() like '%debug%') > 0`)
{
diff --git a/mysql-test/suite/innodb_i_s/innodb_buffer_page.result b/mysql-test/suite/innodb_i_s/innodb_buffer_page.result
index 6699fd9ae79..394d16caffa 100644
--- a/mysql-test/suite/innodb_i_s/innodb_buffer_page.result
+++ b/mysql-test/suite/innodb_i_s/innodb_buffer_page.result
@@ -8,7 +8,6 @@ INNODB_BUFFER_PAGE CREATE TEMPORARY TABLE `INNODB_BUFFER_PAGE` (
`PAGE_TYPE` varchar(64) DEFAULT NULL,
`FLUSH_TYPE` int(11) unsigned NOT NULL DEFAULT 0,
`FIX_COUNT` int(11) unsigned NOT NULL DEFAULT 0,
- `IS_HASHED` int(1) NOT NULL DEFAULT 0,
`NEWEST_MODIFICATION` bigint(21) unsigned NOT NULL DEFAULT 0,
`OLDEST_MODIFICATION` bigint(21) unsigned NOT NULL DEFAULT 0,
`ACCESS_TIME` bigint(21) unsigned NOT NULL DEFAULT 0,
diff --git a/mysql-test/suite/innodb_i_s/innodb_buffer_page_lru.result b/mysql-test/suite/innodb_i_s/innodb_buffer_page_lru.result
index 00134233350..00cb92f31df 100644
--- a/mysql-test/suite/innodb_i_s/innodb_buffer_page_lru.result
+++ b/mysql-test/suite/innodb_i_s/innodb_buffer_page_lru.result
@@ -8,7 +8,6 @@ INNODB_BUFFER_PAGE_LRU CREATE TEMPORARY TABLE `INNODB_BUFFER_PAGE_LRU` (
`PAGE_TYPE` varchar(64) DEFAULT NULL,
`FLUSH_TYPE` bigint(21) unsigned NOT NULL DEFAULT 0,
`FIX_COUNT` int(11) unsigned NOT NULL DEFAULT 0,
- `IS_HASHED` int(1) NOT NULL DEFAULT 0,
`NEWEST_MODIFICATION` bigint(21) unsigned NOT NULL DEFAULT 0,
`OLDEST_MODIFICATION` bigint(21) unsigned NOT NULL DEFAULT 0,
`ACCESS_TIME` bigint(21) unsigned NOT NULL DEFAULT 0,
diff --git a/mysql-test/suite/rpl/t/rpl_failed_drop_tbl_binlog.test b/mysql-test/suite/rpl/t/rpl_failed_drop_tbl_binlog.test
index 281e2a2ab47..1d544891a9c 100644
--- a/mysql-test/suite/rpl/t/rpl_failed_drop_tbl_binlog.test
+++ b/mysql-test/suite/rpl/t/rpl_failed_drop_tbl_binlog.test
@@ -26,6 +26,11 @@
--source include/have_binlog_format_statement.inc
--source include/master-slave.inc
+if (!`select @@GLOBAL.innodb_adaptive_hash_index`)
+{
+ --skip Need innodb_adaptive_hash_index
+}
+
create table t1 (a int) engine=innodb;
create table t2 (b longblob) engine=innodb;
create table t3 (c int) engine=innodb;
diff --git a/mysql-test/suite/sys_vars/r/innodb_adaptive_hash_index_basic.result b/mysql-test/suite/sys_vars/r/innodb_adaptive_hash_index_basic.result
index 1471ae36244..c0f55de1ea3 100644
--- a/mysql-test/suite/sys_vars/r/innodb_adaptive_hash_index_basic.result
+++ b/mysql-test/suite/sys_vars/r/innodb_adaptive_hash_index_basic.result
@@ -1,8 +1,3 @@
-SET @start_global_value = @@global.innodb_adaptive_hash_index;
-Valid values are 'ON' and 'OFF'
-select @@global.innodb_adaptive_hash_index in (0, 1);
-@@global.innodb_adaptive_hash_index in (0, 1)
-1
select @@session.innodb_adaptive_hash_index;
ERROR HY000: Variable 'innodb_adaptive_hash_index' is a GLOBAL variable
show global variables like 'innodb_adaptive_hash_index';
@@ -18,66 +13,42 @@ select variable_name from information_schema.session_variables where variable_na
variable_name
INNODB_ADAPTIVE_HASH_INDEX
set global innodb_adaptive_hash_index='OFF';
+ERROR HY000: Variable 'innodb_adaptive_hash_index' is a read only variable
select @@global.innodb_adaptive_hash_index;
@@global.innodb_adaptive_hash_index
-0
+NULL
select * from information_schema.global_variables where variable_name='innodb_adaptive_hash_index';
VARIABLE_NAME VARIABLE_VALUE
-INNODB_ADAPTIVE_HASH_INDEX OFF
+INNODB_ADAPTIVE_HASH_INDEX
select * from information_schema.session_variables where variable_name='innodb_adaptive_hash_index';
VARIABLE_NAME VARIABLE_VALUE
-INNODB_ADAPTIVE_HASH_INDEX OFF
+INNODB_ADAPTIVE_HASH_INDEX
set @@global.innodb_adaptive_hash_index=1;
-select @@global.innodb_adaptive_hash_index;
-@@global.innodb_adaptive_hash_index
-1
-select * from information_schema.global_variables where variable_name='innodb_adaptive_hash_index';
-VARIABLE_NAME VARIABLE_VALUE
-INNODB_ADAPTIVE_HASH_INDEX ON
-select * from information_schema.session_variables where variable_name='innodb_adaptive_hash_index';
-VARIABLE_NAME VARIABLE_VALUE
-INNODB_ADAPTIVE_HASH_INDEX ON
+ERROR HY000: Variable 'innodb_adaptive_hash_index' is a read only variable
set global innodb_adaptive_hash_index=0;
-select @@global.innodb_adaptive_hash_index;
-@@global.innodb_adaptive_hash_index
-0
-select * from information_schema.global_variables where variable_name='innodb_adaptive_hash_index';
-VARIABLE_NAME VARIABLE_VALUE
-INNODB_ADAPTIVE_HASH_INDEX OFF
-select * from information_schema.session_variables where variable_name='innodb_adaptive_hash_index';
-VARIABLE_NAME VARIABLE_VALUE
-INNODB_ADAPTIVE_HASH_INDEX OFF
+ERROR HY000: Variable 'innodb_adaptive_hash_index' is a read only variable
set @@global.innodb_adaptive_hash_index='ON';
-select @@global.innodb_adaptive_hash_index;
-@@global.innodb_adaptive_hash_index
-1
-select * from information_schema.global_variables where variable_name='innodb_adaptive_hash_index';
-VARIABLE_NAME VARIABLE_VALUE
-INNODB_ADAPTIVE_HASH_INDEX ON
-select * from information_schema.session_variables where variable_name='innodb_adaptive_hash_index';
-VARIABLE_NAME VARIABLE_VALUE
-INNODB_ADAPTIVE_HASH_INDEX ON
+ERROR HY000: Variable 'innodb_adaptive_hash_index' is a read only variable
set session innodb_adaptive_hash_index='OFF';
-ERROR HY000: Variable 'innodb_adaptive_hash_index' is a GLOBAL variable and should be set with SET GLOBAL
+ERROR HY000: Variable 'innodb_adaptive_hash_index' is a read only variable
set @@session.innodb_adaptive_hash_index='ON';
-ERROR HY000: Variable 'innodb_adaptive_hash_index' is a GLOBAL variable and should be set with SET GLOBAL
+ERROR HY000: Variable 'innodb_adaptive_hash_index' is a read only variable
set global innodb_adaptive_hash_index=1.1;
-ERROR 42000: Incorrect argument type to variable 'innodb_adaptive_hash_index'
+ERROR HY000: Variable 'innodb_adaptive_hash_index' is a read only variable
set global innodb_adaptive_hash_index=1e1;
-ERROR 42000: Incorrect argument type to variable 'innodb_adaptive_hash_index'
+ERROR HY000: Variable 'innodb_adaptive_hash_index' is a read only variable
set global innodb_adaptive_hash_index=2;
-ERROR 42000: Variable 'innodb_adaptive_hash_index' can't be set to the value of '2'
+ERROR HY000: Variable 'innodb_adaptive_hash_index' is a read only variable
set global innodb_adaptive_hash_index=-3;
-ERROR 42000: Variable 'innodb_adaptive_hash_index' can't be set to the value of '-3'
+ERROR HY000: Variable 'innodb_adaptive_hash_index' is a read only variable
+set global innodb_adaptive_hash_index='AUTO';
+ERROR HY000: Variable 'innodb_adaptive_hash_index' is a read only variable
select @@global.innodb_adaptive_hash_index;
@@global.innodb_adaptive_hash_index
-1
+NULL
select * from information_schema.global_variables where variable_name='innodb_adaptive_hash_index';
VARIABLE_NAME VARIABLE_VALUE
-INNODB_ADAPTIVE_HASH_INDEX ON
+INNODB_ADAPTIVE_HASH_INDEX
select * from information_schema.session_variables where variable_name='innodb_adaptive_hash_index';
VARIABLE_NAME VARIABLE_VALUE
-INNODB_ADAPTIVE_HASH_INDEX ON
-set global innodb_adaptive_hash_index='AUTO';
-ERROR 42000: Variable 'innodb_adaptive_hash_index' can't be set to the value of 'AUTO'
-SET @@global.innodb_adaptive_hash_index = @start_global_value;
+INNODB_ADAPTIVE_HASH_INDEX
diff --git a/mysql-test/suite/sys_vars/r/innodb_adaptive_hash_index_parts_basic.result b/mysql-test/suite/sys_vars/r/innodb_adaptive_hash_index_parts_basic.result
deleted file mode 100644
index 965e2efedf3..00000000000
--- a/mysql-test/suite/sys_vars/r/innodb_adaptive_hash_index_parts_basic.result
+++ /dev/null
@@ -1,48 +0,0 @@
-SELECT COUNT(@@GLOBAL.innodb_adaptive_hash_index_parts);
-COUNT(@@GLOBAL.innodb_adaptive_hash_index_parts)
-1
-1 Expected
-SET @@GLOBAL.innodb_adaptive_hash_index_parts=1;
-ERROR HY000: Variable 'innodb_adaptive_hash_index_parts' is a read only variable
-Expected error 'Read only variable'
-SELECT COUNT(@@GLOBAL.innodb_adaptive_hash_index_parts);
-COUNT(@@GLOBAL.innodb_adaptive_hash_index_parts)
-1
-1 Expected
-SELECT @@GLOBAL.innodb_adaptive_hash_index_parts = VARIABLE_VALUE
-FROM INFORMATION_SCHEMA.GLOBAL_VARIABLES
-WHERE VARIABLE_NAME='innodb_adaptive_hash_index_parts';
-@@GLOBAL.innodb_adaptive_hash_index_parts = VARIABLE_VALUE
-1
-1 Expected
-SELECT COUNT(@@GLOBAL.innodb_adaptive_hash_index_parts);
-COUNT(@@GLOBAL.innodb_adaptive_hash_index_parts)
-1
-1 Expected
-SELECT COUNT(VARIABLE_VALUE)
-FROM INFORMATION_SCHEMA.GLOBAL_VARIABLES
-WHERE VARIABLE_NAME='innodb_adaptive_hash_index_parts';
-COUNT(VARIABLE_VALUE)
-1
-1 Expected
-SELECT @@innodb_adaptive_hash_index_parts = @@GLOBAL.innodb_adaptive_hash_index_parts;
-@@innodb_adaptive_hash_index_parts = @@GLOBAL.innodb_adaptive_hash_index_parts
-1
-1 Expected
-SELECT COUNT(@@innodb_adaptive_hash_index_parts);
-COUNT(@@innodb_adaptive_hash_index_parts)
-1
-1 Expected
-SELECT COUNT(@@local.innodb_adaptive_hash_index_parts);
-ERROR HY000: Variable 'innodb_adaptive_hash_index_parts' is a GLOBAL variable
-Expected error 'Variable is a GLOBAL variable'
-SELECT COUNT(@@SESSION.innodb_adaptive_hash_index_parts);
-ERROR HY000: Variable 'innodb_adaptive_hash_index_parts' is a GLOBAL variable
-Expected error 'Variable is a GLOBAL variable'
-SELECT COUNT(@@GLOBAL.innodb_adaptive_hash_index_parts);
-COUNT(@@GLOBAL.innodb_adaptive_hash_index_parts)
-1
-1 Expected
-SELECT innodb_adaptive_hash_index_parts = @@SESSION.innodb_adaptive_hash_index_parts;
-ERROR 42S22: Unknown column 'innodb_adaptive_hash_index_parts' in 'field list'
-Expected error 'Readonly variable'
diff --git a/mysql-test/suite/sys_vars/r/sysvars_innodb,32bit.rdiff b/mysql-test/suite/sys_vars/r/sysvars_innodb,32bit.rdiff
index a7850658aa9..190a8b111f9 100644
--- a/mysql-test/suite/sys_vars/r/sysvars_innodb,32bit.rdiff
+++ b/mysql-test/suite/sys_vars/r/sysvars_innodb,32bit.rdiff
@@ -1,16 +1,7 @@
---- sysvars_innodb.result
-+++ sysvars_innodb,32bit.result
-@@ -49,7 +49,7 @@
- SESSION_VALUE NULL
- DEFAULT_VALUE 8
- VARIABLE_SCOPE GLOBAL
--VARIABLE_TYPE BIGINT UNSIGNED
-+VARIABLE_TYPE INT UNSIGNED
- VARIABLE_COMMENT Number of InnoDB Adaptive Hash Index Partitions (default 8)
- NUMERIC_MIN_VALUE 1
- NUMERIC_MAX_VALUE 512
-@@ -61,7 +61,7 @@
- SESSION_VALUE NULL
+--- suite/sys_vars/r/sysvars_innodb.result 2017-09-14 10:37:46.000000000 +0000
++++ suite/sys_vars/r/sysvars_innodb,32bit.reject 2017-09-26 13:13:23.685012523 +0000
+@@ -70,7 +70,7 @@
+ GLOBAL_VALUE_ORIGIN COMPILE-TIME
DEFAULT_VALUE 150000
VARIABLE_SCOPE GLOBAL
-VARIABLE_TYPE BIGINT UNSIGNED
diff --git a/mysql-test/suite/sys_vars/r/sysvars_innodb.result b/mysql-test/suite/sys_vars/r/sysvars_innodb.result
index e44b5a06e3d..c518817fd3d 100644
--- a/mysql-test/suite/sys_vars/r/sysvars_innodb.result
+++ b/mysql-test/suite/sys_vars/r/sysvars_innodb.result
@@ -35,28 +35,28 @@ READ_ONLY NO
COMMAND_LINE_ARGUMENT REQUIRED
VARIABLE_NAME INNODB_ADAPTIVE_HASH_INDEX
SESSION_VALUE NULL
-DEFAULT_VALUE ON
+DEFAULT_VALUE
VARIABLE_SCOPE GLOBAL
-VARIABLE_TYPE BOOLEAN
-VARIABLE_COMMENT Enable InnoDB adaptive hash index (enabled by default). Disable with --skip-innodb-adaptive-hash-index.
+VARIABLE_TYPE VARCHAR
+VARIABLE_COMMENT Deprecated parameter with no effect.
NUMERIC_MIN_VALUE NULL
NUMERIC_MAX_VALUE NULL
NUMERIC_BLOCK_SIZE NULL
-ENUM_VALUE_LIST OFF,ON
-READ_ONLY NO
-COMMAND_LINE_ARGUMENT OPTIONAL
+ENUM_VALUE_LIST NULL
+READ_ONLY YES
+COMMAND_LINE_ARGUMENT REQUIRED
VARIABLE_NAME INNODB_ADAPTIVE_HASH_INDEX_PARTS
SESSION_VALUE NULL
-DEFAULT_VALUE 8
+DEFAULT_VALUE
VARIABLE_SCOPE GLOBAL
-VARIABLE_TYPE BIGINT UNSIGNED
-VARIABLE_COMMENT Number of InnoDB Adaptive Hash Index Partitions (default 8)
-NUMERIC_MIN_VALUE 1
-NUMERIC_MAX_VALUE 512
-NUMERIC_BLOCK_SIZE 0
+VARIABLE_TYPE VARCHAR
+VARIABLE_COMMENT Deprecated parameter with no effect.
+NUMERIC_MIN_VALUE NULL
+NUMERIC_MAX_VALUE NULL
+NUMERIC_BLOCK_SIZE NULL
ENUM_VALUE_LIST NULL
READ_ONLY YES
-COMMAND_LINE_ARGUMENT OPTIONAL
+COMMAND_LINE_ARGUMENT REQUIRED
VARIABLE_NAME INNODB_ADAPTIVE_MAX_SLEEP_DELAY
SESSION_VALUE NULL
DEFAULT_VALUE 150000
diff --git a/mysql-test/suite/sys_vars/t/innodb_adaptive_hash_index_basic.test b/mysql-test/suite/sys_vars/t/innodb_adaptive_hash_index_basic.test
index 88e239574d2..f9f505b5b0a 100644
--- a/mysql-test/suite/sys_vars/t/innodb_adaptive_hash_index_basic.test
+++ b/mysql-test/suite/sys_vars/t/innodb_adaptive_hash_index_basic.test
@@ -1,17 +1,5 @@
-
-
-# 2010-01-25 - Added
-#
-
--source include/have_innodb.inc
-SET @start_global_value = @@global.innodb_adaptive_hash_index;
-
-#
-# exists as global only
-#
---echo Valid values are 'ON' and 'OFF'
-select @@global.innodb_adaptive_hash_index in (0, 1);
--error ER_INCORRECT_GLOBAL_LOCAL_VAR
select @@session.innodb_adaptive_hash_index;
--replace_column 2 #
@@ -21,49 +9,31 @@ show session variables like 'innodb_adaptive_hash_index';
select variable_name from information_schema.global_variables where variable_name='innodb_adaptive_hash_index';
select variable_name from information_schema.session_variables where variable_name='innodb_adaptive_hash_index';
-#
-# show that it's writable
-#
+--error ER_INCORRECT_GLOBAL_LOCAL_VAR
set global innodb_adaptive_hash_index='OFF';
select @@global.innodb_adaptive_hash_index;
select * from information_schema.global_variables where variable_name='innodb_adaptive_hash_index';
select * from information_schema.session_variables where variable_name='innodb_adaptive_hash_index';
+--error ER_INCORRECT_GLOBAL_LOCAL_VAR
set @@global.innodb_adaptive_hash_index=1;
-select @@global.innodb_adaptive_hash_index;
-select * from information_schema.global_variables where variable_name='innodb_adaptive_hash_index';
-select * from information_schema.session_variables where variable_name='innodb_adaptive_hash_index';
+--error ER_INCORRECT_GLOBAL_LOCAL_VAR
set global innodb_adaptive_hash_index=0;
-select @@global.innodb_adaptive_hash_index;
-select * from information_schema.global_variables where variable_name='innodb_adaptive_hash_index';
-select * from information_schema.session_variables where variable_name='innodb_adaptive_hash_index';
+--error ER_INCORRECT_GLOBAL_LOCAL_VAR
set @@global.innodb_adaptive_hash_index='ON';
-select @@global.innodb_adaptive_hash_index;
-select * from information_schema.global_variables where variable_name='innodb_adaptive_hash_index';
-select * from information_schema.session_variables where variable_name='innodb_adaptive_hash_index';
---error ER_GLOBAL_VARIABLE
+--error ER_INCORRECT_GLOBAL_LOCAL_VAR
set session innodb_adaptive_hash_index='OFF';
---error ER_GLOBAL_VARIABLE
+--error ER_INCORRECT_GLOBAL_LOCAL_VAR
set @@session.innodb_adaptive_hash_index='ON';
-
-#
-# incorrect types
-#
---error ER_WRONG_TYPE_FOR_VAR
+--error ER_INCORRECT_GLOBAL_LOCAL_VAR
set global innodb_adaptive_hash_index=1.1;
---error ER_WRONG_TYPE_FOR_VAR
+--error ER_INCORRECT_GLOBAL_LOCAL_VAR
set global innodb_adaptive_hash_index=1e1;
---error ER_WRONG_VALUE_FOR_VAR
+--error ER_INCORRECT_GLOBAL_LOCAL_VAR
set global innodb_adaptive_hash_index=2;
---error ER_WRONG_VALUE_FOR_VAR
+--error ER_INCORRECT_GLOBAL_LOCAL_VAR
set global innodb_adaptive_hash_index=-3;
+--error ER_INCORRECT_GLOBAL_LOCAL_VAR
+set global innodb_adaptive_hash_index='AUTO';
select @@global.innodb_adaptive_hash_index;
select * from information_schema.global_variables where variable_name='innodb_adaptive_hash_index';
select * from information_schema.session_variables where variable_name='innodb_adaptive_hash_index';
---error ER_WRONG_VALUE_FOR_VAR
-set global innodb_adaptive_hash_index='AUTO';
-
-#
-# Cleanup
-#
-
-SET @@global.innodb_adaptive_hash_index = @start_global_value;
diff --git a/mysql-test/suite/sys_vars/t/innodb_adaptive_hash_index_parts_basic.test b/mysql-test/suite/sys_vars/t/innodb_adaptive_hash_index_parts_basic.test
deleted file mode 100644
index 3f4a9283339..00000000000
--- a/mysql-test/suite/sys_vars/t/innodb_adaptive_hash_index_parts_basic.test
+++ /dev/null
@@ -1,75 +0,0 @@
---source include/have_innodb.inc
-
-####################################################################
-# Displaying default value #
-####################################################################
-SELECT COUNT(@@GLOBAL.innodb_adaptive_hash_index_parts);
---echo 1 Expected
-
-
-####################################################################
-# Check if Value can set #
-####################################################################
-
---error ER_INCORRECT_GLOBAL_LOCAL_VAR
-SET @@GLOBAL.innodb_adaptive_hash_index_parts=1;
---echo Expected error 'Read only variable'
-
-SELECT COUNT(@@GLOBAL.innodb_adaptive_hash_index_parts);
---echo 1 Expected
-
-
-
-
-#################################################################
-# Check if the value in GLOBAL Table matches value in variable #
-#################################################################
-
---disable_warnings
-SELECT @@GLOBAL.innodb_adaptive_hash_index_parts = VARIABLE_VALUE
-FROM INFORMATION_SCHEMA.GLOBAL_VARIABLES
-WHERE VARIABLE_NAME='innodb_adaptive_hash_index_parts';
---echo 1 Expected
-
-SELECT COUNT(@@GLOBAL.innodb_adaptive_hash_index_parts);
---echo 1 Expected
-
-SELECT COUNT(VARIABLE_VALUE)
-FROM INFORMATION_SCHEMA.GLOBAL_VARIABLES
-WHERE VARIABLE_NAME='innodb_adaptive_hash_index_parts';
---echo 1 Expected
---enable_warnings
-
-
-
-################################################################################
-# Check if accessing variable with and without GLOBAL point to same variable #
-################################################################################
-SELECT @@innodb_adaptive_hash_index_parts = @@GLOBAL.innodb_adaptive_hash_index_parts;
---echo 1 Expected
-
-
-
-################################################################################
-# Check if innodb_adaptive_hash_index_parts can be accessed with and without @@ sign #
-################################################################################
-
-SELECT COUNT(@@innodb_adaptive_hash_index_parts);
---echo 1 Expected
-
---Error ER_INCORRECT_GLOBAL_LOCAL_VAR
-SELECT COUNT(@@local.innodb_adaptive_hash_index_parts);
---echo Expected error 'Variable is a GLOBAL variable'
-
---Error ER_INCORRECT_GLOBAL_LOCAL_VAR
-SELECT COUNT(@@SESSION.innodb_adaptive_hash_index_parts);
---echo Expected error 'Variable is a GLOBAL variable'
-
-SELECT COUNT(@@GLOBAL.innodb_adaptive_hash_index_parts);
---echo 1 Expected
-
---Error ER_BAD_FIELD_ERROR
-SELECT innodb_adaptive_hash_index_parts = @@SESSION.innodb_adaptive_hash_index_parts;
---echo Expected error 'Readonly variable'
-
-
diff --git a/sql/upgrade_conf_file.cc b/sql/upgrade_conf_file.cc
index 4e167f0263f..cde561dff21 100644
--- a/sql/upgrade_conf_file.cc
+++ b/sql/upgrade_conf_file.cc
@@ -41,6 +41,8 @@ static const char *removed_variables[] =
"have_ndbcluster",
"have_partitioning",
"innodb_adaptive_flushing_method",
+"innodb_adaptive_hash_index",
+"innodb_adaptive_hash_index_parts",
"innodb_adaptive_hash_index_partitions",
"innodb_additional_mem_pool_size",
"innodb_api_bk_commit_interval",
diff --git a/storage/innobase/handler/ha_innodb.cc b/storage/innobase/handler/ha_innodb.cc
index 9f4c04741b6..d6971c825a7 100644
--- a/storage/innobase/handler/ha_innodb.cc
+++ b/storage/innobase/handler/ha_innodb.cc
@@ -193,6 +193,12 @@ static ulong innodb_flush_method;
static char* innodb_file_format;
/** Deprecated; no effect other than issuing a deprecation warning. */
static char* innodb_large_prefix;
+#ifndef BTR_CUR_HASH_ADAPT
+/** Deprecated; no effect other than issuing a deprecation warning. */
+static char* innodb_ahi;
+/** Deprecated; no effect other than issuing a deprecation warning. */
+static char* innodb_ahi_parts;
+#endif /* !BTR_CUR_HASH_ADAPT */
/* This variable can be set in the server configure file, specifying
stopword table to be used */
@@ -3577,6 +3583,16 @@ static int innodb_init_params()
"xtradbinnodb-file-format/", p);
}
+#ifndef BTR_CUR_HASH_ADAPT
+ if (innodb_ahi || innodb_ahi_parts) {
+ const char* p = innodb_ahi ? "adaptive_hash_index"
+ : "adaptive_hash_index_parts";
+ sql_print_warning("The parameter innodb_%s is deprecated"
+ " and has no effect."
+ " It may be removed in future releases.", p);
+ }
+#endif
+
/* Check that values don't overflow on 32-bit systems. */
if (sizeof(ulint) == 4) {
if (innobase_buffer_pool_size > UINT_MAX32) {
@@ -18582,6 +18598,14 @@ static MYSQL_SYSVAR_STR(file_format, innodb_file_format,
static MYSQL_SYSVAR_STR(large_prefix, innodb_large_prefix,
PLUGIN_VAR_RQCMDARG | PLUGIN_VAR_READONLY,
innodb_deprecated_ignored, NULL, NULL, NULL);
+#ifndef BTR_CUR_HASH_ADAPT
+static MYSQL_SYSVAR_STR(adaptive_hash_index, innodb_ahi,
+ PLUGIN_VAR_RQCMDARG | PLUGIN_VAR_READONLY,
+ innodb_deprecated_ignored, NULL, NULL, NULL);
+static MYSQL_SYSVAR_STR(adaptive_hash_index_parts, innodb_ahi_parts,
+ PLUGIN_VAR_RQCMDARG | PLUGIN_VAR_READONLY,
+ innodb_deprecated_ignored, NULL, NULL, NULL);
+#endif /* !BTR_CUR_HASH_ADAPT */
static MYSQL_SYSVAR_BOOL(force_load_corrupted, srv_load_corrupted,
PLUGIN_VAR_NOCMDARG | PLUGIN_VAR_READONLY,
@@ -19666,10 +19690,10 @@ static struct st_mysql_sys_var* innobase_system_variables[]= {
MYSQL_SYSVAR(stats_auto_recalc),
MYSQL_SYSVAR(stats_modified_counter),
MYSQL_SYSVAR(stats_traditional),
-#ifdef BTR_CUR_HASH_ADAPT
+#if 1 /* defined BTR_CUR_HASH_ADAPT */
MYSQL_SYSVAR(adaptive_hash_index),
MYSQL_SYSVAR(adaptive_hash_index_parts),
-#endif /* BTR_CUR_HASH_ADAPT */
+#endif
MYSQL_SYSVAR(stats_method),
MYSQL_SYSVAR(replication_delay),
MYSQL_SYSVAR(status_file),
diff --git a/storage/innobase/innodb.cmake b/storage/innobase/innodb.cmake
index 964f1edb598..f218dfff84a 100644
--- a/storage/innobase/innodb.cmake
+++ b/storage/innobase/innodb.cmake
@@ -109,7 +109,7 @@ ENDIF()
# Enable InnoDB's UNIV_DEBUG in debug builds
SET(CMAKE_CXX_FLAGS_DEBUG "${CMAKE_CXX_FLAGS_DEBUG} -DUNIV_DEBUG")
-OPTION(WITH_INNODB_AHI "Include innodb_adaptive_hash_index" ON)
+OPTION(WITH_INNODB_AHI "Include innodb_adaptive_hash_index" OFF)
OPTION(WITH_INNODB_ROOT_GUESS "Cache index root block descriptors" ON)
IF(WITH_INNODB_AHI)
ADD_DEFINITIONS(-DBTR_CUR_HASH_ADAPT -DBTR_CUR_ADAPT)
diff --git a/storage/rocksdb/mysql-test/rocksdb/r/innodb_i_s_tables_disabled.result b/storage/rocksdb/mysql-test/rocksdb/r/innodb_i_s_tables_disabled.result
index 6f446a13132..47a7dd5704f 100644
--- a/storage/rocksdb/mysql-test/rocksdb/r/innodb_i_s_tables_disabled.result
+++ b/storage/rocksdb/mysql-test/rocksdb/r/innodb_i_s_tables_disabled.result
@@ -208,14 +208,7 @@ 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 0 counter Number of index page reorganization attempts
index_page_reorg_successful index 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL 0 counter Number of successful index page reorganizations
index_page_discards index 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL 0 counter Number of index pages discarded
-adaptive_hash_searches adaptive_hash_index 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL 0 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 0 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 0 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 0 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 0 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 0 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 0 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 0 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 0 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 0 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 0 status_counter Number of deleted records merged by change buffering
@@ -319,9 +312,9 @@ KEY VALUE
SELECT * FROM INFORMATION_SCHEMA.INNODB_BUFFER_POOL_STATS;
POOL_ID POOL_SIZE FREE_BUFFERS DATABASE_PAGES OLD_DATABASE_PAGES MODIFIED_DATABASE_PAGES PENDING_DECOMPRESS PENDING_READS PENDING_FLUSH_LRU PENDING_FLUSH_LIST PAGES_MADE_YOUNG PAGES_NOT_MADE_YOUNG PAGES_MADE_YOUNG_RATE PAGES_MADE_NOT_YOUNG_RATE NUMBER_PAGES_READ NUMBER_PAGES_CREATED NUMBER_PAGES_WRITTEN PAGES_READ_RATE PAGES_CREATE_RATE PAGES_WRITTEN_RATE NUMBER_PAGES_GET HIT_RATE YOUNG_MAKE_PER_THOUSAND_GETS NOT_YOUNG_MAKE_PER_THOUSAND_GETS NUMBER_PAGES_READ_AHEAD NUMBER_READ_AHEAD_EVICTED READ_AHEAD_RATE READ_AHEAD_EVICTED_RATE LRU_IO_TOTAL LRU_IO_CURRENT UNCOMPRESS_TOTAL UNCOMPRESS_CURRENT
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
+POOL_ID BLOCK_ID SPACE PAGE_NUMBER PAGE_TYPE FLUSH_TYPE FIX_COUNT 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 * 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
+POOL_ID LRU_POSITION SPACE PAGE_NUMBER PAGE_TYPE FLUSH_TYPE FIX_COUNT 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 * FROM INFORMATION_SCHEMA.INNODB_SYS_TABLES;
TABLE_ID NAME FLAG N_COLS SPACE ROW_FORMAT ZIP_PAGE_SIZE SPACE_TYPE
SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_TABLESTATS;