A method, device and medium for WAL log compression under PostgreSQL stream replication

Abstract

This application discloses a WAL log compression method, device, and medium under PostgreSQL streaming replication, belonging to the database field. The method includes: reading configuration parameters and establishing a streaming replication connection when the master database starts up, obtaining network I / O load values ​​in real time, and calculating the predicted WAL log generation rate based on the transaction processing rate to estimate future bandwidth requirements. When the network I / O or predicted bandwidth requirements reach a preset threshold, samples of the original WAL log data are extracted and compared using multiple algorithms for rapid compression. The optimal compression algorithm is dynamically selected for adaptive compression processing, and the corresponding compression flag field is set; otherwise, the data is transmitted directly in an uncompressed manner. The standby database completes the corresponding decompression or direct writing by parsing the compression flag field. This invention achieves a dynamic balance between network bandwidth and CPU resources through a dual mechanism of load prediction and content awareness, effectively reducing master-standby replication latency and improving the adaptability and overall operating efficiency of the streaming replication system in complex production environments.

Description

Technical Field

[0001] This application relates to the field of database technology, and in particular to a WAL log compression method, device and medium under PostgreSQL streaming replication. Background Technology

[0002] PostgreSQL, as a mainstream open-source high-performance relational database, is widely used in high-availability scenarios such as finance and government. Streaming replication is the core technology for achieving master-slave data synchronization and ensuring system failover. This technology generates a Write-Ahead Log (WAL) on the master database and streams data modification records to the slave database for replay, ensuring data consistency between master and slave.

[0003] However, under high-concurrency business, the amount of WAL log generation increases significantly. In environments with limited network bandwidth, WAL log transmission can easily consume a large amount of network resources. PostgreSQL's native streaming replication protocol only supports raw log transmission and lacks built-in compression capabilities. Therefore, it can easily lead to problems such as transmission delays and master-slave replication lags, reducing system stability.

[0004] Furthermore, existing WAL compression relies on third-party plugins, all of which employ static configuration strategies. The compression mode must be preset before the database starts, and cannot be adjusted in real time according to network I / O load after startup, making it difficult to adapt to dynamic network environments. Additionally, these plugins only support a single, fixed compression algorithm, unable to flexibly select algorithms based on network pressure and data characteristics, and lack a compression validity verification mechanism. When data compression is ineffective or even excessive, the compression process is still executed, resulting in wasted CPU resources.

[0005] In other words, static transmission strategies cannot achieve a dynamic balance between network bandwidth and CPU resources. They can only sacrifice resources in one direction to achieve a single performance optimization, which cannot adapt to the fluctuating load requirements of the production environment and severely restricts the transmission efficiency and system stability of PostgreSQL streaming replication. Summary of the Invention

[0006] To address the technical problems described in the background, this application provides a WAL log compression method, device, and medium under PostgreSQL streaming replication. The method includes: when the master database node starts, reading preset configuration parameters and establishing a streaming replication connection between the master and backup database nodes; the configuration parameters include a compression algorithm identifier and a network I / O threshold; reading raw WAL log data to be sent according to a preset fixed length to put the master database node into a cyclic sending state, while simultaneously acquiring the real-time network I / O load value; calculating the predicted generation rate of the WAL log through the master database node based on the PostgreSQL transaction processing rate, and determining the predicted network bandwidth demand within a preset time window based on the predicted generation rate; when the network I / O load is... When the real-time network load value is less than the preset network IO threshold and the predicted network bandwidth demand value is less than the current available network bandwidth, the compression algorithm identifier of the primary database node is set to an uncompressed identifier, and the original WAL log data is directly encapsulated into a streaming replication data packet and sent to the backup database node; when the real-time network IO load value is greater than or equal to the network IO threshold, and / or the predicted network bandwidth demand value is greater than or equal to the current available network bandwidth, adaptive compression processing is performed on the original WAL log data to generate compressed data and determine the corresponding compression algorithm identifier, and the compressed data is encapsulated into a streaming replication data packet and sent to the backup database node; the adaptive compression processing includes extracting samples from the original WAL log data for multi-algorithm compression comparison to select a target compression algorithm for compression.

