An adaptive wal log bloating resolution method

Abstract

The application discloses a kind of self-adapting wal log inflation solutions, it is related to cloud computing and big data technical field.The self-adapting wal log inflation solutions, it includes the following steps: S1, wal file size and wal file remaining space in disk are detected regularly, whether disk remaining space is lower than threshold value, if yes, then enter adaptive adjustment;S2, enter adaptive adjustment, according to adaptive adjustment algorithm, submit delay transaction adjustment wal log generation speed;S3, S1 and S2 are circulated.The application is by regularly detecting wal file size and wal file remaining space in disk, when disk remaining space is lower than threshold value, take adaptive algorithm control wal file generation speed technology, reaches the obvious control effect to the inflation speed of wal file, solves the problem of database disk burst disk unusable caused by wal log inflation in prior art.

Description

Technical Field

[0001] The present invention relates to the field of cloud computing and big data technology, and in particular to an adaptive WAL log expansion solution. Background Art

[0002] The WAL log, also known as the write-ahead log, is a technology used in database systems (particularly relational databases) to achieve transaction durability. For example, in MySQL, the WAL log is called the redo log; before PostgreSQL version 10, it was called the xlog log, and since then, it has been called the WAL log. In actual database applications, some databases require the preservation of WAL logs to implement specific functions. For example, point-in-time recovery requires archiving or backing up WAL logs for preservation. Database master-slave clusters also support asynchronous replication. In some cases, WAL log loss can compromise data consistency. Therefore, if the WAL log is cleared, point-in-time recovery will not be possible due to log loss. Furthermore, the slave database will lose synchronization with the master database due to WAL log loss.

[0003] In actual applications, database applications usually save WAL logs to machines that are asynchronous with the database instance. Asynchronous machines may not be able to keep pace with the database due to differences in hardware performance such as CPU, memory, network, and disk IO. Therefore, unsynchronized WAL log files are reserved in the database instance.

[0004] However, in the process of implementing the technical solutions of the embodiments of the present application, the inventors of the present application discovered that the above technology has at least the following technical problems:

[0005] To ensure application availability, the database master instance must retain WAL logs for the applications. These asynchronous applications can affect database performance in certain scenarios. For example, under high transaction pressure, there may be differences in network or disk I / O between the master and standby databases (e.g., SSD drives for the master and SATA drives for the standby). The master WAL log generation rate may exceed the standby WAL log synchronization rate, causing the WAL log files to expand rapidly. Over time, this can lead to disk overflow and affect database performance. In summary, existing technologies suffer from the problem of WAL log expansion causing database disk overflow and unavailability. Summary of the Invention

[0006] The embodiments of the present application provide an adaptive WAL log expansion solution to solve the technical problem of database disk explosion and unavailability caused by WAL log expansion in the prior art, thereby maintaining high availability of database applications and ensuring stable operation of the database.

[0007] This embodiment of the present application provides an adaptive WAL log expansion solution, including the following steps:

[0008] S1: Regularly detect the size of the WAL file and the remaining space on the disk where the WAL file is located to determine whether the remaining disk space is lower than the threshold. If so, enter adaptive adjustment;

[0009] S2, enters adaptive adjustment, and adjusts the WAL log generation speed by submitting delayed transactions according to the adaptive adjustment algorithm;

[0010] S3, loop S1 and S2.

[0011] Furthermore, in S1, it is determined whether the remaining disk space is lower than a threshold value. If not, the size of the wal file and the remaining disk space where the wal file is located are continuously detected.

[0012] Furthermore, before detecting the size of the wal file and the remaining space on the disk where the wal file is located in S1, it is necessary to obtain indicators and initialize the delayed submission parameter and the database write prohibition parameter.

[0013] Furthermore, the indicators include: the number of WAL files, the maximum WAL threshold, the remaining disk space, and the remaining disk space threshold.

[0014] Furthermore, the adaptive adjustment algorithm in S2 specifically includes:

[0015] Slow start: When the amount of WAL files increases or the remaining disk space decreases, the delay in committing increases;

[0016] Fast recovery: When the WAL file size decreases or the remaining disk space increases, the delayed commit time is reduced;

[0017] Avoid disk overflow: When the remaining disk space is less than 1G, database writing is prohibited. Database writing is only enabled when the remaining space is greater than 2G.

[0018] Furthermore, increasing the delayed submission time refers to increasing the delayed time by a fixed value, and the data type of the fixed value is a numeric type.

[0019] Furthermore, reducing the delayed submission time refers to reducing the delayed time to half of the original time.

[0020] Furthermore, after adjusting the WAL log generation speed in S2, it is necessary to save the current WAL file size and the remaining disk space where the WAL file is located as old data for comparison with the next parameter change.

