Database processing method, storage medium and equipment

Abstract

The invention relates to a database technology, in particular to a database processing method, a storage medium and equipment. The processing method comprises the steps of obtaining a log range of a transaction log required by page mirror image data recovery; according to the position information of the page mirror image data recorded by all the transaction logs in the log range, creating an index set; according to the index set, obtaining to-be-recovered page mirror image data from the page mirror image storage table and / or the transaction log containing the page mirror image data; and storing the obtained to-be-recovered page mirror image data and the position information thereof into a pre-created page mirror image recovery table to complete page mirror image data recovery. According to the database processing method, the index set is created to obtain the page mirror image data to be recovered from the page mirror image storage table and / or the transaction log containing the page mirror image data, so that the problem that plug-ins partially depending on the page mirror image data cannot be normally used due to the fact that the page mirror image data in the page mirror image storage table is reduced is solved; and stable operation of the database is ensured.

Description

Technical Field

[0001] The present invention relates to database technology, and particularly to a method for processing a database, a storage medium, and a device. Background Art

[0002] During the use of a database, transaction logs are recorded. An important function of the transaction logs is to be used for constructing a primary and standby database cluster. Briefly speaking, after the primary and standby database cluster is constructed, the primary database provides read and write services externally and continuously generates transaction logs. The transaction logs are transmitted to the standby database through streaming replication, so that the standby database is almost identical to the primary database.

[0003] In order to strictly keep the data of the primary database and the standby database identical, a synchronous streaming replication mode is usually adopted. In the synchronous streaming replication mode, after the primary database sends a transaction log to the standby database, it needs to wait for the standby database to store this transaction log locally or replay it successfully before the primary database can continue to execute. Therefore, the amount of data of the transaction logs that the primary database needs to transmit to the standby database is an important factor affecting the performance of synchronous streaming replication.

[0004] The inventor has recognized that since the page mirror data in the transaction logs stores the page data of the database pages, separating the page mirror data in some of the transaction logs in the database and storing it separately in a page mirror storage table can, to a certain extent, reduce the amount of data of the transaction logs while reducing the size of the page mirror storage table to ensure the write process performance of the transaction logs.

[0005] However, in some scenarios, such as when the walminer plugin in the database that depends on the page mirror data is running, if some of the page mirror data is stored in the page mirror storage table and other parts of the page mirror data remain in the transaction logs, it may cause the page mirror data required when the walminer is running to be unable to be directly obtained from the page mirror storage table, affecting the normal use of the walminer plugin.

[0006] The above information disclosed in this background art is only used to increase the understanding of the background art of the present application. Therefore, it may include prior art that is not known to those of ordinary skill in the art. Summary of the Invention

[0007] In view of the above problems, a method for processing a database, a storage medium, and a device that overcome the above problems or at least partially solve the above problems are proposed.

[0008] An object of the present invention is to complete the recovery of page mirror data in the case where only the page mirror data separated from some of the transaction logs is stored in the page mirror storage table.

[0009] A further object of the present invention is to improve the accuracy of obtaining page mirror data to be restored.

[0010] In particular, the present invention provides a method for processing a database. A page mirror storage table and a page mirror recovery table are pre-created in the database. The page mirror storage table stores page mirror data separated from part of the transaction logs generated by the database and their location information. The page mirror recovery table is used to store the page mirror data to be restored and their location information. And the processing method includes:

[0011] Obtain the log range of the transaction logs required for page mirror data recovery. There is at least one transaction log within the log range;

[0012] According to the location information of the page mirror data recorded in all the transaction logs within the log range, create an index set for finding the page mirror data to be restored;

[0013] According to the index set, obtain the page mirror data to be restored from the page mirror storage table and / or the transaction logs containing the page mirror data;

[0014] Store the obtained page mirror data to be restored and their location information into the page mirror recovery table to complete the recovery of the page mirror data.

[0015] Optionally, according to the index set, obtaining the page mirror data to be restored from the page mirror storage table and / or the transaction logs containing the page mirror data includes:

[0016] Search for the page mirror data to be restored in the page mirror storage table according to the index set, and update the index set according to the search result;

[0017] Judge whether the updated index set is an empty set;

[0018] If not, start from the transaction log at the preset starting position of the redo transaction log, and redo the transaction log according to the location information of the page mirror data in the updated index set to obtain the page mirror data to be restored.

[0019] Optionally, the starting point of the log range is after the starting position of the base backup; and

[0020] The preset starting position of the redo transaction log is the starting position of the base backup.

[0021] Optionally, the preset starting position of the redo transaction log is before the starting address of the first transaction log within the log range and is separated from the starting address by a preset length.

[0022] Optionally, starting from the transaction log at the preset starting position of the redo transaction log, redoing the transaction log according to the location information of the page mirror data in the updated index set to obtain the page mirror data to be restored includes:

[0023] Redo the transaction log between the starting position and the starting address of the preset redo transaction log according to the position information of the page mirror data in the updated index set, so as to obtain the page mirror data to be restored.

[0024] Optionally, starting from the transaction log at the preset starting position of the redo transaction log, redo the transaction log according to the position information of the page mirror data in the updated index set, and obtaining the page mirror data to be restored includes:

[0025] Obtain the position information of the page mirror data of the transaction log record to be redone;

[0026] According to the position information of the page mirror data of the transaction log record to be redone and the updated index set, find the page mirror data to be restored in the transaction log record to be redone, and update the index set according to the search result;

