Data management optimization method and system for database performance diagnosis report system
By optimizing the memory management and storage structure of the WDR module, the problems of low memory efficiency, insufficient SQL normalization capability, and high snapshot data maintenance cost were solved, resulting in reduced memory usage, improved system stability, and enhanced performance diagnostic efficiency.
Patent Information
- Authority / Receiving Office
- CN · China
- Patent Type
- Applications(China)
- Current Assignee / Owner
- Filing Date
- 2026-01-21
- Publication Date
- 2026-04-03
AI Technical Summary
The existing Workload Diagnostic Reporting (WDR) module suffers from low memory efficiency, limited SQL normalization capabilities, and high snapshot data maintenance costs, leading to increased risk of memory overflow, wasted storage resources, and limited scalability of performance diagnostic functions.
It adopts independent allocation of fixed-length metadata and variable-length text storage areas, memory management that supports disk overflow, normalization of IN clauses, and a partitioned table structure without log records. Combined with partition cascading operations, it optimizes memory usage and storage management.
Significantly reduces memory usage, avoids memory overflow, improves system stability, reduces storage overhead, increases report generation efficiency, and provides accurate performance data support.
Smart Images
Figure CN121786067A_ABST
Abstract
Description
Technical Field
[0001] This application relates to the field of database data management technology, and in particular to a data management optimization method, system, computer-readable storage medium, and electronic device for a database performance diagnostic reporting system. Background Technology
[0002] Workload Diagnostic Report (WDR), as a core mechanism for performance diagnosis of modern relational databases, continuously collects key performance indicators such as SQL execution statistics, wait events, and system load by periodically capturing database snapshots and generating diagnostic reports, providing data support for database administrators to locate performance bottlenecks. However, the implementation of existing WDR modules faces the following common technical challenges:
[0003] (1) Low memory efficiency of SQL text storage: WDR needs to maintain a unique set of SQL statements throughout the entire lifecycle of the instance. Existing solutions mostly adopt a shared hash table structure and pre-allocate a fixed metadata area and contiguous memory space for each entry, resulting in a serious underutilization of storage space for short SQL statements. When performing a full query or dump, loading the hash table at once can easily cause an instantaneous memory expansion, significantly increasing the risk of Out of Memory (OOM). In addition, the memory reclamation mechanism is imperfect. After the entries are cleaned up, the allocated memory cannot be completely released by the system, which poses a potential memory leak risk.
[0004] (2) Limited SQL normalization capability: In order to compress storage size, the system needs to perform constant parameterization processing on SQL text. However, the existing technology has weak normalization capability for IN clauses and cannot effectively identify logically equivalent SQL such as a IN (1, 2) and a IN (1, 2, 3), resulting in repeated storage of similar statements, which further aggravates memory and storage overhead.
[0005] (3) High maintenance cost of snapshot data: As a storage carrier for massive time-series data, the WDR snapshot table generates a large amount of write-ahead logs (WAL / xlog) due to high-frequency INSERT and DELETE operations, which puts significant pressure on system I / O. In particular, when cleaning up expired snapshot data, the row-by-row deletion mechanism is inefficient and easily leads to dead tuple accumulation and table fragmentation, increasing the burden of database maintenance. In addition, some distributed databases have redundant global and summary table structures in single-machine deployment scenarios, resulting in a waste of storage resources.
[0006] In summary, the technical bottlenecks in memory efficiency, normalization capabilities, and storage maintenance inherent in the WDR data management mechanism restrict the large-scale application of performance diagnostic functions and system stability. Therefore, designing an efficient, low-power, and intelligent WDR data management mechanism to address these memory, storage, and performance issues is a critical technical challenge that urgently needs to be overcome in the current database technology field. Summary of the Invention
[0007] To overcome the aforementioned deficiencies in the existing technology, this application proposes a new data management optimization method and system for a database performance diagnostic reporting system, aiming to reduce the memory and storage overhead of the WDR module and improve its operating efficiency and stability.
[0008] Specifically, this application provides the following technical solutions:
[0009] The first aspect of this application provides a data management optimization method for a database performance diagnostic reporting system, wherein the database performance diagnostic reporting system is a WDR system, such as... Figure 1 As shown, the method includes:
[0010] S1. Unique SQL Statement Memory Management: Each unique SQL statement is allocated a fixed-length metadata area and a variable-length text storage area in memory, and the two are associated through pointers. When executing a full SQL query or dump, tuple data is generated one by one and cached using a memory management component that supports disk overflow, so that memory usage is controlled within a preset threshold. Upon return, the SRF context of the data is read from the memory management component. When resetting the unique SQL storage structure, memory is completely reclaimed by destroying and rebuilding the entire memory context.
[0011] S2, IN Clause Normalization Processing: During SQL normalization, the array operation expression structure transformed from the IN clause is identified, and the entire constant list is replaced with a preset placeholder, so that similar SQL statements with only different numbers of constant members are normalized to the same identifier.
[0012] S3. Snapshot data table storage optimization: Configure the diagnostic snapshot data table to a no-logging mode and establish a range-based partitioning mechanism based on the status snapshot identifier. New snapshot data will automatically fall into the corresponding partition. When cleaning up expired snapshots, use partition cascading operations to directly clear or remove the target partition.
[0013] Furthermore, in the method of this application, step S1 also includes: independently allocating a memory block matching its length for a unique SQL statement, and storing the address pointer of the memory block in a fixed-length metadata structure to achieve physical storage decoupling and eliminate memory waste caused by continuous reserved space.
[0014] Furthermore, in the method of this application, when the memory usage exceeds a preset threshold, the memory management component that supports disk overflow in step S1 automatically transfers part of the data to a temporary disk file, controls the peak memory within the working memory range, and maintains the uniformity of the data reading interface.
[0015] Furthermore, in the method of this application, the IN clause normalization processing step identifies the array operation expression, locates its start and end positions in the original SQL text, and replaces the entire constant list area with a uniform placeholder.
[0016] Furthermore, in the method of this application, step S2 includes: checking that the expression is of type ScalarArrayOpExpr and the isInExpr flag is true, not recursively processing the list of child node constants, obtaining the start and end positions of the IN list in the original SQL through exprLocation, recording the replacement position and length, and replacing the entire list of constants string with a preset placeholder during the token replacement stage.
[0017] Furthermore, in the method of this application, the snapshot data table storage optimization step adopts an interval partitioning strategy, and when the new state snapshot identifier exceeds the range of existing partitions, a corresponding new partition is automatically created.
[0018] Furthermore, in the method of this application, step S3 includes:
[0019] (1) Data table storage type conversion: Modify the snapshot data table to a UNLOGGED partitioned table, using the snapshot identifier snapshot_id as the partition key, and adopting the interval partitioning method;
[0020] (2) Partition cascading operation replaces row-level deletion: When deleting expired snapshot data, use the TRUNCATE PARTITION or DROP PARTITION command instead of DELETE row by row;
[0021] (3) Report generation efficiency optimization: Record timestamps and calculate the time consumed in the key data processing stage of diagnostic report generation, and output them to the performance log.
[0022] Furthermore, in the method of this application, the partition cascading operation in step S3 is a partition clearing command or a partition deletion command, which is performed at the metadata level and does not generate a single deletion log.
[0023] A second aspect of this application provides a data management optimization system for a database performance diagnostic reporting system. The data management optimization system, when running, implements the steps of the aforementioned data management optimization method for a database performance diagnostic reporting system. The system includes:
[0024] The memory management module is used to independently allocate a fixed-length metadata area and a variable-length text storage area in memory for each unique SQL statement, and associate the two through pointers. When executing a full SQL query or dump, it generates tuple data one by one and uses a memory management component that supports disk overflow to cache the data, keeping memory usage within a preset threshold. Upon return, it reads the SRF context from the memory management component. When resetting the unique SQL storage structure, it achieves complete memory reclamation by destroying and rebuilding the entire memory context.
[0025] The normalization module is used to identify the array operation expression structure transformed from the IN clause during the SQL normalization process, replace the entire constant list with preset placeholders, and normalize similar SQL statements that differ only in the number of constant members to the same identifier.
[0026] The storage optimization module is used to configure the diagnostic snapshot data table to a no-logging mode and establish a range-based partitioning mechanism based on the status snapshot identifier. New snapshot data automatically falls into the corresponding partition. When cleaning up expired snapshots, the target partition is directly cleared or removed using partition cascading operations.
[0027] A third aspect of this application provides an electronic device, including: a memory and a processor;
[0028] Memory: Used to store computer programs;
[0029] Processor: Used to execute the computer program to implement the steps of the data management optimization method of the aforementioned database performance diagnostic report system.
[0030] A fourth aspect of this application provides a computer-readable storage medium having a computer program stored thereon, wherein when the computer program is executed by a processor, it implements the steps of the aforementioned data management optimization method for a database performance diagnostic report system.
[0031] In summary, compared with the prior art, the present invention has the following advantages:
[0032] (1) Significantly reduced memory usage: The separate storage architecture that decouples metadata and SQL text is adopted, combined with the enhanced IN clause normalization algorithm, which effectively compresses the memory storage scale of massive SQL statements; at the same time, it supports memory quota limit management through GUC parameters, improving resource controllability.
[0033] (2) Eliminate instantaneous memory peaks: Introduce the TupleStore indirect memory access mechanism to decouple the full SQL data dump operation from the direct loading of physical memory, avoid the risk of memory overflow (OOM) caused by instantaneous memory multiplication, and ensure system stability.
[0034] (3) Improved I / O efficiency and maintainability: The WDR snapshot table adopts the UNLOGGED attribute partition table structure and realizes batch cleanup of expired data based on TRUNCATE operation. Compared with the traditional DELETE method, it can significantly reduce the amount of write-ahead log (WAL) generation and I / O overhead, reduce table fragmentation rate, and simplify storage space maintenance.
[0035] (4) Enhanced observability of report generation: Performance probes are embedded in the critical path of report generation to achieve accurate collection of time consumption and resource consumption at each processing stage, providing quantitative data support for subsequent performance bottleneck identification and continuous optimization.
[0036] Other features and advantages of this application will be set forth in detail in the following description, or will become apparent through the implementation of the relevant technical solutions of this application. The objectives and other advantages of this application can be achieved through the technical features and means explicitly pointed out in the description, claims, and drawings, and will be obtained through the implementation of these technical contents. Attached Figure Description
[0037] To more clearly illustrate the technical solution of this application, the accompanying drawings involved in the description of this invention will be briefly introduced below. It should be noted that the drawings only show some embodiments of the invention. For those skilled in the art, other related drawings can be derived from these drawings without creative effort.
[0038] Figure 1 This is a flowchart illustrating the overall implementation process of the data management optimization method for the database performance diagnostic report system in this application.
[0039] Figure 2 This is a structural diagram of the data management optimization system for the database performance diagnostic report system in this application.
[0040] Figure 3 This is a schematic diagram of the structure of an electronic device provided in an embodiment of this application. Detailed Implementation
[0041] To make the objectives, technical solutions, and advantages of the embodiments of this application clearer, the technical solutions of the embodiments of this application will be clearly and completely described below with reference to the accompanying drawings. It should be noted that the described embodiments are only some embodiments of this application, and not all embodiments. All other embodiments obtained by those skilled in the art based on the embodiments of this application without creative effort are within the protection scope of this application.
[0042] In this document, the term "comprising" and any variations thereof (such as "including," "including," etc.) are open-ended expressions and should be understood as "including but not limited to," meaning that the listed content is not exhaustive and may include other content not explicitly mentioned. The term "based on" should be understood as "at least partially based on," meaning that the basis or condition referred to may not be the only factor and may involve other relevant factors. The term "one embodiment" should be understood as "at least one embodiment," meaning that the described embodiment is not the only possible implementation, and other similar embodiments may exist.
[0043] In this application, the terms "a" and "a plurality of" are used to modify related elements or features, and their expression is illustrative rather than restrictive. Unless otherwise expressly stated in the context, "a" should be understood as "at least one," and "a plurality of" should be understood as "at least two." Those skilled in the art should reasonably interpret these terms based on the semantic and logical relationships of the context to ensure that they cover the possibility of "one or more."
[0044] Example: A data management optimization method for a database performance diagnostic reporting system
[0045] I. Memory management and normalization methods for unique SQL statements in the WDR module, including the following steps:
[0046] (1) Separate storage of metadata and SQL text: When allocating memory for each unique SQL entry in the memory hash table, the fixed-length metadata structure (UniqueSQLEntry) and the variable-length SQL text string are separated and memory is allocated independently, thereby eliminating memory waste caused by reserving too much string space;
[0047] (2) Introduce a memory storage mechanism that can overflow the disk: When it is necessary to query or dump all unique SQL statements, the hash table content is not directly copied to the memory array. Instead, tuples are generated one by one, and a memory management component (TupleStore) that can automatically spill data to a temporary disk file according to memory pressure is used for caching, thereby controlling the peak memory usage within the preset working memory (work_mem) range;
[0048] (3) Achieve complete memory context reclamation: When resetting the unique SQL hash table, ensure that all allocated but unused memory space is completely returned to the operating system by directly destroying and rebuilding the memory context in which the entire hash table resides;
[0049] (4) Implement advanced normalization of IN clauses: Provide a configuration parameter to enable normalization of IN clauses. When enabled, during the SQL normalization process, the ScalarArrayOpExpr expression structure converted from the IN clause is identified, the position and length of its constant list are obtained, and the entire constant list (e.g., (1, 2, 3, 4, 5)) is uniformly replaced with a placeholder (e.g., ?), thereby normalizing SQL statements with different constant members and numbers of IN clauses into the same SQL statement.
[0050] II. Storage optimization methods for snapshot data tables in the WDR module, including the following steps:
[0051] (1) Data table storage type conversion: Modify the table structure that stores WDR snapshot data to a UNLOGGED table so that its data modification operations do not generate write-ahead logs (WAL / xlog), thereby reducing I / O overhead;
[0052] (2) Introduce a partition table mechanism: Transform the WDR snapshot data table into a partition table, use the snapshot identifier (snapshot_id) as the partition key, and adopt the interval partitioning method so that new snapshot data can automatically fall into the newly created partition;
[0053] (3) Use partition cascading operations instead of row-level deletion: When it is necessary to delete expired snapshot data, use the efficient TRUNCATE PARTITION or DROP PARTITION commands instead of the traditional row-by-row DELETE commands to achieve near-instantaneous metadata-level operations and avoid generating a large number of logs and dead tuples.
[0054] To more clearly illustrate the technical solution of this application, the following will provide further explanation through specific scenario embodiments.
[0055] I. Unique SQL Optimization Methods
[0056] (1) Memory allocation optimization: When a new SQL needs to be stored in the hash table of UniqueSQLContext, the system no longer allocates contiguous memory of sizeof(UniqueSQLEntry) + query_size. Instead, it first allocates memory of sizeof(UniqueSQLEntry) to store metadata. After the SQL text is normalized, it allocates memory of exactly the size of strlen(sql) + 1 according to its actual length to store the string, and stores the address pointer of the latter into the former.
[0057] (2) Optimization of the get_instr_unique_sql function: When this function is called, the system creates a TupleStore instance. Then, it iterates through the hash table, and for each SQL entry retrieved, generates a tuple on the spot and stores it in the TupleStore. If the memory occupied by the TupleStore exceeds work_mem, it will automatically write some data to a temporary disk file. The function eventually returns an SRF (Set-ReturningFunction) context that can read data from the TupleStore (whether in memory or on disk).
[0058] (3) Optimization of the reset_unique_sql function: When this function is called, the system directly destroys the entire memory context pointed to by the pointer to the hash table memory context in g_instance, then creates a new empty context and initializes a new hash table in it.
[0059] (4) IN clause normalization: When the instr_unique_sql_ignore_in_clause parameter is enabled, in the pgssJumble normalization logic:
[0060] a) Check if the expression is of type ScalarArrayOpExpr and check if its newly added isInExpr flag is true.
[0061] b) If true, its child nodes (constant list) are not processed recursively.
[0062] c) Use the exprLocation function to get the start and end positions of the IN list in the original SQL text.
[0063] d) Record a constant position that needs to be replaced, with its starting point being the beginning position of the list and its length being the calculated total length of the list.
[0064] e) In the subsequent token replacement phase, replace the entire string at that position (e.g., (1, 2, 3, 4, 5)) with (?).
[0065] II. WDR Data Table Optimization Methods
[0066] (1) Table structure change: In the database initialization or upgrade script, add the UNLOGGED and PARTITION BY RANGE (snapshot_id) clauses to the creation statements of target tables such as snapshot.snap_global_*.
[0067] (2) Insertion operation: Before inserting new snapshot data, the system checks whether the partition corresponding to the current snapshot_id exists. If it does not exist, the interval partitioning mechanism will automatically create a new partition. The INSERT statement does not need to be modified.
[0068] (3) Deletion operation: The cleanup program calculates the range of snapshot_ids to be deleted according to the set retention policy, and then executes the ALTER TABLE ... TRUNCATE PARTITION ... command to directly clear or delete the corresponding partition files.
[0069] (4) Report generation: In the generate_wdr_report function, the current timestamp is recorded at the beginning and end of each key data processing stage (such as get_report_data, load profile, sql statistics, etc.), and the time consumed is calculated. Finally, the time consumed in each stage is output through the NOTICE level log.
[0070] III. Database Performance Diagnostic Report System and Data Management Optimization System
[0071] Figure 2 The diagram shows the system's structure, which includes:
[0072] The memory management module is used to independently allocate a fixed-length metadata area and a variable-length text storage area in memory for each unique SQL statement, and associate the two through pointers. When executing a full SQL query or dump, it generates tuple data one by one and uses a memory management component that supports disk overflow to cache the data, keeping memory usage within a preset threshold. Upon return, it reads the SRF context from the memory management component. When resetting the unique SQL storage structure, it achieves complete memory reclamation by destroying and rebuilding the entire memory context.
[0073] The normalization module is used to identify the array operation expression structure transformed from the IN clause during the SQL normalization process, replace the entire constant list with preset placeholders, and normalize similar SQL statements that differ only in the number of constant members to the same identifier.
[0074] The storage optimization module is used to configure the diagnostic snapshot data table to a no-logging mode and establish a range-based partitioning mechanism based on the status snapshot identifier. New snapshot data automatically falls into the corresponding partition. When cleaning up expired snapshots, the target partition is directly cleared or removed using partition cascading operations.
[0075] The above system implements the data management optimization method of the database performance diagnostic report system disclosed in this application during runtime.
[0076] The flowcharts and block diagrams in the accompanying drawings illustrate possible implementations of systems, methods, and computer program products according to various embodiments of this application, including architecture, functionality, and operation. In these figures, each block may represent a module, program segment, or portion of code containing one or more executable instructions for implementing a specified logical function. Each block in the block diagrams and / or flowcharts, and combinations thereof, can be implemented using either a dedicated hardware-based system or a combination of dedicated hardware and computer instructions to achieve the specified function or operation.
[0077] like Figure 3 As shown in the illustration, an embodiment of this application also discloses an electronic device, including: a processor 310, a communication interface 320, a memory 330 for storing a processor-executable computer program, and a communication bus 340. The processor 310, communication interface 320, and memory 330 communicate with each other via the communication bus 340. The processor 310 executes the executable computer program to implement the steps of the data management optimization method of the database performance diagnostic report system described above.
[0078] It is understood that, in addition to memory and a processor, this electronic device may also include input devices (such as a keyboard), output devices (such as a display), and other communication modules. These input devices, output devices, and other communication modules all communicate with the processor through I / O interfaces (i.e., input / output interfaces).
[0079] The operations described in this application can be implemented by writing computer program code using one or more programming languages or a combination thereof. The programming languages include, but are not limited to, the following types:
[0080] Object-oriented programming languages, such as Java, Smalltalk, C++, etc.
[0081] Conventional procedural programming languages, such as "C" or similar programming languages.
[0082] The execution methods of program code include, but are not limited to:
[0083] It runs entirely on the user's computer;
[0084] Part of it executes on the user's computer, and part of it executes on a remote computer;
[0085] Execute as a standalone software package;
[0086] It is executed entirely on a remote computer or server.
[0087] In scenarios involving remote computers, the remote computer can connect to the user's computer via any type of network, including but not limited to local area networks (LANs) or wide area networks (WANs). Furthermore, the remote computer can also connect to external computers through an internet service provider, for example, by utilizing the internet for connection.
[0088] Furthermore, this application also discloses a computer-readable storage medium, wherein when the instructions in the computer-readable storage medium are executed by a processor of an electronic device, the electronic device is able to perform the various steps of the data management optimization method of the database performance diagnostic report system disclosed in this application.
[0089] In the context of this application, a computer-readable storage medium refers to a tangible medium capable of storing computer program code and related data. Specific examples include, but are not limited to, the following:
[0090] (1) Portable computer disk: such as floppy disks and other removable magnetic storage media.
[0091] (2) Hard disk: including mechanical hard disks and solid-state hard disks and other fixed storage devices.
[0092] (3) Random Access Memory (RAM): A volatile storage medium used for temporary storage of data and program code.
[0093] (4) Read-only memory (ROM): a non-volatile storage medium used to store fixed programs and data.
[0094] (5) Erasable programmable read-only memory (EPROM) or flash memory: non-volatile storage media that supports multiple erasures and reprogrammings.
[0095] (6) Fiber optic storage devices: storage media based on fiber optic technology.
[0096] (7) Portable compact disc read-only memory (CD-ROM): a read-only medium that stores data in the form of an optical disc.
[0097] (8) Optical storage devices: such as DVDs, Blu-ray discs and other storage media based on optical principles.
[0098] (9) Magnetic storage devices: such as magnetic tapes, disks and other storage media based on magnetic principles.
[0099] (10) Any suitable combination of the above: for example, combining multiple storage media to meet different storage needs.
[0100] These computer-readable storage media can be used to store the program code and related data described in this application to support program execution and persistent data storage.
[0101] Specifically, according to embodiments of this application, the processes described in the flowcharts can be implemented as computer software programs. For example, embodiments of this application relate to a computer program product comprising a computer program carried on a non-transitory computer-readable medium. This computer program includes program code for executing the data management optimization method of the database performance diagnostic reporting system disclosed in this application. When this computer program is executed by a processing system, it can achieve the functions defined in the embodiments of this application.
[0102] While the foregoing discussion contains several specific implementation details, these details should not be construed as limiting the scope of this application. The above description is merely a preferred embodiment of this application and an explanation of the technical principles employed. Those skilled in the art should understand that the scope of this application is not limited to technical solutions formed by specific combinations of the above-described technical features. Furthermore, this application should also cover other technical solutions formed by any combination of the above-described technical features or their equivalents without departing from the foregoing disclosed concept.
[0103] Those skilled in the art should also understand that modifications can be made to the technical solutions described in the foregoing embodiments, or equivalent substitutions can be made to some of the technical features, without departing from the spirit and scope of the technical solutions of the embodiments of this application. These modifications or substitutions will not cause the essence of the corresponding technical solutions to deviate from the core spirit and scope of the technical solutions of the embodiments of this application.
Claims
1. A data management optimization method for a database performance diagnostic reporting system, characterized in that, The database performance diagnostic reporting system is a WDR system, and the method includes: S1. Unique SQL Statement Memory Management: Each unique SQL statement is allocated a fixed-length metadata area and a variable-length text storage area in memory, and the two are associated through pointers. When executing a full SQL query or dump, tuple data is generated one by one and cached using a memory management component that supports disk overflow, so that memory usage is controlled within a preset threshold. Upon return, the SRF context of the data is read from the memory management component. When resetting the unique SQL storage structure, memory is completely reclaimed by destroying and rebuilding the entire memory context. S2, IN Clause Normalization Processing: During SQL normalization, the array operation expression structure transformed from the IN clause is identified, and the entire constant list is replaced with a preset placeholder, so that similar SQL statements with only different numbers of constant members are normalized to the same identifier. S3. Snapshot data table storage optimization: Configure the diagnostic snapshot data table to a no-logging mode and establish a range-based partitioning mechanism based on the status snapshot identifier. New snapshot data will automatically fall into the corresponding partition. When cleaning up expired snapshots, use partition cascading operations to directly clear or remove the target partition.
2. The method according to claim 1, characterized in that, Step S1 also includes: allocating a memory block that matches the length of the unique SQL statement independently, and storing the address pointer of the memory block in a fixed-length metadata structure to achieve physical storage decoupling and eliminate memory waste caused by continuous reserved space.
3. The method according to claim 1, characterized in that, When the memory usage exceeds a preset threshold, the memory management component that supports disk overflow described in step S1 automatically transfers some data to a temporary disk file to control the peak memory within the working memory range and maintain the consistency of the data reading interface.
4. The method according to claim 1, characterized in that, The IN clause normalization process identifies the array operation expression, locates its start and end positions in the original SQL text, and replaces the entire constant list area with a uniform placeholder.
5. The method according to claim 1, characterized in that, Step S2 includes: checking if the expression is of type ScalarArrayOpExpr and the isInExpr flag is true, not recursively processing the list of child node constants, obtaining the start and end positions of the IN list in the original SQL through exprLocation, recording the replacement position and length, and replacing the entire list of constants with preset placeholders during the token replacement phase.
6. The method according to claim 1, characterized in that, The snapshot data table storage optimization step adopts an interval partitioning strategy. When the new state snapshot identifier exceeds the range of existing partitions, a corresponding new partition is automatically created.
7. The method according to claim 1, characterized in that, Step S3 includes: (1) Data table storage type conversion: Modify the snapshot data table to a UNLOGGED partitioned table, using the snapshot identifier snapshot_id as the partition key, and adopting the interval partitioning method; (2) Partition cascading operation replaces row-level deletion: When deleting expired snapshot data, use the TRUNCATE PARTITION or DROPPARTITION command instead of DELETE row by row; (3) Report generation efficiency optimization: Record timestamps and calculate the time consumed in the key data processing stage of diagnostic report generation, and output them to the performance log.
8. The method according to claim 1, characterized in that, The partition cascading operation described in step S3 is a partition clearing command or a partition deletion command. This operation is performed at the metadata level and does not generate a single deletion log.
9. A data management and optimization system for a database performance diagnostic reporting system, characterized in that, The database performance diagnostic reporting system is a WDR system. When the data management optimization system runs, it implements the steps of the data management optimization method for the database performance diagnostic reporting system as described in any one of claims 1-8. The data management optimization system includes: The memory management module is used to independently allocate a fixed-length metadata area and a variable-length text storage area in memory for each unique SQL statement, and associate the two through pointers. When executing a full SQL query or dump, it generates tuple data one by one and uses a memory management component that supports disk overflow to cache the data, keeping memory usage within a preset threshold. Upon return, it reads the SRF context from the memory management component. When resetting the unique SQL storage structure, it achieves complete memory reclamation by destroying and rebuilding the entire memory context. The normalization module is used to identify the array operation expression structure transformed from the IN clause during the SQL normalization process, replace the entire constant list with preset placeholders, and normalize similar SQL statements that differ only in the number of constant members to the same identifier. The storage optimization module is used to configure the diagnostic snapshot data table to a no-logging mode and establish a range-based partitioning mechanism based on the status snapshot identifier. New snapshot data automatically falls into the corresponding partition. When cleaning up expired snapshots, the target partition is directly cleared or removed using partition cascading operations.
10. An electronic device, characterized in that, include: Memory and processor; Memory: Used to store computer programs; Processor: Used to execute the computer program to implement the steps of the data management optimization method of the database performance diagnostic report system as described in any one of claims 1-8.