Description:
We upgraded mysql from 8.0.36 to 8.0.41 and had a significant performance loss that caused us to need to increase the size of many of our EC2 instances significantly. I ran git bisect and was able to narrow down the commit that caused the regression to https://github.com/mysql/mysql-server/commit/2969af5e1d930b803c1dc71b586c000a7b05b3f1
It appears that what happened was that we have many SELECT statements that caused range scans, which had previously not had any record buffer preloading, but that was better for our use case since the number of rows that we look at in the scan was fairly small, and the number of rows loaded in the record buffer is fairly large.
How to repeat:
This is not extremely easy to repeat. I eventually created a python program that loads about 5 GB of data into an empty database and then runs multiple SELECT statements against it. I will attach the program after I create the ticket.
Following the code to see if I could prevent the record buffer from being used, I was able to land on one workaround, which is to use `LOCK IN SHARE MODE` at the end of the SELECT statement. The python program gives an option to use this to compare performance.
In general, the program does not show as pronounced a decline in performance as happens when querying against real data, but in some cases the queries take about twice as long without the workaround as they do with the workaround.
On my computer, I get the results
```
Database already initialized with 32484480 rows.
Total test time: 3.06s. Record buffer disabled: False
MySQL version: 8.0.41
>>>
Database already initialized with 32484480 rows.
Total test time: 1.04s. Record buffer disabled: True
MySQL version: 8.0.41
>>>
```
when using a new 8.0.41 mysql docker instance started with
```
docker run -d \
--name mysql8041 \
-e MYSQL_ROOT_PASSWORD=a \
-e MYSQL_DATABASE=b \
-p 3307:3306 \
mysql:8.0.41^
```
Suggested fix:
It would be great to get some configuration to prevent the record buffer from being used.