[0027] Judge whether the re-updated index set is an empty set;

[0028] If it is an empty set, stop the operation of redoing the transaction log;

[0029] If it is not an empty set, take the next transaction log as the transaction log to be redone, and continue to execute the step of obtaining the position information of the page mirror data of the transaction log record to be redone.

[0030] Optionally, the position information of the page mirror data includes the log sequence number of the transaction log corresponding to the page mirror data and the position information of the data page corresponding to the page mirror data; and

[0031] According to the position information of the page mirror data of the transaction log record to be redone and the updated index set, find the page mirror data to be restored in the transaction log record to be redone, and updating the index set according to the search result includes:

[0032] Judge whether the updated index set contains the position information of the data page of the transaction log record to be redone;

[0033] If it does not contain, confirm that the transaction log record to be redone does not contain the page mirror data to be restored, and use the updated index set as the re-updated index set;

[0034] If it contains, obtain the minimum value of the log sequence number corresponding to the position information of the data page of the transaction log record to be redone in the updated index set;

[0035] Compare the log sequence number of the transaction log record to be redone with the minimum value of the log sequence number;

[0036] When the log sequence number of the transaction log to be redone is less than the minimum log sequence number, apply the content of the transaction log to be redone to the data page corresponding to the transaction log to be redone, and use the updated index set as the re-updated index set;

[0037] When the log sequence number of the transaction log to be redone is greater than the minimum log sequence number, send an error signal, and use the updated index set as the re-updated index set;

[0038] When the log sequence number of the transaction log to be redone is equal to the minimum log sequence number, apply the content of the transaction log to be redone to the data page corresponding to the transaction log to be redone, and use the page mirror data and its location information in the transaction log to be redone as the obtained page mirror data and its location information to be recovered, and remove the page mirror data and its location information in the transaction log to be redone from the index set to obtain a re-updated index set.

[0039] Optionally, the index set stores the location information of the page mirror data of all transaction log records within the log range in the form of a hash table.

[0040] According to another aspect of the present invention, there is also provided a machine-readable storage medium, on which a machine-executable program is stored, and when the machine-executable program is executed by a processor, the processing method of any one of the above databases is implemented.

[0041] According to yet another aspect of the present invention, there is also provided 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, the processing method of any one of the above databases is implemented.

[0042] Processing method for a database of the present invention. A page mirror storage table is pre-created in the database, which stores page mirror data separated from part of the transaction logs generated by the database and its location information, so as to reduce the data volume of the transaction logs to a certain extent while effectively controlling the size of the page mirror storage table to ensure the write process performance of the transaction logs. The database also pre-creates a page mirror recovery table for storing the page mirror data to be recovered and its location information, so as to recover the page mirror data. The processing method for the database of the present invention realizes the summary of index information by obtaining the log range of the transaction logs required for page mirror data recovery, and creating an index set for finding the page mirror data to be recovered according to the location information of the page mirror data in all transaction log records within the log range. According to the index set, the page mirror data to be recovered is obtained from the page mirror storage table and / or the transaction logs containing the page mirror data, and the obtained page mirror data to be recovered and its location information are stored in the page mirror recovery table to complete the recovery of the page mirror data, realizing the recovery of the page mirror data in the case where only the page mirror data separated from part of the transaction logs is stored in the page mirror storage table, so that plugins relying on the page mirror data, such as the walminer plugin, can be used normally.

[0043] Further, in the processing method for the database of the present invention, by sequentially searching for the page mirror data to be recovered from the page mirror storage table and from the transaction logs starting from the preset redo transaction log start position according to the index set, and updating the index set according to the search results until the index set is an empty set, the accurate acquisition of all the page mirror data to be recovered is realized, and the accuracy of obtaining the page mirror data to be recovered is improved.

[0044] Those skilled in the art will become more clear about the above and other objects, advantages and features of the present invention according to the following detailed description of specific embodiments of the present invention in conjunction with the accompanying drawings. Description of the Drawings

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

[0046] Figure 1 is a schematic flow chart of the processing method for a database according to an embodiment of the present invention;

[0047] Figure 2 is a schematic structural diagram of the page mirror storage table in the processing method for a database according to an embodiment of the present invention;

[0048] Figure 3Schematic diagram of the structure of the page mirror recovery table in the database processing method according to an embodiment of the present invention;

[0049] Figure 4 Schematic diagram of the structure of the transaction log in the database processing method according to an embodiment of the present invention;

[0050] Figure 5 Schematic diagram of the conversion from the first assembly linked list to the second assembly linked list in the database processing method according to an embodiment of the present invention;

[0051] Figure 6 Schematic diagram of the structure of the global page data linked list in the database processing method according to an embodiment of the present invention;

[0052] Figure 7 Schematic diagram of the structure of the page mirror linked list node in the database processing method according to an embodiment of the present invention;

[0053] Figure 8 Schematic diagram of the structure of the transaction log file in the database processing method according to an embodiment of the present invention;

[0054] Figure 9 Schematic diagram of the log range in the database processing method according to an embodiment of the present invention;

[0055] Figure 10 Schematic diagram of the structure of the transaction log file in the database processing method according to another embodiment of the present invention;

[0056] Figure 11 Schematic diagram of the process of the database processing method according to an embodiment of the present invention;

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

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