[0007] In one example, adaptive compression processing is performed on the raw WAL log data to generate compressed data and determine the corresponding compression algorithm identifier. Specifically, this includes: extracting header data of a preset length from the raw WAL log data as a compression sample; calling at least two compression algorithms corresponding to the compression algorithm identifier in the configuration parameters to perform compression operations on the compression sample, obtaining the compression ratio and compression time of the sample compression corresponding to each algorithm; selecting a target compression algorithm according to the compression ratio and compression time and a preset cost-effectiveness rule; setting the compression algorithm identifier to the compression algorithm identifier corresponding to the target compression algorithm; and calling the target compression algorithm to compress the raw WAL log data to generate compressed data.

[0008] In one example, after the target compression algorithm is invoked to compress the original WAL log data and generate compressed data, the method further includes: comparing the data volume of the compressed data with that of the original WAL log data; if the data volume of the compressed data is smaller than that of the original WAL log data, encapsulating the compressed data into the subprotocol payload of the streaming replication data packet; if the compressed data volume is greater than or equal to that of the original WAL log data, reverting the compression algorithm flag to the uncompressed flag, and directly encapsulating the original WAL log data into the subprotocol payload.

[0009] In one example, after the compressed data is encapsulated into a streaming replication data packet and sent to the backup database node, the method further includes: receiving the streaming replication data packet through the backup database node and parsing the compression algorithm identifier; if the compression algorithm identifier is an uncompressed identifier, directly writing the original data in the sub-protocol payload into the local WAL file through the backup database node; if the compression algorithm identifier is the identifier of the corresponding target compression algorithm, calling the corresponding decompression algorithm through the backup database node to decompress the data in the sub-protocol payload and writing the decompressed data into the local WAL file.

[0010] In one example, the predicted WAL log generation rate is calculated based on the PostgreSQL transaction processing rate through the master database node. Specifically, this includes: collecting the number of committed transactions and the amount of WAL log data generated within the corresponding time periods of multiple consecutive preset time windows of the master database node; establishing a linear mapping relationship between the transaction processing volume and the WAL log generation volume based on the number of committed transactions and the amount of WAL log data; obtaining the transaction commit rate of the master database node; and calculating the predicted WAL log generation rate within the next preset time window based on the linear mapping relationship.

[0011] In one example, raw WAL log data to be sent is read according to a preset fixed length to put the master database node into a loop sending state, while simultaneously obtaining the real-time network I / O load value. Specifically, this includes: the master database node reading raw WAL log data blocks of a preset fixed length from the local WAL log file; the master database node performing a data sending process after each read operation, and returning to read the next data block after the sending is completed, thus forming a loop sending state; each time the master database node enters the loop sending state for a read operation, it obtains the network bandwidth utilization rate of the current streaming replication connection by calling the operating system network interface, and uses the network bandwidth utilization rate as the real-time network I / O load value.

[0012] In one example, when the primary database node starts up, it reads preset configuration parameters and establishes a streaming replication connection between the primary and backup database nodes. Specifically, during the startup process of the primary database node, it reads a pre-set compression algorithm identifier list and network I / O threshold from the PostgreSQL GUC parameter configuration file and loads the compression algorithm identifier list and network I / O threshold into the primary database node's memory runtime environment; the backup database node initiates a streaming replication connection request to the primary database node; after receiving the connection request, the primary database node performs authentication and replication site negotiation with the backup database node; after the authentication and replication site negotiation are successful, a streaming replication connection channel for WAL log transmission is established between the primary and backup database nodes.

[0013] In one example, the method further includes: the compression algorithm identifier is located after the streaming replication protocol header and before the subprotocol payload, continuous with the original header fields, and is in binary encoding, with different encodings corresponding to different compression states and algorithms.

[0014] On the other hand, embodiments of this application provide a WAL log compression device under PostgreSQL streaming replication, including: at least one processor; and a memory communicatively connected to the at least one processor; wherein the memory stores instructions executable by the at least one processor, the instructions being executed by the at least one processor to enable the at least one processor to perform any of the above-mentioned WAL log compression methods under PostgreSQL streaming replication.

[0015] On the other hand, embodiments of this application provide a non-volatile computer storage medium for WAL log compression under PostgreSQL streaming replication, which stores computer-executable instructions capable of executing any of the above-mentioned WAL log compression methods under PostgreSQL streaming replication.

