A database dynamic query optimization and resource scheduling method, device and medium

Abstract

The embodiment of the application discloses a database dynamic query optimization and resource scheduling method, device and medium, belongs to the technical field of databases, and solves the problems that the manual adjustment of PostgreSQL resource parameters is inefficient and causes resource waste. Query feature data and resource state data corresponding to a database are acquired, time series processing is performed on the resource state data, and resource prediction results are obtained; the query feature data and the resource state data are input into a preset AI strategy optimization model to output an optimized execution plan and optimized resource allocation parameters; the optimized execution plan is input into a dynamic injection module; based on the optimized resource allocation parameters and the resource prediction results, the running parameters of PostgreSQL are dynamically adjusted; in response to a plan injection instruction, a required execution plan is selected in the dynamic injection module, and based on the adjusted running parameters and the required execution plan, an optimized query task is executed.

Description

TECHNICAL FIELD

[0001] The present application relates to the technical field of database, and particularly relates to a database dynamic query optimization and resource scheduling method, device and medium. BACKGROUND

[0002] In the field of databases, PostgreSQL, as a mainstream open-source database, faces great challenges in query optimization and resource scheduling mechanisms.

[0003] The traditional PostgreSQL query optimizer relies on fixed rules and static statistical information to generate execution plans, which is difficult to adapt to dynamically changing query patterns, often leading to inefficient execution plans. For example, in the face of sudden high-concurrency queries or complex business scenarios, static optimization strategies are difficult to adjust key parameters such as index selection and connection order in real time, resulting in low query performance.

[0004] At the same time, PostgreSQL's resource scheduling relies on manual configuration of shared buffers, working memory and other parameters, making it difficult to dynamically optimize according to real-time load. Therefore, when the system load fluctuates, manually adjusting resource parameters is not only inefficient, but also causes resource waste due to uneven allocation. SUMMARY

[0005] The embodiments of the present application provide a database dynamic query optimization and resource scheduling method, device and medium, which are used to solve the technical problems that manually adjusting PostgreSQL resource parameters is not only inefficient, but also causes resource waste due to uneven allocation.

[0006] The embodiments of the present application adopt the following technical solutions:

[0007] The embodiments of the present application provide a database dynamic query optimization and resource scheduling method. It includes obtaining query feature data and resource state data corresponding to the database, and performing time series processing on the resource state data to obtain resource prediction results; inputting the query feature data and the resource state data into a preset AI strategy optimization model to output an optimized execution plan and an optimized resource allocation parameter through the preset AI strategy optimization model; inputting the optimized execution plan into a dynamic injection module; wherein the dynamic injection module is implemented by extending the database and is used for receiving and detecting the optimized execution plan; based on the optimized resource allocation parameter and the resource prediction result, dynamically adjusting the running parameters of PostgreSQL; in response to a plan injection instruction, selecting a required execution plan in the dynamic injection module to execute the query task of the optimized PostgreSQL based on the adjusted running parameters and the required execution plan.

[0008] In an implementation manner of the present application, the resource state data is subjected to time series processing to obtain resource prediction results, specifically including: dividing historical resource state data into historical basic resource feature data, historical query load feature data and historical database state feature data; according to time sequence, performing time dimension alignment on the divided historical data, and based on the data change values corresponding to the same time, determining the resource consumption correlation between the query load feature data and the basic resource feature data and the database state feature data; based on the resource consumption correlation corresponding to different times respectively, constructing a resource consumption correlation sequence; based on the resource consumption correlation sequence and the resource state sequence corresponding to the resource state data, predicting future load conditions to obtain resource prediction results.

[0009] In an implementation manner of the present application, based on the optimized resource allocation parameters and the resource prediction results, the running parameters of PostgreSQL are dynamically adjusted, specifically including: based on the current query task, querying the historical query tasks with similarity greater than a preset similarity threshold in the historical database, and determining the historical optimized resource allocation parameters and the historical resource prediction results corresponding to the historical query tasks; based on the allocation parameter types corresponding to different historical query tasks respectively, performing parameter division on the historical optimized resource allocation parameters and the historical resource prediction results; based on the performance data corresponding to the historical query tasks, determining the adjustment factors corresponding to each parameter type after division respectively; based on the preset fusion weight and the adjustment factors, performing fusion processing and dynamic adjustment processing on the optimized resource allocation parameters and the resource prediction results.

