Transaction ID processing method, program product, storage medium and electronic equipment

Abstract

Embodiments of the present application provide a transaction ID processing method, a program product, a storage medium and an electronic device, the method comprising: exporting metadata in a system table through index query, the system table being used for storing metadata in a large-scale parallel processor (MPP) cloud data warehouse; invalid metadata in a basic database is recognized and cleaned, and the basic database is used for storing the metadata in the system table in a key-value pair mode; and performing vacuum cleaning operation on the system table to release the transaction ID of the terminated transaction. Through the embodiment of the invention, the problem that excessive transaction IDs of terminated transactions cannot be effectively released in related technologies is solved.

Description

Technical Field

[0001] This application relates to the field of computer technology, and more specifically, to a method for processing transaction IDs, a program product, a storage medium, and an electronic device. Background Technology

[0002] In cloud data warehouses with massively parallel processor architectures (MPP), the management of transaction identifiers (XIDs) is a crucial mechanism for ensuring stable database operation and transaction correctness. MPP cloud data warehouses provide high-performance and highly scalable solutions for data-intensive applications by processing large amounts of data in parallel. Their transaction management mechanism is based on Multi-Version Concurrency Control (MVCC), using unique XIDs to control the execution order of parallel transactions and the visibility of data rows.

[0003] In traditional Greenplum databases, XIDs are allocated sequentially based on the start time of transactions, and the range of XIDs is finite. Once the preset maximum value (4.2 billion) is reached, without a proper XID reclamation mechanism, the database will face XID exhaustion, ultimately leading to service unavailability. Therefore, XID reclamation is particularly important in MPP cloud data warehouses, especially in architectures with shared metadata and compute-storage separation, where the volume of metadata is massive and the XID growth rate is rapid, traditional XID reclamation methods may not be able to meet the demands. Summary of the Invention

[0004] This application provides a method, program product, storage medium, and electronic device for processing transaction IDs, in order to at least solve the problem in the related art of not being able to effectively release too many transaction IDs of terminated transactions.

[0005] According to one embodiment of this application, a method for processing transaction IDs is provided, comprising: exporting metadata in a system table through an index query, wherein the system table is used to store metadata in a massively parallel processor (MPP) cloud data warehouse; identifying and cleaning up invalid metadata in a base database, wherein the base database is used to store metadata in the system table in the form of key-value pairs; and releasing the transaction identifier ID of terminated transactions by performing a vacuum cleanup operation on the system table.

[0006] In one exemplary embodiment, before exporting metadata from the system table via an index query, the method further includes: exporting data from the underlying database and backing it up.

[0007] In one exemplary embodiment, before performing a vacuum cleanup operation on the system table to release the transaction identifier ID of the terminated transaction, the method further includes: creating a new business table by reorganizing the data of the business table with a larger age value in the MPP cloud data warehouse, so that the maximum age value of the business table in the MPP cloud data warehouse has been updated and reduced.

[0008] In one exemplary embodiment, identifying and cleaning up invalid metadata in the underlying database includes: scanning the underlying database using an underlying database management tool to identify and delete invalid key-value pairs in order to clean up invalid metadata.

[0009] In one exemplary embodiment, releasing the transaction identifier ID of a terminated transaction by performing a vacuum cleanup operation on the system table includes: not updating the table directory table of the system table when performing a vacuum cleanup operation on the first N-1 system tables, where N is a positive integer greater than 1; and updating the table directory table of the system table when performing a vacuum cleanup operation on the Nth system table.

[0010] In one exemplary embodiment, after performing a vacuum cleanup operation on the system table to release the transaction identifier ID of the terminated transaction, the method further includes: exporting the data of the system table after the vacuum cleanup operation and comparing it with the data in the backed-up base database to determine whether the vacuum cleanup operation was accurate.

[0011] According to another embodiment of this application, an apparatus for processing transaction IDs is also provided. The apparatus includes: an export module for exporting metadata in a system table through an index query, wherein the system table is used to store metadata in a massively parallel processor (MPP) cloud data warehouse; a cleanup module for identifying and cleaning up invalid metadata in a base database, wherein the base database is used to store metadata in the system table in the form of key-value pairs; and a processing module for releasing the transaction identifier IDs of terminated transactions by performing a vacuum cleanup operation on the system table.

