A method for lazy instruction optimization and monitoring for Postgres databases

Abstract

The application relates to the technical field of data storage, and discloses a Postgres database lag instruction optimization and monitoring method, which is characterized in that: a dynamic instruction of a lag instruction is replaced and reorganized according to a template file, so that a real executable lag instruction is obtained, the convenience and accuracy of operation and maintenance are greatly improved, and the pain of manually constructing a context is eliminated; the lag instruction is subjected to context filling, the lag instruction dynamic parameter is realized, invalid instruction units are eliminated, the slow retrieval positioning efficiency is improved, and the burden of operation and maintenance personnel is reduced; an execution plan and a query body of the lag instruction are saved and displayed for monitoring, the execution plan during lag instruction running is saved, deviation of the execution plan caused by subsequent data plan changes is avoided, the lag instruction is recorded in shared memory, then a view is registered, and the instruction is conveniently persisted in the database from the view by a monitoring system.

Description

Technical Field

[0001] This invention relates to the field of data storage technology, and in particular to a method for optimizing and monitoring slow commands in Postgres databases. Background Technology

[0002] In data storage systems, delayed commands refer to retrieval commands whose execution time exceeds a specific threshold. Databases typically follow this delayed command recording process: First, calculate the execution time of the delayed command; if it exceeds the threshold, record the original command in memory. Second, save the content in memory to a data table buffer. Third, flush the buffer content to disk. Traditional PostgreSQL delayed command recording functionality is limited, only recording the original retrieval command and the time required for the retrieval, and storing delayed commands in a log system. The original retrieval command may contain useless or missing information, making it difficult for operations personnel to pinpoint the cause of the delayed command. If the original command contains invalid information, operations personnel need to manually remove the relevant useless information to obtain a statement close to the true delayed command; the difficulty of this process increases exponentially with the complexity of the delayed command. If the original command is indeed missing information, operations personnel need to manually add the information, which also presents an exponential increase in complexity. Furthermore, using traditional logging methods, optimizing delayed command content and retaining more runtime context may increase disk I / O overhead, impacting core database performance.

[0003] For example, Chinese patent CN109344202B discloses a data synchronization method and management node. The method is applied to a management node in a big data management system. The management node includes a primary management node and a backup management node. The method includes: the primary management node periodically sending attribute information of a first Postgres database corresponding to the primary management node to a Dynamic Host Configuration Protocol (DHCP) server; the backup management node periodically requesting the attribute information of the first Postgres database from the DHCP server; and when the backup management node determines that the obtained attribute information of the first Postgres database is different from the attribute information of its corresponding second Postgres database, it synchronizes the data of the first Postgres database to the second Postgres database. This improves the operational reliability of the big data integration management platform.

[0004] The aforementioned patents suffer from the problems described in this background section: traditional Postgres slow command logging functionality is limited, only recording the original search command and the time required for the search; the original search command may contain useless or missing information, making it difficult for maintenance personnel to pinpoint the cause of slow commands; using traditional logging methods, optimizing slow command content and retaining more runtime context may increase disk I / O overhead, impacting core database performance. To address this issue, this invention proposes a method for optimizing and monitoring slow commands in Postgres databases. Summary of the Invention

[0005] The purpose of this section is to outline some aspects of embodiments of the present invention and to briefly describe some preferred embodiments. Simplifications or omissions may be made in this section, as well as in the abstract and title of this application, to avoid obscuring the purpose of these documents; however, such simplifications or omissions should not be construed as limiting the scope of the invention.

[0006] In view of the problems existing in the current method for optimizing and monitoring slow commands for Postgres databases, this invention is proposed.

[0007] Therefore, the purpose of this invention is to provide a method for optimizing and monitoring slow commands in Postgres databases.

[0008] To solve the above technical problems, the present invention provides the following technical solution: replacing and recombining the dynamic instructions of the deferred instructions according to the template file;

[0009] Context filling is performed on the deferred instructions;

[0010] The execution plan and query body of the slow instructions are saved, displayed, and monitored.

