AnalyticDB：Tiered storage of hot and cold data

Before you use tiered storage of hot and cold data, make sure that the following requirements are met:

• An AnalyticDB for MySQL cluster in elastic mode for Cluster Edition is created.

• The minor version of the cluster is 3.1.3.3 or later.

  Note

  • For information about how to view the minor version of an AnalyticDB for MySQL cluster, see How do I view the minor version of a cluster?

  • To update the minor version of an AnalyticDB for MySQL cluster, Submit a ticket and provide a time window for the update. A minor version update may take about 20 minutes to complete. During the update, your cluster can run as expected but may encounter transient connections several times. We recommend that you update the minor version of your cluster during off-peak hours and make sure that your application is configured to automatically reconnect to your cluster.

AnalyticDB for MySQL provides three policies for hot and cold data storage, which are cold storage, hot storage, and mixed storage.

• Cold storage is a cost-effective storage policy. When this storage policy is used, all data is stored in Object Storage Service (OSS).

  Note

  AnalyticDB for MySQL stores cold data in OSS in locally redundant storage (LRS) mode.

• Hot storage is a storage policy that can meet high access performance requirements. When this storage policy is used, all data is stored in SSDs.

• Mixed storage is a storage policy that allows specific partitions to be stored in SSDs and other partitions to be stored in OSS.

The distribution of hot and cold partitions may be changed in the following scenarios:

• Data is added, modified, or deleted. For more information, see the "Impact of data changes on the distribution of hot and cold partitions" section of this topic.

• The number of hot partitions is changed. For more information, see the "Impact of hot partition quantity changes on the distribution of hot and cold partitions" section of this topic.

In the following example, a new partition 20201110 is inserted into a table. This partition has the largest partition key value among all partitions of the table. As a result, the data in partition 20201105 that has the smallest partition key value is migrated from hot partitions to cold partitions and the data in partition 20201110 is migrated to hot partitions.

Assume that the number of hot partitions is N and you want to change the number of hot partitions to M.

• If M is greater than N, the data of M - N partitions is migrated from cold partitions to hot partitions.

  In the following example, the number of hot partitions is changed from five to six. As a result, the data in partition 20201104 that has the largest partition key value is migrated from cold partitions to hot partitions.

• If M is less than N, the data of N - M partitions is migrated from hot partitions to cold partitions.

  For example, the number of hot partitions is changed from five to four. As a result, the data in the partition that has the smallest partition key value is migrated from hot partitions to cold partitions.

You can use the table_usage table to query the distribution of hot and cold data storage. Examples:

• Query the distribution of hot and cold data storage in all tables.

  select * from information_schema.table_usage;

• Query the distribution of hot and cold data storage in a specific table.

  select * from information_schema.table_usage where table_schema='<schema_name>' and table_name='<table_name>';

The following table describes the fields involved in the table_usage table.

Note:

• The table_usage table is updated in real time. Values of the rt_total_size, rt_data_size, rt_index_size, hot_total_size, hot_data_size, hot_index_size, cold_total_size, cold_data_size, and cold_index_size fields vary based on the execution of INSERT, UPDATE, DELETE, and BUILD statements.

• If the values of the hot_total_size and cold_total_size fields are both 0 after data is loaded, data is still stored in the real-time engine and the value of the rt_total_size field indicates the volume of real-time data. You can execute the following BUILD statement to convert real-time data to partitioned data and then query the values of the hot_total_size and cold_total_size fields:

  build table <table_name>;

• A user-defined hot_partition_count field indicates the number of hot partitions in a single shard after list partitioning, whereas the hot_partition_count field queried from the table_usage table indicates the number of hot partitions after shards are unioned. If data partitions are differently distributed across shards, the value of the hot_partition_count field queried from the table_usage table may be greater than that of the user-defined hot_partition_count field.

  For example, assume that Table A contains Shard 1 and Shard 2, and hot_partition_count is set to 2. The following figure shows the distribution of data partitions in Table A.

  Shard 1: P4 and P5 are hot partitions. P1, P2, and P3 are cold partitions.

  Shard 2: P3 and P4 are hot partitions. P1 and P2 are cold partitions.

  The actual number of hot partitions is calculated by using the following formula: (P4, P5) Union (P3, P4) = (P3, P4, P5). Therefore, the actual value of hot_partition_count is 3.

You can execute the ALTER TABLE statement to change the storage policy of a table. For more information, see ALTER TABLE. The storage_policy_modify_progress table can be used to query the change progress of the storage policy.

• Query the change progress of the storage policy for all tables involved in the current cluster.

  select * from information_schema.storage_policy_modify_progress;

• Query the change progress of the storage policy for a specific table.

  select * from information_schema.storage_policy_modify_progress where table_schema='<schema_name>' and table_name='<table_name>';

The following table describes the fields involved in the storage_policy_modify_progress table.