Database memory context statistics optimization method and related product

Abstract

The invention provides a database memory context statistics optimization method and related products. The optimization method for the database memory context statistics comprises the following steps: carrying out merging processing of repeated leaf nodes on statistical data of a memory context so as to reduce storage occupation of the statistical data; and shared memory storage and access control are performed on the merged statistical data by adopting a partition lock mechanism, so that resource competition under high concurrency is reduced, and the influence of statistical operation on database performance is optimized. According to the scheme, the collection data volume of memory context statistics can be reduced, and storage occupation expansion caused by memory context node increase is avoided; the performance influence of memory context statistics can be reduced, and the situation that the statistics becomes the bottleneck of blocking the database query efficiency under high concurrency is avoided.

Description

Technical Field

[0001] This invention relates to the field of database technology, and in particular to an optimization method and related products for database memory context statistics. Background Technology

[0002] In a database, a memory context is a logical structure used to manage memory allocation and deallocation. It provides an efficient and secure memory management mechanism for each database process. It allocates a tree-like local memory environment to each process, and the memory environments of each process are isolated from each other. When a process is handling different tasks, the database switches to different sub-environments of the tree-like memory for memory usage.

[0003] Databases contain various statistical data, typically used to track the status of different database activities to understand performance metrics. The database also employs a statistical mechanism for memory context. This mechanism collects the memory context of each foreground and background process and saves it to shared memory. It provides a Structured Query Language (SQL) interface for statistical queries, allowing database administrators (DBAs) to analyze the specific memory usage of all processes within the current database instance and resolve abnormal memory issues.

[0004] However, the current memory context statistics function has significant storage and performance issues. Specifically, this function collects the entire memory context of the foreground process and saves it to shared memory every time an SQL query is executed, and it also collects the entire memory context of the background process and saves it to shared memory at the end of each loop. As the database instance continues to run business operations, the number of memory context nodes for each process will increase. With the increasing amount of collected data, storage consumption will also increase significantly.

[0005] On the other hand, when a database faces high-concurrency business loads, multiple client processes may simultaneously access the same shared memory to update their own memory context data. In this situation, each process competes for a lock on the shared memory to ensure mutual exclusion and prevent simultaneous data modification or dirty reads. However, multiple processes competing for the same lock under high concurrency can lead to severe performance issues, thereby blocking business execution. Summary of the Invention

[0006] One object of the present invention is to provide an optimization method for database memory context statistics that can solve any of the above problems.

[0007] A further objective of this invention is to reduce the amount of data collected for memory context statistics, thereby avoiding storage occupancy expansion caused by an increase in the number of memory context nodes.

[0008] Another further objective of this invention is to reduce the performance impact of memory context statistics and prevent these statistics from becoming a bottleneck that blocks database query efficiency under high concurrency.

[0009] In particular, the present invention provides an optimization method for database memory context statistics, wherein the memory context is a tree structure. The method includes: merging duplicate leaf nodes of the statistical data of the memory context to reduce the storage occupation of the statistical data; and using a partition lock mechanism to perform shared memory storage and access control on the merged statistical data to reduce resource contention under high concurrency and optimize the impact of statistical operations on database performance.

[0010] Optionally, the step of merging duplicate leaf nodes in the statistical data of the memory context includes: identifying duplicate leaf nodes that are sibling nodes in the tree-structured memory context, where duplicate leaf nodes must meet the conditions of having the same node name, the same level, and the same parent node; and merging the statistical data of the duplicate leaf nodes into a single record, keeping the node name, level, and external identifier of the corresponding process unchanged in the record, while accumulating the values ​​of statistical fields related to memory usage in the record.

[0011] Optionally, the statistical fields related to memory usage include: number of blocks, total space, and free space; and the merged single record is marked with the total number of corresponding duplicate leaf nodes.

[0012] Optionally, the steps of using a partition lock mechanism to perform shared memory storage and access control on the merged statistical data include: logically dividing the shared memory array storing the statistical data into multiple partitions, configuring an independent lock for each partition; and determining the target partition and target lock of the statistical data corresponding to the process based on the internal identifier of the process in the database, thereby realizing the association mapping between the process and the partition.