[0011] As a preferred embodiment of the method for optimizing and monitoring slow commands in Postgres databases as described in this invention, a preset template file is loaded in the database, or the template file can be dynamically added and modified to implement new optimization rules for slow commands.

[0012] The replacement and reorganization process includes: the kernel first runs a virtual machine, which hot-updates the rules of the template file into the kernel engine. Operators can dynamically modify the template file and dynamically add the rules of the template file into the engine through the hot-update feature of dynamic languages, and remove redundant units through the rules of the template file.

[0013] As a preferred embodiment of the method for optimizing and monitoring slow commands in a Postgres database as described in this invention, the specific steps of the replacement and reorganization are as follows:

[0014] First, a template file for reassembling deferred instructions is preset; then, the dynamic language virtual machine is invoked to load the template file and generate an optimization interface for the corresponding dynamic instructions; finally, the reassembled deferred instructions that meet the requirements are obtained after passing through the template optimization interface.

[0015] As a preferred embodiment of the method for optimizing and monitoring slow commands in a Postgres database according to the present invention, the context filling scheme is as follows:

[0016] When a delayed instruction is received, the dynamic parameters input by the client are first saved to a specific container. Then, the text content of the delayed instruction is passed to the template file processing unit and template file replacement unit from the previous step. The delayed instruction is regenerated based on the template file, eliminating invalid information and making the instruction an actually executable delayed instruction. Finally, the instruction parameters in the delayed instruction are identified. In the delayed instruction parameter replacement processing unit, if a specific instruction parameter is identified, the parameter in the specific container is replaced to concretize the parameter.

[0017] As a preferred embodiment of the method for optimizing and monitoring slow commands in Postgres database as described in this invention, the slow commands are recorded in shared memory and then a view is registered, which facilitates the monitoring system to persist the slow commands to the database from the view, thus avoiding the IO overhead of the kernel directly writing to disk.

[0018] The specific rules for saving and displaying the execution plan and query body of the slow instructions are as follows:

[0019] Save the delayed instructions to shared memory;

[0020] Shared memory exceeding the specified size requires optimization.

[0021] Persist and display slow-moving searches.

[0022] As a preferred embodiment of the method for optimizing and monitoring slow commands in Postgres databases as described in this invention, when each slow command exceeds a predetermined time threshold, the query_text of the slow retrieval, the client IP, username, and the execution tree of the current execution plan are stored in the shared memory.

[0023] As a preferred embodiment of the method for optimizing and monitoring slow commands in a Postgres database as described in this invention, the rules for the optimization process are as follows:

[0024] When the monitoring and query system finds the corresponding delayed instruction through the preset query view, it stores the delayed instruction in the circular array of the shared memory. If the number of delayed instructions reaches the maximum number of shared memory storage, it sends a request to the monitoring and query system to synchronize the delayed retrieval content. After synchronization is completed, the delayed instruction array will be overwritten from the beginning.

[0025] As a preferred embodiment of the method for optimizing and monitoring slow commands in a Postgres database as described in this invention, the persistence and display method includes saving the structure of the slow retrieval to a local persistent database after the monitoring query system retrieves the stored slow retrieval content from the shared memory in the database through a query view, for subsequent display by the monitoring system.

[0026] A computer device includes: a memory for storing instructions; and a processor for executing the instructions, causing the device to implement a method for optimizing and monitoring slow instructions for a Postgres database.

[0027] A computer-readable storage medium having a computer program stored thereon, which, when executed, implements a method for optimizing and monitoring slow commands for a Postgres database.

