Clearing method for transaction log data of database, storage medium and equipment

Abstract

The invention provides a cleaning method for transaction log data of a database, a storage medium and equipment. The method for cleaning the transaction log data of the database comprises the steps of obtaining an event that the database completes an exception recovery process; determining whether a transaction log is remade in the exception recovery process or not; if yes, obtaining a log serial number of the last redo transaction log, and recording the log serial number as a final redo serial number; and cleaning invalid data in a preset page data table according to the final redo serial number, wherein the preset page data table is used for storing page mirror image data separated from the transaction log. According to the scheme, on the premise that the data security of the database is guaranteed, the disk space occupied by the page mirror image data in the transaction log is greatly reduced, and the page data table used for storing the page mirror image data separated from the transaction log can stably and normally work after the database is started.

Description

Technical Field

[0001] The present invention relates to the technical field of databases, and in particular, to a method for cleaning transaction log data of a database, a storage medium, and a device. Background Art

[0002] A transaction log (or XLOG log) is an important part of a database, storing the history of all changes and operations in the database system to ensure that the database does not lose data due to failures (such as power outages or other failures that cause the server to crash).

[0003] The transaction log is generally a Write Ahead Log (WAL) log. The mechanism of the Write Ahead Log is as follows: before all data modification operations (such as insert, update, or delete) are actually written to the persistent storage (such as disk) of the database, they are first recorded in the transaction log. That is, when a transaction starts to execute a modification operation, the relevant change information is first recorded in the transaction log. WAL is one of the key technologies for implementing the ACID properties (atomicity, consistency, isolation, and durability) of transactions. Through the transaction log, the system can ensure that the modifications of a single transaction are either all completed or all rolled back, while ensuring that the modifications of committed transactions are not lost due to failures.

[0004] While realizing the above functions, the transaction log may also occupy a relatively large storage space. Existing technologies also provide some solutions for cleaning the transaction log, such as cleaning regularly or manually by an administrator. These cleaning methods cannot clean in time and there is a risk of cleaning up still valid transaction logs, resulting in the inability of the transaction log to recover data. Summary of the Invention

[0005] An object of the present invention is to provide a method for cleaning transaction log data of a database, a storage medium, and a device that can clean the transaction log data accurately and reliably.

[0006] A further object of the present invention is to reduce the storage space occupied by the transaction log data.

[0007] Another further object of the present invention is to improve the management efficiency of page mirror data and reduce the storage space it occupies.

[0008] In particular, the present invention provides a method for cleaning transaction log data of a database, including:

[0009] Obtaining an event that the database completes an abnormal recovery process;

[0010] Determining whether there is a transaction log to be redone in the abnormal recovery process;

[0011] If so, obtain the log sequence number of the last transaction log to be redone, denoted as the final redo sequence number;

[0012] Clean the invalid data in the preset page data table according to the final redo sequence number, where the preset page data table is used to store the page mirror data separated from the transaction log.

[0013] Optionally, the step of cleaning the invalid data in the preset page data table according to the final redo sequence number includes:

[0014] Search for the page mirror data in the page data table whose log sequence number is greater than the final redo sequence number, and clean the found page mirror data.

[0015] Optionally, when it is determined that there is no transaction log to be redone in the abnormal recovery process, it further includes: searching for the last valid page mirror data in the page data table, and writing the page mirror data regenerated after the database is started after the last valid page mirror data.

[0016] Optionally, the page data table is used to record the page mirror data and its related information, and the related information includes: the location information of the table to which the data page corresponding to the page mirror data belongs on the disk, the data block number of the data page corresponding to the page mirror data within the table to which it belongs, and the log sequence number of the transaction log corresponding to the page mirror data.

[0017] Optionally, the tuple corresponding to the location information of the table to which the data page corresponding to the page mirror data belongs on the disk includes: the tablespace object identifier, the database object identifier, and the data table object identifier.

[0018] Optionally, the generation process of the transaction log includes:

[0019] Obtain and register the transaction log data according to the transactions of the database;

[0020] Assemble the transaction log in a preset format, and separate the page mirror data in the transaction log;

[0021] Write the page mirror data into the page data table, and flush the remaining data of the transaction log to disk after the page mirror data is written into the page data table.

