Database dynamic query optimization and resource scheduling method, equipment and medium

Abstract

The embodiment of the invention discloses a database dynamic query optimization and resource scheduling method and device and a medium, belongs to the technical field of databases, and solves the problems that the mode of manually adjusting PostgreSQL resource parameters is low in efficiency and causes resource waste. Obtaining query feature data and resource state data corresponding to the database, and performing time sequence processing on the resource state data to obtain a resource prediction result; inputting the query feature data and the resource state data into a preset AI strategy optimization model to output an optimization execution plan and optimization resource allocation parameters; inputting the optimization execution plan into a dynamic injection module; dynamically adjusting the operation parameters of the PostgreSQL based on the optimized resource allocation parameters and the resource prediction result; and in response to the plan injection instruction, selecting a required execution plan in the dynamic injection module so as to execute the optimized query task based on the adjusted operation parameters and the required execution plan.

Description

Technical Field

[0001] This application relates to the field of database technology, and in particular to a method, device and medium for dynamic database query optimization and resource scheduling. Background Technology

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

[0003] Traditional PostgreSQL query optimizers rely on fixed rules and static statistics to generate execution plans, making it difficult to adapt to dynamically changing query patterns and often resulting in inefficient execution plans. For example, when faced with sudden high-concurrency queries or complex business scenarios, static optimization strategies struggle to adjust key parameters such as index selection and join order in real time, leading to lower query performance.

[0004] Meanwhile, PostgreSQL's resource scheduling relies on manual configuration of parameters such as shared buffers and working memory, making it difficult to dynamically optimize based on real-time load. Therefore, when system load fluctuates, manually adjusting resource parameters is not only inefficient but can also lead to resource waste due to uneven allocation. Summary of the Invention

[0005] This application provides a method, device, and medium for dynamic database query optimization and resource scheduling to solve the following technical problem: manually adjusting PostgreSQL resource parameters is not only inefficient, but also leads to resource waste due to uneven allocation.

[0006] The embodiments of this application adopt the following technical solutions: This application provides a method for dynamic query optimization and resource scheduling in a database. The method includes: acquiring query feature data and resource status data corresponding to the database; performing time-series processing on the resource status data to obtain resource prediction results; inputting the query feature data and resource status data into a pre-set AI strategy optimization model to output an optimized execution plan and optimized resource allocation parameters; inputting the optimized execution plan into a 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; dynamically adjusting the running parameters of PostgreSQL based on the optimized resource allocation parameters and resource prediction results; responding to the plan injection command, selecting the desired execution plan in the dynamic injection module, and executing the optimized PostgreSQL query task based on the adjusted running parameters and the desired execution plan.

[0007] In one implementation of this application, time-series processing is performed on resource status data to obtain resource prediction results. Specifically, this includes: dividing historical resource status data into historical basic resource characteristic data, historical query load characteristic data, and historical database status characteristic data; aligning the divided historical data according to time order, and determining the resource consumption correlation between query load characteristic data, basic resource characteristic data, and database status characteristic data based on the data change values ​​corresponding to the same time; constructing a resource consumption correlation sequence based on the resource consumption correlation sequences corresponding to different times; and predicting future load conditions based on the resource consumption correlation sequence and the resource status sequence corresponding to the resource status data to obtain resource prediction results.

[0008] In one implementation of this application, the PostgreSQL running parameters are dynamically adjusted based on optimized resource allocation parameters and resource prediction results. Specifically, this includes: based on the current query task, retrieving historical query tasks from the historical database with a similarity greater than a preset similarity threshold, and determining the historical optimized resource allocation parameters and historical resource prediction results corresponding to the historical query tasks; classifying the historical optimized resource allocation parameters and historical resource prediction results based on the allocation parameter types corresponding to different historical query tasks; determining the adjustment factors corresponding to each parameter type after classification based on the performance data corresponding to the historical query tasks; and performing fusion processing and dynamic adjustment processing on the optimized resource allocation parameters and resource prediction results based on preset fusion weights and adjustment factors.