[0010] In an implementation manner of the present application, based on the optimized resource allocation parameters and the resource prediction results, the running parameters of PostgreSQL are dynamically adjusted, specifically including: in the case that the optimized resource allocation parameters and the resource prediction results exist resource allocation conflicts, determining the conflict resource difference; in the case that the conflict resource difference is greater than a preset conflict threshold, based on the task priority, determining the resource quota and dividing the resource quota to a preset number of adjustment sections; sequentially allocating the resource quota corresponding to each adjustment section, and after completing the resource allocation of any adjustment section, starting the resource allocation effect detection strategy; in the case that the resource allocation effect detection strategies corresponding to each adjustment section respectively all meet the detection conditions, completing the dynamic adjustment of the running parameters of PostgreSQL.

[0011] In an implementation form of the present application, in response to the plan injection instruction, the required execution plan is selected in the dynamic injection module, specifically comprising: in response to the plan injection instruction, determining the required function in the preset function set; wherein the query task identifier is marked in the plan injection instruction; based on the query task identifier, the required execution plan is filtered out in the dynamic injection module; wherein the dynamic injection module includes the optimization execution plans corresponding to different query tasks, and each optimization execution plan is marked with a query task identifier; the required function is called to inject the required execution plan in the dynamic injection module into the query planner of PostgreSQL.

[0012] In an implementation form of the present application, before the required execution plan in the dynamic injection module is injected into the query planner of PostgreSQL, the method further comprises: performing feasibility detection on the optimization execution plan received by the dynamic injection module; wherein the feasibility detection at least includes index existence detection, table or view access restriction detection, and parallel execution permission detection; and performing security detection on the execution plan received by the dynamic injection module and the preset policy whitelist; and determining the version information of PostgreSQL, and performing compatibility detection on the optimization execution plan and the version information.

[0013] In an implementation form of the present application, based on the adjusted running parameters and the required execution plan, after the query task of the optimized PostgreSQL is executed, the method further comprises: recording the performance data of the execution of the query task of the optimized PostgreSQL; comparing the recorded performance data with the historical performance data; if the comparison result is that the performance decline value is greater than the preset performance threshold, automatically restoring the original execution plan through the dynamic injection module.

[0014] In an implementation form of the present application, after the recorded performance data is compared with the historical performance data, the method further comprises: if the comparison result is that the performance decline value is greater than the preset performance threshold, marking the required execution plan as an abnormal event; or if the comparison result is that the performance decline value is not greater than the preset performance threshold, marking the required execution plan as a positive event; taking the abnormal event marking and the positive event marking as model training samples to iteratively optimize the preset AI strategy optimization model.

[0015] The embodiment of the application provides a database dynamic query optimization and resource scheduling device, comprising: at least one processor; and a memory connected with the at least one processor in communication; wherein the memory stores instructions executable by the at least one processor, and the instructions are executed by the at least one processor to enable the at least one processor to: acquire query feature data and resource state data corresponding to a database, and perform time series processing on the resource state data to obtain a resource prediction result; input the query feature data and the resource state data into a preset AI strategy optimization model to output an optimized execution plan and an optimized resource allocation parameter through the preset AI strategy optimization model; input the optimized execution plan into a dynamic injection module; wherein the dynamic injection module is implemented by extending the database and is used for receiving and detecting the optimized execution plan; based on the optimized resource allocation parameter and the resource prediction result, dynamically adjusting running parameters of PostgreSQL; in response to a plan injection instruction, selecting a required execution plan in the dynamic injection module to execute an optimized PostgreSQL query task based on the adjusted running parameters and the required execution plan.

[0016] The embodiment of the application provides a nonvolatile computer storage medium, which stores computer executable instructions, and the computer executable instructions are set to: acquire query feature data and resource state data corresponding to a database, and perform time series processing on the resource state data to obtain a resource prediction result; input the query feature data and the resource state data into a preset AI strategy optimization model to output an optimized execution plan and an optimized resource allocation parameter through the preset AI strategy optimization model; input the optimized execution plan into a dynamic injection module; wherein the dynamic injection module is implemented by extending the database and is used for receiving and detecting the optimized execution plan; based on the optimized resource allocation parameter and the resource prediction result, dynamically adjusting running parameters of PostgreSQL; in response to a plan injection instruction, selecting a required execution plan in the dynamic injection module to execute an optimized PostgreSQL query task based on the adjusted running parameters and the required execution plan.

[0017] The at least one technical solution adopted by the embodiments of the present application can achieve the following beneficial effects: The embodiments of the present application can accurately obtain the dynamic changes of the load by obtaining the query features and the resource state data and performing time series processing, so that the resource prediction result is more in line with the actual demand. Secondly, the embodiments of the present application output the optimized execution plan and the resource parameters by pre-setting the AI strategy optimization model, break through the limitations of traditional static rules, can adaptively adjust the execution strategies such as index selection and connection order, and dynamically match the resource allocation parameters to avoid resource waste or competition. In addition, the dynamic injection module in the embodiments of the present application is implemented in an extended form, which can inject the optimization plan without modifying the kernel, ensuring compatibility and flexibility. In addition, the running parameter adjustment based on the prediction result and the optimization parameter can improve the response efficiency and stability of the database under complex load. BRIEF DESCRIPTION OF DRAWINGS