[0059] Hereinafter, the exemplary embodiments of the present invention will be described in more detail with reference to the accompanying drawings. Although the exemplary embodiments of the present invention are shown in the drawings, it should be understood that the present invention can be implemented in various forms and should not be limited by the embodiments set forth herein. On the contrary, these embodiments are provided so that the present disclosure can be more thoroughly understood and the scope of the present invention can be fully conveyed to those skilled in the art.

[0060] To solve the above technical problems, an embodiment of the present invention proposes a database processing method. Figure 1Schematic flowchart of a method for processing a database according to an embodiment of the present invention. Figure 2 Schematic structural diagram of a page mirror storage table in a method for processing a database according to an embodiment of the present invention. Figure 3 Schematic structural diagram of a page mirror recovery table in a method for processing a database according to an embodiment of the present invention. As Figure 1 shown, the method for processing a database generally may include:

[0061] Step S102, obtaining a log range of transaction logs required for page mirror data recovery, where there is at least one transaction log within the log range.

[0062] Step S104, creating an index set for finding page mirror data to be recovered according to the location information of the page mirror data recorded in all transaction logs within the log range.

[0063] Step S106, obtaining the page mirror data to be recovered from the page mirror storage table and / or the transaction logs containing the page mirror data according to the index set.

[0064] Step S108, storing the obtained page mirror data to be recovered and its location information into the page mirror recovery table to complete the page mirror data recovery.

[0065] In the above step S106, a page mirror storage table is pre-created in the database. As Figure 2 shown, the page mirror storage table stores page mirror data (denoted as page data) separated from some transaction logs generated by the database and its location information.

[0066] In the above step S108, as Figure 3 shown, a page mirror recovery table (denoted as walminer_need_page table) is pre-created in the database. The page mirror recovery table is used to store the page mirror data to be recovered and its location information.

[0067] In the method for processing a database of this embodiment, a page mirror storage table for storing page mirror data separated from some transaction logs generated by the database and its location information is pre-created in the database, so as to reduce the data volume of the transaction logs to a certain extent while effectively controlling the size of the page mirror storage table to ensure the write process performance of the transaction logs. The database also pre-creates a page mirror recovery table for storing the page mirror data to be recovered and its location information, so as to recover the page mirror data.

[0068] In addition, for the database processing method of this embodiment, by obtaining the log range of the transaction logs required for page mirror data recovery, and creating an index set for finding the page mirror data to be recovered according to the location information of the page mirror data in all the transaction logs within the log range, the summary of index information is realized. According to the index set, the page mirror data to be recovered is obtained from the page mirror storage table and / or the transaction logs containing the page mirror data, and the obtained page mirror data to be recovered and its location information are stored in the page mirror recovery table to complete the page mirror data recovery. When only the page mirror data separated from some transaction logs is stored in the page mirror storage table, the page mirror data recovery is completed, so that plugins dependent on the page mirror data, such as the walminer plugin, can be used normally.

[0069] In a database, the transaction log refers to the XLOG log (or WAL log), and the XLOG log details the operation process of the service process on the database. During the operation of the database, multiple XLOG logs are generated. At least one transaction log file is pre-created in the database, and multiple XLOG logs are stored in each transaction log file. For all the XLOG logs in a transaction log file, the log sequence number (denoted as lsn) of each XLOG log increases sequentially. The content of the XLOG log is described below.

[0070] Figure 4 It is a schematic structural diagram of the transaction log in the database processing method according to an embodiment of the present invention. As Figure 4 shown, an XLOG log may include an XLOG log header (denoted as head data) and an XLOG log data area.

[0071] Specifically, the XLOG log data area may include multiple block data areas (denoted as block) and main data (denoted as maindata), and each block may contain page data and tuple data (denoted as tupledata). The XLOG log header includes the XLogRecord structure, the header data of each block, and the header data of the main data. The header data of the page data is denoted as page data header.

[0072] It should be noted that different types of XLOG logs have different compositions. Each XLOG log contains headdata, but not necessarily page data, tupledata, and main data. Some types of XLOG logs only have head data without an XLOG log data area; some other types of XLOG logs only have head data and main data; and some other types of XLOG logs only have head data, page data, and tupledata, and each block may contain both page data and tupledata, or may only contain tupledata. That is to say, a single XLOG log may or may not contain page data.

[0073] During the process of the database's operating system crash, some operating system pages (such as those with a page size of 4KB) may not have been written to the disk in time, which may result in a database page (such as one composed of two operating system pages) containing a mixture of old and new data. During the recovery period after the database's operating system crash, since the information stored in the XLOG log is not complete enough, it is impossible to fully recover the database pages containing a mixture of old and new data in the database. In the prior art, in order to recover such database pages, after the redo point of the checkpoint (checkpoint log), when a database page (with a default size of 8KB) is first modified (updated), the entire database page is written to the XLOG log. At this time, the XLOG log contains page mirror data to save the complete database page mirror. It should be noted that the redo point is a special point in the XLOG log. Before this point, all the data in the database is the same as the information reflected by the XLOG log that has been written to the disk. During the creation of a checkpoint, a special XLOG log (denoted as the checkpoint log) is created. A redo point is recorded in this checkpoint log. Only when the XLOG logs and user data before this redo point have all been written to the disk can the checkpoint be successfully created. When the database crashes, we can find a redo point from the most recent checkpoint log, and then start playing back the XLOG log from this redo point to recover the database. In the case of a broken page during the database recovery process, the broken page is replaced according to the page mirror data in the XLOG log, thus ensuring the security and stability of the database data. Therefore, the data volume of the page mirror data in the XLOG log is equivalent to the data volume of the database page, and the data volume of the XLOG log containing page mirror data is relatively large.

