Debian Codegraph @

mysql-test/suite/innodb/r/index_merge_threshold.result

raw
1 CREATE TABLE tab(a BIGINT PRIMARY KEY,c1 TINYTEXT,c2 TEXT,c3 MEDIUMTEXT,
2 c4 TINYBLOB,c5 BLOB,c6 MEDIUMBLOB,c7 LONGBLOB) charset latin1 ENGINE=InnoDB STATS_PERSISTENT=0;
3 CREATE INDEX index1 ON tab(c1(255)) COMMENT 'Check index level merge MERGE_THRESHOLD=51';
4 Warnings:
5 Warning 1478 InnoDB: Invalid value for MERGE_THRESHOLD in the CREATE TABLE statement. The value is ignored.
6 CREATE INDEX index2 ON tab(c2(750)) COMMENT 'Check index level merge MERGE_THRESHOLD=-1';
7 Warnings:
8 Warning 1478 InnoDB: Invalid value for MERGE_THRESHOLD in the CREATE TABLE statement. The value is ignored.
9 Warning 1478 InnoDB: Invalid value for MERGE_THRESHOLD in the CREATE TABLE statement. The value is ignored.
10 CREATE INDEX index3 ON tab(c3(750)) COMMENT 'Check index level merge MERGE_THRESHOLD=20';
11 Warnings:
12 Warning 1478 InnoDB: Invalid value for MERGE_THRESHOLD in the CREATE TABLE statement. The value is ignored.
13 Warning 1478 InnoDB: Invalid value for MERGE_THRESHOLD in the CREATE TABLE statement. The value is ignored.
14 CREATE INDEX index4 ON tab(c4(255)) COMMENT 'Check index level merge MERGE_THRESHOLD=25';
15 Warnings:
16 Warning 1478 InnoDB: Invalid value for MERGE_THRESHOLD in the CREATE TABLE statement. The value is ignored.
17 Warning 1478 InnoDB: Invalid value for MERGE_THRESHOLD in the CREATE TABLE statement. The value is ignored.
18 CREATE INDEX index5 ON tab(c5(750)) COMMENT 'Check index level merge MERGE_THRESHOLD=30';
19 Warnings:
20 Warning 1478 InnoDB: Invalid value for MERGE_THRESHOLD in the CREATE TABLE statement. The value is ignored.
21 Warning 1478 InnoDB: Invalid value for MERGE_THRESHOLD in the CREATE TABLE statement. The value is ignored.
22 CREATE INDEX index6 ON tab(c6(750)) COMMENT 'Check index level merge MERGE_THRESHOLD=35';
23 Warnings:
24 Warning 1478 InnoDB: Invalid value for MERGE_THRESHOLD in the CREATE TABLE statement. The value is ignored.
25 Warning 1478 InnoDB: Invalid value for MERGE_THRESHOLD in the CREATE TABLE statement. The value is ignored.
26 CREATE INDEX index7 ON tab(c7(750)) COMMENT 'Check index level merge MERGE_THRESHOLD=40';
27 Warnings:
28 Warning 1478 InnoDB: Invalid value for MERGE_THRESHOLD in the CREATE TABLE statement. The value is ignored.
29 Warning 1478 InnoDB: Invalid value for MERGE_THRESHOLD in the CREATE TABLE statement. The value is ignored.
30 SHOW CREATE TABLE tab;
31 Table Create Table
32 tab CREATE TABLE `tab` (
33 `a` bigint NOT NULL,
34 `c1` tinytext,
35 `c2` text,
36 `c3` mediumtext,
37 `c4` tinyblob,
38 `c5` blob,
39 `c6` mediumblob,
40 `c7` longblob,
41 PRIMARY KEY (`a`),
42 KEY `index1` (`c1`(255)) COMMENT 'Check index level merge MERGE_THRESHOLD=51',
43 KEY `index2` (`c2`(750)) COMMENT 'Check index level merge MERGE_THRESHOLD=-1',
44 KEY `index3` (`c3`(750)) COMMENT 'Check index level merge MERGE_THRESHOLD=20',
45 KEY `index4` (`c4`(255)) COMMENT 'Check index level merge MERGE_THRESHOLD=25',
46 KEY `index5` (`c5`(750)) COMMENT 'Check index level merge MERGE_THRESHOLD=30',
47 KEY `index6` (`c6`(750)) COMMENT 'Check index level merge MERGE_THRESHOLD=35',
48 KEY `index7` (`c7`(750)) COMMENT 'Check index level merge MERGE_THRESHOLD=40'
49 ) ENGINE=InnoDB DEFAULT CHARSET=latin1 STATS_PERSISTENT=0
50 SELECT t.NAME as TABLE_NAME, i.NAME as INDEX_NAME, i.MERGE_THRESHOLD
51 FROM INFORMATION_SCHEMA.INNODB_TABLES t, INFORMATION_SCHEMA.INNODB_INDEXES i
52 WHERE t.TABLE_ID = i.TABLE_ID AND t.NAME = 'test/tab' ORDER BY i.NAME;
53 TABLE_NAME INDEX_NAME MERGE_THRESHOLD
54 test/tab index1 50
55 test/tab index2 50
56 test/tab index3 20
57 test/tab index4 25
58 test/tab index5 30
59 test/tab index6 35
60 test/tab index7 40
61 test/tab PRIMARY 50
62 ALTER TABLE tab comment='MERGE_THRESHOLD=49';
63 Warnings:
64 Warning 1478 InnoDB: Invalid value for MERGE_THRESHOLD in the CREATE TABLE statement. The value is ignored.
65 Warning 1478 InnoDB: Invalid value for MERGE_THRESHOLD in the CREATE TABLE statement. The value is ignored.
66 SHOW CREATE TABLE tab;
67 Table Create Table
68 tab CREATE TABLE `tab` (
69 `a` bigint NOT NULL,
70 `c1` tinytext,
71 `c2` text,
72 `c3` mediumtext,
73 `c4` tinyblob,
74 `c5` blob,
75 `c6` mediumblob,
76 `c7` longblob,
77 PRIMARY KEY (`a`),
78 KEY `index1` (`c1`(255)) COMMENT 'Check index level merge MERGE_THRESHOLD=51',
79 KEY `index2` (`c2`(750)) COMMENT 'Check index level merge MERGE_THRESHOLD=-1',
80 KEY `index3` (`c3`(750)) COMMENT 'Check index level merge MERGE_THRESHOLD=20',
81 KEY `index4` (`c4`(255)) COMMENT 'Check index level merge MERGE_THRESHOLD=25',
82 KEY `index5` (`c5`(750)) COMMENT 'Check index level merge MERGE_THRESHOLD=30',
83 KEY `index6` (`c6`(750)) COMMENT 'Check index level merge MERGE_THRESHOLD=35',
84 KEY `index7` (`c7`(750)) COMMENT 'Check index level merge MERGE_THRESHOLD=40'
85 ) ENGINE=InnoDB DEFAULT CHARSET=latin1 STATS_PERSISTENT=0 COMMENT='MERGE_THRESHOLD=49'
86 SELECT t.NAME as TABLE_NAME, i.NAME as INDEX_NAME, i.MERGE_THRESHOLD
87 FROM INFORMATION_SCHEMA.INNODB_TABLES t, INFORMATION_SCHEMA.INNODB_INDEXES i
88 WHERE t.TABLE_ID = i.TABLE_ID AND t.NAME = 'test/tab' ORDER BY i.NAME;
89 TABLE_NAME INDEX_NAME MERGE_THRESHOLD
90 test/tab index1 49
91 test/tab index2 49
92 test/tab index3 20
93 test/tab index4 25
94 test/tab index5 30
95 test/tab index6 35
96 test/tab index7 40
97 test/tab PRIMARY 49
98 ALTER TABLE tab MODIFY COLUMN c7 VARCHAR(2048) ;
99 Warnings:
100 Warning 1478 InnoDB: Invalid value for MERGE_THRESHOLD in the CREATE TABLE statement. The value is ignored.
101 Warning 1478 InnoDB: Invalid value for MERGE_THRESHOLD in the CREATE TABLE statement. The value is ignored.
102 SHOW CREATE TABLE tab;
103 Table Create Table
104 tab CREATE TABLE `tab` (
105 `a` bigint NOT NULL,
106 `c1` tinytext,
107 `c2` text,
108 `c3` mediumtext,
109 `c4` tinyblob,
110 `c5` blob,
111 `c6` mediumblob,
112 `c7` varchar(2048) DEFAULT NULL,
113 PRIMARY KEY (`a`),
114 KEY `index1` (`c1`(255)) COMMENT 'Check index level merge MERGE_THRESHOLD=51',
115 KEY `index2` (`c2`(750)) COMMENT 'Check index level merge MERGE_THRESHOLD=-1',
116 KEY `index3` (`c3`(750)) COMMENT 'Check index level merge MERGE_THRESHOLD=20',
117 KEY `index4` (`c4`(255)) COMMENT 'Check index level merge MERGE_THRESHOLD=25',
118 KEY `index5` (`c5`(750)) COMMENT 'Check index level merge MERGE_THRESHOLD=30',
119 KEY `index6` (`c6`(750)) COMMENT 'Check index level merge MERGE_THRESHOLD=35',
120 KEY `index7` (`c7`(750)) COMMENT 'Check index level merge MERGE_THRESHOLD=40'
121 ) ENGINE=InnoDB DEFAULT CHARSET=latin1 STATS_PERSISTENT=0 COMMENT='MERGE_THRESHOLD=49'
122 SELECT t.NAME as TABLE_NAME, i.NAME as INDEX_NAME, i.MERGE_THRESHOLD
123 FROM INFORMATION_SCHEMA.INNODB_TABLES t, INFORMATION_SCHEMA.INNODB_INDEXES i
124 WHERE t.TABLE_ID = i.TABLE_ID AND t.NAME = 'test/tab' ORDER BY i.NAME;
125 TABLE_NAME INDEX_NAME MERGE_THRESHOLD
126 test/tab index1 49
127 test/tab index2 49
128 test/tab index3 20
129 test/tab index4 25
130 test/tab index5 30
131 test/tab index6 35
132 test/tab index7 40
133 test/tab PRIMARY 49
134 ALTER TABLE tab ADD INDEX index8 (c7(750)) COMMENT 'Check index level merge MERGE_THRESHOLD=45';
135 Warnings:
136 Warning 1831 Duplicate index 'index8' defined on the table 'test.tab'. This is deprecated and will be disallowed in a future release.
137 Warning 1478 InnoDB: Invalid value for MERGE_THRESHOLD in the CREATE TABLE statement. The value is ignored.
138 Warning 1478 InnoDB: Invalid value for MERGE_THRESHOLD in the CREATE TABLE statement. The value is ignored.
139 SHOW CREATE TABLE tab;
140 Table Create Table
141 tab CREATE TABLE `tab` (
142 `a` bigint NOT NULL,
143 `c1` tinytext,
144 `c2` text,
145 `c3` mediumtext,
146 `c4` tinyblob,
147 `c5` blob,
148 `c6` mediumblob,
149 `c7` varchar(2048) DEFAULT NULL,
150 PRIMARY KEY (`a`),
151 KEY `index1` (`c1`(255)) COMMENT 'Check index level merge MERGE_THRESHOLD=51',
152 KEY `index2` (`c2`(750)) COMMENT 'Check index level merge MERGE_THRESHOLD=-1',
153 KEY `index3` (`c3`(750)) COMMENT 'Check index level merge MERGE_THRESHOLD=20',
154 KEY `index4` (`c4`(255)) COMMENT 'Check index level merge MERGE_THRESHOLD=25',
155 KEY `index5` (`c5`(750)) COMMENT 'Check index level merge MERGE_THRESHOLD=30',
156 KEY `index6` (`c6`(750)) COMMENT 'Check index level merge MERGE_THRESHOLD=35',
157 KEY `index7` (`c7`(750)) COMMENT 'Check index level merge MERGE_THRESHOLD=40',
158 KEY `index8` (`c7`(750)) COMMENT 'Check index level merge MERGE_THRESHOLD=45'
159 ) ENGINE=InnoDB DEFAULT CHARSET=latin1 STATS_PERSISTENT=0 COMMENT='MERGE_THRESHOLD=49'
160 SELECT t.NAME as TABLE_NAME, i.NAME as INDEX_NAME, i.MERGE_THRESHOLD
161 FROM INFORMATION_SCHEMA.INNODB_TABLES t, INFORMATION_SCHEMA.INNODB_INDEXES i
162 WHERE t.TABLE_ID = i.TABLE_ID AND t.NAME = 'test/tab' ORDER BY i.NAME;
163 TABLE_NAME INDEX_NAME MERGE_THRESHOLD
164 test/tab index1 49
165 test/tab index2 49
166 test/tab index3 20
167 test/tab index4 25
168 test/tab index5 30
169 test/tab index6 35
170 test/tab index7 40
171 test/tab index8 45
172 test/tab PRIMARY 49
173 DROP TABLE tab;
174 #
175 # behavior for deleting records
176 #
177 # test to confirm behavior (MERGE_THRESHOLD=50 (default))
178 CREATE TABLE tab1 (a bigint primary key, b varchar(2048)) engine=InnoDB STATS_PERSISTENT=0;
179 # check MERGE_THRESHOLD
180 select t.NAME as TABLE_NAME, i.NAME as INDEX_NAME, i.MERGE_THRESHOLD
181 from INFORMATION_SCHEMA.INNODB_TABLES t, INFORMATION_SCHEMA.INNODB_INDEXES i
182 where t.TABLE_ID = i.TABLE_ID and t.NAME like 'test/tab1%' ORDER BY i.NAME;
183 TABLE_NAME INDEX_NAME MERGE_THRESHOLD
184 test/tab1 PRIMARY 50
185 insert into tab1 values (1, repeat('a',2048));
186 insert into tab1 values (2, repeat('a',2048));
187 insert into tab1 values (3, repeat('a',2048));
188 insert into tab1 values (8, repeat('a',2048));
189 insert into tab1 values (9, repeat('a',2048));
190 insert into tab1 values (10, repeat('a',2048));
191 insert into tab1 values (11, repeat('a',2048));
192 insert into tab1 values (12, repeat('a',2048));
193 insert into tab1 values (4, repeat('a',2048));
194 insert into tab1 values (5, repeat('a',2048));
195 insert into tab1 values (6, repeat('a',2048));
196 insert into tab1 values (7, repeat('a',2048));
197 insert into tab1 values (13, repeat('a',2048));
198 insert into tab1 values (14, repeat('a',2048));
199 select PAGE_NUMBER, NUMBER_RECORDS
200 from INFORMATION_SCHEMA.INNODB_TABLESPACES s1,
201 INFORMATION_SCHEMA.INNODB_BUFFER_PAGE s2,
202 INFORMATION_SCHEMA.INNODB_TABLES s3
203 where s1.SPACE = s2.SPACE AND s1.SPACE = s3.SPACE AND s3.NAME like 'test/tab1%'
204 and PAGE_TYPE = "INDEX" order by PAGE_NUMBER, NUMBER_RECORDS;
205 PAGE_NUMBER NUMBER_RECORDS
206 4 2
207 5 7
208 6 7
209 set global innodb_purge_stop_now=ON;
210 delete from tab1 where a = 12;
211 delete from tab1 where a = 13;
212 delete from tab1 where a = 14;
213 delete from tab1 where a = 5;
214 delete from tab1 where a = 6;
215 delete from tab1 where a = 7;
216 set global innodb_purge_run_now=ON;
217 # check page merge happens (nothing is expected)
218 SELECT name,count_reset FROM information_schema.innodb_metrics
219 WHERE name like 'index_page_merge_%';
220 name count_reset
221 index_page_merge_attempts 0
222 index_page_merge_successful 0
223 select PAGE_NUMBER, NUMBER_RECORDS
224 from INFORMATION_SCHEMA.INNODB_TABLESPACES s1,
225 INFORMATION_SCHEMA.INNODB_BUFFER_PAGE s2,
226 INFORMATION_SCHEMA.INNODB_TABLES s3
227 where s1.SPACE = s2.SPACE AND s1.SPACE = s3.SPACE AND s3.NAME like 'test/tab1%'
228 and PAGE_TYPE = "INDEX" order by PAGE_NUMBER, NUMBER_RECORDS;
229 PAGE_NUMBER NUMBER_RECORDS
230 4 2
231 5 4
232 6 4
233 set global innodb_purge_stop_now=ON;
234 delete from tab1 where a = 11;
235 set global innodb_purge_run_now=ON;
236 # check page merge happens (MERGE_THRESHOLD=50 causes merge here)
237 SELECT name,count_reset FROM information_schema.innodb_metrics
238 WHERE name like 'index_page_merge_%';
239 name count_reset
240 index_page_merge_attempts 1
241 index_page_merge_successful 1
242 set global innodb_purge_stop_now=ON;
243 delete from tab1 where a = 10;
244 set global innodb_purge_run_now=ON;
245 # check page merge happens (MERGE_THRESHOLD=35 causes merge here)
246 SELECT name,count_reset FROM information_schema.innodb_metrics
247 WHERE name like 'index_page_merge_%';
248 name count_reset
249 index_page_merge_attempts 2
250 index_page_merge_successful 2
251 set global innodb_purge_stop_now=ON;
252 delete from tab1 where a = 9;
253 set global innodb_purge_run_now=ON;
254 # check page merge happens (MERGE_THRESHOLD=25 causes merge here)
255 SELECT name,count_reset FROM information_schema.innodb_metrics
256 WHERE name like 'index_page_merge_%';
257 name count_reset
258 index_page_merge_attempts 2
259 index_page_merge_successful 2
260 DROP TABLE tab1;
261 # test to confirm behavior (MERGE_THRESHOLD=35)
262 CREATE TABLE tab1 (a bigint primary key, b varchar(2048)) engine=InnoDB STATS_PERSISTENT=0
263 COMMENT='MERGE_THRESHOLD=35';
264 # check MERGE_THRESHOLD
265 select t.NAME as TABLE_NAME, i.NAME as INDEX_NAME, i.MERGE_THRESHOLD
266 from INFORMATION_SCHEMA.INNODB_TABLES t, INFORMATION_SCHEMA.INNODB_INDEXES i
267 where t.TABLE_ID = i.TABLE_ID and t.NAME like 'test/tab1%' ORDER BY i.NAME;
268 TABLE_NAME INDEX_NAME MERGE_THRESHOLD
269 test/tab1 PRIMARY 35
270 insert into tab1 values (1, repeat('a',2048));
271 insert into tab1 values (2, repeat('a',2048));
272 insert into tab1 values (3, repeat('a',2048));
273 insert into tab1 values (8, repeat('a',2048));
274 insert into tab1 values (9, repeat('a',2048));
275 insert into tab1 values (10, repeat('a',2048));
276 insert into tab1 values (11, repeat('a',2048));
277 insert into tab1 values (12, repeat('a',2048));
278 insert into tab1 values (4, repeat('a',2048));
279 insert into tab1 values (5, repeat('a',2048));
280 insert into tab1 values (6, repeat('a',2048));
281 insert into tab1 values (7, repeat('a',2048));
282 insert into tab1 values (13, repeat('a',2048));
283 insert into tab1 values (14, repeat('a',2048));
284 select PAGE_NUMBER, NUMBER_RECORDS
285 from INFORMATION_SCHEMA.INNODB_TABLESPACES s1,
286 INFORMATION_SCHEMA.INNODB_BUFFER_PAGE s2,
287 INFORMATION_SCHEMA.INNODB_TABLES s3
288 where s1.SPACE = s2.SPACE AND s1.SPACE = s3.SPACE AND s3.NAME like 'test/tab1%'
289 and PAGE_TYPE = "INDEX" order by PAGE_NUMBER, NUMBER_RECORDS;
290 PAGE_NUMBER NUMBER_RECORDS
291 4 2
292 5 7
293 6 7
294 set global innodb_purge_stop_now=ON;
295 delete from tab1 where a = 12;
296 delete from tab1 where a = 13;
297 delete from tab1 where a = 14;
298 delete from tab1 where a = 5;
299 delete from tab1 where a = 6;
300 delete from tab1 where a = 7;
301 set global innodb_purge_run_now=ON;
302 # check page merge happens (nothing is expected)
303 SELECT name,count_reset FROM information_schema.innodb_metrics
304 WHERE name like 'index_page_merge_%';
305 name count_reset
306 index_page_merge_attempts 0
307 index_page_merge_successful 0
308 select PAGE_NUMBER, NUMBER_RECORDS
309 from INFORMATION_SCHEMA.INNODB_TABLESPACES s1,
310 INFORMATION_SCHEMA.INNODB_BUFFER_PAGE s2,
311 INFORMATION_SCHEMA.INNODB_TABLES s3
312 where s1.SPACE = s2.SPACE AND s1.SPACE = s3.SPACE AND s3.NAME like 'test/tab1%'
313 and PAGE_TYPE = "INDEX" order by PAGE_NUMBER, NUMBER_RECORDS;
314 PAGE_NUMBER NUMBER_RECORDS
315 4 2
316 5 4
317 6 4
318 set global innodb_purge_stop_now=ON;
319 delete from tab1 where a = 11;
320 set global innodb_purge_run_now=ON;
321 # check page merge happens (MERGE_THRESHOLD=50 causes merge here)
322 SELECT name,count_reset FROM information_schema.innodb_metrics
323 WHERE name like 'index_page_merge_%';
324 name count_reset
325 index_page_merge_attempts 0
326 index_page_merge_successful 0
327 set global innodb_purge_stop_now=ON;
328 delete from tab1 where a = 10;
329 set global innodb_purge_run_now=ON;
330 # check page merge happens (MERGE_THRESHOLD=35 causes merge here)
331 SELECT name,count_reset FROM information_schema.innodb_metrics
332 WHERE name like 'index_page_merge_%';
333 name count_reset
334 index_page_merge_attempts 1
335 index_page_merge_successful 1
336 set global innodb_purge_stop_now=ON;
337 delete from tab1 where a = 9;
338 set global innodb_purge_run_now=ON;
339 # check page merge happens (MERGE_THRESHOLD=25 causes merge here)
340 SELECT name,count_reset FROM information_schema.innodb_metrics
341 WHERE name like 'index_page_merge_%';
342 name count_reset
343 index_page_merge_attempts 2
344 index_page_merge_successful 2
345 DROP TABLE tab1;
346 # test to confirm behavior (MERGE_THRESHOLD=25)
347 CREATE TABLE tab1 (a bigint primary key, b varchar(2048)) engine=InnoDB STATS_PERSISTENT=0
348 COMMENT='MERGE_THRESHOLD=25';
349 # check MERGE_THRESHOLD
350 select t.NAME as TABLE_NAME, i.NAME as INDEX_NAME, i.MERGE_THRESHOLD
351 from INFORMATION_SCHEMA.INNODB_TABLES t, INFORMATION_SCHEMA.INNODB_INDEXES i
352 where t.TABLE_ID = i.TABLE_ID and t.NAME like 'test/tab1%' ORDER BY i.NAME;
353 TABLE_NAME INDEX_NAME MERGE_THRESHOLD
354 test/tab1 PRIMARY 25
355 insert into tab1 values (1, repeat('a',2048));
356 insert into tab1 values (2, repeat('a',2048));
357 insert into tab1 values (3, repeat('a',2048));
358 insert into tab1 values (8, repeat('a',2048));
359 insert into tab1 values (9, repeat('a',2048));
360 insert into tab1 values (10, repeat('a',2048));
361 insert into tab1 values (11, repeat('a',2048));
362 insert into tab1 values (12, repeat('a',2048));
363 insert into tab1 values (4, repeat('a',2048));
364 insert into tab1 values (5, repeat('a',2048));
365 insert into tab1 values (6, repeat('a',2048));
366 insert into tab1 values (7, repeat('a',2048));
367 insert into tab1 values (13, repeat('a',2048));
368 insert into tab1 values (14, repeat('a',2048));
369 select PAGE_NUMBER, NUMBER_RECORDS
370 from INFORMATION_SCHEMA.INNODB_TABLESPACES s1,
371 INFORMATION_SCHEMA.INNODB_BUFFER_PAGE s2,
372 INFORMATION_SCHEMA.INNODB_TABLES s3
373 where s1.SPACE = s2.SPACE AND s1.SPACE = s3.SPACE AND s3.NAME like 'test/tab1%'
374 and PAGE_TYPE = "INDEX" order by PAGE_NUMBER, NUMBER_RECORDS;
375 PAGE_NUMBER NUMBER_RECORDS
376 4 2
377 5 7
378 6 7
379 set global innodb_purge_stop_now=ON;
380 delete from tab1 where a = 12;
381 delete from tab1 where a = 13;
382 delete from tab1 where a = 14;
383 delete from tab1 where a = 5;
384 delete from tab1 where a = 6;
385 delete from tab1 where a = 7;
386 set global innodb_purge_run_now=ON;
387 # check page merge happens (nothing is expected)
388 SELECT name,count_reset FROM information_schema.innodb_metrics
389 WHERE name like 'index_page_merge_%';
390 name count_reset
391 index_page_merge_attempts 0
392 index_page_merge_successful 0
393 select PAGE_NUMBER, NUMBER_RECORDS
394 from INFORMATION_SCHEMA.INNODB_TABLESPACES s1,
395 INFORMATION_SCHEMA.INNODB_BUFFER_PAGE s2,
396 INFORMATION_SCHEMA.INNODB_TABLES s3
397 where s1.SPACE = s2.SPACE AND s1.SPACE = s3.SPACE AND s3.NAME like 'test/tab1%'
398 and PAGE_TYPE = "INDEX" order by PAGE_NUMBER, NUMBER_RECORDS;
399 PAGE_NUMBER NUMBER_RECORDS
400 4 2
401 5 4
402 6 4
403 set global innodb_purge_stop_now=ON;
404 delete from tab1 where a = 11;
405 set global innodb_purge_run_now=ON;
406 # check page merge happens (MERGE_THRESHOLD=50 causes merge here)
407 SELECT name,count_reset FROM information_schema.innodb_metrics
408 WHERE name like 'index_page_merge_%';
409 name count_reset
410 index_page_merge_attempts 0
411 index_page_merge_successful 0
412 set global innodb_purge_stop_now=ON;
413 delete from tab1 where a = 10;
414 set global innodb_purge_run_now=ON;
415 # check page merge happens (MERGE_THRESHOLD=35 causes merge here)
416 SELECT name,count_reset FROM information_schema.innodb_metrics
417 WHERE name like 'index_page_merge_%';
418 name count_reset
419 index_page_merge_attempts 0
420 index_page_merge_successful 0
421 set global innodb_purge_stop_now=ON;
422 delete from tab1 where a = 9;
423 set global innodb_purge_run_now=ON;
424 # check page merge happens (MERGE_THRESHOLD=25 causes merge here)
425 SELECT name,count_reset FROM information_schema.innodb_metrics
426 WHERE name like 'index_page_merge_%';
427 name count_reset
428 index_page_merge_attempts 1
429 index_page_merge_successful 1
430 DROP TABLE tab1;
431 # test to confirm partitioned table (MERGE_THRESHOLD=35)
432 CREATE TABLE tab1 (a bigint primary key, b varchar(2048)) STATS_PERSISTENT=0
433 COMMENT='MERGE_THRESHOLD=35'
434 PARTITION BY RANGE (a)
435 (PARTITION p0 VALUES LESS THAN (20) ENGINE = InnoDB,
436 PARTITION p1 VALUES LESS THAN MAXVALUE ENGINE = InnoDB);
437 # check MERGE_THRESHOLD
438 select t.NAME as TABLE_NAME, i.NAME as INDEX_NAME, i.MERGE_THRESHOLD
439 from INFORMATION_SCHEMA.INNODB_TABLES t, INFORMATION_SCHEMA.INNODB_INDEXES i
440 where t.TABLE_ID = i.TABLE_ID and t.NAME like 'test/tab1%' ORDER BY i.NAME;
441 TABLE_NAME INDEX_NAME MERGE_THRESHOLD
442 insert into tab1 values (1, repeat('a',2048));
443 insert into tab1 values (2, repeat('a',2048));
444 insert into tab1 values (3, repeat('a',2048));
445 insert into tab1 values (8, repeat('a',2048));
446 insert into tab1 values (9, repeat('a',2048));
447 insert into tab1 values (10, repeat('a',2048));
448 insert into tab1 values (11, repeat('a',2048));
449 insert into tab1 values (12, repeat('a',2048));
450 insert into tab1 values (4, repeat('a',2048));
451 insert into tab1 values (5, repeat('a',2048));
452 insert into tab1 values (6, repeat('a',2048));
453 insert into tab1 values (7, repeat('a',2048));
454 insert into tab1 values (13, repeat('a',2048));
455 insert into tab1 values (14, repeat('a',2048));
456 select PAGE_NUMBER, NUMBER_RECORDS
457 from INFORMATION_SCHEMA.INNODB_TABLESPACES s1,
458 INFORMATION_SCHEMA.INNODB_BUFFER_PAGE s2,
459 INFORMATION_SCHEMA.INNODB_TABLES s3
460 where s1.SPACE = s2.SPACE AND s1.SPACE = s3.SPACE AND s3.NAME like 'test/tab1%'
461 and PAGE_TYPE = "INDEX" order by PAGE_NUMBER, NUMBER_RECORDS;
462 PAGE_NUMBER NUMBER_RECORDS
463 4 0
464 4 2
465 5 7
466 6 7
467 set global innodb_purge_stop_now=ON;
468 delete from tab1 where a = 12;
469 delete from tab1 where a = 13;
470 delete from tab1 where a = 14;
471 delete from tab1 where a = 5;
472 delete from tab1 where a = 6;
473 delete from tab1 where a = 7;
474 set global innodb_purge_run_now=ON;
475 # check page merge happens (nothing is expected)
476 SELECT name,count_reset FROM information_schema.innodb_metrics
477 WHERE name like 'index_page_merge_%';
478 name count_reset
479 index_page_merge_attempts 0
480 index_page_merge_successful 0
481 select PAGE_NUMBER, NUMBER_RECORDS
482 from INFORMATION_SCHEMA.INNODB_TABLESPACES s1,
483 INFORMATION_SCHEMA.INNODB_BUFFER_PAGE s2,
484 INFORMATION_SCHEMA.INNODB_TABLES s3
485 where s1.SPACE = s2.SPACE AND s1.SPACE = s3.SPACE AND s3.NAME like 'test/tab1%'
486 and PAGE_TYPE = "INDEX" order by PAGE_NUMBER, NUMBER_RECORDS;
487 PAGE_NUMBER NUMBER_RECORDS
488 4 0
489 4 2
490 5 4
491 6 4
492 set global innodb_purge_stop_now=ON;
493 delete from tab1 where a = 11;
494 set global innodb_purge_run_now=ON;
495 # check page merge happens (MERGE_THRESHOLD=50 causes merge here)
496 SELECT name,count_reset FROM information_schema.innodb_metrics
497 WHERE name like 'index_page_merge_%';
498 name count_reset
499 index_page_merge_attempts 0
500 index_page_merge_successful 0
501 set global innodb_purge_stop_now=ON;
502 delete from tab1 where a = 10;
503 set global innodb_purge_run_now=ON;
504 # check page merge happens (MERGE_THRESHOLD=35 causes merge here)
505 SELECT name,count_reset FROM information_schema.innodb_metrics
506 WHERE name like 'index_page_merge_%';
507 name count_reset
508 index_page_merge_attempts 1
509 index_page_merge_successful 1
510 set global innodb_purge_stop_now=ON;
511 delete from tab1 where a = 9;
512 set global innodb_purge_run_now=ON;
513 # check page merge happens (MERGE_THRESHOLD=25 causes merge here)
514 SELECT name,count_reset FROM information_schema.innodb_metrics
515 WHERE name like 'index_page_merge_%';
516 name count_reset
517 index_page_merge_attempts 2
518 index_page_merge_successful 2
519 DROP TABLE tab1;
520 #
521 # behavior for updating to smaller records
522 #
523 # test to confirm behavior (MERGE_THRESHOLD=50 (default))
524 CREATE TABLE tab1 (a bigint primary key, b varchar(2048)) engine=InnoDB STATS_PERSISTENT=0;
525 # check MERGE_THRESHOLD
526 select t.NAME as TABLE_NAME, i.NAME as INDEX_NAME, i.MERGE_THRESHOLD
527 from INFORMATION_SCHEMA.INNODB_TABLES t, INFORMATION_SCHEMA.INNODB_INDEXES i
528 where t.TABLE_ID = i.TABLE_ID and t.NAME like 'test/tab1%' ORDER BY i.NAME;
529 TABLE_NAME INDEX_NAME MERGE_THRESHOLD
530 test/tab1 PRIMARY 50
531 insert into tab1 values (1, repeat('a',2048));
532 insert into tab1 values (2, repeat('a',2048));
533 insert into tab1 values (3, repeat('a',2048));
534 insert into tab1 values (8, repeat('a',2048));
535 insert into tab1 values (9, repeat('a',2048));
536 insert into tab1 values (10, repeat('a',2048));
537 insert into tab1 values (11, repeat('a',2048));
538 insert into tab1 values (12, repeat('a',2048));
539 insert into tab1 values (4, repeat('a',2048));
540 insert into tab1 values (5, repeat('a',2048));
541 insert into tab1 values (6, repeat('a',2048));
542 insert into tab1 values (7, repeat('a',2048));
543 insert into tab1 values (13, repeat('a',2048));
544 insert into tab1 values (14, repeat('a',2048));
545 select PAGE_NUMBER, NUMBER_RECORDS
546 from INFORMATION_SCHEMA.INNODB_TABLESPACES s1,
547 INFORMATION_SCHEMA.INNODB_BUFFER_PAGE s2,
548 INFORMATION_SCHEMA.INNODB_TABLES s3
549 where s1.SPACE = s2.SPACE AND s1.SPACE = s3.SPACE AND s3.NAME like 'test/tab1%'
550 and PAGE_TYPE = "INDEX" order by PAGE_NUMBER, NUMBER_RECORDS;
551 PAGE_NUMBER NUMBER_RECORDS
552 4 2
553 5 7
554 6 7
555 update tab1 set b='' where a = 12;
556 update tab1 set b='' where a = 13;
557 update tab1 set b='' where a = 14;
558 update tab1 set b='' where a = 5;
559 update tab1 set b='' where a = 6;
560 update tab1 set b='' where a = 7;
561 # check page merge happens (nothing is expected)
562 SELECT name,count_reset FROM information_schema.innodb_metrics
563 WHERE name like 'index_page_merge_%';
564 name count_reset
565 index_page_merge_attempts 0
566 index_page_merge_successful 0
567 select PAGE_NUMBER, NUMBER_RECORDS
568 from INFORMATION_SCHEMA.INNODB_TABLESPACES s1,
569 INFORMATION_SCHEMA.INNODB_BUFFER_PAGE s2,
570 INFORMATION_SCHEMA.INNODB_TABLES s3
571 where s1.SPACE = s2.SPACE AND s1.SPACE = s3.SPACE AND s3.NAME like 'test/tab1%'
572 and PAGE_TYPE = "INDEX" order by PAGE_NUMBER, NUMBER_RECORDS;
573 PAGE_NUMBER NUMBER_RECORDS
574 4 2
575 5 7
576 6 7
577 update tab1 set b='' where a = 11;
578 # check page merge happens (MERGE_THRESHOLD=50 causes merge here)
579 SELECT name,count_reset FROM information_schema.innodb_metrics
580 WHERE name like 'index_page_merge_%';
581 name count_reset
582 index_page_merge_attempts 1
583 index_page_merge_successful 1
584 update tab1 set b='' where a = 10;
585 # check page merge happens (MERGE_THRESHOLD=35 causes merge here)
586 SELECT name,count_reset FROM information_schema.innodb_metrics
587 WHERE name like 'index_page_merge_%';
588 name count_reset
589 index_page_merge_attempts 1
590 index_page_merge_successful 1
591 update tab1 set b='' where a = 9;
592 # check page merge happens (MERGE_THRESHOLD=25 causes merge here)
593 SELECT name,count_reset FROM information_schema.innodb_metrics
594 WHERE name like 'index_page_merge_%';
595 name count_reset
596 index_page_merge_attempts 1
597 index_page_merge_successful 1
598 DROP TABLE tab1;
599 # test to confirm behavior (MERGE_THRESHOLD=35)
600 CREATE TABLE tab1 (a bigint primary key, b varchar(2048)) engine=InnoDB
601 COMMENT='MERGE_THRESHOLD=35' STATS_PERSISTENT=0;
602 # check MERGE_THRESHOLD
603 select t.NAME as TABLE_NAME, i.NAME as INDEX_NAME, i.MERGE_THRESHOLD
604 from INFORMATION_SCHEMA.INNODB_TABLES t, INFORMATION_SCHEMA.INNODB_INDEXES i
605 where t.TABLE_ID = i.TABLE_ID and t.NAME like 'test/tab1%' ORDER BY i.NAME;
606 TABLE_NAME INDEX_NAME MERGE_THRESHOLD
607 test/tab1 PRIMARY 35
608 insert into tab1 values (1, repeat('a',2048));
609 insert into tab1 values (2, repeat('a',2048));
610 insert into tab1 values (3, repeat('a',2048));
611 insert into tab1 values (8, repeat('a',2048));
612 insert into tab1 values (9, repeat('a',2048));
613 insert into tab1 values (10, repeat('a',2048));
614 insert into tab1 values (11, repeat('a',2048));
615 insert into tab1 values (12, repeat('a',2048));
616 insert into tab1 values (4, repeat('a',2048));
617 insert into tab1 values (5, repeat('a',2048));
618 insert into tab1 values (6, repeat('a',2048));
619 insert into tab1 values (7, repeat('a',2048));
620 insert into tab1 values (13, repeat('a',2048));
621 insert into tab1 values (14, repeat('a',2048));
622 select PAGE_NUMBER, NUMBER_RECORDS
623 from INFORMATION_SCHEMA.INNODB_TABLESPACES s1,
624 INFORMATION_SCHEMA.INNODB_BUFFER_PAGE s2,
625 INFORMATION_SCHEMA.INNODB_TABLES s3
626 where s1.SPACE = s2.SPACE AND s1.SPACE = s3.SPACE AND s3.NAME like 'test/tab1%'
627 and PAGE_TYPE = "INDEX" order by PAGE_NUMBER, NUMBER_RECORDS;
628 PAGE_NUMBER NUMBER_RECORDS
629 4 2
630 5 7
631 6 7
632 update tab1 set b='' where a = 12;
633 update tab1 set b='' where a = 13;
634 update tab1 set b='' where a = 14;
635 update tab1 set b='' where a = 5;
636 update tab1 set b='' where a = 6;
637 update tab1 set b='' where a = 7;
638 # check page merge happens (nothing is expected)
639 SELECT name,count_reset FROM information_schema.innodb_metrics
640 WHERE name like 'index_page_merge_%';
641 name count_reset
642 index_page_merge_attempts 0
643 index_page_merge_successful 0
644 select PAGE_NUMBER, NUMBER_RECORDS
645 from INFORMATION_SCHEMA.INNODB_TABLESPACES s1,
646 INFORMATION_SCHEMA.INNODB_BUFFER_PAGE s2,
647 INFORMATION_SCHEMA.INNODB_TABLES s3
648 where s1.SPACE = s2.SPACE AND s1.SPACE = s3.SPACE AND s3.NAME like 'test/tab1%'
649 and PAGE_TYPE = "INDEX" order by PAGE_NUMBER, NUMBER_RECORDS;
650 PAGE_NUMBER NUMBER_RECORDS
651 4 2
652 5 7
653 6 7
654 update tab1 set b='' where a = 11;
655 # check page merge happens (MERGE_THRESHOLD=50 causes merge here)
656 SELECT name,count_reset FROM information_schema.innodb_metrics
657 WHERE name like 'index_page_merge_%';
658 name count_reset
659 index_page_merge_attempts 0
660 index_page_merge_successful 0
661 update tab1 set b='' where a = 10;
662 # check page merge happens (MERGE_THRESHOLD=35 causes merge here)
663 SELECT name,count_reset FROM information_schema.innodb_metrics
664 WHERE name like 'index_page_merge_%';
665 name count_reset
666 index_page_merge_attempts 1
667 index_page_merge_successful 1
668 update tab1 set b='' where a = 9;
669 # check page merge happens (MERGE_THRESHOLD=25 causes merge here)
670 SELECT name,count_reset FROM information_schema.innodb_metrics
671 WHERE name like 'index_page_merge_%';
672 name count_reset
673 index_page_merge_attempts 1
674 index_page_merge_successful 1
675 DROP TABLE tab1;
676 # test to confirm behavior (MERGE_THRESHOLD=25)
677 CREATE TABLE tab1 (a bigint primary key, b varchar(2048)) engine=InnoDB
678 COMMENT='MERGE_THRESHOLD=25' STATS_PERSISTENT=0;
679 # check MERGE_THRESHOLD
680 select t.NAME as TABLE_NAME, i.NAME as INDEX_NAME, i.MERGE_THRESHOLD
681 from INFORMATION_SCHEMA.INNODB_TABLES t, INFORMATION_SCHEMA.INNODB_INDEXES i
682 where t.TABLE_ID = i.TABLE_ID and t.NAME like 'test/tab1%' ORDER BY i.NAME;
683 TABLE_NAME INDEX_NAME MERGE_THRESHOLD
684 test/tab1 PRIMARY 25
685 insert into tab1 values (1, repeat('a',2048));
686 insert into tab1 values (2, repeat('a',2048));
687 insert into tab1 values (3, repeat('a',2048));
688 insert into tab1 values (8, repeat('a',2048));
689 insert into tab1 values (9, repeat('a',2048));
690 insert into tab1 values (10, repeat('a',2048));
691 insert into tab1 values (11, repeat('a',2048));
692 insert into tab1 values (12, repeat('a',2048));
693 insert into tab1 values (4, repeat('a',2048));
694 insert into tab1 values (5, repeat('a',2048));
695 insert into tab1 values (6, repeat('a',2048));
696 insert into tab1 values (7, repeat('a',2048));
697 insert into tab1 values (13, repeat('a',2048));
698 insert into tab1 values (14, repeat('a',2048));
699 select PAGE_NUMBER, NUMBER_RECORDS
700 from INFORMATION_SCHEMA.INNODB_TABLESPACES s1,
701 INFORMATION_SCHEMA.INNODB_BUFFER_PAGE s2,
702 INFORMATION_SCHEMA.INNODB_TABLES s3
703 where s1.SPACE = s2.SPACE AND s1.SPACE = s3.SPACE AND s3.NAME like 'test/tab1%'
704 and PAGE_TYPE = "INDEX" order by PAGE_NUMBER, NUMBER_RECORDS;
705 PAGE_NUMBER NUMBER_RECORDS
706 4 2
707 5 7
708 6 7
709 update tab1 set b='' where a = 12;
710 update tab1 set b='' where a = 13;
711 update tab1 set b='' where a = 14;
712 update tab1 set b='' where a = 5;
713 update tab1 set b='' where a = 6;
714 update tab1 set b='' where a = 7;
715 # check page merge happens (nothing is expected)
716 SELECT name,count_reset FROM information_schema.innodb_metrics
717 WHERE name like 'index_page_merge_%';
718 name count_reset
719 index_page_merge_attempts 0
720 index_page_merge_successful 0
721 select PAGE_NUMBER, NUMBER_RECORDS
722 from INFORMATION_SCHEMA.INNODB_TABLESPACES s1,
723 INFORMATION_SCHEMA.INNODB_BUFFER_PAGE s2,
724 INFORMATION_SCHEMA.INNODB_TABLES s3
725 where s1.SPACE = s2.SPACE AND s1.SPACE = s3.SPACE AND s3.NAME like 'test/tab1%'
726 and PAGE_TYPE = "INDEX" order by PAGE_NUMBER, NUMBER_RECORDS;
727 PAGE_NUMBER NUMBER_RECORDS
728 4 2
729 5 7
730 6 7
731 update tab1 set b='' where a = 11;
732 # check page merge happens (MERGE_THRESHOLD=50 causes merge here)
733 SELECT name,count_reset FROM information_schema.innodb_metrics
734 WHERE name like 'index_page_merge_%';
735 name count_reset
736 index_page_merge_attempts 0
737 index_page_merge_successful 0
738 update tab1 set b='' where a = 10;
739 # check page merge happens (MERGE_THRESHOLD=35 causes merge here)
740 SELECT name,count_reset FROM information_schema.innodb_metrics
741 WHERE name like 'index_page_merge_%';
742 name count_reset
743 index_page_merge_attempts 0
744 index_page_merge_successful 0
745 update tab1 set b='' where a = 9;
746 # check page merge happens (MERGE_THRESHOLD=25 causes merge here)
747 SELECT name,count_reset FROM information_schema.innodb_metrics
748 WHERE name like 'index_page_merge_%';
749 name count_reset
750 index_page_merge_attempts 1
751 index_page_merge_successful 1
752 DROP TABLE tab1;
753 # test to confirm explicit temporary table (MERGE_THRESHOLD=35)
754 # (though not registered to SYS_TABLES,SYS_INDEXES, it works correctly)
755 CREATE TEMPORARY TABLE tab1 (a bigint primary key, b varchar(2048)) engine=InnoDB
756 COMMENT='MERGE_THRESHOLD=35' STATS_PERSISTENT=0;
757 # check MERGE_THRESHOLD
758 select t.NAME as TABLE_NAME, i.NAME as INDEX_NAME, i.MERGE_THRESHOLD
759 from INFORMATION_SCHEMA.INNODB_TABLES t, INFORMATION_SCHEMA.INNODB_INDEXES i
760 where t.TABLE_ID = i.TABLE_ID and t.NAME like 'test/tab1%' ORDER BY i.NAME;
761 TABLE_NAME INDEX_NAME MERGE_THRESHOLD
762 insert into tab1 values (1, repeat('a',2048));
763 insert into tab1 values (2, repeat('a',2048));
764 insert into tab1 values (3, repeat('a',2048));
765 insert into tab1 values (8, repeat('a',2048));
766 insert into tab1 values (9, repeat('a',2048));
767 insert into tab1 values (10, repeat('a',2048));
768 insert into tab1 values (11, repeat('a',2048));
769 insert into tab1 values (12, repeat('a',2048));
770 insert into tab1 values (4, repeat('a',2048));
771 insert into tab1 values (5, repeat('a',2048));
772 insert into tab1 values (6, repeat('a',2048));
773 insert into tab1 values (7, repeat('a',2048));
774 insert into tab1 values (13, repeat('a',2048));
775 insert into tab1 values (14, repeat('a',2048));
776 select PAGE_NUMBER, NUMBER_RECORDS
777 from INFORMATION_SCHEMA.INNODB_TABLESPACES s1,
778 INFORMATION_SCHEMA.INNODB_BUFFER_PAGE s2,
779 INFORMATION_SCHEMA.INNODB_TABLES s3
780 where s1.SPACE = s2.SPACE AND s1.SPACE = s3.SPACE AND s3.NAME like 'test/tab1%'
781 and PAGE_TYPE = "INDEX" order by PAGE_NUMBER, NUMBER_RECORDS;
782 PAGE_NUMBER NUMBER_RECORDS
783 update tab1 set b='' where a = 12;
784 update tab1 set b='' where a = 13;
785 update tab1 set b='' where a = 14;
786 update tab1 set b='' where a = 5;
787 update tab1 set b='' where a = 6;
788 update tab1 set b='' where a = 7;
789 # check page merge happens (nothing is expected)
790 SELECT name,count_reset FROM information_schema.innodb_metrics
791 WHERE name like 'index_page_merge_%';
792 name count_reset
793 index_page_merge_attempts 0
794 index_page_merge_successful 0
795 select PAGE_NUMBER, NUMBER_RECORDS
796 from INFORMATION_SCHEMA.INNODB_TABLESPACES s1,
797 INFORMATION_SCHEMA.INNODB_BUFFER_PAGE s2,
798 INFORMATION_SCHEMA.INNODB_TABLES s3
799 where s1.SPACE = s2.SPACE AND s1.SPACE = s3.SPACE AND s3.NAME like 'test/tab1%'
800 and PAGE_TYPE = "INDEX" order by PAGE_NUMBER, NUMBER_RECORDS;
801 PAGE_NUMBER NUMBER_RECORDS
802 update tab1 set b='' where a = 11;
803 # check page merge happens (MERGE_THRESHOLD=50 causes merge here)
804 SELECT name,count_reset FROM information_schema.innodb_metrics
805 WHERE name like 'index_page_merge_%';
806 name count_reset
807 index_page_merge_attempts 0
808 index_page_merge_successful 0
809 update tab1 set b='' where a = 10;
810 # check page merge happens (MERGE_THRESHOLD=35 causes merge here)
811 SELECT name,count_reset FROM information_schema.innodb_metrics
812 WHERE name like 'index_page_merge_%';
813 name count_reset
814 index_page_merge_attempts 1
815 index_page_merge_successful 1
816 update tab1 set b='' where a = 9;
817 # check page merge happens (MERGE_THRESHOLD=25 causes merge here)
818 SELECT name,count_reset FROM information_schema.innodb_metrics
819 WHERE name like 'index_page_merge_%';
820 name count_reset
821 index_page_merge_attempts 1
822 index_page_merge_successful 1
823 DROP TABLE tab1;
824 #
825 # behavior for secondary index with blob
826 #
827 # test to confirm behavior (MERGE_THRESHOLD=50 (default))
828 CREATE TABLE tab1 (a bigint primary key, b blob) engine=InnoDB row_format=dynamic STATS_PERSISTENT=0;
829 CREATE INDEX index1 ON tab1(b(750));
830 # check MERGE_THRESHOLD
831 select t.NAME as TABLE_NAME, i.NAME as INDEX_NAME, i.MERGE_THRESHOLD
832 from INFORMATION_SCHEMA.INNODB_TABLES t, INFORMATION_SCHEMA.INNODB_INDEXES i
833 where t.TABLE_ID = i.TABLE_ID and t.NAME like 'test/tab1%' ORDER BY i.NAME;
834 TABLE_NAME INDEX_NAME MERGE_THRESHOLD
835 test/tab1 index1 50
836 test/tab1 PRIMARY 50
837 INSERT INTO tab1 VALUES (1, concat("01", repeat('a',8190)));
838 INSERT INTO tab1 VALUES (2, concat("02", repeat('a',8190)));
839 INSERT INTO tab1 VALUES (3, concat("03", repeat('a',8190)));
840 INSERT INTO tab1 VALUES (4, concat("04", repeat('a',8190)));
841 INSERT INTO tab1 VALUES (5, concat("05", repeat('a',8190)));
842 INSERT INTO tab1 VALUES (6, concat("06", repeat('a',8190)));
843 INSERT INTO tab1 VALUES (7, concat("07", repeat('a',8190)));
844 INSERT INTO tab1 VALUES (8, concat("08", repeat('a',8190)));
845 INSERT INTO tab1 VALUES (9, concat("09", repeat('a',8190)));
846 INSERT INTO tab1 VALUES (10, concat("10", repeat('a',8190)));
847 INSERT INTO tab1 VALUES (22, concat("22", repeat('a',8190)));
848 INSERT INTO tab1 VALUES (23, concat("23", repeat('a',8190)));
849 INSERT INTO tab1 VALUES (24, concat("24", repeat('a',8190)));
850 INSERT INTO tab1 VALUES (25, concat("25", repeat('a',8190)));
851 INSERT INTO tab1 VALUES (26, concat("26", repeat('a',8190)));
852 INSERT INTO tab1 VALUES (27, concat("27", repeat('a',8190)));
853 INSERT INTO tab1 VALUES (28, concat("28", repeat('a',8190)));
854 INSERT INTO tab1 VALUES (29, concat("29", repeat('a',8190)));
855 INSERT INTO tab1 VALUES (30, concat("30", repeat('a',8190)));
856 INSERT INTO tab1 VALUES (31, concat("31", repeat('a',8190)));
857 INSERT INTO tab1 VALUES (32, concat("32", repeat('a',8190)));
858 INSERT INTO tab1 VALUES (33, concat("33", repeat('a',8190)));
859 INSERT INTO tab1 VALUES (11, concat("11", repeat('a',8190)));
860 INSERT INTO tab1 VALUES (12, concat("12", repeat('a',8190)));
861 INSERT INTO tab1 VALUES (13, concat("13", repeat('a',8190)));
862 INSERT INTO tab1 VALUES (14, concat("14", repeat('a',8190)));
863 INSERT INTO tab1 VALUES (15, concat("15", repeat('a',8190)));
864 INSERT INTO tab1 VALUES (16, concat("16", repeat('a',8190)));
865 INSERT INTO tab1 VALUES (17, concat("17", repeat('a',8190)));
866 INSERT INTO tab1 VALUES (18, concat("18", repeat('a',8190)));
867 INSERT INTO tab1 VALUES (19, concat("19", repeat('a',8190)));
868 INSERT INTO tab1 VALUES (20, concat("20", repeat('a',8190)));
869 INSERT INTO tab1 VALUES (21, concat("21", repeat('a',8190)));
870 INSERT INTO tab1 VALUES (34, concat("34", repeat('a',8190)));
871 INSERT INTO tab1 VALUES (35, concat("35", repeat('a',8190)));
872 INSERT INTO tab1 VALUES (36, concat("36", repeat('a',8190)));
873 INSERT INTO tab1 VALUES (37, concat("37", repeat('a',8190)));
874 INSERT INTO tab1 VALUES (38, concat("38", repeat('a',8190)));
875 INSERT INTO tab1 VALUES (39, concat("39", repeat('a',8190)));
876 INSERT INTO tab1 VALUES (40, concat("40", repeat('a',8190)));
877 INSERT INTO tab1 VALUES (41, concat("41", repeat('a',8190)));
878 INSERT INTO tab1 VALUES (42, concat("42", repeat('a',8190)));
879 select PAGE_NUMBER, NUMBER_RECORDS
880 from INFORMATION_SCHEMA.INNODB_TABLESPACES s1,
881 INFORMATION_SCHEMA.INNODB_BUFFER_PAGE s2,
882 INFORMATION_SCHEMA.INNODB_TABLES s3
883 where s1.SPACE = s2.SPACE AND s1.SPACE = s3.SPACE AND s3.NAME like 'test/tab1%'
884 and PAGE_TYPE = "INDEX" order by PAGE_NUMBER, NUMBER_RECORDS;
885 PAGE_NUMBER NUMBER_RECORDS
886 4 42
887 5 2
888 28 21
889 29 21
890 set global innodb_purge_stop_now=ON;
891 delete from tab1 where a = 33;
892 delete from tab1 where a = 34;
893 delete from tab1 where a = 35;
894 delete from tab1 where a = 36;
895 delete from tab1 where a = 37;
896 delete from tab1 where a = 38;
897 delete from tab1 where a = 39;
898 delete from tab1 where a = 40;
899 delete from tab1 where a = 41;
900 delete from tab1 where a = 42;
901 delete from tab1 where a = 12;
902 delete from tab1 where a = 13;
903 delete from tab1 where a = 14;
904 delete from tab1 where a = 15;
905 delete from tab1 where a = 16;
906 delete from tab1 where a = 17;
907 delete from tab1 where a = 18;
908 delete from tab1 where a = 19;
909 delete from tab1 where a = 20;
910 delete from tab1 where a = 21;
911 set global innodb_purge_run_now=ON;
912 # check page merge happens (nothing is expected)
913 SELECT name,count_reset FROM information_schema.innodb_metrics
914 WHERE name like 'index_page_merge_%';
915 name count_reset
916 index_page_merge_attempts 0
917 index_page_merge_successful 0
918 select PAGE_NUMBER, NUMBER_RECORDS
919 from INFORMATION_SCHEMA.INNODB_TABLESPACES s1,
920 INFORMATION_SCHEMA.INNODB_BUFFER_PAGE s2,
921 INFORMATION_SCHEMA.INNODB_TABLES s3
922 where s1.SPACE = s2.SPACE AND s1.SPACE = s3.SPACE AND s3.NAME like 'test/tab1%'
923 and PAGE_TYPE = "INDEX" order by PAGE_NUMBER, NUMBER_RECORDS;
924 PAGE_NUMBER NUMBER_RECORDS
925 4 22
926 5 2
927 28 11
928 29 11
929 set global innodb_purge_stop_now=ON;
930 delete from tab1 where a = 32;
931 set global innodb_purge_run_now=ON;
932 # check page merge happens (MERGE_THRESHOLD=50 causes merge here)
933 SELECT name,count_reset FROM information_schema.innodb_metrics
934 WHERE name like 'index_page_merge_%';
935 name count_reset
936 index_page_merge_attempts 1
937 index_page_merge_successful 1
938 set global innodb_purge_stop_now=ON;
939 delete from tab1 where a = 31;
940 set global innodb_purge_run_now=ON;
941 # check page merge happens (MERGE_THRESHOLD=45 causes merge here)
942 SELECT name,count_reset FROM information_schema.innodb_metrics
943 WHERE name like 'index_page_merge_%';
944 name count_reset
945 index_page_merge_attempts 2
946 index_page_merge_successful 2
947 set global innodb_purge_stop_now=ON;
948 delete from tab1 where a = 30;
949 set global innodb_purge_run_now=ON;
950 # check page merge happens (MERGE_THRESHOLD=40 causes merge here)
951 SELECT name,count_reset FROM information_schema.innodb_metrics
952 WHERE name like 'index_page_merge_%';
953 name count_reset
954 index_page_merge_attempts 2
955 index_page_merge_successful 2
956 DROP TABLE tab1;
957 # test to confirm behavior (MERGE_THRESHOLD=45)
958 CREATE TABLE tab1 (a bigint primary key, b blob) engine=InnoDB row_format=dynamic STATS_PERSISTENT=0;
959 CREATE INDEX index1 ON tab1(b(750)) COMMENT 'MERGE_THRESHOLD=45';
960 # check MERGE_THRESHOLD
961 select t.NAME as TABLE_NAME, i.NAME as INDEX_NAME, i.MERGE_THRESHOLD
962 from INFORMATION_SCHEMA.INNODB_TABLES t, INFORMATION_SCHEMA.INNODB_INDEXES i
963 where t.TABLE_ID = i.TABLE_ID and t.NAME like 'test/tab1%' ORDER BY i.NAME;
964 TABLE_NAME INDEX_NAME MERGE_THRESHOLD
965 test/tab1 index1 45
966 test/tab1 PRIMARY 50
967 INSERT INTO tab1 VALUES (1, concat("01", repeat('a',8190)));
968 INSERT INTO tab1 VALUES (2, concat("02", repeat('a',8190)));
969 INSERT INTO tab1 VALUES (3, concat("03", repeat('a',8190)));
970 INSERT INTO tab1 VALUES (4, concat("04", repeat('a',8190)));
971 INSERT INTO tab1 VALUES (5, concat("05", repeat('a',8190)));
972 INSERT INTO tab1 VALUES (6, concat("06", repeat('a',8190)));
973 INSERT INTO tab1 VALUES (7, concat("07", repeat('a',8190)));
974 INSERT INTO tab1 VALUES (8, concat("08", repeat('a',8190)));
975 INSERT INTO tab1 VALUES (9, concat("09", repeat('a',8190)));
976 INSERT INTO tab1 VALUES (10, concat("10", repeat('a',8190)));
977 INSERT INTO tab1 VALUES (22, concat("22", repeat('a',8190)));
978 INSERT INTO tab1 VALUES (23, concat("23", repeat('a',8190)));
979 INSERT INTO tab1 VALUES (24, concat("24", repeat('a',8190)));
980 INSERT INTO tab1 VALUES (25, concat("25", repeat('a',8190)));
981 INSERT INTO tab1 VALUES (26, concat("26", repeat('a',8190)));
982 INSERT INTO tab1 VALUES (27, concat("27", repeat('a',8190)));
983 INSERT INTO tab1 VALUES (28, concat("28", repeat('a',8190)));
984 INSERT INTO tab1 VALUES (29, concat("29", repeat('a',8190)));
985 INSERT INTO tab1 VALUES (30, concat("30", repeat('a',8190)));
986 INSERT INTO tab1 VALUES (31, concat("31", repeat('a',8190)));
987 INSERT INTO tab1 VALUES (32, concat("32", repeat('a',8190)));
988 INSERT INTO tab1 VALUES (33, concat("33", repeat('a',8190)));
989 INSERT INTO tab1 VALUES (11, concat("11", repeat('a',8190)));
990 INSERT INTO tab1 VALUES (12, concat("12", repeat('a',8190)));
991 INSERT INTO tab1 VALUES (13, concat("13", repeat('a',8190)));
992 INSERT INTO tab1 VALUES (14, concat("14", repeat('a',8190)));
993 INSERT INTO tab1 VALUES (15, concat("15", repeat('a',8190)));
994 INSERT INTO tab1 VALUES (16, concat("16", repeat('a',8190)));
995 INSERT INTO tab1 VALUES (17, concat("17", repeat('a',8190)));
996 INSERT INTO tab1 VALUES (18, concat("18", repeat('a',8190)));
997 INSERT INTO tab1 VALUES (19, concat("19", repeat('a',8190)));
998 INSERT INTO tab1 VALUES (20, concat("20", repeat('a',8190)));
999 INSERT INTO tab1 VALUES (21, concat("21", repeat('a',8190)));
1000 INSERT INTO tab1 VALUES (34, concat("34", repeat('a',8190)));
1001 INSERT INTO tab1 VALUES (35, concat("35", repeat('a',8190)));
1002 INSERT INTO tab1 VALUES (36, concat("36", repeat('a',8190)));
1003 INSERT INTO tab1 VALUES (37, concat("37", repeat('a',8190)));
1004 INSERT INTO tab1 VALUES (38, concat("38", repeat('a',8190)));
1005 INSERT INTO tab1 VALUES (39, concat("39", repeat('a',8190)));
1006 INSERT INTO tab1 VALUES (40, concat("40", repeat('a',8190)));
1007 INSERT INTO tab1 VALUES (41, concat("41", repeat('a',8190)));
1008 INSERT INTO tab1 VALUES (42, concat("42", repeat('a',8190)));
1009 select PAGE_NUMBER, NUMBER_RECORDS
1010 from INFORMATION_SCHEMA.INNODB_TABLESPACES s1,
1011 INFORMATION_SCHEMA.INNODB_BUFFER_PAGE s2,
1012 INFORMATION_SCHEMA.INNODB_TABLES s3
1013 where s1.SPACE = s2.SPACE AND s1.SPACE = s3.SPACE AND s3.NAME like 'test/tab1%'
1014 and PAGE_TYPE = "INDEX" order by PAGE_NUMBER, NUMBER_RECORDS;
1015 PAGE_NUMBER NUMBER_RECORDS
1016 4 42
1017 5 2
1018 28 21
1019 29 21
1020 set global innodb_purge_stop_now=ON;
1021 delete from tab1 where a = 33;
1022 delete from tab1 where a = 34;
1023 delete from tab1 where a = 35;
1024 delete from tab1 where a = 36;
1025 delete from tab1 where a = 37;
1026 delete from tab1 where a = 38;
1027 delete from tab1 where a = 39;
1028 delete from tab1 where a = 40;
1029 delete from tab1 where a = 41;
1030 delete from tab1 where a = 42;
1031 delete from tab1 where a = 12;
1032 delete from tab1 where a = 13;
1033 delete from tab1 where a = 14;
1034 delete from tab1 where a = 15;
1035 delete from tab1 where a = 16;
1036 delete from tab1 where a = 17;
1037 delete from tab1 where a = 18;
1038 delete from tab1 where a = 19;
1039 delete from tab1 where a = 20;
1040 delete from tab1 where a = 21;
1041 set global innodb_purge_run_now=ON;
1042 # check page merge happens (nothing is expected)
1043 SELECT name,count_reset FROM information_schema.innodb_metrics
1044 WHERE name like 'index_page_merge_%';
1045 name count_reset
1046 index_page_merge_attempts 0
1047 index_page_merge_successful 0
1048 select PAGE_NUMBER, NUMBER_RECORDS
1049 from INFORMATION_SCHEMA.INNODB_TABLESPACES s1,
1050 INFORMATION_SCHEMA.INNODB_BUFFER_PAGE s2,
1051 INFORMATION_SCHEMA.INNODB_TABLES s3
1052 where s1.SPACE = s2.SPACE AND s1.SPACE = s3.SPACE AND s3.NAME like 'test/tab1%'
1053 and PAGE_TYPE = "INDEX" order by PAGE_NUMBER, NUMBER_RECORDS;
1054 PAGE_NUMBER NUMBER_RECORDS
1055 4 22
1056 5 2
1057 28 11
1058 29 11
1059 set global innodb_purge_stop_now=ON;
1060 delete from tab1 where a = 32;
1061 set global innodb_purge_run_now=ON;
1062 # check page merge happens (MERGE_THRESHOLD=50 causes merge here)
1063 SELECT name,count_reset FROM information_schema.innodb_metrics
1064 WHERE name like 'index_page_merge_%';
1065 name count_reset
1066 index_page_merge_attempts 0
1067 index_page_merge_successful 0
1068 set global innodb_purge_stop_now=ON;
1069 delete from tab1 where a = 31;
1070 set global innodb_purge_run_now=ON;
1071 # check page merge happens (MERGE_THRESHOLD=45 causes merge here)
1072 SELECT name,count_reset FROM information_schema.innodb_metrics
1073 WHERE name like 'index_page_merge_%';
1074 name count_reset
1075 index_page_merge_attempts 1
1076 index_page_merge_successful 1
1077 set global innodb_purge_stop_now=ON;
1078 delete from tab1 where a = 30;
1079 set global innodb_purge_run_now=ON;
1080 # check page merge happens (MERGE_THRESHOLD=40 causes merge here)
1081 SELECT name,count_reset FROM information_schema.innodb_metrics
1082 WHERE name like 'index_page_merge_%';
1083 name count_reset
1084 index_page_merge_attempts 2
1085 index_page_merge_successful 2
1086 DROP TABLE tab1;
1087 # test to confirm behavior (MERGE_THRESHOLD=40)
1088 CREATE TABLE tab1 (a bigint primary key, b blob) engine=InnoDB row_format=dynamic STATS_PERSISTENT=0;
1089 CREATE INDEX index1 ON tab1(b(750)) COMMENT 'MERGE_THRESHOLD=40';
1090 # check MERGE_THRESHOLD
1091 select t.NAME as TABLE_NAME, i.NAME as INDEX_NAME, i.MERGE_THRESHOLD
1092 from INFORMATION_SCHEMA.INNODB_TABLES t, INFORMATION_SCHEMA.INNODB_INDEXES i
1093 where t.TABLE_ID = i.TABLE_ID and t.NAME like 'test/tab1%' ORDER BY i.NAME;
1094 TABLE_NAME INDEX_NAME MERGE_THRESHOLD
1095 test/tab1 index1 40
1096 test/tab1 PRIMARY 50
1097 INSERT INTO tab1 VALUES (1, concat("01", repeat('a',8190)));
1098 INSERT INTO tab1 VALUES (2, concat("02", repeat('a',8190)));
1099 INSERT INTO tab1 VALUES (3, concat("03", repeat('a',8190)));
1100 INSERT INTO tab1 VALUES (4, concat("04", repeat('a',8190)));
1101 INSERT INTO tab1 VALUES (5, concat("05", repeat('a',8190)));
1102 INSERT INTO tab1 VALUES (6, concat("06", repeat('a',8190)));
1103 INSERT INTO tab1 VALUES (7, concat("07", repeat('a',8190)));
1104 INSERT INTO tab1 VALUES (8, concat("08", repeat('a',8190)));
1105 INSERT INTO tab1 VALUES (9, concat("09", repeat('a',8190)));
1106 INSERT INTO tab1 VALUES (10, concat("10", repeat('a',8190)));
1107 INSERT INTO tab1 VALUES (22, concat("22", repeat('a',8190)));
1108 INSERT INTO tab1 VALUES (23, concat("23", repeat('a',8190)));
1109 INSERT INTO tab1 VALUES (24, concat("24", repeat('a',8190)));
1110 INSERT INTO tab1 VALUES (25, concat("25", repeat('a',8190)));
1111 INSERT INTO tab1 VALUES (26, concat("26", repeat('a',8190)));
1112 INSERT INTO tab1 VALUES (27, concat("27", repeat('a',8190)));
1113 INSERT INTO tab1 VALUES (28, concat("28", repeat('a',8190)));
1114 INSERT INTO tab1 VALUES (29, concat("29", repeat('a',8190)));
1115 INSERT INTO tab1 VALUES (30, concat("30", repeat('a',8190)));
1116 INSERT INTO tab1 VALUES (31, concat("31", repeat('a',8190)));
1117 INSERT INTO tab1 VALUES (32, concat("32", repeat('a',8190)));
1118 INSERT INTO tab1 VALUES (33, concat("33", repeat('a',8190)));
1119 INSERT INTO tab1 VALUES (11, concat("11", repeat('a',8190)));
1120 INSERT INTO tab1 VALUES (12, concat("12", repeat('a',8190)));
1121 INSERT INTO tab1 VALUES (13, concat("13", repeat('a',8190)));
1122 INSERT INTO tab1 VALUES (14, concat("14", repeat('a',8190)));
1123 INSERT INTO tab1 VALUES (15, concat("15", repeat('a',8190)));
1124 INSERT INTO tab1 VALUES (16, concat("16", repeat('a',8190)));
1125 INSERT INTO tab1 VALUES (17, concat("17", repeat('a',8190)));
1126 INSERT INTO tab1 VALUES (18, concat("18", repeat('a',8190)));
1127 INSERT INTO tab1 VALUES (19, concat("19", repeat('a',8190)));
1128 INSERT INTO tab1 VALUES (20, concat("20", repeat('a',8190)));
1129 INSERT INTO tab1 VALUES (21, concat("21", repeat('a',8190)));
1130 INSERT INTO tab1 VALUES (34, concat("34", repeat('a',8190)));
1131 INSERT INTO tab1 VALUES (35, concat("35", repeat('a',8190)));
1132 INSERT INTO tab1 VALUES (36, concat("36", repeat('a',8190)));
1133 INSERT INTO tab1 VALUES (37, concat("37", repeat('a',8190)));
1134 INSERT INTO tab1 VALUES (38, concat("38", repeat('a',8190)));
1135 INSERT INTO tab1 VALUES (39, concat("39", repeat('a',8190)));
1136 INSERT INTO tab1 VALUES (40, concat("40", repeat('a',8190)));
1137 INSERT INTO tab1 VALUES (41, concat("41", repeat('a',8190)));
1138 INSERT INTO tab1 VALUES (42, concat("42", repeat('a',8190)));
1139 select PAGE_NUMBER, NUMBER_RECORDS
1140 from INFORMATION_SCHEMA.INNODB_TABLESPACES s1,
1141 INFORMATION_SCHEMA.INNODB_BUFFER_PAGE s2,
1142 INFORMATION_SCHEMA.INNODB_TABLES s3
1143 where s1.SPACE = s2.SPACE AND s1.SPACE = s3.SPACE AND s3.NAME like 'test/tab1%'
1144 and PAGE_TYPE = "INDEX" order by PAGE_NUMBER, NUMBER_RECORDS;
1145 PAGE_NUMBER NUMBER_RECORDS
1146 4 42
1147 5 2
1148 28 21
1149 29 21
1150 set global innodb_purge_stop_now=ON;
1151 delete from tab1 where a = 33;
1152 delete from tab1 where a = 34;
1153 delete from tab1 where a = 35;
1154 delete from tab1 where a = 36;
1155 delete from tab1 where a = 37;
1156 delete from tab1 where a = 38;
1157 delete from tab1 where a = 39;
1158 delete from tab1 where a = 40;
1159 delete from tab1 where a = 41;
1160 delete from tab1 where a = 42;
1161 delete from tab1 where a = 12;
1162 delete from tab1 where a = 13;
1163 delete from tab1 where a = 14;
1164 delete from tab1 where a = 15;
1165 delete from tab1 where a = 16;
1166 delete from tab1 where a = 17;
1167 delete from tab1 where a = 18;
1168 delete from tab1 where a = 19;
1169 delete from tab1 where a = 20;
1170 delete from tab1 where a = 21;
1171 set global innodb_purge_run_now=ON;
1172 # check page merge happens (nothing is expected)
1173 SELECT name,count_reset FROM information_schema.innodb_metrics
1174 WHERE name like 'index_page_merge_%';
1175 name count_reset
1176 index_page_merge_attempts 0
1177 index_page_merge_successful 0
1178 select PAGE_NUMBER, NUMBER_RECORDS
1179 from INFORMATION_SCHEMA.INNODB_TABLESPACES s1,
1180 INFORMATION_SCHEMA.INNODB_BUFFER_PAGE s2,
1181 INFORMATION_SCHEMA.INNODB_TABLES s3
1182 where s1.SPACE = s2.SPACE AND s1.SPACE = s3.SPACE AND s3.NAME like 'test/tab1%'
1183 and PAGE_TYPE = "INDEX" order by PAGE_NUMBER, NUMBER_RECORDS;
1184 PAGE_NUMBER NUMBER_RECORDS
1185 4 22
1186 5 2
1187 28 11
1188 29 11
1189 set global innodb_purge_stop_now=ON;
1190 delete from tab1 where a = 32;
1191 set global innodb_purge_run_now=ON;
1192 # check page merge happens (MERGE_THRESHOLD=50 causes merge here)
1193 SELECT name,count_reset FROM information_schema.innodb_metrics
1194 WHERE name like 'index_page_merge_%';
1195 name count_reset
1196 index_page_merge_attempts 0
1197 index_page_merge_successful 0
1198 set global innodb_purge_stop_now=ON;
1199 delete from tab1 where a = 31;
1200 set global innodb_purge_run_now=ON;
1201 # check page merge happens (MERGE_THRESHOLD=45 causes merge here)
1202 SELECT name,count_reset FROM information_schema.innodb_metrics
1203 WHERE name like 'index_page_merge_%';
1204 name count_reset
1205 index_page_merge_attempts 0
1206 index_page_merge_successful 0
1207 set global innodb_purge_stop_now=ON;
1208 delete from tab1 where a = 30;
1209 set global innodb_purge_run_now=ON;
1210 # check page merge happens (MERGE_THRESHOLD=40 causes merge here)
1211 SELECT name,count_reset FROM information_schema.innodb_metrics
1212 WHERE name like 'index_page_merge_%';
1213 name count_reset
1214 index_page_merge_attempts 1
1215 index_page_merge_successful 1
1216 DROP TABLE tab1;
1217 # compressed table behaves same (MERGE_THRESHOLD=45)
1218 CREATE TABLE tab1 (a bigint primary key, b blob) engine=InnoDB STATS_PERSISTENT=0
1219 ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8;
1220 CREATE INDEX index1 ON tab1(b(750)) COMMENT 'MERGE_THRESHOLD=45';
1221 # check MERGE_THRESHOLD
1222 select t.NAME as TABLE_NAME, i.NAME as INDEX_NAME, i.MERGE_THRESHOLD
1223 from INFORMATION_SCHEMA.INNODB_TABLES t, INFORMATION_SCHEMA.INNODB_INDEXES i
1224 where t.TABLE_ID = i.TABLE_ID and t.NAME like 'test/tab1%' ORDER BY i.NAME;
1225 TABLE_NAME INDEX_NAME MERGE_THRESHOLD
1226 test/tab1 index1 45
1227 test/tab1 PRIMARY 50
1228 INSERT INTO tab1 VALUES (1, concat("01", repeat('a',8190)));
1229 INSERT INTO tab1 VALUES (2, concat("02", repeat('a',8190)));
1230 INSERT INTO tab1 VALUES (3, concat("03", repeat('a',8190)));
1231 INSERT INTO tab1 VALUES (4, concat("04", repeat('a',8190)));
1232 INSERT INTO tab1 VALUES (5, concat("05", repeat('a',8190)));
1233 INSERT INTO tab1 VALUES (6, concat("06", repeat('a',8190)));
1234 INSERT INTO tab1 VALUES (7, concat("07", repeat('a',8190)));
1235 INSERT INTO tab1 VALUES (8, concat("08", repeat('a',8190)));
1236 INSERT INTO tab1 VALUES (9, concat("09", repeat('a',8190)));
1237 INSERT INTO tab1 VALUES (10, concat("10", repeat('a',8190)));
1238 INSERT INTO tab1 VALUES (22, concat("22", repeat('a',8190)));
1239 INSERT INTO tab1 VALUES (23, concat("23", repeat('a',8190)));
1240 INSERT INTO tab1 VALUES (24, concat("24", repeat('a',8190)));
1241 INSERT INTO tab1 VALUES (25, concat("25", repeat('a',8190)));
1242 INSERT INTO tab1 VALUES (26, concat("26", repeat('a',8190)));
1243 INSERT INTO tab1 VALUES (27, concat("27", repeat('a',8190)));
1244 INSERT INTO tab1 VALUES (28, concat("28", repeat('a',8190)));
1245 INSERT INTO tab1 VALUES (29, concat("29", repeat('a',8190)));
1246 INSERT INTO tab1 VALUES (30, concat("30", repeat('a',8190)));
1247 INSERT INTO tab1 VALUES (31, concat("31", repeat('a',8190)));
1248 INSERT INTO tab1 VALUES (32, concat("32", repeat('a',8190)));
1249 INSERT INTO tab1 VALUES (33, concat("33", repeat('a',8190)));
1250 INSERT INTO tab1 VALUES (11, concat("11", repeat('a',8190)));
1251 INSERT INTO tab1 VALUES (12, concat("12", repeat('a',8190)));
1252 INSERT INTO tab1 VALUES (13, concat("13", repeat('a',8190)));
1253 INSERT INTO tab1 VALUES (14, concat("14", repeat('a',8190)));
1254 INSERT INTO tab1 VALUES (15, concat("15", repeat('a',8190)));
1255 INSERT INTO tab1 VALUES (16, concat("16", repeat('a',8190)));
1256 INSERT INTO tab1 VALUES (17, concat("17", repeat('a',8190)));
1257 INSERT INTO tab1 VALUES (18, concat("18", repeat('a',8190)));
1258 INSERT INTO tab1 VALUES (19, concat("19", repeat('a',8190)));
1259 INSERT INTO tab1 VALUES (20, concat("20", repeat('a',8190)));
1260 INSERT INTO tab1 VALUES (21, concat("21", repeat('a',8190)));
1261 INSERT INTO tab1 VALUES (34, concat("34", repeat('a',8190)));
1262 INSERT INTO tab1 VALUES (35, concat("35", repeat('a',8190)));
1263 INSERT INTO tab1 VALUES (36, concat("36", repeat('a',8190)));
1264 INSERT INTO tab1 VALUES (37, concat("37", repeat('a',8190)));
1265 INSERT INTO tab1 VALUES (38, concat("38", repeat('a',8190)));
1266 INSERT INTO tab1 VALUES (39, concat("39", repeat('a',8190)));
1267 INSERT INTO tab1 VALUES (40, concat("40", repeat('a',8190)));
1268 INSERT INTO tab1 VALUES (41, concat("41", repeat('a',8190)));
1269 INSERT INTO tab1 VALUES (42, concat("42", repeat('a',8190)));
1270 select PAGE_NUMBER, NUMBER_RECORDS
1271 from INFORMATION_SCHEMA.INNODB_TABLESPACES s1,
1272 INFORMATION_SCHEMA.INNODB_BUFFER_PAGE s2,
1273 INFORMATION_SCHEMA.INNODB_TABLES s3
1274 where s1.SPACE = s2.SPACE AND s1.SPACE = s3.SPACE AND s3.NAME like 'test/tab1%'
1275 and PAGE_TYPE = "INDEX" order by PAGE_NUMBER, NUMBER_RECORDS;
1276 PAGE_NUMBER NUMBER_RECORDS
1277 4 42
1278 5 2
1279 28 21
1280 29 21
1281 set global innodb_purge_stop_now=ON;
1282 delete from tab1 where a = 33;
1283 delete from tab1 where a = 34;
1284 delete from tab1 where a = 35;
1285 delete from tab1 where a = 36;
1286 delete from tab1 where a = 37;
1287 delete from tab1 where a = 38;
1288 delete from tab1 where a = 39;
1289 delete from tab1 where a = 40;
1290 delete from tab1 where a = 41;
1291 delete from tab1 where a = 42;
1292 delete from tab1 where a = 12;
1293 delete from tab1 where a = 13;
1294 delete from tab1 where a = 14;
1295 delete from tab1 where a = 15;
1296 delete from tab1 where a = 16;
1297 delete from tab1 where a = 17;
1298 delete from tab1 where a = 18;
1299 delete from tab1 where a = 19;
1300 delete from tab1 where a = 20;
1301 delete from tab1 where a = 21;
1302 set global innodb_purge_run_now=ON;
1303 # check page merge happens (nothing is expected)
1304 SELECT name,count_reset FROM information_schema.innodb_metrics
1305 WHERE name like 'index_page_merge_%';
1306 name count_reset
1307 index_page_merge_attempts 0
1308 index_page_merge_successful 0
1309 select PAGE_NUMBER, NUMBER_RECORDS
1310 from INFORMATION_SCHEMA.INNODB_TABLESPACES s1,
1311 INFORMATION_SCHEMA.INNODB_BUFFER_PAGE s2,
1312 INFORMATION_SCHEMA.INNODB_TABLES s3
1313 where s1.SPACE = s2.SPACE AND s1.SPACE = s3.SPACE AND s3.NAME like 'test/tab1%'
1314 and PAGE_TYPE = "INDEX" order by PAGE_NUMBER, NUMBER_RECORDS;
1315 PAGE_NUMBER NUMBER_RECORDS
1316 4 22
1317 5 2
1318 28 11
1319 29 11
1320 set global innodb_purge_stop_now=ON;
1321 delete from tab1 where a = 32;
1322 set global innodb_purge_run_now=ON;
1323 # check page merge happens (MERGE_THRESHOLD=50 causes merge here)
1324 SELECT name,count_reset FROM information_schema.innodb_metrics
1325 WHERE name like 'index_page_merge_%';
1326 name count_reset
1327 index_page_merge_attempts 0
1328 index_page_merge_successful 0
1329 set global innodb_purge_stop_now=ON;
1330 delete from tab1 where a = 31;
1331 set global innodb_purge_run_now=ON;
1332 # check page merge happens (MERGE_THRESHOLD=45 causes merge here)
1333 SELECT name,count_reset FROM information_schema.innodb_metrics
1334 WHERE name like 'index_page_merge_%';
1335 name count_reset
1336 index_page_merge_attempts 1
1337 index_page_merge_successful 1
1338 set global innodb_purge_stop_now=ON;
1339 delete from tab1 where a = 30;
1340 set global innodb_purge_run_now=ON;
1341 # check page merge happens (MERGE_THRESHOLD=40 causes merge here)
1342 SELECT name,count_reset FROM information_schema.innodb_metrics
1343 WHERE name like 'index_page_merge_%';
1344 name count_reset
1345 index_page_merge_attempts 2
1346 index_page_merge_successful 2
1347 DROP TABLE tab1;