Data operation management method based on local resource state and computer equipment

Abstract

The invention discloses a data operation management method based on a local resource state and computer equipment. The method comprises the following steps: acquiring a resource dynamic state of a local server by a preset project agency service and synchronizing the resource dynamic state to a preset resource manager used for managing a database SQL (Structured Query Language) thread; in response to a database operation request monitored by the project agency service, generating a unique identifier according to related information in the database operation request, allocating a weight, binding the unique identifier and the weight, and sending the unique identifier and the weight to the resource manager; analyzing the database operation request by the project proxy service to obtain an SQL statement, inserting a corresponding unique identifier into the SQL statement, and obtaining a corresponding resource connection from a resource connection pool of the database to execute the SQL statement; and dynamically managing the SQL thread executed in the database by the resource manager in real time according to the resource dynamic state, the unique identifier and the weight of the local server. The execution efficiency and stability of the service system can be improved.

Description

Technical Field

[0001] The present invention relates to the field of computer technology, and in particular to a data operation management method based on local resource status and a computer device. Background Art

[0002] In the process of using the business system, in order to meet the business growth, the system needs to expand components. These components may cover all aspects from data processing modules to user interaction interfaces, aiming to improve the system's performance, functions and user experience. However, component expansion will cause a series of resource management problems. Among them, system resources include but are not limited to computing power, storage capacity, etc., and newly introduced components have different resource requirements for these resources; for example, some high-performance data analysis components may require a large amount of computing resources to quickly process massive data; at the same time, resource competition between different components has also become a prominent problem. If there is no effective resource management strategy, some key components may not be able to obtain sufficient resources and affect their performance, and may even cause system instability or failure. In addition, in order to improve the execution efficiency of business requests, traditional technologies will also create a connection pool in the database to reduce the time for business projects to create links with the database to improve query efficiency, but this operation leads to another resource management problem, that is, when it is necessary to terminate the data query operation, only the communication connection between the project and the connection pool can be terminated, but the SQL (Structured Query Language) thread being executed in the database cannot be terminated. The resources occupied by the thread still exist until the query ends.

[0003] However, there is currently no effective solution to the above-mentioned resource competition and the problem of being unable to release resource occupation in a timely manner. Summary of the invention

[0004] In order to effectively manage resource competition and release resource occupation in a timely manner, thereby improving the execution efficiency and stability of the business system, in the first aspect of the present invention, a data operation management method based on local resource status is proposed, the method comprising: a preset project proxy service obtains the resource dynamics of the local server and synchronizes it to a preset resource manager for managing database SQL threads; in response to the project proxy service monitoring a database operation request, a unique identifier is generated and a weight is assigned according to the relevant information in the database operation request, and the unique identifier and weight are bound and sent to the resource manager; the project proxy service parses the database operation request to obtain an SQL statement, inserts the corresponding unique identifier into the SQL statement, and obtains the corresponding resource connection from the resource connection pool of the database to execute the SQL statement; the resource manager dynamically manages the SQL thread executed in the database in real time according to the resource dynamics of the local server, the unique identifier and the weight.

[0005] In one or more embodiments, the preset project proxy service obtains the resource dynamics of the local server and synchronizes them to the preset resource manager for managing database SQL threads, including: the preset project proxy service obtains the usage of the local server's processor, memory and / or disk and synchronizes them to the preset resource manager for managing database SQL threads.

[0006] In one or more embodiments, a unique identifier is generated and a weight is assigned based on the relevant information in the database operation request, including: generating a unique identifier using a preset snowflake algorithm based on the timestamp, machine code and serial number in the database operation request; and assigning a weight based on the source IP address in the database operation request.

[0007] In one or more embodiments, the method of the present invention also includes: counting the frequency of database operation requests from the same source IP address; in response to the frequency reaching a first preset threshold, increasing the weight of the database operation requests from the same source IP address; in response to the frequency reaching a second preset threshold, reducing the weight of the database operation requests from the same source IP address; in response to the frequency reaching a third preset threshold, reducing the weight of the database operation requests from the same source IP address to the minimum; wherein the first preset threshold, the second preset threshold and the third preset threshold increase sequentially. In one or more embodiments, the project proxy service parses the database operation request to obtain an SQL statement, and inserts a corresponding unique identifier into the SQL statement, including: the project proxy service parses the database operation request to obtain an SQL statement, and inserts the corresponding unique identifier by inserting an annotation into the SQL statement.