[0022] Optionally, after flushing the remaining data of the transaction log to disk after the page mirror data is written into the page data table, flush the data page corresponding to the page mirror data to disk.

[0023] Optionally, the step of assembling the transaction log in a preset format includes:

[0024] Pre-allocate the storage location of the transaction log in the transaction log write cache according to the length of the transaction log;

[0025] Put the transaction log data into a continuous range;

[0026] Copy the transaction log data in the continuous interval to the transaction log write cache.

[0027] In another aspect, the present invention also provides a machine-readable storage medium, on which a machine-executable program is stored. When the machine-executable program is executed by a processor, it implements the method for cleaning the transaction log data of the database according to any one of the above.

[0028] In yet another aspect, the present invention also provides a computer device, including a memory, a processor, and a machine-executable program stored on the memory and running on the processor. When the processor executes the machine-executable program, it implements the method for cleaning the transaction log data of the database according to any one of the above.

[0029] The method for cleaning the transaction log data of the database of the present invention reduces the disk space occupied by the page mirror data in the transaction log on the premise of ensuring the security of the database data, and makes the page data table used to store the page mirror data separated from the transaction log clean after the database is started, so that it can work stably and normally. The method of the present invention first checks whether there is a transaction log to be redone in the abnormal recovery process after determining that the database has completed the abnormal recovery process, determines the log sequence number of the last transaction log to be redone, thereby determining the range of invalid page mirror files, and performs targeted cleaning to ensure the reliable and stable operation of the page data table.

[0030] Further, in the method for cleaning the transaction log data of the database of the present invention, the page mirror data is independently stored in a preset page data table, and the table tuple management method of the database is used to manage the page mirror data, which greatly reduces the management difficulty of the page mirror data in the transaction log file. The solution of the present invention is applicable to relational databases, especially to KingbaseES databases (abbreviated as KES databases), enriching the functions of such databases and improving the efficiency of the databases.

[0031] From the following detailed description of specific embodiments of the present invention in conjunction with the drawings, those skilled in the art will become more clear about the above and other objects, advantages and features of the present invention. BRIEF DESCRIPTION OF THE DRAWINGS

[0032] Some specific embodiments of the present invention will be described in detail hereinafter with reference to the drawings in an exemplary but non-limiting manner. The same reference numerals in the drawings denote the same or similar components or parts. Those skilled in the art should understand that these drawings are not necessarily drawn to scale. In the drawings:

[0033] Figure 1 is a schematic diagram of the method for cleaning the transaction log data of the database according to an embodiment of the present invention;

[0034] Figure 2 Schematic diagram of a page data table in a method for cleaning transaction log data of a database according to an embodiment of the present invention;

[0035] Figure 3 Schematic diagram after the page data table in the method for cleaning transaction log data of a database according to an embodiment of the present invention is cleaned;

[0036] Figure 4 Schematic diagram of the logical structure of a transaction log in the method for cleaning transaction log data of a database according to an embodiment of the present invention;

[0037] Figure 5 Schematic diagram of the process for generating a transaction log in the method for cleaning transaction log data of a database according to an embodiment of the present invention;

[0038] Figure 6 Schematic diagram in the form of a linked list of an original transaction log with page mirror data in the method for cleaning transaction log data of a database according to an embodiment of the present invention;

[0039] Figure 7 is Figure 6 Schematic diagram in the form of a linked list after removing page mirror data from the linked list shown;

[0040] Figure 8 Schematic diagram of the simplification of the linked list of two page mirror data nodes in a transaction log in the method for cleaning transaction log data of a database according to an embodiment of the present invention;

[0041] Figure 9 Schematic diagram of the specific content of a page mirror data node in the method for cleaning transaction log data of a database according to an embodiment of the present invention;

[0042] Figure 10 Schematic diagram of the final state of a transaction log in the method for cleaning transaction log data of a database according to an embodiment of the present invention;

[0043] Figure 11 Schematic diagram of a global page data linked list in the method for cleaning transaction log data of a database according to an embodiment of the present invention;

[0044] Figure 12 Schematic diagram of the brief process of the method for cleaning transaction log data of a database according to an embodiment of the present invention;

