A database multi-component diagnosis and tuning method and system based on a large language model

Abstract

The purpose of the present application is to provide a database multi-component diagnosis and optimization method and system based on a large language model, in order to overcome the problems of fragmented optimization components, large configuration space, low optimization efficiency and difficulty in continuous optimization in the existing database optimization technology. Specifically, the present application introduces an abnormality-driven optimization decision mechanism to automatically diagnose performance abnormalities occurring during query execution, thereby identifying key optimization components that have a greater impact on performance, and on this basis, combining the knowledge reasoning ability of the large language model to generate optimization decisions, to achieve efficient joint optimization of multiple components under limited optimization budget. At the same time, the present application memorizes and reuses the historical optimization process and execution results, so that the optimization strategy can be continuously improved with the system running, thereby realizing the automation, autonomy and long-term stable optimization of database performance optimization.

Description

Technical Field

[0001] This application relates to the field of database management system technology, and in particular to a method and system for diagnosing and optimizing multiple database components based on a large language model. Background Technology

[0002] Database management systems are widely used in data-intensive business scenarios, primarily for storing, managing, and querying large-scale data. Query processing is one of the core functions of a database system. As business scale continues to expand and query loads become increasingly complex, query execution performance has gradually become a significant factor affecting system responsiveness, resource utilization efficiency, and operating costs.

[0003] In existing technologies, database systems typically optimize query performance through various tuning techniques, including index configuration, system parameter tuning, query optimization hints, and query rewriting. These tuning techniques operate on the storage layer, execution layer, and query optimizer layer of the database system, respectively. Their configuration results collectively influence the generation process of the query execution plan and ultimately determine the system's resource consumption during query execution.

[0004] Existing research and engineering practices have developed various technical solutions for database query tuning. One type of approach focuses on a single tuning component, such as automatic index selection or automatic system parameter adjustment. These methods typically generate optimized configurations for specific tuning components based on rules, statistical models, or machine learning models to improve query execution performance. Another type attempts to jointly optimize multiple tuning components by simultaneously adjusting indexes, parameters, or other configuration items to achieve better overall performance. This type of approach usually employs an iterative trial-and-error approach based on execution feedback, gradually updating the tuning strategy and configuration scheme after multiple actual query executions. Furthermore, with the development of large language model technology, some technologies are attempting to leverage its knowledge reasoning capabilities to directly generate database tuning suggestions, reducing manual intervention and the number of explorations during the tuning process, thereby assisting in database performance optimization.

[0005] The aforementioned background technologies still have certain shortcomings in practical applications. First, optimization methods targeting a single tuning component fail to consider the interactions between different tuning components, easily leading to local optimization results that fail to improve overall query performance. Second, multi-component joint tuning methods typically face the problem of a large configuration combination space, relying on a large number of real query executions for trial and error, resulting in high tuning costs and low efficiency. Furthermore, existing methods generally rely on frequent execution feedback, requiring re-exploration when workloads change, limiting their generalization ability. Tuning methods based on large language models are prone to convergence to suboptimal solutions in complex configuration spaces and lack effective feedback utilization and continuous optimization mechanisms, making it difficult to achieve stable and long-term tuning results. Current technologies cannot yet achieve efficient, stable, and sustainably evolving database multi-component performance optimization with limited tuning overhead. Summary of the Invention

[0006] This application provides a database multi-component diagnostic and optimization method and system based on a large language model to solve the problems mentioned in the background art.

[0007] Firstly, this application provides a database multi-component diagnostic and optimization method based on a large language model, including: Multimodal context collection is performed on the target query. Through collaborative reasoning between a lightweight diagnostic model and a diagnostic agent, the set of tuning components related to the performance bottleneck of the query is automatically identified, thereby reducing the full configuration space to an abnormal local tuning subspace. Based on historical tuning experience and structured prompts, candidate tuning actions are generated using a tuning agent, and the syntax and semantics are verified by a verification module. Finally, the tuning actions are executed in the database system and execution feedback is collected. The query context, the local tuning subspace of anomalies, tuning actions and their execution feedback are uniformly constructed into tuning observation records and stored in the tuning history database; Based on the historical tuning database, the anomaly detection model and historical tuning retrieval strategy are updated online to continuously improve the accuracy of anomaly localization and the effectiveness of tuning decisions, thereby constructing an adaptive and evolving tuning system.

[0008] Furthermore, it also includes: Each tuning component is quantitatively evaluated through a data-driven tuning component scoring mechanism; A knowledge-driven anomaly reasoning mechanism is introduced, which combines domain diagnostic knowledge with historical optimization trajectories to verify, correct, and enhance the confidence of component scoring results. By using an adaptive component exploration mechanism, the system is guided to dynamically explore potential unverified components while ensuring the stability of the tuning process, thus avoiding getting stuck in local optima.

[0009] Furthermore, the data-driven tuning component scoring mechanism includes: The diagnostic agent first uses a data-driven tuning component scoring mechanism to quickly filter tuning components that may degrade query performance; Using the context information related to query execution as input, a lightweight prediction model outputs the potential contribution of each tuning component to performance optimization, providing a priori basis for subsequent tuning space positioning. The diagnostic model employs a multimodal feature encoding and fusion mechanism to jointly model different types of query context information; After each feature is represented by its corresponding encoding module, cross-modal fusion is performed to finally form a unified feature representation for component scoring.

[0010] Furthermore, the knowledge-driven anomaly reasoning mechanism includes: The diagnostic agent has built-in domain diagnostic rules for different tuning components. The domain diagnostic rules take query context information as input and perform consistency verification on the component scoring results. The diagnostic agent compares the prediction results of the domain diagnostic rules with those of the component scoring model. When the prediction results match the domain diagnostic rules, the anomaly confidence of the corresponding component is enhanced. When there is a significant conflict between the two, the prediction results are adjusted.

[0011] Furthermore, the adaptive component exploration mechanism includes: In each round of optimization decision-making, the system adaptively determines whether to enter the exploration state based on the historical optimization benefits and number of optimization attempts of the target optimization component. When entering the exploration state, similar queries are retrieved from the historical tuning library based on query context similarity, and tuning component combinations that have not yet been fully explored are selected as new tuning objects. If no suitable candidate is found, a random selection is made from the remaining combinations of components that have not been fully explored.