[0028] The beneficial effects of this invention are as follows: By replacing and recombining the dynamic instructions of delayed instructions according to a template file, this invention obtains truly executable delayed instructions, greatly improving the convenience and accuracy of operation and maintenance, and eliminating the pain of manually constructing context. Context filling of delayed instructions is performed by integrating lexical deconstruction, syntactic deconstruction modules, and configuration template file processing units to concretize the dynamic parameters of delayed instructions, while eliminating invalid instruction units, thereby improving the efficiency of slow retrieval and locating, and reducing the burden on operation and maintenance personnel. The execution plan and query body of delayed instructions are saved and monitored. Saving the execution plan during the execution of delayed instructions avoids deviations in the execution plan caused by subsequent changes in the data plan. By recording delayed instructions in shared memory and then registering a view, the monitoring system can easily persist the instructions from the view to the database, avoiding the I / O overhead of direct disk writes and reducing the impact of delayed retrieval and disk writes on the core performance of the database. This also facilitates the location and viewing of delayed instructions by operation and maintenance personnel. Attached Figure Description

[0029] To more clearly illustrate the technical solutions of the embodiments of the present invention, the drawings used in the description of the embodiments will be briefly introduced below. Obviously, the drawings described below are only some embodiments of the present invention. For those skilled in the art, other drawings can be obtained based on these drawings without creative effort. Wherein:

[0030] Figure 1 This is a flowchart of a method for optimizing and monitoring slow commands in a Postgres database according to the present invention.

[0031] Figure 2 This invention provides a template file processing flow and rule diagram for a method to optimize and monitor slow commands in a Postgres database.

[0032] Figure 3 This diagram illustrates the optimization process for data exceeding size in shared memory in a method for optimizing and monitoring slow commands in Postgres databases, as described in this invention. Detailed Implementation

[0033] To make the above-mentioned objects, features and advantages of the present invention more apparent and understandable, the specific embodiments of the present invention will be described in detail below with reference to the accompanying drawings.

[0034] Many specific details are set forth in the following description in order to provide a full understanding of the invention. However, the invention may also be practiced in other ways different from those described herein, and those skilled in the art can make similar extensions without departing from the spirit of the invention. Therefore, the invention is not limited to the specific embodiments disclosed below.

[0035] Secondly, the term "one embodiment" or "embodiment" as used herein refers to a specific feature, structure, or characteristic that may be included in at least one implementation of the present invention. The phrase "in one embodiment" appearing in different places in this specification does not necessarily refer to the same embodiment, nor is it a single or selective embodiment that is mutually exclusive with other embodiments.

[0036] Secondly, the present invention is described in detail with reference to the schematic diagrams. When detailing the embodiments of the present invention, for ease of explanation, the cross-sectional views illustrating the device structure may be partially enlarged, not according to the usual scale. Furthermore, the schematic diagrams are merely examples and should not limit the scope of protection of the present invention. In addition, actual fabrication should include three-dimensional spatial dimensions of length, width, and depth.

[0037] Example 1

[0038] This embodiment provides a flowchart of a method for optimizing and monitoring slow commands in a Postgres database, as shown below. Figure 1 As shown, a method for optimizing and monitoring slow commands in a Postgres database includes:

[0039] S1. Replace and reorganize the dynamic instructions of the deferred instructions according to the template file;

[0040] Pre-defined template files can be loaded from the database, and template files can also be dynamically added and modified to implement new optimization rules for delayed instructions.

[0041] The replacement and reorganization process includes: the kernel first runs a virtual machine, the virtual machine hot-updates the rules of the template file to the kernel engine, the operator can dynamically modify the template file, and through the hot-update feature of dynamic language, dynamically add the rules of the template file to the engine, and remove redundant units through the rules of the template file;

[0042] The specific steps for replacement and recombination are as follows:

[0043] First, a template file for reassembling deferred instructions is pre-defined; then, the dynamic language virtual machine is called to load the template file and generate the optimization interface for the corresponding dynamic instructions; finally, after passing through the template optimization interface, the reassembled deferred instructions that meet the requirements are obtained.

[0044] The delayed instruction dynamic replacement and recombination module first loads a preset template file. Based on the delayed instructions, if a preset recombination rule is identified, the corresponding processing unit is activated, such as... Figure 2 As shown;

