1# 2# Test to cause merge of the pages (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 varchar(2048)) engine=InnoDB;" 6# 7 8--source include/have_innodb.inc 9--source include/have_debug.inc 10--source include/have_innodb_16k.inc 11 12# turn on flags 13--disable_query_log 14SET GLOBAL innodb_monitor_enable=index_page_merge_attempts; 15SET GLOBAL innodb_monitor_reset=index_page_merge_attempts; 16SET GLOBAL innodb_monitor_enable=index_page_merge_successful; 17SET GLOBAL innodb_monitor_reset=index_page_merge_successful; 18--enable_query_log 19 20--echo # check MERGE_THRESHOLD 21--replace_result tab1#P tab1#p 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, repeat('a',2048)); 27insert into tab1 values (2, repeat('a',2048)); 28insert into tab1 values (3, repeat('a',2048)); 29insert into tab1 values (8, repeat('a',2048)); 30insert into tab1 values (9, repeat('a',2048)); 31insert into tab1 values (10, repeat('a',2048)); 32insert into tab1 values (11, repeat('a',2048)); 33insert into tab1 values (12, repeat('a',2048)); 34insert into tab1 values (4, repeat('a',2048)); 35insert into tab1 values (5, repeat('a',2048)); 36insert into tab1 values (6, repeat('a',2048)); 37insert into tab1 values (7, repeat('a',2048)); 38insert into tab1 values (13, repeat('a',2048)); 39insert into tab1 values (14, repeat('a',2048)); 40 41# filled 2 leaf pages have been prepared 42# | 1,..,7 | 8,..,14 | 43 44select PAGE_NUMBER, NUMBER_RECORDS 45from INFORMATION_SCHEMA.INNODB_SYS_TABLESPACES s1, 46INFORMATION_SCHEMA.INNODB_BUFFER_PAGE s2 47where s1.SPACE = s2.SPACE AND NAME like 'test/tab1%' 48and PAGE_TYPE = "INDEX" order by PAGE_NUMBER, NUMBER_RECORDS; 49 50set global innodb_purge_stop_now=ON; 51delete from tab1 where a = 12; 52delete from tab1 where a = 13; 53delete from tab1 where a = 14; 54delete from tab1 where a = 5; 55delete from tab1 where a = 6; 56delete from tab1 where a = 7; 57set global innodb_purge_run_now=ON; 58 59# wait for purge view progress (records are deleted actually by purge) 60--source include/wait_innodb_all_purged.inc 61 62# not merged yet 63# | 1,2,3,4 | 8,9,10,11 | 64 65--echo # check page merge happens (nothing is expected) 66SELECT name,count_reset FROM information_schema.innodb_metrics 67WHERE name like 'index_page_merge_%'; 68 69select PAGE_NUMBER, NUMBER_RECORDS 70from INFORMATION_SCHEMA.INNODB_SYS_TABLESPACES s1, 71INFORMATION_SCHEMA.INNODB_BUFFER_PAGE s2 72where s1.SPACE = s2.SPACE AND NAME like 'test/tab1%' 73and PAGE_TYPE = "INDEX" order by PAGE_NUMBER, NUMBER_RECORDS; 74 75 76set global innodb_purge_stop_now=ON; 77delete from tab1 where a = 11; 78set global innodb_purge_run_now=ON; 79# wait for purge view progress (records are deleted actually by purge) 80--source include/wait_innodb_all_purged.inc 81 82--echo # check page merge happens (MERGE_THRESHOLD=50 causes merge here) 83SELECT name,count_reset FROM information_schema.innodb_metrics 84WHERE name like 'index_page_merge_%'; 85 86 87set global innodb_purge_stop_now=ON; 88delete from tab1 where a = 10; 89set global innodb_purge_run_now=ON; 90# wait for purge view progress (records are deleted actually by purge) 91--source include/wait_innodb_all_purged.inc 92 93--echo # check page merge happens (MERGE_THRESHOLD=35 causes merge here) 94SELECT name,count_reset FROM information_schema.innodb_metrics 95WHERE name like 'index_page_merge_%'; 96 97 98set global innodb_purge_stop_now=ON; 99delete from tab1 where a = 9; 100set global innodb_purge_run_now=ON; 101# wait for purge view progress (records are deleted actually by purge) 102--source include/wait_innodb_all_purged.inc 103 104--echo # check page merge happens (MERGE_THRESHOLD=25 causes merge here) 105SELECT name,count_reset FROM information_schema.innodb_metrics 106WHERE name like 'index_page_merge_%'; 107 108--disable_query_log 109# Reset flags 110SET GLOBAL innodb_monitor_disable=index_page_merge_attempts; 111SET GLOBAL innodb_monitor_disable=index_page_merge_successful; 112 113--disable_warnings 114set global innodb_monitor_enable = default; 115set global innodb_monitor_disable = default; 116set global innodb_monitor_reset = default; 117set global innodb_monitor_reset_all = default; 118--enable_warnings 119--enable_query_log 120