[0012] According to another embodiment of this application, a computer program product is also provided, including a computer program configured to have a processor perform the steps in any of the above method embodiments.

[0013] According to yet another embodiment of this application, a computer-readable storage medium is also provided, wherein a computer program is stored in the computer-readable storage medium, and the computer program is configured to execute the steps in any of the above method embodiments by a processor.

[0014] According to yet another embodiment of this application, an electronic device is also provided, including a memory, a processor, and a computer program stored in the memory and executable on the processor, wherein the processor is configured to execute the computer program to perform the steps in any of the above method embodiments.

[0015] The index-based query method used in this embodiment exports metadata from the system tables, avoiding full table scans. This significantly improves data export speed, especially in cases of severe metadata bloat, thereby accelerating the subsequent transaction ID reclamation process. Identifying and cleaning up invalid metadata in the underlying database reduces the amount of data that needs to be processed and scanned, lowering the system resource consumption during vacuum cleanup operations. Vacuum cleanup of the system tables effectively reclaims transaction IDs from terminated transactions. Therefore, it solves the problem in related technologies of the inability to effectively release excessive amounts of transaction IDs from terminated transactions. Attached Figure Description

[0016] Figure 1 This is a hardware structure block diagram of a mobile terminal for processing transaction IDs according to an embodiment of this application.

[0017] Figure 2 This is a flowchart of a transaction ID processing method according to an embodiment of this application;

[0018] Figure 3 This is a structural block diagram of a transaction ID processing apparatus according to an embodiment of this application;

[0019] Figure 4 This is a flowchart of a transaction ID processing method according to another embodiment of this application. Detailed Implementation

[0020] To enable those skilled in the art to better understand the present application, the technical solutions in the embodiments of the present application will be clearly and completely described below with reference to the accompanying drawings. Obviously, the described embodiments are only some embodiments of the present application, and not all embodiments. Based on the embodiments in the present application, all other embodiments obtained by those of ordinary skill in the art without creative effort should fall within the scope of protection of the present application.

[0021] It should be noted that the terms "comprising" and "having" and any variations thereof in the specification, claims and accompanying drawings of this application are intended to cover non-exclusive inclusion. For example, a process, method, system, product or device that includes a series of steps or units is not necessarily limited to those steps or units that are explicitly listed, but may include other steps or units that are not explicitly listed or that are inherent to such process, method, product or device.

[0022] It should be noted that the terms "first," "second," etc., in the specification, claims, and drawings of this invention are used to distinguish similar objects and are not necessarily used to describe a specific order or sequence.

[0023] The following is a description of the relevant terms in the embodiments of the present invention:

[0024] Transaction ID: The concurrency and transaction management of a Massively Parallel Processor (MPP) is based on Greenplum's Multi-Version Concurrency Control (MVCC) and snapshot features. A complete distributed transaction management mechanism has been developed, assigning a corresponding Transaction ID (XID) to each transaction. Hidden XIDs are used to identify rows of data being processed, thus controlling data visibility. XIDs are generated strictly in chronological order of transaction start time, and their values ​​are globally unique. When the number of XIDs reaches its maximum of 4.2 billion, XID rollback occurs. Because XIDs can be reassigned after rollback, to ensure the correctness of transaction comparisons, the database stipulates that only 50% of XIDs can be allocated, meaning a maximum of 2.1 billion XIDs can be allocated. When the XID allocation is exhausted, the database becomes unavailable.

[0025] xmin: Represents the transaction ID of the data tuple being inserted. When a new row of data is inserted, the database will fill the xmin field of the newly inserted row with the current transaction ID.

[0026] xmax: The transaction ID in the partitioned table that represents the deletion or update of this data tuple. If the tuple header contains a valid, committed xmax value, the tuple is usually said to be "deleted" or "invisible".

[0027] Relfrozenxid (Relationship Frozen Transaction ID): The relfrozenxid field in the system table pg_class (table directory table) stores the FrozenXID (Frozen Transaction ID) of each table, which is the XID of the table when the transaction first started. All XIDs before that have been replaced with a permanent XID in the table.

[0028] Table age: The age of a table can be calculated using the age function, which is the difference between the current transaction and the relfrozenxid.

[0029] Datfrozenxid (Database Frozen Transaction ID): The datfrozenxid field in the system table pg_database stores the minimum relfrozenxid value of pg_class for each database.