[0045] In this embodiment, the method of reorganizing and replacing the prepare instruction is adopted. After the prepare instruction is passed to the processing unit, the processing unit only retains the modules of interest for slow retrieval, and the other redundant units adopt the corresponding processing method. This embodiment is to eliminate useless information.

[0046] Finally, after processing all the reorganization rules, the optimized instructions are returned.

[0047] S2. Fill in the context for deferred instructions;

[0048] The context filling scheme is as follows:

[0049] When a deferred instruction is received, the dynamic parameters input by the client are first saved into a specific container. Then, the text content of the deferred instruction is passed to the template file processing unit and template file replacement unit from the previous step. The deferred instruction is regenerated based on the template file. This process eliminates invalid information and makes the instruction an actual executable deferred instruction. Finally, the instruction parameters in the deferred instruction are identified. In the deferred instruction parameter replacement processing unit, if a specific instruction parameter is identified, the parameter in the specific container is replaced to make the parameter specific.

[0050] The instruction replacement and reorganization process includes saving the dynamic parameters of the deferred instruction input by the client to a database memory structure, such as... Figure 2As shown; then the text content of the delayed instruction is analyzed, and after template reorganization, the delayed instruction parameters in the delayed instruction are identified. In the analysis tree, if the delayed instruction parameters are identified, the parameters in the memory structure are replaced to specify the dynamic parameters of the delayed instruction.

[0051] S3. Save, display, and monitor the execution plan and query body of slow instructions;

[0052] By recording delayed instructions in shared memory and then registering a view, the monitoring system can easily persist delayed instructions from the view to the database, avoiding the IO overhead of the kernel directly writing to disk.

[0053] The specific rules for saving and displaying the execution plan and query body of slow commands are as follows:

[0054] Save the delayed instructions to shared memory;

[0055] When each delayed instruction exceeds the predetermined time threshold, the query_text of the delayed retrieval, the client IP, username, and the execution tree of the current execution plan are saved in shared memory;

[0056] Shared memory exceeding the specified size requires optimization.

[0057] The optimization rules are as follows:

[0058] When the monitoring and query system finds the corresponding delayed instruction through the preset query view, it stores the delayed instruction in a circular array in shared memory. If the number of delayed instructions reaches the maximum number of shared memory storage, it sends a request to the monitoring and query system to synchronize the delayed retrieval content. After synchronization is completed, the delayed instruction array will be overwritten from the beginning.

[0059] Persist and display slow-moving searches;

[0060] The persistence and display methods include saving the structure of the delayed retrieval to a local persistent database after the monitoring query system retrieves the stored delayed retrieval content from the shared memory of the database through the query view, for subsequent display by the monitoring system.

[0061] Delayed instruction execution plan and query body storage display as follows Figure 3As shown, it mainly consists of three parts: First, a delayed instruction is set to run for a duration exceeding a predetermined time threshold, and then the text of the delayed instruction, the client IP, the username, and the execution tree of the current execution plan are stored in shared memory. Second, when the monitoring query system finds the corresponding delayed instruction through a preset query view, it stores the delayed instruction in a circular array in shared memory. If the number of delayed instructions reaches the maximum shared memory storage limit, a request is sent to the monitoring system to synchronize the delayed retrieval content. After synchronization is complete, subsequent received instructions will overwrite the delayed instruction array from the beginning, and the overwritten delayed instruction units will be marked as non-overwriteable. Third, when the monitoring system actively or passively retrieves the stored delayed retrieval content from the shared memory of the database through a query view, it will save the delayed retrieval structure to a local persistent database for subsequent display by the monitoring system.

[0062] Example 2

[0063] In this embodiment, a computer device is provided, including a memory and a processor. The memory is used to store instructions, and the processor is used to execute the instructions, causing the computer device to perform the steps of the above-described method for optimizing and monitoring slow instructions for a Postgres database.

[0064] Example 3

[0065] In this embodiment, a computer-readable storage medium is provided, on which a computer program is stored. When the computer program is executed, it implements the steps of the above-described method for optimizing and monitoring slow commands for a Postgres database.