[0016] The above-described technical solutions adopted in the embodiments of this application can achieve the following beneficial effects: This application upgrades the compression strategy from passive response to proactive prediction by combining real-time monitoring of network I / O load with prediction of WAL log generation rate. When network congestion is not yet apparent during peak business periods but bandwidth demand is predicted to exceed limits, the primary database node can proactively enable compressed transmission, effectively avoiding the accumulation of instantaneous replication delays caused by sudden traffic surges and ensuring the real-time performance and stability of primary-backup data synchronization. Secondly, when entering compression mode, this solution does not mechanically execute a single fixed compression algorithm. Instead, it extracts samples of the raw WAL log data to be sent and performs rapid compression comparisons using multiple algorithms, dynamically selecting the optimal compression algorithm for this transmission based on the cost-effectiveness of compression ratio and time consumption. This mechanism matches compression behavior with specific data characteristics, minimizing unnecessary CPU resource overhead while ensuring bandwidth savings. Finally, by adding a compression flag field to the streaming replication protocol packet, the backup database node can automatically identify the compression status and algorithm type of data packets without relying on any static configuration, achieving transparent collaboration of compression strategies between primary and backup nodes. Attached Figure Description

[0017] To more clearly illustrate the technical solution of this application, some embodiments of this application will be described in detail below with reference to the accompanying drawings, in which: Figure 1 A flowchart illustrating a WAL log compression method under PostgreSQL streaming replication provided in this application embodiment; Figure 2 A design diagram of a streaming replication protocol for a WAL log compression method under PostgreSQL streaming replication provided in this application embodiment; Figure 3 This is a schematic diagram of the structure of a WAL log compression device under PostgreSQL streaming replication provided in an embodiment of this application. Detailed Implementation

[0018] To make the objectives, technical solutions, and advantages of this application clearer, the technical solutions of this application will be clearly and completely described below in conjunction with specific embodiments and corresponding drawings. Obviously, the described embodiments are only a part of the embodiments of this application, and not all of them. Based on the embodiments in this application, all other embodiments obtained by those skilled in the art without creative effort are within the scope of protection of this application.

[0019] Some embodiments of this application will now be described in detail with reference to the accompanying drawings.

[0020] Figure 1This is a flowchart illustrating a WAL log compression method under PostgreSQL streaming replication, provided as an embodiment of this application. This method can be applied to different business domains. Certain input parameters or intermediate results in this process can be manually adjusted to help improve accuracy.

[0021] The analysis method involved in the embodiments of this application can be implemented by a terminal device or a server, and this application does not impose any special limitations on it. For ease of understanding and description, the following embodiments are all described in detail using a server as an example.

[0022] Based on this Figure 1 The process may include the following steps: S101: When the primary database node starts, read the preset configuration parameters and establish a streaming replication connection between the primary database node and the backup database node; the configuration parameters include compression algorithm identifier and network IO threshold.

[0023] In some embodiments of this application, this step involves the master database node completing two key initialization tasks during startup: configuration loading and connection establishment, providing a foundation for subsequent dynamic adaptive compressed transmission.

[0024] Specifically, the master database node reads several configuration items pre-set by the database administrator from the PostgreSQL GUC parameter configuration file. These include at least a list of compression algorithm identifiers and a network I / O threshold. Optional parameters such as prediction enable flags can also be read as needed. The compression algorithm identifier list specifies the compression algorithm types supported by the master database node, such as LZ4 and ZSTD. Their corresponding binary codes will be referenced in the compression flag field of subsequent streaming replication protocol packets. The network I / O threshold is a critical reference value used to determine whether to trigger compressed transmission. The administrator can set it to a network bandwidth utilization threshold (e.g., 80%) or an actual transmission rate threshold (e.g., 100MB / s) based on the actual network bandwidth conditions of the production environment. This threshold directly affects the compression strategy selection of the master database node during cyclic transmission.

[0025] The above configuration parameters are loaded into the primary database node's in-memory runtime environment for subsequent steps to access in real time during each WAL log read and send decision. Simultaneously, the backup database node proactively initiates a streaming replication connection request to the primary database node. Upon receiving this request, the primary database node and the backup database node sequentially execute standard streaming replication handshake procedures, including authentication and replication site negotiation.

[0026] After successful authentication and agreement on the replication start point between both parties, a streaming replication connection channel for WAL log transmission is formally established between the primary and standby database nodes. This channel will carry out all subsequent sending and receiving operations of WAL log data packets, and its connection status will remain unchanged throughout the entire streaming replication lifecycle. S102: Read the raw WAL log data to be sent according to the preset fixed length, so that the main database node enters the cyclic sending state, and at the same time obtain the real-time network IO load value.

[0027] In some embodiments of this application, after the streaming replication connection is successfully established and initialized, the master database node immediately enters the WAL log cyclic sending process. The master database node reads the raw WAL log data blocks to be sent sequentially from the local WAL log file according to a preset fixed length. This preset fixed length is the size of a WAL log page or an integer multiple thereof, to ensure natural alignment with the WAL management mechanism of the PostgreSQL kernel and avoid unnecessary I / O fragmentation overhead caused by cross-page reading.

