Debian Codegraph @

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

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