Transaction log processing method of database, storage medium and equipment

Abstract

The invention provides a transaction log processing method of a database, a storage medium and equipment. The transaction log processing method of the database comprises the following steps: obtaining a trigger event of successfully creating a check point by the database; checking a basic backup of the database and a state of a transaction log of the database, wherein page mirror data in the transaction log is independently stored in a preset page data table; determining whether the basic backup has been backed up to a state in which all the transaction logs exist; and if yes, cleaning the page data table according to the application scene of the database. According to the scheme, the disk space occupied by the page data table used for storing the page mirror image data can be greatly reduced on the premise that the data security of the database is guaranteed.

Description

Technical Field

[0001] The present invention relates to the technical field of databases, and particularly to a method for processing transaction logs of a database, a storage medium, and a device. Background Art

[0002] A transaction log (or called XLOG log) is a Write Ahead Log (WAL) log. An important function of it is to build a primary and standby database cluster. After the primary machine in the database cluster is damaged, the standby machine can immediately be upgraded to the primary machine and take over the primary machine to continue providing services externally.

[0003] During the process of the primary machine providing services externally, transaction logs are continuously generated. 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 primary machine data by receiving and repeatedly executing these transaction logs. When the database is under high 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.

[0004] Therefore, improving the streaming replication efficiency between the primary database and the standby database has become an urgent problem to be solved in this field. Summary of the Invention

[0005] An object of the present invention is to provide a method for processing transaction logs of a database, a storage medium, and a device that can improve the streaming replication efficiency of transaction logs between the primary database and the standby database.

[0006] A further object of the present invention is to reduce the amount of transaction log data by storing page mirror data independently in a preset page data table.

[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] Yet another further object of the present invention is to make the transaction logs and their page mirror data meet the requirements of the database application scenario for data processing speed and security.

[0009] Specifically, the present invention provides a method for processing transaction logs of a database, including:

[0010] Obtaining a trigger event for successfully creating a checkpoint in the database;

[0011] Checking the status of the base backup of the database and the transaction logs of the database, where the page mirror data in the transaction logs is independently stored in a preset page data table;

[0012] Determining whether all the transaction logs generated from the most recent base backup to the current running time exist;

[0013] If so, clean the page data table according to the application scenario of the database.

[0014] Optionally, before the step of obtaining the trigger event that the database successfully creates a checkpoint, it further includes:

[0015] Create a checkpoint log, which is used to record the checkpoints established before and after each base backup and their corresponding redo point log sequence numbers.

[0016] Optionally, the step of cleaning the page data table according to the application scenario of the database includes:

[0017] Judge the requirement of the application scenario of the database for the data block recovery speed;

[0018] If the requirement for the data block recovery speed exceeds the preset speed, clear the page mirror data in the page data table whose log sequence number is less than the set clearing sequence number, and the clearing sequence number is the log sequence number corresponding to the redo point of the checkpoint established before the most recent base backup.

[0019] Optionally, the step of clearing the page mirror data in the page data table whose log sequence number is less than the set clearing sequence number includes:

[0020] Query according to the location information of the page data table and the block number of the page mirror data, and search for the page mirror data whose log sequence number is greater than or equal to the set clearing sequence number as the reserved page mirror data;

[0021] Delete other page mirror data except the reserved page mirror data.

[0022] Optionally, when the requirement for the data block recovery speed is less than the preset speed, retain the latest set number of page mirror data or the page mirror data within the latest set time period in the page data table.

[0023] Optionally, when it is determined that the base backup has not been backed up to the state where all transaction logs exist, identify the requirement of the application scenario of the database for data security;

[0024] Select whether to clean the page data table according to the requirement of the application scenario for data security.

[0025] Optionally, the step of selecting whether to clean the page data table according to the requirement of the application scenario for data security includes:

[0026] When the requirement for data security exceeds the preset security level, prohibit cleaning the page data table and output a prompt message for performing a base backup;

[0027] When the requirement for data security is lower than the preset security level, allow cleaning the page data table and output a cleaning risk prompt message.

[0028] Optionally, after the step of cleaning the page data table according to the application scenario of the database, the following is further included:

[0029] During the process of the database restoring and redoing the transaction log, detect whether there is a bad block;

[0030] If there is a bad block, repair the bad block using the page data table or the base backup according to the log sequence number corresponding to the bad block.

[0031] In another aspect, the present invention also provides 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 transaction log processing method of the database according to any one of the above.

[0032] In still 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, and when the processor executes the machine-executable program, it implements the transaction log processing method of the database according to any one of the above.

