ApsaraDB RDS:Use the data archiving feature of general ESSDs

Feature description

After you enable the data archiving feature of general Enterprise SSDs (ESSDs) for an RDS instance, you can execute DDL statements to convert data between ESSDs and OSS buckets. The data archiving feature allows you to store large tables that are infrequently accessed to OSS buckets and use the native InnoDB access method to read data from the tables. The following DDL statements are supported:

1. You can execute the following statement to archive data:

   ALTER TABLE $table_name ENGINE_ATTRIBUTE='{"OSS":"Y"}';

   Note

   ApsaraDB RDS for MySQL allows you to archive all data in a table to an OSS bucket. After you archive a table, you can no longer write data to the table. You can perform the SELECT, DROP TABLE, DROP DATABASE, and RENAME operations on the table.

2. You can execute the following statement to retrieve data:

   ALTER TABLE $table_name ENGINE_ATTRIBUTE='{"OSS":"N"}';

In ApsaraDB RDS for MySQL, archived tables retain the complete InnoDB indexes and are transactional. This allows offset queries and cache acceleration for archived tables and improves the efficiency of cold table access.

Benefits

After you enable the data archiving feature, you can store data of your RDS instance in an OSS bucket at low costs based on the mass storage, security, cost-effectiveness, and high reliability of OSS. The data archiving feature allows you to retain the InnoDB transaction capabilities and read data that is infrequently accessed. After you enable the data archiving feature, the storage cost is 80% lower the storage cost when you use ESSDs of performance level 1 (PL1).

Applicable scope

• Engine: MySQL

• Product type: standard and YiTian

• Storage type: general ESSD

  Note

  • If your RDS instance does not use general ESSDs, you can change the storage type of the RDS instance to general ESSD. For more information, see Change the storage type of an ApsaraDB RDS for MySQL instance from ESSD to general ESSD.

  • You cannot change the storage type from local SSD or standard SSD to general ESSD.

• Version: MySQL 8.0 with a minor engine version of 20240131 or later

• Edition: RDS Basic Edition, RDS High-availability Edition, and RDS Cluster Edition

• Unavailable regions: South Korea (Seoul), Australia (Sydney), and UK (London)

Billing rules

Limits

• If an RDS instance uses the serverless billing method, the data archiving feature is not supported for the RDS instance.

• Tables that are stored in an OSS bucket support the SELECT, DROP TABLE, DROP DATABASE, and RENAME operations. After you transfer the data of a table from an OSS bucket to an ESSD, the table supports all operations.

• You cannot store partitioned tables, tables that contain full-text indexes, encrypted tables, tables that contain foreign keys, and compressed tables in an OSS bucket. Tables that use table compression and page compression are considered compressed tables.

• Due to the access latency of OSS, the access latency of cold tables in an OSS bucket reaches 10 ms, which is higher than the access latency of ESSDs.

• The size of an archived table in your RDS instance cannot be smaller than 6 MB.

• DDL statements that are used to archive data are not executed by using the INSTANT algorithm. The execution duration of the DDL statements is affected by the table size, and metadata locks (MDLs) may be held during the execution.

• The data archiving feature may affect instance backups and specific features of the RDS instance. The following table describes the affected features.

  Feature	Description
  Single-digit second backup	If you want to enable the single-digit second backup feature, you must disable the data archiving feature. For more information, see Disable the data archiving feature. If you want to enable the data archiving feature, you must disable the single-digit second backup feature.
  Cross-region backup	If you want to enable the cross-region backup feature, you must disable the data archiving feature. For more information, see Disable the data archiving feature. If you want to enable the data archiving feature, you must disable the cross-region backup feature.
  Restoration of individual databases and tables	If you want to restore a database, you can restore only data that is not archived in the database.
  Backup set download	A downloaded backup set contains only data that is not archived.

  Note

  If you enable the data archiving feature, the speed of instance backup or restoration is decreased because the archived data needs to be restored.

Usage notes

OSS storage and reads consume memory resources. You must change the speeds and frequencies of data conversion and data access.

Enable and disable the data archiving feature

Enable the data archiving feature

You can enable the data archiving feature on the ApsaraDB RDS buy page or instance details page.

Disable the data archiving feature

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

2. On the instance details page, click Configure General ESSD. In the Data Archiving card of the dialog box that appears, turn off Data Archiving.

   

   Important

   • When you disable the data archiving feature, a transient connection that lasts for approximately 30 seconds occurs.

   • If you want to disable the data archiving feature, make sure that the OSS bucket does not contain archived tables. If the OSS bucket contains an archived table, you cannot disable the feature. In this case, you must convert the archived table into an online table or perform the DROP operation on the archived table and then disable the data archiving feature.

View archived data

1. View archived data in the console.

   1. Go to the Instances page. In the top navigation bar, select a region. Then, click the ID of the RDS instance that meets the requirements of the data archiving feature.

   2. In the left-side navigation pane, click Cold Storage Management. In the Archive Layer Settings section of the page that appears, view the databases for which the data archiving feature is enabled.

2. Execute SQL statements to view archived data.

   SELECT t.NAME AS tablespace_name, SUBSTRING_INDEX(t.NAME, '/', 1) AS database_name, SUBSTRING_INDEX(t.NAME, '/', -1) AS table_name, oss.OSS_OBJECT_NUM * oss.OSS_OBJECT_SIZE AS SIZE_IN_OSS_BYTES FROM information_schema.innodb_tables AS t JOIN information_schema.innodb_tablespaces_oss AS oss ON t.space = oss.space;

References

Parameters

The following table describes the parameters that are supported for the data archiving feature. You can modify parameter settings based on your business requirements. For more information, see Parameters supported by ApsaraDB RDS instances that run MySQL 8.0.

FAQ

Why does the [OSS] Size of tables is less than 6291456 error message appear when I execute the ALTER statement on my RDS instance?

The data size of an archived table on the RDS instance cannot be smaller than 6 MB.