[0012] Furthermore, it also includes: In the process of generating optimization actions, the optimization process is formalized as a Markov decision process to characterize the interaction between historical experience retrieval and optimization action generation. The maximum entropy reinforcement learning framework is used to optimize the historical experience retrieval strategy, and the Soft Q-learning mechanism is introduced to model the long-term value of historical experience. For each historical optimization experience, a conditional value function is learned to evaluate its contribution to future optimization gains under a given state. Based on this value evaluation, the retrieval probability of historical experiences is normalized using the Softmax method.

[0013] Furthermore, it also includes: Using the target optimization component set output from the anomaly diagnosis phase as a constraint, and combining historical optimization observations and execution feedback, the original optimization space is structurally split and inefficient regions are eliminated, thereby forming a compact, controllable, and high-yield optimization subspace. After completing the historical experience retrieval and optimization subspace construction, the optimization agent generates candidate optimization actions based on the current query context, the target optimization component set, historical optimization observation data, and optimization subspace constraints. During the optimization process, the optimization agent combines the results of previous optimization actions to analyze the reasons for optimization success or failure, and dynamically adjusts subsequent optimization strategies accordingly.

[0014] Secondly, this application provides a database multi-component diagnostic and optimization system based on a large language model, including: The component query module is used to collect multimodal context for the target query. Through collaborative reasoning between a lightweight diagnostic model and a diagnostic agent, it automatically identifies the set of optimization components related to the performance bottleneck of the query, thereby reducing the full configuration space to an abnormal local optimization subspace. The action generation module is used to generate candidate optimization actions based on historical optimization experience and structured prompts using the optimization agent, and to perform syntax and semantic verification through the verification module. Finally, the optimization actions are executed in the database system and execution feedback is collected. The observation record module is used to uniformly construct the query context, the abnormal local tuning subspace, the tuning action and its execution feedback into a tuning observation record and store it in the tuning history database; The optimization and update module is used to update the anomaly detection model and historical optimization retrieval strategies online based on the optimization history database, thereby continuously improving the accuracy of anomaly localization and the effectiveness of optimization decisions, and building an adaptive evolution optimization system.

[0015] Thirdly, this application provides a terminal device, including a memory, a processor, and a computer program stored in the memory and executable on the processor. When the processor executes the computer program, it implements the database multi-component diagnostic and optimization method based on a large language model as described above.

[0016] Fourthly, this application provides a computer-readable storage medium storing a computer program that, when executed by a processor, implements the database multi-component diagnostic and tuning method based on a large language model as described above.

[0017] The above-mentioned technical solution of this application has the following advantages: The first aspect of this application provides a database multi-component diagnostic and tuning method based on a large language model. By introducing an anomaly-driven tuning decision mechanism, it automatically diagnoses performance anomalies occurring during query execution, thereby identifying key tuning components with significant performance impact. Based on this, it combines the knowledge reasoning capabilities of the large language model to generate tuning decisions, achieving efficient joint optimization of multiple components within a limited tuning budget. Simultaneously, by memorizing and reusing historical tuning processes and execution results, the tuning strategy can be continuously improved as the system runs, thus achieving automated, autonomous, and long-term stable optimization of database performance.

[0018] It is understood that the beneficial effects of the second, third and fourth aspects mentioned above can be found in the relevant descriptions in the first aspect above, and will not be repeated here. Attached Figure Description

[0019] To more clearly illustrate the technical solutions in the specific embodiments of this application or the prior art, the drawings used in the description of the specific embodiments or the prior art will be briefly introduced below. Obviously, the drawings described below are some embodiments of this application. For those skilled in the art, other drawings can be obtained from these drawings without creative effort.

[0020] Figure 1 A flowchart of a database multi-component diagnostic and optimization method based on a large language model provided in this application embodiment; Figure 2 This is a performance comparison chart on a PostgreSQL database provided in an embodiment of this application; Figure 3 This is a performance comparison chart on the TiDB database provided in an embodiment of this application. Detailed Implementation

[0021] In the following description, specific details such as particular device structures and techniques are set forth for illustrative purposes and not for limitation, in order to provide a thorough understanding of the embodiments of this application. However, those skilled in the art will understand that this application may also be implemented in other embodiments without these specific details. In other instances, detailed descriptions of well-known devices, apparatuses, circuits, and methods have been omitted so as not to obscure the description of this application with unnecessary detail.

[0022] It should be understood that, when used in this application specification and the appended claims, the term "comprising" indicates the presence of the described features, integrals, steps, operations, elements and / or components, but does not exclude the presence or addition of one or more other features, integrals, steps, operations, elements, components and / or a collection thereof.

[0023] Furthermore, in the description of this application and the appended claims, the terms "first," "second," "third," etc., are used only to distinguish descriptions and should not be construed as indicating or implying relative importance.

[0024] References to "one embodiment" or "some embodiments" in this specification mean that one or more embodiments of this application include a specific feature, structure, or characteristic described in connection with that embodiment. Therefore, the phrases "in one embodiment," "in some embodiments," "in other embodiments," "in still other embodiments," etc., appearing in different parts of this specification do not necessarily refer to the same embodiment, but rather mean "one or more, but not all, embodiments," unless otherwise specifically emphasized. The terms "comprising," "including," "having," and variations thereof mean "including but not limited to," unless otherwise specifically emphasized. "A plurality" means "two or more."

[0025] The specific embodiments of this application will be described in further detail below with reference to the accompanying drawings and examples. The following examples are used to illustrate this application, but are not intended to limit the scope of this application.

[0026] This application provides a database multi-component diagnostic tuning method based on a large language model. This method constructs an autonomous tuning system with multiple functional modules working collaboratively. It establishes a closed-loop autonomous tuning process that starts with anomaly identification, centers on tuning subspace constraints, uses multi-component joint tuning actions as the execution carrier, and drives continuous model updates through tuning result feedback. This achieves unified, adaptive, and continuous optimization control of the database's multiple tuning components, realizing a highly efficient, high-return, and low-trial-error-cost automatic tuning process within a limited tuning budget. The overall flowchart is as follows: Figure 1 As shown.