[0009] In one implementation of this application, the running parameters of PostgreSQL are dynamically adjusted based on optimized resource allocation parameters and resource prediction results. Specifically, this includes: determining the conflicting resource difference when a resource allocation conflict is detected between the optimized resource allocation parameters and the resource prediction results; determining resource quotas based on task priority when the conflicting resource difference is greater than a preset conflict threshold, and dividing the resource quotas into a preset number of adjustment segments; allocating resource quotas corresponding to each adjustment segment in sequence, and initiating a resource allocation effect detection strategy after completing the resource allocation of any adjustment segment; and completing the dynamic adjustment of the running parameters of PostgreSQL when the resource allocation effect detection strategies corresponding to each adjustment segment meet the detection conditions.

[0010] In one implementation of this application, responding to the plan injection instruction and selecting the required execution plan in the dynamic injection module specifically includes: responding to the plan injection instruction and determining the required function from the preset function set; wherein the plan injection instruction is marked with a query task identifier; filtering the required execution plan in the dynamic injection module based on the query task identifier; 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; calling the required function to inject the required execution plan from the dynamic injection module into the PostgreSQL query planner.

[0011] In one implementation of this application, before injecting the required execution plan from the dynamic injection module into the PostgreSQL query planner, the method further includes: performing a feasibility test on the optimized execution plan received by the dynamic injection module; wherein the feasibility test includes at least index existence detection, table or view access restriction detection, and parallel execution permission detection; and performing a security test on the execution plan received by the dynamic injection module and the pre-defined policy whitelist; and determining the PostgreSQL version information and performing a compatibility test on the optimized execution plan and the version information.

[0012] In one implementation of this application, after executing the optimized PostgreSQL query task based on the adjusted running parameters and the required execution plan, the method further includes: recording performance data of the executed optimized PostgreSQL query task; comparing the recorded performance data with historical performance data; and if the comparison result shows that the performance degradation value is greater than a preset performance threshold, then automatically restoring the original execution plan through the dynamic injection module.

[0013] In one implementation of this application, 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 a preset performance threshold, the required execution plan is labeled with an abnormal event; or, if the comparison result shows that the performance degradation value is not greater than the preset performance threshold, the required execution plan is labeled with a positive event; the abnormal event labels and positive event labels are used as model training samples to iteratively optimize the preset AI strategy optimization model.

[0014] This application provides a database dynamic query optimization and resource scheduling device, including: at least one processor; and a memory communicatively connected to the at least one processor; 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: obtain query feature data and resource status data corresponding to the database, and perform time series processing on the resource status data to obtain resource prediction results; input the query feature data and resource status data into a preset AI strategy optimization model to output an optimized execution plan and optimized resource allocation parameters 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 to receive and detect the optimized execution plan; dynamically adjust the running parameters of PostgreSQL based on the optimized resource allocation parameters and resource prediction results; respond to the plan injection instruction, select the required execution plan in the dynamic injection module, and execute the optimized PostgreSQL query task based on the adjusted running parameters and the required execution plan.

[0015] This application provides a non-volatile computer storage medium storing computer-executable instructions. These instructions are configured to: acquire query feature data and resource status data corresponding to a database, perform time-series processing on the resource status data to obtain resource prediction results; input the query feature data and resource status data into a pre-set AI strategy optimization model to output an optimized execution plan and optimized resource allocation parameters; input the optimized execution plan into a 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; dynamically adjust the PostgreSQL running parameters based on the optimized resource allocation parameters and resource prediction results; respond to the plan injection instruction, select the desired execution plan in the dynamic injection module, and execute the optimized PostgreSQL query task based on the adjusted running parameters and the desired execution plan.

[0016] The at least one technical solution adopted in this application embodiment can achieve the following beneficial effects: By acquiring query features and resource status data and performing time series processing, this application embodiment can accurately obtain dynamic load changes, making resource prediction results more in line with actual needs. Secondly, this application embodiment outputs optimized execution plans and resource parameters through a pre-set AI strategy optimization model, breaking through the limitations of traditional static rules. It can adaptively adjust execution strategies such as index selection and join order, while dynamically matching resource allocation parameters to avoid resource waste or competition. In addition, the dynamic injection module in this application embodiment is implemented in an extended form, which can inject optimization plans without modifying the kernel, ensuring compatibility and flexibility. Furthermore, the adjustment of running parameters based on prediction results and optimization parameters can improve the response efficiency and stability of the database under complex loads. Attached Figure Description To more clearly illustrate the technical solutions in the embodiments of this application or the prior art, the drawings used in the description of the embodiments or the prior art will be briefly introduced below. Obviously, the drawings described below are only some embodiments recorded in this application. For those skilled in the art, other drawings can be obtained based on these drawings without creative effort. In the drawings: Figure 1 A flowchart of a database dynamic query optimization and resource scheduling method provided in this application embodiment; Figure 2 This application provides a schematic diagram of a database dynamic query optimization and resource scheduling process. Figure 3 This is a schematic diagram of the structure of a database dynamic query optimization and resource scheduling device provided in an embodiment of this application.

