MySQL Bugs: #107362: switch default read-ahead off

Bug #107362	switch default read-ahead off
Submitted:	21 May 2022 10:09	Modified:	23 May 2022 13:03
Reporter:	haochen he	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S5 (Performance)
Version:	all	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	performance

Description:
Read-ahead tries its best to predict random IOs and transform them to sequential IOs. It benefits query performance on HDD significantly. An increasing number of systems use SSDs rather than HDDs. One of the most shining properties of SSD is its fast random read performance. On the other hand, the read-ahead can not ensure the transformed sequential IOs are sequential inside SSD (because there is one more layer of address abstraction controlled by FTL), this may be more true when the data layout in SSD getting more scattered (i.e., worn).

We use read-intensive workload (tpc-e) to benchmark the performance on HDD and SSD with/without linear read-ahead (innodb_read_ahead_threshold=0/default-56). The result below supports our assumption:

https://ibb.co/wLVxQL5

Thanks!

How to repeat:
N/A

Suggested fix:
Change the default value of innodb_read_ahead_threshold to 0

Hi Mr. he,

Thank you for your bug report.

However, this is not a bug.
InnoDB read ahead threshold is a dynamic variable. Most of the machines hosting MySQL server use HDD's and not SSD's.

However, this variable is dynamic, hence you can set it to any value that suits you, including 0 (zero).

Not a bug.