[0018] In order to more clearly illustrate the technical solutions in the embodiments of the present application or the prior art, the drawings needed to be used in the embodiments or the prior art description will be briefly introduced. Obviously, the drawings in the following description are only some embodiments described in the present application, and other drawings can be obtained by those skilled in the art without creative labor. In the drawings:

[0019] Figure 1 A database dynamic query optimization and resource scheduling method flow chart is provided for the embodiments of the present application.

[0020] Figure 2 A database dynamic query optimization and resource scheduling flowchart is provided for the embodiments of the present application.

[0021] Figure 3 A structural diagram of a database dynamic query optimization and resource scheduling device is provided for the embodiments of the present application.

[0022] Reference signs:

[0023] 200: database dynamic query optimization and resource scheduling device, 201: processor, 202: memory. DETAILED DESCRIPTION

[0024] The embodiments of the present application provide a database dynamic query optimization and resource scheduling method, device and medium.

[0025] In order to enable a person skilled in the art to better understand the technical solutions in the present application, the technical solutions in the embodiments of the present application will be clearly and completely described in the following with reference to the accompanying drawings. Obviously, the described embodiments are only some of the embodiments of the present application, not all. Based on the embodiments of the present application, all other embodiments obtained by a person skilled in the art without creative labor should fall within the scope of protection of the present application.

[0026] The technical solutions of the embodiments of the present application will be described in detail below with reference to the accompanying drawings.

[0027] Figure 1 A database dynamic query optimization and resource scheduling method flowchart provided by the embodiments of the present application is shown in FIG. 1. Figure 1 The database dynamic query optimization and resource scheduling method includes the following steps:

[0028] Step 101, obtain the query feature data and resource state data corresponding to the database, and perform time series processing on the resource state data to obtain a resource prediction result.

[0029] In an implementation manner of the present application, the data acquisition module collects the query feature data inside the database, mainly the query statistical information pg_stat_statements and the lock information pg_locks, to provide the query features for the AI model. In addition, the monitoring data acquisition module collects the resource state data of the operating system layer, including CPU usage, memory occupation, I / O throughput and delay, etc. These data are used for load prediction on one hand, and also as the input of the AI model to represent the current resource state of the system.

[0030] In an implementation manner of the present application, the historical resource state data is divided into historical basic resource feature data, historical query load feature data and historical database state feature data. According to the time sequence, the divided historical data is aligned in the time dimension, and based on the data change values corresponding to the same time, the resource consumption correlation between the query load feature data and the basic resource feature data and the database state feature data is determined. Based on the resource consumption correlation corresponding to different times, a resource consumption correlation sequence is constructed. Based on the resource consumption correlation sequence and the resource state sequence corresponding to the resource state data, the future load condition is predicted to obtain a resource prediction result.

[0031] Specifically, first, the collected historical resource state data is divided into at least three categories according to types, wherein the historical basic resource feature data contains CPU usage, memory occupation, etc., the historical query load feature data includes query type, table size, etc., and the historical database state feature data includes shared_buffers hit rate (shared_buffers is a server configuration parameter in the PostgreSQL database, which belongs to the shared memory part and is mainly used for caching database data pages), work_mem (work_mem is a key runtime parameter in the PostgreSQL database, which is mainly used to control the allocation of working memory during query execution) usage, and other database-specific indicators. Then, according to time sequence, the three types of data are sampled with the same time window, so that different types of data correspond to each other on the time axis.

[0032] For each time point data aligned in the time dimension, the correlation between the change value of the query load feature data at the time point and the change value of the basic resource feature data and the database state feature data at the same period is analyzed. For example, when the amount of a certain type of complex query increases, the corresponding change of work_mem usage and CPU occupancy rate, the correlation between different query load features and resource consumption features is calculated through statistical methods, so as to determine the specific resource consumption correlation.

[0033] Further, according to the resource consumption correlation determined at each time point, the resource consumption correlation is arranged in time sequence to form a resource consumption correlation sequence. In addition, the historical resource state data is arranged in time sequence to form a resource state sequence. The constructed resource consumption correlation sequence and the corresponding resource state sequence are combined to predict the future load situation, so as to obtain the possible resource consumption in the future time period, such as CPU utilization and work_mem demand in the future period of time.