[0030] Database age: The age of a database can be calculated using the age function, which is the difference between the current transaction and the datfrozenxid.

[0031] HEAP Tables: In the MPP cloud data warehouse, all metadata system tables are HEAP tables, which support row-level transaction processing, including insert, update, and delete operations. They are suitable for scenarios that require frequent updates and deletions or need to support transaction processing.

[0032] AO Tables: In MPP cloud data warehouses, all business tables are append-only (AO) tables, specifically designed for batch data loading and data analysis optimization. They do not support traditional update and delete operations; instead, data updates are achieved by appending new data. This design makes AO tables highly efficient when handling large amounts of data insertion, making them suitable for data warehouses and Online Analytical Processing (OLAP) systems. AO tables also have an associated pg_aovisimap table that records which rows in the data file have been deleted or updated, thus avoiding scanning invalid rows during queries and improving query efficiency.

[0033] MVCC: Greenplum uses PostgreSQL's MVCC mechanism to manage the versioning of data rows. Deleted and updated rows are not immediately physically deleted, but are marked as expired (dead tuples). The actual data is still stored in the table but is not visible.

[0034] Vacuum: The vacuum command modifies the hidden field xmin of each row in a single table to the current XID, and updates the relfrozenxid of the corresponding row in pg_class to the current XID. For HEAP tables, vacuum also reclaims garbage space and corresponding null pointers, thereby eliminating bloat.