[0013] Optionally, the steps of determining the target partition and target lock of the statistical data corresponding to the process based on the internal identifier of the process in the database include: determining the target partition and target lock of the statistical data corresponding to the process by taking the modulo of the internal identifier with respect to the total number of partitions.

[0014] Optionally, after determining the target partition and target lock of the statistical data corresponding to the process based on the internal identifier of the process in the database, the method further includes: adding an exclusive lock to the determined target partition; traversing the tree-structured memory context of the current process in a depth-first manner, writing the statistical data of the current process's merged memory context into the shared memory array corresponding to the target partition in traversal order; and releasing the exclusive lock of the target partition after completing the writing of the statistical data.

[0015] Optionally, after the step of logically dividing the shared memory array storing statistical data into multiple partitions and configuring an independent lock for each partition, the method further includes: acquiring shared locks on all partitions in ascending order of the lock number corresponding to the partition; reading the statistical data in the shared memory array corresponding to all partitions; and releasing the shared locks on all partitions in reverse order of the lock number after the data reading is completed.

[0016] According to another aspect of the present invention, a computer device is also provided, including a memory, a processor, and a computer program stored in the memory and running on the processor, wherein the processor executes the computer program to implement the steps of the optimization method for database memory context statistics as described above.

[0017] According to another aspect of the present invention, a computer-readable storage medium is also provided, on which a computer program is stored, wherein the computer program, when executed by a processor, implements the steps of the database memory context statistics optimization method described above.

[0018] According to another aspect of the present invention, a computer program product is also provided, comprising a computer program, characterized in that, when the computer program is executed by a processor, it implements the steps of the optimization method for database memory context statistics described above.

[0019] The optimization method for database memory context statistics of the present invention merges duplicate leaf nodes of memory context statistics to reduce the storage footprint of statistical data, thereby reducing the amount of data collected for memory context statistics and avoiding storage expansion caused by an increase in the number of memory context nodes.

[0020] Furthermore, the database memory context statistics optimization method of the present invention adopts a partition lock mechanism to perform shared memory storage and access control on the merged statistical data, thereby reducing resource contention under high concurrency, optimizing the impact of statistical operations on database performance, reducing the performance impact of memory context statistics, and preventing the statistics from becoming a bottleneck that blocks database query efficiency under high concurrency.

[0021] The above and other objects, advantages and features of the present invention will become more apparent to those skilled in the art from the following detailed description of specific embodiments of the invention in conjunction with the accompanying drawings. Attached Figure Description

[0022] The following sections will describe some specific embodiments of the invention in detail by way of example and not limitation, with reference to the accompanying drawings. The same reference numerals in the drawings denote the same or similar parts or portions. Those skilled in the art should understand that these drawings are not necessarily drawn to scale. In the drawings: Figure 1 This is a schematic flowchart of an optimization method for database memory context statistics according to an embodiment of the present invention; Figure 2 This is a partial structural diagram of the memory context in an optimization method for database memory context statistics according to an embodiment of the present invention; Figure 3 This is a schematic diagram of the partition lock mechanism in an optimization method for database memory context statistics according to an embodiment of the present invention; Figure 4 This is a schematic diagram of a computer program product according to an embodiment of the present invention; Figure 5 This is a schematic diagram of a computer-readable storage medium according to an embodiment of the present invention; and Figure 6 This is a schematic diagram of a computer device according to an embodiment of the present invention. Detailed Implementation

[0023] Those skilled in the art should understand that the embodiments described below are merely a part of the embodiments of the present invention, and not all of the embodiments of the present invention. These partial embodiments are intended to explain the technical principles of the present invention and are not intended to limit the scope of protection of the present invention. Based on the embodiments provided by the present invention, all other embodiments obtained by those skilled in the art without creative effort should still fall within the scope of protection of the present invention.