[0027] This application comprises five core modules: The overall technical approach of this application includes the following three core stages: 1. Anomaly-driven tuning space localization stage: Multimodal context collection is performed on the target query. Through collaborative reasoning between a lightweight diagnostic model and a diagnostic agent, the set of tuning components related to the performance bottleneck of the query is automatically identified, thereby reducing the full configuration space to an anomaly-specific local tuning subspace.

[0028] 2. Optimization action generation and execution stage for local subspaces: Based on historical optimization experience and structured prompts, candidate optimization actions are generated using the optimization agent, and the syntax and semantics are verified by the verification module. Finally, the optimization actions are executed in the database system and execution feedback is collected.

[0029] 3. Self-evolutionary stage based on memory and feedback: The query context, the local tuning subspace of anomalies, tuning actions and their execution feedback are uniformly constructed into tuning observation records and stored in the tuning history database; Based on the tuning history database, the anomaly detection model and historical tuning retrieval strategies are updated online, thereby continuously improving the accuracy of anomaly localization and the effectiveness of tuning decisions, and constructing an adaptive evolution tuning system.

[0030] I. Optimization Space Location for Anomaly-Driven Tuning This application first uses a data-driven optimization component scoring mechanism to quantitatively evaluate each optimization component. On this basis, a knowledge-driven anomaly reasoning mechanism is introduced, which combines domain diagnostic knowledge and historical optimization trajectory to verify, correct and enhance the confidence of the component scoring results. Furthermore, through an adaptive component exploration mechanism, the system is guided to dynamically explore potential undervalidated components while ensuring optimization stability, so as to avoid the optimization process from getting stuck in local optima.

[0031] Data-driven tuning component scoring mechanism The diagnostic agent first employs a data-driven component scoring mechanism to quickly filter out components that might degrade query performance. This mechanism takes query execution-related contextual information as input and outputs the potential contribution of each component to performance optimization through a lightweight predictive model, providing prior information for subsequent optimization space localization.

[0032] (a) Component scoring model For detected slow queries, the system collects multi-source context information generated during their execution and organizes it into a unified query context for component optimization scoring. The query context includes the following four types of information: 1. Query Statement Information: The query statement is represented in structured text form, reflecting the semantic intent of the query, the tables involved, and attribute information. This information can reveal whether the query structure contains redundant calculations, unreasonable expressions, or potential query rewriting space.

[0033] 2. Execution Plan Information: The corresponding execution plan contains the type of each physical operator, the execution order, and related cost statistics. This information can help identify potential inefficiencies in the execution path, such as unreasonable join orders, missing indexes, or suboptimal access methods.

[0034] 3. Internal Operational Statistics: Internal statistics are automatically collected by the database system during query execution. They characterize query behavior at the operator level, including data row access, disk read / write activity, and cache hit rates. This information directly reflects the query's consumption of system resources and is a crucial basis for parameter and index tuning.

[0035] 4. External performance monitoring information: External monitoring information is used to describe the overall operating status of the system during query execution. It is usually recorded in time series form as indicators such as CPU utilization, memory utilization and I / O throughput.

[0036] Building upon this, the diagnostic model employs a multimodal feature encoding and fusion mechanism to jointly model the aforementioned different types of query context information. Each feature is represented through its corresponding encoding module, and then fused across modalities to ultimately form a unified feature representation for component scoring. Based on this representation, the system outputs a corresponding score for each type of optimization component, reflecting the component's impact on current query performance.

[0037] (ii) Continuous update mechanism During the tuning process, the system uses the tuning results to update the component scoring model online. The update process also incorporates reliable feedback constraints and query similarity constraints to improve the learning stability of the model under incomplete feedback conditions.

[0038] First, after performing tuning actions, the system compares the changes in query performance before and after tuning and converts the tuning results into component-level monitoring signals. If at least one tuning operation on a certain component can bring about a clear performance improvement, the component is marked as a verified performance bottleneck component; if no significant performance improvement is produced, the tuning result of the component is marked as unverified.

[0039] Secondly, the system determines the credibility of feedback based on the number of optimization attempts. For each query and optimization component, the system continuously records the corresponding number of optimization attempts. Only when the number of attempts for a component reaches a preset minimum exploration threshold is its optimization result considered sufficiently credible. For optimization results that reach the exploration threshold, the system uses them as valid monitoring signals; for optimization failures that do not reach the exploration threshold, the system excludes them through a masking mechanism, thereby avoiding misleading the model due to insufficient exploration.

[0040] During the model parameter update phase, based on the masking mechanism mentioned above, the system calculates the classification error only for credible component-level feedback, and adjusts the prediction results of the corresponding components in the component scoring model accordingly, so that the model can gradually strengthen its ability to identify verified bottleneck components.

[0041] Meanwhile, to further utilize weak feedback information and improve the model's generalization ability, the system introduces a comparison and update mechanism based on query similarity. The system performs representation learning on the query context and constrains the relative relationships of the model in the representation space based on the similarity of queries in semantic structure, execution features, and performance metrics: for query samples with the same bottleneck components and consistent tuning results, the system guides the model to reduce their internal representation differences; for query samples with different bottleneck components or opposite tuning results, the system guides the model to increase their representation differences. Through this approach, even in the absence of explicit negative feedback, the model can still gradually form stable component discrimination boundaries by leveraging the relative relationships between queries.

[0042] Knowledge-driven anomaly reasoning mechanism Building upon data-driven component scoring results, this application further introduces a knowledge-driven anomaly reasoning mechanism to verify and correct the anomaly judgment results of the optimized components, thereby improving the stability and interpretability of the anomaly localization results. This anomaly reasoning mechanism comprehensively utilizes domain diagnostic knowledge and historical optimization trajectory information to perform secondary reasoning on the confidence of the optimized components output by the component scoring model, avoiding misjudgments caused by relying solely on model prediction results.

