SET @save_stats_persistent = @@GLOBAL.innodb_stats_persistent; SET GLOBAL innodb_stats_persistent = 0; CREATE TABLE tab(a BIGINT PRIMARY KEY,c1 TINYTEXT,c2 TEXT,c3 MEDIUMTEXT, c4 TINYBLOB,c5 BLOB,c6 MEDIUMBLOB,c7 LONGBLOB) ENGINE=InnoDB; CREATE INDEX index1 ON tab(c1(255)) COMMENT 'Check index level merge MERGE_THRESHOLD=51'; Warnings: Warning 1478 InnoDB: Invalid value for MERGE_THRESHOLD in the CREATE TABLE statement. The value is ignored. CREATE INDEX index2 ON tab(c2(750)) COMMENT 'Check index level merge MERGE_THRESHOLD=-1'; Warnings: Warning 1478 InnoDB: Invalid value for MERGE_THRESHOLD in the CREATE TABLE statement. The value is ignored. Warning 1478 InnoDB: Invalid value for MERGE_THRESHOLD in the CREATE TABLE statement. The value is ignored. CREATE INDEX index3 ON tab(c3(750)) COMMENT 'Check index level merge MERGE_THRESHOLD=20'; Warnings: Warning 1478 InnoDB: Invalid value for MERGE_THRESHOLD in the CREATE TABLE statement. The value is ignored. Warning 1478 InnoDB: Invalid value for MERGE_THRESHOLD in the CREATE TABLE statement. The value is ignored. CREATE INDEX index4 ON tab(c4(255)) COMMENT 'Check index level merge MERGE_THRESHOLD=25'; Warnings: Warning 1478 InnoDB: Invalid value for MERGE_THRESHOLD in the CREATE TABLE statement. The value is ignored. Warning 1478 InnoDB: Invalid value for MERGE_THRESHOLD in the CREATE TABLE statement. The value is ignored. CREATE INDEX index5 ON tab(c5(750)) COMMENT 'Check index level merge MERGE_THRESHOLD=30'; Warnings: Warning 1478 InnoDB: Invalid value for MERGE_THRESHOLD in the CREATE TABLE statement. The value is ignored. Warning 1478 InnoDB: Invalid value for MERGE_THRESHOLD in the CREATE TABLE statement. The value is ignored. CREATE INDEX index6 ON tab(c6(750)) COMMENT 'Check index level merge MERGE_THRESHOLD=35'; Warnings: Warning 1478 InnoDB: Invalid value for MERGE_THRESHOLD in the CREATE TABLE statement. The value is ignored. Warning 1478 InnoDB: Invalid value for MERGE_THRESHOLD in the CREATE TABLE statement. The value is ignored. CREATE INDEX index7 ON tab(c7(750)) COMMENT 'Check index level merge MERGE_THRESHOLD=40'; Warnings: Warning 1478 InnoDB: Invalid value for MERGE_THRESHOLD in the CREATE TABLE statement. The value is ignored. Warning 1478 InnoDB: Invalid value for MERGE_THRESHOLD in the CREATE TABLE statement. The value is ignored. SHOW CREATE TABLE tab; Table Create Table tab CREATE TABLE `tab` ( `a` bigint(20) NOT NULL, `c1` tinytext DEFAULT NULL, `c2` text DEFAULT NULL, `c3` mediumtext DEFAULT NULL, `c4` tinyblob DEFAULT NULL, `c5` blob DEFAULT NULL, `c6` mediumblob DEFAULT NULL, `c7` longblob DEFAULT NULL, PRIMARY KEY (`a`), KEY `index1` (`c1`(255)) COMMENT 'Check index level merge MERGE_THRESHOLD=51', KEY `index2` (`c2`(750)) COMMENT 'Check index level merge MERGE_THRESHOLD=-1', KEY `index3` (`c3`(750)) COMMENT 'Check index level merge MERGE_THRESHOLD=20', KEY `index4` (`c4`(255)) COMMENT 'Check index level merge MERGE_THRESHOLD=25', KEY `index5` (`c5`(750)) COMMENT 'Check index level merge MERGE_THRESHOLD=30', KEY `index6` (`c6`(750)) COMMENT 'Check index level merge MERGE_THRESHOLD=35', KEY `index7` (`c7`(750)) COMMENT 'Check index level merge MERGE_THRESHOLD=40' ) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci SELECT t.NAME as TABLE_NAME, i.NAME as INDEX_NAME, i.MERGE_THRESHOLD FROM INFORMATION_SCHEMA.INNODB_SYS_TABLES t, INFORMATION_SCHEMA.INNODB_SYS_INDEXES i WHERE t.TABLE_ID = i.TABLE_ID AND t.NAME = 'test/tab'; TABLE_NAME INDEX_NAME MERGE_THRESHOLD test/tab PRIMARY 50 test/tab index1 50 test/tab index2 50 test/tab index3 20 test/tab index4 25 test/tab index5 30 test/tab index6 35 test/tab index7 40 ALTER TABLE tab comment='MERGE_THRESHOLD=49'; Warnings: Warning 1478 InnoDB: Invalid value for MERGE_THRESHOLD in the CREATE TABLE statement. The value is ignored. Warning 1478 InnoDB: Invalid value for MERGE_THRESHOLD in the CREATE TABLE statement. The value is ignored. SHOW CREATE TABLE tab; Table Create Table tab CREATE TABLE `tab` ( `a` bigint(20) NOT NULL, `c1` tinytext DEFAULT NULL, `c2` text DEFAULT NULL, `c3` mediumtext DEFAULT NULL, `c4` tinyblob DEFAULT NULL, `c5` blob DEFAULT NULL, `c6` mediumblob DEFAULT NULL, `c7` longblob DEFAULT NULL, PRIMARY KEY (`a`), KEY `index1` (`c1`(255)) COMMENT 'Check index level merge MERGE_THRESHOLD=51', KEY `index2` (`c2`(750)) COMMENT 'Check index level merge MERGE_THRESHOLD=-1', KEY `index3` (`c3`(750)) COMMENT 'Check index level merge MERGE_THRESHOLD=20', KEY `index4` (`c4`(255)) COMMENT 'Check index level merge MERGE_THRESHOLD=25', KEY `index5` (`c5`(750)) COMMENT 'Check index level merge MERGE_THRESHOLD=30', KEY `index6` (`c6`(750)) COMMENT 'Check index level merge MERGE_THRESHOLD=35', KEY `index7` (`c7`(750)) COMMENT 'Check index level merge MERGE_THRESHOLD=40' ) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci COMMENT='MERGE_THRESHOLD=49' SELECT t.NAME as TABLE_NAME, i.NAME as INDEX_NAME, i.MERGE_THRESHOLD FROM INFORMATION_SCHEMA.INNODB_SYS_TABLES t, INFORMATION_SCHEMA.INNODB_SYS_INDEXES i WHERE t.TABLE_ID = i.TABLE_ID AND t.NAME = 'test/tab'; TABLE_NAME INDEX_NAME MERGE_THRESHOLD test/tab PRIMARY 49 test/tab index1 49 test/tab index2 49 test/tab index3 20 test/tab index4 25 test/tab index5 30 test/tab index6 35 test/tab index7 40 ALTER TABLE tab MODIFY COLUMN c7 VARCHAR(2048) ; Warnings: Warning 1478 InnoDB: Invalid value for MERGE_THRESHOLD in the CREATE TABLE statement. The value is ignored. Warning 1478 InnoDB: Invalid value for MERGE_THRESHOLD in the CREATE TABLE statement. The value is ignored. SHOW CREATE TABLE tab; Table Create Table tab CREATE TABLE `tab` ( `a` bigint(20) NOT NULL, `c1` tinytext DEFAULT NULL, `c2` text DEFAULT NULL, `c3` mediumtext DEFAULT NULL, `c4` tinyblob DEFAULT NULL, `c5` blob DEFAULT NULL, `c6` mediumblob DEFAULT NULL, `c7` varchar(2048) DEFAULT NULL, PRIMARY KEY (`a`), KEY `index1` (`c1`(255)) COMMENT 'Check index level merge MERGE_THRESHOLD=51', KEY `index2` (`c2`(750)) COMMENT 'Check index level merge MERGE_THRESHOLD=-1', KEY `index3` (`c3`(750)) COMMENT 'Check index level merge MERGE_THRESHOLD=20', KEY `index4` (`c4`(255)) COMMENT 'Check index level merge MERGE_THRESHOLD=25', KEY `index5` (`c5`(750)) COMMENT 'Check index level merge MERGE_THRESHOLD=30', KEY `index6` (`c6`(750)) COMMENT 'Check index level merge MERGE_THRESHOLD=35', KEY `index7` (`c7`(750)) COMMENT 'Check index level merge MERGE_THRESHOLD=40' ) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci COMMENT='MERGE_THRESHOLD=49' SELECT t.NAME as TABLE_NAME, i.NAME as INDEX_NAME, i.MERGE_THRESHOLD FROM INFORMATION_SCHEMA.INNODB_SYS_TABLES t, INFORMATION_SCHEMA.INNODB_SYS_INDEXES i WHERE t.TABLE_ID = i.TABLE_ID AND t.NAME = 'test/tab'; TABLE_NAME INDEX_NAME MERGE_THRESHOLD test/tab PRIMARY 49 test/tab index1 49 test/tab index2 49 test/tab index3 20 test/tab index4 25 test/tab index5 30 test/tab index6 35 test/tab index7 40 ALTER TABLE tab ADD INDEX index8 (c7(750)) COMMENT 'Check index level merge MERGE_THRESHOLD=45'; Warnings: Note 1831 Duplicate index `index8`. This is deprecated and will be disallowed in a future release Warning 1478 InnoDB: Invalid value for MERGE_THRESHOLD in the CREATE TABLE statement. The value is ignored. Warning 1478 InnoDB: Invalid value for MERGE_THRESHOLD in the CREATE TABLE statement. The value is ignored. SHOW CREATE TABLE tab; Table Create Table tab CREATE TABLE `tab` ( `a` bigint(20) NOT NULL, `c1` tinytext DEFAULT NULL, `c2` text DEFAULT NULL, `c3` mediumtext DEFAULT NULL, `c4` tinyblob DEFAULT NULL, `c5` blob DEFAULT NULL, `c6` mediumblob DEFAULT NULL, `c7` varchar(2048) DEFAULT NULL, PRIMARY KEY (`a`), KEY `index1` (`c1`(255)) COMMENT 'Check index level merge MERGE_THRESHOLD=51', KEY `index2` (`c2`(750)) COMMENT 'Check index level merge MERGE_THRESHOLD=-1', KEY `index3` (`c3`(750)) COMMENT 'Check index level merge MERGE_THRESHOLD=20', KEY `index4` (`c4`(255)) COMMENT 'Check index level merge MERGE_THRESHOLD=25', KEY `index5` (`c5`(750)) COMMENT 'Check index level merge MERGE_THRESHOLD=30', KEY `index6` (`c6`(750)) COMMENT 'Check index level merge MERGE_THRESHOLD=35', KEY `index7` (`c7`(750)) COMMENT 'Check index level merge MERGE_THRESHOLD=40', KEY `index8` (`c7`(750)) COMMENT 'Check index level merge MERGE_THRESHOLD=45' ) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci COMMENT='MERGE_THRESHOLD=49' SELECT t.NAME as TABLE_NAME, i.NAME as INDEX_NAME, i.MERGE_THRESHOLD FROM INFORMATION_SCHEMA.INNODB_SYS_TABLES t, INFORMATION_SCHEMA.INNODB_SYS_INDEXES i WHERE t.TABLE_ID = i.TABLE_ID AND t.NAME = 'test/tab'; TABLE_NAME INDEX_NAME MERGE_THRESHOLD test/tab PRIMARY 49 test/tab index1 49 test/tab index2 49 test/tab index3 20 test/tab index4 25 test/tab index5 30 test/tab index6 35 test/tab index7 40 test/tab index8 45 DROP TABLE tab; # # behavior for deleting records # # test to confirm behavior (MERGE_THRESHOLD=50 (default)) CREATE TABLE tab1 (a bigint primary key, b varchar(2048)) engine=InnoDB; # check MERGE_THRESHOLD select t.NAME as TABLE_NAME, i.NAME as INDEX_NAME, i.MERGE_THRESHOLD from INFORMATION_SCHEMA.INNODB_SYS_TABLES t, INFORMATION_SCHEMA.INNODB_SYS_INDEXES i where t.TABLE_ID = i.TABLE_ID and t.NAME like 'test/tab1%'; TABLE_NAME INDEX_NAME MERGE_THRESHOLD test/tab1 PRIMARY 50 insert into tab1 values (1, repeat('a',2048)); insert into tab1 values (2, repeat('a',2048)); insert into tab1 values (3, repeat('a',2048)); insert into tab1 values (8, repeat('a',2048)); insert into tab1 values (9, repeat('a',2048)); insert into tab1 values (10, repeat('a',2048)); insert into tab1 values (11, repeat('a',2048)); insert into tab1 values (12, repeat('a',2048)); insert into tab1 values (4, repeat('a',2048)); insert into tab1 values (5, repeat('a',2048)); insert into tab1 values (6, repeat('a',2048)); insert into tab1 values (7, repeat('a',2048)); insert into tab1 values (13, repeat('a',2048)); insert into tab1 values (14, repeat('a',2048)); select PAGE_NUMBER, NUMBER_RECORDS from INFORMATION_SCHEMA.INNODB_SYS_TABLESPACES s1, INFORMATION_SCHEMA.INNODB_BUFFER_PAGE s2 where s1.SPACE = s2.SPACE AND NAME like 'test/tab1%' and PAGE_TYPE = "INDEX" order by PAGE_NUMBER, NUMBER_RECORDS; PAGE_NUMBER NUMBER_RECORDS 3 2 4 7 5 7 begin; delete from tab1 where a = 12; delete from tab1 where a = 13; delete from tab1 where a = 14; delete from tab1 where a = 5; delete from tab1 where a = 6; delete from tab1 where a = 7; commit; InnoDB 0 transactions not purged # check page merge happens (nothing is expected) SELECT name,count_reset FROM information_schema.innodb_metrics WHERE name like 'index_page_merge_%'; name count_reset index_page_merge_attempts 0 index_page_merge_successful 0 select PAGE_NUMBER, NUMBER_RECORDS from INFORMATION_SCHEMA.INNODB_SYS_TABLESPACES s1, INFORMATION_SCHEMA.INNODB_BUFFER_PAGE s2 where s1.SPACE = s2.SPACE AND NAME like 'test/tab1%' and PAGE_TYPE = "INDEX" order by PAGE_NUMBER, NUMBER_RECORDS; PAGE_NUMBER NUMBER_RECORDS 3 2 4 4 5 4 delete from tab1 where a = 11; InnoDB 0 transactions not purged # check page merge happens (MERGE_THRESHOLD=50 causes merge here) SELECT name,count_reset FROM information_schema.innodb_metrics WHERE name like 'index_page_merge_%'; name count_reset index_page_merge_attempts 1 index_page_merge_successful 1 delete from tab1 where a = 10; InnoDB 0 transactions not purged # check page merge happens (MERGE_THRESHOLD=35 causes merge here) SELECT name,count_reset FROM information_schema.innodb_metrics WHERE name like 'index_page_merge_%'; name count_reset index_page_merge_attempts 2 index_page_merge_successful 2 delete from tab1 where a = 9; InnoDB 0 transactions not purged # check page merge happens (MERGE_THRESHOLD=25 causes merge here) SELECT name,count_reset FROM information_schema.innodb_metrics WHERE name like 'index_page_merge_%'; name count_reset index_page_merge_attempts 2 index_page_merge_successful 2 DROP TABLE tab1; # test to confirm behavior (MERGE_THRESHOLD=35) CREATE TABLE tab1 (a bigint primary key, b varchar(2048)) engine=InnoDB COMMENT='MERGE_THRESHOLD=35'; # check MERGE_THRESHOLD select t.NAME as TABLE_NAME, i.NAME as INDEX_NAME, i.MERGE_THRESHOLD from INFORMATION_SCHEMA.INNODB_SYS_TABLES t, INFORMATION_SCHEMA.INNODB_SYS_INDEXES i where t.TABLE_ID = i.TABLE_ID and t.NAME like 'test/tab1%'; TABLE_NAME INDEX_NAME MERGE_THRESHOLD test/tab1 PRIMARY 35 insert into tab1 values (1, repeat('a',2048)); insert into tab1 values (2, repeat('a',2048)); insert into tab1 values (3, repeat('a',2048)); insert into tab1 values (8, repeat('a',2048)); insert into tab1 values (9, repeat('a',2048)); insert into tab1 values (10, repeat('a',2048)); insert into tab1 values (11, repeat('a',2048)); insert into tab1 values (12, repeat('a',2048)); insert into tab1 values (4, repeat('a',2048)); insert into tab1 values (5, repeat('a',2048)); insert into tab1 values (6, repeat('a',2048)); insert into tab1 values (7, repeat('a',2048)); insert into tab1 values (13, repeat('a',2048)); insert into tab1 values (14, repeat('a',2048)); select PAGE_NUMBER, NUMBER_RECORDS from INFORMATION_SCHEMA.INNODB_SYS_TABLESPACES s1, INFORMATION_SCHEMA.INNODB_BUFFER_PAGE s2 where s1.SPACE = s2.SPACE AND NAME like 'test/tab1%' and PAGE_TYPE = "INDEX" order by PAGE_NUMBER, NUMBER_RECORDS; PAGE_NUMBER NUMBER_RECORDS 3 2 4 7 5 7 begin; delete from tab1 where a = 12; delete from tab1 where a = 13; delete from tab1 where a = 14; delete from tab1 where a = 5; delete from tab1 where a = 6; delete from tab1 where a = 7; commit; InnoDB 0 transactions not purged # check page merge happens (nothing is expected) SELECT name,count_reset FROM information_schema.innodb_metrics WHERE name like 'index_page_merge_%'; name count_reset index_page_merge_attempts 0 index_page_merge_successful 0 select PAGE_NUMBER, NUMBER_RECORDS from INFORMATION_SCHEMA.INNODB_SYS_TABLESPACES s1, INFORMATION_SCHEMA.INNODB_BUFFER_PAGE s2 where s1.SPACE = s2.SPACE AND NAME like 'test/tab1%' and PAGE_TYPE = "INDEX" order by PAGE_NUMBER, NUMBER_RECORDS; PAGE_NUMBER NUMBER_RECORDS 3 2 4 4 5 4 delete from tab1 where a = 11; InnoDB 0 transactions not purged # check page merge happens (MERGE_THRESHOLD=50 causes merge here) SELECT name,count_reset FROM information_schema.innodb_metrics WHERE name like 'index_page_merge_%'; name count_reset index_page_merge_attempts 0 index_page_merge_successful 0 delete from tab1 where a = 10; InnoDB 0 transactions not purged # check page merge happens (MERGE_THRESHOLD=35 causes merge here) SELECT name,count_reset FROM information_schema.innodb_metrics WHERE name like 'index_page_merge_%'; name count_reset index_page_merge_attempts 1 index_page_merge_successful 1 delete from tab1 where a = 9; InnoDB 0 transactions not purged # check page merge happens (MERGE_THRESHOLD=25 causes merge here) SELECT name,count_reset FROM information_schema.innodb_metrics WHERE name like 'index_page_merge_%'; name count_reset index_page_merge_attempts 2 index_page_merge_successful 2 DROP TABLE tab1; # test to confirm behavior (MERGE_THRESHOLD=25) CREATE TABLE tab1 (a bigint primary key, b varchar(2048)) engine=InnoDB COMMENT='MERGE_THRESHOLD=25'; # check MERGE_THRESHOLD select t.NAME as TABLE_NAME, i.NAME as INDEX_NAME, i.MERGE_THRESHOLD from INFORMATION_SCHEMA.INNODB_SYS_TABLES t, INFORMATION_SCHEMA.INNODB_SYS_INDEXES i where t.TABLE_ID = i.TABLE_ID and t.NAME like 'test/tab1%'; TABLE_NAME INDEX_NAME MERGE_THRESHOLD test/tab1 PRIMARY 25 insert into tab1 values (1, repeat('a',2048)); insert into tab1 values (2, repeat('a',2048)); insert into tab1 values (3, repeat('a',2048)); insert into tab1 values (8, repeat('a',2048)); insert into tab1 values (9, repeat('a',2048)); insert into tab1 values (10, repeat('a',2048)); insert into tab1 values (11, repeat('a',2048)); insert into tab1 values (12, repeat('a',2048)); insert into tab1 values (4, repeat('a',2048)); insert into tab1 values (5, repeat('a',2048)); insert into tab1 values (6, repeat('a',2048)); insert into tab1 values (7, repeat('a',2048)); insert into tab1 values (13, repeat('a',2048)); insert into tab1 values (14, repeat('a',2048)); select PAGE_NUMBER, NUMBER_RECORDS from INFORMATION_SCHEMA.INNODB_SYS_TABLESPACES s1, INFORMATION_SCHEMA.INNODB_BUFFER_PAGE s2 where s1.SPACE = s2.SPACE AND NAME like 'test/tab1%' and PAGE_TYPE = "INDEX" order by PAGE_NUMBER, NUMBER_RECORDS; PAGE_NUMBER NUMBER_RECORDS 3 2 4 7 5 7 begin; delete from tab1 where a = 12; delete from tab1 where a = 13; delete from tab1 where a = 14; delete from tab1 where a = 5; delete from tab1 where a = 6; delete from tab1 where a = 7; commit; InnoDB 0 transactions not purged # check page merge happens (nothing is expected) SELECT name,count_reset FROM information_schema.innodb_metrics WHERE name like 'index_page_merge_%'; name count_reset index_page_merge_attempts 0 index_page_merge_successful 0 select PAGE_NUMBER, NUMBER_RECORDS from INFORMATION_SCHEMA.INNODB_SYS_TABLESPACES s1, INFORMATION_SCHEMA.INNODB_BUFFER_PAGE s2 where s1.SPACE = s2.SPACE AND NAME like 'test/tab1%' and PAGE_TYPE = "INDEX" order by PAGE_NUMBER, NUMBER_RECORDS; PAGE_NUMBER NUMBER_RECORDS 3 2 4 4 5 4 delete from tab1 where a = 11; InnoDB 0 transactions not purged # check page merge happens (MERGE_THRESHOLD=50 causes merge here) SELECT name,count_reset FROM information_schema.innodb_metrics WHERE name like 'index_page_merge_%'; name count_reset index_page_merge_attempts 0 index_page_merge_successful 0 delete from tab1 where a = 10; InnoDB 0 transactions not purged # check page merge happens (MERGE_THRESHOLD=35 causes merge here) SELECT name,count_reset FROM information_schema.innodb_metrics WHERE name like 'index_page_merge_%'; name count_reset index_page_merge_attempts 0 index_page_merge_successful 0 delete from tab1 where a = 9; InnoDB 0 transactions not purged # check page merge happens (MERGE_THRESHOLD=25 causes merge here) SELECT name,count_reset FROM information_schema.innodb_metrics WHERE name like 'index_page_merge_%'; name count_reset index_page_merge_attempts 1 index_page_merge_successful 1 DROP TABLE tab1; # test to confirm partitioned table (MERGE_THRESHOLD=35) CREATE TABLE tab1 (a bigint primary key, b varchar(2048)) COMMENT='MERGE_THRESHOLD=35' PARTITION BY RANGE (a) (PARTITION p0 VALUES LESS THAN (20) ENGINE = InnoDB, PARTITION p1 VALUES LESS THAN MAXVALUE ENGINE = InnoDB); # check MERGE_THRESHOLD select t.NAME as TABLE_NAME, i.NAME as INDEX_NAME, i.MERGE_THRESHOLD from INFORMATION_SCHEMA.INNODB_SYS_TABLES t, INFORMATION_SCHEMA.INNODB_SYS_INDEXES i where t.TABLE_ID = i.TABLE_ID and t.NAME like 'test/tab1%'; TABLE_NAME INDEX_NAME MERGE_THRESHOLD test/tab1#p#p0 PRIMARY 35 test/tab1#p#p1 PRIMARY 35 insert into tab1 values (1, repeat('a',2048)); insert into tab1 values (2, repeat('a',2048)); insert into tab1 values (3, repeat('a',2048)); insert into tab1 values (8, repeat('a',2048)); insert into tab1 values (9, repeat('a',2048)); insert into tab1 values (10, repeat('a',2048)); insert into tab1 values (11, repeat('a',2048)); insert into tab1 values (12, repeat('a',2048)); insert into tab1 values (4, repeat('a',2048)); insert into tab1 values (5, repeat('a',2048)); insert into tab1 values (6, repeat('a',2048)); insert into tab1 values (7, repeat('a',2048)); insert into tab1 values (13, repeat('a',2048)); insert into tab1 values (14, repeat('a',2048)); select PAGE_NUMBER, NUMBER_RECORDS from INFORMATION_SCHEMA.INNODB_SYS_TABLESPACES s1, INFORMATION_SCHEMA.INNODB_BUFFER_PAGE s2 where s1.SPACE = s2.SPACE AND NAME like 'test/tab1%' and PAGE_TYPE = "INDEX" order by PAGE_NUMBER, NUMBER_RECORDS; PAGE_NUMBER NUMBER_RECORDS 3 0 3 2 4 7 5 7 begin; delete from tab1 where a = 12; delete from tab1 where a = 13; delete from tab1 where a = 14; delete from tab1 where a = 5; delete from tab1 where a = 6; delete from tab1 where a = 7; commit; InnoDB 0 transactions not purged # check page merge happens (nothing is expected) SELECT name,count_reset FROM information_schema.innodb_metrics WHERE name like 'index_page_merge_%'; name count_reset index_page_merge_attempts 0 index_page_merge_successful 0 select PAGE_NUMBER, NUMBER_RECORDS from INFORMATION_SCHEMA.INNODB_SYS_TABLESPACES s1, INFORMATION_SCHEMA.INNODB_BUFFER_PAGE s2 where s1.SPACE = s2.SPACE AND NAME like 'test/tab1%' and PAGE_TYPE = "INDEX" order by PAGE_NUMBER, NUMBER_RECORDS; PAGE_NUMBER NUMBER_RECORDS 3 0 3 2 4 4 5 4 delete from tab1 where a = 11; InnoDB 0 transactions not purged # check page merge happens (MERGE_THRESHOLD=50 causes merge here) SELECT name,count_reset FROM information_schema.innodb_metrics WHERE name like 'index_page_merge_%'; name count_reset index_page_merge_attempts 0 index_page_merge_successful 0 delete from tab1 where a = 10; InnoDB 0 transactions not purged # check page merge happens (MERGE_THRESHOLD=35 causes merge here) SELECT name,count_reset FROM information_schema.innodb_metrics WHERE name like 'index_page_merge_%'; name count_reset index_page_merge_attempts 1 index_page_merge_successful 1 delete from tab1 where a = 9; InnoDB 0 transactions not purged # check page merge happens (MERGE_THRESHOLD=25 causes merge here) SELECT name,count_reset FROM information_schema.innodb_metrics WHERE name like 'index_page_merge_%'; name count_reset index_page_merge_attempts 2 index_page_merge_successful 2 DROP TABLE tab1; # # behavior for updating to smaller records # # test to confirm behavior (MERGE_THRESHOLD=50 (default)) CREATE TABLE tab1 (a bigint primary key, b varchar(2048)) engine=InnoDB; # check MERGE_THRESHOLD select t.NAME as TABLE_NAME, i.NAME as INDEX_NAME, i.MERGE_THRESHOLD from INFORMATION_SCHEMA.INNODB_SYS_TABLES t, INFORMATION_SCHEMA.INNODB_SYS_INDEXES i where t.TABLE_ID = i.TABLE_ID and t.NAME like 'test/tab1%'; TABLE_NAME INDEX_NAME MERGE_THRESHOLD test/tab1 PRIMARY 50 insert into tab1 values (1, repeat('a',2048)); insert into tab1 values (2, repeat('a',2048)); insert into tab1 values (3, repeat('a',2048)); insert into tab1 values (8, repeat('a',2048)); insert into tab1 values (9, repeat('a',2048)); insert into tab1 values (10, repeat('a',2048)); insert into tab1 values (11, repeat('a',2048)); insert into tab1 values (12, repeat('a',2048)); insert into tab1 values (4, repeat('a',2048)); insert into tab1 values (5, repeat('a',2048)); insert into tab1 values (6, repeat('a',2048)); insert into tab1 values (7, repeat('a',2048)); insert into tab1 values (13, repeat('a',2048)); insert into tab1 values (14, repeat('a',2048)); select PAGE_NUMBER, NUMBER_RECORDS from INFORMATION_SCHEMA.INNODB_SYS_TABLESPACES s1, INFORMATION_SCHEMA.INNODB_BUFFER_PAGE s2 where s1.SPACE = s2.SPACE AND NAME like 'test/tab1%' and PAGE_TYPE = "INDEX" order by PAGE_NUMBER, NUMBER_RECORDS; PAGE_NUMBER NUMBER_RECORDS 3 2 4 7 5 7 update tab1 set b='' where a = 12; update tab1 set b='' where a = 13; update tab1 set b='' where a = 14; update tab1 set b='' where a = 5; update tab1 set b='' where a = 6; update tab1 set b='' where a = 7; # check page merge happens (nothing is expected) SELECT name,count_reset FROM information_schema.innodb_metrics WHERE name like 'index_page_merge_%'; name count_reset index_page_merge_attempts 0 index_page_merge_successful 0 select PAGE_NUMBER, NUMBER_RECORDS from INFORMATION_SCHEMA.INNODB_SYS_TABLESPACES s1, INFORMATION_SCHEMA.INNODB_BUFFER_PAGE s2 where s1.SPACE = s2.SPACE AND NAME like 'test/tab1%' and PAGE_TYPE = "INDEX" order by PAGE_NUMBER, NUMBER_RECORDS; PAGE_NUMBER NUMBER_RECORDS 3 2 4 7 5 7 update tab1 set b='' where a = 11; # check page merge happens (MERGE_THRESHOLD=50 causes merge here) SELECT name,count_reset FROM information_schema.innodb_metrics WHERE name like 'index_page_merge_%'; name count_reset index_page_merge_attempts 1 index_page_merge_successful 1 update tab1 set b='' where a = 10; # check page merge happens (MERGE_THRESHOLD=35 causes merge here) SELECT name,count_reset FROM information_schema.innodb_metrics WHERE name like 'index_page_merge_%'; name count_reset index_page_merge_attempts 1 index_page_merge_successful 1 update tab1 set b='' where a = 9; # check page merge happens (MERGE_THRESHOLD=25 causes merge here) SELECT name,count_reset FROM information_schema.innodb_metrics WHERE name like 'index_page_merge_%'; name count_reset index_page_merge_attempts 1 index_page_merge_successful 1 DROP TABLE tab1; # test to confirm behavior (MERGE_THRESHOLD=35) CREATE TABLE tab1 (a bigint primary key, b varchar(2048)) engine=InnoDB COMMENT='MERGE_THRESHOLD=35'; # check MERGE_THRESHOLD select t.NAME as TABLE_NAME, i.NAME as INDEX_NAME, i.MERGE_THRESHOLD from INFORMATION_SCHEMA.INNODB_SYS_TABLES t, INFORMATION_SCHEMA.INNODB_SYS_INDEXES i where t.TABLE_ID = i.TABLE_ID and t.NAME like 'test/tab1%'; TABLE_NAME INDEX_NAME MERGE_THRESHOLD test/tab1 PRIMARY 35 insert into tab1 values (1, repeat('a',2048)); insert into tab1 values (2, repeat('a',2048)); insert into tab1 values (3, repeat('a',2048)); insert into tab1 values (8, repeat('a',2048)); insert into tab1 values (9, repeat('a',2048)); insert into tab1 values (10, repeat('a',2048)); insert into tab1 values (11, repeat('a',2048)); insert into tab1 values (12, repeat('a',2048)); insert into tab1 values (4, repeat('a',2048)); insert into tab1 values (5, repeat('a',2048)); insert into tab1 values (6, repeat('a',2048)); insert into tab1 values (7, repeat('a',2048)); insert into tab1 values (13, repeat('a',2048)); insert into tab1 values (14, repeat('a',2048)); select PAGE_NUMBER, NUMBER_RECORDS from INFORMATION_SCHEMA.INNODB_SYS_TABLESPACES s1, INFORMATION_SCHEMA.INNODB_BUFFER_PAGE s2 where s1.SPACE = s2.SPACE AND NAME like 'test/tab1%' and PAGE_TYPE = "INDEX" order by PAGE_NUMBER, NUMBER_RECORDS; PAGE_NUMBER NUMBER_RECORDS 3 2 4 7 5 7 update tab1 set b='' where a = 12; update tab1 set b='' where a = 13; update tab1 set b='' where a = 14; update tab1 set b='' where a = 5; update tab1 set b='' where a = 6; update tab1 set b='' where a = 7; # check page merge happens (nothing is expected) SELECT name,count_reset FROM information_schema.innodb_metrics WHERE name like 'index_page_merge_%'; name count_reset index_page_merge_attempts 0 index_page_merge_successful 0 select PAGE_NUMBER, NUMBER_RECORDS from INFORMATION_SCHEMA.INNODB_SYS_TABLESPACES s1, INFORMATION_SCHEMA.INNODB_BUFFER_PAGE s2 where s1.SPACE = s2.SPACE AND NAME like 'test/tab1%' and PAGE_TYPE = "INDEX" order by PAGE_NUMBER, NUMBER_RECORDS; PAGE_NUMBER NUMBER_RECORDS 3 2 4 7 5 7 update tab1 set b='' where a = 11; # check page merge happens (MERGE_THRESHOLD=50 causes merge here) SELECT name,count_reset FROM information_schema.innodb_metrics WHERE name like 'index_page_merge_%'; name count_reset index_page_merge_attempts 0 index_page_merge_successful 0 update tab1 set b='' where a = 10; # check page merge happens (MERGE_THRESHOLD=35 causes merge here) SELECT name,count_reset FROM information_schema.innodb_metrics WHERE name like 'index_page_merge_%'; name count_reset index_page_merge_attempts 1 index_page_merge_successful 1 update tab1 set b='' where a = 9; # check page merge happens (MERGE_THRESHOLD=25 causes merge here) SELECT name,count_reset FROM information_schema.innodb_metrics WHERE name like 'index_page_merge_%'; name count_reset index_page_merge_attempts 1 index_page_merge_successful 1 DROP TABLE tab1; # test to confirm behavior (MERGE_THRESHOLD=25) CREATE TABLE tab1 (a bigint primary key, b varchar(2048)) engine=InnoDB COMMENT='MERGE_THRESHOLD=25'; # check MERGE_THRESHOLD select t.NAME as TABLE_NAME, i.NAME as INDEX_NAME, i.MERGE_THRESHOLD from INFORMATION_SCHEMA.INNODB_SYS_TABLES t, INFORMATION_SCHEMA.INNODB_SYS_INDEXES i where t.TABLE_ID = i.TABLE_ID and t.NAME like 'test/tab1%'; TABLE_NAME INDEX_NAME MERGE_THRESHOLD test/tab1 PRIMARY 25 insert into tab1 values (1, repeat('a',2048)); insert into tab1 values (2, repeat('a',2048)); insert into tab1 values (3, repeat('a',2048)); insert into tab1 values (8, repeat('a',2048)); insert into tab1 values (9, repeat('a',2048)); insert into tab1 values (10, repeat('a',2048)); insert into tab1 values (11, repeat('a',2048)); insert into tab1 values (12, repeat('a',2048)); insert into tab1 values (4, repeat('a',2048)); insert into tab1 values (5, repeat('a',2048)); insert into tab1 values (6, repeat('a',2048)); insert into tab1 values (7, repeat('a',2048)); insert into tab1 values (13, repeat('a',2048)); insert into tab1 values (14, repeat('a',2048)); select PAGE_NUMBER, NUMBER_RECORDS from INFORMATION_SCHEMA.INNODB_SYS_TABLESPACES s1, INFORMATION_SCHEMA.INNODB_BUFFER_PAGE s2 where s1.SPACE = s2.SPACE AND NAME like 'test/tab1%' and PAGE_TYPE = "INDEX" order by PAGE_NUMBER, NUMBER_RECORDS; PAGE_NUMBER NUMBER_RECORDS 3 2 4 7 5 7 update tab1 set b='' where a = 12; update tab1 set b='' where a = 13; update tab1 set b='' where a = 14; update tab1 set b='' where a = 5; update tab1 set b='' where a = 6; update tab1 set b='' where a = 7; # check page merge happens (nothing is expected) SELECT name,count_reset FROM information_schema.innodb_metrics WHERE name like 'index_page_merge_%'; name count_reset index_page_merge_attempts 0 index_page_merge_successful 0 select PAGE_NUMBER, NUMBER_RECORDS from INFORMATION_SCHEMA.INNODB_SYS_TABLESPACES s1, INFORMATION_SCHEMA.INNODB_BUFFER_PAGE s2 where s1.SPACE = s2.SPACE AND NAME like 'test/tab1%' and PAGE_TYPE = "INDEX" order by PAGE_NUMBER, NUMBER_RECORDS; PAGE_NUMBER NUMBER_RECORDS 3 2 4 7 5 7 update tab1 set b='' where a = 11; # check page merge happens (MERGE_THRESHOLD=50 causes merge here) SELECT name,count_reset FROM information_schema.innodb_metrics WHERE name like 'index_page_merge_%'; name count_reset index_page_merge_attempts 0 index_page_merge_successful 0 update tab1 set b='' where a = 10; # check page merge happens (MERGE_THRESHOLD=35 causes merge here) SELECT name,count_reset FROM information_schema.innodb_metrics WHERE name like 'index_page_merge_%'; name count_reset index_page_merge_attempts 0 index_page_merge_successful 0 update tab1 set b='' where a = 9; # check page merge happens (MERGE_THRESHOLD=25 causes merge here) SELECT name,count_reset FROM information_schema.innodb_metrics WHERE name like 'index_page_merge_%'; name count_reset index_page_merge_attempts 1 index_page_merge_successful 1 DROP TABLE tab1; # test to confirm explicit temporary table (MERGE_THRESHOLD=35) # (though not registered to SYS_TABLES,SYS_INDEXES, it works correctly) CREATE TEMPORARY TABLE tab1 (a bigint primary key, b varchar(2048)) engine=InnoDB COMMENT='MERGE_THRESHOLD=35'; # check MERGE_THRESHOLD select t.NAME as TABLE_NAME, i.NAME as INDEX_NAME, i.MERGE_THRESHOLD from INFORMATION_SCHEMA.INNODB_SYS_TABLES t, INFORMATION_SCHEMA.INNODB_SYS_INDEXES i where t.TABLE_ID = i.TABLE_ID and t.NAME like 'test/tab1%'; TABLE_NAME INDEX_NAME MERGE_THRESHOLD insert into tab1 values (1, repeat('a',2048)); insert into tab1 values (2, repeat('a',2048)); insert into tab1 values (3, repeat('a',2048)); insert into tab1 values (8, repeat('a',2048)); insert into tab1 values (9, repeat('a',2048)); insert into tab1 values (10, repeat('a',2048)); insert into tab1 values (11, repeat('a',2048)); insert into tab1 values (12, repeat('a',2048)); insert into tab1 values (4, repeat('a',2048)); insert into tab1 values (5, repeat('a',2048)); insert into tab1 values (6, repeat('a',2048)); insert into tab1 values (7, repeat('a',2048)); insert into tab1 values (13, repeat('a',2048)); insert into tab1 values (14, repeat('a',2048)); select PAGE_NUMBER, NUMBER_RECORDS from INFORMATION_SCHEMA.INNODB_SYS_TABLESPACES s1, INFORMATION_SCHEMA.INNODB_BUFFER_PAGE s2 where s1.SPACE = s2.SPACE AND NAME like 'test/tab1%' and PAGE_TYPE = "INDEX" order by PAGE_NUMBER, NUMBER_RECORDS; PAGE_NUMBER NUMBER_RECORDS update tab1 set b='' where a = 12; update tab1 set b='' where a = 13; update tab1 set b='' where a = 14; update tab1 set b='' where a = 5; update tab1 set b='' where a = 6; update tab1 set b='' where a = 7; # check page merge happens (nothing is expected) SELECT name,count_reset FROM information_schema.innodb_metrics WHERE name like 'index_page_merge_%'; name count_reset index_page_merge_attempts 0 index_page_merge_successful 0 select PAGE_NUMBER, NUMBER_RECORDS from INFORMATION_SCHEMA.INNODB_SYS_TABLESPACES s1, INFORMATION_SCHEMA.INNODB_BUFFER_PAGE s2 where s1.SPACE = s2.SPACE AND NAME like 'test/tab1%' and PAGE_TYPE = "INDEX" order by PAGE_NUMBER, NUMBER_RECORDS; PAGE_NUMBER NUMBER_RECORDS update tab1 set b='' where a = 11; # check page merge happens (MERGE_THRESHOLD=50 causes merge here) SELECT name,count_reset FROM information_schema.innodb_metrics WHERE name like 'index_page_merge_%'; name count_reset index_page_merge_attempts 0 index_page_merge_successful 0 update tab1 set b='' where a = 10; # check page merge happens (MERGE_THRESHOLD=35 causes merge here) SELECT name,count_reset FROM information_schema.innodb_metrics WHERE name like 'index_page_merge_%'; name count_reset index_page_merge_attempts 1 index_page_merge_successful 1 update tab1 set b='' where a = 9; # check page merge happens (MERGE_THRESHOLD=25 causes merge here) SELECT name,count_reset FROM information_schema.innodb_metrics WHERE name like 'index_page_merge_%'; name count_reset index_page_merge_attempts 1 index_page_merge_successful 1 DROP TABLE tab1; # # behavior for secondary index with blob # # test to confirm behavior (MERGE_THRESHOLD=50 (default)) CREATE TABLE tab1 (a bigint primary key, b blob) engine=InnoDB row_format=dynamic; CREATE INDEX index1 ON tab1(b(750)); # check MERGE_THRESHOLD select t.NAME as TABLE_NAME, i.NAME as INDEX_NAME, i.MERGE_THRESHOLD from INFORMATION_SCHEMA.INNODB_SYS_TABLES t, INFORMATION_SCHEMA.INNODB_SYS_INDEXES i where t.TABLE_ID = i.TABLE_ID and t.NAME like 'test/tab1%'; TABLE_NAME INDEX_NAME MERGE_THRESHOLD test/tab1 PRIMARY 50 test/tab1 index1 50 INSERT INTO tab1 VALUES (1, concat("01", repeat('a',8190))); INSERT INTO tab1 VALUES (2, concat("02", repeat('a',8190))); INSERT INTO tab1 VALUES (3, concat("03", repeat('a',8190))); INSERT INTO tab1 VALUES (4, concat("04", repeat('a',8190))); INSERT INTO tab1 VALUES (5, concat("05", repeat('a',8190))); INSERT INTO tab1 VALUES (6, concat("06", repeat('a',8190))); INSERT INTO tab1 VALUES (7, concat("07", repeat('a',8190))); INSERT INTO tab1 VALUES (8, concat("08", repeat('a',8190))); INSERT INTO tab1 VALUES (9, concat("09", repeat('a',8190))); INSERT INTO tab1 VALUES (10, concat("10", repeat('a',8190))); INSERT INTO tab1 VALUES (22, concat("22", repeat('a',8190))); INSERT INTO tab1 VALUES (23, concat("23", repeat('a',8190))); INSERT INTO tab1 VALUES (24, concat("24", repeat('a',8190))); INSERT INTO tab1 VALUES (25, concat("25", repeat('a',8190))); INSERT INTO tab1 VALUES (26, concat("26", repeat('a',8190))); INSERT INTO tab1 VALUES (27, concat("27", repeat('a',8190))); INSERT INTO tab1 VALUES (28, concat("28", repeat('a',8190))); INSERT INTO tab1 VALUES (29, concat("29", repeat('a',8190))); INSERT INTO tab1 VALUES (30, concat("30", repeat('a',8190))); INSERT INTO tab1 VALUES (31, concat("31", repeat('a',8190))); INSERT INTO tab1 VALUES (32, concat("32", repeat('a',8190))); INSERT INTO tab1 VALUES (33, concat("33", repeat('a',8190))); INSERT INTO tab1 VALUES (11, concat("11", repeat('a',8190))); INSERT INTO tab1 VALUES (12, concat("12", repeat('a',8190))); INSERT INTO tab1 VALUES (13, concat("13", repeat('a',8190))); INSERT INTO tab1 VALUES (14, concat("14", repeat('a',8190))); INSERT INTO tab1 VALUES (15, concat("15", repeat('a',8190))); INSERT INTO tab1 VALUES (16, concat("16", repeat('a',8190))); INSERT INTO tab1 VALUES (17, concat("17", repeat('a',8190))); INSERT INTO tab1 VALUES (18, concat("18", repeat('a',8190))); INSERT INTO tab1 VALUES (19, concat("19", repeat('a',8190))); INSERT INTO tab1 VALUES (20, concat("20", repeat('a',8190))); INSERT INTO tab1 VALUES (21, concat("21", repeat('a',8190))); INSERT INTO tab1 VALUES (34, concat("34", repeat('a',8190))); INSERT INTO tab1 VALUES (35, concat("35", repeat('a',8190))); INSERT INTO tab1 VALUES (36, concat("36", repeat('a',8190))); INSERT INTO tab1 VALUES (37, concat("37", repeat('a',8190))); INSERT INTO tab1 VALUES (38, concat("38", repeat('a',8190))); INSERT INTO tab1 VALUES (39, concat("39", repeat('a',8190))); INSERT INTO tab1 VALUES (40, concat("40", repeat('a',8190))); INSERT INTO tab1 VALUES (41, concat("41", repeat('a',8190))); INSERT INTO tab1 VALUES (42, concat("42", repeat('a',8190))); select PAGE_NUMBER, NUMBER_RECORDS from INFORMATION_SCHEMA.INNODB_SYS_TABLESPACES s1, INFORMATION_SCHEMA.INNODB_BUFFER_PAGE s2 where s1.SPACE = s2.SPACE AND NAME like 'test/tab1%' and PAGE_TYPE = "INDEX" order by PAGE_NUMBER, NUMBER_RECORDS; PAGE_NUMBER NUMBER_RECORDS 3 42 4 2 27 21 28 21 begin; delete from tab1 where a = 33; delete from tab1 where a = 34; delete from tab1 where a = 35; delete from tab1 where a = 36; delete from tab1 where a = 37; delete from tab1 where a = 38; delete from tab1 where a = 39; delete from tab1 where a = 40; delete from tab1 where a = 41; delete from tab1 where a = 42; delete from tab1 where a = 12; delete from tab1 where a = 13; delete from tab1 where a = 14; delete from tab1 where a = 15; delete from tab1 where a = 16; delete from tab1 where a = 17; delete from tab1 where a = 18; delete from tab1 where a = 19; delete from tab1 where a = 20; delete from tab1 where a = 21; commit; InnoDB 0 transactions not purged # check page merge happens (nothing is expected) SELECT name,count_reset FROM information_schema.innodb_metrics WHERE name like 'index_page_merge_%'; name count_reset index_page_merge_attempts 0 index_page_merge_successful 0 select PAGE_NUMBER, NUMBER_RECORDS from INFORMATION_SCHEMA.INNODB_SYS_TABLESPACES s1, INFORMATION_SCHEMA.INNODB_BUFFER_PAGE s2 where s1.SPACE = s2.SPACE AND NAME like 'test/tab1%' and PAGE_TYPE = "INDEX" order by PAGE_NUMBER, NUMBER_RECORDS; PAGE_NUMBER NUMBER_RECORDS 3 22 4 2 27 11 28 11 delete from tab1 where a = 32; InnoDB 0 transactions not purged # check page merge happens (MERGE_THRESHOLD=50 causes merge here) SELECT name,count_reset FROM information_schema.innodb_metrics WHERE name like 'index_page_merge_%'; name count_reset index_page_merge_attempts 1 index_page_merge_successful 1 delete from tab1 where a = 31; InnoDB 0 transactions not purged # check page merge happens (MERGE_THRESHOLD=45 causes merge here) SELECT name,count_reset FROM information_schema.innodb_metrics WHERE name like 'index_page_merge_%'; name count_reset index_page_merge_attempts 2 index_page_merge_successful 2 delete from tab1 where a = 30; InnoDB 0 transactions not purged # check page merge happens (MERGE_THRESHOLD=40 causes merge here) SELECT name,count_reset FROM information_schema.innodb_metrics WHERE name like 'index_page_merge_%'; name count_reset index_page_merge_attempts 2 index_page_merge_successful 2 DROP TABLE tab1; # test to confirm behavior (MERGE_THRESHOLD=45) CREATE TABLE tab1 (a bigint primary key, b blob) engine=InnoDB row_format=dynamic; CREATE INDEX index1 ON tab1(b(750)) COMMENT 'MERGE_THRESHOLD=45'; # check MERGE_THRESHOLD select t.NAME as TABLE_NAME, i.NAME as INDEX_NAME, i.MERGE_THRESHOLD from INFORMATION_SCHEMA.INNODB_SYS_TABLES t, INFORMATION_SCHEMA.INNODB_SYS_INDEXES i where t.TABLE_ID = i.TABLE_ID and t.NAME like 'test/tab1%'; TABLE_NAME INDEX_NAME MERGE_THRESHOLD test/tab1 PRIMARY 50 test/tab1 index1 45 INSERT INTO tab1 VALUES (1, concat("01", repeat('a',8190))); INSERT INTO tab1 VALUES (2, concat("02", repeat('a',8190))); INSERT INTO tab1 VALUES (3, concat("03", repeat('a',8190))); INSERT INTO tab1 VALUES (4, concat("04", repeat('a',8190))); INSERT INTO tab1 VALUES (5, concat("05", repeat('a',8190))); INSERT INTO tab1 VALUES (6, concat("06", repeat('a',8190))); INSERT INTO tab1 VALUES (7, concat("07", repeat('a',8190))); INSERT INTO tab1 VALUES (8, concat("08", repeat('a',8190))); INSERT INTO tab1 VALUES (9, concat("09", repeat('a',8190))); INSERT INTO tab1 VALUES (10, concat("10", repeat('a',8190))); INSERT INTO tab1 VALUES (22, concat("22", repeat('a',8190))); INSERT INTO tab1 VALUES (23, concat("23", repeat('a',8190))); INSERT INTO tab1 VALUES (24, concat("24", repeat('a',8190))); INSERT INTO tab1 VALUES (25, concat("25", repeat('a',8190))); INSERT INTO tab1 VALUES (26, concat("26", repeat('a',8190))); INSERT INTO tab1 VALUES (27, concat("27", repeat('a',8190))); INSERT INTO tab1 VALUES (28, concat("28", repeat('a',8190))); INSERT INTO tab1 VALUES (29, concat("29", repeat('a',8190))); INSERT INTO tab1 VALUES (30, concat("30", repeat('a',8190))); INSERT INTO tab1 VALUES (31, concat("31", repeat('a',8190))); INSERT INTO tab1 VALUES (32, concat("32", repeat('a',8190))); INSERT INTO tab1 VALUES (33, concat("33", repeat('a',8190))); INSERT INTO tab1 VALUES (11, concat("11", repeat('a',8190))); INSERT INTO tab1 VALUES (12, concat("12", repeat('a',8190))); INSERT INTO tab1 VALUES (13, concat("13", repeat('a',8190))); INSERT INTO tab1 VALUES (14, concat("14", repeat('a',8190))); INSERT INTO tab1 VALUES (15, concat("15", repeat('a',8190))); INSERT INTO tab1 VALUES (16, concat("16", repeat('a',8190))); INSERT INTO tab1 VALUES (17, concat("17", repeat('a',8190))); INSERT INTO tab1 VALUES (18, concat("18", repeat('a',8190))); INSERT INTO tab1 VALUES (19, concat("19", repeat('a',8190))); INSERT INTO tab1 VALUES (20, concat("20", repeat('a',8190))); INSERT INTO tab1 VALUES (21, concat("21", repeat('a',8190))); INSERT INTO tab1 VALUES (34, concat("34", repeat('a',8190))); INSERT INTO tab1 VALUES (35, concat("35", repeat('a',8190))); INSERT INTO tab1 VALUES (36, concat("36", repeat('a',8190))); INSERT INTO tab1 VALUES (37, concat("37", repeat('a',8190))); INSERT INTO tab1 VALUES (38, concat("38", repeat('a',8190))); INSERT INTO tab1 VALUES (39, concat("39", repeat('a',8190))); INSERT INTO tab1 VALUES (40, concat("40", repeat('a',8190))); INSERT INTO tab1 VALUES (41, concat("41", repeat('a',8190))); INSERT INTO tab1 VALUES (42, concat("42", repeat('a',8190))); select PAGE_NUMBER, NUMBER_RECORDS from INFORMATION_SCHEMA.INNODB_SYS_TABLESPACES s1, INFORMATION_SCHEMA.INNODB_BUFFER_PAGE s2 where s1.SPACE = s2.SPACE AND NAME like 'test/tab1%' and PAGE_TYPE = "INDEX" order by PAGE_NUMBER, NUMBER_RECORDS; PAGE_NUMBER NUMBER_RECORDS 3 42 4 2 27 21 28 21 begin; delete from tab1 where a = 33; delete from tab1 where a = 34; delete from tab1 where a = 35; delete from tab1 where a = 36; delete from tab1 where a = 37; delete from tab1 where a = 38; delete from tab1 where a = 39; delete from tab1 where a = 40; delete from tab1 where a = 41; delete from tab1 where a = 42; delete from tab1 where a = 12; delete from tab1 where a = 13; delete from tab1 where a = 14; delete from tab1 where a = 15; delete from tab1 where a = 16; delete from tab1 where a = 17; delete from tab1 where a = 18; delete from tab1 where a = 19; delete from tab1 where a = 20; delete from tab1 where a = 21; commit; InnoDB 0 transactions not purged # check page merge happens (nothing is expected) SELECT name,count_reset FROM information_schema.innodb_metrics WHERE name like 'index_page_merge_%'; name count_reset index_page_merge_attempts 0 index_page_merge_successful 0 select PAGE_NUMBER, NUMBER_RECORDS from INFORMATION_SCHEMA.INNODB_SYS_TABLESPACES s1, INFORMATION_SCHEMA.INNODB_BUFFER_PAGE s2 where s1.SPACE = s2.SPACE AND NAME like 'test/tab1%' and PAGE_TYPE = "INDEX" order by PAGE_NUMBER, NUMBER_RECORDS; PAGE_NUMBER NUMBER_RECORDS 3 22 4 2 27 11 28 11 delete from tab1 where a = 32; InnoDB 0 transactions not purged # check page merge happens (MERGE_THRESHOLD=50 causes merge here) SELECT name,count_reset FROM information_schema.innodb_metrics WHERE name like 'index_page_merge_%'; name count_reset index_page_merge_attempts 0 index_page_merge_successful 0 delete from tab1 where a = 31; InnoDB 0 transactions not purged # check page merge happens (MERGE_THRESHOLD=45 causes merge here) SELECT name,count_reset FROM information_schema.innodb_metrics WHERE name like 'index_page_merge_%'; name count_reset index_page_merge_attempts 1 index_page_merge_successful 1 delete from tab1 where a = 30; InnoDB 0 transactions not purged # check page merge happens (MERGE_THRESHOLD=40 causes merge here) SELECT name,count_reset FROM information_schema.innodb_metrics WHERE name like 'index_page_merge_%'; name count_reset index_page_merge_attempts 2 index_page_merge_successful 2 DROP TABLE tab1; # test to confirm behavior (MERGE_THRESHOLD=40) CREATE TABLE tab1 (a bigint primary key, b blob) engine=InnoDB row_format=dynamic; CREATE INDEX index1 ON tab1(b(750)) COMMENT 'MERGE_THRESHOLD=40'; # check MERGE_THRESHOLD select t.NAME as TABLE_NAME, i.NAME as INDEX_NAME, i.MERGE_THRESHOLD from INFORMATION_SCHEMA.INNODB_SYS_TABLES t, INFORMATION_SCHEMA.INNODB_SYS_INDEXES i where t.TABLE_ID = i.TABLE_ID and t.NAME like 'test/tab1%'; TABLE_NAME INDEX_NAME MERGE_THRESHOLD test/tab1 PRIMARY 50 test/tab1 index1 40 INSERT INTO tab1 VALUES (1, concat("01", repeat('a',8190))); INSERT INTO tab1 VALUES (2, concat("02", repeat('a',8190))); INSERT INTO tab1 VALUES (3, concat("03", repeat('a',8190))); INSERT INTO tab1 VALUES (4, concat("04", repeat('a',8190))); INSERT INTO tab1 VALUES (5, concat("05", repeat('a',8190))); INSERT INTO tab1 VALUES (6, concat("06", repeat('a',8190))); INSERT INTO tab1 VALUES (7, concat("07", repeat('a',8190))); INSERT INTO tab1 VALUES (8, concat("08", repeat('a',8190))); INSERT INTO tab1 VALUES (9, concat("09", repeat('a',8190))); INSERT INTO tab1 VALUES (10, concat("10", repeat('a',8190))); INSERT INTO tab1 VALUES (22, concat("22", repeat('a',8190))); INSERT INTO tab1 VALUES (23, concat("23", repeat('a',8190))); INSERT INTO tab1 VALUES (24, concat("24", repeat('a',8190))); INSERT INTO tab1 VALUES (25, concat("25", repeat('a',8190))); INSERT INTO tab1 VALUES (26, concat("26", repeat('a',8190))); INSERT INTO tab1 VALUES (27, concat("27", repeat('a',8190))); INSERT INTO tab1 VALUES (28, concat("28", repeat('a',8190))); INSERT INTO tab1 VALUES (29, concat("29", repeat('a',8190))); INSERT INTO tab1 VALUES (30, concat("30", repeat('a',8190))); INSERT INTO tab1 VALUES (31, concat("31", repeat('a',8190))); INSERT INTO tab1 VALUES (32, concat("32", repeat('a',8190))); INSERT INTO tab1 VALUES (33, concat("33", repeat('a',8190))); INSERT INTO tab1 VALUES (11, concat("11", repeat('a',8190))); INSERT INTO tab1 VALUES (12, concat("12", repeat('a',8190))); INSERT INTO tab1 VALUES (13, concat("13", repeat('a',8190))); INSERT INTO tab1 VALUES (14, concat("14", repeat('a',8190))); INSERT INTO tab1 VALUES (15, concat("15", repeat('a',8190))); INSERT INTO tab1 VALUES (16, concat("16", repeat('a',8190))); INSERT INTO tab1 VALUES (17, concat("17", repeat('a',8190))); INSERT INTO tab1 VALUES (18, concat("18", repeat('a',8190))); INSERT INTO tab1 VALUES (19, concat("19", repeat('a',8190))); INSERT INTO tab1 VALUES (20, concat("20", repeat('a',8190))); INSERT INTO tab1 VALUES (21, concat("21", repeat('a',8190))); INSERT INTO tab1 VALUES (34, concat("34", repeat('a',8190))); INSERT INTO tab1 VALUES (35, concat("35", repeat('a',8190))); INSERT INTO tab1 VALUES (36, concat("36", repeat('a',8190))); INSERT INTO tab1 VALUES (37, concat("37", repeat('a',8190))); INSERT INTO tab1 VALUES (38, concat("38", repeat('a',8190))); INSERT INTO tab1 VALUES (39, concat("39", repeat('a',8190))); INSERT INTO tab1 VALUES (40, concat("40", repeat('a',8190))); INSERT INTO tab1 VALUES (41, concat("41", repeat('a',8190))); INSERT INTO tab1 VALUES (42, concat("42", repeat('a',8190))); select PAGE_NUMBER, NUMBER_RECORDS from INFORMATION_SCHEMA.INNODB_SYS_TABLESPACES s1, INFORMATION_SCHEMA.INNODB_BUFFER_PAGE s2 where s1.SPACE = s2.SPACE AND NAME like 'test/tab1%' and PAGE_TYPE = "INDEX" order by PAGE_NUMBER, NUMBER_RECORDS; PAGE_NUMBER NUMBER_RECORDS 3 42 4 2 27 21 28 21 begin; delete from tab1 where a = 33; delete from tab1 where a = 34; delete from tab1 where a = 35; delete from tab1 where a = 36; delete from tab1 where a = 37; delete from tab1 where a = 38; delete from tab1 where a = 39; delete from tab1 where a = 40; delete from tab1 where a = 41; delete from tab1 where a = 42; delete from tab1 where a = 12; delete from tab1 where a = 13; delete from tab1 where a = 14; delete from tab1 where a = 15; delete from tab1 where a = 16; delete from tab1 where a = 17; delete from tab1 where a = 18; delete from tab1 where a = 19; delete from tab1 where a = 20; delete from tab1 where a = 21; commit; InnoDB 0 transactions not purged # check page merge happens (nothing is expected) SELECT name,count_reset FROM information_schema.innodb_metrics WHERE name like 'index_page_merge_%'; name count_reset index_page_merge_attempts 0 index_page_merge_successful 0 select PAGE_NUMBER, NUMBER_RECORDS from INFORMATION_SCHEMA.INNODB_SYS_TABLESPACES s1, INFORMATION_SCHEMA.INNODB_BUFFER_PAGE s2 where s1.SPACE = s2.SPACE AND NAME like 'test/tab1%' and PAGE_TYPE = "INDEX" order by PAGE_NUMBER, NUMBER_RECORDS; PAGE_NUMBER NUMBER_RECORDS 3 22 4 2 27 11 28 11 delete from tab1 where a = 32; InnoDB 0 transactions not purged # check page merge happens (MERGE_THRESHOLD=50 causes merge here) SELECT name,count_reset FROM information_schema.innodb_metrics WHERE name like 'index_page_merge_%'; name count_reset index_page_merge_attempts 0 index_page_merge_successful 0 delete from tab1 where a = 31; InnoDB 0 transactions not purged # check page merge happens (MERGE_THRESHOLD=45 causes merge here) SELECT name,count_reset FROM information_schema.innodb_metrics WHERE name like 'index_page_merge_%'; name count_reset index_page_merge_attempts 0 index_page_merge_successful 0 delete from tab1 where a = 30; InnoDB 0 transactions not purged # check page merge happens (MERGE_THRESHOLD=40 causes merge here) SELECT name,count_reset FROM information_schema.innodb_metrics WHERE name like 'index_page_merge_%'; name count_reset index_page_merge_attempts 1 index_page_merge_successful 1 DROP TABLE tab1; # compressed table behaves same (MERGE_THRESHOLD=45) CREATE TABLE tab1 (a bigint primary key, b blob) engine=InnoDB ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8; CREATE INDEX index1 ON tab1(b(750)) COMMENT 'MERGE_THRESHOLD=45'; # check MERGE_THRESHOLD select t.NAME as TABLE_NAME, i.NAME as INDEX_NAME, i.MERGE_THRESHOLD from INFORMATION_SCHEMA.INNODB_SYS_TABLES t, INFORMATION_SCHEMA.INNODB_SYS_INDEXES i where t.TABLE_ID = i.TABLE_ID and t.NAME like 'test/tab1%'; TABLE_NAME INDEX_NAME MERGE_THRESHOLD test/tab1 PRIMARY 50 test/tab1 index1 45 INSERT INTO tab1 VALUES (1, concat("01", repeat('a',8190))); INSERT INTO tab1 VALUES (2, concat("02", repeat('a',8190))); INSERT INTO tab1 VALUES (3, concat("03", repeat('a',8190))); INSERT INTO tab1 VALUES (4, concat("04", repeat('a',8190))); INSERT INTO tab1 VALUES (5, concat("05", repeat('a',8190))); INSERT INTO tab1 VALUES (6, concat("06", repeat('a',8190))); INSERT INTO tab1 VALUES (7, concat("07", repeat('a',8190))); INSERT INTO tab1 VALUES (8, concat("08", repeat('a',8190))); INSERT INTO tab1 VALUES (9, concat("09", repeat('a',8190))); INSERT INTO tab1 VALUES (10, concat("10", repeat('a',8190))); INSERT INTO tab1 VALUES (22, concat("22", repeat('a',8190))); INSERT INTO tab1 VALUES (23, concat("23", repeat('a',8190))); INSERT INTO tab1 VALUES (24, concat("24", repeat('a',8190))); INSERT INTO tab1 VALUES (25, concat("25", repeat('a',8190))); INSERT INTO tab1 VALUES (26, concat("26", repeat('a',8190))); INSERT INTO tab1 VALUES (27, concat("27", repeat('a',8190))); INSERT INTO tab1 VALUES (28, concat("28", repeat('a',8190))); INSERT INTO tab1 VALUES (29, concat("29", repeat('a',8190))); INSERT INTO tab1 VALUES (30, concat("30", repeat('a',8190))); INSERT INTO tab1 VALUES (31, concat("31", repeat('a',8190))); INSERT INTO tab1 VALUES (32, concat("32", repeat('a',8190))); INSERT INTO tab1 VALUES (33, concat("33", repeat('a',8190))); INSERT INTO tab1 VALUES (11, concat("11", repeat('a',8190))); INSERT INTO tab1 VALUES (12, concat("12", repeat('a',8190))); INSERT INTO tab1 VALUES (13, concat("13", repeat('a',8190))); INSERT INTO tab1 VALUES (14, concat("14", repeat('a',8190))); INSERT INTO tab1 VALUES (15, concat("15", repeat('a',8190))); INSERT INTO tab1 VALUES (16, concat("16", repeat('a',8190))); INSERT INTO tab1 VALUES (17, concat("17", repeat('a',8190))); INSERT INTO tab1 VALUES (18, concat("18", repeat('a',8190))); INSERT INTO tab1 VALUES (19, concat("19", repeat('a',8190))); INSERT INTO tab1 VALUES (20, concat("20", repeat('a',8190))); INSERT INTO tab1 VALUES (21, concat("21", repeat('a',8190))); INSERT INTO tab1 VALUES (34, concat("34", repeat('a',8190))); INSERT INTO tab1 VALUES (35, concat("35", repeat('a',8190))); INSERT INTO tab1 VALUES (36, concat("36", repeat('a',8190))); INSERT INTO tab1 VALUES (37, concat("37", repeat('a',8190))); INSERT INTO tab1 VALUES (38, concat("38", repeat('a',8190))); INSERT INTO tab1 VALUES (39, concat("39", repeat('a',8190))); INSERT INTO tab1 VALUES (40, concat("40", repeat('a',8190))); INSERT INTO tab1 VALUES (41, concat("41", repeat('a',8190))); INSERT INTO tab1 VALUES (42, concat("42", repeat('a',8190))); select PAGE_NUMBER, NUMBER_RECORDS from INFORMATION_SCHEMA.INNODB_SYS_TABLESPACES s1, INFORMATION_SCHEMA.INNODB_BUFFER_PAGE s2 where s1.SPACE = s2.SPACE AND NAME like 'test/tab1%' and PAGE_TYPE = "INDEX" order by PAGE_NUMBER, NUMBER_RECORDS; PAGE_NUMBER NUMBER_RECORDS 3 42 4 2 27 21 28 21 begin; delete from tab1 where a = 33; delete from tab1 where a = 34; delete from tab1 where a = 35; delete from tab1 where a = 36; delete from tab1 where a = 37; delete from tab1 where a = 38; delete from tab1 where a = 39; delete from tab1 where a = 40; delete from tab1 where a = 41; delete from tab1 where a = 42; delete from tab1 where a = 12; delete from tab1 where a = 13; delete from tab1 where a = 14; delete from tab1 where a = 15; delete from tab1 where a = 16; delete from tab1 where a = 17; delete from tab1 where a = 18; delete from tab1 where a = 19; delete from tab1 where a = 20; delete from tab1 where a = 21; commit; InnoDB 0 transactions not purged # check page merge happens (nothing is expected) SELECT name,count_reset FROM information_schema.innodb_metrics WHERE name like 'index_page_merge_%'; name count_reset index_page_merge_attempts 0 index_page_merge_successful 0 select PAGE_NUMBER, NUMBER_RECORDS from INFORMATION_SCHEMA.INNODB_SYS_TABLESPACES s1, INFORMATION_SCHEMA.INNODB_BUFFER_PAGE s2 where s1.SPACE = s2.SPACE AND NAME like 'test/tab1%' and PAGE_TYPE = "INDEX" order by PAGE_NUMBER, NUMBER_RECORDS; PAGE_NUMBER NUMBER_RECORDS 3 22 4 2 27 11 28 11 delete from tab1 where a = 32; InnoDB 0 transactions not purged # check page merge happens (MERGE_THRESHOLD=50 causes merge here) SELECT name,count_reset FROM information_schema.innodb_metrics WHERE name like 'index_page_merge_%'; name count_reset index_page_merge_attempts 0 index_page_merge_successful 0 delete from tab1 where a = 31; InnoDB 0 transactions not purged # check page merge happens (MERGE_THRESHOLD=45 causes merge here) SELECT name,count_reset FROM information_schema.innodb_metrics WHERE name like 'index_page_merge_%'; name count_reset index_page_merge_attempts 1 index_page_merge_successful 1 delete from tab1 where a = 30; InnoDB 0 transactions not purged # check page merge happens (MERGE_THRESHOLD=40 causes merge here) SELECT name,count_reset FROM information_schema.innodb_metrics WHERE name like 'index_page_merge_%'; name count_reset index_page_merge_attempts 2 index_page_merge_successful 2 DROP TABLE tab1; SET GLOBAL innodb_stats_persistent = @save_stats_persistent;