[0045] Figure 13 Schematic diagram of a machine-readable storage medium according to an embodiment of the present invention;

[0046] Figure 14Schematic diagram of a computer device according to an embodiment of the present invention. Detailed implementation manners

[0047] Those skilled in the art should understand that the embodiments described below are only a part of the embodiments of the present invention, rather than all the embodiments of the present invention. This part of the embodiments is intended to explain the technical principles of the present invention, rather than to limit the protection scope of the present invention. Based on the embodiments provided by the present invention, all other embodiments obtained by those of ordinary skill in the art without creative efforts should still fall within the protection scope of the present invention.

[0048] It should be noted that the logic and / or steps represented in the flowchart or described in other ways herein, for example, can be considered as a definite sequence list of executable instructions for implementing logical functions, and can be specifically implemented in any computer-readable medium for use by an instruction execution system, apparatus, or device (such as a computer-based system, a system including a processor, or other systems that can fetch instructions from the instruction execution system, apparatus, or device and execute the instructions), or used in combination with these instruction execution systems, apparatus, or devices.

[0049] The flowchart provided by the present invention is not intended to indicate that the operations of the method will be executed in any specific order, or that all the operations of the method are included in every case. In addition, the method may include additional operations. Within the scope of the technical idea provided by the method of this embodiment, additional changes can be made to the above method.

[0050] An important function of the transaction log is to construct a primary and standby database cluster. After the primary machine in the database cluster fails, the standby machine can immediately be upgraded to the primary machine and take over the primary machine to continue providing services externally. During the process of the primary machine providing services externally, transaction logs will be continuously generated, and these transaction logs are transmitted to the standby machine through streaming replication. The standby machine ensures that the data is almost the same as the data of the primary machine by receiving and repeatedly executing these transaction logs. When the database is under heavy business pressure, a large number of transaction logs will be generated, which will cause the streaming replication between the primary database and the standby database to become a performance bottleneck.

[0051] In order to improve the streaming replication efficiency between the primary database and the standby database, the solution of the method of this embodiment is to separate the page mirror data in the transaction log (XLOG log), reduce the data volume of the transaction logs generated by the primary database, and thus reduce the transmission pressure of the streaming replication between the primary database and the standby database.

[0052] During the process of operating system crash, some operating system pages (e.g., with a page size of 4KB) may not have been written to disk in time, which may result in a database page (e.g., composed of two operating system pages with a page size of 8KB) containing a mixture of old and new data (mixed data before and after the crash). During the recovery period after the operating system crash, since the information stored in the transaction log is not complete enough, it is impossible to fully recover the database page containing the mixture of old and new data in the database. When a database page (e.g., with a default size of 8KB) is modified (updated) for the first time, the entire database page is written to the transaction log. At this time, the transaction log contains the page mirror data page data to save the complete database page mirror.

[0053] In the case of encountering a broken page during the database recovery process, replacing the broken page according to the page mirror data pagedata in the transaction log can ensure the security and stability of the database data. Therefore, the amount of data of the page mirror data pagedata in the transaction log is equivalent to the amount of data of the database page, and the amount of data of the transaction log containing the page mirror data is relatively large.

[0054] In some embodiments, when the page mirror data separated from the transaction log is written to disk, a corresponding page mirror file is created for each page mirror data. In actual use, the number of page mirrors in the transaction log is extremely large, and it is very inconvenient to manage each page mirror corresponding to a file. Alternatively, the page mirror data separated from the transaction log can be written into a preset page data table, converting the management of a large number of page mirror files into the management of table tuples, so that the powerful management methods of the database itself for tables can be utilized, greatly reducing the management difficulty of the transaction page mirror data on disk.

[0055] For the primary host in the primary and standby clusters, the page mirror data page data separated from its transaction log needs to be retained in the page data table pagedata_table. An overly large page data table pagedata_table will occupy a large amount of storage space. Especially for the invalid page mirror data generated when the database suddenly shuts down, there is a lack of corresponding cleaning means for this kind of invalid page mirror data caused by the database shutdown. Based on this defect, this embodiment provides a method for cleaning the transaction log data of a database to realize the cleaning of the invalid page mirror data caused by the database shutdown.

