Management and tracking method and equipment for sql execution memory of database computing middleware

Abstract

The invention discloses a management and tracking method and device for an sql execution memory of database computing middleware, and belongs to the technical field of databases. According to the method, the corresponding tree structure body is constructed according to the execution process of the sql and used for recording the memory use condition of the whole execution process of the sql, memory use on the computing middleware can be effectively tracked in real time, and response measures are triggered when the memory exceeds the limit, so that efficient management of the memory of the computing middleware is achieved.

Description

Technical Field

[0001] The present invention belongs to the technical field of databases, and in particular relates to a method for tracking and managing memory usage on a computing middleware in a SQL execution process. Background Art

[0002] Due to different business needs, the business data of large enterprises are generally stored in database clusters composed of different database products. However, there are differences in the SQL supported by different database products. Therefore, in order to shield the SQL differences from the business end, computing middleware is often used. The middleware parses the SQL of different database products, generates an execution plan, and finally sends the execution task to the heterogeneous database at the back end. In order to automate operation and maintenance, enterprises often use containers to deploy computing middleware, which is more sensitive to the memory usage of computing middleware.

[0003] In the execution process of a SQL statement, for the computing middleware, there are two main steps that consume a lot of memory: the computing middleware receives data from the backend database; and then calculates the received data. For simple SQL statements, these two steps can be executed serially once to get the SQL result. For example, for a range query of a field in a table, you only need to find the corresponding data from the backend database first, and then merge the data on the computing middleware to generate the SQL result set. However, for complex SQL statements, the above two steps need to be executed alternately multiple times to get the final SQL result. For example, for SQL statements containing multi-table joint query, nested subqueries, etc., the computing middleware generally needs to calculate the intermediate temporary data based on the data returned by the backend database, and then send the task to the backend database for execution. Repeating this process multiple times can generate the final SQL result set.

[0004] In the SQL execution process, the computing middleware not only needs to continuously send and receive data and calculate data, but also needs to support SQL of different database products. This requires that the computing middleware, as a containerized component, needs to establish effective memory usage and management methods in the SQL execution process to analyze memory usage and whether there are memory leaks, so as to better optimize version performance.

[0005] When executing complex query SQL, the database's computing middleware occupies a large amount of memory. How to effectively track and manage the memory usage of the computing middleware is a technical difficulty that needs to be solved urgently. Summary of the invention

[0006] The purpose of the present invention is to provide an effective method for tracking and managing the memory usage of computing middleware in a SQL execution process.

[0007] To achieve the purpose of the present invention, the embodiment of the present application first provides a method for managing and tracking SQL execution memory of a database computing middleware, which is characterized by comprising: A tree structure is constructed according to the execution process of SQL. It has multiple structure objects. Each structure object corresponds to each SQL sub-process and is used to record the memory usage of each SQL sub-process. Each structure object has the same life cycle as its corresponding SQL sub-process. Maintain a global variable to store the structure objects corresponding to the top N sessions with the highest memory usage among all sessions, where N is configurable; When any sql subprocess increases or decreases memory usage, the memory usage recorded by the corresponding structure object and its parent node structure object is updated, and the global variable is updated; Check whether the memory usage recorded in the updated structure object exceeds the limit, and trigger multi-strategy over-limit processing when it exceeds the limit; The memory usage of the process is checked regularly. When the memory usage of the process exceeds the limit, the structure objects corresponding to the top N sessions with the highest memory usage are traversed until a session that is allowed to be killed is detected and killed.

[0008] To achieve the purpose of the present invention, an embodiment of the present application also provides an electronic device, including a processor and a memory, wherein the memory stores a program that can be run on the processor, and is characterized in that when the program is executed by the processor, the steps of the above-mentioned database computing middleware SQL execution memory management and tracking method are implemented.

[0009] Also, a computer-readable storage medium is provided, storing at least one program, characterized in that the at least one program can be executed by at least one processor to implement the steps of the above-mentioned method for managing and tracking the SQL execution memory of the database computing middleware.

[0010] In the face of complex, memory-consuming query SQL, the present invention provides a method for managing and tracking SQL execution memory of a database computing middleware. A corresponding tree structure is constructed according to the SQL execution process to record the memory usage of the entire SQL execution process. The memory usage on the computing middleware can be tracked effectively and in real time, and countermeasures are triggered when the memory exceeds the limit, so as to achieve efficient memory management of the computing middleware. BRIEF DESCRIPTION OF THE DRAWINGS