[0043] (i) Anomaly Detection Reasoning Based on Domain Knowledge The diagnostic agent incorporates domain-specific diagnostic rules for different tuning components to characterize the typical manifestations of common performance bottlenecks during query execution. These diagnostic rules take query context information as input and perform consistency checks on component scoring results, specifically including but not limited to the following scenarios: 1. Regarding the index component, when a full table scan or sequential scan occurs during query execution, and the system exhibits high computational resource usage while disk I / O pressure is relatively moderate, the system is more likely to determine that the index is missing or improperly used. 2. Regarding the execution plan component, when the query execution plan contains a complex join structure, an unreasonable join order, or a cardinality estimate that deviates significantly from the actual execution situation, the system determines that the execution plan selection is highly likely to be inappropriate. 3. Regarding database parameter components, when resource usage is uneven during system operation, such as low parallel resource utilization or abnormal CPU or I / O metrics, the system is more likely to determine that the database parameter configuration is unreasonable. 4. Regarding the query structure, when there are repeated calculations, redundant aggregation operations, or unoptimized subquery structures in the query, the system determines that there is a high probability that the query structure has room for optimization.

[0044] The diagnostic agent compares the domain knowledge rules with the prediction results of the component scoring model. When the domain diagnostic rules and the prediction results of the component scoring model are consistent, the anomaly confidence of the corresponding component is enhanced. When there is a significant conflict between the two, the system adjusts the model prediction results, thereby improving the reliability of the anomaly localization results.

[0045] (ii) Confidence adjustment mechanism based on optimized trajectory In addition to domain knowledge, this application further utilizes historical tuning trajectories to dynamically correct anomaly detection results. During the tuning process, the system continuously records the attempts made for each query and its corresponding tuning component, including the number of successful and failed tunings. The diagnostic agent adjusts the component anomaly confidence level according to the following rules: 1. When a certain tuning component has generated positive performance improvements multiple times in the historical tuning process and has no tuning failure record, the system increases the confidence of the component as a performance bottleneck; 2. When a certain tuning component fails to produce significant performance improvement in multiple tuning attempts, the system gradually reduces the confidence in that component and guides the subsequent tuning process to explore other candidate tuning components; 3. When a certain tuning component has both successful tuning records and failed tuning records, the system maintains the current confidence level and retains the possibility of utilization and exploration in subsequent tuning processes.

[0046] The diagnostic agent comprehensively utilizes the output results of the component scoring model, the domain knowledge verification results, and the optimization trajectory feedback to perform unified reasoning and fusion on the abnormal confidence of the optimization components, and finally determines the set of target optimization components that are highly correlated with the current query performance.

[0047] Adaptive component exploration mechanism To avoid getting stuck in local optima by focusing on identified tuning components for a long time during the tuning process, this application introduces an adaptive component exploration mechanism to dynamically balance the use of existing diagnostic results with the exploration of new tuning directions during the tuning process.

[0048] In each round of optimization decisions, the system adaptively determines whether to enter the exploration state based on the historical optimization benefits and number of optimization attempts of the target component. When entering the exploration state, the system no longer relies entirely on the current abnormal local optimization subspace, but instead retrieves similar queries from the historical optimization database based on query context similarity, and prioritizes selecting combinations of optimization components that have not yet been fully explored as new optimization targets; if no suitable candidates are found, random selection is made from the remaining combinations of components that have not been fully explored.

[0049] As the number of optimization attempts accumulates and the failure rate increases, the system gradually increases its exploratory tendency, achieving a smooth transition from a utilization-oriented approach to an exploration-oriented one. Through this mechanism, the system can expand the optimization space while ensuring optimization stability, and improve its ability to discover potential bottleneck components and multi-component interaction problems.

[0050] II. Optimization Action Generation and Execution After the diagnostic agent determines the target set of optimization components, this application uses an optimization agent to generate and execute specific optimization actions within a defined optimization subspace, thereby achieving collaborative optimization control of multiple database components. The optimization agent adopts a three-stage workflow of "historical experience retrieval—optimization subspace construction—optimization action generation and verification" to balance optimization effectiveness and execution security.

[0051] Adaptive experience retrieval mechanism based on tuning history In the process of generating optimization actions, the quality of the selection of historical optimization experience directly affects the effectiveness of optimization decisions. To address this, this application proposes an adaptive optimization experience retrieval mechanism based on reinforcement learning. This mechanism models the optimization history retrieval process as a sequential decision problem, dynamically selecting historical experiences that offer the greatest long-term benefits to the current optimization decision through learning, rather than relying on static similarity matching.

[0052] (a) Optimizing historical retrieval model This application formalizes the optimization process as a Markov Decision Process (MDP) to characterize the interaction between historical experience retrieval and optimization action generation. Specifically: 1. State: Consists of the current query context and the identified abnormal tuning components, denoted as s=(Q,B), where Q represents the query context and B represents the set of bottleneck components currently identified; 2. Retrieval Action: This indicates the selection of a historical optimization observation sample from the optimization history database to assist in the current optimization decision; 3. Reward: Determined by the reduction in query execution time resulting from the implementation of corresponding optimization actions, reflecting the actual value of this historical experience for current decision-making; 4. State transition: Implicitly determined by the change in system state after the optimization action is executed.

[0053] Under this modeling framework, the selection of optimization history is no longer a one-time retrieval operation, but a key decision-making step that affects subsequent optimization results and long-term benefits.

[0054] (II) Learning Experience Retrieval Strategies Based on Soft Q-learning To balance retrieval optimization and retrieval diversity, this application employs a maximum-entropy reinforcement learning framework to optimize historical experience retrieval strategies and introduces a SoftQ-learning mechanism to model the long-term value of historical experience.

[0055] The system learns a conditional value function for each historical optimization experience to evaluate its contribution to future optimization gains under a given state. Based on this value evaluation, the retrieval probability of historical experiences is normalized using a softmax method, thereby achieving the following objective: Prioritize retrieving historical experiences with high long-term returns to guide the optimization process toward performance improvement. Entropy regularization encourages search diversity and prevents the optimization process from focusing too early on a small number of historical patterns, which could lead to a degradation of the search space.

[0056] Compared to retrieval methods based on static similarity, this strategy can consider both "whether they are similar" and "whether they are still valid" simultaneously, which is more in line with the actual characteristics of dynamic changes in the state of database systems.

