The Buffer Pool Extension (BPE) feature for ApsaraDB RDS for MySQL instances that use high-performance disks improves disk I/O efficiency and system responsiveness. It expands the buffer pool to accelerate caching, boosting the overall read and write performance of your RDS instance. This topic describes how BPE works, how to use it, and its performance test results.
Background
Reading data from a disk to memory and writing data from memory to a disk are common database I/O operations. Compared to in-memory operations, disk I/O is significantly slower and more time-consuming. For workloads with large-scale or frequent data access, I/O can become a performance bottleneck.
To address this, ApsaraDB RDS introduced a new storage type: high-performance disk. Compatible with all features of an ESSD disk, the high-performance disk uses a three-tier storage architecture to manage different types of data and cache. It also introduces the I/O burst and Buffer Pool Extension (BPE) features to boost I/O performance of your RDS instance.
Buffer Pool Extension (BPE)
Overview
A buffer pool is a memory area that caches data pages from disk. When the database needs to access a data page, it first checks the buffer pool:
If the page exists, it is read directly from the buffer pool.
If the page does not exist, it is fetched from the disk and then stored in the buffer pool.
This caching logic effectively reduces disk I/O operations and enhances instance performance. However, the size of the buffer pool is constrained by the instance's memory. Insufficient memory limits the cache size, which can negatively impact I/O performance.
ApsaraDB RDS for MySQL introduces the Buffer Pool Extension (BPE) feature to expand the buffer pool. By integrating with the RDS engine kernel, BPE significantly improves the overall read and write performance of an ApsaraDB RDS for MySQL instance. This allows the instance to process more commands in a given period, increasing I/O efficiency, reducing system response times, and accelerating caching.
Benefits
BPE introduces a three-tier storage structure for ApsaraDB RDS for MySQL, leveraging different storage media to meet various data storage needs and improve instance I/O performance:
Improved read/write performance and reduced SQL execution time
BPE accelerates data page access by caching read operations, which significantly reduces SQL execution time.
BPE reduces the frequency of accessing data files on the high-performance disk, which lowers disk bandwidth usage.
Enhanced instance stability
The disk where BPE resides has lower latency than a high-performance disk. This design significantly reduces the impact of I/O jitter from the high-performance disk on database operations, which improves stability.
In the Buffer Pool Extension (BPE) solution experience center, you can build real RDS resources to compare stress test results with and without BPE. This experience is free of charge. For more information, see RDS high-performance disk I/O acceleration (BPE) performance observation.
Business value
The Buffer Pool Extension (BPE) feature for ApsaraDB RDS for MySQL instances is available at no additional cost and requires no changes to your application.
You can significantly improve instance I/O performance with no changes to your costs or business logic. For example, for a general-purpose ApsaraDB RDS for MySQL instance on High-availability Edition with 8 cores and 16 GB of memory, enabling BPE can lead to substantial gains in QPS for read and write operations. For details, see Performance test.
How it works
The Buffer Pool Extension (BPE) feature for a high-performance disk leverages cache extension technology to enhance the performance of the ESSD-based storage without additional cost. It operates on the principle of separating hot and cold data. BPE extends the InnoDB buffer pool by caching warm data pages to a dedicated extension layer. This allows InnoDB to use a multi-tier storage architecture, resulting in significant QPS performance gains.
Architecture
The following figure shows the deployment architecture of BPE.
BPE uses high-speed disks as a caching medium, creating an intermediate storage tier between memory and the high-performance disk. These high-speed disks offer lower I/O latency and superior performance compared to the high-performance disk.
The high-speed disks are used only for caching.
Data files, binlog files, and redo files remain on the high-performance disk.
By extending the InnoDB buffer pool, data pages are cached to the Buffer Pool Extension, allowing InnoDB to flexibly use multiple caching media.
Data read process
When the Buffer Pool Extension (BPE) feature is enabled, instance data pages are cached in the Buffer Pool Extension on the high-speed disk.
The Buffer Pool Extension stores only clean pages, which are identical to the data in the data files on the high-performance disk.
The data page read process is as follows:
A client requests to read a data page.
The database first searches the in-memory buffer pool for the page:
If the page is found, the database returns it to the client.
If the page is not found, the database proceeds to the next step.
The database then searches for the page in the Buffer Pool Extension on the high-speed disk:
If found, the database copies the page to the buffer pool and returns it to the client.
If the page is not found, the database proceeds to the next step.
The database fetches the page from the data table files on the high-performance disk, copies it to the buffer pool, and returns it to the client.
Applicability
Engine: ApsaraDB RDS for MySQL
Version: MySQL 8.0 with a minor engine version of 20230914 or later
Edition: High-availability Edition or Cluster Edition
Product type: Standard
Instance family: general-purpose
Regions and availability zones (for reference only; refer to the console for the most up-to-date information):
Region
Availability 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 available at no additional cost for general-purpose ApsaraDB RDS for MySQL instances that use high-performance disks.
Precautions
The BPE feature is free of charge for general-purpose ApsaraDB RDS for MySQL instances that use high-performance disks.
Data loss in the Buffer Pool Extension does not affect your business data, which is stored persistently on the data disk. The instance can continue to operate normally.
Enabling BPE reserves a small portion of memory (4% of
buffer_pool_size). The defaultbuffer_pool_sizeis automatically reduced by 4% to accommodate this. If you modifybuffer_pool_sizelater, adjust the value based on memory usage metrics.Enabling or disabling BPE causes a brief service interruption of approximately 15 seconds. We recommend performing this operation during off-peak hours.
After you enable BPE, instance performance improves significantly. The larger the instance specification, the more noticeable the improvement. For example, the performance of an 8-core, 16 GB general-purpose High-availability Edition ApsaraDB RDS for MySQL instance can increase by up to 103%.
Using Buffer Pool Extension (BPE)
Enable BPE
Go to the Instances page, select a region at the top, and then click the ID of the target instance.
In the Basic Information section, find Storage Type and click High-performance Disk Settings. In the dialog box that appears, turn on the Buffer Pool Extension (BPE) switch.
Disable BPE
Go to the Instances page, select a region at the top, and then click the ID of the target instance.
In the Basic Information section, find Storage Type and click High-performance Disk Settings. In the dialog box that appears, turn off the Buffer Pool Extension (BPE) switch.
Performance test
Test setup
Test method: For a given instance specification, compare the QPS performance before and after enabling the BPE feature.
Test data size: 300 GB
Test tool: Sysbench (For more information, see the official Sysbench documentation.)
NoteYou must first create an ECS instance. In this test, the tool is installed on an ECS instance.
Test instances: This test used two general-purpose, High-availability Edition ApsaraDB RDS for MySQL instances with different specifications: one with 4 cores and 8 GB of memory, and another with 8 cores and 16 GB of memory. For each instance, performance was measured before and after enabling BPE.
Edition
Instance type
CPU and memory
High-availability Edition
mysql.n2.large.xc
4 cores, 8 GB
High-availability Edition
mysql.n2.xlarge.xc
8 cores, 16 GB
Test procedure
This test uses CentOS as an example. For instructions on how to install Sysbench on other operating systems, 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 QPS performance
Parameter description
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 "special" values for the specified random number 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, 40 million rows each).
sysbench oltp_read_only --tables=30 --table_size=40000000 --rand-type=special --rand-spec-pct=15 --threads=64 --time=100 prepareTest read performance: 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, 40 million rows each).
sysbench oltp_write_only --tables=30 --table_size=40000000 --rand-type=special --rand-spec-pct=15 --threads=64 --time=100 prepareTest write performance: 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, 40 million rows each).
sysbench oltp_read_write --tables=30 --table_size=40000000 --rand-type=special --rand-spec-pct=15 --threads=64 --time=100 prepareTest read/write performance: Run the following command to test the read/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
Results for the 4-core, 8 GB general-purpose High-availability Edition ApsaraDB RDS for MySQL instance:
Read performance: QPS was nearly identical before and after enabling BPE.
Write performance: QPS increased by 30% after enabling BPE.
Read/write performance: QPS increased by 26% after enabling BPE.
Results for the 8-core, 16 GB general-purpose High-availability Edition ApsaraDB RDS for MySQL instance:
Read performance: QPS increased by 80% after enabling BPE.
Write performance: QPS increased by 33% after enabling BPE.
Read/write performance: QPS increased by 103% after enabling BPE.