[0074] Based on this, in this embodiment, before the above-mentioned step S102, the processing method of the database of the present invention further includes the following steps: separating the page mirror data in some XLOG logs in the database and storing it separately in a page mirror storage table; the header information corresponding to the page mirror data (i.e., page data header) remains in the XLOG log header in the XLOG log.

[0075] Using the above method, while reducing the data volume of the XLOG log to a certain extent, the size of the page mirror storage table is reduced, thereby solving the problem of database performance bottleneck caused by the excessive data volume of the XLOG log and ensuring the write process performance of the XLOG log.

[0076] In addition, after the primary and standby database clusters are built, the data volume of the XLOG log transmitted from the primary host to the standby host is reduced, thereby greatly reducing the pressure of XLOG log transmission in the streaming replication between the primary host and the standby host on the premise of ensuring the security and stability of the database data.

[0077] Figure 5 It is a schematic diagram of the conversion from the first assembly linked list to the second assembly linked list in the processing method of the database according to an embodiment of the present invention. Figure 6 According to the schematic diagram of the structure of the global page data linked list in the processing method of the database according to an embodiment of the present invention, it is a simple schematic diagram of the global page data linked list storing two page mirror linked lists. page_data_list_local represents the page mirror linked list of a database 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 XLOG logs to which the two page mirror linked lists belong are different.

[0078] As Figure 6 shown, the global page data linked list 60 is a linked list pre-created in the shared memory for temporarily storing the page mirror data separated from the XLOG log. The global page data linked list 60 includes a plurality of connected page mirror linked lists 70, and each page mirror linked list 70 contains at least one page mirror linked list node 71. Each page mirror linked list node 71 corresponds to a page mirror data, and the page mirror linked list node 71 includes a page mirror data area and a pointing identifier (denoted as next). next is used to point to the next page mirror linked list node 71 to connect the page mirror linked lists 70 corresponding to two adjacent XLOG logs.

[0079] In this embodiment, the step of separating page mirror data in some XLOG logs in the database may include the following steps: create a page mirror linked list node 71 for each page mirror data in the XLOG log to be assembled, and sequentially connect the page mirror linked list nodes 71 of all the page mirror data in the XLOG log to be assembled to form a page mirror linked list 70; connect the page mirror linked list 70 to the globally created page data linked list 60; and sequentially connect the remaining nodes in the first assembly linked list 51 except for the assembly linked list nodes used to store page mirror data to form a second assembly linked list 52 to store the XLOG log after the separation of page mirror data.

[0080] In the processing method of the database in this embodiment, by creating a page mirror linked list node 71 for each page mirror data in the XLOG log to be assembled, sequentially connecting the page mirror linked list nodes 71 of all the page mirror data in the XLOG log to be assembled to form a page mirror linked list 70, connecting the page mirror linked list 70 to the globally created page data linked list 60, and sequentially connecting the remaining nodes in the first assembly linked list 51 except for the assembly linked list nodes used to store page mirror data to form a second assembly linked list 52, the operation of separating page mirror data in the XLOG log to be assembled is completed, and the accuracy of the operation of separating page mirror data in the XLOG log is further improved.

[0081] In a specific embodiment, after the step of sequentially connecting the page mirror linked list nodes 71 of all the page mirror data in the XLOG log to be assembled to form a page mirror linked list 70, the processing method of the database of the present invention may further include the following steps: connect the page mirror linked list 70 after the remaining nodes in the first assembly linked list 51 except for the assembly linked list nodes used to store page mirror data.

[0082] That is to say, after sequentially connecting the page mirror linked list nodes 71 of all the page mirror data in the XLOG log to be assembled to form a page mirror linked list 70, first connect the page mirror linked list 70 to the end of the first assembly linked list 51, then move out the page mirror linked list 70 behind the first assembly linked list 51 and connect it to the globally created page data linked list 60.

[0083] The processing method of the database in this embodiment can orderly adjust the nodes of page mirror data, so that the data can be effectively managed and maintained globally, which is beneficial to data sharing and consistency maintenance, and improves the stability of the database.

[0084] Figure 7 It is a schematic structural diagram of a page mirror linked list node in the processing method of a database according to an embodiment of the present invention. As Figure 7 shown, the page mirror data area stores page mirror data and related data of the page mirror data.

[0085] The related data of the page mirror data includes: the lsn of the XLOG log corresponding to the page mirror data, the page dataheader, the location information of the data page corresponding to the page mirror data, and the total length of the page mirror data area (denoted as len).

[0086] The lsn corresponding to each page mirror data increases sequentially. The lsn can be used to determine the correspondence between the page mirror data and the XLOG log. The data page refers to the data disk page. The location information of the data page includes RelFileNode and BlockNumber. RelFileNode is the location information of the table where the data disk page corresponding to the page mirror data is located, and BlockNumber is the block number of the data disk page corresponding to the page mirror data in its table. RelFileNode and BlockNumber together determine the location of the data disk page on the disk. Based on these two data of RelFileNode and BlockNumber, a unique data disk page can be determined on the disk. When searching on the disk, it can be determined whether a version of the target page mirror data is found according to these two values. The page mirror data header is the header information corresponding to the page mirror data. len is used to calculate the start address of the next page mirror data area in the page mirror file. When traversing the page mirror file, the next page mirror data area can be quickly found according to the len of each page mirror data area. On this basis, one page mirror data corresponds to one XLOG log and one data page.