[0024] It should be noted that the logic and / or steps represented in the flowchart or otherwise described herein, for example, can be considered as a sequenced list of executable instructions for implementing logical functions, and can be specifically implemented in any computer-readable medium for use by, or in conjunction with, an instruction execution system, apparatus or device (such as a computer-based system, a processor-included system or other system that can fetch and execute instructions from, an instruction execution system, apparatus or device).

[0025] The optimized database memory context statistics method in this embodiment can reduce the amount of data collected for memory context statistics, avoid the storage expansion caused by the increase of memory context nodes, and prevent the statistics from becoming a bottleneck that blocks the efficiency of database queries under high concurrency. Figure 1This is a schematic flowchart of an optimization method for database memory context statistics according to an embodiment of the present invention, such as... Figure 1 As shown, optimization methods for database memory context statistics generally include the following steps: Step S102: Merge duplicate leaf nodes of the statistical data in the memory context to reduce the storage footprint of the statistical data.

[0026] Step S104: A partition lock mechanism is used to store and control the merged statistical data in shared memory, thereby reducing resource contention under high concurrency and optimizing the impact of statistical operations on database performance.

[0027] It should be noted that the memory context in this embodiment is a tree structure. Figure 2 This is a partial structural diagram of the memory context in an optimization method for database memory context statistics according to an embodiment of the present invention. Figure 2 This diagram illustrates a portion of the tree-like memory context structure of a specific client process within a database at a given moment. The memory context mechanism simplifies memory allocation and deallocation by classifying and hierarchically managing memory, thus reducing the risk of memory leaks.

[0028] Specifically, step S102, which merges duplicate leaf nodes in the memory context statistics, may include: identifying duplicate leaf nodes that are sibling nodes in the tree-structured memory context, where duplicate leaf nodes must have the same name, the same level, and the same parent node; and merging the statistics of the duplicate leaf nodes into a single record, keeping the node name, level, and corresponding process's external identifier unchanged, while accumulating the values ​​of memory usage-related statistical fields in the record. These memory usage-related statistical fields include: number of blocks (nblocks), total space (totalspace), and free space (freespace). Furthermore, the merged single record is labeled with the total number of corresponding duplicate leaf nodes.

[0029] As mentioned earlier, the current memory context statistics function has significant storage issues. Specifically, this function collects the entire memory context of the foreground process and saves it to shared memory every time an SQL query is executed, and it also collects the entire memory context of the background process and saves it to shared memory at the end of each loop. As the database instance continuously runs business operations, the number of memory context nodes for each process increases significantly. For example, in the query example shown in Table 1, a single process already has approximately 9,000 memory context nodes. With the increasing amount of collected data, storage consumption becomes increasingly large.

[0030] Table 1 Using the query example in Table 1 to analyze the tree-structured memory context of a single process, we can find that there are many duplicate nodes, such as PL / pgSQL function, dynahash, SPI Plan, CachedPlan, etc. These nodes are leaf nodes without child nodes, and some duplicate nodes are sibling nodes with the same name and level, and the same parent node.

[0031] Since these duplicate nodes have the same name and levels, they do not provide any additional information for memory diagnostics. This embodiment merges these duplicate nodes, combining multiple identical node records into one. After merging, the name, levels, and the corresponding process's external identifier (PID) remain unchanged; only the values ​​of the nblocks, totalspace, and freespace fields need to be added. Merging these sibling leaf nodes reduces the data volume without affecting the tree structure of the memory context.

[0032] Table 2 It should be noted that in the query results shown in Table 2, the numbers in parentheses after the memory context name indicate the number of times the node is repeated, for example, "dynahash(27)". In the memory context statistics example after the above merging process, we can see that only more than 4,000 nodes remain, and memory usage is reduced by about 50%.

[0033] Step S104 employs a partitioned locking mechanism to perform shared memory storage and access control on the merged statistical data. Specifically, this may include: logically dividing the shared memory array storing the statistical data into multiple partitions, each partition configured with an independent lock; and determining the target partition and target lock for the statistical data corresponding to a process based on the process's internal identifier in the database, thus achieving a mapping between processes and partitions. The step of determining the target partition and target lock for the statistical data corresponding to a process based on the process's internal identifier may specifically include: determining the target partition and target lock for the statistical data corresponding to a process by taking the modulo of the internal identifier with the total number of partitions.