[0033] The transaction log processing method of the database of the present invention selects to perform the operation of cleaning the page data table after the time when the database successfully creates a checkpoint. After the database successfully creates a checkpoint, the data and status of the database before this checkpoint have been completely written to the disk, and there is a possibility that the transaction log file can be cleared. The method of the present invention checks the status of the base backup of the database and the transaction log of the database, and confirms whether all the transaction logs generated from the most recent base backup to the current running time exist, that is, confirms that the condition for cleaning the page data table is met. Then, the page data table is cleaned according to the application scenario of the database, meeting the requirements of the application scenario of the database.

[0034] Further, in the transaction log processing method of the database of the present invention, the page mirror data is independently stored in a preset page data table, and the page mirror data is managed by using the table tuple management method of the database, greatly reducing the management difficulty of the page mirror data in the transaction log file.

[0035] Furthermore, in the transaction log processing method of the database of the present invention, the application scenario of the database is determined to determine the requirements for the data block recovery speed and the security requirements, and the cleaning method is set specifically, meeting the specific requirements of the database and expanding the usage range of the database. The solution of the present invention is applicable to relational databases, especially applicable to the KingbaseES database (abbreviated as KES database), enriching the functions of such databases and improving the efficiency of the databases.

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

[0037] Some specific embodiments of the present invention will be described in detail hereinafter with reference to the accompanying drawings in an exemplary but not 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:

[0038] Figure 1 is a schematic diagram of the logical structure of a transaction log in a transaction log processing method of a database according to an embodiment of the present invention;

[0039] Figure 2 is a schematic diagram of the process of generating a transaction log in a transaction log processing method of a database according to an embodiment of the present invention;

[0040] Figure 3 is a schematic diagram in the form of a linked list of an original transaction log with page mirror data in a transaction log processing method of a database according to an embodiment of the present invention;

[0041] Figure 4 is Figure 3 a schematic diagram in the form of a linked list after removing the page mirror data from the linked list shown;

[0042] Figure 5 is a schematic diagram of the simplification of the linked list of two page mirror data nodes in a transaction log in a transaction log processing method of a database according to an embodiment of the present invention;

[0043] Figure 6 is a schematic diagram of the specific content of a page mirror data node in a transaction log in a transaction log processing method of a database according to an embodiment of the present invention;

[0044] Figure 7 is a schematic diagram of the final state of a transaction log in a transaction log processing method of a database according to an embodiment of the present invention;

[0045] Figure 8 is a schematic diagram of the global page data linked list in a transaction log processing method of a database according to an embodiment of the present invention;

[0046] Figure 9 is a schematic diagram of a transaction log processing method of a database according to an embodiment of the present invention;

[0047] Figure 10 is a schematic diagram of a checkpoint log in a transaction log processing method of a database according to an embodiment of the present invention;

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

[0049] Figure 12 Schematic diagram of the first data recovery situation in a method for processing a transaction log of a database according to an embodiment of the present invention;

[0050] Figure 13 Schematic diagram of the second repair situation in a method for processing a transaction log of a database according to an embodiment of the present invention;

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

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

[0053] 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.

[0054] 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, which 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 in combination with these instruction execution systems, apparatus or devices.

[0055] 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.

[0056] 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 log generated by the primary database, and thus reduce the transmission pressure of the streaming replication between the primary database and the standby database. Figure 1It is a schematic diagram of the logical structure of a transaction log in the transaction log processing method 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 head data) 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 and tuple data. 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.

[0057] It should be noted that different types of transaction logs may have different structures. Each transaction log necessarily contains head data, but does not necessarily contain page mirror data, tuple data, and main data.

