If you want to improve the disk I/O efficiency and the response speed of the system, you can use the I/O acceleration feature of general Enterprise SSDs (ESSDs) for an ApsaraDB RDS for MySQL instance. This feature helps increase the size of the buffer pool, implement cache acceleration, and improve the overall read and write performance of the RDS instance. This topic describes how the I/O acceleration feature works and how to use the feature. This topic also describes the performance test details of the feature.
Background information
Reading data from a disk to the memory and writing data in the memory to a disk are common I/O operations in database systems. Disk I/O operations are slower than in-memory operations and require more time to complete. If your database system experiences heavy read and write workloads or needs to process frequent read and write requests, the disk I/O may become a performance bottleneck.
ApsaraDB RDS launches general ESSDs, a new storage type. General ESSDs are compatible with all features of ESSDs, use a three-tier storage architecture to hierarchically manage, read, and write different types of data and cache, and support the I/O burst and I/O acceleration features to improve the I/O performance of ApsaraDB RDS instances.
I/O acceleration
Introduction
A buffer pool is an area of memory that is used to cache disk data. If a data block needs to be read from or written to a disk, the system searches for the data block from the buffer pool.
If the data block is found in the buffer pool, the system reads and writes data from and to the buffer pool.
If the data block is not found in the buffer pool, the system reads data from or writes data to the disk and then stores data in the buffer pool.
The buffer pool reduces the number of disk I/O operations in an efficient manner and improves the I/O performance of your RDS instance. The size of the buffer pool varies based on the memory size. If the memory resources are insufficient, the size of the buffer pool is limited. This affects the I/O performance.
The I/O acceleration feature of ApsaraDB RDS for MySQL provides Buffer Pool Extension (BPE) to expand the size of the buffer pool. The feature also improves the overall read and write performance of an RDS instance based on the kernel capabilities of the required database engine and allows the RDS instance to process more read and write requests within a specific period of time. This improves the disk I/O efficiency and the response speed of the system and implements cache acceleration.
Benefits
After BPE is supported for the I/O acceleration feature, a three-tier storage architecture is provided for ApsaraDB RDS for MySQL. This helps suit storage needs in different scenarios, allows you to make full use of various storage media of the Alibaba Cloud base, and improves the I/O performance of your RDS instance.
Improve the read and write performance and shorten the execution duration of SQL queries
BPE significantly improves the speed of data page access and shortens the execution duration of SQL queries by caching data.
BPE significantly reduces the frequency of accessing data files in general ESSDs of an RDS instance and reduces the bandwidth of general ESSDs.
Improve instance stability
The disk for which BPE is enabled has a lower latency than general ESSDs. BPE significantly reduces the impact of I/O jitters of general ESSDs on an RDS instance and improves the stability of the RDS instance.
In Alibaba Cloud Business Foundation System Solution Experience Center, you can view the real-time stress test results before and after the I/O acceleration feature is enabled based on actual RDS instances. The feature is provided free of charge. For more information, see Observe the performance and price of the I/O acceleration feature of general ESSDs.
Values
If you enable the I/O acceleration feature for an RDS instance, you are not charged additional fees and you do not need to modify the code of your applications.
The I/O performance of your RDS instance is significantly improved without the need to change the cost or application code. For example, an RDS instance runs RDS High-availability Edition and uses a general-purpose instance type that provides 8 cores and 16 GB of memory, and you enable the I/O acceleration feature for the RDS instance. In this case, the queries per second (QPS) for read and write operations is increased. For more information, see Performance test.
How the I/O acceleration feature works
The I/O acceleration feature of general ESSDs uses BPE to improve the performance of ESSDs without increasing costs. The I/O acceleration feature inherits the capability of separated storage of hot data and cold data, and increases the size of the InnoDB buffer pool to cache data pages and warm data to BPE. This way, InnoDB can flexibly use multiple storage media to increase the QPS.
Architecture
The following figure shows the deployment architecture of BPE.
BPE uses various high-speed disks as cache media between the memory and general ESSDs. Compared with general ESSDs, high-speed disks deliver lower I/O latency and better performance.
High-speed disks are only the cache media.
Data files, binary log files, and redo files are stored in general ESSDs.
The feature increases the size of the InnoDB buffer pool to cache data pages in the buffer pool to BPE. This way, InnoDB can flexibly use multiple storage media.
Implementation
After you enable the I/O acceleration feature, the data pages of an RDS instance are cached to BPE of high-speed disks.
All data pages cached to BPE are clean pages. These pages are consistent with the data of the data files stored in general ESSDs.
The following list describes the process of reading a data page:
The client initiates a request to read a data page.
The request goes to the buffer pool of the memory to query the data page.
If the data page is found in the buffer pool, the query result is returned to the client, and the query and read end.
If the data page is not found in the buffer pool, the system proceeds with Step 3.
The request goes to BPE of high-speed disks to query the data page.
If the data page is found in BPE, the data page is returned to the buffer pool, and then the query result is returned to the client. The query and read end.
If the data page is not found in BPE, the system proceeds with Step 4.
The request goes to the data table file of general ESSDs to query the data page. If the data page is found, the data page is returned to the buffer pool, and then the query result is returned to the client.
The query and read end.
Applicable scope
The RDS instance runs MySQL.
The RDS instance runs MySQL 8.0 and a minor engine version of 20230914 or later.
The RDS instance runs RDS High-availability Edition or RDS Cluster Edition.
The product type of the RDS instance is standard.
The RDS instance uses a general-purpose instance type.
The RDS instance resides in one of the following regions and zones. You can view the supported regions and zones in the ApsaraDB RDS console.
Region
Zone
China (Chengdu)
Zone B
China (Beijing)
Zone I
China (Shanghai)
Zone M
Zone N
China (Hangzhou)
Zone J
NoteAfter you enable the I/O acceleration feature on the ApsaraDB RDS buy page, you can check whether the feature is available in the Zone and Network of Primary Node section.
Billing rules
If you enable the I/O acceleration feature of general ESSDs for an RDS instance that uses a general-purpose instance type, you are not charged for the feature.
Usage notes
The I/O acceleration feature is provided free of charge for RDS instances that use general-purpose instance types.
Business data is persistently stored on data disks. Therefore, data loss in BPE does not affect the business data and the normal use of RDS instances.
The use of the I/O acceleration feature occupies a specific amount of memory. The amount is equal to 4% of the amount of memory specified by the buffer_pool_size parameter. The amount of memory that is allocated based on the default value of the buffer_pool_size parameter is automatically reduced by 4%. If you want to change the value of the buffer_pool_size parameter in subsequent operations, you can change the value based on the memory usage metric.
After you enable or disable the I/O acceleration feature for your RDS instance, a service interruption that lasts approximately 30 seconds occurs. We recommend that you enable or disable the feature during off-peak hours.
If you enable the I/O acceleration feature for your RDS instance, the performance of the RDS instance is significantly improved. The higher the specifications of the RDS instance, the greater the improvement of the instance performance. For example, your standard RDS instance runs RDS High-availability Edition and uses a general-purpose instance type that provides 8 cores and 16 GB of memory. In this case, the performance of the RDS instance can be improved by 103%.
The buffer pool reduces the number of disk I/O operations in an efficient manner and improves the I/O performance of your RDS instance. The size of the buffer pool varies based on the memory size. If the memory resources are insufficient, the size of the buffer pool is limited. This affects the I/O performance.
Use the I/O acceleration feature
Enable the I/O acceleration feature
Enable the I/O acceleration feature when you create an RDS instance
When you create an RDS instance, you can configure the parameters required to use the I/O acceleration feature and enable the I/O acceleration feature for the RDS instance. For more information, see Use the I/O acceleration feature of general ESSDs.
Enable the I/O acceleration feature for an existing RDS instance that meets the requirements to use the feature
Log on to the ApsaraDB RDS console and go to the Instances page. In the top navigation bar, select the region in which the RDS instance resides. Then, find the RDS instance and click the instance ID.
In the Basic Information section, click Configure General ESSD to the right of the Storage Type parameter. In the dialog box that appears, turn on I/O Acceleration.
Disable the I/O acceleration feature
Log on to the ApsaraDB RDS console and go to the Instances page. In the top navigation bar, select the region in which the RDS instance resides. Then, find the RDS instance and click the instance ID.
In the Basic Information section, click Configure General ESSD to the right of the Storage Type parameter. In the dialog box that appears, turn off I/O Acceleration.
Performance test
Test preparations
Method: Perform read and write operations on RDS instances that use the same instance type and compare the QPS performance before and after the I/O acceleration feature is enabled for the RDS instances.
Data volume: 300 GB.
Tool: Sysbench. For more information, see Sysbench documentation.
NoteCreate an Elastic Compute Service (ECS) instance in advance. In this example, the test tool is installed on the ECS instance. For more information, see Create and manage an ECS instance by using the ECS console.
Testing instances: two RDS instances that run RDS High-availability Edition and use a general-purpose instance type. One RDS instance provides 4 cores and 8 GB of memory, and the other RDS instance provides 8 cores and 16 GB of memory. The I/O acceleration feature is enabled for the RDS instances.
RDS edition
Instance type
Number of CPU cores and memory capacity
RDS High-availability Edition
mysql.n2.large.xc
4 cores, 8 GB
RDS High-availability Edition
mysql.n2.xlarge.xc
8 cores, 16 GB
Test method
In this section, the CentOS system is used as an example to perform the performance test. If you want to install Sysbench in other operating systems, follow the instructions provided in Sysbench documentation.
Install Sysbench
Run the following command 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 the QPS performance of an RDS instance
Description
The following table describes the parameters that are involved in the performance test.
Parameter | Description |
--tables | The number of tables. |
--table_size | The number of entries in each table. |
--rand-type | The type of the distribution that is used to generate random numbers. |
--rand-spec-pct | The percentage of special random numbers that are considered as special values. |
--threads | The number of concurrent threads. |
--time | The duration of the test. Unit: seconds. |
Test the read performance
Run the following command to generate 300 GB of data that includes 30 tables with 40 million rows per table:
sysbench oltp_read_only --tables=30 --table_size=40000000 --rand-type=special --rand-spec-pct=15 --threads=64 --time=100 prepareRun the following command to test the read performance of the RDS instance:
sysbench oltp_read_only --tables=30 --table_size=40000000 --rand-type=special --rand-spec-pct=15 --threads=64 --time=100 run
Test the write performance
Run the following command to generate 300 GB of data that includes 30 tables with 40 million rows per table:
sysbench oltp_write_only --tables=30 --table_size=40000000 --rand-type=special --rand-spec-pct=15 --threads=64 --time=100 prepareRun the following command to test the write performance of the RDS instance:
sysbench oltp_write_only --tables=30 --table_size=40000000 --rand-type=special --rand-spec-pct=15 --threads=64 --time=100 run
Test the read and write performance
Run the following command to generate 300 GB of data that includes 30 tables with 40 million rows per table:
sysbench oltp_read_write --tables=30 --table_size=40000000 --rand-type=special --rand-spec-pct=15 --threads=64 --time=100 prepareRun the following command to test the read and write performance of the RDS instance:
sysbench oltp_read_write --tables=30 --table_size=40000000 --rand-type=special --rand-spec-pct=15 --threads=64 --time=100 run
Test results
The following list describes the test results for the standard RDS instance that runs RDS High-availability Edition and uses a general-purpose instance type providing 4 cores and 8 GB of memory:
Read performance: The QPS performance is basically the same before and after the I/O acceleration feature is enabled.
Write performance: After the I/O acceleration feature is enabled, the QPS performance is improved by 30%.
Read and write performance: After the I/O acceleration feature is enabled, the QPS performance is improved by 26%.
The following list describes the test results for the standard RDS instance that runs RDS High-availability Edition and uses a general-purpose instance type providing 8 cores and 16 GB of memory:
Read performance: After the I/O acceleration feature is enabled, the QPS performance is improved by 80%.
Write performance: After the I/O acceleration feature is enabled, the QPS performance is improved by 33%.
Read and write performance: After the I/O acceleration feature is enabled, the QPS performance is improved by 103%.