[0008] In one or more embodiments, a corresponding resource connection is obtained from a resource connection pool of a database to execute the SQL statement, including: the project agent service obtains a corresponding resource connection from a resource connection pool of a database according to an operation type in the SQL statement to execute the SQL statement, so that the annotation of the SQL thread that executes the SQL statement in the database carries the unique identifier.

[0009] In one or more embodiments, the resource manager dynamically manages the SQL threads executed in the database in real time based on the resource dynamics of the local server, the unique identifier and the weight, including: periodically scanning the SQL threads executed in the database; comparing the usage of the processor, memory and / or disk of the local server with the corresponding preset thresholds in real time; in response to the usage of any of the processor, memory and / or disk exceeding the corresponding preset threshold, obtaining the corresponding unique identifier based on the annotation in each SQL thread; determining the weight of each SQL thread based on the unique identifier, and giving priority to executing SQL threads with high weights.

[0010] In one or more embodiments, the resource manager dynamically manages the SQL thread executed in the database in real time according to the resource dynamics of the local server, the unique identifier and the weight, and also includes: marking the number of scans of the SQL thread in response to the execution time of the SQL thread exceeding a preset time; terminating the corresponding SQL thread and modifying the task status of the corresponding database operation request to incomplete in response to the number of scans being greater than or equal to the preset number.

[0011] In one or more embodiments, the method of the present invention further includes: the resource manager reporting the unfinished database operation request.

[0012] In a second aspect of the present invention, a computer device is proposed, comprising: at least one processor; and a memory, wherein an executable computer program is stored in the memory, and when the computer program is executed by the at least one processor, it is used to implement the steps of the data operation management method based on the local resource status in any of the above method embodiments.

[0013] The beneficial effects of the present invention include: the present invention obtains local resources dynamically in real time through the project proxy service, adds a unique identifier and assigns a weight to the database operation request, and synchronizes the above three types of information to the resource manager, so that the resource manager can bypass the resource pool under certain circumstances and dynamically manage the SOL thread according to the local resource status, thereby greatly improving the work efficiency and stability of the business system. BRIEF DESCRIPTION OF THE DRAWINGS

[0014] In order to more clearly illustrate the embodiments of the present invention or the technical solutions in the prior art, the drawings required for use in the embodiments or the description of the prior art will be briefly introduced below. Obviously, the drawings in the following description are only some embodiments of the present invention. For ordinary technicians in this field, other embodiments can be obtained based on these drawings without paying creative work.

[0015] Figure 1 A workflow diagram of a data operation management method based on local resource status according to an embodiment of the present invention; Figure 2 A schematic diagram of the communication connection relationship between the project service, resource pool and database implemented in the present invention; Figure 3 A schematic diagram of the communication connection relationship among the project service, project proxy service, resource manager and database in an embodiment of the present invention; Figure 4 Schematic diagram of the structure of a computer device according to an embodiment of the present invention. DETAILED DESCRIPTION

[0016] In order to make the objectives, technical solutions and advantages of the present invention more clearly understood, the embodiments of the present invention are further described in detail below in combination with specific embodiments and with reference to the accompanying drawings.

[0017] In order to effectively manage resource competition and release resource occupation in a timely manner, thereby improving the execution efficiency and stability of the business system, in one embodiment of the present invention, a data operation management method based on local resource status is proposed, such as Figure 1 As shown, the method includes: step S1, the preset project proxy service obtains the resource dynamics of the local server and synchronizes it to the preset resource manager for managing the database SQL thread; step S2, in response to the project proxy service monitoring the database operation request, generates a unique identifier and assigns a weight according to the relevant information in the database operation request, and sends the unique identifier and weight binding to the resource manager; step S3, the project proxy service parses the database operation request to obtain an SQL statement, inserts the corresponding unique identifier into the SQL statement, and obtains the corresponding resource connection from the resource connection pool of the database to execute the SQL statement, which further makes the SQL thread executing the SQL statement in the database have a unique identifier; and step S4, the resource manager dynamically manages the SQL thread executed in the database in real time according to the resource dynamics, unique identifier and weight of the local server.