[0017] Figure label: 200: Database dynamic query optimization and resource scheduling device; 201: Processor; 202: Memory. Detailed Implementation

[0018] This application provides a method, device, and medium for dynamic database query optimization and resource scheduling.

[0019] To enable those skilled in the art to better understand the technical solutions in this application, the technical solutions in the embodiments of this application will be clearly and completely described below with reference to the accompanying drawings. Obviously, the described embodiments are only some embodiments of this application, and not all embodiments. Based on the embodiments of this specification, all other embodiments obtained by those skilled in the art without creative effort should fall within the scope of protection of this application.

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

[0021] Figure 1 A flowchart of a database dynamic query optimization and resource scheduling method provided in this application embodiment is shown below. Figure 1 As shown, the database dynamic query optimization and resource scheduling method includes the following steps: Step 101: 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.

[0022] In one implementation of this application, a data acquisition module collects query feature data from within the database, primarily query statistics (pg_stat_statements) and lock information (pg_locks), to provide query features for the AI ​​model. Additionally, a monitoring data acquisition module collects resource status data from the operating system layer, including CPU utilization, memory usage, I / O throughput, and latency. This data is used for load prediction and also serves as input to the AI ​​model, representing the current resource status of the system.

[0023] In one implementation of this application, 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 chronological order, the divided historical data is aligned along the time dimension, and resource consumption relationships between query load characteristic data, basic resource characteristic data, and database status characteristic data are determined based on data change values ​​corresponding to the same time period. A resource consumption relationship sequence is constructed based on the resource consumption relationship sequence corresponding to different times. Future load conditions are predicted based on the resource consumption relationship sequence and the resource status sequence corresponding to the resource status data, yielding resource prediction results.

[0024] Specifically, the collected historical resource status data is first categorized into at least three types. Historical basic resource characteristic data includes CPU utilization and memory usage; historical query load characteristic data includes query type and table size; and historical database status characteristic data includes database-specific metrics such as shared_buffers hit rate (shared_buffers is a server configuration parameter in PostgreSQL, part of shared memory, primarily used for caching database data pages) and work_mem (work_mem is a key runtime parameter in PostgreSQL, primarily used to control the allocation of working memory during query execution). Then, these three types of data are sampled in chronological order within the same time window, ensuring a one-to-one correspondence between different data types on the timeline. For each time point after time-dimensional alignment, analyze the correlation between changes in query load characteristics and changes in basic resource characteristics and database status characteristics during the same period. For example, when the volume of a certain type of complex query increases, analyze the corresponding changes in work_mem usage and CPU utilization. Calculate the correlation between different query load characteristics and resource consumption characteristics using statistical methods to determine the specific resource consumption relationships.

[0025] Furthermore, based on the resource consumption relationships determined at each time point, these relationships are arranged chronologically to form a resource consumption relationship sequence. Additionally, historical resource status data is arranged chronologically to form a resource status sequence. By combining the constructed resource consumption relationship sequence with the corresponding resource status sequence, future load conditions are predicted, thereby obtaining possible resource consumption patterns over future periods, such as CPU utilization and work_mem requirements.