[0034] The specific process of predicting the future load condition is: aligning the constructed resource consumption association sequence and the resource state sequence in the time dimension, for example, binding the association relationship that the increase of multi-table JOIN query volume in a certain time period leads to a 20% increase in work_mem consumption with resource state data such as CPU utilization and memory occupancy in the same period, to generate training sample data of the prediction model. The long-term trend and nonlinear fluctuation of CPU, memory and other indicators are learned by using the LSTM deep learning model, and the resource consumption association sequence is input as context to adjust the prediction weight of the time series model. For example, when the association sequence shows that the complex query volume increases by 10% on average, the work_mem demand increases by 15%, the model will preferentially refer to this rule when predicting memory demand. The confidence of the resource consumption association relationship at different time points is determined, and higher weight is given to the high-confidence association relationship, and the low-confidence association relationship is weakened to avoid prediction deviation. Based on the preset time window, the load condition of multiple time points in the future, such as the next 30 minutes, is predicted, and the sliding window is updated with new data after each prediction to ensure that the model is always based on the latest association rule.

[0035] Step 102, input the query feature data and the resource state data into the preset AI strategy optimization model to output the optimized execution plan and the optimized resource allocation parameter through the preset AI strategy optimization model.

[0036] In an implementation manner of the present application, the training process of the preset AI strategy optimization model in the embodiment of the present application is: receiving the query features and the resource states from the data collection layer as inputs. Based on a reinforcement learning framework such as TensorFlow, the output is the optimized execution plan and the optimized resource allocation parameter. The query features and the resource states in the input sample can include: SQL statements, execution frequency, average response time, returned row number, cache read volume, index usage, connection mode, parallelism, CPU, memory, I / O usage, database cache hit rate, and task priority. The output sample can include the historical execution plan and the historical resource allocation parameter corresponding to the input sample. Through the input sample and the output sample, the reinforcement learning framework is trained to obtain the preset AI strategy optimization model.

[0037] Step 103, input the optimized execution plan into the dynamic injection module.

[0038] In an implementation manner of the present application, the dynamic injection module in the embodiment of the present application is implemented as a PostgreSQL extension without modifying the kernel source code. The dynamic injection module receives the optimized execution plan generated by the AI model, and injects it into the query planner of PostgreSQL through Hook or custom function, thereby affecting the generation of the final execution plan.

[0039] In an implementation form of the present application, the dynamic injection module is implemented as a PostgreSQL extension, which is loaded in the form of a dynamic link library through a CREATE EXTENSION command (the CREATE EXTENSION command is used to load and install an extension module, and can add new functions to PostgreSQL without modifying the database kernel). The communication interaction component is responsible for communicating with the AI strategy optimization model service and receiving the optimized execution plan, the plan verification component verifies the legality and compatibility of the optimized execution plan, the Hook control component intercepts the query planning process by registering planner_hook and other planning stage Hook functions and implements plan injection, and the feedback collection component collects execution feedback data for AI strategy optimization model iteration. The components work together through the PostgreSQL standard interface, register and connect in the initialization stage, perform receiving, verification, conversion and injection operations in the plan injection stage, and form a closed loop by collecting feedback in the execution stage, thereby realizing the injection of the optimized execution plan without modifying the kernel.

[0040] Step 104, based on the optimized resource allocation parameters and the resource prediction results, dynamically adjusting the running parameters of PostgreSQL.

[0041] In an implementation form of the present application, based on the current query task, the historical query tasks with a similarity greater than a preset similarity threshold are queried in the historical database, and the historical optimization resource allocation parameters and the historical resource prediction results corresponding to the historical query tasks are determined. Based on the allocation parameter types corresponding to different historical query tasks, the historical optimization resource allocation parameters and the historical resource prediction results are parameter-divided. Based on the performance data corresponding to the historical query tasks, the adjustment factors corresponding to each parameter type after the division are determined. Based on the preset fusion weight and the adjustment factors, the optimized resource allocation parameters and the resource prediction results are fused and dynamically adjusted.

[0042] Specifically, based on the characteristics of the current query task, such as the query type, table structure, etc., the historical query tasks with a similarity greater than a preset similarity threshold are retrieved in the historical database. For each matched historical query task, the corresponding historical optimization resource allocation parameters and historical resource prediction results are determined. According to the allocation parameter types involved in different historical query tasks, the obtained historical optimization resource allocation parameters and historical resource prediction results are classified and divided. For example, the parameters are divided into memory-related parameters, CPU-related parameters, etc., so that the same type of parameters can be processed centrally.