[0021] Furthermore, the adaptive WAL log expansion solution can be deployed and executed as an independent process in any relational database.

[0022] Furthermore, the adaptive WAL log expansion solution can be embedded into the internal database process as a database plug-in for execution.

[0023] One or more technical solutions provided in the embodiments of this application have at least the following technical effects or advantages:

[0024] 1. By using a technology that regularly detects the size of the WAL file and the remaining disk space where the WAL file resides, and uses an adaptive algorithm to control the WAL file generation speed when the remaining disk space is determined to be below a threshold, the WAL file expansion rate is significantly controlled. This effectively solves the problem of database disk overflow and unavailability caused by the expansion of the WAL log, thereby maintaining high availability of database applications and ensuring stable database operation.

[0025] 2. Because indicators need to be obtained before detecting the WAL file size and the remaining space on the disk where the WAL file is located, and the delayed submission parameters and the database write prohibition parameters need to be initialized, these parameters can be modified dynamically. This effectively solves the problem of WAL log expansion in multiple scenarios that is difficult to solve in existing technologies. It also enables real-time response to scenario changes by simply modifying parameters, making the adaptive WAL log expansion solution applicable to different business scenarios.

[0026] 3. After adjusting the WAL log generation speed, the WAL file size and the remaining disk space where the WAL file is located need to be saved as old data for comparison when the next parameter change occurs. Therefore, the difference in synchronization speed of asynchronous database applications is recorded. This effectively solves the problem of asynchronous application bottlenecks that are difficult for operation and maintenance personnel to optimize in existing technologies. This makes it easier for operation and maintenance personnel to optimize bottlenecks and improve overall database performance. BRIEF DESCRIPTION OF THE DRAWINGS

[0027] Figure 1 Flowchart of the adaptive WAL log expansion solution provided in Example 1 of this application;

[0028] Figure 2 This is a diagram of a one-master-one-backup cluster deployment architecture provided in Example 1 of the present application;

[0029] Figure 3 A diagram of a method for obtaining postgresql indicators provided in Example 1 of this application;

[0030] Figure 4 This is a code diagram of the adaptive adjustment algorithm provided in Example 1 of this application. DETAILED DESCRIPTION

[0031] The embodiments of the present application provide an adaptive WAL log expansion solution to solve the problem of database disk explosion and unavailability caused by WAL log expansion in the prior art, and use an adaptive algorithm in the database to control the WAL log generation speed.

[0032] The technical solution in the embodiment of the present application is to solve the problem of database disk explosion and unavailability caused by the above-mentioned WAL log expansion. The overall idea is as follows:

[0033] By periodically detecting the size of the WAL file and the remaining space on the disk where the WAL file is located, relevant parameters are obtained, and then determining whether the remaining disk space is lower than the threshold. If so, adaptive adjustment is initiated. After entering adjustment, delayed transactions are submitted according to the adaptive adjustment algorithm to adjust the generation speed of the WAL log, and the above steps are repeated.

[0034] After adjusting the generation speed of the wal log as described above, the size of the wal file and the remaining space on the disk where the wal file is located need to be saved as old data for comparison with the next parameter change.

[0035] In order to better understand the above technical solution, the above technical solution will be described in detail below with reference to the accompanying drawings and specific implementation methods.

[0036] Example 1

[0037] like Figure 1 As shown in FIG, a flow chart of an adaptive WAL log expansion solution provided by an embodiment of the present application is provided. The method includes the following steps:

[0038] S1: Regularly detect the size of the WAL file and the remaining space on the disk where the WAL file is located to determine whether the remaining disk space is lower than the threshold. If so, enter adaptive adjustment;

[0039] S2, enters adaptive adjustment, and adjusts the WAL log generation speed by submitting delayed transactions according to the adaptive adjustment algorithm;

[0040] S3, loop S1 and S2.

[0041] Further, such as Figure 2 As shown in FIG, a one-master-one-standby cluster deployment architecture diagram provided by an embodiment of the present application, the method provided by an embodiment of the present application can select a one-master-one-standby cluster for deployment in PostgreSQL, and the adaptive WAL log expansion solution is implemented as an independent process to automatically adjust the WAL log generation speed according to the adaptive algorithm. The specific steps are as follows:

[0042] In the first step, each parameter is initialized, and the corresponding indicators available_space and wal_size are obtained, and it is determined whether the remaining space of the disk is lower than the threshold value; if the available_space is less than the free_threshold, the second step is entered, otherwise the first step is returned;

[0043] In the second step, adaptive processing is performed according to the parameters:

[0044] When the available_space is less than 1GB, the database is set to read-only mode;