[0056] Figure 1 FIG. is a schematic diagram of a method for cleaning the transaction log data of a database according to an embodiment of the present invention. Generally, the method for cleaning the transaction log data of the database includes:

[0057] Step S101: Obtain the event of the database completing the abnormal recovery process. That is, the method for cleaning the transaction log data of the database in this embodiment starts to execute the cleaning after it is determined that the database has recovered data from a downtime or other failures.

[0058] Step S102: Determine whether there is any transaction log to be redone in the abnormal recovery process. If it is determined that there is no transaction log to be redone in the abnormal recovery process, it further includes: finding the last valid page mirror data in the page data table, and the page mirror data regenerated after the database is started is written after the last valid page mirror data.

[0059] Step S103: If it is determined that there is a transaction log to be redone, obtain the log sequence number of the last redone transaction log, denoted as the final redone sequence number last_redo_lsn;

[0060] Step S104: Clean the invalid data in the preset page data table according to the final redone sequence number. The preset page data table pagedata_table is used to store the page mirror data pagedata separated from the transaction log XLOG. The page data table is used to record the page mirror data and its related information. The related information includes: the location information (RelFileNode) of the data page corresponding to the page mirror data in the disk, the data block number (BlockNumber) of the data page corresponding to the page mirror data in the table to which it belongs, and the log sequence number (LSN) of the transaction log corresponding to the page mirror data. The tuple corresponding to the location information of the data page corresponding to the page mirror data in the disk may include: the table space object identifier (table space oid), the page data table database object identifier (database oid), and the data table object identifier (table oid).

[0061] An optional execution process of Step S104 is: find the page mirror data in the page data table whose log sequence number is greater than the final redone sequence number, and clean the found page mirror data.

[0062] Figure 2 It is a schematic diagram of the page data table in the method for cleaning the transaction log data of the database according to an embodiment of the present invention. Figure 3It is a schematic diagram after the page data table in the method for cleaning transaction log data of a database according to an embodiment of the present invention is cleaned. As shown in the figure, each tuple of page mirror data in the page data table includes: tablespace oid, database oid, table oid, BlockNumber, LSN, page data header, page data. Among them, using the tablespace oid, database oid, and table oid as part of the RelFileNode, the position of the table to which the data page belongs on the disk can be located; the data block number (BlockNumber) can locate the position of the data block corresponding to the page mirror data within the table; LSN (Log SequenceNumber) represents the log sequence number corresponding to the page mirror data; page data header and page data together constitute the page mirror data.

[0063] Since the page mirror data is written to disk before the corresponding page mirror data, if the database suddenly crashes, it is possible that the page mirror data has been written to the page data table pagedata_table, while other data of the corresponding transaction log has not been written to the transaction log file.

[0064] It is easy to know that these page mirror data without corresponding valid transaction logs are located at the end of the page data table pagedata_table. In order to append the newly generated page data to a clean (without invalid data) page data table pagedata_table after the database restarts, the method of this embodiment places the cleaning process of invalid page mirror data in the page data table pagedata_table after the database abnormal recovery process.

[0065] During the cleaning process, the LSN that demarcates the valid page mirror data and the invalid page mirror data can be determined first, and this LSN can be denoted as last_redo_lsn. When it is determined that there is a transaction log to be redone, the LSN of the last transaction log to be redone can be obtained, and this LSN is the last_redo_lsn. When it is determined that there is no transaction log to be redone in the abnormal recovery process, last_redo_lsn can be set to the LSN of the last valid transaction log. After the database is started, new transaction logs will be written starting from after this log. For the tuples of page mirror data in the page data table pagedata_table with LSN greater than last_redo_lsn, they can be directly deleted.

[0066] To better understand the cleaning method of this embodiment and the corresponding technical effects, the separation of the above page mirror data and the generation process of the transaction log will be introduced.

[0067] The generation process of the transaction log may include: obtaining and registering transaction log data according to the transactions of the database; assembling the transaction log in a preset format and separating the page mirror data in the transaction log; writing the page mirror data into the page data table, and flushing the remaining data of the transaction log after the page mirror data is written into the page data table. Among them, the step of assembling the transaction log in a preset format may include: pre-allocating the storage location of the transaction log in the transaction log write cache according to the length of the transaction log; putting the transaction log data into a continuous range; copying the transaction log data in the continuous range to the transaction log write cache.

