The Buffer Pool Extension (BPE) feature for RDS MySQL instances that use high-performance disks can improve disk I/O efficiency and system responsiveness. This feature extends the buffer pool to accelerate caching, boosting the overall read and write performance of your RDS instance. This topic describes how BPE works, its use cases, and performance benchmarks.
You can join the RDS Storage Capability Communication DingTalk group (ID: 121770005512) to obtain more information about RDS storage capabilities. You can ask questions, communicate, and provide feedback in the group.
Background
Reading data from disk to memory and writing data from memory to disk are common database operations. Compared to in-memory operations, disk I/O is significantly slower and more time-consuming. For workloads with large-scale or frequent data requests, disk I/O can become a major performance bottleneck.
To address this, ApsaraDB RDS for MySQL offers a new storage option: high-performance disks. These disks are compatible with all ESSD features(except for pausing instances) and use a three-tiered storage architecture to manage different types of data and cache. High-performance disks also introduce I/O burst and the Buffer Pool Extension (BPE) feature to further improve the I/O performance of your RDS instance. For more information, see What is a high-performance disk?.
Introduction
Overview
A buffer pool is a memory area that caches data from disks. When the database needs to read or write a data block, it first checks the buffer pool:
If the data block is in the buffer pool, the database reads or writes it directly from memory.
If the data block is not in the buffer pool, the database reads it from or writes it to the disk and then copies it into the buffer pool.
This caching mechanism effectively reduces disk I/O operations and improves instance I/O performance. However, the size of the buffer pool is limited by the system's memory. If memory is insufficient, the limited buffer pool size can negatively impact I/O performance.
RDS MySQL introduces the Buffer Pool Extension (BPE) feature to expand the effective size of the buffer pool. By leveraging the RDS engine kernel, BPE improves the overall read and write performance of RDS MySQL. This allows your instance to execute more read and write commands within a given period, increasing disk I/O efficiency and system responsiveness, thereby accelerating caching.
Benefits
The BPE feature creates a three-tiered storage structure that leverages Alibaba Cloud's underlying storage media to improve instance I/O performance for various data storage scenarios and requirements:
Improved read/write performance and reduced SQL execution time
The buffer pool extension caches read operations, significantly increasing the access speed of data pages and shortening SQL query execution time.
The buffer pool extension greatly reduces the frequency of accessing data files on the instance's high-performance disk, which lowers disk bandwidth usage.
Enhanced instance stability
The disk that hosts the buffer pool extension has lower latency than a high-performance disk. This design significantly reduces the impact of high-performance disk I/O jitter on database operations, improving stability.
In the Buffer Pool Extension (BPE) Solution Experience Center, you can build a real RDS environment and observe real-time stress test results with and without BPE enabled. This feature is free to use. For more information, see RDS High-performance Disk I/O Acceleration (BPE) Performance Observation.
Cost-effectiveness
The Buffer Pool Extension (BPE) feature is free for supported RDS MySQL instances, and you do not need to make any changes to your application.
You can significantly improve instance I/O performance without any changes to your costs or business logic. For example, an RDS MySQL instance on the High-availability Edition using a general-purpose instance type with 8 vCPUs and 16 GB of memory can achieve a substantial increase in read and write QPS. For details, see Performance test.
How it works
The Buffer Pool Extension (BPE) feature for high-performance disks uses buffer pool extension technology to boost the performance of ESSDs at no additional cost. By separating hot and cold data, this feature extends the InnoDB buffer pool. It caches warm data pages in the buffer pool extension, which enables InnoDB to use multiple storage media flexibly and achieve higher QPS.
Architecture
The following figure shows the deployment architecture of BPE.
The buffer pool extension uses high-speed disks as a caching layer between memory and the high-performance disk. These high-speed disks offer lower I/O latency and superior performance compared to high-performance disks.
The high-speed disk is used only for caching.
The data file, binlog file, and redo file are stored on the high-performance disk.
By extending the InnoDB buffer pool, the system can cache data pages from the main buffer pool to the buffer pool extension, allowing InnoDB to flexibly use multiple caching media.
Implementation
When you enable the Buffer Pool Extension (BPE) feature, the system caches your instance's data pages to the buffer pool extension on the high-speed disk.
All data pages stored in the buffer pool extension are clean pages, meaning they are consistent with the data in the data file on the high-performance disk.
When reading a data page, the system follows these steps:
A client initiates a request to read a data page.
The system checks the in-memory buffer pool for the requested data page.
If the page is found (a cache hit), the system returns it to the client, and the process ends.
If the page is not found (a cache miss), the process continues to the next step.
The system checks the buffer pool extension on the high-speed disk for the data page.
If the page is found, the system copies it to the in-memory buffer pool and then returns it to the client. The process ends.
If the page is not found, the process continues to the next step.
The system reads the data page from the data file on the high-performance disk. The system then copies the page to the in-memory buffer pool and returns it to the client.
The read operation is complete.
Applicability
Engine: RDS MySQL
Version: MySQL 8.4, or MySQL 8.0 with a minor engine version of 20230914 or later
Edition: High-availability Edition or RDS Cluster Edition
Category: Standard Edition
Instance family: general-purpose
Regions and zones: (For availability, see the console. This list is for reference only.)
Region
Zone
China (Chengdu)
Zone B
China (Beijing)
Zone I
China (Shanghai)
Zone M
Zone N
China (Hangzhou)
Zone J
Billing
The Buffer Pool Extension (BPE) feature is free of charge for general-purpose RDS MySQL instances that use high-performance disks.
Precautions
Data loss in the buffer pool extension does not affect your business data, as it is durably stored on the data disk. Your instance continues to operate normally.
Enabling the BPE feature allocates a small amount of memory (4% of the
buffer_pool_size). Therefore, the defaultbuffer_pool_sizeis automatically reduced by 4%. If you need to modify thebuffer_pool_sizelater, adjust it based on your memory usage metrics.After you enable or disable the BPE feature, your instance is unavailable for about 15 seconds. We recommend that you perform this operation during off-peak hours.
Enabling the BPE feature provides a significant performance improvement. The effect is more noticeable on larger instance types. For example, an 8-vCPU, 16-GB general-purpose RDS MySQL instance on the High-availability Edition (Standard) can achieve a performance improvement of up to 103%.
Managing BPE
Enable BPE
Go to the Instances page, select your instance's region, and click the instance ID.
In the Basic Information section, to the right of Storage Type, click Configure Premium ESSD. In the dialog box that appears, turn on the Buffer Pool Extension (BPE) switch.
Disable BPE
Go to the Instances page, select your instance's region, and click the instance ID.
In the Basic Information section, to the right of Storage Type, click Configure Premium ESSD. In the dialog box that appears, turn off the Buffer Pool Extension (BPE) switch.
Performance test
Test preparation
Test method: Run read and write operations on an instance of the same specifications to compare the QPS with and without the BPE feature enabled.
Test data size: 300 GB
Test tool: Sysbench (For more information, see the official Sysbench documentation.)
NoteYou must create an ECS instance first. The test tool in this topic is installed on an ECS instance.
Test instances: Prepare two RDS MySQL instances on the High-availability Edition with a general-purpose instance type. One instance has 4 vCPUs and 8 GB of memory, and the other has 8 vCPUs and 16 GB of memory. The tests are run on each instance first with BPE disabled, and then again with BPE enabled.
Edition
Instance type code
vCPUs and memory
High-availability Edition
mysql.n2.large.xc
4 vCPUs, 8 GB
High-availability Edition
mysql.n2.xlarge.xc
8 vCPUs, 16 GB
Test method
This topic uses CentOS as an example for the performance test. If you want to install and test on a different operating system, see the official Sysbench documentation.
Install Sysbench
Run the following commands to install Sysbench.
git clone https://github.com/akopytov/sysbench.git
cd sysbench
git checkout 0.5
yum -y install make automake libtool pkgconfig libaio-devel
yum -y install mariadb-devel
./autogen.sh
./configure
make -j
make install
Test instance QPS performance
Parameters
The following table describes the parameters used in this test.
Parameter | Description |
--tables | The number of tables. |
--table_size | The number of rows in each table. |
--rand-type | The random number distribution type. |
--rand-spec-pct | The percentage of values that are considered "special" for the specified random distribution. |
--threads | The number of concurrent threads. |
--time | The duration of the test, in seconds. |
Test read performance
Prepare data: Run the following command to prepare 300 GB of data (30 tables with 40 million rows each).
sysbench oltp_read_only --tables=30 --table_size=40000000 --rand-type=special --rand-spec-pct=15 --threads=64 --time=100 prepareRun the read test: Run the following command to test the read performance of the instance.
sysbench oltp_read_only --tables=30 --table_size=40000000 --rand-type=special --rand-spec-pct=15 --threads=64 --time=100 run
Test write performance
Prepare data: Run the following command to prepare 300 GB of data (30 tables with 40 million rows each).
sysbench oltp_write_only --tables=30 --table_size=40000000 --rand-type=special --rand-spec-pct=15 --threads=64 --time=100 prepareRun the write test: Run the following command to test the write performance of the instance.
sysbench oltp_write_only --tables=30 --table_size=40000000 --rand-type=special --rand-spec-pct=15 --threads=64 --time=100 run
Test read/write performance
Prepare data: Run the following command to prepare 300 GB of data (30 tables with 40 million rows each).
sysbench oltp_read_write --tables=30 --table_size=40000000 --rand-type=special --rand-spec-pct=15 --threads=64 --time=100 prepareRun the read/write test: Run the following command to test the mixed read and write performance of the instance.
sysbench oltp_read_write --tables=30 --table_size=40000000 --rand-type=special --rand-spec-pct=15 --threads=64 --time=100 run
Test results
Test results for an RDS MySQL instance on High-availability Edition (Standard) using a general-purpose instance type with 4 vCPUs and 8 GB of memory:
Read performance: The QPS remains nearly unchanged with BPE enabled.
Write performance: The QPS increases by 30% with BPE enabled.
Read/write performance: The QPS increases by 26% with BPE enabled.
Test results for an RDS MySQL instance on High-availability Edition (Standard) using a general-purpose instance type with 8 vCPUs and 16 GB of memory:
Read performance: The QPS increases by 80% with BPE enabled.
Write performance: The QPS increases by 33% with BPE enabled.
Read/write performance: The QPS increases by 103% with BPE enabled.