[0011] Figure 1 A flowchart of a method for managing and tracking SQL execution memory of a database computing middleware according to an embodiment of the present application; Figure 2 A schematic diagram of the corresponding relationship between the SQL process and MemHacker in the embodiment of the present application; Figure 3 This is a MemHacker branch flow chart when sql consumes memory in an embodiment of the present application; Figure 4 A sequence diagram of the MemHacker timing check of an embodiment of the present application; Figure 5 A schematic diagram of the MemHacker tree in the SQL execution process of an embodiment of the present application; Figure 6 This is a MemHacker object sequence diagram when sql consumes memory in an embodiment of the present application; Figure 7 This is a schematic diagram of the structure of an electronic device according to an embodiment of the present application. DETAILED DESCRIPTION

[0012] In order to make the technical solution of the present invention more clearly understood, the technical solution of the present invention is further described in detail below according to specific embodiments and in combination with the accompanying drawings. Obviously, the following embodiments are only part of the embodiments of the present invention and do not constitute a limitation on the technical solution of the present invention. For those of ordinary skill in the art, other embodiments can also be obtained according to the embodiments of the present application without paying creative labor.

[0013] It should be noted that the order of steps described in the method embodiments in the following embodiments does not constitute a limitation on the technical solution of the present invention. For ordinary technicians in this field, without paying any creative labor, they can also reasonably adjust the order of certain steps according to the embodiments of the present application. Example 1

[0014] This embodiment provides a method for managing and tracking the SQL execution memory of a database computing middleware, such as Figure 1 As shown, the following steps are included: Step S101, construct a tree structure according to the execution process of sql, the tree structure has multiple structure objects, each structure object corresponds to each sql sub-process, and is used to record the memory usage of each sql sub-process. Each structure object has the same life cycle as its corresponding sql sub-process.

[0015] In the execution process of sql, sql will be divided into multiple sql sub-processes for execution, and these sql sub-processes form a tree structure. In order to track the entire process of sql execution, this embodiment designs a structure MemHacker, and establishes a corresponding structure object MemHacker object with the same life cycle for each sql sub-process to record the memory usage of each sub-process. Therefore, the structure MemHacker is also a tree structure. Taking the sql statement: "select * from t1 left join t4 on t1.id=t4.aid order by t1.id;" as an example, the correspondence between the constructed structure MemHacker and the sql process is as follows: Figure 2 shown.

[0016] The MemHacker class designed in this embodiment is shown in Table 1 below:

[0017] In the MemHacker class structure, the ParentHacker property is a pointer to the parent MemHacker in the current MemHacker, which corresponds to the parent process of the current sql subprocess; the ChildrenHackers property is an array of pointers to all child MemHackers in the current MemHacker, which corresponds to all subprocesses of the current sql process; the ClientSessionID property is the unique identifier of the session in which the client connects to the database; the SqlLabel property is used to identify the category of the sql subprocess corresponding to the MemHacker. For example, when SQL is about to start executing, a MemHacker will be created to track the overall execution process of SQL. The corresponding SqlLabel is the type of the SQL statement (add, delete, modify, query, etc.). When SQL needs to generate a result set, a MemHacker will be created to track the memory usage of the result set. The corresponding SqlLabel is LabelOfRowChunks. The BytesLimit attribute is the current MemHacker object that records the upper limit of memory used in the SQL execution process. If the upper limit is exceeded, the overlimit processing thread MemExceedProcessor will be triggered. The BytesUsed attribute records the total memory usage of the current MemHacker object and its child objects, which represents the memory size used by the corresponding SQL process.

[0018] For the values ​​of BytesLimit and MemExceedProcessor of different types of sql sub-processes (the value of MemExceedProcessor indicates which limit-exceeding processing action is triggered when the memory exceeds the limit), users can use sql statements to set the global-level value (the value that applies to all sessions), the session-level value (the value that applies only to the current session), and the sql statement-level value (the value that applies only to the current sql statement). The sql statement-level value takes precedence over the session-level value, and the session-level value takes precedence over the global-level value.

[0019] Step S102: Maintain a global variable to store structure objects corresponding to the top N sessions with the highest memory usage among all sessions, where N is configurable.

[0020] The entire computing middleware process maintains a global variable MemHackerTopN of MemHacker pointer array type, which is used to store MemHacker objects corresponding to the top N sessions with the highest memory usage among all sessions.

[0021] Step S103: When any sql sub-process increases or decreases memory usage, the memory usage of the corresponding structure object and its parent node structure object is updated, and the global variable is updated.

[0022] Each MemHacker object must update MemHackerTopN when consuming memory.

[0023] Step S104: After any structure object is updated, check whether the memory usage recorded in it exceeds the limit. If it exceeds the limit, multi-strategy over-limit processing is triggered.

[0024] When the sql subprocess increases or decreases memory usage, the corresponding MemHacker object will update its own and the ancestor MemHacker's BytesUsed, and trigger the overlimit processing thread MemExceedProcessor when the corresponding MemHacker object exceeds the memory limit.

