:Data archiving

How it works

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

1. Archive a table

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

   Note

   RDS for MySQL supports archiving an entire table to OSS. After a table is archived, it becomes read-only. You can perform SELECT, DROP TABLE, DROP DATABASE, and RENAME operations, but you cannot write to the table.

2. Retrieve a table

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

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

Applicability

• Engine: RDS for MySQL

• Product type: Standard, YiTian

• Storage type: Premium ESSD

  Note

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

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

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

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

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

• Unavailable regions: Thailand (Bangkok), South Korea (Seoul), UK (London), and UAE (Dubai).

Billing

Limitations

• The data archiving feature is not supported for RDS for MySQL instances that use the serverless billing method.

• Tables stored in OSS are read-only and support only SELECT, DROP TABLE, DROP DATABASE, and RENAME operations. After you retrieve a table to an ESSD, full access permissions are restored.

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

• Due to the access latency of OSS, reading data from archived tables has a higher latency (in the 10 ms range) compared to reading from ESSDs.

• The data size of a table to be archived must be at least 6 MB.

• The time required to run the archiving DDL statement depends on the table size. During this operation, the table is readable but not writable.

• Enabling data archiving affects instance backups and other features. For more information, see the following table:

  Affected feature	Description
  Single-digit second backup	To enable single-digit second backup, you must disable the data archiving feature. Before enabling data archiving, you must disable the single-digit second backup feature.
  Cross-region backup	To enable cross-region backup, you must disable the data archiving feature. Before enabling data archiving, you must disable the cross-region backup feature.
  Restoration of individual databases and tables	When you restore a database, the operation restores only the non-archived data in that database.
  Backup set download	A downloaded backup set contains only non-archived data.

  Note

  For instances with data archiving enabled, backup and restoration operations take longer because the system must retrieve archived data from OSS.

Precautions

Archiving data to OSS and reading archived data from OSS consume memory. Adjust the speed and frequency of data transfers and access based on your workload.

Manage data archiving

1. Go to the Instances page. In the top navigation bar, select the region where your target instance is located. Then, find the instance and click its ID. Ensure the instance is supported by the data archiving feature.

2. On the instance details page, click Configure Premium ESSD next to Premium ESSD, and then turn the Data Archiving switch on or off.

   Important

   • When you enable or disable the data archiving feature, a primary/standby switchover occurs, causing a transient connection interruption that lasts about 30 seconds. 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 archived tables exist, you must first retrieve them by using a DDL statement or drop them before you can disable the feature.

View archived data information

MySQL 8.0 version 20241130 and later supports directly retrieving tablespace information from information_schema.innodb_tablespaces_oss, which is more efficient.

-- For 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 RDS for MySQL instances running 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 Overview of parameters for an RDS for MySQL 8.0 instance.

FAQ

Q: What causes the [OSS] Size of tables is less than 6291456 error when an ALTER DDL statement is executed in MySQL?

A: The data size of a table to be archived must be at least 6 MB.