[0057] (III) Continuous learning and online updating based on the optimized trajectory During the model training phase, the system first utilizes a static retrieval strategy to collect offline tuning trajectories to initialize the value assessment network, thus avoiding the cold start problem in the online phase. Based on this, the system continuously collects new tuning trajectories during the actual tuning process and continuously refines the value assessment of historical experience through temporal-difference learning. Simultaneously, according to the tuning pattern provided during the anomaly detection phase, the system divides historical experience into local experience sets and global experience sets. In the utilization mode, priority is given to retrieving local experience related to the current bottleneck component to support fine-tuning; In Exploration Mode, global experience across components is introduced to expand the scope of tuning searches and discover potential new tuning paths.

[0058] During the online decision-making phase, the system selects only a small number of high-value historical experiences as contextual inputs for optimizing the agent, thus controlling inference overhead while ensuring decision quality. As optimization feedback accumulates, the experience retrieval strategy continuously self-corrects, thereby gradually improving its adaptability to different query scenarios.

[0059] Target component-oriented optimization subspace construction and pruning To address the issues of high-dimensionality tuning parameters, large combination space, and low efficiency of blind search in database tuning, this application proposes a method for constructing and pruning tuning subspaces oriented towards target tuning components. This method uses the set of target tuning components output during the anomaly diagnosis phase as constraints, and combines historical tuning observations and execution feedback to structurally split the original tuning space and remove inefficient regions, thereby forming a compact, controllable, and highly profitable tuning subspace.

[0060] (I) Construction of the tuning subspace for the target component The system constructs a corresponding tuning subspace based on the currently determined set of target tuning components. Specifically: For the index tuning component, the system includes all table attributes and their combinations in the candidate index space, ensuring that no redundant indexes are introduced. For the query rewriting component, the system uses a predefined set of query rewriting rules to filter rewriting rules that match the current query structure through structure matching. For the database parameter tuning component and optimizer hint component, the system automatically collects all adjustable parameters, their value ranges, default configurations, and current running status from the database management system to form the corresponding basic tuning space.

[0061] (ii) Subspace pruning mechanism based on historical optimization feedback After completing the construction of the basic optimization subspace, the system further combines historical optimization observation data and execution feedback to perform targeted pruning of each optimization subspace in order to eliminate optimization candidates with long-term low returns or ineffectiveness.

[0062] For discrete tuning components (including indexes and query rewrite rules), the system statistically analyzes the average performance improvement brought by each candidate configuration during historical tuning processes and identifies candidates that do not reach a preset benefit threshold as inefficient configurations. In subsequent tuning processes, such inefficient configurations will be removed from the corresponding tuning subspace, thereby avoiding repeated attempts at tuning solutions that have already been proven ineffective.

[0063] For continuously tuned components (including database parameters and optimizer hints), the system evaluates the average performance gains of different parameter values ​​or ranges based on historical tuning observation data. When historical samples indicate significant differences in gains between different values, the system retains the high-gain values ​​or ranges and prunes the original parameter space; when the gains are not significant or the sample size is insufficient, the system retains the complete range of parameter values ​​to avoid prematurely shrinking the search space. Through this method, adaptive compression of the search space for continuously tuned components is achieved while ensuring the reliability of the tuning results.

[0064] After pruning the subspaces for each component's tuning, the system combines the pruned subspaces corresponding to all target tuning components to construct the final global tuning search space. This space is structurally significantly smaller than the original tuning space and focuses on tuning regions that have been historically validated or have the potential for high returns.

[0065] Optimization action generation and verification execution mechanism After completing historical experience retrieval and optimization subspace construction, the optimization agent generates candidate optimization actions based on the current query context, the target optimization component set, historical optimization observation data, and optimization subspace constraints. During the optimization process, the optimization agent analyzes the reasons for optimization success or failure based on the execution results of previous optimization actions, and dynamically adjusts subsequent optimization strategies accordingly.

[0066] The generated tuning actions are represented in a structured form, clearly defining the specific tuning type and execution content, and ensuring compliance with the syntax and execution specifications of the target database system. Before execution, the system performs syntax validity, executability, and semantic consistency checks through a verification module. For query rewriting actions, the system ensures that the query semantics and results are consistent before and after rewriting. If the verification fails, the system will attempt to fix the issue within a limited number of attempts, and if necessary, revert to the original configuration to ensure system correctness.

[0067] After the optimization action is executed, the system collects performance feedback and marks the optimization results. The relevant query context, optimization action, component information and execution feedback are uniformly stored in the historical experience pool to support subsequent experience retrieval and strategy updates, so as to realize the continuous optimization and self-evolution of the optimization process.

[0068] The key technical point of this application is: 1. An anomaly-driven optimization subspace localization mechanism is adopted, which combines data-driven model scoring and knowledge-driven reasoning. A lightweight predictive model is used to estimate the component bottleneck probability. Combined with domain diagnostic rules and optimization trajectory feedback, accurate localization is achieved. At the same time, an adaptive exploration strategy is introduced to avoid local optima and significantly reduce the search space.

[0069] 2. Design a multi-agent collaborative framework to decouple the tuning task into diagnosis and optimization phases. The diagnostic agent is responsible for identifying the tuning components, the tuning agent generates the configuration scheme, and the agent verifies the syntactic and semantic correctness of the tuning actions. The three work together to improve the effectiveness and robustness of tuning.

[0070] 3. Construct a three-step optimization action recommendation process: First, adaptively retrieve historical experience based on the optimization target component and query context; then, construct a streamlined optimization subspace through component-specific pruning strategies; finally, analyze historical optimization actions using a reflective reasoning mechanism to generate structured optimization actions compatible with the current database system.

[0071] 4. A memory-aware evolutionary mechanism is proposed, which can achieve feedback-driven optimization without fine-tuning a large language model: the anomaly detection capability of the diagnostic agent is continuously optimized through hybrid supervision (masked binary classification cross-entropy loss + contrastive loss); the utility-aware retrieval system is trained based on reinforcement learning, and the most valuable historical experience for the current optimization is recalled first.

[0072] 5. Supports unified tuning of four major components: index, knob, optimizer hints, and query rewriting. Through component-specific base space construction and pruning strategies, it balances comprehensive tuning with search efficiency and naturally captures the interaction effects between components.