[0068] After flushing the remaining data of the transaction log after the page mirror data is written into the page data table, the data pages corresponding to the page mirror data are flushed. That is, the write-ahead log precedes the data flushing.

[0069] Figure 4 It is a schematic diagram of the logical structure of a transaction log in the method for cleaning transaction log data of a database according to an embodiment of the present invention. In this embodiment, a transaction log may include an XLOG log header (denoted as headdata) and an XLOG log data area. Specifically, the XLOG log data area may include multiple block data areas and a main data area. Each block data area may contain page mirror data (page data) and tuple data (tupledata). The XLOG log header may include an XLogRecord structure, the header data of each block data area, and the header data of the main data area.

[0070] It should be noted that different types of transaction logs may have different structures. Each transaction log must necessarily contain head data, but not necessarily page mirror data (page data), tuple data (tupledata), and main data.

[0071] The transaction log processing method of this embodiment separates the page mirror data in the transaction log on the premise of ensuring the security and stability of the database data, thereby greatly reducing the data volume of the transaction log.

[0072] Figure 5 It is a schematic diagram of the process for generating a transaction log in the method for cleaning transaction log data of a database according to an embodiment of the present invention. The generation process of this transaction log includes:

[0073] Step S501, generating transaction log data. Since the transaction log data may be selectively stored according to certain flag bits during the transaction log assembly process, the final data content in the transaction log cannot be completely determined at this stage.

[0074] Step S502, register transaction log data. Similar to step S501, the final data content in the transaction log cannot be fully determined at this stage, so the separation operation of page mirror data cannot be performed.

[0075] Step S503, assemble transaction log data. At this stage, the final data content in the transaction log can be fully determined, and the page mirror data in the transaction log can be conveniently obtained. In addition, at this stage, the position of the to-be-written transaction log in the transaction log write cache has not been determined. Only the total length of the transaction log after the separation of page mirror data needs to be calculated, and the storage position of the transaction log is pre-allocated in the transaction log write cache according to this length, so as to eliminate the influence of the change in the data size after the separation of the page mirror data of the transaction log on the change of the transaction log storage position information. Therefore, this stage is suitable for performing the page mirror data separation operation on the transaction log.

[0076] Step S504, pre-allocate the storage position of the transaction log in the transaction log write cache according to the length of the transaction log. In this process, each process writing the transaction log needs to obtain an exclusive lock to prevent the positions pre-allocated by each process on the transaction log write cache from overlapping.

[0077] Step S505, put the data of each part of the transaction log into a continuous interval.

[0078] Step S506, copy the transaction data in the continuous interval to the transaction log write cache.

[0079] Step S507, write the transaction log write cache page storing the transaction log in the transaction log write cache to the disk.

[0080] On this basis, the transaction log processing method of this embodiment can also pre-allocate the storage position of the transaction log after the separation of page mirror data in the transaction log write cache according to the length of the transaction log after the separation of page mirror data; copy the transaction log after the separation of page mirror data to the transaction log write cache according to the pre-allocated storage position of the transaction log after the separation of page mirror data; perform a disk write operation on the transaction log write cache page storing the transaction log after the separation of page mirror data.

[0081] During the generation process of the transaction log, when all the content of a transaction log is determined, the transaction log is assembled into a structure composed of an assembly linked list and a page data linked list, and then the assembly linked list and the page data linked list are respectively stored in the cache, and subsequent disk writes can be performed separately.