[0035] The methods and embodiments provided in this application can be executed on a mobile terminal, computer terminal, or similar computing device. Taking running on a mobile terminal as an example, Figure 1 This is a hardware structure block diagram of a mobile terminal for processing transaction IDs according to an embodiment of this application. Figure 1 As shown, a mobile terminal may include one or more ( Figure 1 Only one is shown in the diagram. A processor 102 (which may include, but is not limited to, a microprocessor MCU or a programmable logic device FPGA, etc.) and a memory 104 for storing data are also shown. The mobile terminal may further include a transmission device 106 for communication functions and an input / output device 108. Those skilled in the art will understand that... Figure 1The structure shown is for illustrative purposes only and does not limit the structure of the mobile terminal described above. For example, the mobile terminal may also include components that are more... Figure 1 The more or fewer components shown, or having the same Figure 1 The different configurations shown.

[0036] The memory 104 can be used to store computer programs, such as application software programs and modules, like the computer program corresponding to a transaction ID processing method in this embodiment. The processor 102 executes various functional applications and data processing by running the computer program stored in the memory 104, thus implementing the aforementioned method. The memory 104 may include high-speed random access memory and non-volatile memory, such as one or more magnetic storage devices, flash memory, or other non-volatile solid-state memory. In some instances, the memory 104 may further include memory remotely located relative to the processor 102, and these remote memories can be connected to the mobile terminal via a network. Examples of such networks include, but are not limited to, the Internet, corporate intranets, local area networks, mobile communication networks, and combinations thereof.

[0037] The transmission device 106 is used to receive or send data via a network. Specific examples of the network described above may include a wireless network provided by the mobile terminal's communication provider. In one example, the transmission device 106 includes a Network Interface Controller (NIC), which can connect to other network devices via a base station to communicate with the Internet. In another example, the transmission device 106 may be a Radio Frequency (RF) module, used for wireless communication with the Internet.

[0038] This embodiment provides a method for processing transaction IDs. Figure 2 This is a flowchart of a transaction ID processing method according to an embodiment of this application, such as... Figure 2 As shown, the process includes the following steps:

[0039] Step S202: Export metadata from the system table through index query, wherein the system table is used to store metadata in the massively parallel processor (MPP) cloud data warehouse;

[0040] Before step S202 in this embodiment, the method further includes: exporting data from the base database and backing it up.

[0041] Step S204: Identify and clean up invalid metadata in the base database, whereby the base database is used to store metadata in system tables in the form of key-value pairs;

[0042] In an exemplary embodiment of this application, identifying and cleaning up invalid metadata in the underlying database includes: scanning the underlying database using an underlying database management tool, identifying and deleting invalid key-value pairs to clean up invalid metadata.

[0043] Step S206: Perform a vacuum cleanup operation on the system table to release the transaction identifier ID of the terminated transaction.

[0044] Before step S206 in this embodiment, the method further includes: creating a new business table by reorganizing the data of the business table with a larger age value in the MPP cloud data warehouse, so that the maximum age value of the business table in the MPP cloud data warehouse has been updated and reduced.

[0045] In an exemplary embodiment of this application, the process of releasing the transaction identifier ID of terminated transactions by performing a vacuum cleanup operation on the system table includes: not updating the table directory table of the system table when performing a vacuum cleanup operation on the first N-1 system tables; and updating the table directory table of the system table when performing a vacuum cleanup operation on the Nth system table.

[0046] After step S206 in this embodiment, the method further includes: after performing a vacuum cleanup operation on the system table to release the transaction identifier ID of the terminated transaction, the method further includes: exporting the data of the system table after the vacuum cleanup operation and comparing it with the data in the backed-up base database to determine whether the vacuum cleanup operation is accurate.

[0047] The index-based query method used in this embodiment exports metadata from the system tables, avoiding full table scans. This significantly improves data export speed, especially in cases of severe metadata bloat, thereby accelerating the subsequent transaction ID reclamation process. Identifying and cleaning up invalid metadata in the underlying database reduces the amount of data that needs to be processed and scanned, lowering the system resource consumption during vacuum cleanup operations. Vacuum cleanup of the system tables effectively reclaims transaction IDs from terminated transactions. Therefore, it solves the problem in related technologies of the inability to effectively release excessive amounts of transaction IDs from terminated transactions.

[0048] Through the above description of the embodiments, those skilled in the art can clearly understand that the methods according to the above embodiments can be implemented by means of software plus necessary general-purpose hardware platforms. Of course, they can also be implemented by hardware, but in many cases the former is a better implementation method. Based on this understanding, the technical solution of this application, in essence, or the part that contributes to the prior art, can be embodied in the form of a software product. This computer software product is stored in a storage medium (such as ROM / RAM, magnetic disk, optical disk) and includes several instructions to cause a terminal device (which may be a mobile phone, computer, server, or network device, etc.) to execute the methods of the various embodiments of this application.

[0049] This embodiment also provides a transaction ID processing apparatus for implementing the above embodiments and preferred embodiments; details already described will not be repeated. As used below, the term "module" can refer to a combination of software and / or hardware that implements a predetermined function. Although the apparatus described in the following embodiments is preferably implemented in software, hardware implementation, or a combination of software and hardware, is also possible and contemplated.

[0050] Figure 3 This is a structural block diagram of a transaction ID processing apparatus according to an embodiment of this application, such as... Figure 3 As shown, the processing device for the transaction ID includes: an export module 10, a cleanup module 20, and a processing module 30.

[0051] Export module 10 is used to export metadata from the system table through index query, wherein the system table is used to store metadata in the massively parallel processor MPP cloud data warehouse;

[0052] Cleanup module 20 is used to identify and clean up invalid metadata in the base database, which is used to store metadata in system tables in the form of key-value pairs;

[0053] Processing module 30 is used to release the transaction identifier ID of terminated transactions by performing a vacuum cleanup operation on the system tables.

[0054] The present invention will now be described in conjunction with specific embodiments:

[0055] MPP cloud data warehouse adopts a metadata sharing and storage-compute separation architecture. The shared metadata processes massive transactions daily, and the XID grows rapidly. If it is not recycled in time, the database will stop operating when the XID reaches 2.1 billion.

[0056] In a shared architecture, MPP's master production metadata is massive, with 102 system tables requiring XID reclamation. These tables contain a large amount of invalid metadata, while valid data comprises only 500 million rows, representing just 3% of the total data volume. Each row in the system tables contains an XID field. Based on Greenplum's native XID reclamation logic, MPP needs to process each row of the system tables during XID reclamation.

[0057] The transaction identifier (ID) processing method in related technologies, when directly applied to MPP cloud data warehouses, has the following main problems: Greenplum's data is stored in local disks as files, and the cleanup of obsolete space is also based on the underlying operating system. However, the metadata information of MPP cloud data warehouses is stored in FoundationDB (basic database) in the form of key-value pairs, accessed through external interfaces, and the modification logic is relatively complex. Corresponding to the AO table, the data in MPP cloud data warehouses is persistently stored in COS, which does not support direct file modification, making it difficult to reclaim obsolete space in the AO table; the MPP cloud data warehouse system under the shared architecture is huge, and the metadata is severely bloated. If the database is shut down for vacuuming, it will take three days, which is unacceptable to the business; direct online processing is risky and will lock system tables, affecting normal business operations.

[0058] Compared to traditional GP, MPP cloud data warehouses are characterized by large metadata volumes and high business volumes. Metadata storage has shifted from a local file system to external interface access to FoundationDB. XID reclamation involves large data volumes and complex implementation logic. MPP's MVCC mechanism uses deletion marking logic and stores multiple versions of data records. A large amount of invalid data needs to be deleted row by row, severely delaying the XID reclamation progress.

[0059] Figure 4 This is a flowchart of a transaction ID processing method according to another embodiment of this application, such as... Figure 4 As shown, the process includes the following steps:

[0060] Step S401: Back up the basic database data;

[0061] Specifically, before vacuuming, the data in the base database (FDB) needs to be backed up. This allows for the restoration of metadata information by restoring the backed-up FDB data in case of problems during implementation. To improve overall efficiency, a metadata cluster and a computing cluster were set up for verification. When backing up the FDB data, the FDB data is transferred to the verification cluster using scp, and the database is started normally to verify the validity of the data.

[0062] Step S402: Export the system table before the change;

[0063] Specifically, to ensure data consistency before and after XID reclamation, it's necessary to export the system tables before the change. Due to severe metadata bloat, direct copy export requires scanning all key-value pairs in the FDB, which is very time-consuming. Therefore, this solution uses index-based queries to export system table data, avoiding querying bloated data and impacting export efficiency. However, the metadata bandwidth (bps) remains a bottleneck, and single-point queries to system tables involve a large number of transactions, making metadata export time still unsatisfactory. Therefore, a range query based on indexes, such as `SELECT`, was subsequently adopted. from pg_class where oid in (xxx,xxx...).

[0064] Step S403: Clean up invalid metadata data;

[0065] Use the tool mxtools to scan the FDB data, identify invalid key-value pairs, delete invalid key-value pairs, and clean up bloated metadata information to reduce the number of key-value pairs that need to be scanned during subsequent vacuuming, thus reducing vacuuming time.

[0066] Step S404: Release the transaction identifier ID;

[0067] First, a reorg (data reorganization) operation is performed on the business tables (all AO tables) with large age values ​​to rebuild the AO tables. At this point, the maximum age of the AO tables has been updated and reduced. Therefore, only vacuuming all system tables (HEAP tables) is needed to update the minimum relfrozenxid value of pg_class normally. In implementation, the basic principle of vacuuming is basically the same as Greenplum, but the MPP cloud data warehouse will also clear the historical value in the key-value pairs of each row of valid data in the metadata FDB. When the downstream computing cluster of metadata is large, vacuuming a single table to update pg_class takes a long time. Therefore, this solution adjusts the vacuuming process so that pg_class is only updated when vacuuming the last system table, reducing the overall execution time.

[0068] Step S405: Export the system table after the release transaction identifier ID;

[0069] After vacuuming, it's necessary to export the metadata at this point and compare it with the data exported before vacuuming. First, check if the hidden fields xmin, xmax, and relfrozenxid of the table have been correctly modified. Second, compare whether the data other than the aforementioned fields, reltuples and relpages, is consistent. If they are consistent, the solution is implemented correctly.

[0070] The vacuum process is optimized through the embodiments of this application. When vacuuming the first N-1 tables, the process of updating pg_class is skipped, and pg_class is scanned again when vacuuming the Nth table, which reduces the vacuum time. A tool for metadata bloat scanning and cleanup is designed, which can scan and clean up invalid key-value pairs in FDB. A method for exporting metadata from index range is designed, which can efficiently export and compare data consistency.

[0071] Compared to Greenplum's vacuum method, the embodiments of this application can scan the metadata bloat and clean up the bloated data first under the shared metadata architecture of MPP, thereby reducing the amount of data scanned in subsequent vacuum scans; and during the table-by-table vacuum process, pg_class is not scanned and updated first, but only when the last table is vacuumed; and data consistency comparison is also performed before and after vacuuming.

[0072] Embodiments of this application provide a computer program product, including a computer program that, when executed by a processor, implements the steps in any of the above method embodiments.

[0073] Embodiments of this application also provide a computer-readable storage medium storing a computer program, wherein the computer program is configured to execute the steps in any of the above method embodiments when run.

[0074] In one exemplary embodiment, the aforementioned computer-readable storage medium may include, but is not limited to, various media capable of storing computer programs, such as USB flash drives, read-only memory (ROM), random access memory (RAM), portable hard drives, magnetic disks, or optical disks.

[0075] Embodiments of this application also provide an electronic device, including a memory and a processor, wherein the memory stores a computer program and the processor is configured to run the computer program to perform the steps in any of the above method embodiments.

[0076] In one exemplary embodiment, the electronic device may further include a transmission device and an input / output device, wherein the transmission device is connected to the processor and the input / output device is connected to the processor.

[0077] Specific examples in this embodiment can be found in the examples described in the above embodiments and exemplary implementations, and will not be repeated here.

[0078] Obviously, those skilled in the art should understand that the modules or steps of this application described above can be implemented using general-purpose computing devices. They can be centralized on a single computing device or distributed across a network of multiple computing devices. They can be implemented using computer-executable program code, and thus can be stored in a storage device for execution by a computing device. In some cases, the steps shown or described can be performed in a different order than those presented here, or they can be fabricated as separate integrated circuit modules, or multiple modules or steps can be fabricated as a single integrated circuit module. Thus, this application is not limited to any particular combination of hardware and software.

[0079] The above are merely preferred embodiments of this application and are not intended to limit this application. Various modifications and variations can be made to this application by those skilled in the art. Any modifications, equivalent substitutions, improvements, etc., made within the principles of this application should be included within the scope of protection of this application.

Claims

1. A transaction ID processing method, characterized by, The method comprises the following steps: metadata in a system table is exported by index query, wherein the system table is used to store metadata in a massively parallel processor (MPP) cloud data warehouse; invalid metadata in a base database is identified and cleaned, wherein the base database is used to store the metadata in the system table in the form of key-value pairs; transaction identifiers (IDs) of terminated transactions are released by vacuuming the system table.

2. The method of claim 1, wherein, Before the metadata in the system table is exported by index query, the method further comprises the following step:

3. The method of claim 1, wherein, data in the base database is exported and backed up. Before the transaction IDs of the terminated transactions are released by vacuuming the system table, the method further comprises the following step:

4. The method of claim 1, wherein, a new business table is created by data reorganization on a business table with a large age value in the MPP cloud data warehouse, so that the maximum age value of the business table in the MPP cloud data warehouse is updated and reduced. The invalid metadata in the base database is identified and cleaned, and the method comprises the following steps:

5. The method of claim 1, wherein, the base database is scanned by a base database management tool to identify and delete invalid key-value pairs, so as to clean the invalid metadata. The transaction IDs of the terminated transactions are released by vacuuming the system table, and the method comprises the following steps: the table directory table of the system table is not updated when vacuuming the first N-1 system tables, wherein N is a positive integer greater than 1; 6. The method of claim 1, wherein, the table directory table of the system table is updated when vacuuming the Nth system table. After the transaction IDs of the terminated transactions are released by vacuuming the system table, the method further comprises the following step:

7. An apparatus for processing of transaction IDs, characterized in that, data of the system table after vacuuming is exported and compared with data in the backed-up base database to determine whether the vacuuming is accurate. The method comprises the following steps: a metadata exporting module is configured to export metadata in a system table by index query, wherein the system table is used to store metadata in a massively parallel processor (MPP) cloud data warehouse; a metadata cleaning module is configured to identify and clean invalid metadata in a base database, wherein the base database is used to store the metadata in the system table in the form of key-value pairs; 8. A computer program product comprising a computer program, characterized in that, a processing module is configured to release transaction identifiers (IDs) of terminated transactions by vacuuming the system table.

9. A computer-readable storage medium, characterized in that, The computer program is executed by the processor to implement the steps of the method in any one of claims 1 to 6.

10. An electronic device comprising a memory, a processor, and a computer program stored on the memory and executable on the processor, characterized in that, The computer program is stored in the computer-readable storage medium, and when executed by the processor, the computer program implements the steps of the method in any one of claims 1 to 6. The processor executes the computer program to implement the steps of the method in any one of claims 1 to 6.

Images