[0028] After each fixed-length data block is read, the primary database node performs a complete sending process (including subsequent compression decisions, compression processing, data packet encapsulation, and actual network transmission) on that data block. After the sending is completed, it immediately returns to read the next data block, thus forming a continuously running loop sending state, ensuring that the WAL log can be continuously transmitted from the primary database node to the backup database node in a streaming manner.

[0029] During each iteration of this cyclical sending state, the master database node also synchronously performs real-time network I / O load awareness. Specifically, the master database node obtains the network bandwidth utilization rate of the current streaming replication connection by calling the network interface provided by the operating system, or it can calculate the actual transmission rate of the current WAL log data by counting the actual number of bytes sent per unit time. The obtained network bandwidth utilization rate or actual transmission rate is then used as the real-time network I / O load value. This real-time network I / O load value is an instantaneous quantitative description of the current network transmission pressure state, and its value will directly participate in the triggering condition judgment of the compression strategy in subsequent steps, thereby enabling the compression decision to be dynamically adjusted closely following the real-time changes in the network environment.

[0030] S103: Based on the PostgreSQL transaction processing rate, calculate the predicted generation rate of the WAL log through the master database node, and determine the predicted network bandwidth demand value within a preset time window based on the predicted generation rate.

[0031] In some embodiments of this application, in addition to relying on the current real-time network IO load value for passive response judgment, the master database node further introduces an active prediction mechanism based on business load prediction to achieve forward-looking control of the compression strategy.

[0032] Specifically, the master database node continuously collects and maintains historical statistical data within multiple preset time windows in the background. The statistical data includes, but is not limited to, the number of transactions committed by the PostgreSQL instance within each time window and the amount of WAL log data actually generated within the corresponding time period.

[0033] Based on these historical observation data, the master database node establishes a linear mapping relationship between transaction processing volume and WAL log generation volume through regression analysis and other methods. This is because under PostgreSQL's MVCC and WAL mechanism, the length of the WAL record written for each data modification operation is roughly linearly related to the amount of tuple change involved in the transaction. Therefore, there is a quantifiable proportional relationship between the transaction commit rate and the WAL log generation rate.

[0034] Subsequently, the master database node obtains the transaction commit rate at the current moment (for example, by calculating the xact_commit increment in the pg_stat_database view over the last few seconds), and substitutes this transaction commit rate into the established linear mapping relationship to calculate in real time the predicted WAL log generation rate that will be generated in the next preset time window (for example, the next 100 milliseconds or 1 second).

[0035] Furthermore, the master database node predicts the generation rate based on the WAL log and, in conjunction with the compression ratio estimation coefficient (i.e., the empirical ratio of data volume before and after compression) pre-configured by the administrator or set by the system default, estimates the network bandwidth requirement for the streaming replication connection within a future preset time window without compression transmission. This estimation result is the predicted network bandwidth requirement value.

[0036] Through this predictive mechanism, the master database node can not only sense whether the network is congested, but also predict in advance that the bandwidth demand will exceed the available network capacity during the "window period" when the network has not yet experienced actual congestion but the business load is about to increase. This provides a more proactive and timely trigger for the next compression decision, effectively avoiding the instantaneous expansion of master-slave replication delay caused by sudden high-concurrency transactions.

[0037] S104: When the real-time network IO load value is less than the preset network IO threshold and the predicted network bandwidth demand value is less than the current available network bandwidth, the compression algorithm identifier of the primary database node is set to an uncompressed identifier, and the original WAL log data is directly encapsulated into a streaming replication data packet and sent to the backup database node.

[0038] In some embodiments of this application, after the master database node reads the raw data block of the WAL log and obtains the real-time network IO load value and the predicted network bandwidth demand value each time, it first performs a dual logical judgment on these two real-time / predictive indicators with preset threshold conditions.

[0039] Specifically, if the following two conditions are met simultaneously: first, the current real-time network IO load value is less than the network IO threshold read from the GUC parameter in step S101; second, the network bandwidth demand prediction value calculated in step S103 is less than the actual available bandwidth of the current network connection, it indicates that the current network resources are generally in a relatively abundant state, with neither immediate network transmission congestion nor the risk of bandwidth shortage caused by a sudden increase in business load in the short term.