[0073] This application underwent systematic experimental verification under typical analytical database workload scenarios. The experiments were conducted on the open-source database system PostgreSQL, deployed in a cloud server environment with 16 vCPUs and 64GB of memory. The database was initially configured with the officially recommended default parameters, with only a primary key index created. An optimization process was implemented for queries with execution times exceeding 1 second, with a timeout of 5 minutes per query and a total optimization time budget of 30 hours. The overall workload execution time was used as the primary evaluation metric. The actual effectiveness of this application was evaluated by comparing performance changes before and after optimization and by comparing results with existing technologies. The experimental results are as follows: Figure 2 As shown.

[0074] (i) Significantly improve overall database performance and reduce workload execution time. This application achieved optimal or near-optimal performance on typical analytical benchmark workloads such as JOB, TPC-H SF10, and TPC-DS SF50. Experimental results show that: For JOB workloads, compared to the second-best method Proto-X, this application further reduces the overall workload execution time by 20.9%; On TPC-H workloads, compared to the second-best method D-BOT, the overall workload execution time is reduced by 32.5%; On the TPC-DS workload, the overall workload execution time is reduced by 9.8% compared to the second-best method, D-BOT.

[0075] The experimental results above demonstrate that this application can stably discover high-yield optimization combinations in multiple scenarios, significantly improving the overall operating performance of the database system.

[0076] (ii) Significantly improve tuning efficiency and shorten tuning convergence time. Figure 2 Furthermore, it can be observed that this application has a significant advantage in terms of the time required to achieve stable performance improvement. Experimental results show that: On JOB workloads, the tuning time required to achieve optimal performance in this application is 2.13 times shorter than that of the second-best method, Proto-X. On the TPC-H workload, the tuning time is reduced by 18.52 times compared to the second-best method, D-BOT. On the TPC-DS workload, the tuning time is reduced by 1.2 times compared to the second-best method, D-BOT.

[0077] The results show that this application can quickly converge to a high-quality tuning configuration under strict time budget conditions, significantly reducing the trial and error cost in the tuning process, and is suitable for online or quasi-online tuning scenarios in actual production environments.

[0078] (III) Comprehensive optimization effect and system adaptability analysis A comprehensive analysis of the experimental process and optimization results shows that this application effectively alleviates the search efficiency degradation caused by the high-dimensional optimization space by jointly modeling and adaptively scheduling multiple optimization components. It can also identify and combine various collaborative optimization methods for queries with long execution times and complex structures, thereby achieving more stable performance improvements. Furthermore, this application introduces a historical optimization memory and online update mechanism, enabling the system to continuously adjust its decision-making strategy based on actual optimization feedback, avoiding repeated ineffective attempts and gradually improving optimization quality over long-term operation. Compared to existing technologies that rely on single-inference or pure trial-and-error search, this application demonstrates significant advantages in optimization stability, adaptability, and long-term evolution capabilities.

[0079] The following is a description through specific embodiments.

[0080] Example 1: PostgreSQL Database Tuning Example Based on TPC-DS Workload 1. Experimental environment: Database system: PostgreSQL Hardware environment: 16 vCPUs, 64GB RAM Workload: TPC-DS SF50 (99 queries) Initial configuration: Database default parameters, only primary key index is created. 2. Optimization potential: The query-level parameter tuning space includes 27 PostgreSQL parameters, such as max_parallel_workers and work_mem, which are used to support fine-grained tuning of query execution resources and operator behavior.

[0081] The optimizer hint tuning space includes 11 PostgreSQL hint parameters, such as enable_hashagg and enable_sort, which provide explicit control over the query execution plan through the PostgreSQL hint mechanism.

[0082] Index tuning encompasses both single-column indexes and multi-column composite indexes, and incorporates existing index information in the current database during the tuning process to avoid creating redundant indexes.

[0083] The query rewrite optimization space is built based on a predefined set of query rewrite rules, which consists of 70 rules and are accompanied by semantic descriptions. These rules guide the system to generate multiple equivalent query rewrite forms while maintaining semantic equivalence.

[0084] 3. Experimental Procedure 1) The system automatically monitors query execution and identifies queries that take more than 1 second to execute; 2) Analyze query execution characteristics using the spatial positioning module to identify the set of target tuning components related to performance bottlenecks; 3) Under the constraints of the tuning space, the tuning agent generates candidate tuning actions based on historical tuning memory. The tuning actions include index creation or deletion, database parameter adjustment, and execution plan prompt settings. 4) Before the optimization action is executed, the validation module verifies the syntactic validity and semantic consistency of the optimization action; 5) Execute the optimization actions that pass the verification and collect the corresponding query execution performance feedback information; 6) Store the optimization actions and their results in the historical optimization database, and repeat the above process within the optimization time budget until the preset optimization limit is reached.

[0085] 4. Experimental Results Experimental results show that, compared with the initial configuration without optimization, the overall execution time of the TPC-DS workload is reduced by approximately 32.5% after adopting the method of this embodiment. At the same time, under the condition of achieving the same optimization effect, the optimization time is shortened by approximately 18.52 times compared with the comparison method, which verifies the efficiency and engineering practicality of this embodiment in multi-component joint optimization scenarios.

[0086] Example 2: TiDB database tuning example based on TPC-H workload under cold start conditions 1. Experimental Environment Database system: TiDB v8.5.3 (1 TiDB node, 1 PD node, 1 TiFlash node, and 3 TiKV nodes) Hardware environment: 16 vCPUs, 64GB RAM Workload: TPC-H SF10 (22 queries) Initial configuration: Database default parameters, only primary key index is created. 2. Optimization potential: The query-level parameter tuning space includes 19 TiDB parameters, such as tidb_index_join_batch_size; The optimizer hint tuning space includes 11 TiDB Hint parameters, such as tidb_opt_enable_hash_join, which are injected through TiDB's Hint mechanism; The index tuning space and query rewrite space are built according to the system's default initialization strategy, covering table-level index combinations and 70 equivalent query rewrite rules.

