PolarDB:Row-store snapshots

Supported versions

The compute node version must be 5.4.20-20240716 or later. The data node version must be xcluster8.4.19-20240630 or later. The database engine version must be MySQL 8.0.

Benefits

The row-store snapshot feature allows you to query historical data within the row-store snapshot retention period. You can use the row-store snapshot feature to perform the following actions:

• Restore data from an earlier point in time.

• Back up data at key points in time.

• Perform computational analysis on data at an earlier point in time.

The row-store snapshot feature is implemented based on undo logs. A limited storage size is allocated to undo logs. Therefore, the retention period of row-store snapshots is relatively short.

Billing

The row-store snapshot feature is free of charge. However, the feature results in more undo logs and increased storage usage. We recommend that you plan for sufficient storage capacity in advance.

Usage notes

• After you execute a DDL statement on a table for which the row-store snapshot feature is enabled, you cannot query the row-store snapshots created before the execution of the DDL statement.

• You cannot query row-store snapshots that have elapsed the retention period.

Preparations

Before you use the row-store snapshot feature, perform the following operations:

• Change the storage layer parameter loose_opt_flashback_area to true in the PolarDB console. For more information, see Parameter settings.

  Note

  • You must modify this parameter before you create a table that uses the row-store snapshot feature.

  • If you change this parameter to true after the table is created, you must rebuild the table for the row-store snapshot feature to take effect. For example, you can execute the OPTIMIZE TABLE statement on the table.

• Change the storage layer parameter loose_innodb_txn_retention to the expected value in seconds. This parameter specifies the retention period of row-store snapshots.

  Note

  The maximum retention period is 4,294,967,295 seconds. The default retention period is 1,800 seconds. We recommend that you set the retention period to no more than 259,200 seconds, which is 3 days.

Convert a timestamp into a snapshot point Timestamp Oracle (TSO) value

Each point in time can be a row-store snapshot point. You can use the following statements to convert a timestamp to a snapshot point TSO value:

SELECT TIME_TO_TSO("2024-07-24 10:00:00"); -- Convert 2024-07-24 10:00:00 to a snapshot point TSO value. By default, the time zone of the current session is used.
SELECT TIME_TO_TSO(NOW()); -- Convert the current timestamp to a snapshot point TSO value. By default, the time zone of the current session is used.

You can execute the following statement to specify the time zone used for the conversion function:

-- The second parameter specifies the time zone.
SELECT TIME_TO_TSO("2024-07-24 10:00:00", "+8:00");

Query historical data from a row-store snapshot

To query historical data from a row-store snapshot of a table, add the AS OF TSO {TSO} clause after the table name in the SELECT statement. Example:

SELECT * FROM tb1 AS OF TSO 7206138458723582016;

To restore data of a table from an earlier point in time to a temporary table by using a row-store snapshot, add the AS OF TSO {TSO} clause after the table name in the INSERT/REPLACE SELECT statement. Example:

CREATE TABLE tmp (
  -- The temporary table has the same schema as the original table.
);

INSERT INTO tmp SELECT * FROM tb1 AS OF TSO 7206138458723582016;

Example

1. Change the database parameters. For more information, see the "Preparations" section of this topic.

2. Create a table named tb1.

   CREATE TABLE tb1 (
     id INT PRIMARY KEY,
     a INT,
     gmt_created TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
     gmt_modified TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
   ) PARTITION BY KEY(id);

3. Insert data into the table and obtain snapshot point TSO values.

   INSERT INTO tb1 (id, a) VALUES (0, 0);
   
   -- Wait for 1 second.
   SELECT TIME_TO_TSO(NOW()); -- Convert the current timestamp to a snapshot point TSO value. In this example, the TSO value is labeled as TSO1.
   
   INSERT INTO tb1 (id, a) VALUES (1, 1);
   
   -- Wait for 1 second.
   SELECT TIME_TO_TSO(NOW()); -- Convert the current timestamp to a snapshot point TSO value. In this example, the TSO value is labeled as TSO2.
   
   INSERT INTO tb1 (id, a) VALUES (2, 2);

4. Query historical data from row-store snapshots.

   SELECT * FROM tb1 AS OF TSO {TSO1} ORDER BY id; -- (0, 0) is returned.
   SELECT * FROM tb1 AS OF TSO {TSO2} ORDER BY id; -- (0, 0), (1, 1) is returned.
   SELECT * FROM tb1 ORDER BY id; -- (0, 0), (1, 1), (2, 2) is returned.

5. Execute the INSERT SELECT AS OF TSO statement to restore data from an earlier point in time.

   CREATE TABLE tb1_tmp (
     id INT PRIMARY KEY,
     a INT,
     gmt_created TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
     gmt_modified TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
   ) PARTITION BY KEY(id);
   
   -- Use a row-store snapshot to restore data.
   INSERT INTO tb1_tmp SELECT * FROM tb1 AS OF TSO {TSO} FORCE INDEX(PRIMARY);