diff options
Diffstat (limited to 'mysql-test/suite/innodb/r/index_merge_threshold.result')
-rw-r--r-- | mysql-test/suite/innodb/r/index_merge_threshold.result | 1312 |
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; |