[0058] During the process of operating system crash, some operating system pages (such as pages with a size of 4KB) may not have been written to the disk in time, which may cause a database page (such as a page composed of two operating system pages with a size of 8KB) to contain a mixture of old and new data (the mixture of 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 that contains a mixture of old and new data in the database. When a database page (for example, 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 page mirror data to save the complete database page mirror.

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

[0060] 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.

[0061] Figure 2 It is a schematic diagram of the process of generating a transaction log in the transaction log processing method of a database according to an embodiment of the present invention. The generation process of this transaction log includes:

[0062] Step S201, generate transaction log data. Since the transaction log assembly process selectively stores transaction log data according to certain flag bits, the final data content in the transaction log cannot be fully determined at this stage.

[0063] Step S202, register the transaction log data. Similar to step S201, the final data content in the transaction log cannot be fully determined at this stage either. Therefore, the separation operation of page mirror data cannot be performed.

[0064] Step S203, assemble the 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. Additionally, at this stage, the position of the to-be-written transaction log in the transaction log write cache has not been determined. Only by calculating the total length of the transaction log after page mirror data separation and pre-allocating the storage position of the transaction log in the transaction log write cache according to this length can the impact of the change in the storage position information of the transaction log caused by the reduction of the data size after page mirror data separation of the transaction log be eliminated. Therefore, this stage is suitable for performing the page mirror data separation operation on the transaction log.

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

[0066] Step S205, place the data of each part of the transaction log into a continuous range.

[0067] Step S206, copy the transaction data in the continuous range to the transaction log write cache.

[0068] Step S207, write the transaction log write cache page storing the transaction log in the transaction log write cache to the disk.

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

[0070] During the generation process of the transaction log, when all the contents of a transaction log are determined, the transaction log is assembled into a structure consisting 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.

[0071] Figures 3 to 7 It shows 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 3 is a schematic diagram of the linked list form of the original transaction log with page mirror data in the transaction log processing method of the database according to an embodiment of the present invention, Figure 4 is Figure 3 a schematic diagram of the linked list form after removing the 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 represents the head data of the transaction log; page data represents the page mirror data in the transaction log; the tuple data and the main data represent other data in the transaction log except page mirror data. Figure 5 is a simplified schematic diagram of the linked list of two page mirror data nodes in the transaction log in the transaction log processing method of the 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 6 is a schematic diagram of the specific content of a page mirror data node in the transaction log processing method of the 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 in the disk (the location information of the table where the data disk page corresponding to page data is located), and BlockNumber represents the location in 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 in the disk. Lsn (LogSequence Number) represents the log sequence number corresponding to the page mirror data. next represents pointing to the next node. Figure 7 is a schematic diagram of the final state of the transaction log in the transaction log processing method of the database according to an embodiment of the present invention.

[0072] 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.

[0073] Figure 8 It is a schematic diagram of the global page data linked list in the transaction log processing method of the database according to an embodiment of the present invention. Two page mirror data linked lists are 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_global linked list, and it is located in the shared memory.

[0074] 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.

[0075] As can be seen from the above process, the pagedata_table stores the page mirror data that has been flushed 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. An overly large pagedata_table will occupy a large amount of storage space. The method of this embodiment further improves and proposes a solution to reduce the storage space of the host occupied by the pagedata_table while ensuring the consistency of the primary and standby databases.

[0076] Figure 9 FIG. is a schematic diagram of a method for processing a transaction log of a database according to an embodiment of the present invention. The method for processing the transaction log of the database generally may include:

[0077] Step S901: Obtain a trigger event for successfully creating a checkpoint in the database. In this embodiment, the cleaning work of the transaction log is generally performed after the successful creation of a checkpoint. When a checkpoint is created, the current state of the database is completely written to disk. After the checkpoint is successfully created, since recovery can start from the latest checkpoint, previous transaction logs may no longer be needed. Therefore, the cleaning process of the pagedata_table is also performed after the successful creation of a checkpoint. Checkpoints are established before and after each base backup is created. Before step S901, it may further include: creating a checkpoint log for recording the log sequence numbers of the checkpoints established before and after each base backup is created.

[0078] Step S902: Check the status of the base backup of the database and the transaction log of the database, where the page mirror data in the transaction log is independently stored in a preset page data table.

[0079] Step S903: Determine whether all the transaction logs generated from the most recent base backup to the current running time exist;

[0080] Step S904: When all the transaction logs exist, clean the page data table according to the application scenario of the database. An optionally specific implementation manner is: determine the requirement of the application scenario of the database for the data block recovery speed; if the requirement for the data block recovery speed exceeds a preset speed, clear the page mirror data in the page data table whose log sequence number is less than the set clearing sequence number, and the clearing sequence number is the log sequence number of the corresponding re-do point of the checkpoint established before the most recent base backup is created.

[0081] The application scenarios of different databases have different requirements for the data block recovery speed. For example, in the fields of finance, e-commerce, etc., there are extremely high requirements for data accuracy, integrity, and real-time performance. For databases applied to scenarios with high requirements for data block recovery speed, the page mirror data in the page data table can be cleared in a timely manner, and the page mirror data in the pagedata_table table with an LSN less than backup_begin_lsn can be cleared. This is because the speed of data block recovery from the pagedata_table table is obviously greater than the speed of recovery from the base backup. The requirements of the above database application scenarios for the data block recovery speed can be determined by the configuration information and usage records of the database.

[0082] Among them, the steps to clear the page mirror data with a log sequence number less than the set clearing sequence number in the page data table may include: query according to the location information of the page data table and the block number of the page mirror data, and search for the page mirror data with a log sequence number greater than or equal to the set clearing sequence number as the reserved page mirror data; delete other page mirror data except the reserved page mirror data.

[0083] When the requirement for the data block recovery speed is less than the preset speed, the latest set number of page mirror data or the page mirror data within the latest set time period can be retained in the page data table. That is, for databases with lower requirements for data block recovery speed, the retention amount of the pagedata_table table can be more flexible. For example, retain the latest set number of rows (e.g., 20,000 rows, the specific number can be adjusted), or retain the page mirror data within the latest set time period (e.g., generated within the most recent 2 hours, the specific duration can be adjusted). In some extreme cases, the page mirror data can even not be retained. Because the page mirror data required for block repair can be obtained from the base backup.

[0084] When it is determined in step S903 that there is no base backup or there is a missing transaction log from the most recent base backup to the current running time, the requirements of the database application scenario for data security can also be identified; whether to clean the page data table is selected according to the requirements of the application scenario for data security. For example, when the requirement for data security exceeds the preset security level, cleaning the page data table is prohibited, and a prompt message for performing a base backup is output; when the requirement for data security is lower than the preset security level, cleaning the page data table is allowed, and a cleaning risk prompt message is output. The requirements of the above database application scenario for data security can also be determined by the configuration information and usage records of the database.

[0085] In the case where no base backup is created, if the database has high requirements for data security, cleaning of page mirror data may not be allowed, and the database administrator may be prompted to create a base backup to avoid data loss. If the database has low requirements for data security, a risk may be prompted and cleaning may be allowed. For example, only page mirror data within a certain period (e.g., 1 day) may be retained, and expired page mirror data may be directly cleaned. If bad blocks are found in the expired page mirror data, they cannot be recovered.

[0086] After the step of cleaning the page data table according to the application scenario of the database in step S904, the following steps are further included: During the process of the database restoring and redoing the transaction log, it is detected whether bad blocks (broken pages) occur; if bad blocks (broken pages) occur, the bad blocks (broken pages) are repaired using the page data table or the base backup according to the log sequence number corresponding to the bad blocks (broken pages).

[0087] In the database of this embodiment, before and after creating the base backup, a checkpoint log will be created as the boundary between the start and end of the base backup, and the start position information will be recorded in a specified file (e.g., backup_label). Figure 10 It is a schematic diagram of the checkpoint log in the transaction log processing method of the database according to an embodiment of the present invention. The start LSNs of these two checkpoint logs are respectively denoted as backup_begin_lsn and backup_end_lsn. The redo point recorded in the checkpoint log at backup_begin_lsn is denoted as begin_redo_lsn, and the redo point recorded in the checkpoint log at backup_end_lsn is denoted as end_redo_lsn.

[0088] In the case where a base backup exists and all transaction logs from the most recent base backup to the current one exist, when a data page page corresponds to multiple pagedata_table table tuples, for example Figure 11 as shown in the figure. The corresponding page mirror data can be found by searching in the pagedata_table table through RelFileNode and BlockNumber.

[0089] For example, the specific SQL command can be:

[0090] SELECT lsn FROM pagedata_table WHERE tablespace oid = 1664 AND

[0091] database oid = 12001 AND table oid = 16385 AND BlockNumber in (5, 6)

[0092] From Figure 11 As can be obtained from the table in Figure 11 , the above SQL can search for three tuples with LSNs of 0 / 6363C50, 0 / 6364C50, and 0 / 6365C50 respectively. During cleaning, only the tuple with the largest LSN can be retained, and other tuples are cleaned up.

[0093] During the redo of the transaction log in the database recovery process, if the page of the data disk involved in the transaction log content is damaged, it may be impossible to find the page mirror data corresponding to this bad block. The transaction log LSN of the page mirror data corresponding to this bad block can be denoted as need_lsn, and the final recovery end position (denoted as recovery_end) is obviously greater than or equal to need_lsn. The recovery processes for different situations are analyzed below.

[0094] If need_lsn is less than the lsn (denoted as lsn_in_table) corresponding to the page data retained in the pagedata_table table, only the bad block information can be recorded without rushing to repair. Figure 12 FIG. is a schematic diagram of the first data repair situation in the transaction log processing method of the database according to an embodiment of the present invention. In this case, if recovery_end is greater than or equal to lsn_in_table, then this bad block can be repaired when redoing the transaction log at lsn_in_table. Figure 13 FIG. is a schematic diagram of the second repair situation in the transaction log processing method of the database according to an embodiment of the present invention. In this case, if recovery_end is less than lsn_in_table and greater than or equal to need_lsn, then the block repair technology needs to be used to repair the bad block. Due to the existence of the base backup, the starting lsn for repairing the bad block can be the lsn position where the base backup starts.

[0095] If need_lsn is greater than lsn_in_table, the bad block is probably generated when redoing the transaction log between lsn_in_table and need_lsn, and the block repair technology is used to repair the bad block. Since the nearest historical version corresponding to the bad block is in the pagedata_table table, the starting lsn for repairing the bad block can be lsn_in_table.

[0096] It can be seen from this that the above cleaning will not have a negative impact on the repair of page damage to the data disk. Therefore, when there is a base backup and the transaction logs from the most recent base backup to the current one all exist, by using the block repair technology in combination, for multiple tuples corresponding to the same page in the pagedata_table table, only the tuple with the largest LSN needs to be retained. For other tuples, the size of the pagedata_table can be further reduced.

[0097] This embodiment also provides a machine-readable storage medium and a computer device. Figure 14 It is a schematic diagram of a machine-readable storage medium 10 according to an embodiment of the present invention. Figure 15 It is a schematic diagram of a computer device 20 according to an embodiment of the present invention.

[0098] 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 transaction log processing method of the database in any of the above embodiments.

[0099] 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 transaction log processing method of the database in any of the above embodiments.

[0100] For the description of this embodiment, the machine-readable storage medium 10 may 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 with one or more wirings (electronic device), a portable computer diskette (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). Additionally, 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, followed by editing, interpretation, or otherwise processing as appropriate, and then storing it in a computer memory.

[0101] 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.

[0102] 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 particular tasks or implement particular abstract data types. The computer device 20 can be implemented in a distributed cloud computing environment where tasks are performed 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.

[0103] 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.

[0104] 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 can be devices externally connected to the computing device.

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

[0106] At this point, those skilled in the art should recognize that although numerous exemplary embodiments of the present invention have been shown and described in detail herein, many other variations or modifications that conform to the principles of the present invention can still be directly determined or derived from the disclosed content 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 as covering all such other variations or modifications.

Claims

1. A method for processing transaction logs of a database, comprising: Obtaining a trigger event for the database to successfully create a checkpoint; Checking the status of the base backup of the database and the transaction log of the database, wherein the page mirror data in the transaction log is independently stored in a preset page data table; Determining whether all the transaction logs generated from the most recent base backup to the current running time exist; If so, cleaning the page data table according to the application scenario of the database.

2. The method for processing a transaction log of a database according to claim 1, wherein, Before the step of obtaining the trigger event for the database to successfully create a checkpoint, it further includes: Creating a checkpoint log, which is used to record the log sequence numbers of the checkpoints established before and after the creation of each base backup.

3. The method for processing a transaction log of a database according to claim 2, wherein, The step of cleaning the page data table according to the application scenario of the database includes: Judging the requirement of the application scenario of the database for the data block recovery speed; If the requirement for the data block recovery speed exceeds a preset speed, clearing the page mirror data in the page data table whose log sequence number is less than the set clearing sequence number, and the clearing sequence number is the log sequence number of the corresponding redo point of the checkpoint established before the creation of the most recent base backup.

4. The method for processing a transaction log of a database according to claim 3, wherein, The step of clearing the page mirror data in the page data table whose log sequence number is less than the set clearing sequence number includes: Querying according to the location information of the page data table and the block number of the page mirror data, and searching for the page mirror data whose log sequence number is greater than or equal to the set clearing sequence number as the reserved page mirror data; Deleting other page mirror data except the reserved page mirror data.

5. The method for processing transaction logs of a database according to claim 3, wherein, When the requirement for the data block recovery speed is less than the preset speed, retain the latest set number of page mirror data or the page mirror data within the latest set time period in the page data table.

6. The method for processing transaction logs of a database according to claim 1, wherein When it is determined that the base backup has not been backed up to the state where all the transaction logs exist, identifying the requirement of the application scenario of the database for data security; Selecting whether to clean the page data table according to the requirement of the application scenario for data security.

7. The method for processing a transaction log of a database according to claim 6, wherein, The step of selecting whether to clean the page data table according to the requirement of the application scenario for data security includes: When the requirement for data security exceeds a preset security level, prohibit cleaning the page data table and output a prompt message for performing the base backup; When the requirement for data security is lower than the preset security level, allow cleaning the page data table and output a cleaning risk prompt message.

8. The method for processing a transaction log of a database according to claim 1, wherein, After the step of cleaning the page data table according to the application scenario of the database, it further includes: During the process of the database restoring and redoing the transaction log, detecting whether a bad block appears; If the bad block appears, repairing the bad block using the page data table or the base backup according to the log sequence number corresponding to the bad block.

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

10. A computer device, comprising 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 processing a transaction log of the database according to any one of claims 1 to 8.

Images