Data archiving

How it works

After you enable the data archiving feature for an ApsaraDB RDS for MySQL instance that uses Premium ESSD, you can run DDL statements to move data between the Premium ESSD and OSS. This feature allows you to archive large, infrequently accessed tables to OSS and read the data using native InnoDB access methods. The following DDL statements are supported:

1. Archive a table

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

   Note

   ApsaraDB RDS for MySQL allows you to archive an entire table to OSS. After a table is archived, it becomes read-only. You can only perform SELECT, DROP TABLE, DROP DATABASE, and RENAME operations on it.

2. Retrieve a table

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

Archived tables in ApsaraDB RDS for MySQL retain their complete InnoDB index information and transactional properties. This enables fast offset queries and cache acceleration, which significantly improves the access efficiency for cold tables.

Prerequisites

• Engine: ApsaraDB RDS for MySQL

• Product type: Standard and Yitian

• Storage type: Premium ESSD

  Note

  • If your instance does not use Premium ESSD, you can change its storage type to Premium ESSD.

  • You cannot change the storage type from Premium Local SSD or standard SSD to Premium ESSD.

• Billing method: pay-as-you-go and subscription

• Engine 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: Thailand (Bangkok), South Korea (Seoul), UK (London), and UAE (Dubai)

Billing

Limitations

• The data archiving feature is not available for serverless ApsaraDB RDS for MySQL instances.

• Tables stored in OSS are read-only and support only the SELECT, DROP TABLE, DROP DATABASE, and RENAME operations. After you retrieve a table to a Premium ESSD, it supports all operations.

• You cannot archive partitioned tables, tables with full-text indexes, encrypted tables, tables with foreign keys, tables with triggers, or compressed tables (including table and page compression) to OSS.

• Due to the access latency of OSS, querying cold tables stored in OSS may experience higher latency, of about 10 ms, compared to querying tables on a Premium ESSD.

• A table must be at least 6 MB to be archived.

• The execution time for an archive DDL statement depends on the table size. During the execution, the table is readable but not writable.

• Enabling data archiving affects instance backups and other features as described in the following table:

  Unsupported feature	Description
  Single-digit second backup	To use Single-digit second backup, you must first disable the data archiving feature. Before you can enable the data archiving feature, you must make sure that Single-digit second backup is disabled.
  Cross-region backup	To use cross-region backup, you must first disable the data archiving feature. Before you can enable the data archiving feature, you must make sure that cross-region backup is disabled.
  Restoration of individual databases and tables	When you restore a database, you can restore only the non-archived data in that database.
  Backup set download	A downloaded backup set contains only non-archived data.

  Note

  For an instance with data archiving enabled, backup and restoration operations take longer because the system must retrieve and restore archived data.

Usage notes

Archiving data to OSS and reading archived data from OSS consume memory. You must adjust the speed and frequency of data conversion and data access based on your workload.

Enable and disable data archiving

1. Go to the Instances page, select a region in the top navigation bar, and then click the ID of the target instance. The instance must meet the prerequisites for data archiving.

2. On the instance details page, click Configure Premium ESSD to the right of Premium ESSD, and then toggle the Data Archiving switch.

   Important

   • When you enable or disable the data archiving feature, a primary/standby switchover occurs, which causes a temporary interruption of about 30 seconds. We recommend that you perform this operation during off-peak hours and ensure that your application has an automatic reconnection mechanism.

   • To disable the data archiving feature, you must ensure that no archived tables exist in OSS. If there are archived tables in OSS, you must first run a DDL statement to retrieve them to the Premium ESSD or drop them before you can disable the feature.

View archived data

For ApsaraDB RDS for MySQL 8.0 instances with a minor engine version of 20241130 or later, you can directly query information_schema.innodb_tablespaces_oss to retrieve tablespace information more efficiently.

-- For ApsaraDB RDS for MySQL instances running a version earlier than 20241130
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;

-- For ApsaraDB RDS for MySQL instances running version 20241130 or later
SELECT NAME AS tablespace_name, SUBSTRING_INDEX(NAME, '/', 1) AS database_name, SUBSTRING_INDEX(NAME, '/', -1) AS table_name, OSS_PART_SIZE AS SIZE_IN_OSS_BYTES FROM information_schema.innodb_tablespaces_oss;

References

The data archiving feature introduces several parameters that you can modify based on your business requirements. For more information, see List of available parameters for RDS MySQL 8.0.

FAQ

Q: Why does running an ALTER DDL command in MySQL result in the error [OSS] Size of tables is less than 6291456?

A: A table must be at least 6 MB to be archived.