[0082] Figures 6 to 10Shows the processing process of page mirror data in the transaction log. The transaction log is separately stored in the cache in the form of an assembled linked list and a page mirror data linked list. The page mirror data linked list is a linked list composed of page mirror data in the database log, and the assembled linked list is a linked list composed of other data in the database log except page mirror data. And the page mirror data linked list is stored in a preset global page data linked list. Among them Figure 6 Is a schematic diagram of the linked list form of the original transaction log with page mirror data in the cleaning method of transaction log data of a database according to an embodiment of the present invention, Figure 7 Is Figure 6 A schematic diagram of the linked list form after removing page mirror data from the linked list shown. Taking the transaction log with two page mirror data nodes in the complete content as an example, next represents pointing to the next node; len represents the len function, which is used to calculate the length of the corresponding data; the head data head data represents the head data of the transaction log; page data represents the page mirror data in the transaction log; the tuple data tupledata and the main data main data represent other data in the transaction log except page mirror data. Figure 8 Is a simplified schematic diagram of the linked list of two page mirror data nodes in the transaction log in the cleaning method of transaction log data of a database according to an embodiment of the present invention. wal1 page data1 and wal1 page data2 are used to simply indicate two different page mirror data nodes belonging to the same database, and do not represent specific content. Figure 9 Is a schematic diagram of the specific content of a page mirror data node in the cleaning method of transaction log data of a database according to an embodiment of the present invention. RelFileNode represents the location information of the table to which the data page corresponding to this page mirror data belongs on the disk (the location information of the table where the data disk page corresponding to page data is located), and BlockNumber represents the location on the disk of the block number of the data page corresponding to this page mirror data in the table to which it belongs (the block number of the data disk page corresponding to page data in the table to which it belongs). Therefore, RelFileNode and BlockNumber can determine the location of the data page corresponding to the page mirror data on the disk. Lsn (Log Sequence Number) represents the log sequence number corresponding to the page mirror data. next represents pointing to the next node. Figure 10 Is a schematic diagram of the final state of the transaction log in the cleaning method of transaction log data of a database according to an embodiment of the present invention.

[0083] During the process of writing the transaction log into the cache, the assembly linked list and the page mirror data linked list are stored separately. Moreover, the page mirror data linked list is stored in the global page data linked list. That is to say, the global page data linked list is a linked list formed by connecting the page mirror data linked lists to each other.

[0084] Figure 11 It is a schematic diagram of the global page data linked list in the cleaning method of the transaction log data of the database according to an embodiment of the present invention. There are two page mirror data linked lists stored in the global page data linked list. page_data_list_local represents the page mirror data linked list of a transaction log, and page_data_list_global represents the global page data linked list. Among them, wal1 and wal2 are only used to indicate that the two page mirror data linked lists belong to two different transaction logs. That is to say, the page mirror data in a single transaction log is organized in the form of a linked list, denoted as the page_data_list_local linked list. The page_data_list_local linked lists of all the page mirror data separated from the transaction logs are connected to form the global page data linked list, denoted as the page_data_list_local linked list, and it is located in the shared memory.

[0085] Since the transaction log must be written to disk before its corresponding data, the corresponding data has not been written to disk when the transaction log is generated. In the case where the corresponding page in the database data disk has not been updated, there will be no page break problem. The process of writing the transaction log to disk includes: sorting the nodes in the page_data_list_global linked list in ascending order according to the lsn. Determining the LSN range of the transaction log to be written to disk this time. Finding the linked list nodes within the above LSN range from the page_data_list_global linked list; sequentially storing the data in these linked list nodes at the end of a preset page data table (pagedata_table table). Removing the linked list nodes within the above LSN range from the page_data_list_global linked list. Writing the transaction log to be written to disk this time into the transaction log file. So far, the page corresponding to the page mirror data has not been written to disk. When these pages are written to disk subsequently, if a page break occurs, the page mirror data in the pagedata_table table can be used for repair.

[0086] As can be seen from the above process, the pagedata_table in the page data table stores the page mirror data that has been written to disk, thus converting the management of page mirror data into the management of table tuples, and enabling the use of the powerful management methods of the database itself for tables, greatly reducing the management difficulty of transaction log page mirror data on disk. However, for the hosts in the primary and standby clusters, the page mirror data separated from their transaction logs needs to be retained in the pagedata_table table, and an overly large pagedata_table table will occupy a large amount of storage space. The method of this embodiment is further improved to propose a solution for reducing the storage space of the host occupied by the pagedata_table table on the premise of ensuring the consistency of the primary and standby databases.

[0087] In this embodiment, after a database failure occurs, it is possible to reconstruct the missing page mirror data by redoing the transaction log while restoring the database. One implementation method is as follows: It is relatively easy to determine whether the transaction log originally contained page mirror data pagedata based on whether the header information of page mirror data page data exists in the header information of the transaction log. According to the header information of page mirror data page data recorded in the transaction log, the complete data page can be constructed as page mirror data page data using the same method as in the assembly stage of the transaction log write process, enabling the reuse of the header of page data in the transaction log. After the repair is completed, the last page mirror data page data redone is used as the boundary between the valid data and the invalid data in the pagedata_table table.