[0018] Specifically, Figure 2As shown, in order to adapt to the rapid expansion of system functional components, the traditional technology will establish a resource connection pool between the business system and the database. After the resource connection pool is started, it will create many connections with the database. When the business request executes an SQL statement, it will send a request to the resource connection pool. The SQL resource pool will allocate a connection to it according to the operation type of the business request and the database will pull up a thread to execute the corresponding SQL statement. In this way, the rapid expansion of system functions can be achieved by managing the resource connection pool; however, this method will cause the business system to be unable to directly send instructions to the database across the resource connection pool when it wants to terminate the execution of a certain SQL statement, and it is also impossible to determine the process that executes the corresponding SQL statement in the database. To this end, this embodiment proposes to generate a unique identifier based on the relevant information in the database operation request, and the project proxy service parses the database operation request to obtain the SQL statement, and inserts the corresponding unique identifier into the SQL statement so that the subsequent process of executing the SQL statement carries the unique identifier, and then when you want to terminate a certain SQL execution process, you can locate it through the unique identifier. On this basis, this embodiment realizes dynamic management of the SQL execution process by monitoring the local resource status and weight allocation, solving the problem of resource waste caused by the inability of the traditional technology to terminate the process in time after adopting the resource connection pool technology.

[0019] In one embodiment, the method for terminating the SQL execution process includes the business system issuing a termination request to terminate a business request, the project agent service monitoring the termination request and calculating the unique identifier of the business request to be terminated and forwarding it to the resource manager, and the resource manager terminating the corresponding SQL statement execution process according to the unique identifier and releasing the resource connection.

[0020] In one embodiment, the operation of terminating the SQL execution process can also be actively terminated by the resource manager according to the resource status of the local resource server and the weight uniquely identified in the SQL statement execution process, so as to release more resources and give priority to high-weight business requests.

[0021] In one embodiment, the preset project proxy service obtains the resource dynamics of the local server and synchronizes them to the preset resource manager for managing database SQL threads, including: the preset project proxy service obtains the usage of the local server's processor, memory and / or disk and synchronizes them to the preset resource manager for managing database SQL threads.

[0022] In one implementation, the project proxy service dynamically collects the CPU, memory, and disk information of the server where it is located, and synchronizes the information to the resource manager. The resource manager provides users with control over CPU, memory, disk and other resources through shared quotas, where shared quotas are configured with certain weights as a percentage, and resource allocation is adjusted based on priority without restricting resource usage.

[0023] In one embodiment, a unique identifier is generated and a weight is assigned based on relevant information in a database operation request, including: using a preset snowflake algorithm to generate a unique identifier based on a timestamp, machine code, and serial number in the database operation request; and assigning a weight based on a source IP address in the database operation request.

[0024] Specifically, the timestamp can effectively distinguish multiple identical database operation requests, so that the same database operation requests sent multiple times can generate multiple different unique identifiers to increase the targeted management; the machine code and serial number can identify the source of the database operation request to ensure the uniqueness of the identifier.

[0025] In one embodiment, the method of the present invention also includes: counting the frequency of database operation requests from the same source IP address; in response to the frequency reaching a first preset threshold, increasing the weight of the database operation requests from the same source IP address; in response to the frequency reaching a second preset threshold, reducing the weight of the database operation requests from the same source IP address; in response to the frequency reaching a third preset threshold, reducing the weight of the database operation requests from the same source IP address to the minimum; wherein the first preset threshold, the second preset threshold and the third preset threshold increase in sequence. Specifically, this embodiment aims to determine the current busy state of a certain IP user through a first preset threshold, and increase its weight when it is relatively busy (i.e., the request frequency reaches the first preset threshold) to improve the request processing speed and achieve full utilization of resources; but when the request frequency reaches the second preset value, considering the demand for resources of other IP users, it is necessary to reduce its weight instead; when the request frequency reaches the third preset value, it is determined that there may be abnormal operations, and its weight is reduced to the minimum to avoid affecting the normal operations of other IP users. In an optional implementation, the above-mentioned embodiment operation is only performed when resources are relatively sufficient.

[0026] In one embodiment, the project proxy service parses the database operation request to obtain the SQL statement, and inserts the corresponding unique identifier into the SQL statement, including: the project proxy service parses the database operation request to obtain the SQL statement, and inserts the corresponding unique identifier by inserting an annotation into the SQL statement.

[0027] Specifically, the project proxy service is loaded when the project system is started. It will automatically identify the access operations to the database in the system and provide another form of annotation. In addition, the project proxy service uses aspect technology and can be quickly embedded in the underlying logic of the system without modifying the original business logic.