[0040] In this relaxed network environment, continuing to compress the WAL logs, while potentially saving bandwidth, would result in a disproportionate CPU overhead for compression calculations compared to the marginal bandwidth gains, leading to unnecessary CPU resource consumption. Therefore, the master database node in this branch sets its compression strategy to uncompressed transmission mode: the master database node sets the compression flag field in the streaming replication protocol packet to an uncompressed identifier (e.g., binary code 0x00). This compression flag field is located after the original protocol header and before the sub-protocol payload in the protocol structure, and is stored contiguously with the protocol header field, occupying one byte of space.

[0041] Subsequently, the primary database node directly uses the raw WAL log data read in step S102 as the content of the sub-protocol payload, encapsulates it together with the protocol header and compression tag field into a complete streaming replication data packet, and sends the data packet to the backup database node through the established streaming replication connection channel.

[0042] Since the compression flag field explicitly indicates the uncompressed state, the backup database node will not need to perform any decompression operation upon receiving the data packet; it can directly write the raw WAL data from the subprotocol payload to the local WAL file. Through this branching process, the primary database node proactively forgoes compression during periods of sufficient bandwidth to save CPU resources, achieving a dynamic trade-off between network and computing resources.

[0043] S105: When the real-time network IO load value is greater than or equal to the network IO threshold, and / or the predicted network bandwidth demand value is greater than or equal to the current available network bandwidth, adaptive compression processing is performed on the raw WAL log data to generate compressed data and determine the corresponding compression algorithm identifier, and the compressed data is encapsulated into a streaming replication data packet and sent to the backup database node; the adaptive compression processing includes extracting samples from the raw WAL log data for multi-algorithm compression comparison to select a target compression algorithm for compression.

[0044] In some embodiments of this application, if the master database node finds any of the following triggering conditions during the dual judgment process in step S104: the current real-time network IO load value has reached or exceeded the preset network IO threshold, or the predicted network bandwidth demand value based on the transaction rate has reached or exceeded the current available network bandwidth, it indicates that the current network transmission environment is in or about to be in a high-load, high-pressure state. At this time, compressing the WAL log to reduce the actual amount of transmitted data and save network bandwidth becomes a necessary means to improve the overall replication efficiency.

[0045] In this scenario, the master database node enters an adaptive compression process. First, the master database node extracts a short header segment of a preset length from the currently read WAL log raw data block as a compression sample. The extraction position is at the beginning of the WAL log raw data. This length selection ensures that the sample has a certain data representativeness (sufficient to reflect the data distribution characteristics and redundancy of the current WAL page) while keeping the additional computational overhead of sample compression at the nanosecond level, thus not affecting the real-time throughput performance of streaming replication.

[0046] Subsequently, the master database node calls at least two compression algorithms specified in the configuration parameters in step S101 to perform a fast compression operation on the above-mentioned compressed sample, and obtains the sample compression results of each compression algorithm for the sample, including the compressed data volume and the CPU time consumed in the compression process.

[0047] Based on these sample compression results, the main database node calculates the compression ratio (i.e., the ratio of compressed volume to original volume) and unit compression time for each algorithm, and selects the target compression algorithm according to the preset cost-effectiveness rules: Specifically, the main database node calculates the ratio of compression ratio to compression time for each algorithm, and selects the algorithm with the largest ratio as the target compression algorithm for this transmission; if the above ratios of multiple algorithms are very similar, the algorithm with shorter compression time is selected as the target compression algorithm to maximize the protection of CPU resources under similar bandwidth saving effects.

[0048] After the target compression algorithm is selected, the master database node sets the compression flag field in the streaming replication protocol packet to the compression algorithm identifier corresponding to the target compression algorithm. For example, if LZ4 is selected, the compression flag field is set to 0x01; if ZSTD is selected, the compression flag field is set to 0x02. Then, the master database node calls the selected target compression algorithm to perform a formal compression operation on the complete WAL log raw data block, generating compressed WAL data.

[0049] After compression is complete, the master database node needs to perform a compression validity self-check step, comparing the data volume of the compressed WAL data with the data volume of the original WAL log data. If the compressed volume is smaller than the original volume, the compressed data will be encapsulated as the content of the sub-protocol payload into the streaming replication data packet. Conversely, if the compressed volume is greater than or equal to the original volume due to reasons such as extremely high entropy of the data itself or incompatibility of the compression algorithm, the master database node will abandon the result of this compression operation, revert the compression flag field to the uncompressed flag, and directly encapsulate the original WAL log data into the sub-protocol payload.

