ApsaraDB RDS:Use the I/O acceleration feature of general ESSDs for an ApsaraDB RDS for PostgreSQL instance

Background information

During data processing, ApsaraDB RDS for PostgreSQL reads data from disks to the memory, processes data in the memory, and then writes data back to the disks. Compared with in-memory operations, disk I/O operations are slower and require more time to complete. If your database system needs to process a large number of read and write requests, the disk I/O may become a performance bottleneck.

In this case, ApsaraDB RDS for PostgreSQL launches general Enterprise SSDs (ESSDs). General ESSDs are compatible with all features of ESSDs, use a three-tier storage architecture to hierarchically manage different types of data, and support the I/O burst and I/O acceleration features to improve the I/O performance of RDS instances. For more information, see General ESSDs.

Feature description

The I/O acceleration feature functions in the same way as the cache layer of the three-tier storage architecture of general ESSDs. The feature improves the read and write performance of your database system by using high-speed cache media. This accelerates SQL queries. The I/O acceleration feature of ApsaraDB RDS for PostgreSQL stores temporary tables and the temporary files generated during queries in high-speed cache media. This way, the database engine can perform read and write I/O operations on the temporary data in a quick manner. This significantly accelerates queries and improves the query performance of your RDS instance by 100% in complex query scenarios.

Benefits

The I/O acceleration feature is provided free of charge. The I/O acceleration feature helps significantly improve the queries-per-second (QPS) performance of your database system in applicable scenarios without additional costs or business changes. For more information, see Scenarios.

Scenarios

The I/O acceleration feature is suitable for the following scenarios:

• Complex operations that generate temporary data need to be performed. The complex operations include sorting, grouping, aggregation, and joining.

• Recursive queries need to be performed by using common table expressions (CTEs).

• Complex queries need to be performed when the required indexes are not available.

• Analytic queries on large tables or multiple tables need to be performed.

• Other workloads that use temporary tables are involved.

Prerequisites

The I/O acceleration feature is supported for an RDS instance that meets the following requirements:

• The RDS instance uses the subscription or pay-as-you-go billing method.

• The RDS instance runs PostgreSQL 11 or later.

• The RDS instance runs a minor engine version of 20231030 or later.

• The RDS instance runs RDS High-availability Edition.

• The RDS instance uses the standard product type.

• The RDS instance uses general ESSDs.

• The RDS instance belong to the general-purpose instance family.

• The RDS instance resides in one of the following regions and zones.

  Region	Zone
  China (Chengdu)	Zone B
  China (Beijing)	Zone I
  China (Shanghai)	Zone M
  	Zone N
  China (Hangzhou)	Zone J

  Note

  After 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.

How the I/O acceleration feature works

• Cache disks are high-speed caching media that act as a read and write performance intermediary between the memory and general ESSDs. Cache disks are suitable for storing temporary data in a database system because cache disks deliver higher read and write performance than general ESSDs.

• After the server on which your RDS instance resides receives SQL statements submitted by users, the query executor performs the queries. In this process, the query executor determines the location of data storage based on the tablespace of the data to be accessed.

  • Before you enable the I/O acceleration feature, all data can be stored only on cloud disks.

  • After you enable the I/O acceleration feature, the following scenarios occur:

    • If the data to be accessed is non-temporary data, such as normal tables and views, the query executor reads data from cloud disks to the memory for data processing.

    • If the data to be accessed is temporary data, such as temporary tables and files, the query executor reads and writes data from cache disks.

Temporary objects, such as temporary tables created on an RDS instance and temporary files generated during the execution of statements, are stored in cache disks. This accelerates the read and write operations on the temporary data within a specific period of time. The tablespaces of temporary objects are not explicitly specified.

Usage notes