[0028] In one embodiment, a corresponding resource connection is obtained from a resource connection pool of a database to execute an SQL statement, including: a project proxy service obtains a corresponding resource connection from a resource connection pool of a database according to an operation type in the SQL statement to execute the SQL statement, so that the annotation of the SQL thread that executes the SQL statement in the database carries a unique identifier.

[0029] Specifically, the operation types of database operation requests include add, delete, modify, query and other types. Different operations correspond to different resource connections. After determining the operation type and calling the resource connection, the database will pull up the thread according to the enabled resource connection to execute the corresponding SQL statement, so that each thread can be located based on the annotation.

[0030] In one embodiment, the resource manager dynamically manages the SQL threads executed in the database in real time according to the resource dynamics, unique identifiers and weights of the local server, including: periodically scanning the SQL threads executed in the database; comparing the usage of the processor, memory and / or disk of the local server with the corresponding preset thresholds in real time; in response to the usage of any one of the processor, memory and / or disk exceeding the corresponding preset threshold, obtaining the corresponding unique identifier according to the annotation in each SQL thread; determining the weight of each SQL thread according to the unique identifier, and giving priority to executing SQL threads with high weights.

[0031] In one embodiment, precise adjustments can also be made based on the resources required by the SQL thread. For example, when the memory usage percentage exceeds a preset value, only SQL threads that require memory resources are prioritized for high-weight SQL thread operations, while SQL threads that do not require memory resources are not restricted.

[0032] In one embodiment, the resource manager dynamically manages the SQL thread executed in the database in real time according to the resource dynamics, unique identifier and weight of the local server, and further includes: marking the number of scans of the SQL thread in response to the execution time of the SQL thread exceeding the preset time; terminating the corresponding SQL thread and modifying the task status of the corresponding database operation request to incomplete in response to the number of scans being greater than or equal to the preset number, for example, 2. This embodiment can avoid the problem of thread deadlock, can quickly release thread deadlock and release resources and connections.

[0033] In one embodiment, the method of the present invention further includes: the resource manager reports the unfinished database operation request. The purpose of reporting the status is to prompt the user to resubmit the request and improve the efficiency of task completion.

[0034] The communication connection relationship between the project service, project proxy service, resource manager and database of the present invention is as follows: Figure 3 As shown, the project proxy service is embedded into the underlying logic of the project service using aspect technology, so there is no need to modify the original project service, making the implementation of the method of the present invention easier and adaptable to various project services; in addition, in order to bypass the resource pool, the present invention also needs to build a resource manager to communicate directly with the database, and in order to reduce the impact on the original database operations, the present invention also needs to set up a proxy service on the database side to facilitate the management of SQL threads.

[0035] In one implementation, the resource manager may implement management policy updates by updating policy configurations.

[0036] In one implementation, the user can directly send instructions to the resource manager through manual operations, thereby actively implementing control and management of the SQL thread.

[0037] Through the above embodiments, the present invention obtains local resources dynamically in real time through the project proxy service, adds unique identification and assigns weights to database operation requests, and synchronizes the above three types of information to the resource manager, so that the resource manager can bypass the resource pool under specific circumstances and dynamically manage the SOL thread according to the local resource status, thereby greatly improving the work efficiency and stability of the business system.

[0038] In the second aspect of the present invention, see Figure 4 , a computer device is proposed, including: at least one processor 100; and a memory 200, in which an executable computer program is stored, and when the computer program is executed by at least one processor 100, it is used to implement the steps of the data operation management method based on the local resource status in any of the above method embodiments.

[0039] The above are exemplary embodiments disclosed in the present invention, but it should be noted that various changes and modifications may be made without departing from the scope of the embodiments disclosed in the claims. The functions, steps and / or actions of the method claims according to the disclosed embodiments described herein do not need to be performed in any particular order.

[0040] The serial numbers of the embodiments disclosed in the above embodiments of the present invention are only for description and do not represent the advantages or disadvantages of the embodiments.