[0045] When the available_space decreases or the wal_size increases, the delay commit time is increased;

[0046] When the available_space increases, the delay commit time is reduced;

[0047] In the third step, the available_space and wal_size of this time are saved as old data for comparison of parameter changes next time;

[0048] In the fourth step, the first step to the third step is repeated.

[0049] In this embodiment, as shown in Figure 2 The wal site relationship is: initial site < cleanup site < synchronization site < generation site;

[0050] The synchronization speed is the speed of the standby library synchronizing the wal log to the master library, and there can be multiple standby libraries. The master library cleanup speed is less than the minimum standby library synchronization speed, and the synchronization speed is affected by network and standby library disk IO, standby library business application processing wal log speed and other conditions;

[0051] The generation speed is the speed of the master library wal log file generation, which is affected by business pressure and business type. The wal generation of read-only business is much smaller than that of write business.

[0052] Further, in S1, it is determined whether the disk remaining space is lower than the threshold value, and if not, the wal file size and the disk remaining space where the wal file is located are continued to be detected.

[0053] In this embodiment, the key indicators are the wal file size and the remaining disk space, which are related to whether the master library is caused by the expansion of the wal log to cause the disk to explode.

[0054] Further, before detecting the wal file size and the disk remaining space where the wal file is located in S1, the indicators need to be obtained, and the delay commit parameter and the database write prohibition parameter are initialized.

[0055] In this embodiment, the delayed commit parameter is initialized to 0, and the database write-forbidden parameter is initialized to off.

[0056] Furthermore, the indicators include: number of WAL files, maximum WAL threshold, free disk space, and free disk space threshold.

[0057] In this embodiment, the postgresql indicator acquisition method is shown in Figure 3 Each acquisition target has its own acquisition method. The method for obtaining the number of WAL files, namely wal_size, is to use the SQL statement select count(*)from pg_ls_waldir(); the method for obtaining the maximum WAL threshold, namely max_wal_size, is to use the show max_wal_size statement to display the database parameters; the available disk space, namely available_space, is obtained by using the sh script get_available_space statement; the free disk space threshold, namely free_threshold, is user-defined and has a default value of 10GB; the delayed commit parameter is obtained by using the show commit_delay statement; the database write-forbidden parameter is obtained by using the default_transaction_read_only statement. The above acquisition targets are as follows: Figure 4 The adaptive algorithm shown is reflected in the code.

[0058] Furthermore, the adaptive adjustment algorithm in S2 specifically includes:

[0059] Slow start: When the amount of WAL files increases or the remaining disk space decreases, the delay in committing increases;

[0060] Fast recovery: When the WAL file size decreases or the remaining disk space increases, the delayed commit time is reduced;

[0061] Avoid disk overflow: When the remaining disk space is less than 1G, database writing is prohibited. Database writing is only enabled when the remaining space is greater than 2G.

[0062] In this embodiment, increasing the transaction delay commit parameter in the database can reduce the WAL log generation speed, but also reduce the database TPS performance. In the scenario where this method is used, default high availability is more important than high performance. By increasing the commit_delay parameter to adjust the WAL generation speed, WAL log explosion is avoided. In addition, the decrease in database TPS does not affect read-only services, and the database can still be used for query services.

[0063] Furthermore, increasing the delayed submission time refers to increasing the delayed time by a fixed value, and the data type of the fixed value is a numeric type.

[0064] In the embodiment, the fixed value can be changed later. In the embodiment, the fixed value is set to 100.

[0065] Further, the delay submission time is reduced to half of the original delay time.

[0066] In the embodiment, although the reduction value is set to half of the original delay time, the value is not limited to this value, and the value can be changed to a more appropriate value later.

[0067] Further, after adjusting the wal log generation speed in S2, the wal file size of this time and the remaining space of the disk where the wal file is located are saved as old data for comparison of the next parameter change.

[0068] In the embodiment, the difference between the asynchronous application and the synchronous speed is recorded, which facilitates the analysis of the asynchronous application bottleneck by the later operation and maintenance personnel, and the optimization of the bottleneck to improve the overall database performance.

[0069] Further, the adaptive wal log inflation solution can be used as an independent process and deployed and executed in any relational database.

[0070] In the embodiment, the method is deployed as an independent process in a primary-standby cluster database for execution.

[0071] Further, the adaptive wal log inflation solution can be used as a database plug-in and embedded in the internal process of the database for execution.

[0072] In the embodiment, the premise of preventing the database from exploding is that the disk is only used for one database instance, and the adaptive program can only prevent the database from exploding, but cannot prevent other disks from exploding. It is not recommended to use the same disk for multiple applications.

