By Cuanye and Xiahua
Flashback technology, as the name suggests, refers to a capability provided by the database system to allow users to view and restore to the status at a certain point in the past, or to undo specific operations without relying on traditional backup and recovery mechanisms. This function greatly enhances the flexibility and reliability of the database, providing database administrators and developers with an effective means to quickly respond to errors, audit data changes, and diagnose problems.
Currently, flashback queries are typically applied in the following scenarios:
• Accidental Operation Repair: Quickly restore lost data caused by accidental deletion or update.
• Data Recovery Drill: Test and verify data recovery without affecting the production environment.
• Problem Diagnosis: Trace back historical data status to quickly locate and resolve database performance issues or exceptions.
• Compliance Audit: Provide historical records of data changes to support audit and legal investigation requirements.
As an enterprise-level database capability, flashback queries hold the following solutions in the industry:
It generates a set of operations that are contrary to historical data changes and replays them to the original database or a backup database. A typical example is MySQL. MySQL can flash the database back to a certain point in the past through binlogs and related third-party tools (such as mysqlbinlog_flashback and binlog2sql). This solution has certain limitations. For example, the format of binlogs is usually required to be FULL, which is inflexible in use and inefficient in performance and execution efficiency.
A typical database is MariaDB. System-Versioned Tables are a special kind of table, whose core idea is to store both the current version and the historical version of the record on the table. To distinguish all versions, some columns are added to the table by default to indicate version information, such as the generation time of the row record or the transaction ID number generated by the row record. This solution provides good flexibility. Users can use SQL to read data of a specific historical version or to directly perform data flashback. At the same time, the solution allows control over whether to retain historical version data at the table level. However, this solution has two defects, leading to its lack of widespread use:
A typical database is Oracle. The same as System-Versioned Tables, this solution also supports operating and accessing historical version data through SQL queries. However, compared with the System-Versioned Tables solution, the notable feature of this solution is the physical isolation of historical versions, that is, historical versions are maintained by the dedicated area Undo, while the current active data continues to reside in the user's online tablespace. This separation strategy optimizes resource utilization, ensuring the performance of the main data area and reducing the load that may be caused by the accumulation of historical data. Efficient organization and automatic lifecycle management of historical data further reduce maintenance pressure, eliminating the need for users to manually intervene in the data cleaning and space reclamation process.
Through the above analysis, users are making a difficult choice. So is there a solution that can take into account space, performance, and usage costs without any business transformation? We believe the answer lies in the PolarDB-X multi-level flashback solution.
Core Technology of PolarDB-X Storage Engine: Lizard Multi-level Flashback
As shown in the preceding architecture diagram, relying on the capabilities of the PolarDB-X Lizard transaction engine, user data versions are stored in multiple areas based on layers. The core areas, the level-1 flashback and the level-2 flashback areas, support the multi-dimensional demands of users for flashback queries. The design of the level-1 flashback and the level-2 flashback will be discussed below.
In the past, the Undo management of the MySQL InnoDB storage engine was oriented to online active views, and the design based on Read View could not well express the version information of transactions, so the design does not support flashback capability. Based on the Lizard transaction system, the PolarDB-X storage engine supports the level-1 flashback capability through three components.
Querying visibility depends on the database view and the real version information of the corresponding record. The version information for the transaction that produced the record is maintained by the LTS component of the PolarDB-X Lizard transaction engine.
The PolarDB-X Lizard transaction engine uses the system commit number (SCN) to indicate the sequence of transaction commits, and the global commit number (GCN) to indicate the global commit sequence of distributed transactions. To meet the strong demand on the user side for physical time, the PolarDB-X Lizard transaction engine provides internal tables and a tracking mechanism to maintain the mapping between logical time and physical time.
The PolarDB-X Lizard transaction engine takes over the flow of historical version data in the Undo area. The cleanup of historical version data no longer depends only on the current visibility, but will take future visibility into consideration. Future visibility can be measured in two dimensions:
• Time Dimension
Ensure that historical version data remains visible within a certain period of time, determined by the innodb_undo_retetion global parameter.
• Spatial Dimension
Retain as much historical version data as possible if there is surplus space, determined by the innodb_undo_space_reserved_size global parameter.
Enable the level-1 flashback capability by setting global parameters
SET GLOBAL innodb_undo_retention = 1800;Access the level-1 flashback area through flexible SQL statements
SELECT * FROM t AS OF TIMESTAMP $timestamp
SELECT * FROM t AS OF SCN $scn
SELECT * FROM t AS OF GCN $gcnThe above AS OF clauses apply to the table t and support standard SQL syntax. For example, you can integrate a WHERE clause later.
The level-1 flashback area maintains all historical versions within a period of time (such as 1,800s). The features of this area are as follows:
1) The data in the level-1 flashback area is organized into a version chain. Through chain backtracking, the caller can query any desired version information.
2) The level-1 flashback area occupies the online storage space and uses index nodes, contributing to the efficient and direct historical version search.
3) The level-1 flashback area is instance-oriented and all modified versions of the database are retained. The area has almost the same query efficiency as ordinary queries and retains the historical version information of the entire database.
However, the level-1 flashback area, with an instance-level capability:
1) occupies the user's online storage space.
2) retains some index nodes, which may have an impact on ordinary online queries.
3) does not have fine-grained table-level flashback management capabilities. Therefore, the retention time should not be set too long. At present, level-1 flashback queries are widely used in the PolarDB-X distributed database for consistent flashback queries in a short period of time.
The level-2 flashback area is an area that PolarDB-X redesigns outside the level-1 flashback area. After the user creates a Flashback Area table, its historical versions are moved to the level-2 flashback area if the retention period of the previous versions exceeds that of the level-1 flashback. The historical version of the level-2 flashback area is not managed by the level-1 area and is maintained by an independent space reclamation system. Since the level-2 flashback area is physically isolated from the level-1 flashback area, the level-2 flashback storage area may be stored on inexpensive storage devices, such as Alibaba Cloud Object Storage Service (OSS).
The level-2 flashback area also adopts an efficient space self-management mode. Users only need to set the retention period, and historical versions will be properly maintained. Since the level-2 flashback area only retains the historical version data of a specific table, its data space usage is greatly reduced. This means that the historical version data of the level-2 flashback area can be retained for a long time, such as 7 days.
A new type of table, the Flashback Area Table, is introduced. Flashback Area Table has the following features:
1) Relying on the flexible control capabilities provided by the Lizard transaction engine, users can choose to set tables as the Flashback Area Table.
2) Flashback Area is an attribute of a table, whose lifecycle is properly maintained. For example, it can be synchronized to the secondary database, producing reasonable results.
3) The historical versions of the Flashback Area Table will be moved to the level-2 flashback area.
4) A large number of index nodes in the Flashback Area Table will be cleared as the corresponding historical versions enter the level-2 flashback area, effectively reducing the impact of enabling flashback queries on regular online business.
Users can specify to create a user table that supports the Flashback Area capability in the following ways:
mysql> SET OPT_FLASHBACK_AREA = 1;
mysql> CREATE TABLE t (id INT PRIMARY KEY, sec INT, KEY(sec));
# Observe table attributes through INFORMATION_SCHEMA.INNODB_TABLE_STATUS
# flashback_area to determine whether the capability has been enabled correctly
mysql> SELECT * FROM mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_TABLE_STATUS;
+----------------+------------------+------------------------+
| SCHEMA_NAME | TABLE_NAME | options |
+----------------+------------------+------------------------+
| test | t1 | ...flashback_area=1... |
+----------------+------------------+------------------------+To perform a level-2 flashback query, users can use the following methods:
mysql> SET query_via_flashback_area = 1;
mysql> SELECT * FROM t1 AS OF TIMESTAMP $snapshot_gcn;
+----+----+
| id | c1 |
+----+----+
| 1 | 10 |
+----+----+The retention period of historical versions in the level-2 flashback area is set by innodb_txn_retention instead of innodb_undo_retention:
mysql> SET GLOBAL innodb_txn_retention = 604800;The level-2 flashback overcomes the typical problems encountered on the level-1 flashback and has the following advantages:
1) Users can flexibly choose table-level management.
2) Index nodes in the online table are greatly reduced.
3) It is independent of the level-1 flashback area so historical data can be retained for a long time to meet the requirements of flashback queries.
The PolarDB-X Lizard transaction engine enables efficient management of historical data and optimized flashback queries by introducing a hierarchical data version storage structure. The structure is divided into three levels: the current data area, the level-1 flashback area, and the level-2 flashback area, which together constitute the multi-dimensional time maintenance system of the database.
The PolarDB-X Lizard transaction engine optimizes access to the transaction status information for the latest data version to ensure the efficiency of regular query and update operations. This area is the access hotspot in the entire data storage structure.
It provides temporary storage of historical versions within a time window to support short-term data backtracking queries. Its design enables PolarDB-X to support distributed consistent flashback reads, reinforcing the data integrity and consistency of the database when handling concurrent operations.
The level-2 flashback area stores historical version data for a longer time span, alleviating the pressure of long-term historical data on the primary storage area and effectively reducing the maintenance cost of the online hot data area. Its design allows old data to be migrated to lower-cost storage media. This not only saves storage costs but also provides flexible and efficient support for flashback operations. Users can perform flashback queries using standard SQL statements without specifying the location where the data is stored. On the other hand, this area supports the maintenance of historical data in table units, providing a more flexible and universal method.
This hierarchical historical data storage architecture of PolarDB-X is the core of efficient flashback queries, an innovation that allows the database to maintain long-term historical versions while ensuring that query performance and data availability are not affected. This provides users with convenient data backtracking capabilities, making data recovery and change tracking easier and more reliable.
The following section compares the current mainstream flashback solutions in terms of space cost, performance cost, and usage cost.
In modern database systems, the tiered policy of storage cost is generally based on the performance index and SLA of the storage medium and its economic benefits. According to the business form, storage services can be divided into two types:
| Business Type | Storage Form | Overview | Cost |
|---|---|---|---|
| Online business storage space | Local SSD or equivalent disk | For online business | 0.8 ~ 1.6 per GB per month |
| Offline business archiving space | OSS | For archiving or backup business | 0.1 ~ 0.2 per GB per month |
The flashback solution based on database logs shows natural advantages in storage cost control. Log files in such a solution are usually stored on lower-cost storage media, such as Alibaba Cloud OSS or Amazon S3. This type of storage media is significantly less costly than online high-performance storage space and is suitable for long-term data retention. Inexpensive storage solutions make it possible to retain historical data for longer periods of time, such as PolarDB-X which typically retains binlog files for up to 7 days.
In the System-Versioned Tables solution, historical version data is stored in the user's online tablespace, directly occupying valuable high-performance online storage resources.
Although the Undo solution improves management flexibility by separating historical data and online tablespace, it still occupies high-performance online storage. This design will drive up storage costs as historical data accumulates, affecting overall costs. Practical tests show that in extreme scenarios such as the Sysbench Write Only scenario, the rapid growth of Undo space (about 90 GB per hour) is bound to result in significant storage cost increases. Given this, in practice, the retention time of historical data is usually limited in case of storage costs out of control. For example, the retention time PolarDB-X recommends is 1,800 seconds.
It supports multi-dimensional and multi-gradient storage cost design. Currently, it is available for physical isolation from ordinary Undo areas and will support storage isolation in the future.
Flashback queries have a noticeable impact on online business queries.
The flashback solution based on database logs is non-intrusive to the online tablespace. Therefore, it does not have any additional impact on online business.
System-Versioned Tables store all historical versions in the user's online tablespace. These historical records are invisible and meaningless for most online business queries, but the query overhead is not saved accordingly. Therefore, the query performance of System-Versioned Tables gradually decreases over time.
Although the Undo solution separates historical versions from online tablespace, significantly reducing the impact on online business, our tests show that it still has an impact on the performance of online business in some extreme scenarios.
The intrusion into online tables is minimized and experimental results show that online business is not affected in extreme stress testing scenarios.
In terms of usage cost, we can compare them from three dimensions: access interface, space reclamation, and table-level management.
1) Access Interface
The flashback solution based on database logs has overwhelming advantages in terms of performance cost and space cost, but it is almost unusable due to its poor usage and various restrictions. In a typical case, when users need to restore a table, it is necessary to download database logs, use a third-party tool for reverse resolution of the logs, and then filter the reverse DML and replay it on the original database. The entire operation cannot be rolled back or stopped with low restoration efficiency.
2) Space Reclamation
Its space reclamation depends on the task flow of the management platform, which is not controlled by the database itself.
3) Table-level Management
Only limited support for table-level management: Flashback at the table level is supported. However, since the log organization is usually not organized by table objects, it is less efficient to perform the flashback on table objects. Log archiving at the table level is not supported.
1) Access Interface
It supports flexible SQL statements for version backtracking and data restoration, and transactional operations, which is safe and reliable. It has strong advantages in ease of usability and the usage cost.
2) Space Reclamation
Users are required to explicitly maintain and manage historical versions, which have high maintenance costs.
3) Table-level Management
It supports the retention of table-level historical versions.
1) Access Interface
It supports flexible SQL statements and transactional operations.
2) Space Reclamation
It supports the automatic space reclamation mechanism.
3) Table-level Management
This feature is instance-oriented and does not support historical version management at the table level.
1) Access Interface
It also supports flexible SQL statements and transactional operations. It is available for in-place access on the database side, which blocks the details of the multi-level flashback area without crossing multiple platforms.
2) Space Reclamation
Multiple layers support the automatic space reclamation mechanism.
3) Table-level Management
It supports historical version management at the table level and retains historical version data for an extremely long period of time. The following table shows a comparison of the current mainstream flashback solutions:
| Flashback Solution | Space Cost | Usability | Table-level | Business Table Intrusion | Flexibility | Retention Period | Space Self-management | Backtracking Period |
|---|---|---|---|---|---|---|---|---|
| Based on Database Logs | Low | Low | Limited Support | N/A | No Flexibility | High | N/A | Long |
| System-Versioned Tables | High | High | Full Support | High | Support for SQL | Low | N/A | Fast |
| Undo | High | High | No Support | High | Support for SQL | Medium | Yes | Fast |
| PolarDB-X Multi-level Flashback | Medium | High | Full Support | Low | Support for SQL | High | Yes | Fast |
Core Technology of PolarDB-X Storage Engine | Lizard Lock-free Backup
ApsaraDB - January 17, 2024
ApsaraDB - January 23, 2024
ApsaraDB - October 17, 2024
ApsaraDB - November 7, 2024
ApsaraDB - June 19, 2024
ApsaraDB - June 12, 2024
PolarDB for Xscale
Alibaba Cloud PolarDB for Xscale (PolarDB-X) is a cloud-native high-performance distributed database service independently developed by Alibaba Cloud.
Learn More
PolarDB for PostgreSQL
Alibaba Cloud PolarDB for PostgreSQL is an in-house relational database service 100% compatible with PostgreSQL and highly compatible with the Oracle syntax.
Learn More
PolarDB for MySQL
Alibaba Cloud PolarDB for MySQL is a cloud-native relational database service 100% compatible with MySQL.
Learn More
Database for FinTech Solution
Leverage cloud-native database solutions dedicated for FinTech.
Learn MoreMore Posts by ApsaraDB