[0026] The specific process for predicting future load conditions involves aligning the constructed resource consumption correlation sequence with the resource status sequence along the time dimension. For example, the correlation between an increase in multi-table JOIN queries leading to a 20% increase in work_mem consumption within a certain time period is bound to resource status data such as CPU utilization and memory usage during the same period to generate training sample data for the prediction model. An LSTM deep learning model is used to learn the long-term trends and non-linear fluctuations of indicators such as CPU and memory. The resource consumption correlation sequence is used as context input to adjust the prediction weights of the time series model. For example, when the correlation sequence shows that for every 10% increase in complex query volume, work_mem demand increases by an average of 15%, the model will prioritize this pattern when predicting memory demand. The confidence level of resource consumption correlations at different time points is determined, with higher-confidence correlations assigned higher weights and lower-confidence correlations having a weaker impact to avoid prediction bias. Based on a preset time window, multiple future time points are predicted, such as the load situation in the next 30 minutes. After each prediction, a sliding window incorporates new data to ensure that the model is always based on the latest correlation patterns.

[0027] Step 102: Input the query feature data and resource status data into the preset AI strategy optimization model, so as to output the optimized execution plan and optimized resource allocation parameters through the preset AI strategy optimization model.

[0028] In one implementation of this application, the training process of the pre-defined AI strategy optimization model in this embodiment is as follows: Query features and resource status from the data acquisition layer are received as input. Training is performed based on a reinforcement learning framework such as TensorFlow, and the output is the optimized execution plan and optimized resource allocation parameters. The query features and resource status in the input samples may include: SQL statements, execution frequency, average response time, number of returned rows, cache read volume, index usage, join method, parallelism, CPU, memory, I / O utilization, database cache hit rate, and task priority, etc. The output samples may include the historical execution plans and historical resource allocation parameters corresponding to the input samples. Using these input and output samples, training is performed based on the reinforcement learning framework to obtain the pre-defined AI strategy optimization model.

[0029] Step 103: Input the optimized execution plan into the dynamic injection module.

[0030] In one implementation of this application, the dynamic injection module is implemented as a PostgreSQL extension without modifying the kernel source code. This dynamic injection module receives the optimized execution plan generated by the AI ​​model and injects it into the PostgreSQL query planner via a hook or a custom function, thereby influencing the generation of the final execution plan.

[0031] In one implementation of this application, the dynamic injection module is implemented as a PostgreSQL extension, consisting of a communication and interaction component, a plan verification component, a hook control component, and a feedback collection component. It is loaded as a dynamic link library via the `CREATE EXTENSION` command (which loads and installs extension modules, adding new functionality to PostgreSQL without modifying the database kernel). Specifically, the communication and interaction component communicates with the AI ​​strategy optimization model service and receives the optimization execution plan; the plan verification component performs legality and compatibility checks on the optimization execution plan; the hook control component intercepts the query planning process and injects the plan by registering planning-phase hook functions such as `planner_hook`; and the feedback collection component collects execution feedback data for AI strategy optimization model iteration. All components work collaboratively through the PostgreSQL standard interface, completing registration and connection during the initialization phase, sequentially receiving, verifying, transforming, and injecting during the plan injection phase, and collecting feedback during the execution phase to form a closed loop, thus achieving the injection of the optimization execution plan without modifying the kernel.

[0032] Step 104: Based on the optimized resource allocation parameters and resource prediction results, dynamically adjust the running parameters of PostgreSQL.

[0033] In one implementation of this application, based on the current query task, historical query tasks with a similarity greater than a preset similarity threshold are retrieved from the historical database, and the historical optimized resource allocation parameters and 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 optimized resource allocation parameters and historical resource prediction results are parameterized. Based on the performance data corresponding to the historical query tasks, adjustment factors corresponding to each parameter type after partitioning are determined. Based on preset fusion weights and adjustment factors, the optimized resource allocation parameters and resource prediction results are fused and dynamically adjusted.

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

[0035] Furthermore, based on historical query task performance data during actual execution, such as execution time, resource consumption, and response speed, the impact of different parameter types on performance in different scenarios is determined, thereby identifying the adjustment factors corresponding to each parameter type after classification. That is, for each group of parameters, the correlation between its value and performance indicators is analyzed. For example, when the memory parameter `work_mem` increases from 256MB to 512MB, the execution time of the corresponding query task is shortened by 20%, thus determining that `work_mem` has a significant performance impact in this scenario. Therefore, statistical methods are used to calculate the performance impact of different parameter types in different query scenarios such as transaction queries and report queries. Secondly, based on the mapping relationship of the degree of influence, adjustment factors are generated for each group of parameters. For example, if a 10% increase in shared_buffers in a certain type of report query can reduce I / O wait time by 15%, then the adjustment factor for shared_buffers (shared_buffers is a server configuration parameter in the PostgreSQL database, which is part of shared memory and is mainly used to cache database data pages) is set to a positive correlation of 1.5 in this scenario; if excessive allocation of work_mem leads to memory contention and prolongs the execution time, then the adjustment factor is a negative correlation such as -0.8.