[0087] In some embodiments, the location information of the page mirror data includes the lsn of the XLOG log corresponding to the page mirror data and the location information of the data page corresponding to the page mirror data. The location information of the data page includes RelFileNode and BlockNumber.

[0088] Such as Figure 2 shown, multiple tuples are recorded in the page mirror storage table. Each tuple includes the related data of the page mirror data and the location information of the page mirror data. The related data of the page mirror data includes the page mirror data and the page data header corresponding to the page mirror data. The database selects the page mirror data in some XLOG logs according to the lsn of the XLOG log for separation operations. The difference between the lsn recorded in two adjacent tuples in the page mirror storage table is greater than the preset interval (denoted as fpw_distance) set by the database to reduce the frequency of storing page mirror data into the table.

[0089] Such as Figure 3As shown, there are multiple tuples recorded in the walminer_need_page table, and each tuple also includes data related to page mirror data and location information of the page mirror data. The data related to page mirror data includes page mirror data and a page data header corresponding to the page mirror data.

[0090] Figure 8 It is a schematic diagram of the structure of a transaction log file in a database processing method according to an embodiment of the present invention. Figure 9 It is a schematic diagram of a log range in a database processing method according to an embodiment of the present invention. As Figure 8 and Figure 9 shown, there is at least one transaction log within the log range in the above step S102.

[0091] In this embodiment, the above step S104 may include the following steps: Determine the log range of the XLOG log required for page mirror data recovery according to the current base backup situation and the XLOG log retention situation.

[0092] In a specific embodiment, as Figure 8 shown, in the scenario where walminer is executed, first determine the range L1 of the XLOG log required by walminer according to the current base backup situation and the XLOG log retention situation. L1 is after the starting position of the base backup (denoted as backup_begin_lsn). Denote the starting point of L1 as L1_begin_lsn and the ending point as L1_end_lsn. All XLOG logs between backup_begin_lsn and L1_end_lsn need to be ensured to exist.

[0093] In some embodiments, the above step S106 may include the following steps: Search for the page mirror data to be recovered in the page mirror storage table according to the index set, and update the index set according to the search result; Determine whether the updated index set is an empty set; If not, start from the XLOG log at the starting position of the preset redo XLOG log, and redo the XLOG log according to the location information of the page mirror data in the updated index set to obtain the page mirror data to be recovered.

[0094] The database processing method of this embodiment realizes the accurate acquisition of all page mirror data to be recovered by sequentially searching for the page mirror data to be recovered from the page mirror storage table and from the XLOG log starting from the preset redo XLOG log starting position according to the index set, and updating the index set according to the search result until the index set is an empty set, improving the accuracy of obtaining the page mirror data to be recovered.

[0095] In a specific embodiment, as Figure 8As shown, in the scenario where walminer is executed, the starting position of the preset redo XLOG log is the starting position of the base backup. On this basis, the step of redoing the XLOG log from the XLOG log at the preset starting position of the redo XLOG log and obtaining the page mirror data to be restored according to the position information of the page mirror data in the updated index set is specifically executed as follows: starting from the XLOG log at backup_begin_lsn, redoing the XLOG log according to the position information of the page mirror data in the updated index set to obtain the page mirror data to be restored. That is to say, starting from the base backup to redo the XLOG log and repair the missing page mirror data ensures the integrity of the page mirror data restoration.

[0096] Figure 10 FIG. is a schematic diagram of the structure of a transaction log file in a database processing method according to another embodiment of the present invention. The inventor recognizes that starting from the base backup to redo the XLOG log can restore the pagedata required by walminer. However, if the XLOG log range where walminer needs to be executed is too far from the base backup (as Figure 10 shown), then the time consumed in the process of redoing the XLOG log starting from the base backup will be quite considerable. In addition, the solution of redoing the XLOG log starting from the base backup is highly dependent on the base backup. If there is no base backup in the transaction log file, the walminer plugin still cannot be used normally.

[0097] On this basis, in some embodiments, the present invention can select other positions as the preset starting position of the redo XLOG log. Specifically, as Figure 10 shown, the preset starting position of the redo XLOG log is before the starting address of the first XLOG log within the log range and is separated from the starting address by a preset length.

[0098] In a specific embodiment, a position closer to L1_begin_lsn (denoted as replay_begin) is selected as the preset starting position of the redo XLOG log, thereby greatly shortening the time required to restore the page data when walminer runs. The preset length can be the sum of the preset interval and the preset increment set for the database. The preset increment is less than the preset interval so that the distance between the starting addresses is greater than the preset interval. For example, when fpw_distance is 1GB, the preset increment is selected as 0.1GB, then replay_begin = L1_begin_lsn - fpw_distance - 0.1GB.

[0099] The inventor realizes that if the XLOG log is redone from replay_begin to L1_end_lsn, for the XLOG log within the L1 range, it will be traversed twice. Once when restoring the page data required by walminer, and once when walminer is running. Since during the execution of walminer, the XLOG log will be traversed one by one starting from L1_begin_lsn. Therefore, the remaining page data restoration process can be incorporated into the walminer execution process.

[0100] On this basis, the step of redoing the XLOG log from the XLOG log at the preset XLOG log redo start position according to the position information of the page mirror data in the updated index set to obtain the page mirror data to be restored can be specifically executed as follows: According to the position information of the page mirror data in the updated index set, redo the XLOG log between the preset XLOG log redo start position and the start address to obtain the page mirror data to be restored. That is to say, start redoing the XLOG log from replay_begin, only redo up to the L1_begin_lsn position, and incorporate the remaining page data restoration process into the walminer execution process.

