AnalyticDB:Improve I/O throughput and IOPS

I/O throughput and IOPS

I/O throughput: the amount of data that can be processed per unit time.

IOPS: the number of I/O operations that can be performed per unit time.

The I/O throughput and IOPS of AnalyticDB for PostgreSQL instances vary based on the node specifications, enhanced SSD (ESSD) capacity, and ESSD performance level.

• Node specifications: Instances that have different node specifications provide different maximum storage bandwidths. For example, an instance that has two cores provides up to 1.0 Gbit/s storage bandwidth and up to 125 MB/s I/O throughput.

• ESSD specifications: The maximum I/O throughput and maximum IOPS per ESSD vary based on the ESSD capacity and ESSD performance level. For example, a single PL2 ESSD provides up to 750 MB/s I/O throughput and up to 100,000 IOPS.

Relationship between node specifications and I/O performance

Different node specifications provide different maximum I/O performance to meet a variety of business requirements. The following table describes the maximum I/O throughput and maximum IOPS that are provided by four node specifications of AnalyticDB for PostgreSQL instances.

Relationship between ESSD capacity, ESSD performance level, and I/O performance

ESSDs are high-performance storage devices. AnalyticDB for PostgreSQL supports three ESSD performance levels: PL0, PL1, and PL2. Each performance level provides different maximum I/O performance. In addition, the ESSD capacity affects I/O performance.

The following table describes the maximum I/O throughput and maximum IOPS that are provided by three ESSD performance levels and how to calculate the I/O throughput and IOPS based on the ESSD capacity.

The I/O throughput and IOPS of ESSDs are proportional to the ESSD capacity but are limited by the maximum performance of each ESSD performance level. If your application requires higher I/O performance, you can select a higher ESSD performance level or increase the ESSD capacity. Each ESSD performance level has an upper limit of performance. If you increase only the ESSD capacity, you may fail to meet higher I/O performance requirements. In this case, you can refer to the following policies to optimize performance.

Performance optimization policies

When the I/O throughput or IOPS of your AnalyticDB for PostgreSQL instance experiences a performance bottleneck, we recommend that you optimize performance by using the following policies:

Upgrade node specifications

When the I/O throughput or IOPS of the current node specifications approaches or reaches the maximum value, we recommend that you upgrade the node specifications of your AnalyticDB for PostgreSQL instance. Higher node specifications provide higher computing capabilities and I/O performance and help you resolve I/O throughput or IOPS bottlenecks.

After you upgrade the node specifications of your instance, the database processing capabilities and user experience can significantly improve, and the response time can be reduced. This policy is ideal for I/O-intensive scenarios that involve large amounts of data and complex queries, such as data analysis and machine learning. For information about how to upgrade node specifications, see Change compute node configurations.

Node specification upgrades may require additional fees. Before you upgrade node specifications, we recommend that you perform comprehensive performance and cost assessments to find the solution that meets your business requirements and budget goals. In some cases, you can optimize database parameters or queries to improve performance without increasing costs. For more information, see Performance parameter optimization.

In summary, you can upgrade node specifications to improve the performance of your AnalyticDB for PostgreSQL instance based on your business requirements and budget goals. This is a major performance optimization method.

Increase the ESSD capacity and upgrade the ESSD performance level

When the I/O throughput or IOPS of the current ESSD performance level reaches the maximum value, we recommend that you increase the ESSD capacity. If performance bottlenecks persist, upgrade the ESSD performance level.

Increasing the ESSD capacity is a direct and effective method to improve I/O performance because I/O throughput and IOPS are related to the ESSD capacity. Capacity increase can improve the overall application performance by providing higher data storage capabilities, I/O throughput, and IOPS. Increasing the ESSD capacity is cost-effective and has minor impact on the application performance.

If you want to further improve I/O performance after you increase the ESSD capacity, you can upgrade the ESSD performance level. This improves I/O performance by providing higher maximum I/O throughput and IOPS. When an ESSD performance bottleneck occurs, you can upgrade the ESSD performance level to improve performance. However, this may require additional fees. Balance the relationship between improved performance and increased costs.

In summary, you can increase the ESSD capacity and upgrade the ESSD performance level to improve performance by balancing performance and costs.

Use cases

This section provides several cases about how storage performance of AnalyticDB for PostgreSQL instances is affected by node specifications, ESSD capacity, and ESSD performance level.

Case 1

AnalyticDB for PostgreSQL instance A has the following node specifications and ESSD specifications:

• Node specifications: 2 cores. The maximum storage I/O throughput is 125 MB/s, and the maximum IOPS is 10,000.

• ESSD performance level: PL1. The maximum IOPS per ESSD is 50,000, and the maximum I/O throughput per ESSD is 375 MB/s.

• ESSD capacity: 2,000 GiB.

In this scenario, the maximum storage I/O throughput of AnalyticDB for PostgreSQL nodes is limited to 125 MB/s and the maximum IOPS is limited to 10,000. This cannot maximize the ESSD capabilities.

Case 2

AnalyticDB for PostgreSQL instance B has the following node specifications and ESSD specifications:

• Node specifications: 16 cores. The maximum storage I/O throughput is 375 MB/s, and the maximum IOPS is 40,000.

• ESSD performance level: PL1. The maximum I/O throughput is 175 MB/s, which is calculated by using the following formula: min{120 + 0.5 × Capacity, 350}.

• ESSD capacity: 100 GiB.

In this scenario, the maximum storage I/O throughput of AnalyticDB for PostgreSQL nodes is limited to 175 MB/s by the ESSD capacity.

Case 3

AnalyticDB for PostgreSQL instance C has the following node specifications and ESSD specifications:

• Node specifications: 16 cores. The maximum storage I/O throughput is 375 MB/s, and the maximum IOPS is 40,000.

• ESSD performance level: PL1. The maximum IOPS per ESSD is 50,000, and the maximum I/O throughput per ESSD is 375 MB/s.

• ESSD capacity: 2,000 GiB.

In this scenario, the maximum storage I/O throughput of AnalyticDB for PostgreSQL nodes is limited to 375 MB/s and the maximum IOPS is limited to 40,000.

The preceding cases suggest that you must select AnalyticDB for PostgreSQL node specifications and ESSD performance levels that match each other. If the ESSD performance of AnalyticDB for PostgreSQL is much higher than the node performance, the ESSD performance may be limited by the node performance and cannot be fully utilized. To optimize storage performance and improve the overall system efficiency, you must select the appropriate AnalyticDB for PostgreSQL node specifications and ESSD performance level and increase the ESSD capacity.

The following table describes the solutions to the preceding cases.