[0025] When the sql subprocess consumes memory x, MemHacker branches the execution process as follows Figure 3 As shown, including: (1) Check whether currHacker (current MemHacker) is SessionRootHacker (session root MemHacker). If it is SessionRootHacker, update MemHackerTopN according to the memory size used by currHacker; (2) The memory usage of currHacker plus x (a positive number indicates an increase in memory usage, and a negative number indicates a decrease in memory usage); (3) Determine whether the memory used by currHacker exceeds the limit. If so, execute currHacker's MemExceedProcessor (software action for memory limit excess); (4) Check whether currHacker is SessionRootHacker (the root MemHacker of the session). If it is SessionRootHacker, it means that all ancestor MemHackers have been updated and the update of MemHacker should be ended. If not, it means that the BytesUsed (memory consumption size) of the parent MemHacker needs to be updated upward, that is, currHacker = currHacker.ParentHacker, and then restart execution (1).

[0026] In this embodiment, MemExceedProcessor provides multiple strategies for over-limit processing software actions, each of which has a priority. Users can set MemExceedProcessor to one or more of the following software actions as needed. When multiple software actions are set, they will be executed one by one from low to high priority, and software actions of the same priority will be executed in the order configured by the user. The software actions provided by the present invention include: (1) Log on Exceed: Log the stack information and MemHacker information of the current session, and send an alarm to the monitoring component to facilitate troubleshooting and optimization. The priority is I. It is recommended to set this software action when you need to analyze the memory overflow in the SQL process or when the user's SQL is in the trial run stage.

[0027] (2) Write result set to disk WriteDiskOnExceed: Executes the task of writing result set to disk, reduces memory usage, and ensures business continuity. The priority is I, and it is only supported for SQL sub-processes that need to query full table data. For example, when using join to query multiple tables, you may get a result set that grows exponentially (the more tables you connect, the larger the result set). In this case, you need to write some intermediate result sets to disk first when the memory limit is exceeded.

[0028] (3) Execute garbage collection GCOnExceed: Execute global garbage collection first, and then continue to execute SQL. The priority is II. It is usually used together with LogOnExceed. It is recommended to use it for complex SQL (SQL that needs to frequently generate a large amount of temporary data). For example, when facing add, delete, and modify statements with complex query conditions, the middleware needs to frequently calculate a large amount of temporary data, and these temporary data need to be cleared in time.

[0029] (4) Hard interruption of SQL execution PanicOnExceed: Directly hard interrupt the thread executing SQL, that is, cancel the execution of SQL. The priority is III, and it is recommended for some SQLs that consume huge memory but do not affect the consistency of database data after the hard interruption. For example: use PanicOnExceed for SQL processes that may cause memory overflow in the database computing middleware, or use PanicOnExceed for statements with database transactions (database transactions have consistency protection for data).

[0030] Step S105: regularly detect the memory usage of the process. When the memory usage of the process exceeds the limit, traverse the structure objects corresponding to the top N sessions with the highest memory usage until a session that is allowed to be killed is detected and killed.

[0031] Specifically, the computing middleware starts a thread MemExceedThread, which periodically checks whether the actual memory usage at the process level exceeds the set upper limit (ComputeNodeMemoryLimit). If it exceeds the upper limit, MemExceedThread traverses MemHackerTopN until it checks and kills a meeting the conditions. The condition that the MemHackerTopN session can be killed is: the executed SQL contains a hint in the form of "kill session topN", which means that when MemExceedThread detects that the memory exceeds the limit, if the session where the SQL belongs to MemHackerTopN, the session can be killed. The sequence diagram of the thread MemExceedThread is shown in the figure below. Figure 4 shown.

[0032] This embodiment also provides a command line sql instruction: "show MemHacker on [sessionID | topN]", which is used to print the statistical information of a session-level or global top N MemHackers: displaying the current memory usage size, memory usage upper limit, and actions triggered when the memory exceeds the limit of all sub-MemHackers in a tree structure. Example 2

[0033] This embodiment takes the SQL query statement: "select * from t1 left join t4 on t1.id=t4.aidorder by t1.id;" as an example to further illustrate in detail the SQL execution memory management and tracking method of the database computing middleware proposed in the present invention.

[0034] For the sql: "select * from t1 left join t4 on t1.id=t4.aid order byt1.id;", corresponding to its decomposed execution process, construct a MemHacker tree with the same tree structure, such as Figure 5 As shown, the root node of the MemHacker tree is SessionRootHacker_403, which records the total memory currently consumed by the entire sql. The leaf node LabelOfRowChunks_13 records the memory occupied by the result of scanning table t1, and the leaf node LabelOfRowChunks_15 records the memory occupied by the result of scanning table t4. Figure 5 As shown in the figure, when the memory consumption of the leaf node LabelOfRowChunks_13 increases by x bytes, the increased x bytes will be fed back upward layer by layer along the path (LabelOfRowChunks_13==》LabelOfInnerTable_14==》LabelOfMergeJoin_15==》LabelOfSQLText_22==》SessionRootHacker_403), that is, the memory consumption of each node record on the path from the leaf node to the root node will increase by x.

