Debian Codegraph @

mysql-test/suite/innodb/include/innodb_merge_threshold_delete.inc

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