[0034] Specifically, after determining the target partition and target lock for the statistical data corresponding to the process based on the internal identifier of the process in the database, the process further includes: acquiring an exclusive lock on the determined target partition; traversing the tree-structured memory context of the current process in a depth-first manner, writing the statistical data of the current process's merged memory context into the shared memory array corresponding to the target partition in traversal order; and releasing the exclusive lock on the target partition after completing the writing of the statistical data. In fact, this can be seen as the statistical data update process.

[0035] After logically dividing the shared memory array storing statistical data into multiple partitions and configuring an independent lock for each partition, the process includes: acquiring shared locks on all partitions in ascending order of their corresponding lock numbers; reading the statistical data from the shared memory arrays of all partitions; and releasing the shared locks on all partitions in reverse order of their lock numbers after the data reading is complete. In essence, this can be viewed as the process of reading statistical data.

[0036] Specifically, in memory context statistics, the statistical data for each database process is stored in a shared memory array. Furthermore, for the KES database, the internal identifier procno is used to associate the array number corresponding to different processes. Figure 3 This is a schematic diagram of the partition lock mechanism in an optimization method for database memory context statistics according to an embodiment of the present invention. In this embodiment, the shared memory array storing statistical data can be logically divided into 128 partitions. Each partition corresponds to multiple arrays, that is, to the statistical data of multiple processes, and an independent lock is allocated to each partition, resulting in a total of 128 locks. Figure 3 As shown, the internal identifier procno of the process is used to represent the array number corresponding to the process. Now, the value obtained by taking the remainder of procno with respect to 128 is also used to represent the partition number corresponding to the process, which is also the partition lock number.

[0037] After implementing the partition lock in this embodiment, when the process updates the memory context and finds the corresponding shared memory array, it first calculates the partition number based on the process's internal identifier procno, then acquires an exclusive lock on the corresponding target partition based on the partition number, and updates the data in the current shared memory array. Specifically, it traverses each node in the current process's memory context in a depth-first manner, then saves each node sequentially into the current array, and finally releases the exclusive lock on the target partition.

[0038] When reading memory context statistics, since it involves reading the memory context data of all processes, that is, reading all arrays in shared memory, the process first acquires shared locks on all 128 partitions in ascending order, locking all arrays in all partitions. Then, it reads the contents of each array one by one. Finally, it releases the shared locks on all 128 partitions in descending order. It's important to note that when processing all partition locks simultaneously, locking must be done in ascending order of lock number, and unlocking must be done in descending order of lock number. This order of operations is to avoid deadlock.

[0039] In this embodiment, the amount of statistical data is reduced by merging sibling leaf nodes, thereby reducing storage usage without affecting statistical accuracy. Partition locks are used to optimize the control logic for concurrent access, disperse lock conflicts in concurrent access to statistical data, effectively avoid the blocking of database query business by the memory context statistics themselves, and improve the overall efficiency of the database instance.

[0040] The flowcharts provided in the above embodiments are not intended to indicate that the operations of the method will be performed in any particular order, or that all operations of the method are included in all every case. Furthermore, the method may include additional operations. Within the scope of the technical concept provided by the methods in the above embodiments, additional variations can be made to the above methods.

[0041] This embodiment also provides a computer program product, a computer-readable storage medium, and a computer device. Figure 4 This is a schematic diagram of a computer program product 500 according to an embodiment of the present invention. Figure 5 This is a schematic diagram of a computer-readable storage medium 300 according to an embodiment of the present invention. Figure 6 This is a schematic diagram of a computer device 400 according to an embodiment of the present invention.

[0042] Computer program product 500 includes computer program 310, which, when executed by processor 410, implements the steps of the database memory context statistics optimization method described above. Computer-readable storage medium 300 stores the aforementioned computer program 310, which, when executed by processor 410, implements the steps of the database memory context statistics optimization method described above. Computer device 400 may include memory 420, processor 410, and computer program 310 stored in memory 420 and running on processor 410.