[0050] Through this compression effectiveness self-checking and automatic degradation mechanism, the master database node effectively avoids the double waste of resources caused by invalid compression or data expansion.

[0051] Finally, the primary database node sends the encapsulated streaming replication data packet to the backup database node via the streaming replication connection.

[0052] After receiving the streaming replication data packet, the backup database node first parses the compression flag field: if the field is an uncompressed flag, the backup database node directly writes the original data in the subprotocol payload to the local WAL file; if the field is a compression algorithm flag, the backup database node calls the decompression algorithm corresponding to the flag to decompress the data in the subprotocol payload, and writes the decompressed original WAL log data to the local WAL file, thus completing a complete round of dynamic adaptive compression transmission.

[0053] After this, the master database node returns to step S102 and continues to read the next fixed-length WAL log data block, repeating the above cyclic sending process until the streaming replication connection terminates or the WAL log is sent.

[0054] Through the organic coordination of the above steps S101 to S105, the embodiments of this application integrate four mechanisms into a complete adaptive compression decision closed loop: passive response based on real-time network I / O monitoring, active prediction based on transaction rate prediction, dynamic selection of multiple algorithms based on content sampling, and automatic degradation for invalid compression. This enables the WAL log transmission in the PostgreSQL streaming replication process to follow the dynamic changes of the network environment and business load in real time, automatically finding the optimal balance between bandwidth saving and CPU protection, thereby significantly improving the real-time performance, stability, and overall resource utilization efficiency of master-slave replication.

[0055] It should be noted that, although the embodiments in this application are based on... Figure 1 Steps S101 to S105 will be described sequentially, but this does not mean that steps S101 and S105 must be performed in a strict order. The reason this embodiment follows this order is... Figure 1 The order in which steps S101 to S105 are described is provided to facilitate understanding of the technical solutions of the embodiments of this application by those skilled in the art. In other words, in the embodiments of this application, the order of steps S101 to S105 can be appropriately adjusted according to actual needs.

[0056] pass Figure 1 This application utilizes a dual approach: real-time monitoring of network I / O load combined with prediction of WAL log generation rate. This upgrades the compression strategy from passive response to proactive prediction. When network congestion is not yet apparent during peak business periods, but bandwidth demand is predicted to exceed limits, the primary database node can proactively enable compressed transmission. This effectively avoids the accumulation of instantaneous replication delays caused by sudden traffic surges, ensuring the real-time performance and stability of primary-backup data synchronization. Secondly, upon entering compression mode, this solution does not mechanically execute a single fixed compression algorithm. Instead, it extracts samples of the raw WAL log data to be sent and performs rapid compression comparisons using multiple algorithms. The optimal compression algorithm for this transmission is dynamically selected based on the cost-effectiveness of compression ratio and time consumption. This mechanism matches compression behavior with specific data characteristics, minimizing unnecessary CPU resource overhead while ensuring bandwidth savings. Finally, by adding a compression flag field to the streaming replication protocol packet, the backup database node can automatically identify the compression status and algorithm type of data packets without relying on any static configuration, achieving transparent collaboration of compression strategies between primary and backup nodes.

[0057] Figure 2 This is a design diagram of a streaming replication protocol for a WAL log compression method under PostgreSQL streaming replication, provided in an embodiment of this application.

[0058] Figure 3A schematic diagram of a WAL log compression device under PostgreSQL streaming replication provided in this application embodiment includes: At least one processor; and, A memory that is communicatively connected to at least one processor; wherein, The memory stores instructions that can be executed by at least one processor, which enable at least one processor to perform any of the above-mentioned WAL log compression methods under PostgreSQL streaming replication.

[0059] Some embodiments of this application provide a non-volatile computer storage medium for WAL log compression under PostgreSQL streaming replication, which stores computer-executable instructions capable of executing any of the above-mentioned WAL log compression methods under PostgreSQL streaming replication.

[0060] The various embodiments in this application are described in a progressive manner. Similar or identical parts between embodiments can be referred to mutually. Each embodiment focuses on describing the differences from other embodiments. In particular, the device and medium embodiments are basically similar to the method embodiments, so the description is relatively simple; relevant parts can be referred to the description of the method embodiments.

[0061] The devices and media provided in this application are one-to-one with the methods. Therefore, the devices and media also have similar beneficial technical effects as their corresponding methods. Since the beneficial technical effects of the methods have been described in detail above, the beneficial technical effects of the devices and media will not be repeated here.

