Customer Voice: Data Storage Management Using GBase 8a Database Audit Logs

A high-performance, China-developed database built for big data analytics, GBase 8a is widely used in data warehousing and other scenarios. For such GBase 8a clusters, rapid growth in database storage often leads to operational challenges such as data migration and cluster scaling. To improve the storage resource utilization of GBase 8a databases and reduce server costs, this article proposes a data storage management solution based on GBase 8a audit logs, drawing from practical experience.

GBase 8a database audit logs record the SQL statements executed by the database, including information such as the submitting user, the database name to which the task belongs, the start time, the SQL statement, and the execution status. By leveraging these audit logs, you can obtain SQL execution details across the logical databases in the cluster, facilitating analysis and management.

Solution

This solution primarily parses the tables involved in the SQL statements within the audit logs and the point-in-time values of date fields in the WHERE conditions to build a picture of data access across different time points in each table. Then, based on the business characteristics of the system, it dynamically migrates data, achieving fine-grained, point-in-time management of each table in the database. The specific method is as follows:

1. Enable the audit log and dump features of GBase 8a, and periodically access the audit_log_express table to retrieve SQL execution records.
(1) From the audit_log_express table, retrieve records that meet the following conditions: SQL command type is insert, update, delete, or create table, and execution status is success;
(2) Retrieve the start time, SQL statement, and other details from each record.

2. Parse the SQL statements obtained in the previous step to identify the tables and filter conditions involved.
(1) Perform syntax analysis on the SQL statements;
(2) Extract the table names from the FROM and JOIN clauses, as well as the date fields, date operators (e.g., =, in, between and), and values from the WHERE conditions.

3. Store the results from the previous two steps in an access history table.
(1) Pre-create an access history table with the following fields: table name, date field name, date operator, point-in-time value, and last access date;
(2) If a record from step 2 does not exist in the access history table, insert it; if it already exists, update the last access date of the corresponding record.

4. Formulate a storage strategy based on application business characteristics, identify hot and cold data using the access history table, and perform point-in-time data management. According to the specific hot/cold data tiering plan, migrate point-in-time data that has not been accessed for a long time to archive storage, such as object storage.

Outcomes

By adopting this solution, you can identify the hot and cold status of point-in-time data in tables and dynamically migrate such data, enabling fine-grained point-in-time data management. This not only improves database storage utilization and saves high-spec physical server resources, but also enhances the execution efficiency of SQL within the database after data migration, reducing operational workload pressure.

Recommendations

It is reported that GBase has implemented features such as data lineage analysis and table access frequency tracking. Further, more sophisticated data management capabilities can be developed in the future. We recommend that GBase add support for migrating data to object storage, enabling automated archiving to and recovery from object storage through configuration, which would further simplify user operation.