[0101] Using the above method, one traversal of the XLOG log within the L1 range is reduced, improving the redo efficiency.

[0102] In some embodiments, the step of redoing the XLOG log from the XLOG log at the preset XLOG log redo start position according to the position information of the page mirror data in the updated index set to obtain the page mirror data to be restored may include the following steps: Obtain the position information of the page mirror data recorded in the XLOG log to be redone; According to the position information of the page mirror data recorded in the XLOG log to be redone and the updated index set, search for the page mirror data to be restored in the XLOG log to be redone, and update the index set according to the search result; Determine whether the re-updated index set is an empty set; If it is an empty set, stop the operation of redoing the XLOG log; If it is not an empty set, take the next XLOG log as the XLOG log to be redone, and continue to execute the step of obtaining the position information of the page mirror data recorded in the XLOG log to be redone.

[0103] The processing method of the database in this embodiment, through the index set, orderly searches for the page mirror data to be restored from the page mirror storage table and from the XLOG log starting from the preset XLOG log redo start position, and updates the index set according to the search result until the index set is an empty set, realizing the accurate acquisition of all the page mirror data to be restored, and further improving the accuracy of obtaining the page mirror data to be restored.

[0104] Further, the steps of finding the page mirror data to be restored in the XLOG log to be redone according to the location information of the page mirror data recorded in the XLOG log to be redone and the updated index set, and then updating the index set according to the search result may include the following steps: determining whether the updated index set contains the location information of the data page recorded in the XLOG log to be redone; if not, confirming that the XLOG log to be redone does not contain the page mirror data to be restored, and using the updated index set as the re-updated index set; if so, obtaining the minimum log sequence number corresponding to the location information of the data page recorded in the XLOG log to be redone in the updated index set; comparing the log sequence number of the XLOG log to be redone with the minimum log sequence number; in the case where the log sequence number of the XLOG log to be redone is less than the minimum log sequence number, applying the content of the XLOG log to be redone to the data page corresponding to the XLOG log to be redone, and using the updated index set as the re-updated index set; in the case where the log sequence number of the XLOG log to be redone is greater than the minimum log sequence number, sending an error signal, and using the updated index set as the re-updated index set; in the case where the log sequence number of the XLOG log to be redone is equal to the minimum log sequence number, applying the content of the XLOG log to be redone to the data page corresponding to the XLOG log to be redone, and using the page mirror data and its location information in the XLOG log to be redone as the obtained page mirror data and its location information to be restored, and removing the page mirror data and its location information in the XLOG log to be redone from the index set to obtain the re-updated index set.

[0105] In some embodiments, the index set stores the location information of the page mirror data of all XLOG log records within the log range in the form of a hash table, thereby improving the query speed of page data.

[0106] In a specific embodiment, the process of restoring page mirror data depending on the base backup may include:

[0107] 1. Before walminer is executed, first determine the range L1 of the XLOG logs required by walminer according to the current base backup situation and the XLOG log retention situation;

[0108] 2. Traverse the XLOG logs within the L1 range, find the location information of all page data from these XLOG logs, combine the location information (RelFileNode, BlockNumber) of the page corresponding to the page data on the data disk and the lsn of the XLOG log where the page data is located as the index for determining the page data in the page mirror storage table (denoted as page_index_i, i = 1, 2,..., N), and form these index information into page_index_set;

[0109] 3. Traverse page_index_set, and look up the corresponding tuple in the page mirror storage table according to the RelFileNode, BlockNumber, and lsn recorded in each set member of page_index_set; if the tuple is found, store the content of this tuple into the walminer_need_page table, and remove the index information of this page data from page_index_set, then continue to execute step 4; if not found, then continue to execute step 4;

[0110] 4. Check whether page_index_set is empty?

[0111] (1) Yes, it means that all the page data required by walminer has been obtained, and end;

[0112] (2) No, it means that the page data required by walminer has not been obtained yet, and continue to execute step 5;

[0113] 5. Redo the XLOG logs starting from the base backup to restore the page data required by walminer:

[0114] (1) Can the page (denoted as page_cur) involved in the content of the XLOG log (whose lsn is denoted as lsn_cur) be found in the page_index_set collection through RelFileNode and BlockNumber?

[0115] 1) Yes, if multiple are found, then take the page with the smallest lsn among them, denoted as page_found,

[0116] the corresponding lsn is lsn_found; if only one is found, denote the found page as page_found, and the corresponding lsn as lsn_found;

[0117] a. lsn_cur < lsn_found, apply the XLOG log content to page_cur;

[0118] If b.lsn_cur > lsn_found, report an error;

[0119] If c.lsn_cur is equal to lsn_found, apply the XLOG log content to page_cur, store the relevant information of the updated page_cur into the walminer_need_page table, and finally remove the index information of page_cur from the page_index_set; continue with (2);

[0120] 2) No, continue with (2);

[0121] (2) Check if page_index_set is empty?

[0122] 1) Yes, stop redoing the XLOG log; at this time, all the page data required during the execution of walminer is restored; sort the tuples in the walminer_need_page table in ascending order of lsn so that the tuples in the walminer_need_page table can be sequentially obtained during the operation of walminer;

[0123] 2) No, continue with (3);

[0124] (3) Determine if there is any XLOG log that has not been redone?