[0062] Those skilled in the art will understand that embodiments of the present invention can be provided as methods, systems, or computer program products. Therefore, the present invention can take the form of a completely hardware embodiment, a completely software embodiment, or an embodiment combining software and hardware aspects. Furthermore, the present invention can take the form of a computer program product embodied on one or more computer-usable storage media (including, but not limited to, disk storage, CD-ROM, optical storage, etc.) containing computer-usable program code.

[0063] This invention is described with reference to flowchart illustrations and / or block diagrams of methods, apparatus (systems), and computer program products according to embodiments of the invention. It will be understood that each block of the flowchart illustrations and / or block diagrams, and combinations of blocks in the flowchart illustrations and / or block diagrams, 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 processor, 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, generate instructions for implementing the flowchart illustrations and / or block diagrams. Figure 1 One or more processes and / or boxes Figure 1 A device that provides the functions specified in one or more boxes.

[0064] These computer program instructions may also be stored in a computer-readable storage medium that can direct a computer or other programmable data processing device to function in a particular manner, such that the instructions stored in the computer-readable storage medium produce an article of manufacture including instruction means, which are implemented in a process Figure 1 One or more processes and / or boxes Figure 1 The function specified in one or more boxes.

[0065] These computer program instructions may also be loaded onto a computer or other programmable data processing equipment to cause a series of operational steps to be performed on the computer or other programmable equipment to produce a computer-implemented process, thereby providing instructions that execute on the computer or other programmable equipment for implementing the process. Figure 1 One or more processes and / or boxes Figure 1 The steps of the function specified in one or more boxes.

[0066] In a typical configuration, a computing device includes one or more processors (CPU), input / output interfaces, network interfaces, and memory.

[0067] Memory may include non-persistent storage in computer-readable media, random access memory (RAM), and non-volatile memory such as read-only memory (ROM) or flash RAM. Memory is an example of computer-readable media.

[0068] Computer-readable media includes both permanent and non-permanent, removable and non-removable media that can store information using any method or technology. Information can be computer-readable instructions, data structures, modules of programs, or other data. Examples of computer storage media include, but are not limited to, phase-change memory (PRAM), static random access memory (SRAM), dynamic random access memory (DRAM), other types of random access memory (RAM), read-only memory (ROM), electrically erasable programmable read-only memory (EEPROM), flash memory or other memory technologies, CD-ROM, digital versatile optical disc (DVD) or other optical storage, magnetic tape, magnetic disk storage or other magnetic storage devices, or any other non-transferable medium that can be used to store information accessible by a computing device. As defined herein, computer-readable media does not include transient computer-readable media, such as modulated data signals and carrier waves.

[0069] It should also be noted that the terms "comprising," "including," or any other variations thereof are intended to cover non-exclusive inclusion, such that a process, method, article, or apparatus that comprises a list of elements includes not only those elements but also other elements not expressly listed, or elements inherent to such process, method, article, or apparatus. Unless otherwise specified, an element defined by the phrase "comprising one..." does not exclude the presence of other identical elements in the process, method, article, or apparatus that includes that element.

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

Claims

1. A WAL log compression method under PostgreSQL streaming replication, characterized in that, The method includes: When the primary database node starts, it reads preset configuration parameters and establishes a streaming replication connection between the primary database node and the backup database node; the configuration parameters include compression algorithm identifier and network I / O threshold. Read the raw WAL log data to be sent according to a preset fixed length, so that the main database node enters the cyclic sending state, and at the same time obtain the real-time network IO load value. Based on the PostgreSQL transaction processing rate, the predicted generation rate of the WAL log is calculated through the master database node, and the predicted network bandwidth demand within a preset time window is determined based on the predicted generation rate. When the real-time network I / O load value is less than the preset network I / O threshold and the predicted network bandwidth demand value is less than the current available network bandwidth, the compression algorithm identifier of the primary database node is set to an uncompressed identifier, and the original WAL log data is directly encapsulated into a streaming replication data packet and sent to the backup database node. When the real-time network I / O load value is greater than or equal to the network I / O threshold, and / or the predicted network bandwidth demand value is greater than or equal to the current available network bandwidth, adaptive compression processing is performed on the raw WAL log data to generate compressed data and determine the corresponding compression algorithm identifier. The compressed data is then encapsulated into a streaming replication data packet and sent to the backup database node. The adaptive compression processing includes extracting samples from the raw WAL log data for multi-algorithm compression comparison to select a target compression algorithm for compression.

