MySQL Bugs: #119541: Performance regression due to record buffer in index range scan

Bug #119541	Performance regression due to record buffer in index range scan
Submitted:	11 Dec 12:32	Modified:	12 Dec 8:19
Reporter:	Martin Hansson (OCA)	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S2 (Serious)
Version:	8.0, 8.4, 9.5	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	index scan, record buffer, regression

Description:
Regression caused by a follow up fix for Bug#36775910: Record buffer not set in index range scans (SHA 2969af5)

Prior to this commit, the executioner consulted the storage engine for how many rows should be in the record buffer (handler::ha_is_record_buffer_wanted()). The line that caps the number of rows in the record buffer according to this number fell away in the bespoke commit, likely by mistake.

How to repeat:
CREATE TABLE t1 (
  id INT NOT NULL AUTO_INCREMENT,
  a INT,
  b INT,
  PRIMARY KEY (id),
  KEY i1 (b, a)
);

INSERT INTO t1(a, b) VALUES (1, 1);
INSERT INTO t1(a, b) SELECT a, b FROM t1;
INSERT INTO t1(a, b) SELECT a, b FROM t1;
INSERT INTO t1(a, b) SELECT a, b FROM t1;
INSERT INTO t1(a, b) SELECT a, b FROM t1;
INSERT INTO t1(a, b) SELECT a, b FROM t1;
INSERT INTO t1(a, b) SELECT a, b FROM t1;
INSERT INTO t1(a, b) SELECT a, b FROM t1;
INSERT INTO t1(a, b) SELECT a, b FROM t1;
INSERT INTO t1(a, b) SELECT a, b FROM t1;
INSERT INTO t1(a, b) SELECT a, b FROM t1;
INSERT INTO t1(a, b) SELECT a, b FROM t1;
INSERT INTO t1(a, b) SELECT a, b FROM t1;
INSERT INTO t1(a, b) SELECT a, b FROM t1;
INSERT INTO t1(a, b) SELECT a, b FROM t1;

mysqlslap --socket=... --user=... --concurrency=1 --query="SELECT b, a FROM ps10232.t1 FORCE KEY(i1) WHERE b = 1 ORDER BY a DESC LIMIT 1" --number-of-queries=100000 --iterations=1

This benchmark show more than three times as long execution time prior to bespoke commit. (19s vs. 7s) The suggested fix brings execution time back to what it was.

Suggested fix:
Put the cap back on the execution time.

`git show` of patch merged to percona-server. (*) I confirm the code being submitted is offered under the terms of the OCA, and that I am authorized to contribute it.

Contribution: submission.patch (application/octet-stream, text), 1.18 KiB.

Hi Martin,

Thank you for the test case and the patch. Verified as described.