[0125] 1) Yes, read the next XLOG log; jump to (1);

[0126] 2) No, stop redoing the XLOG log; at this time, the page data required during the execution of walminer is not restored, and report an error.

[0127] The processing method of the database in this embodiment utilizes the uniqueness of the correspondence between the two data, RelFileNode and BlockNumber, and the data page, improving the convenience and accuracy of finding the page mirror data corresponding to the data page to be written to disk in the global page data linked list 60, and improving the processing speed of the page stage of the persistent data disk.

[0128] Figure 11 It is a flow chart of the processing method of the database according to an embodiment of the present invention. The following combines Figure 11 Specifically describe the flow steps of the processing method of the database in this embodiment.

[0129] Step S1102, obtain the log range L1 of the XLOG log required for page mirror data recovery.

[0130] Step S1104: Create page_index_set according to the location information of the page mirror recorded in the XLOG log within L1. Specifically, the location information of the page mirror includes RelFileNode, BlockNumber, and lsn, and page_index_set is used to find the page mirror data to be restored.

[0131] Step S1106: Find the corresponding tuple in the page mirror storage table according to the index information recorded in page_index_set. It should be noted that the index information includes RelFileNode, BlockNumber, and lsn. The corresponding tuple in the page mirror storage table includes page data, page data header, RelFileNode, BlockNumber, and lsn.

[0132] Step S1108: Determine whether there is a corresponding tuple in the page mirror storage table. If so, execute Step S1110; if not, execute Step S1112.

[0133] Step S1110: Store the content of the found tuple into the walminer_need_page table, and remove the index information corresponding to the found tuple from page_index_set. Then execute Step S1112.

[0134] Step S1112: Determine whether page_index_set is an empty set. If so, execute Step S1128; if not, execute Step S1114.

[0135] Step S1114: Obtain the location information of the page data in the XLOG log. The location information of the page data includes RelFileNode, BlockNumber, and lsn.

[0136] Step S1116: Determine whether the index set contains the index information of the location information of the page data. If so, execute Step S1118; if not, execute Step S1124.

[0137] Step S1118: Obtain the minimum log sequence number (denoted as lsn_found) in the index information found by page_index_set. It should be noted that if the index set contains multiple index information corresponding to the location information of the page data, take the smallest lsn among them and denote it as lsn_found. If the index set contains only one index information corresponding to the location information of the page data, denote the lsn in the found index information as lsn_found.

[0138] Step S1120, determine whether lsn_cur is equal to lsn_found. If so, execute Step S1122; if not, execute Step S1124.

[0139] Step S1122, apply the content of the XLOG log to the corresponding data page, store the page mirror data of the XLOG log and its location information in the walminer_need_page table, and remove the found index information from the page_index_set. Then execute Step S1124. It should be noted that the location information of the page mirror data includes page dataheader, RelFileNode, BlockNumber, and lsn.

[0140] Step S1124, determine whether the page_index_set is an empty set. If so, execute Step S1128; if not, execute Step S1126.

[0141] Step S1126, read the next XLOG log and return to Step S1114.

[0142] Step S1128, confirm that the page mirror data recovery is completed. This process ends.

[0143] The processing method of the database in this embodiment, by obtaining the log range of the XLOG logs required for page mirror data recovery, creating an index set for finding the page mirror data to be recovered according to the location information of the page mirror data recorded in all XLOG logs within the log range, realizes the summary of index information. According to the index set, obtain the page mirror data to be recovered from the page mirror storage table and / or the XLOG logs containing the page mirror data, and store the obtained page mirror data to be recovered and its location information in the page mirror recovery table to complete the page mirror data recovery, realizing the completion of page mirror data recovery when only the page mirror data separated from some XLOG logs is stored in the page mirror storage table, so that plugins such as the walminer plugin that depend on page mirror data can be used normally.

[0144] Furthermore, the processing method of the database in this embodiment, by sequentially searching for the page mirror data to be recovered from the page mirror storage table and the XLOG logs starting from the preset redo XLOG log start position according to the index set, and updating the index set according to the search results until the index set is an empty set, realizes the accurate acquisition of all page mirror data to be recovered, improving the accuracy of obtaining the page mirror data to be recovered.

[0145] This embodiment also provides a machine-readable storage medium and a computer device. Figure 12Schematic diagram of a machine-readable storage medium 80 according to an embodiment of the present invention. Figure 13 Schematic diagram of a computer device 90 according to an embodiment of the present invention.

[0146] The machine-readable storage medium 80 stores a machine-executable program 81 thereon. When the machine-executable program 81 is executed by a processor, the processing method of the database in any of the above embodiments is implemented.

[0147] The computer device 90 may include a memory 920, a processor 910, and a machine-executable program 81 stored on the memory 920 and running on the processor 910. When the processor 910 executes the machine-executable program 81, the processing method of the database in any of the above embodiments is implemented.

[0148] 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 predefined list of executable instructions for implementing logical functions, and can be specifically implemented in any machine-readable storage 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 and execute instructions from the instruction execution system, apparatus, or device), or in combination with these instruction execution systems, apparatus, or devices.

[0149] It should be understood that the 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. For the description of this embodiment, the machine-readable storage medium 80 can be any device that can contain, store, communicate, propagate, or transmit a program for use by an instruction execution system, apparatus, or device, or in combination with these instruction execution systems, apparatus, or devices. The computer device 90 can be, for example, a server, a desktop computer, a laptop computer, a tablet computer, or a smart phone. The computer device 90 can include a processor 910 suitable for executing stored instructions and a memory 920 that provides temporary storage space for the operation of the instructions during operation. The processor 910 can be a single-core processor, a multi-core processor, a computing cluster, or any other number of other configurations. The memory 920 can include random access memory (RAM), read-only memory, flash memory, or any other suitable storage system.