[0035] like Figure 6 As shown in the figure, when the memory consumption of the child node MemHacker increases by x bytes (corresponding to step 1), in the process of feeding back the memory consumption to the parent node, if the memory exceeds the limit, the corresponding action is triggered (corresponding to step 3); until the memory consumption of SessionRootHacker is updated (corresponding to step 4), SessionRootHacker is updated to MemHackerTopN (corresponding to step 5). Example 3

[0036] This embodiment provides an electronic device, such as Figure 7 As shown, it includes a processor 31 and a memory 32 that is communicatively connected to the processor 31. The memory 32 stores a program that can be run on the processor 31, and when the program is executed by the processor 31, the steps of the method for managing and tracking the SQL execution memory of the database computing middleware provided in the above embodiment are implemented.

[0037] The above program can be written in one or more programming languages ​​or a combination thereof, including object-oriented programming languages ​​such as Java, Smalltalk, C++, and conventional procedural programming languages ​​such as "C" or similar programming languages. The program code can be executed entirely on the user's computer, partially on the user's computer, as a separate software package, partially on the user's computer and partially on a remote computer, or entirely on the remote computer or server. Example 4

[0038] This embodiment provides a computer-readable storage medium, which stores at least one program. The at least one program can be executed by at least one processor to implement the steps of the SQL execution memory management and tracking method of the database computing middleware provided in the above embodiment.

[0039] The above storage media include: U disk, mobile hard disk, read-only memory, random access memory, disk or optical disk and other media that can store program code and their combination. With the development of science and technology, the meaning of storage media may become more and more extensive and not limited to tangible media.

[0040] Those skilled in the art can understand that the above-mentioned embodiments are specific embodiments for implementing the present invention. In addition to the above-mentioned embodiments, the present invention may also have other implementation modes; any technical solutions formed by equivalent replacement or equivalent transformation shall fall within the protection scope required by the present invention.

Claims

1. A method for managing and tracking SQL execution memory of a database computing middleware, characterized in that: include: A tree structure is constructed according to the execution process of SQL. It has multiple structure objects. Each structure object corresponds to each SQL sub-process and is used to record the memory usage of each SQL sub-process. Each structure object has the same life cycle as its corresponding SQL sub-process. Maintain a global variable to store the structure objects corresponding to the top N sessions with the highest memory usage among all sessions, where N is configurable; When any sql subprocess increases or decreases memory usage, the memory usage recorded by the corresponding structure object and its parent node structure object is updated, and the global variable is updated; Check whether the memory usage recorded in the updated structure object exceeds the limit, and trigger multi-strategy over-limit processing when it exceeds the limit; The memory usage of the process is checked regularly. When the memory usage of the process exceeds the limit, the structure objects corresponding to the top N sessions with the highest memory usage are traversed until a session that is allowed to be killed is detected and killed.

2. The method for managing and tracking SQL execution memory of database computing middleware according to claim 1, characterized in that: The session that is allowed to be killed is specified through a hint.

3. The method for managing and tracking the SQL execution memory of the database computing middleware according to claim 1, characterized in that: The multi-strategy over-limit processing includes: recording current stack information, structure information, and issuing an alarm.

4. The method for managing and tracking SQL execution memory of database computing middleware according to claim 1, characterized in that: The multi-strategy overlimit processing includes: executing the disk writing task of the result set, reducing memory usage, and ensuring business continuity.

5. The method for managing and tracking the SQL execution memory of the database computing middleware according to claim 1, characterized in that: The multi-strategy over-limit processing includes: directly performing hard interrupt processing on the thread executing the SQL, that is, canceling the execution of the SQL.

6. The method for managing and tracking SQL execution memory of database computing middleware according to claim 1, characterized in that: The multi-strategy over-limit processing includes: first executing global garbage collection, and then continuing the execution of SQL.

7. The method for managing and tracking SQL execution memory of database computing middleware according to claim 1, characterized in that: Also includes: Print structure statistics, displaying the memory usage of all structure objects, the upper limit of memory usage, and the limit handling actions triggered when the memory exceeds the limit in a tree structure.

8. An electronic device comprising a processor and a memory, wherein the memory stores a program that can be run on the processor, characterized in that: When the program is executed by the processor, the steps of the method for managing and tracking the SQL execution memory of the database computing middleware described in any one of claims 1-7 are implemented.

9. A computer-readable storage medium storing at least one program, characterized in that: The at least one program can be executed by at least one processor to implement the steps of the method for managing and tracking the SQL execution memory of the database computing middleware as described in any one of claims 1-7.

Images