[0088] Figure 12 It is a schematic flowchart of the cleaning method for transaction log data of a database according to an embodiment of the present invention. The cleaning process includes:

[0089] Step S121, the database crashes, generating invalid page mirror data page data;

[0090] Step S122, the invalid page mirror data page data is written into the pagedata_table table;

[0091] Step S123, the database performs abnormal repair;

[0092] Step S114, after determining that the repair is completed, determine the LSN range of the invalid data in the pagedata_table table.

[0093] Applying the method of this embodiment can reduce the disk space occupied by page mirror data in the transaction log while ensuring the security of database data, and make the page data table used to store the page mirror data separated from the transaction log clean after the database is started, so that it can work stably and normally. The method of the present invention first checks whether there is a transaction log to be redone in the abnormal recovery process after determining that the database has completed the abnormal recovery process, determines the log sequence number of the last transaction log to be redone, thereby determining the range of invalid page mirror files, and performs targeted cleaning to ensure the reliable and stable operation of the page data table. In addition, the page mirror data is independently stored in a preset page data table, and the table tuple management method of the database is used to manage the page mirror data, which greatly reduces the management difficulty of the page mirror data in the transaction log file. This embodiment also provides a machine-readable storage medium and a computer device. Figure 13 It is a schematic diagram of a machine-readable storage medium 10 according to an embodiment of the present invention. Figure 14 It is a schematic diagram of a computer device 20 according to an embodiment of the present invention.

[0094] The machine-readable storage medium 10 stores thereon a machine-executable program 11, and when the machine-executable program 11 is executed by a processor, it implements the method for cleaning the transaction log data of the database in any of the above embodiments.

[0095] The computer device 20 may include a memory 210, a processor 220, and a machine-executable program 11 stored on the memory 210 and running on the processor 220, and when the processor 220 executes the machine-executable program 11, it implements the method for cleaning the transaction log data of the database in any of the above embodiments.

[0096] For the description of this embodiment, the machine-readable storage medium 10 can be any device that can contain, store, communicate, propagate, or transmit a program for use by or in connection with an instruction execution system, apparatus, or device. More specific examples (non-exhaustive list) of computer-readable media include the following: an electrical connection portion (electronic device) having one or more wirings, a portable computer disk cartridge (magnetic device), a random access memory (RAM), a read-only memory (ROM), an erasable programmable read-only memory (EPROM or flash memory), an optical fiber device, and a portable compact disc read-only memory (CDROM). In addition, the machine-readable storage medium 10 can even be paper or other suitable media on which the program can be printed, because the program can be obtained electronically, for example, by optically scanning the paper or other media, then editing, interpreting, or processing it in other suitable ways if necessary, and then storing it in a computer memory.

[0097] It should be understood that various parts of the present invention can be implemented by hardware, software, firmware, or a combination thereof. In the above embodiments, multiple steps or methods can be implemented by software or firmware stored in a memory and executed by a suitable instruction execution system.

[0098] The computer device 20 can be, for example, a server, a desktop computer, a laptop computer, a tablet computer, or a smart phone. In some examples, the computer device 20 can be a cloud computing node. The computer device 20 can be described in the general context of computer system-executable instructions, such as program modules, executed by a computer system. Generally, program modules can include routines, programs, object programs, components, logic, data structures, etc. that perform specific tasks or implement specific abstract data types. The computer device 20 can be implemented in a distributed cloud computing environment where tasks are executed by remote processing devices linked through a communication network. In a distributed cloud computing environment, program modules can be located on local or remote computing system storage media including storage devices.

[0099] The computer device 20 can include a processor 220 adapted to execute stored instructions and a memory 210 that provides temporary storage space for the operation of the instructions during operation. The processor 220 can be a single-core processor, a multi-core processor, a computing cluster, or any number of other configurations. The memory 210 can include random access memory (RAM), read-only memory, flash memory, or any other suitable storage system.