[0043] The computer program 310 used to perform the operations of the present invention may be assembly instructions, instruction set architecture (ISA) instructions, machine instructions, machine-related instructions, microcode, firmware instructions, status setting data, integrated circuit configuration data, or source code or object code written in any combination of one or more programming languages ​​and procedural programming languages.

[0044] Computer program 310 may execute entirely on the user's computer, partially on the user's computer, as a standalone software package, partially on the user's computer and partially on a remote computer, or entirely on a remote computer or server. In the latter case, the remote computer may be connected to the user's computer via any type of network, including a local area network (LAN) or a wide area network (WAN), or may be connected to an external computer (e.g., via the Internet using an Internet service provider).

[0045] In some embodiments, in order to perform aspects of the present invention, electronic circuits including, for example, programmable logic circuits, field-programmable gate arrays (FPGAs) or programmable logic arrays (PLAs) can execute computer-readable program instructions to personalize the electronic circuits by utilizing state information of computer-readable program instructions.

[0046] For the purposes of this embodiment, computer program product 500 is a related product containing computer program 310. For the purposes of this embodiment, computer-readable storage medium 300 is a tangible device capable of holding and storing computer program 310, and can be any device capable of containing, storing, communicating, propagating or transmitting computer program 310 for use by or in conjunction with an instruction execution system, apparatus or device.

[0047] More specific examples (a non-exhaustive list) of computer-readable storage media 300 include the following: portable computer disks, hard disks, random access memory (RAM), read-only memory (ROM), erasable programmable read-only memory (EPROM or flash memory), static random access memory (SRAM), portable optical disc read-only memory (CD-ROM), digital multifunction disc (DVD), memory sticks, floppy disks, mechanical encoding devices, and any suitable combination of the foregoing.

[0048] Computer device 400 may include memory 420, processor 410, and computer program 310 stored on memory 420 and running on processor 410, wherein processor 410 executes computer program 310 to implement the steps of the database memory context statistics optimization method of any of the above embodiments.

[0049] It should be noted that the logic and / or steps represented in the flowchart or otherwise described herein, for example, can be considered as a sequenced list of executable instructions for implementing logical functions, and can be specifically implemented in any machine-readable storage medium for use by, or in conjunction with, instruction execution systems, apparatuses or devices (such as computer-based systems, processor-based systems or other systems that can fetch and execute instructions from, or instruction execution systems, apparatuses or devices).

[0050] It should be understood that various parts of the present invention can be implemented using hardware, software, firmware, or a combination thereof. In the above embodiments, multiple steps or methods can be implemented using software or firmware stored in memory and executed by a suitable instruction execution system.

[0051] Computer device 400 can be, for example, a server, desktop computer, laptop computer, tablet computer, or smartphone. In some examples, computer device 400 can be a cloud computing node. Computer device 400 can be described in the general context of computer system executable instructions (such as program modules) executed by a computer system. Typically, program modules can include routines, programs, object programs, components, logic, data structures, etc., that perform specific tasks or implement specific abstract data types. Computer device 400 can be implemented in a distributed cloud computing environment where tasks are performed by remote processing devices linked through a communication network. In a distributed cloud computing environment, program modules can reside on local or remote computing system storage media, including storage devices.

[0052] Computer device 400 may include a processor 410 adapted to execute stored instructions and a memory 420 that provides temporary storage space for the operation of instructions during operation. The processor 410 may be a single-core processor, a multi-core processor, a computing cluster, or any other configuration. The memory 420 may include random access memory (RAM), read-only memory, flash memory, or any other suitable storage system.

[0053] The processor 410 can be connected via a system interconnect (e.g., PCI, PCI-Express, etc.) to an I / O interface (input / output interface) suitable for connecting the computer device 400 to one or more I / O devices (input / output devices). I / O devices may include, for example, a keyboard and indicating devices, where indicating devices may include a touchpad or touchscreen, etc. I / O devices may be built into the computer device 400 or may be external devices connected to the computing device.