[0043] Further, based on the performance data of the historical query tasks in the actual execution process, such as execution time, resource consumption, response speed, etc., the influence degree of different parameter types on performance in different scenarios is determined, so as to determine the adjustment factor corresponding to each parameter type after division. That is, for each group of parameters, the correlation between its value and performance index is analyzed. For example, when the memory parameter work_mem is increased from 256MB to 512MB, the execution time of the corresponding query task is shortened by 20%, and it is determined that work_mem has a significant influence on the performance in this scenario. In this way, the influence of different parameter types on performance in different task query scenarios such as transaction query and report query is calculated by statistical method. Secondly, according to the influence degree mapping relationship, an adjustment factor is generated for each group of parameters, for example: if shared_buffers in a certain type of report query is increased by 10%, the I / O waiting time can be reduced by 15%, then the adjustment factor of shared_buffers (shared_buffers is a server configuration parameter in PostgreSQL database, which belongs to shared memory and is mainly used to cache database data pages) in this scenario is set to positive correlation 1.5; if work_mem is over-allocated, causing memory competition and prolonging execution time, then the adjustment factor is negative correlation such as -0.8.

[0044] The embodiment of the application sets the fusion weight for different parameter types in advance, which reflects the priority of the parameter types in resource scheduling. The current optimized resource allocation parameter and resource prediction result are weighted and fused and dynamically adjusted by combining the preset fusion weight and the determined adjustment factor. The final resource allocation parameter and prediction result are more suitable for the actual needs of the current query task, and the query performance and resource utilization are improved.

[0045] In an implementation manner of the application, in the case that the optimized resource allocation parameter and the resource prediction result exist resource allocation conflict, the conflict resource difference is determined. In the case that the conflict resource difference is greater than the preset conflict threshold, the resource quota is determined based on the task priority, and the resource quota is divided into a preset number of adjustment sections. The resource quota corresponding to each adjustment section is allocated in turn, and after completing the resource allocation of any adjustment section, the resource allocation effect detection strategy is started. In the case that the resource allocation effect detection strategies corresponding to each adjustment section all meet the detection conditions, the dynamic adjustment of the running parameters of PostgreSQL is completed

[0046] Specifically, the real-time monitoring optimizes the resource allocation parameters and the resource prediction results. When there is a conflict in resource allocation between the two, the specific difference of the conflict resource is calculated. For example, the optimized resource allocation parameter sets work_mem as 1 GB, while the resource prediction result shows that 1.5 GB is needed, and the conflict difference is 0.5 GB. At this time, the conflict resource difference exceeds the preset conflict threshold, such as 0.3 GB. Then, according to the priority of each query task, the total resource quota is determined. For example, the query tasks are divided into high priority, medium priority and low priority, and different priority levels are allocated resource weights. The higher the priority, the greater the corresponding allocated resource weight. If it is predicted that the concurrent volume of high-priority transaction queries will increase, the total resource quota will be expanded according to the weight ratio, for example, the total work_mem will be increased from 1 GB to 1.5 GB, of which 60% (900 MB) is allocated to transaction queries.

[0047] Further, the resource quota is divided into multiple adjustment sections, each section corresponding to a different resource allocation ratio or increment, so as to gradually adjust the resource allocation. According to the order of the divided adjustment sections, the resource quota corresponding to each section is allocated in turn. For example, the first section allocates 30% of the resource quota first, and adjusts the related running parameters. After completing the resource allocation of each section, the resource allocation effect detection strategy is started immediately, such as monitoring the query execution time, resource utilization and other indicators to determine whether the current allocation has improved the system performance. The resource allocation effect of each adjustment section is detected, and if all the detection strategies corresponding to the sections meet the preset detection conditions, it is confirmed that the current resource allocation scheme is effective, and the dynamic adjustment of the PostgreSQL running parameters is completed. If a section fails to pass the detection, subsequent allocation is stopped and the previous effective state is rolled back, and the allocation strategy is adjusted again.

[0048] Step 105, in response to the plan injection instruction, selecting the required execution plan in the dynamic injection module to execute the optimized PostgreSQL query task based on the adjusted running parameters and the required execution plan.

[0049] In an implementation manner of the present application, in response to the plan injection instruction, the required function is determined in the preset function set; wherein the query task identifier is marked in the plan injection instruction. The required execution plan is filtered out in the dynamic injection module based on the query task identifier; wherein the dynamic injection module includes the optimized execution plans corresponding to different query tasks respectively, and each optimized execution plan is marked with a query task identifier. The required function is called to inject the required execution plan in the dynamic injection module into the query planner of PostgreSQL.

[0050] Specifically, when receiving the plan injection instruction, the instruction is parsed to extract the annotated query task identifier therefrom. The identifier is used to uniquely identify the query task currently requiring processing. In the dynamic injection module, the optimization execution plans corresponding to different query tasks are stored, and each execution plan is annotated with a query task identifier matching the execution plan. According to the extracted query task identifier, the module is searched and screened to find an optimization execution plan completely matching the identifier, ensuring that the selected plan is the optimal solution customized for the current query task.

