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 | 280 |
1 files changed, 129 insertions, 151 deletions
diff --git a/mysql-test/suite/innodb/r/index_merge_threshold.result b/mysql-test/suite/innodb/r/index_merge_threshold.result index b8a6d81aecc..35cb82e6f1e 100644 --- a/mysql-test/suite/innodb/r/index_merge_threshold.result +++ b/mysql-test/suite/innodb/r/index_merge_threshold.result @@ -204,10 +204,9 @@ 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 3 +3 2 4 7 -5 3 -6 4 +5 7 begin; delete from tab1 where a = 12; delete from tab1 where a = 13; @@ -221,8 +220,8 @@ InnoDB 0 transactions not purged 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 +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 @@ -231,32 +230,31 @@ and PAGE_TYPE = "INDEX" order by PAGE_NUMBER, NUMBER_RECORDS; PAGE_NUMBER NUMBER_RECORDS 3 2 4 4 -5 2 -6 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 2 -index_page_merge_successful 2 +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 3 -index_page_merge_successful 3 +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 3 -index_page_merge_successful 3 +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 @@ -287,10 +285,9 @@ 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 3 +3 2 4 7 -5 3 -6 4 +5 7 begin; delete from tab1 where a = 12; delete from tab1 where a = 13; @@ -304,8 +301,8 @@ InnoDB 0 transactions not purged 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 +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 @@ -314,32 +311,31 @@ and PAGE_TYPE = "INDEX" order by PAGE_NUMBER, NUMBER_RECORDS; PAGE_NUMBER NUMBER_RECORDS 3 2 4 4 -5 2 -6 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 +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 2 -index_page_merge_successful 2 +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 3 -index_page_merge_successful 3 +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 @@ -370,10 +366,9 @@ 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 3 +3 2 4 7 -5 3 -6 4 +5 7 begin; delete from tab1 where a = 12; delete from tab1 where a = 13; @@ -387,8 +382,8 @@ InnoDB 0 transactions not purged 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 +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 @@ -397,32 +392,31 @@ and PAGE_TYPE = "INDEX" order by PAGE_NUMBER, NUMBER_RECORDS; PAGE_NUMBER NUMBER_RECORDS 3 2 4 4 -5 1 -6 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 +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 +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 2 -index_page_merge_successful 2 +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)) @@ -458,10 +452,9 @@ 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 3 +3 2 4 7 -5 3 -6 4 +5 7 begin; delete from tab1 where a = 12; delete from tab1 where a = 13; @@ -475,8 +468,8 @@ InnoDB 0 transactions not purged 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 +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 @@ -486,32 +479,31 @@ PAGE_NUMBER NUMBER_RECORDS 3 0 3 2 4 4 -5 2 -6 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 +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 2 -index_page_merge_successful 2 +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 3 -index_page_merge_successful 3 +index_page_merge_attempts 2 +index_page_merge_successful 2 DROP TABLE tab1; # # behavior for updating to smaller records @@ -544,10 +536,9 @@ 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 3 +3 2 4 7 -5 3 -6 4 +5 7 update tab1 set b='' where a = 12; update tab1 set b='' where a = 13; update tab1 set b='' where a = 14; @@ -558,8 +549,8 @@ update tab1 set b='' where a = 7; 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 +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 @@ -568,29 +559,28 @@ and PAGE_TYPE = "INDEX" order by PAGE_NUMBER, NUMBER_RECORDS; PAGE_NUMBER NUMBER_RECORDS 3 2 4 7 -5 3 -6 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 2 -index_page_merge_successful 2 +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 2 -index_page_merge_successful 2 +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 2 -index_page_merge_successful 2 +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 @@ -621,10 +611,9 @@ 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 3 +3 2 4 7 -5 3 -6 4 +5 7 update tab1 set b='' where a = 12; update tab1 set b='' where a = 13; update tab1 set b='' where a = 14; @@ -635,8 +624,8 @@ update tab1 set b='' where a = 7; 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 +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 @@ -645,29 +634,28 @@ and PAGE_TYPE = "INDEX" order by PAGE_NUMBER, NUMBER_RECORDS; PAGE_NUMBER NUMBER_RECORDS 3 2 4 7 -5 3 -6 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 +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 2 -index_page_merge_successful 2 +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 2 -index_page_merge_successful 2 +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 @@ -698,10 +686,9 @@ 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 3 +3 2 4 7 -5 3 -6 4 +5 7 update tab1 set b='' where a = 12; update tab1 set b='' where a = 13; update tab1 set b='' where a = 14; @@ -712,8 +699,8 @@ update tab1 set b='' where a = 7; 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 +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 @@ -722,29 +709,28 @@ and PAGE_TYPE = "INDEX" order by PAGE_NUMBER, NUMBER_RECORDS; PAGE_NUMBER NUMBER_RECORDS 3 2 4 7 -5 3 -6 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 +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 +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 2 -index_page_merge_successful 2 +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) @@ -785,8 +771,8 @@ update tab1 set b='' where a = 7; 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 +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 @@ -798,22 +784,22 @@ update tab1 set b='' where a = 11; 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 +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 2 -index_page_merge_successful 2 +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 2 -index_page_merge_successful 2 +index_page_merge_attempts 1 +index_page_merge_successful 1 DROP TABLE tab1; # # behavior for secondary index with blob @@ -877,10 +863,9 @@ 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 3 +4 2 27 21 -28 10 -30 11 +28 21 begin; delete from tab1 where a = 33; delete from tab1 where a = 34; @@ -908,8 +893,8 @@ InnoDB 0 transactions not purged 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 +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 @@ -919,32 +904,31 @@ PAGE_NUMBER NUMBER_RECORDS 3 22 4 2 27 11 -28 9 -30 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 2 -index_page_merge_successful 2 +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 3 -index_page_merge_successful 3 +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 3 -index_page_merge_successful 3 +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; @@ -1005,10 +989,9 @@ 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 3 +4 2 27 21 -28 10 -30 11 +28 21 begin; delete from tab1 where a = 33; delete from tab1 where a = 34; @@ -1036,8 +1019,8 @@ InnoDB 0 transactions not purged 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 +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 @@ -1047,32 +1030,31 @@ PAGE_NUMBER NUMBER_RECORDS 3 22 4 2 27 11 -28 9 -30 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 +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 2 -index_page_merge_successful 2 +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 3 -index_page_merge_successful 3 +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; @@ -1133,10 +1115,9 @@ 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 3 +4 2 27 21 -28 10 -30 11 +28 21 begin; delete from tab1 where a = 33; delete from tab1 where a = 34; @@ -1164,8 +1145,8 @@ InnoDB 0 transactions not purged 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 +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 @@ -1175,32 +1156,31 @@ PAGE_NUMBER NUMBER_RECORDS 3 22 4 2 27 11 -28 8 -30 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 +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 +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 2 -index_page_merge_successful 2 +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 @@ -1262,10 +1242,9 @@ 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 3 +4 2 27 21 -28 10 -30 11 +28 21 begin; delete from tab1 where a = 33; delete from tab1 where a = 34; @@ -1293,8 +1272,8 @@ InnoDB 0 transactions not purged 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 +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 @@ -1304,31 +1283,30 @@ PAGE_NUMBER NUMBER_RECORDS 3 22 4 2 27 11 -28 9 -30 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 +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 2 -index_page_merge_successful 2 +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 3 -index_page_merge_successful 3 +index_page_merge_attempts 2 +index_page_merge_successful 2 DROP TABLE tab1; SET GLOBAL innodb_purge_rseg_truncate_frequency = @saved_frequency; |