Bug #105044 Added page szie judgment logic like btr_cur_optimistic_insert in btr_insert_into
Submitted: 26 Sep 2021 15:32 Modified: 27 Sep 2021 12:32
Reporter: alex xing (OCA) Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: InnoDB storage engine Severity:S5 (Performance)
Version:8.0 OS:Any
Assigned to: CPU Architecture:Any

[26 Sep 2021 15:32] alex xing
Description:
in btr_insert_into_right_sibling, user may be failed to insert in next_block,
If judge the result just like btr_cur_optimistic_insert  ahead of time , we can skip the btr_page_get_father and page_cur_search.

The following example shows the benefits of optimization:
drop table if  exists t;
CREATE TABLE t (
a INT NOT NULL PRIMARY KEY,
b VARCHAR(3500)
) ENGINE=INNODB CHARSET=LATIN1 ROW_FORMAT=COMPACT;
INSERT INTO t SELECT 10,REPEAT('a',3500);
INSERT INTO t SELECT 20,REPEAT('a',3500);
INSERT INTO t SELECT 30,REPEAT('a',3500);
INSERT INTO t SELECT 40,REPEAT('a',3500);
INSERT INTO t SELECT 50,REPEAT('a',3500);
INSERT INTO t SELECT 23,REPEAT('a',3500);
INSERT INTO t SELECT 22,REPEAT('a',3500);
INSERT INTO t SELECT 60,REPEAT('a',3500);

// faied to btr_insert_into_right_sibling, which can be optimized
INSERT INTO t SELECT 24,REPEAT('a',3500);

How to repeat:
just read the code

Suggested fix:
judge the result just like btr_cur_optimistic_insert  ahead of time just as the below patch
[26 Sep 2021 15:34] alex xing
a simple patch to describe the optimization

(*) I confirm the code being submitted is offered under the terms of the OCA, and that I am authorized to contribute it.

Contribution: commit.patch (text/plain), 1.40 KiB.

[27 Sep 2021 12:32] MySQL Verification Team
Hi Mr. xing,

Thank you for your bug report.

We have ran your test case and analysed your patch and the performance difference and concluded that your contribution has merit.

Thank you, especially, for your patch.

Verified as reported.