[0051] Further, the preset function set in the embodiment of the present application includes various functions for implementing the plan injection function. According to the specific requirements of the plan injection and the interface requirements of the PostgreSQL query planner, specific functions required for performing the injection operation are determined from the function set, for example, functions for registering a Hook, replacing an execution plan node, or interacting with the planner. These functions will be used to complete the subsequent plan injection operation. The determined required functions are called, and the selected required execution plan is transmitted into the functions as a parameter. Through the execution of the functions, the optimization execution plan is injected into the query planner of the PostgreSQL, thereby updating the final query execution path and implementing the optimization of the query task.

[0052] In an implementation manner of the present application, before injecting the required execution plan in the dynamic injection module into the query planner of the PostgreSQL, the method further includes performing feasibility detection on the optimization execution plans received by the dynamic injection module; wherein the feasibility detection at least includes index existence detection, table or view access restriction detection, and parallel execution allowance detection. Further, the execution plan received by the dynamic injection module is subjected to security detection with a preset policy whitelist. Further, the version information of the PostgreSQL is determined, and the compatibility of the optimization execution plan and the version information is detected.

[0053] Specifically, before injecting the required execution plan into the PostgreSQL query planner, the feasibility of the optimization execution plan received by the dynamic injection module is detected. The detection includes index existence detection, checking whether the indexes involved in the plan actually exist and are valid in the database; table or view access restriction detection, confirming that the access of the execution plan to the target table or view conforms to the permission settings of the database, without the risk of unauthorized access; parallel execution permission detection, judging whether the parallel execution operation involved in the plan conforms to the parallel execution rules of PostgreSQL and the current system configuration, ensuring that the execution plan can normally proceed in the existing environment. After completing the feasibility detection, the execution plan received by the dynamic injection module is compared with the preset policy whitelist for security detection. The preset policy whitelist in the embodiment of the application contains legal execution operations, query types and resource access ranges that have been security verified, and through detection, execution plans with malicious operations or exceeding the safe range can be screened out, avoiding the risk of unsafe execution plan injection to the database. The version information of the current PostgreSQL database is determined, including the major version number, the minor version number and related patch versions, etc. The compatibility of the optimization execution plan and the obtained version information is detected, checking whether the database features, functions, syntax and parameter configurations involved in the plan are compatible with the current PostgreSQL version, for example, some new query syntax or parameters supported by high versions may not be recognized in low versions, and such compatibility problems need to be excluded in advance through detection.

[0054] In an implementation manner of the application, the performance data of the execution-optimized PostgreSQL query task is recorded. The recorded performance data is compared with the historical performance data. If the comparison result is that the performance decline value is greater than the preset performance threshold, the original execution plan is automatically restored through the dynamic injection module.

[0055] Specifically, the performance data of the query task executed by the optimized PostgreSQL is recorded, and the recorded performance data includes but is not limited to query execution time, CPU usage, memory occupancy, I / O throughput and delay, lock waiting time and other key indicators. The recorded performance data is compared with the historical performance data of the corresponding query task stored in advance, and the change difference of each performance indicator is calculated. According to the comparison result, the comprehensive value of performance decline is calculated, and the value is compared with the preset performance threshold. If the performance decline value does not exceed the preset threshold, it is determined that the current optimization execution plan is effective and does not need to be adjusted; if the performance decline value is greater than the preset threshold, it is determined that the optimization execution plan does not achieve the expected effect, and the execution plan recovery process needs to be started. When it is determined that the original execution plan needs to be recovered, a recovery instruction is sent to the dynamic injection module. After receiving the instruction, the dynamic injection module retrieves the original execution plan corresponding to the query task from the stored historical execution plan, and re-injects the original execution plan into the query planner of the PostgreSQL through the registered Hook or custom function, to replace the current optimization execution plan, so that the query task can be executed according to the original plan in the future, and the performance continues to decline.

[0056] In an implementation manner of the present application, if the comparison result is that the performance decline value is greater than the preset performance threshold, the required execution plan is marked as an abnormal event. Alternatively, if the comparison result is that the performance decline value is not greater than the preset performance threshold, the required execution plan is marked as a positive event. The abnormal event marking and the positive event marking are used as model training samples to iteratively optimize the preset AI strategy optimization model.

[0057] Specifically, if the performance decline value is greater than the preset performance threshold, it indicates that the current required execution plan does not achieve the expected effect, and the abnormal event is marked. If the performance decline value is not greater than the preset performance threshold, it indicates that the execution plan optimization is effective, and the positive event is marked. The marked abnormal event and positive event data are constructed as standardized model training samples and input into the preset AI strategy optimization model to start the model iterative optimization process.

