Docs

RDS for MySQL

Performance

Logical prefetch

This topic describes the logical prefetch feature of RDS for MySQL.

Supported version

MySQL 5.7 version, and the kernel minor version is MySQL 5.7.44_20260215 and above.

MySQL 8.0 with a kernel version of MySQL 8.0.43_20251215 or later.

MySQL 8.4 version, and the kernel minor version is MySQL 8.4.7_20260415 and above.

tip

You can view the kernel minor version of an instance by Viewing instance information. If the kernel minor version of your instance is not within the supported kernel minor version range, you can Manually upgrading the instance kernel minor version.

Background

MySQL natively supports linear prefetching. It predicts the pages that need to be read based on their physical location and asynchronously loads them into the buffer pool. This allows subsequent read requests from user threads to directly hit the buffer pool, reducing the I/O latency of user SQL queries. However, for instances that use volumes, adjacent data pages may not be contiguous in the actual storage. As a result, the native linear prefetching mechanism of MySQL provides suboptimal optimization.

RDS for MySQL introduces a logical prefetching feature. This feature accurately predicts the pages that need to be read based on user SQL queries to reduce the I/O latency of SQL queries for instances that use volumes.

Feature description

The logical pre-read capability scans indexes through a background thread, accurately predicts the pages that user threads need to read based on the index, and asynchronously loads them into the buffer pool.

Logical pre-read supports the following scenarios:

Primary key range scan (Suitable for scenarios with a high volume of primary key index scans)

Secondary index range scan (Suitable for scenarios with a high volume of secondary covering index scans)

Covering index scan (Suitable for scenarios with a high volume of secondary covering index scans)

Secondary index range scan with primary key lookup (Suitable for scenarios with a poor secondary index filtering rate)

To optimize the read performance of BLOB data, this iteration of MySQL 8.0 also implements the prefetching capability for BLOB objects.

Instructions

Parameter description

RDS for MySQL provides the following modifiable parameters to configure the logical prefetch feature.

Parameter name	Supported version	Default value	Whether an instance restart is required for the change to take effect	Value range	Scope	Parameter description
loose_enable_logical_prefetch_page	MySQL 5.7，MySQL 8.0，MySQL 8.4	OFF	No	[ON\|OFF]	GLOBAL/SESSION	Enables or disables the logical prefetch feature. Values: ON: enable. OFF: disable.
loose_logical_prefetch_rows_threshold	MySQL 5.7，MySQL 8.0，MySQL 8.4	OFF	No	[ON\|OFF]	GLOBAL/SESSION	The minimum number of scanned rows for executing a logical prefetch. Logical prefetch is executed only if the estimated number of scanned rows in the execution plan exceeds the value of this parameter.
loose_enable_blob_prefetch	MySQL 8.0，MySQL 8.4	OFF	No	[ON\|OFF]	GLOBAL/SESSION	Enables or disables the blob prefetch feature. Values: ON: enable. OFF: disable.
loose_enable_track_index_io	MySQL 5.7，MySQL 8.0，MySQL 8.4	OFF	No	[ON\|OFF]	GLOBAL/SESSION	Enables or disables the IO metric monitoring feature of information_schema.rds_io_trace_stats.

Examples

Before application, you can use Database Workshop to manually connect to the secondary instance and execute prefetch SQL to test the optimization effect. This avoids putting heavy pressure on the primary instance from large-scale scans.

After enabling IO metric observation by running SET SESSION loose_enable_track_index_io=ON, you can execute SELECT * FROM information_schema.rds_io_trace_stats WHERE thread_id = connection_Id() to check whether the current thread performed a logical prefetch, the details of the logical prefetch, and the possible reason for not supporting it:

Metrics to observe	Description of indicators
prepare_logical_prefetch.is_exceed_max_parallel	Whether the number of concurrent operations exceeds the current supported maximum value.
prepare_logical_prefetch.btr_root_level	Pre-reading is not supported if the index tree has only one layer (small data volume).
finish_logical_prefetch.n_ctx_async_read_page_request	The quantity of asynchronous read requests that the logical prefetch tries to issue.
finish_logical_prefetch.n_ctx_async_read_page_success	The quantity of asynchronous read requests successfully issued by logical prefetch.
finish_logical_prefetch.ctx_execute	The overall elapsed time of the logical prefetch process can represent the overall latency of the query.

Precautions

Enabling the logical prefetch feature increases IO/CPU resource consumption due to prefetch threads, which might impact other concurrent SQL operations. Therefore, enable this feature selectively only for the required SQL statements.

Logical prefetch is not currently supported for compressed tables, partitioned tables, or non-InnoDB tables.

Logical prefetch is not currently supported for multi-valued indexes, virtual generated column indexes, spatial indexes, full-text indexes, or vector indexes.

In MySQL 5.7, BLOB data can only be read sequentially, so prefetching for BLOB objects is not currently supported.

Performance data

Test Environment

An RDS for MySQL 8.0 instance with a 2C4G node specification and a 10 TB cloud disk.

Test table data

Use `sysbench prepare` to insert data that is five times the number of rows in the test range.

Test method and results

Restart the MySQL process before each test to ensure that the memory `buffer_pool` contains no cached data, which allows for testing the actual improvement in execution efficiency.

Scan range (unit: row)	Prefetch (Second)	Non-prefetch (Unit: Second)	Elapsed time comparison
100,000	0.44	0.62	-29.04%
500,000	2.20	3.03	-27.40%
2,000,000	9.05	13.48	-32.87%
5,000,000	21.48	30.16	-28.78%
10,000,000	43.14	62.17	-30.61%
50,000,000	209.44	302.33	-30.73%
100,000,000	424.69	737.57	-42.42%

Last updated: 2026.05.28 15:17:55