[0100] The processor 220 can be connected through a system interconnect (such as PCI, PCI-Express, etc.) to an I / O interface (input / output interface) adapted to connect the computer device 20 to one or more I / O devices (input / output devices). The I / O devices can include, for example, a keyboard and a pointing device, where the pointing device can include a touchpad or a touch screen, etc. The I / O devices can be built-in components of the computer device 20 or devices externally connected to the computing device.

[0101] The processor 220 may also be linked to a display interface adapted to connect the computer device 20 to a display device through the system interconnect. The display device may include a display screen as a built-in component of the computer device 20. The display device may also include a computer monitor, a television set, a projector, etc. externally connected to the computer device 20. In addition, a network interface controller (NIC) may be adapted to connect the computer device 20 to a network through the system interconnect. In some embodiments, the NIC may use any suitable interface or protocol (such as Internet Small Computer System Interface, etc.) to transmit data. The network may be a cellular network, a radio network, a wide area network (WAN), a local area network (LAN), or the Internet, etc. The remote device may be connected to the computing device through the network.

[0102] At this point, those skilled in the art should recognize that although a number of exemplary embodiments of the present invention have been shown and described in detail herein, many other variations or modifications consistent with the principles of the present invention can still be directly determined or derived from the disclosure of the present invention without departing from the spirit and scope of the present invention. Therefore, the scope of the present invention should be understood and construed to cover all such other variations or modifications.

Claims

1. A method for cleaning transaction log data of a database, comprising: Obtaining an event that the database completes an abnormal recovery process; Determining whether there is any transaction log to be redone in the abnormal recovery process; If so, obtaining the log sequence number of the last transaction log to be redone, denoted as the final redo sequence number; Cleaning invalid data in a preset page data table according to the final redo sequence number, where the preset page data table is used to store page mirror data separated from the transaction log.

2. The method for cleaning transaction log data of a database according to claim 1, wherein, The step of cleaning invalid data in the preset page data table according to the final redo sequence number includes: Searching for page mirror data with a log sequence number greater than the final redo sequence number in the page data table, and cleaning the found page mirror data.

3. The method for cleaning transaction log data of a database according to claim 1, in the case of determining that there is no transaction log to be redone in the abnormal recovery process, further comprising: Searching for the last valid page mirror data in the page data table, and the page mirror data regenerated after the database is started is written after the last valid page mirror data.

4. The method for cleaning transaction log data of a database according to claim 1, wherein The page data table is used to record the page mirror data and its related information, and the related information includes: The location information of the table to which the data page corresponding to the page mirror data belongs in the disk, the data block number of the data page corresponding to the page mirror data in the table to which it belongs, and the log sequence number of the transaction log corresponding to the page mirror data.

5. The method for cleaning transaction log data of a database according to claim 4, wherein The tuple corresponding to the location information of the table to which the data page corresponding to the page mirror data belongs in the disk includes: Table space object identifier, database object identifier, data table object identifier.

6. The method for cleaning transaction log data of a database according to claim 1, the generation process of the transaction log includes: Obtaining and registering transaction log data according to the transaction of the database; Assembling the transaction log in a preset format, and separating the page mirror data in the transaction log; Writing the page mirror data into the page data table, and flushing the remaining data of the transaction log after the page mirror data is written into the page data table.

7. The method for cleaning transaction log data of a database according to claim 6, wherein After flushing the remaining data of the transaction log after the page mirror data is written into the page data table, flushing the data page corresponding to the page mirror data.

8. The method for cleaning transaction log data of a database according to claim 6, wherein the step of assembling the transaction log in a preset format includes: Pre-allocating a storage location for the transaction log in the transaction log write cache according to the length of the transaction log; Placing the transaction log data in a continuous range; Copying the transaction log data in the continuous range to the transaction log write cache.

9. A machine-readable storage medium, on which a machine-executable program is stored, and when the machine-executable program is executed by a processor, it implements the method for cleaning transaction log data of a database according to any one of claims 1 to 8.

10. A computer device includes a memory, a processor, and a machine-executable program stored on the memory and running on the processor, and when the processor executes the machine-executable program, it implements the method for cleaning the transaction log data of the database according to any one of claims 1 to 8.

Images