[0058] Figure 2 A database dynamic query optimization and resource scheduling process schematic diagram is provided for the embodiments of the present application. As shown in Figure 2As shown, the data collection module: for collecting query statistics, lock information and other information inside the database, providing query features for AI model. Monitoring data collection: responsible for collecting resource indicators at the operating system level, including CPU usage, memory occupancy, I / O throughput and delay, etc. These data are used for load prediction on the one hand, and also as input for AI model, representing the current resource state of the system. AI model training module: receives query features and resource state from data collection layer as input. Based on reinforcement learning framework for training, its output is the optimized execution plan and optimized resource allocation parameters. Dynamic injection module: this module is implemented as a PostgreSQL extension without modifying the kernel source code. The module receives the optimized execution plan generated by the AI model, and injects it into the query planner of PostgreSQL through Hook or custom function, thereby affecting the final execution plan generation. PostgreSQL kernel: the core of the database system, receiving optimization strategies from the dynamic injection module, executing SQL queries. Resource scheduling engine: this system component receives two kinds of instructions: future load prediction results from load prediction module and resource parameter optimization suggestions from AI model. Based on these information, dynamically adjust the runtime parameters of PostgreSQL, realize the elastic allocation of resources. Load prediction module: using historical resource indicator data sent by monitoring data collection, through time series analysis to predict future load trend, and inform the resource scheduling engine of the prediction result for resource adjustment.

[0059] Figure 3 A structural schematic diagram of a database dynamic query optimization and resource scheduling device provided for an embodiment of the present application is shown in the figure. Figure 3 As shown, the database dynamic query optimization and resource scheduling device 200 comprises at least one processor 201, and a memory 202 communicatively connected with the at least one processor 201; wherein the memory 202 stores instructions executable by the at least one processor 201, and the instructions are executed by the at least one processor 201 to enable the at least one processor 201 to: acquire query feature data and resource state data corresponding to a database, and perform time series processing on the resource state data to obtain a resource prediction result; input the query feature data and the resource state data into a preset AI strategy optimization model to output an optimized execution plan and an optimized resource allocation parameter through the preset AI strategy optimization model; input the optimized execution plan into a dynamic injection module; wherein the dynamic injection module is implemented by extending the database, and is used for receiving and detecting the optimized execution plan; based on the optimized resource allocation parameter and the resource prediction result, dynamically adjust the running parameters of PostgreSQL; in response to a plan injection instruction, select a required execution plan in the dynamic injection module to execute a query task of the optimized PostgreSQL based on the adjusted running parameters and the required execution plan.

[0060] The non-volatile computer storage medium provided by the embodiment of the application stores computer executable instructions, and the computer executable instructions are configured to: acquire query feature data and resource state data corresponding to a database, and perform time sequence processing on the resource state data to obtain a resource prediction result; input the query feature data and the resource state data into a preset AI strategy optimization model to output an optimized execution plan and an optimized resource allocation parameter through the preset AI strategy optimization model; input the optimized execution plan into a dynamic injection module; the dynamic injection module is implemented by database extension, and is configured to receive and detect the optimized execution plan; based on the optimized resource allocation parameter and the resource prediction result, dynamically adjust running parameters of PostgreSQL; in response to a plan injection instruction, select a required execution plan in the dynamic injection module, and execute a query task of the optimized PostgreSQL based on the adjusted running parameters and the required execution plan.

[0061] Each of the embodiments in the application is described in a progressive manner, and the same or similar parts of each of the embodiments can be referred to each other. Each of the embodiments mainly describes the difference from other embodiments. In particular, for the device, equipment and non-volatile computer storage medium embodiments, since they are basically similar to the method embodiments, the description is relatively simple, and the relevant parts can be referred to the part of the method embodiment.

[0062] The above only describes the embodiments of the application and is not used to limit the application. The embodiments of the application can be variously changed and modified by those skilled in the art. The modifications or replacements do not make the corresponding technical solutions deviate from the spirit and scope of the technical solutions of the embodiments of the application.

Claims