• Cache disks can be used only to store temporary cache data. ApsaraDB RDS for PostgreSQL does not ensure the durability of the data stored in the rds_temp_tablespace tablespace. If you perform data migration operations such as specification changes on your RDS instance, the data stored in the rds_temp_tablespace tablespace is lost. Therefore, do not store non-temporary database objects in the rds_temp_tablespace tablespace.

• After you enable the I/O acceleration feature for an RDS instance, a specific amount of high-speed cache space is provided based on the instance type of the RDS instance. If the cache space is full, the No space left on device error message is reported. The error does not affect the execution of SQL statements on non-temporary data.

• 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.

Enable the I/O acceleration feature

1. Turn on I/O Acceleration.

   You can enable the I/O acceleration feature for an RDS instance when you create the RDS instance. You can also enable the I/O acceleration feature for an existing RDS instance that meets all requirements described in Prerequisites.

   • Enable the I/O acceleration feature when you create an RDS instance.

     When you create an RDS instance, you can configure parameters based on the requirements described in Prerequisites and enable the I/O acceleration feature for the RDS instance.

   • Enable the I/O acceleration feature for an existing RDS instance that meets the requirements described in Prerequisites.

     1. 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 ID of the instance.

     2. In the Basic Information section, click Configure General ESSD to the right of Storage Type. In the dialog box that appears, turn on I/O Acceleration.

2. Modify parameter settings.

   After you enable the I/O acceleration feature for an RDS instance, a tablespace named rds_temp_tablespace is automatically generated. The tablespace uses cache disks that support the I/O acceleration feature. If you want to use the tablespace as the default temporary tablespace, you must set the temp_tablespaces parameter to rds_temp_tablespace. For more information, see Modify the parameters of an ApsaraDB RDS for PostgreSQL instance.

   Note

   • You can execute the following SET statement to configure the parameter at the session level:

     SET temp_tablespace TO 'rds_temp_tablespace';

   • If you want the tablespace to use cloud disks, you can set the temp_tablespaces parameter to ''.

3. Use the cache acceleration feature.

   After you enable the I/O acceleration feature and modify the required parameter, the rds_temp_tablespace tablespace is automatically used when you create a temporary table.

   1. Connect to the RDS instance. For more information, see Connect to an ApsaraDB RDS for PostgreSQL instance.

   2. Execute the following statements to create test tables:

      -- Create a non-temporary table named test_table.
      CREATE TABLE test_table (di INT);
      
      -- Create a temporary table named test_temp_table.
      CREATE TEMPORARY TABLE test_temp_table (id INT);

   3. Query the tablespaces of the test tables.

      SELECT
          c.relname AS table_name,
          COALESCE(t.spcname, 'pg_default') AS tablespace_name
      FROM
          pg_class c
      JOIN
          pg_namespace n ON c.relnamespace = n.oid
      LEFT JOIN
          pg_tablespace t ON c.reltablespace = t.oid
      WHERE
          c.relkind = 'r'
          AND c.relname IN ('test_table', 'test_temp_table');

      Sample output:

         table_name    |   tablespace_name
      -----------------+---------------------
       test_table      | pg_default
       test_temp_table | rds_temp_tablespace
      (2 rows)

References

• For more information about other storage types that are supported by ApsaraDB RDS for PostgreSQL, see Storage types.

• For more information about the general ESSD storage type, see General ESSDs.

• If your workloads significantly fluctuate and the peak hours frequently change, you can enable the I/O burst feature for general ESSDs of an RDS instance. This way, the IOPS of general ESSDs is not subject to the maximum IOPS of the RDS instance. This helps meet business requirements for burstable IOPS during peak hours. For more information, see Use the I/O burst feature.

FAQ

What do I do if the ERROR: could not write to file "pg_tblspc/xxxx": No space left on device error message is displayed when I execute SQL statements?

The error occurs because the rds_temp_tablespace tablespace that uses cache disks is full. You can terminate the current session, reconnect to your RDS instance, and then re-execute the SQL statement that failed. After you terminate the current session, the system clears temporary files in the tablespce on your RDS instance.