[0054] The processor 410 may also be linked via a system interconnect to a display interface suitable for connecting the computer device 400 to a display device. The display device may include a display screen as a built-in component of the computer device 400. The display device may also include an external computer monitor, television, or projector connected to the computer device 400. Furthermore, a network interface controller (NIC) may be adapted to connect the computer device 400 to a network via a system interconnect. In some embodiments, the NIC may use any suitable interface or protocol (such as an Internet Minicomputer System Interface) to transmit data. The network may be a cellular network, a radio network, a wide area network (WAN), a local area network (LAN), or the Internet, etc. Remote devices may connect to the computing device via the network.

[0055] Therefore, those skilled in the art should recognize that although numerous exemplary embodiments of the present invention have been shown and described in detail herein, many other variations or modifications conforming to the principles of the present invention can be directly determined or derived from the disclosure of the present invention without departing from the spirit and scope of the invention. Thus, the scope of the present invention should be understood and construed as covering all such other variations or modifications.

Claims

1. An optimization method for database memory context statistics, wherein the memory context is a tree structure, the method comprising: The statistical data of the memory context is processed by merging duplicate leaf nodes to reduce the storage footprint of the statistical data; as well as A partition lock mechanism is used to store and control the merged statistical data in shared memory, reducing resource contention under high concurrency and optimizing the impact of statistical operations on the database performance.

2. The method according to claim 1, wherein the step of merging duplicate leaf nodes in the statistical data of the memory context includes: Identify the duplicate leaf nodes that are sibling nodes in the memory context of the tree structure. The duplicate leaf nodes must satisfy the conditions of having the same node name, the same level, and the same parent node. as well as The statistical data of the duplicate leaf nodes are merged into a single record, while keeping the node name, level and external identifier of the corresponding process unchanged. At the same time, the values ​​of the statistical fields related to memory usage in the record are accumulated.

3. The method according to claim 2, wherein, The memory usage-related statistical fields include: number of blocks, total space, and free space; and The merged single record is marked with the total number of the corresponding duplicate leaf nodes.

4. The method according to claim 1, wherein the step of using a partition lock mechanism to perform shared memory storage and access control on the merged statistical data includes: The shared memory array storing the statistical data is logically divided into multiple partitions, and each partition is configured with an independent lock; as well as Based on the internal identifier of the process in the database, the target partition and target lock of the statistical data corresponding to the process are determined, thereby realizing the association mapping between the process and the partition.

5. The method according to claim 4, wherein the step of determining the target partition and target lock of the statistical data corresponding to the process based on the internal identifier of the process in the database includes: The target partition and target lock of the statistical data corresponding to the process are determined by taking the remainder of the total number of partitions using the internal identifier.

6. The method of claim 4, further comprising, after the step of determining the target partition and target lock of the statistical data corresponding to the process based on the internal identifier of the process in the database: Add an exclusive lock to the identified target partition; The memory context of the current process is traversed in a depth-first manner, and the statistical data of the memory context of the current process after merging is written into the shared memory array corresponding to the target partition in traversal order. as well as After the statistical data has been written, the exclusive lock on the target partition is released.

7. The method of claim 4, further comprising, after the step of logically dividing the shared memory array storing the statistical data into multiple partitions, each partition being configured with an independent lock: All partitions are assigned a shared lock in ascending order of their corresponding lock numbers. Read the statistical data from the shared memory array corresponding to all the partitions; as well as After the data reading is completed, release the shared locks of all partitions in reverse order of the lock sequence number.

8. A computer device comprising a memory, a processor, and a computer program stored in the memory and running on the processor, wherein the processor, when executing the computer program, implements the steps of the optimization method for database memory context statistics as described in any one of claims 1 to 7.

9. A computer-readable storage medium having a computer program stored thereon, the computer program, when executed by a processor, implementing the steps of the optimization method for database memory context statistics as described in any one of claims 1 to 7.

10. A computer program product, comprising a computer program, characterized in that, When the computer program is executed by a processor, it implements the steps of the optimization method for database memory context statistics as described in any one of claims 1 to 7.

Images