[0041] A person skilled in the art should understand that the discussion of any of the above embodiments is only exemplary and is not intended to imply that the scope of the disclosure of the embodiments of the present invention (including the claims) is limited to these examples; under the concept of the embodiments of the present invention, the technical features in the above embodiments or different embodiments can also be combined, and there are many other changes in different aspects of the above embodiments of the present invention, which are not provided in detail for the sake of simplicity. Therefore, any omissions, modifications, equivalent substitutions, improvements, etc. made within the spirit and principles of the embodiments of the present invention should be included in the protection scope of the embodiments of the present invention.

Claims

1. A data operation management method based on local resource status, characterized in that: The method comprises: The preset project proxy service obtains the resource dynamics of the local server and synchronizes them to the preset resource manager for managing database SQL threads; In response to the project proxy service monitoring a database operation request, generating a unique identifier and assigning a weight according to relevant information in the database operation request, and binding the unique identifier and the weight and sending them to the resource manager; The project proxy service parses the database operation request to obtain an SQL statement, inserts a corresponding unique identifier into the SQL statement, and obtains a corresponding resource connection from a database resource connection pool to execute the SQL statement; The resource manager dynamically manages the SQL thread executed in the database in real time according to the resource dynamics of the local server, the unique identifier and the weight.

2. The data operation management method based on local resource status according to claim 1, characterized in that: The preset project proxy service obtains the resource dynamics of the local server and synchronizes them to the preset resource manager for managing database SQL threads, including: The preset project proxy service obtains the usage of the processor, memory and / or disk of the local server and synchronizes it to the preset resource manager for managing the database SQL thread.

3. The data operation management method based on local resource status according to claim 1, characterized in that: Generating a unique identifier and assigning a weight according to relevant information in the database operation request includes: Generate a unique identifier using a preset snowflake algorithm according to the timestamp, machine code and serial number in the database operation request; A weight is assigned according to a source IP address in the database operation request.

4. The data operation management method based on local resource status according to claim 3 is characterized in that: The method further comprises: Count the frequency of database operation requests from the same source IP address; In response to the frequency reaching a first preset threshold, increasing the weight of the database operation request from the same source IP address; In response to the frequency reaching a second preset threshold, reducing the weight of the database operation request from the same source IP address; In response to the frequency reaching a third preset threshold, reducing the weight of the database operation request from the same source IP address to a minimum; Among them, the first preset threshold, the second preset threshold and the third preset threshold increase in sequence.

5. The data operation management method based on local resource status according to claim 1, characterized in that: The project proxy service parses the database operation request to obtain an SQL statement, and inserts a corresponding unique identifier into the SQL statement, including: The project proxy service parses the database operation request to obtain an SQL statement, and inserts a corresponding unique identifier into the SQL statement by inserting a comment.

6. The data operation management method based on local resource status according to claim 5, characterized in that: Obtaining a corresponding resource connection from a resource connection pool of a database to execute the SQL statement includes: The project proxy service obtains the corresponding resource connection from the resource connection pool of the database according to the operation type in the SQL statement to execute the SQL statement, so that the annotation of the SQL thread that executes the SQL statement in the database carries the unique identifier.

7. The data operation management method based on local resource status according to claim 2, characterized in that: The resource manager dynamically manages the SQL thread executed in the database in real time according to the resource dynamics of the local server, the unique identifier and the weight, including: Periodically scanning SQL threads executed in the database; Comparing the usage of the processor, memory and / or disk of the local server with corresponding preset thresholds in real time; In response to the usage of any one of the processor, memory and / or disk exceeding a corresponding preset threshold, obtaining a corresponding unique identifier according to the annotation in each SQL thread; The weight of each SQL thread is determined according to the unique identifier, and SQL threads with high weights are executed preferentially.

8. The data operation management method based on local resource status according to claim 7, characterized in that: The resource manager dynamically manages the SQL thread executed in the database in real time according to the resource dynamics of the local server, the unique identifier and the weight, and also includes: In response to the execution time of the SQL thread exceeding a preset time, marking the number of scans of the SQL thread; In response to the scanning times being greater than or equal to the preset times, the corresponding SQL thread is terminated and the task status of the corresponding database operation request is modified to incomplete.

9. The data operation management method based on local resource status according to claim 8, characterized in that: The method further comprises: The resource manager reports the unfinished database operation request.

10. A computer device, characterized in that: include: at least one processor; as well as A memory, wherein an executable computer program is stored in the memory, and when the computer program is executed by the at least one processor, it is used to implement the steps of the data operation management method based on the local resource status as described in any one of claims 1 to 9.

Images