[0073] The technical solutions in the embodiments of the present application have at least the following technical effects or advantages: by detecting the wal file size and the remaining space of the disk where the wal file is located at regular intervals, when the remaining space of the disk is lower than the threshold value, the wal log generation speed is automatically adjusted according to the adaptive algorithm, the wal log inflation speed is controlled, the database disk explosion caused by the wal log inflation is prevented, the stability of the database is ensured, and the high availability of the database application is maintained.

[0074] Those skilled in the art will appreciate that embodiments of the present application can be devised for a variety of applications. It is therefore intended that the present application cover all such modifications and variations of the application disclosed herein provided they come within the scope of the appended claims and their equivalents. It is intended to embrace all alternatives, modifications and variations of this application within the scope of the appended claims.

[0075] The present application is described in reference to the drawings using a flowchart and / or a block diagram of the method, apparatus (system) and computer program product according to embodiments of the application. It will be understood that each block of the flowchart and / or block diagram, and combinations of blocks in the flowchart and / or block diagram, can be implemented by computer program instructions. These computer program instructions can be provided to a processor of a general purpose computer, special purpose computer, embedded processing device or other programmable data processing apparatus to produce a machine, such that the instructions, which execute via the processor of the computer or other programmable data processing apparatus, create means for implementing the functions specified in the flowchart and / or block diagram block or blocks. Figure 1 Figure 1

[0076] These computer program instructions can also be stored in a computer- readable memory that can direct a computer or other programmable data processing apparatus to function in a particular manner, such that the instructions stored in the computer-readable memory produce an article of manufacture including instructions which implement the function specified in the flowchart and / or block diagram block or blocks. Figure 1 Figure 1

[0077] These computer program instructions can also be loaded onto a computer or other programmable data processing apparatus to cause a series of operational steps to be performed on the computer or other programmable apparatus to produce a computer implemented process such that the instructions which execute on the computer or other programmable apparatus provide steps for implementing the functions specified in the flowchart and / or block diagram block or blocks. Figure 1 Figure 1

[0078] While the preferred embodiments of the application have been described, additional variations and modifications can be employed by those skilled in the art. Therefore, the appended claims are intended to cover all such modifications and variations as fall within the scope of the present application.

[0079] ​​​​​​Obviously, many modifications and variations of the present application are possible in light of the above teachings. It is, therefore, to be understood that within the scope of the appended claims and their equivalents, the application can be practiced otherwise than as specifically described.

Claims

1. An adaptive solution to WAL log expansion, characterized by: The following steps are involved: S1: Regularly detect the size of the WAL file and the remaining space on the disk where the WAL file is located to determine whether the remaining disk space is lower than the threshold. If so, enter adaptive adjustment; S2, enters adaptive adjustment, and adjusts the WAL log generation speed by submitting delayed transactions according to the adaptive adjustment algorithm; S3, loops S1 and S2; The adaptive adjustment algorithm in S2 specifically includes: Slow start: When the amount of WAL files increases or the remaining disk space decreases, the delay in committing increases; Fast recovery: When the WAL file size decreases or the remaining disk space increases, the delayed commit time is reduced; Avoid disk overflow: When the remaining disk space is less than 1G, database write is prohibited. Database write is enabled only when the remaining space is greater than 2G. After adjusting the WAL log generation speed in S2, the current WAL file size and the remaining disk space where the WAL file is located need to be saved as old data for comparison with the next parameter change.

2. The adaptive WAL log expansion solution according to claim 1 is characterized by: In the S1, it is determined whether the remaining disk space is lower than a threshold value. If not, the size of the wal file and the remaining disk space where the wal file is located are continuously detected.

3. The adaptive WAL log expansion solution according to claim 1 is characterized by: Before detecting the size of the wal file and the remaining space on the disk where the wal file is located in S1, it is necessary to obtain indicators and initialize the delayed submission parameter and the database write prohibition parameter.

4. The adaptive WAL log expansion solution as described in claim 3 is characterized by: The indicators include: number of WAL files, maximum WAL threshold, remaining disk space, and remaining disk space threshold.

5. The adaptive WAL log expansion solution according to claim 1 is characterized by: Increasing the delayed submission time refers to increasing the delayed time by a fixed value, and the data type of the fixed value is a numeric type.

6. The adaptive WAL log expansion solution according to claim 1 is characterized by: Reducing the delayed submission time refers to reducing the delayed time to half of the original time.

7. The adaptive WAL log expansion solution according to claim 1 is characterized by: The adaptive WAL log expansion solution can be deployed and executed as an independent process in any relational database.

8. The adaptive WAL log expansion solution according to claim 1 is characterized by: The adaptive WAL log expansion solution can be embedded into the internal process of the database as a database plug-in for execution.

Images