1. A method for dynamic database query optimization and resource scheduling, characterized in that, The method includes: Obtain the corresponding query feature data and resource status data from the database, and perform time series processing on the resource status data to obtain resource prediction results; The query feature data and the resource status data are input into a preset AI strategy optimization model, so that the preset AI strategy optimization model can output an optimized execution plan and optimized resource allocation parameters. The optimized execution plan is input into the dynamic injection module; wherein, the dynamic injection module is implemented by extending the database and is used to receive and detect the optimized execution plan; Based on the optimized resource allocation parameters and the resource prediction results, the running parameters of PostgreSQL are dynamically adjusted. In response to the plan injection instruction, the desired execution plan is selected in the dynamic injection module to execute the optimized PostgreSQL query task based on the adjusted running parameters and the desired execution plan; The dynamic adjustment of PostgreSQL's running parameters based on the optimized resource allocation parameters and the resource prediction results specifically includes: If a resource allocation conflict is detected between the optimized resource allocation parameters and the resource prediction results, the conflicting resource difference is determined. If the difference in conflicting resources is greater than a preset conflict threshold, a resource quota is determined based on task priority, and the resource quota is divided into a preset number of adjustment segments. The resource quotas corresponding to each adjustment segment are allocated sequentially, and the resource allocation effect detection strategy is activated after the resource allocation of any adjustment segment is completed. If the resource allocation effect detection strategy corresponding to each of the adjustment segments meets the detection conditions, the dynamic adjustment of the PostgreSQL running parameters is completed. The response plan injection instruction selects the desired execution plan in the dynamic injection module, specifically including: In response to the plan injection instruction, the required function is determined from the preset function set; wherein, the plan injection instruction is marked with a query task identifier; Based on the query task identifier, the required execution plan is selected in the dynamic injection module; wherein, the dynamic injection module includes optimized execution plans corresponding to different query tasks, and each optimized execution plan is marked with a query task identifier; The required function is invoked to inject the required execution plan from the dynamic injection module into the PostgreSQL query planner; Before injecting the required execution plan from the dynamic injection module into the PostgreSQL query planner, the method further includes: The optimized execution plan received by the dynamic injection module is subjected to a feasibility test; wherein the feasibility test includes at least index existence detection, table or view access restriction detection, and parallel execution permission detection; In addition, the execution plan and the preset policy whitelist received by the dynamic injection module are subjected to security checks; In addition, the version information of the PostgreSQL is determined, and a compatibility test is performed on the optimized execution plan and the version information.

2. The database dynamic query optimization and resource scheduling method according to claim 1, characterized in that, The step of performing time series processing on the resource status data to obtain resource prediction results specifically includes: Historical resource status data is divided into historical basic resource characteristic data, historical query load characteristic data, and historical database status characteristic data. Based on the time sequence, the historical data after division is aligned according to the time dimension, and based on the data change values ​​corresponding to the same time, the resource consumption relationship between query load characteristic data, basic resource characteristic data and database status characteristic data is determined. Based on the resource consumption correlations corresponding to different times, a resource consumption correlation sequence is constructed. Based on the resource consumption correlation sequence and the resource status sequence corresponding to the resource status data, the future load situation is predicted to obtain the resource prediction result.

3. The database dynamic query optimization and resource scheduling method according to claim 1, characterized in that, The dynamic adjustment of PostgreSQL's running parameters based on the optimized resource allocation parameters and the resource prediction results specifically includes: Based on the current query task, retrieve historical query tasks with a similarity greater than a preset similarity threshold from the historical database, and determine the historical optimized resource allocation parameters and historical resource prediction results corresponding to the historical query tasks. Based on the allocation parameter types corresponding to different historical query tasks, the historical optimized resource allocation parameters and the historical resource prediction results are divided into parameters. Based on the performance data corresponding to the historical query tasks, the adjustment factors corresponding to each parameter type after the division are determined. Based on the preset fusion weights and the adjustment factors, the optimized resource allocation parameters and the resource prediction results are fused and dynamically adjusted.

4. The database dynamic query optimization and resource scheduling method according to claim 1, characterized in that, After executing the optimized PostgreSQL query task based on the adjusted operating parameters and the required execution plan, the method further includes: Record performance data for the optimized PostgreSQL query tasks; The recorded performance data is compared with historical performance data; If the comparison result shows that the performance degradation value is greater than the preset performance threshold, the original execution plan will be automatically restored through the dynamic injection module.

5. The database dynamic query optimization and resource scheduling method according to claim 4, characterized in that, After comparing the recorded performance data with historical performance data, the method further includes: If the comparison result shows that the performance degradation value is greater than the preset performance threshold, the required execution plan will be marked as an abnormal event. Alternatively, if the comparison result shows that the performance degradation value is not greater than the preset performance threshold, the required execution plan will be marked as a positive event. The abnormal event labels and the positive event labels are used as training samples for the model to iteratively optimize the preset AI strategy optimization model.

6. A database dynamic query optimization and resource scheduling device, characterized in that, The device includes a memory for storing computer program instructions and a processor for executing the program instructions, wherein when the computer program instructions are executed by the processor, the device is triggered to perform the method described in any one of claims 1-5.

7. A non-volatile computer storage medium storing computer-executable instructions, characterized in that, The computer-executable instructions are capable of performing the method described in any one of claims 1-5.

Images