[0036] This application's embodiments pre-define fusion weights for different parameter types, reflecting their priority in resource scheduling. Combining the pre-set fusion weights with determined adjustment factors, the current optimized resource allocation parameters and resource prediction results are weighted, fused, and dynamically adjusted. This ensures that the final resource allocation parameters and prediction results better align with the actual needs of the current query task, improving query performance and resource utilization.

[0037] In one implementation of this application, when 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 conflicting resource difference exceeds 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 after the resource allocation of any adjustment segment is completed, a resource allocation effect detection strategy is initiated. If the resource allocation effect detection strategies corresponding to each adjustment segment meet the detection conditions, the dynamic adjustment of the PostgreSQL running parameters is completed. Specifically, the system monitors and optimizes resource allocation parameters and resource prediction results in real time. When there is a discrepancy between the two in resource allocation, the specific difference in conflicting resources is calculated. For example, if the optimized resource allocation parameter sets `work_mem` to 1GB, while the resource prediction result shows a requirement of 1.5GB, the conflict difference is 0.5GB. If the conflict difference exceeds a preset conflict threshold, such as 0.3GB, the total resource quota is determined based on the priority of each query task. For example, query tasks are divided into high-priority, medium-priority, and low-priority tasks, and resource weights are assigned to different priorities, with higher priorities receiving larger resource weights. If an increase in high-priority transaction query concurrency is predicted, the total resource quota is increased proportionally according to the weights, for example, increasing the total `work_mem` from 1GB to 1.5GB, with 60% (900MB) allocated to transaction queries.

[0038] Furthermore, the resource quota is divided into multiple adjustment segments, each corresponding to a different resource allocation ratio or increment, to gradually adjust resource allocation. Following the pre-defined adjustment segment order, the resource quota corresponding to each segment is allocated sequentially. For example, the first segment is allocated 30% of the resource quota, and relevant operating parameters are adjusted. After completing the resource allocation for each segment, a resource allocation effect detection strategy is immediately initiated, such as monitoring query execution time, resource utilization, and other indicators to determine whether the current allocation has improved system performance. The resource allocation effect of each adjustment segment is tested. If the detection strategies for all segments meet the preset detection conditions, the current resource allocation scheme is confirmed to be effective, and the dynamic adjustment of PostgreSQL operating parameters is completed. If a segment fails the test, subsequent allocation is stopped, and the system reverts to the previous effective state, readjusting the allocation strategy.

[0039] Step 105: Respond to the plan injection instruction, select the desired execution plan in the dynamic injection module, and execute the optimized PostgreSQL query task based on the adjusted running parameters and the desired execution plan.

[0040] In one implementation of this application, in response to a plan injection instruction, the required function is determined from a predefined 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 from 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 called, injecting the required execution plan from the dynamic injection module into the PostgreSQL query planner.

[0041] Specifically, upon receiving a plan injection instruction, the instruction is parsed to extract the labeled query task identifier. This identifier uniquely identifies the query task currently requiring processing. The dynamic injection module stores optimized execution plans corresponding to different query tasks, and each execution plan is labeled with a matching query task identifier. Based on the extracted query task identifier, the module performs a search and filtering process to find the optimized execution plan that perfectly matches the identifier, ensuring that the selected plan is the optimal solution tailored to the current query task.

[0042] Furthermore, the pre-defined function set in this embodiment includes various functions for implementing the plan injection function. Based on the specific requirements of plan injection and the interface requirements of the PostgreSQL query planner, specific functions required to perform the injection operation are determined from the function set. These include functions for registering hooks, replacing execution plan nodes, or interacting with the planner; these functions will be used to complete the subsequent plan injection operation. The determined required functions are called, with the selected required execution plan passed as a parameter. Through the execution of the function, the optimized execution plan is injected into the PostgreSQL query planner, thereby updating the final query execution path and optimizing the query task.

