Debian Codegraph @

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

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