Help Center/ TaurusDB/ Best Practices/ Enabling Cold and Hot Data Separation
Updated on 2024-12-30 GMT+08:00
View PDF
Share

Enabling Cold and Hot Data Separation

This practice is tailored for partitioned tables and aims to help you perform scheduled cold data archiving on Huawei Cloud Elastic Cloud Servers (ECSs) using shell scripts, with a focus on partitions. For tables without partitions, you can configure cold tables on the TaurusDB console or using SQL statements.

You are advised to use INTERVAL RANGE to automatically expand partitions and, in conjunction with automatic cold table configuration, archive data from less frequently used partitions to OBS.

Operation Process

Figure 1 Flowchart

Procedure

  1. Create an ECS.

    For details, see Purchasing an ECS.
    • Ensure that the ECS is in the same region, AZ, VPC, and security group as a TaurusDB instance.
    • Data disks are not required.

  2. Log in to the ECS and download and install a MySQL client.

    For details about how to download and install a MySQL client, see How Can I Install the MySQL Client?

  3. Connect to the TaurusDB instance and check the structure and archiving status of a table.

    The following uses the sales table as an example.

    As shown in the following figure, the sales table is not archived as cold data.

  4. Use a shell script to configure cold tables automatically.

    Create the following script on the ECS to archive the partitions of the sales table at 01:00:00 every day from July 23, 2024.

    The following script uses the sales table as an example:

    #!/usr/bin/sh
passwd=******
user="root"
ip=*.*.*.*
conn="./mysql -u$user -h$ip -p$passwd"
database=test
table=sales
start_time="2024-07-23 01:00:00"
last_time=$start_time
partition_order=2
while [ true ]
  do
    res=$($conn -se"SELECT TIMEDIFF(current_timestamp(),'$last_time') > 0;")
    if [ $res -gt 0 ]; then
      partition_nums=$($conn -se"select count(1) from information_schema.partitions where table_schema=\"$database\" and table_name=\"$table\";")
      if [ $partition_order -gt $partition_nums ]; then
        last_time=$($conn -se"SELECT DATE_ADD('$last_time',INTERVAL 1 DAY);")
        continue
      fi
      partition_name=$($conn -se"select PARTITION_NAME from information_schema.partitions where table_schema=\"$database\" and table_name=\"$table\" and PARTITION_ORDINAL_POSITION = $partition_order;")


      $conn -e"CALL dbms_schs.make_io_transfer(\"start\", \"${database}\", \"${table}\", \"${partition_name}\", \"\", \"obs\");"
      if [ $? -ne 0 ]; then
        echo "archive failed"
      fi
      partition_order=$(($partition_order+1))
    else 
      sleep 10m
      continue
    fi
  done

  5. Connect to the TaurusDB instance and check the archiving status of the table.

    The following uses the sales table as an example.

    CALL dbms_schs.show_io_transfer("test", "sales", "_p20211001000000");

    CALL dbms_schs.show_io_transfer("test", "sales", "_p20211101000000");

    CALL dbms_schs.show_io_transfer("test", "sales", "_p20211201000000");

    If FINISH is displayed in the status column, the three partitions have been archived.