[0043] In one implementation of this application, before injecting the required execution plan from the dynamic injection module into the PostgreSQL query planner, the method further includes performing a feasibility check on the optimized execution plan received by the dynamic injection module; wherein the feasibility check includes at least index existence check, table or view access restriction check, and parallel execution permission check. Additionally, a security check is performed on the execution plan received by the dynamic injection module against a pre-defined policy whitelist. Finally, the PostgreSQL version information is determined, and a compatibility check is performed on the optimized execution plan and the version information.

[0044] Specifically, before injecting the required execution plan into the PostgreSQL query planner, a feasibility test is performed on the optimized execution plan received by the dynamic injection module. This test includes: index existence check, verifying whether the indexes involved in the plan actually exist and are valid in the database; table or view access restriction check, confirming that the execution plan's access to the target table or view complies with the database's permission settings and there is no risk of unauthorized access; and parallel execution permission check, determining whether the parallel execution operations involved in the plan comply with PostgreSQL's parallel execution rules and the current system configuration, ensuring that the execution plan can proceed normally in the existing environment. After completing the feasibility test, the execution plan received by the dynamic injection module is compared with a pre-defined policy whitelist for security checks. The pre-defined policy whitelist in this embodiment includes verified legitimate execution operations, query types, and resource access ranges. By checking, execution plans with malicious operations or exceeding the security scope can be filtered out, preventing insecure execution plan injection from posing a risk to the database. The current PostgreSQL database version information is determined, including the major version number, minor version number, and relevant patch versions. The optimization execution plan will be tested for compatibility with the obtained version information. The database features, functions, syntax and parameter configurations involved in the plan will be checked to see if they are compatible with the current PostgreSQL version. For example, some new query syntax or parameters supported by higher versions may not be recognized in lower versions. Such compatibility issues need to be ruled out in advance through testing.

[0045] In one implementation of this application, performance data is recorded for the optimized PostgreSQL query task. The recorded performance data is compared with historical performance data. If the comparison result shows that the performance degradation value is greater than a preset performance threshold, the original execution plan is automatically restored through a dynamic injection module.

[0046] Specifically, performance data is recorded for the optimized PostgreSQL query task. This recorded performance data includes, but is not limited to, key metrics such as query execution time, CPU utilization, memory usage, I / O throughput and latency, and lock wait time. The recorded performance data is compared one by one with pre-stored historical performance data for the corresponding query task, and the difference in changes to each performance metric is calculated. Based on the comparison results, a comprehensive performance degradation value is calculated and compared with a preset performance threshold. If the performance degradation value does not exceed the preset threshold, the current optimized execution plan is deemed effective and no adjustment is needed; if the performance degradation value exceeds the preset threshold, the optimized execution plan is deemed not to have achieved the expected results, and the execution plan recovery process needs to be initiated. When it is determined that the original execution plan needs to be restored, a recovery command is sent to the dynamic injection module. Upon receiving the command, the dynamic injection module retrieves the original execution plan corresponding to the query task from the stored historical execution plans and re-injects the original execution plan into the PostgreSQL query planner through a registered Hook or custom function, replacing the current optimized execution plan. This ensures that subsequent query tasks can be executed according to the original plan, avoiding further performance degradation.

[0047] In one implementation of this application, if the comparison result shows a performance degradation value greater than a preset performance threshold, the required execution plan is labeled with an anomaly event. Alternatively, if the comparison result shows a performance degradation value not greater than the preset performance threshold, the required execution plan is labeled with a positive event. The anomaly and positive event labels are used as model training samples to iteratively optimize the preset AI strategy optimization model.

[0048] Specifically, if the performance degradation value is greater than the preset performance threshold, it indicates that the current execution plan has not achieved the expected results, and it is labeled as an abnormal event. If the performance degradation value is not greater than the preset performance threshold, it indicates that the execution plan optimization is effective, and it is labeled as a positive event. The labeled abnormal and positive event data are used to construct standardized model training samples, which are then input into the preset AI strategy optimization model to initiate the model iterative optimization process.

