1# 2# Test to cause merge of the pages (at secondary index by deleting) 3# test/tab1 should be created already with innodb_file_per_table=ON 4# The definition is intended to be based on 5# "create table tab1 (a bigint primary key, b blob) engine=InnoDB row_format=dynamic;" 6# "create index index1 on tab1(b(750));" 7# 8 9--source include/have_innodb.inc 10--source include/have_debug.inc 11--source include/have_innodb_16k.inc 12 13# turn on flags 14--disable_query_log 15SET GLOBAL innodb_monitor_enable=index_page_merge_attempts; 16SET GLOBAL innodb_monitor_reset=index_page_merge_attempts; 17SET GLOBAL innodb_monitor_enable=index_page_merge_successful; 18SET GLOBAL innodb_monitor_reset=index_page_merge_successful; 19--enable_query_log 20 21--echo # check MERGE_THRESHOLD 22select t.NAME as TABLE_NAME, i.NAME as INDEX_NAME, i.MERGE_THRESHOLD 23from INFORMATION_SCHEMA.INNODB_SYS_TABLES t, INFORMATION_SCHEMA.INNODB_SYS_INDEXES i 24where t.TABLE_ID = i.TABLE_ID and t.NAME like 'test/tab1%'; 25 26INSERT INTO tab1 VALUES (1, concat("01", repeat('a',8190))); 27INSERT INTO tab1 VALUES (2, concat("02", repeat('a',8190))); 28INSERT INTO tab1 VALUES (3, concat("03", repeat('a',8190))); 29INSERT INTO tab1 VALUES (4, concat("04", repeat('a',8190))); 30INSERT INTO tab1 VALUES (5, concat("05", repeat('a',8190))); 31INSERT INTO tab1 VALUES (6, concat("06", repeat('a',8190))); 32INSERT INTO tab1 VALUES (7, concat("07", repeat('a',8190))); 33INSERT INTO tab1 VALUES (8, concat("08", repeat('a',8190))); 34INSERT INTO tab1 VALUES (9, concat("09", repeat('a',8190))); 35INSERT INTO tab1 VALUES (10, concat("10", repeat('a',8190))); 36 37INSERT INTO tab1 VALUES (22, concat("22", repeat('a',8190))); 38INSERT INTO tab1 VALUES (23, concat("23", repeat('a',8190))); 39INSERT INTO tab1 VALUES (24, concat("24", repeat('a',8190))); 40INSERT INTO tab1 VALUES (25, concat("25", repeat('a',8190))); 41INSERT INTO tab1 VALUES (26, concat("26", repeat('a',8190))); 42INSERT INTO tab1 VALUES (27, concat("27", repeat('a',8190))); 43INSERT INTO tab1 VALUES (28, concat("28", repeat('a',8190))); 44INSERT INTO tab1 VALUES (29, concat("29", repeat('a',8190))); 45INSERT INTO tab1 VALUES (30, concat("30", repeat('a',8190))); 46INSERT INTO tab1 VALUES (31, concat("31", repeat('a',8190))); 47INSERT INTO tab1 VALUES (32, concat("32", repeat('a',8190))); 48INSERT INTO tab1 VALUES (33, concat("33", repeat('a',8190))); 49 50INSERT INTO tab1 VALUES (11, concat("11", repeat('a',8190))); 51INSERT INTO tab1 VALUES (12, concat("12", repeat('a',8190))); 52INSERT INTO tab1 VALUES (13, concat("13", repeat('a',8190))); 53INSERT INTO tab1 VALUES (14, concat("14", repeat('a',8190))); 54INSERT INTO tab1 VALUES (15, concat("15", repeat('a',8190))); 55INSERT INTO tab1 VALUES (16, concat("16", repeat('a',8190))); 56INSERT INTO tab1 VALUES (17, concat("17", repeat('a',8190))); 57INSERT INTO tab1 VALUES (18, concat("18", repeat('a',8190))); 58INSERT INTO tab1 VALUES (19, concat("19", repeat('a',8190))); 59INSERT INTO tab1 VALUES (20, concat("20", repeat('a',8190))); 60INSERT INTO tab1 VALUES (21, concat("21", repeat('a',8190))); 61 62INSERT INTO tab1 VALUES (34, concat("34", repeat('a',8190))); 63INSERT INTO tab1 VALUES (35, concat("35", repeat('a',8190))); 64INSERT INTO tab1 VALUES (36, concat("36", repeat('a',8190))); 65INSERT INTO tab1 VALUES (37, concat("37", repeat('a',8190))); 66INSERT INTO tab1 VALUES (38, concat("38", repeat('a',8190))); 67INSERT INTO tab1 VALUES (39, concat("39", repeat('a',8190))); 68INSERT INTO tab1 VALUES (40, concat("40", repeat('a',8190))); 69INSERT INTO tab1 VALUES (41, concat("41", repeat('a',8190))); 70INSERT INTO tab1 VALUES (42, concat("42", repeat('a',8190))); 71 72# clustered index is still root page only with the 42 records. 73# secondary index is filled 2 leaf pages have been prepared 74# | 1,..,21 | 22,..,42 | 75 76select PAGE_NUMBER, NUMBER_RECORDS 77from INFORMATION_SCHEMA.INNODB_SYS_TABLESPACES s1, 78INFORMATION_SCHEMA.INNODB_BUFFER_PAGE s2 79where s1.SPACE = s2.SPACE AND NAME like 'test/tab1%' 80and PAGE_TYPE = "INDEX" order by PAGE_NUMBER, NUMBER_RECORDS; 81 82set global innodb_purge_stop_now=ON; 83delete from tab1 where a = 33; 84delete from tab1 where a = 34; 85delete from tab1 where a = 35; 86delete from tab1 where a = 36; 87delete from tab1 where a = 37; 88delete from tab1 where a = 38; 89delete from tab1 where a = 39; 90delete from tab1 where a = 40; 91delete from tab1 where a = 41; 92delete from tab1 where a = 42; 93delete from tab1 where a = 12; 94delete from tab1 where a = 13; 95delete from tab1 where a = 14; 96delete from tab1 where a = 15; 97delete from tab1 where a = 16; 98delete from tab1 where a = 17; 99delete from tab1 where a = 18; 100delete from tab1 where a = 19; 101delete from tab1 where a = 20; 102delete from tab1 where a = 21; 103set global innodb_purge_run_now=ON; 104 105# wait for purge view progress (records are deleted actually by purge) 106--source include/wait_innodb_all_purged.inc 107 108# secondary index is not merged yet 109# | 1,..,11 | 22,..,32 | 110 111--echo # check page merge happens (nothing is expected) 112SELECT name,count_reset FROM information_schema.innodb_metrics 113WHERE name like 'index_page_merge_%'; 114 115select PAGE_NUMBER, NUMBER_RECORDS 116from INFORMATION_SCHEMA.INNODB_SYS_TABLESPACES s1, 117INFORMATION_SCHEMA.INNODB_BUFFER_PAGE s2 118where s1.SPACE = s2.SPACE AND NAME like 'test/tab1%' 119and PAGE_TYPE = "INDEX" order by PAGE_NUMBER, NUMBER_RECORDS; 120 121 122set global innodb_purge_stop_now=ON; 123delete from tab1 where a = 32; 124set global innodb_purge_run_now=ON; 125# wait for purge view progress (records are deleted actually by purge) 126--source include/wait_innodb_all_purged.inc 127 128--echo # check page merge happens (MERGE_THRESHOLD=50 causes merge here) 129SELECT name,count_reset FROM information_schema.innodb_metrics 130WHERE name like 'index_page_merge_%'; 131 132 133set global innodb_purge_stop_now=ON; 134delete from tab1 where a = 31; 135set global innodb_purge_run_now=ON; 136# wait for purge view progress (records are deleted actually by purge) 137--source include/wait_innodb_all_purged.inc 138 139--echo # check page merge happens (MERGE_THRESHOLD=45 causes merge here) 140SELECT name,count_reset FROM information_schema.innodb_metrics 141WHERE name like 'index_page_merge_%'; 142 143 144set global innodb_purge_stop_now=ON; 145delete from tab1 where a = 30; 146set global innodb_purge_run_now=ON; 147# wait for purge view progress (records are deleted actually by purge) 148--source include/wait_innodb_all_purged.inc 149 150--echo # check page merge happens (MERGE_THRESHOLD=40 causes merge here) 151SELECT name,count_reset FROM information_schema.innodb_metrics 152WHERE name like 'index_page_merge_%'; 153 154--disable_query_log 155# Reset flags 156SET GLOBAL innodb_monitor_disable=index_page_merge_attempts; 157SET GLOBAL innodb_monitor_disable=index_page_merge_successful; 158 159--disable_warnings 160set global innodb_monitor_enable = default; 161set global innodb_monitor_disable = default; 162set global innodb_monitor_reset = default; 163set global innodb_monitor_reset_all = default; 164--enable_warnings 165--enable_query_log 166