[0087] 3. Experimental Procedure In this example, the optimization system starts without loading any offline training models or historical optimization samples; the lightweight anomaly detection model and experience retrieval module are both untrained. The system relies solely on query execution feedback information collected in real time during the optimization process, and gradually completes the following process through an online update mechanism: 1) Based on the current query context and execution feedback, dynamically adjust the positioning results of the multi-component optimization space; 2) Gradually learn the impact of different tuning components and their combinations on query performance; 3) Continuously optimize and fine-tune the action generation strategy through the experience reuse mechanism.

[0088] In the early stages of optimization, due to a lack of historical experience, the system mainly underwent exploratory optimization to accumulate effective samples and correct the spatial localization results.

[0089] 4. Experimental Results Experimental results are as follows Figure 3 As shown in the figure. Experimental results indicate that in the first three hours after tuning begins, the overall performance improvement is relatively slow due to the system being in a complete cold start state. As the tuning process continues, the system gradually accumulates effective tuning experience and adaptively adjusts the tuning strategy. Starting from the fourth hour, the system consistently outperforms the comparison method in overall query execution performance, ultimately achieving the optimal workload execution performance under the same time budget.

[0090] Corresponding to the database multi-component diagnostic and optimization method based on a large language model described in the above embodiments, this application also provides a database multi-component diagnostic and optimization system based on a large language model, which includes: The component query module is used to collect multimodal context for the target query. Through collaborative reasoning between a lightweight diagnostic model and a diagnostic agent, it automatically identifies the set of optimization components related to the performance bottleneck of the query, thereby reducing the full configuration space to an abnormal local optimization subspace. The action generation module is used to generate candidate optimization actions based on historical optimization experience and structured prompts using the optimization agent, and to perform syntax and semantic verification through the verification module. Finally, the optimization actions are executed in the database system and execution feedback is collected. The observation record module is used to uniformly construct the query context, the abnormal local tuning subspace, the tuning action and its execution feedback into a tuning observation record and store it in the tuning history database; The optimization and update module is used to update the anomaly detection model and historical optimization retrieval strategies online based on the optimization history database, thereby continuously improving the accuracy of anomaly localization and the effectiveness of optimization decisions, and building an adaptive evolution optimization system.

[0091] It should be noted that the information interaction and execution process between the above modules / units are based on the same concept as the method embodiments of this application. For details on their specific functions and technical effects, please refer to the method embodiments section, and they will not be repeated here.

[0092] Those skilled in the art will clearly understand that, for the sake of convenience and brevity, the above-described division of functional units and modules is merely an example. In practical applications, the above functions can be assigned to different functional units and modules as needed, that is, the internal structure of the device can be divided into different functional units or modules to complete all or part of the functions described above. The functional units and modules in the embodiments can be integrated into one processing unit, or each unit can exist physically separately, or two or more units can be integrated into one unit. The integrated unit can be implemented in hardware or as a software functional unit. Furthermore, the specific names of the functional units and modules are only for easy differentiation and are not intended to limit the scope of protection of this application. The specific working process of the units and modules in the above device can be referred to the corresponding process in the foregoing method embodiments, and will not be repeated here.

[0093] This application also provides a terminal device, including a memory, a processor, and a computer program stored in the memory and executable on the processor. When the processor executes the computer program, it implements the steps of the database multi-component diagnostic tuning method based on a large language model provided in the first aspect.

[0094] In applications, terminal devices may include, but are not limited to, processors and memory. These are merely examples of terminal devices and do not constitute a limitation on them. They may include more or fewer components, combinations of certain components, or different components, such as input / output devices and network access devices. Input / output devices may include cameras, audio capture / playback devices, displays, etc. Network access devices may include network modules for wireless network communication with external devices.

[0095] In applications, the processor can be a Central Processing Unit (CPU), but it can also be other general-purpose processors, digital signal processors (DSPs), application-specific integrated circuits (ASICs), field-programmable gate arrays (FPGAs), or other programmable logic devices, discrete gate or transistor logic devices, discrete hardware components, etc. A general-purpose processor can be a microprocessor or any conventional processor.

[0096] In applications, the memory may be an internal storage unit of the terminal device in some embodiments, such as the hard drive or RAM of the terminal device. In other embodiments, the memory may be an external storage device of the terminal device, such as a plug-in hard drive, Smart Media Card (SMC), Secure Digital (SD) card, or Flash Card. The memory may also include both internal and external storage units of the terminal device. The memory is used to store operating devices, applications, bootloaders, data, and other programs, such as program code for computer programs. The memory can also be used to temporarily store data that has been output or will be output.

[0097] This application also provides a computer-readable storage medium storing a computer program, which, when executed by a processor, can implement the steps in the above-described method embodiments.

[0098] This application implements all or part of the processes in the methods of the above embodiments, which can be accomplished by a computer program instructing related hardware. The computer program can be stored in a computer-readable storage medium, and when executed by a processor, it can implement the steps of the various method embodiments described above. The computer program includes computer program code, which can be in the form of source code, object code, executable file, or some intermediate form. The computer-readable medium can include at least: any entity or device capable of carrying the computer program code to a terminal device, a recording medium, a computer memory, a read-only memory (ROM), a random access memory (RAM), an electrical carrier signal, a telecommunication signal, and a software distribution medium, such as a USB flash drive, a portable hard drive, a magnetic disk, or an optical disk.

[0099] Those skilled in the art will recognize that the device and algorithm steps of the various examples described in conjunction with the embodiments disclosed herein can be implemented in electronic hardware, or a combination of computer software and electronic hardware. Whether these functions are implemented in hardware or software depends on the specific application and design constraints of the technical solution. Those skilled in the art can use different methods to implement the described functions for each specific application, but such implementation should not be considered beyond the scope of this application.

[0100] In the embodiments provided in this application, it should be understood that the disclosed devices and methods can be implemented in other ways. For example, the device embodiments described above are merely illustrative. Furthermore, the coupling or direct coupling or communication connection shown or discussed may be through some interface, or the device may be indirectly coupled or communicated, and may be electrical, mechanical, or other forms.