[0049] Figure 2 This is a schematic diagram illustrating a database dynamic query optimization and resource scheduling process provided in an embodiment of this application. Figure 2As shown, the data acquisition module collects query statistics and lock information from within the database, providing query features for the AI ​​model. The monitoring data acquisition module collects resource metrics from the operating system layer, including CPU utilization, memory usage, I / O throughput, and latency. This data is used for load forecasting and also serves as input to the AI ​​model, representing the current resource status of the system. The AI ​​model training module receives query features and resource status from the data acquisition layer as input. It trains based on a reinforcement learning framework, and its output is an optimized execution plan and optimized resource allocation parameters. The dynamic injection module is implemented as a PostgreSQL extension without modifying the kernel source code. This module receives the optimized execution plan generated by the AI ​​model and injects it into the PostgreSQL query planner through hooks or custom functions, thus influencing the final execution plan generation. The PostgreSQL kernel is the core of the database system, receiving optimization strategies from the dynamic injection module and executing SQL queries. The resource scheduling engine receives two types of instructions: future load forecast results from the load forecasting module and resource parameter optimization suggestions from the AI ​​model. By combining this information, it dynamically adjusts the PostgreSQL runtime parameters to achieve elastic resource allocation. Load forecasting module: Utilizes historical resource indicator data collected from monitoring data collection to predict future load trends through time series analysis, and informs the resource scheduling engine of the forecast results so that resources can be adjusted.

[0050] Figure 3 This is a schematic diagram of the structure of a database dynamic query optimization and resource scheduling device provided in an embodiment of this application. Figure 3 As shown, a database dynamic query optimization and resource scheduling device 200 includes: at least one processor 201; and a memory 202 communicatively connected to the at least one processor 201. The memory 202 stores instructions executable by the at least one processor 201. These instructions, when executed by the at least one processor 201, enable the at least one processor 201 to: acquire query feature data and resource status data corresponding to the database, and perform time-series processing on the resource status data to obtain resource prediction results; input the query feature data and resource status data into a pre-set AI strategy optimization model to output an optimized execution plan and optimized resource allocation parameters; input the optimized execution plan into a dynamic injection module; wherein the dynamic injection module is implemented through database extension and is used to receive and detect the optimized execution plan; dynamically adjust the PostgreSQL running parameters based on the optimized resource allocation parameters and resource prediction results; and, in response to the plan injection instruction, select the desired execution plan in the dynamic injection module to execute the optimized PostgreSQL query task based on the adjusted running parameters and the desired execution plan.

[0051] This application provides a non-volatile computer storage medium storing computer-executable instructions. These instructions are configured to: acquire query feature data and resource status data corresponding to a database, perform time-series processing on the resource status data to obtain resource prediction results; input the query feature data and resource status data into a pre-set AI strategy optimization model to output an optimized execution plan and optimized resource allocation parameters; input the optimized execution plan into a 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; dynamically adjust the PostgreSQL running parameters based on the optimized resource allocation parameters and resource prediction results; respond to the plan injection instruction, select the desired execution plan in the dynamic injection module, and execute the optimized PostgreSQL query task based on the adjusted running parameters and the desired execution plan.

[0052] The various embodiments in this application are described in a progressive manner. Similar or identical parts between embodiments can be referred to mutually. Each embodiment focuses on describing the differences from other embodiments. In particular, the embodiments of apparatus, devices, and non-volatile computer storage media are basically similar to the method embodiments, so the descriptions are relatively simple; relevant parts can be referred to the descriptions of the method embodiments.

[0053] The above descriptions are merely embodiments of this application and are not intended to limit the scope of this application. For those skilled in the art, various modifications and variations can be made to the embodiments of this application. These modifications or substitutions do not cause the essence of the corresponding technical solutions to depart from the spirit and scope of the technical solutions in the embodiments of this 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.

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, 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.

5. The database dynamic query optimization and resource scheduling method according to claim 1, characterized in that, 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.

6. The database dynamic query optimization and resource scheduling method according to claim 5, characterized in that, 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.

7. 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.

8. The database dynamic query optimization and resource scheduling method according to claim 7, 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.

9. 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-8.

10. 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-8.

Images