[0066] The computer-readable storage medium may include various media capable of storing program code, such as USB flash drives, portable hard drives, read-only memory, random access memory, magnetic disks, or optical disks.

[0067] It should be noted that the above embodiments are only used to illustrate the technical solutions of the present invention and are not intended to limit it. Although the present invention has been described in detail with reference to preferred embodiments, those skilled in the art should understand that modifications or equivalent substitutions can be made to the technical solutions of the present invention without departing from the spirit and scope of the technical solutions of the present invention, and all such modifications or substitutions should be covered within the scope of the claims of the present invention.

Claims

1. A method for optimizing and monitoring slow commands in a Postgres database, characterized by: include, Replace and reorganize the dynamic instructions of the deferred instructions according to the template file; Load preset template files into the database, dynamically add and modify template files, and implement optimization rules for new deferred commands; The replacement and reorganization process includes: the kernel first runs a virtual machine, which hot-updates the rules of the template file into the kernel engine. Operators can dynamically modify the template file and dynamically add the rules of the template file into the engine through the hot-update feature of dynamic languages, and remove redundant units through the rules of the template file. The specific steps of the replacement and recombination are as follows: First, a template file for reassembling deferred instructions is preset; then, the dynamic language virtual machine is invoked to load the template file and generate an optimization interface for the corresponding dynamic instructions; finally, the reassembled deferred instructions that meet the requirements are obtained after passing through the template optimization interface. The delayed instructions are then filled with context; the context filling scheme is as follows: When a delayed instruction is received, the dynamic parameters input by the client are first saved to a specific container. Then, the text content of the delayed instruction is passed to the template file processing unit and template file replacement unit from the previous step. The delayed instruction is regenerated based on the template file, which eliminates invalid information and makes the instruction an actually executable delayed instruction. Finally, the instruction parameters in the delayed instruction are identified. In the delayed instruction parameter replacement processing unit, if a specific instruction parameter is identified, the parameter in the specific container is replaced to concretize the parameter. The execution plan and query body of the slow instructions are saved, displayed, and monitored.

2. The method for optimizing and monitoring slow commands in a Postgres database as described in claim 1, characterized in that: By recording the delayed instructions in shared memory and then registering a view, the monitoring system can easily persist the delayed instructions from the view to the database, avoiding the IO overhead of the kernel directly writing to disk. The specific rules for saving and displaying the execution plan and query body of the slow instructions are as follows: Save the delayed instructions to shared memory; Shared memory exceeding the specified size requires optimization. Persist and display slow-moving searches.

3. The method for optimizing and monitoring slow commands in a Postgres database as described in claim 2, characterized in that: When each delayed instruction exceeds a predetermined time threshold, the delayed query_text, client IP, username, and execution tree of the current execution plan are saved in the shared memory.

4. The method for optimizing and monitoring slow commands in a Postgres database as described in claim 3, characterized in that: The rules for the optimization process are as follows: When the monitoring and query system finds the corresponding delayed instruction through the preset query view, it stores the delayed instruction in the circular array of the shared memory. If the number of delayed instructions reaches the maximum number of shared memory storage, it sends a request to the monitoring and query system to synchronize the delayed retrieval content. After synchronization is completed, the delayed instruction array will be overwritten from the beginning.

5. The method for optimizing and monitoring slow commands in a Postgres database as described in claim 4, characterized in that: The persistence and display method includes saving the structure of the slow-retrieval data to a local persistent database after the monitoring query system retrieves the stored slow-retrieval content from the shared memory in the database through a query view, for subsequent display by the monitoring system.

6. A computer device, characterized in that: include, Memory, used to store instructions; A processor for executing the instructions, causing the device to perform a method for optimizing and monitoring slow instructions for a Postgres database as described in any one of claims 1-5.

7. A computer-readable storage medium having a computer program stored thereon, characterized in that: When the computer program is executed, it implements a method for optimizing and monitoring slow commands for a Postgres database as described in any one of claims 1-5.

Images