2. The method according to claim 1, characterized in that, The process of performing adaptive compression on the raw WAL log data to generate compressed data and determine the corresponding compression algorithm identifier specifically includes: A header data of a preset length is extracted from the original WAL log data as a compressed sample; At least two compression algorithms corresponding to the compression algorithm identifier in the configuration parameters are called respectively to perform compression operations on the compressed sample, and the compression ratio and compression time of the sample compression corresponding to each algorithm are obtained; Based on the compression ratio and compression time, a target compression algorithm is selected according to a preset cost-performance ratio rule. Set the compression algorithm identifier to the compression algorithm identifier corresponding to the target compression algorithm; The target compression algorithm is invoked to compress the original WAL log data, generating compressed data.

3. The method according to claim 2, characterized in that, After the method calls the target compression algorithm to compress the original WAL log data and generates compressed data, the method further includes: Compare the data volume of the compressed data with that of the original WAL log data; If the volume of the compressed data is smaller than the volume of the original WAL log data, the compressed data will be encapsulated into the subprotocol payload of the streaming replication data packet; If the compressed volume is greater than or equal to the original WAL log data volume, the compression algorithm flag is reverted to the uncompressed flag, and the original WAL log data is directly encapsulated into the subprotocol payload.

4. The method according to claim 1, characterized in that, After encapsulating the compressed data into a streaming replication data packet and sending it to the backup database node, the method further includes: The backup database node receives the stream replication data packet and parses the compression algorithm identifier; If the compression algorithm is identified as uncompressed, the original data in the subprotocol payload is directly written to the local WAL file through the backup database node. If the compression algorithm identifier is the identifier of the corresponding target compression algorithm, the corresponding decompression algorithm is called by the backup database node to decompress the data in the subprotocol payload, and the decompressed data is written to the local WAL file.

5. The method according to claim 1, characterized in that, The calculation of the predicted WAL log generation rate based on the PostgreSQL transaction processing rate via the master database node specifically includes: Collect the number of committed transactions and the amount of WAL log data generated by the master database node within multiple consecutive preset time windows; Based on the number of committed transactions and the amount of WAL log data, establish a linear mapping relationship between the transaction processing volume and the WAL log generation volume; Obtain the transaction commit rate of the master database node, and calculate the predicted WAL log generation rate within the next preset time window based on the linear mapping relationship.

6. The method according to claim 1, characterized in that, The process of reading the raw WAL log data to be sent according to a preset fixed length, so that the main database node enters a cyclic sending state, and simultaneously obtaining the real-time network IO load value, specifically includes: The master database node reads a preset fixed-length WAL log raw data block from the local WAL log file; after each read is completed, the master database node performs a data sending process and returns to read the next data block after the sending is completed, thus forming a cyclic sending state; Each time the master database node enters the read operation in the loop sending state, it obtains the network bandwidth utilization rate of the current streaming replication connection by calling the operating system network interface, and uses the network bandwidth utilization rate as the real-time network I / O load value.

7. The method according to claim 1, characterized in that, The step of reading preset configuration parameters and establishing a streaming replication connection between the primary database node and the backup database node upon startup specifically includes: During the startup process of the master database node, a pre-set list of compression algorithm identifiers and network I / O thresholds are read from the PostgreSQL GUC parameter configuration file, and the list of compression algorithm identifiers and the network I / O thresholds are loaded into the memory runtime environment of the master database node. The backup database node initiates a streaming replication connection request to the primary database node. After receiving the connection request, the primary database node performs authentication and replication site negotiation with the backup database node. After the authentication and replication site negotiation is successful, a streaming replication connection channel for WAL log transmission is established between the primary database node and the backup database node.

8. The method according to claim 1, characterized in that, The method further includes: The compression algorithm identifier is located after the stream replication protocol header and before the subprotocol payload, and is continuous with the original header fields. It is in binary encoding, and different encodings correspond to different compression states and algorithms.

9. A WAL log compression device for PostgreSQL streaming replication, characterized in that, include: At least one processor; as well as, A memory communicatively connected to the at least one processor; wherein, The memory stores instructions that can be executed by the at least one processor, which, when executed by the at least one processor, enables the at least one processor to perform a WAL log compression method under PostgreSQL streaming replication as described in any one of claims 1-8.

10. A WAL log compression storage medium under PostgreSQL streaming replication, storing computer-executable instructions, characterized in that, The computer-executable instructions are capable of executing the WAL log compression method under PostgreSQL streaming replication as described in any one of claims 1-8.

Images