[0101] The above-described embodiments are only used to illustrate the technical solutions of this application, and are not intended to limit them. Although this application has been described in detail with reference to the foregoing embodiments, those skilled in the art should understand that modifications can still be made to the technical solutions described in the foregoing embodiments, or equivalent substitutions can be made to some of the technical features. Such modifications or substitutions do not cause the essence of the corresponding technical solutions to deviate from the spirit and scope of the technical solutions of the embodiments of this application, and should all be included within the protection scope of this application.

Claims

1. A database multi-component diagnostic and optimization method based on a large language model, characterized in that, include: Multimodal context collection is performed on the target query. Through collaborative reasoning between a lightweight diagnostic model and a diagnostic agent, the set of tuning components related to the performance bottleneck of the query is automatically identified, thereby reducing the full configuration space to an abnormal local tuning subspace. Based on historical tuning experience and structured prompts, candidate tuning actions are generated using a tuning agent, and the syntax and semantics are verified by a verification module. Finally, the tuning actions are executed in the database system and execution feedback is collected. The query context, the local tuning subspace of anomalies, tuning actions and their execution feedback are uniformly constructed into tuning observation records and stored in the tuning history database; Based on the historical tuning database, the anomaly detection model and historical tuning retrieval strategy are updated online to continuously improve the accuracy of anomaly localization and the effectiveness of tuning decisions, thereby constructing an adaptive and evolving tuning system.

2. The database multi-component diagnostic and optimization method based on a large language model as described in claim 1, characterized in that, Also includes: Each tuning component is quantitatively evaluated through a data-driven tuning component scoring mechanism; A knowledge-driven anomaly reasoning mechanism is introduced, which combines domain diagnostic knowledge with historical optimization trajectories to verify, correct, and enhance the confidence of component scoring results. By using an adaptive component exploration mechanism, the system is guided to dynamically explore potential unverified components while ensuring the stability of the tuning process, thus avoiding getting stuck in local optima.

3. The database multi-component diagnostic and optimization method based on a large language model as described in claim 2, characterized in that, The data-driven tuning component scoring mechanism includes: The diagnostic agent first uses a data-driven tuning component scoring mechanism to quickly filter tuning components that may degrade query performance; Using the context information related to query execution as input, a lightweight prediction model outputs the potential contribution of each tuning component to performance optimization, providing a priori basis for subsequent tuning space positioning. The diagnostic model employs a multimodal feature encoding and fusion mechanism to jointly model different types of query context information; After each feature is represented by its corresponding encoding module, cross-modal fusion is performed to finally form a unified feature representation for component scoring.

4. The database multi-component diagnostic and optimization method based on a large language model as described in claim 2, characterized in that, The knowledge-driven anomaly reasoning mechanism includes: The diagnostic agent has built-in domain diagnostic rules for different tuning components. The domain diagnostic rules take query context information as input and perform consistency verification on the component scoring results. The diagnostic agent compares the prediction results of the domain diagnostic rules with those of the component scoring model. When the prediction results match the domain diagnostic rules, the anomaly confidence of the corresponding component is enhanced. When there is a significant conflict between the two, the prediction results are adjusted.

5. The database multi-component diagnostic and optimization method based on a large language model as described in claim 2, characterized in that, The adaptive component exploration mechanism includes: In each round of optimization decision-making, the system adaptively determines whether to enter the exploration state based on the historical optimization benefits and number of optimization attempts of the target optimization component. When entering the exploration state, similar queries are retrieved from the historical tuning library based on query context similarity, and tuning component combinations that have not yet been fully explored are selected as new tuning objects. If no suitable candidate is found, a random selection is made from the remaining combinations of components that have not been fully explored.

6. The database multi-component diagnostic and optimization method based on a large language model as described in claim 1, characterized in that, Also includes: In the process of generating optimization actions, the optimization process is formalized as a Markov decision process to characterize the interaction between historical experience retrieval and optimization action generation. The maximum entropy reinforcement learning framework is used to optimize the historical experience retrieval strategy, and the Soft Q-learning mechanism is introduced to model the long-term value of historical experience. For each historical optimization experience, a conditional value function is learned to evaluate its contribution to future optimization gains under a given state. Based on this value evaluation, the retrieval probability of historical experiences is normalized using the Softmax method.

7. The database multi-component diagnostic and optimization method based on a large language model as described in claim 1, characterized in that, Also includes: Using the target optimization component set output from the anomaly diagnosis phase as a constraint, and combining historical optimization observations and execution feedback, the original optimization space is structurally split and inefficient regions are eliminated, thereby forming a compact, controllable, and high-yield optimization subspace. After completing the historical experience retrieval and optimization subspace construction, the optimization agent generates candidate optimization actions based on the current query context, the target optimization component set, historical optimization observation data, and optimization subspace constraints. During the optimization process, the optimization agent combines the results of previous optimization actions to analyze the reasons for optimization success or failure, and dynamically adjusts subsequent optimization strategies accordingly.

8. A database multi-component diagnostic and optimization system based on a large language model, characterized in that, include: The component query module is used to collect multimodal context for the target query. Through collaborative reasoning between a lightweight diagnostic model and a diagnostic agent, it automatically identifies the set of optimization components related to the performance bottleneck of the query, thereby reducing the full configuration space to an abnormal local optimization subspace. The action generation module is used to generate candidate optimization actions based on historical optimization experience and structured prompts using the optimization agent, and to perform syntax and semantic verification through the verification module. Finally, the optimization actions are executed in the database system and execution feedback is collected. The observation record module is used to uniformly construct the query context, the abnormal local tuning subspace, the tuning action and its execution feedback into a tuning observation record and store it in the tuning history database; The optimization and update module is used to update the anomaly detection model and historical optimization retrieval strategies online based on the optimization history database, thereby continuously improving the accuracy of anomaly localization and the effectiveness of optimization decisions, and building an adaptive evolution optimization system.

9. A terminal device, comprising a memory, a processor, and a computer program stored in the memory and executable on the processor, characterized in that, When the processor executes the computer program, it implements the database multi-component diagnostic and tuning method based on a large language model as described in any one of claims 1 to 7.

10. A computer-readable storage medium storing a computer program, characterized in that, When the computer program is executed by the processor, it implements the database multi-component diagnostic and tuning method based on a large language model as described in any one of claims 1 to 7.

Images