[0150] It should be noted that in some alternative embodiments, the solution of the present invention is applicable to relational databases, particularly applicable to the KingbaseES database (abbreviated as KES database), enriching the functions of the database and improving the efficiency of the database. In some other alternative embodiments, the processing method of the database of the present invention can also be applicable to other relational databases.

[0151] In addition, the flowcharts provided in this embodiment are not intended to indicate that the operations of the method will be performed in any specific order, or that all operations of the method are included in every case. Furthermore, the method may include additional operations. Within the scope of the technical concept provided by the method of this embodiment, additional changes may be made to the above method.

[0152] 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 consistent with the principles of the present invention can still be directly determined or derived from the content disclosed in 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 recognized as covering all such other variations or modifications.

Claims

1. A method for processing a database, where a page mirror storage table and a page mirror recovery table are pre-created in the database. The page mirror storage table stores page mirror data separated from part of the transaction logs generated by the database and its location information. The page mirror recovery table is used to store page mirror data to be recovered and its location information; And the processing method includes: Obtaining a log range of a transaction log required for page mirror data recovery, where there is at least one of the transaction logs within the log range; Creating an index set for finding the page mirror data to be recovered according to the location information of the page mirror data recorded in all the transaction logs within the log range; Obtaining the page mirror data to be recovered from the page mirror storage table and / or the transaction log containing the page mirror data according to the index set; Storing the obtained page mirror data to be recovered and its location information into the page mirror recovery table to complete the recovery of the page mirror data.

2. The processing method of the database according to claim 1, wherein Obtaining the page mirror data to be recovered from the page mirror storage table and / or the transaction log containing the page mirror data according to the index set includes: Searching for the page mirror data to be recovered in the page mirror storage table according to the index set, and updating the index set according to the search result; Determining whether the updated index set is an empty set; If not, starting from the transaction log at the starting position of the preset redo transaction log, redoing the transaction log according to the location information of the page mirror data in the updated index set to obtain the page mirror data to be recovered.

3. The processing method of the database according to claim 2, wherein The starting point of the log range is after the starting position of the base backup; and The preset starting position of the redo transaction log is the starting position of the base backup.

4. The processing method of the database according to claim 2, wherein The preset starting position of the redo transaction log is before the starting address of the first transaction log within the log range and is separated from the starting address by a preset length.

5. The processing method of the database according to claim 4, wherein Starting from the transaction log at the preset starting position of the redo transaction log, redoing the transaction log according to the location information of the page mirror data in the updated index set to obtain the page mirror data to be recovered includes: Redoing the transaction log between the preset starting position of the redo transaction log and the starting address according to the location information of the page mirror data in the updated index set to obtain the page mirror data to be recovered.

6. The processing method of the database according to claim 2, wherein Starting from the transaction log at the preset starting position of the redo transaction log, redoing the transaction log according to the location information of the page mirror data in the updated index set to obtain the page mirror data to be recovered includes: Obtaining the location information of the page mirror data recorded in the transaction log to be redone; Searching for the page mirror data to be recovered in the transaction log to be redone according to the location information of the page mirror data recorded in the transaction log to be redone and the updated index set, and further updating the index set according to the search result; Determining whether the further updated index set is an empty set; If it is an empty set, stopping the operation of redoing the transaction log. If it is not an empty set, then use the next transaction log as the transaction log to be redone, and continue to execute the step of obtaining the location information of the page mirror data of the transaction log record to be redone.

7. The method for processing a database according to claim 6, wherein the location information of the page mirror data includes the log sequence number of the transaction log corresponding to the page mirror data and the location information of the data page corresponding to the page mirror data; and finding the page mirror data to be restored in the transaction log to be redone according to the location information of the page mirror data of the transaction log record to be redone and the updated index set, and updating the index set according to the search result includes: judging whether the updated index set contains the location information of the data page of the transaction log record to be redone; if not, confirm that the transaction log to be redone does not contain the page mirror data to be restored, and use the updated index set as the re-updated index set; if so, obtain the minimum value of the log sequence number corresponding to the location information of the data page of the transaction log record in the updated index set; compare the log sequence number of the transaction log to be redone with the minimum value of the log sequence number; when the log sequence number of the transaction log to be redone is less than the minimum value of the log sequence number, apply the content of the transaction log to be redone to the data page corresponding to the transaction log to be redone, and use the updated index set as the re-updated index set; when the log sequence number of the transaction log to be redone is greater than the minimum value of the log sequence number, send an error signal, and use the updated index set as the re-updated index set; when the log sequence number of the transaction log to be redone is equal to the minimum value of the log sequence number, apply the content of the transaction log to be redone to the data page corresponding to the transaction log to be redone, and use the page mirror data and its location information in the transaction log to be redone as the obtained page mirror data and its location information to be restored, and remove the page mirror data and its location information in the transaction log to be redone from the index set to obtain a re-updated index set.

8. The method for processing a database according to claim 1, wherein the index set stores the location information of the page mirror data of all the transaction log records within the log range in the form of a hash table.

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 processing a database according to any one of claims 1 to 8.

10. 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 method for processing a database according to any one of claims 1 to 8.

Images