summaryrefslogtreecommitdiffstats
path: root/mysql-test/suite/innodb/r/index_merge_threshold.result
diff options
context:
space:
mode:
Diffstat (limited to 'mysql-test/suite/innodb/r/index_merge_threshold.result')
-rw-r--r--mysql-test/suite/innodb/r/index_merge_threshold.result1312
1 files changed, 1312 insertions, 0 deletions
diff --git a/mysql-test/suite/innodb/r/index_merge_threshold.result b/mysql-test/suite/innodb/r/index_merge_threshold.result
new file mode 100644
index 00000000..35cb82e6
--- /dev/null
+++ b/mysql-test/suite/innodb/r/index_merge_threshold.result
@@ -0,0 +1,1312 @@
+SET @saved_frequency = @@GLOBAL.innodb_purge_rseg_truncate_frequency;
+SET GLOBAL innodb_purge_rseg_truncate_frequency = 1;
+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
+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 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 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 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_purge_rseg_truncate_frequency = @saved_frequency;