Methods, apparatus, and electronic devices for optimizing database performance parameters

Abstract

This application relates to a method, apparatus, and electronic device for optimizing database performance parameters. The method includes: inputting each SQL statement in a set of SQL statements into an initial database, outputting the execution data of each SQL statement to obtain a first dataset; classifying and summarizing the first dataset to obtain execution data for each type of SQL statement, and calculating a comprehensive evaluation index value for each type of SQL statement; based on the first dataset, analyzing the first parameter set to be optimized from the full parameter set of the initial database; inputting all comprehensive evaluation index values ​​and the first parameter set into a prediction model to obtain a second parameter set after initial optimization; and using the initial database and the prediction model to iteratively optimize the second parameter set multiple times to obtain the optimal parameter set of the initial database. This application solves the technical problem of the difficulty in parameter tuning caused by the complexity of distributed database architecture.

Description

Technical Field

[0001] This application relates to the field of distributed database technology, and in particular to a method, apparatus, and electronic device for optimizing database performance parameters. Background Technology

[0002] Distributed databases are widely used in modern business applications, achieving high efficiency primarily through a layered design of a computing engine layer and a data storage layer. In this architecture, each layer consists of multiple nodes, enabling concurrent processing of business statements. This allows for near-linear performance improvements as business volume increases, making distributed databases the preferred technology for large-scale, high-concurrency scenarios. However, the complexity of distributed architectures also presents significant challenges, especially in database performance optimization. Since SQL execution involves multiple nodes and layers, performance is limited by factors such as inter-node coordination, resource allocation, and hardware bottlenecks. Furthermore, the interaction between components at each layer complicates troubleshooting and optimization, often requiring comprehensive analysis of multi-dimensional performance metrics, including CPU, memory, disk I / O, and network bandwidth. This multi-factor coupling makes database parameter tuning extremely difficult, requiring not only detailed analysis down to the specific execution stage but also comprehensive debugging and optimization based on server performance, necessitating significant human resource analysis costs. Therefore, effectively collecting and analyzing performance consumption information during execution and accurately tuning parameters based on this data is a pressing issue in current distributed database optimization. Summary of the Invention

[0003] This application provides a method, apparatus, and electronic device for optimizing database performance parameters, in order to solve the technical problem of the difficulty in parameter tuning caused by the complexity of distributed database architecture.

[0004] In a first aspect, this application provides a method for optimizing database performance parameters, comprising: inputting each SQL statement in a set of SQL statements into an initial database, so that the initial database executes each SQL statement and outputs the execution data of each SQL statement to obtain a first dataset, wherein the set of SQL statements includes multiple types of SQL statements, and the execution data includes the performance consumption data of the server of each node of the initial database executing the SQL statements and the detailed execution time of the SQL statements on each node; classifying and summarizing the first dataset to obtain the execution data of each type of SQL statement, and calculating a comprehensive evaluation index value for each type of SQL statement based on the execution data of each type of SQL statement; analyzing the first parameter set to be optimized from the full parameter set of the initial database based on the first dataset; inputting all the comprehensive evaluation index values ​​and the first parameter set into a prediction model, so that the prediction model performs an initial optimization of the first parameter set based on all the comprehensive evaluation index values ​​to obtain a second parameter set after the initial optimization; and using the initial database and the prediction model to perform multiple iterative optimizations on the second parameter set to obtain the optimal parameter set of the initial database.

[0005] Secondly, this application provides a database performance parameter optimization apparatus, comprising: an execution module, configured to input each SQL statement in an SQL statement set into an initial database, so that the initial database executes each SQL statement and outputs the execution data of each SQL statement to obtain a first dataset, wherein the SQL statement set includes multiple types of SQL statements, and the execution data includes the performance consumption data of the server of each node of the initial database executing the SQL statements and the detailed execution time of the SQL statements on each node; and a calculation module, configured to classify and summarize the first dataset to obtain each type of SQL statement. The system uses the execution data of each type of SQL statement to calculate a comprehensive evaluation index value for each type of SQL statement. The analysis module analyzes the first parameter set that needs optimization from the full parameter set of the initial database based on the first dataset. The first optimization module inputs all the comprehensive evaluation index values ​​and the first parameter set into the prediction model, so that the prediction model performs an initial optimization of the first parameter set based on all the comprehensive evaluation index values, resulting in an initially optimized second parameter set. The second optimization module uses the initial database and the prediction model to perform multiple iterative optimizations on the second parameter set to obtain the optimal parameter set of the initial database.

[0006] As an optional example, the above calculation module includes: a first acquisition unit, used to acquire the execution plan tree generated by the initial database when executing each of the above SQL statements; a classification unit, used to classify all the above SQL statements according to the fingerprint of the execution plan tree of each of the above SQL statements to obtain multiple categories of SQL statements; and a first summary unit, used to summarize the running data of each of the above SQL statements in the target category of SQL statements to obtain the running data of the above target category of SQL statements, wherein the above target category of SQL statements is any one of the multiple categories of SQL statements.

[0007] As an optional example, the above apparatus further includes: a removal module, used to remove all outliers in the first dataset using an adaptive algorithm before calculating the comprehensive evaluation index value of each type of SQL statement based on the running data of each type of SQL statement, so as to obtain an updated first dataset.

[0008] As an optional example, the above calculation module includes: a processing unit, configured to treat each type of SQL statement as the current type of SQL statement, and perform the following steps on the current type of SQL statement: obtain the performance consumption data and detailed execution time of each execution stage of the current type of SQL statement, and convert them into comparable units; assign weight factors to the performance consumption data and detailed execution time of each execution stage of the current type of SQL statement; multiply the performance consumption data and detailed execution time of each execution stage of the current type of SQL statement by the corresponding weight factors to obtain the weighted result of each execution stage of the current type of SQL statement; and sum the weighted results of each execution stage of the current type of SQL statement to obtain the comprehensive evaluation index value of the current type of SQL statement.

[0009] As an optional example, the analysis module includes: a first determining unit for determining the bottleneck stage based on the first dataset; a filtering unit for filtering a third parameter set related to the bottleneck stage from the full parameter set; a second determining unit for determining the optimization range of each parameter in the third parameter set; and a second summarizing unit for summarizing the third parameter set and the optimization range of each parameter to obtain the first parameter set.

[0010] As an optional example, the second optimization module includes: a second acquisition unit, configured to acquire the second parameter set and database used in the Mth optimization, wherein M is a positive integer greater than 1; when M is 2, the second parameter set used in the Mth optimization is the second parameter set after the initial optimization, and the database used in the Mth optimization is the initial database; when M is greater than 2, the second parameter set used in the Mth optimization is the second parameter set after the (M-1)th optimization, and the database used in the Mth optimization is the database after the (M-1)th optimization; and a first optimization unit, configured to optimize the database used in the Mth optimization based on the second parameter set used in the Mth optimization, to obtain the database after the Mth optimization. The system comprises a database and a calculation unit, used to recalculate the comprehensive evaluation index value of each type of SQL statement based on the above SQL statement set and the database after the Mth optimization, to obtain the comprehensive evaluation index value of each type of SQL statement after the Mth optimization; and a second optimization unit, used to input all the comprehensive evaluation index values ​​after the Mth optimization and the second parameter set used in the Mth optimization into the prediction model, so that the prediction model optimizes the second parameter set used in the Mth optimization based on all the comprehensive evaluation index values ​​after the Mth optimization, to obtain the second parameter set after the Mth optimization, updates M = M + 1, and performs the (M+1)th optimization until M equals the target threshold, thus obtaining the optimal parameter set.

[0011] As an optional example, the above calculation unit includes: an execution subunit, used to input each SQL statement in the above SQL statement set into the above-M-th optimized database, so that the above-M-th optimized database executes each of the above SQL statements and outputs the running data of each of the above SQL statements to obtain the first dataset after the M-th optimization; and a calculation subunit, used to classify and summarize the above-M-th optimized first dataset to obtain the running data of each type of SQL statement, and calculate the comprehensive evaluation index value of each type of SQL statement after the M-th optimization based on the running data of each type of SQL statement.

[0012] Thirdly, this application provides a storage medium storing a computer program, wherein the computer program is executed by a processor to perform the above-mentioned database performance parameter optimization method.

[0013] Fourthly, this application also provides an electronic device, including a memory and a processor, wherein the memory stores a computer program, and the processor is configured to execute the above-mentioned database performance parameter optimization method through the computer program.

[0014] The technical solutions provided in this application have the following advantages compared with the prior art:

[0015] This application employs a method of inputting each SQL statement in a set of SQL statements into an initial database, causing the initial database to execute each SQL statement and output the execution data of each SQL statement to obtain a first dataset. The set of SQL statements includes multiple types of SQL statements, and the execution data includes the performance consumption data of the server at each node of the initial database and the detailed execution time of each SQL statement at each node. The first dataset is then categorized and summarized to obtain the execution data for each type of SQL statement, and a comprehensive evaluation index value for each type of SQL statement is calculated based on the execution data. Based on the first dataset mentioned above, the first parameter set requiring optimization is analyzed from the full parameter set of the initial database. All the comprehensive evaluation index values ​​and the first parameter set are input into the prediction model, allowing the model to perform initial optimization of the first parameter set based on these comprehensive evaluation index values, resulting in the initially optimized second parameter set. The second parameter set is then iteratively optimized using the initial database and the prediction model to obtain the optimal parameter set for the initial database. This method involves inputting each SQL statement from the SQL statement set into the initial database for execution, collecting runtime data including detailed execution time and server performance consumption at each node, thus forming the first dataset. After classifying and summarizing the data, the comprehensive evaluation index value for each type of SQL is calculated, and the first parameter set requiring optimization is selected from the full parameter set. Subsequently, the comprehensive evaluation index values ​​and the first parameter set are input into the prediction model to generate the second parameter set for initial optimization. Through multiple rounds of iterative adjustments, the optimal parameter set is finally obtained. This achieves the goal of comprehensively collecting performance data and SQL execution time, accurately locating performance bottlenecks, focusing on key parameters, avoiding blind optimization, and automatically adjusting parameters in conjunction with the prediction model to improve optimization efficiency. Multiple rounds of iterative optimization ensure continuous improvement in database performance, meet the needs of complex business scenarios, and significantly reduce the difficulty of optimization under distributed architecture. This solves the technical problem of high parameter optimization difficulty caused by the complexity of distributed database architecture. Attached Figure Description

[0016] The accompanying drawings, which are incorporated in and form part of this specification, illustrate embodiments consistent with this application and, together with the description, serve to explain the principles of this application.

[0017] 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, for those skilled in the art, other drawings can be obtained based on these drawings without creative effort.

[0018] One or more embodiments are illustrated by way of example with reference numerals in the accompanying drawings. These illustrations do not constitute a limitation on the embodiments. Elements with the same reference numerals in the drawings are denoted as similar elements. Unless otherwise stated, the figures in the drawings are not to be limited by scale.

[0019] Figure 1 This is a flowchart of an optional method for optimizing database performance parameters according to an embodiment of this application;

[0020] Figure 2 This is an architecture diagram of an optional database performance parameter optimization method according to an embodiment of this application;

[0021] Figure 3 This is a flowchart illustrating the implementation of an optional database performance parameter optimization method according to an embodiment of this application.

[0022] Figure 4 This is an analysis flowchart of the log analysis module of an optional database performance parameter optimization method according to an embodiment of this application;

[0023] Figure 5 This is an analysis flowchart of the analysis and processing module of an optional database performance parameter optimization method according to an embodiment of this application;

[0024] Figure 6 This is a schematic diagram of an optional database performance parameter optimization device according to an embodiment of this application;

[0025] Figure 7 This is a schematic diagram of an optional electronic device according to an embodiment of this application. Detailed Implementation

[0026] To make the objectives, technical solutions, and advantages of the embodiments of this application clearer, the technical solutions of 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, not all embodiments. Based on the embodiments of this application, all other embodiments obtained by those skilled in the art without creative effort are within the scope of protection of this application.

[0027] The following disclosure provides numerous different embodiments or examples for implementing various structures of this application. To simplify the disclosure, specific examples of components and arrangements are described below. These are merely examples and are not intended to limit the scope of this application. Furthermore, reference numerals and / or letters may be repeated in different examples. Such repetition is for simplification and clarity and does not in itself indicate a relationship between the various embodiments and / or arrangements discussed.

[0028] According to a first aspect of the embodiments of this application, a method for optimizing database performance parameters is provided, optionally, as follows: Figure 1 As shown, the above method includes:

[0029] S102, input each SQL statement in the SQL statement set into the initial database so that the initial database executes each SQL statement and outputs the running data of each SQL statement to obtain the first dataset. The SQL statement set includes multiple types of SQL statements, and the running data includes the SQL statements executed by the initial database, the performance consumption data of the server of each node of the initial database, and the detailed execution time of the SQL statement on each node.

[0030] S104. Classify and summarize the first dataset to obtain the running data of each type of SQL statement, and calculate the comprehensive evaluation index value of each type of SQL statement based on the running data of each type of SQL statement.

[0031] S106, Based on the first dataset, analyze the first parameter set that needs to be optimized from the full parameter set of the initial database;

[0032] S108, input all comprehensive evaluation index values ​​and the first parameter set into the prediction model, so that the prediction model performs initial optimization of the first parameter set based on all comprehensive evaluation index values, and obtains the second parameter set after initial optimization;

[0033] S110, using the initial database and prediction model, the second parameter set is iteratively optimized multiple times to obtain the optimal parameter set of the initial database.

[0034] Optionally, in this embodiment, as Figure 2The architecture diagram shown includes an application (APP), a database, a log analysis module, and an analysis and processing module. The application is used to issue business statements or SQL statements. The database is responsible for storing business data, executing business SQL statements, and returning execution results. Distributed databases are generally divided into a computing engine layer and a data storage layer, with each layer consisting of multiple nodes that concurrently process business statements, thereby achieving a significant near-linear improvement in business performance. The log analysis module receives performance consumption data from each node of the database and detailed execution time data of SQL statements on each node. It parses the SQL statements to generate an execution plan tree, classifies the SQL statements according to the fingerprint of the execution plan tree, and analyzes the time bottlenecks and performance consumption of various SQL statements as a benchmark. Finally, it summarizes and calculates the time consumption and performance consumption of each execution stage of various SQL statements and visually displays the time bottlenecks and performance consumption of SQL statements. Based on the time bottlenecks and performance consumption of various SQL statements transmitted by the log analysis module, the analysis and processing module forms a preliminary optimization plan and related parameter set, continuously adjusts the values ​​of the input parameter set, verifies the execution results, collects time consumption and performance consumption data, evaluates the optimization effect, and updates the parameter prediction model. After multiple comparative tests, the parameter prediction model became more and more perfect, and finally the best parameter tuning scheme and parameter set were output.

[0035] Optionally, in this embodiment, as Figure 3The flowchart shown illustrates the implementation process. Specifically, the application sequentially inputs each SQL statement from various business SQL statements in the SQL statement set into an initial database containing pre-set foundational data, prompting them to execute one by one. This aims to cover as many business scenarios as possible and distribute the data multiple times for more accurate statistics. During this process, the database records the execution data of each SQL statement, including execution time, server performance consumption (such as CPU, memory, disk I / O, network bandwidth, etc.) at each node in the initial database, and detailed execution time of the SQL statement at each node, broken down to specific execution stages. This data constitutes the first dataset and is promptly uploaded to the log analysis module. The log analysis module parses each SQL statement to generate an execution plan tree, categorizes and summarizes the SQL statement execution data based on the fingerprint of the execution plan tree, and visually displays the time bottlenecks and performance consumption of the SQL statements. The analysis and processing module receives the categorized and summarized execution data of various SQL statements, calculates a comprehensive evaluation index value for each type of SQL statement, and this index value combines execution time and performance consumption to provide a quantitative basis for performance evaluation. Based on the first dataset, database parameters that significantly impact performance bottlenecks are identified. From the full parameter set, a first set of parameters requiring optimization is selected. These parameters are typically related to key stages of SQL execution, such as index configuration, cache settings, and parallelism. The first set of parameters and comprehensive evaluation metrics for various SQL queries are input into the predictive model. The predictive model optimizes the first set of parameters through preliminary analysis, generating a second set of parameters after initial optimization—an improvement suggestion based on current performance data. Multiple rounds of optimization are performed on the second set using the predictive model and the initial database, gradually adjusting parameter values ​​and verifying the optimization effect. After each iteration, the predictive model adjusts its optimization direction based on new performance data, ultimately generating the optimal parameter set.

[0036] Optionally, in this embodiment, a comprehensive analysis of SQL execution data and node performance consumption can accurately identify performance bottlenecks, such as high resource consumption on specific nodes or execution latency for specific SQL types. By filtering and optimizing the first set of parameters, the optimization process focuses on key parameters that have the greatest impact on performance, avoiding the blindness and inefficiency of tuning all parameters. Combined with automated optimization and iterative analysis of the predictive model, the time and workload of manually adjusting parameters are significantly reduced, improving tuning efficiency. Multiple rounds of iterative optimization ensure that the database parameter configuration is close to the optimal performance, adapting to diverse business scenario requirements and improving the overall operating efficiency of the distributed database.

[0037] As an optional example, the first dataset is categorized and summarized to obtain the execution data for each category of SQL statements, including:

[0038] Obtain the execution plan tree generated by the initial database as each SQL statement is executed;

[0039] Based on the fingerprint of the execution plan tree for each SQL statement, all SQL statements are classified to obtain multiple categories of SQL statements;

[0040] The execution data of each SQL statement in the target class SQL statement is summarized to obtain the execution data of the target class SQL statement. The target class SQL statement can be any one of the multiple types of SQL statements.

[0041] Optionally, in this embodiment, as Figure 4 The flowchart of the log analysis module illustrates that, specifically, when each SQL statement is executed in the database, the system generates a corresponding execution plan tree. The execution plan tree details the execution flow of the SQL statement, including operation steps (such as index scans, table scans, join operations, etc.) and resource allocation (such as CPU, memory, etc.). These plans are key data structures in the SQL statement execution process, reflecting execution efficiency and potential bottlenecks. The execution plan tree fingerprint of each SQL statement is used to classify the SQL statements. The execution plan tree fingerprint is a feature identifier that uniquely represents the core pattern of the SQL statement's execution logic structure. By classifying fingerprints, SQL statements with similar execution logic can be grouped into one category, forming multiple categories of SQL statements. For the target category of SQL statements (any one of the multiple categories), the runtime data (such as node performance consumption, execution time, etc.) of all SQL statements in that category are summarized. The summary results include averages, peak values, distribution statistics, etc., providing a comprehensive overview of the overall performance of the target category of SQL statements.

[0042] Optionally, in this embodiment, classification based on the execution plan tree can precisely focus on the optimization needs of different SQL types, avoiding a "one-size-fits-all" approach to generalized tuning and improving targeting. Summarizing and analyzing similar SQL queries can identify patterned performance bottlenecks (such as invalid specific indexes or resource contention), providing data support for optimization. The summarization process covers multi-dimensional performance data, making the results more representative and facilitating subsequent calculation of comprehensive evaluation indicators and design of performance optimization schemes. The categorized and summarized data provides high-quality input for subsequent predictive models, improving the accuracy and iterative effect of parameter optimization.

[0043] As an optional example, before calculating the comprehensive evaluation index value for each type of SQL statement based on the execution data of each type of SQL statement, the above method also includes:

[0044] An adaptive algorithm is used to remove all outliers from the first dataset, resulting in an updated first dataset.

[0045] Optionally, in this embodiment, the first dataset may contain various outliers, such as spikes caused by sudden server resource usage (interference from other tasks), network fluctuations, hardware failures, and other special circumstances. These outliers may significantly deviate from the normal data distribution, misleading the analysis results. Therefore, before classifying the first dataset, the log analysis module uses adaptive algorithms (such as least squares, least mean squares, Wiener filtering, etc.) to dynamically identify and remove outliers. These adaptive algorithms automatically adjust parameters based on the statistical characteristics of the data (such as distribution and trend), ensuring that normal data is retained while outliers are removed. After removing outliers, the cleaned first dataset is obtained, which is more realistic and reliable, accurately reflecting the actual performance of the database operation.

[0046] Optionally, in this embodiment, removing outliers results in a more reasonable data distribution, avoiding interference from outliers on statistical results and improving the accuracy of data classification and summarization. It eliminates potential erroneous decisions caused by outliers, ensuring that comprehensive evaluation indicators and optimization strategies are based on normal operating data, thus improving the overall analysis effect. Data cleaning reduces computational bias caused by outliers, making subsequent performance optimization directions clearer and reducing the probability of ineffective optimization.

[0047] As an optional example, based on the execution data of each type of SQL statement, the comprehensive evaluation index value for each type of SQL statement is calculated as follows:

[0048] Treat each type of SQL statement as the current type of SQL statement, and perform the following steps on the current type of SQL statement:

[0049] Obtain performance consumption data and detailed execution time for each execution stage of the current SQL statement, and convert them into comparable units;

[0050] Assign weighting factors to the performance consumption data and detailed execution time of each execution stage of the current SQL statement;

[0051] The performance consumption data and detailed execution time of each execution stage of the current SQL statement are multiplied by the corresponding weighting factor to obtain the weighted result of each execution stage of the current SQL statement.

[0052] The weighted results of each execution stage of the current SQL statement are summed to obtain the comprehensive evaluation index value of the current SQL statement.

[0053] Optionally, in this embodiment, as Figure 5The analysis flowchart shown in the diagram illustrates the analysis process of the SQL statement's processing module. Specifically, for each type of SQL statement, performance consumption data (such as CPU, memory, disk I / O, network bandwidth, etc.) and detailed execution time are extracted at different execution stages (such as parsing, optimization, plan generation, and execution). This data comprehensively reflects the resource usage and performance of the SQL statement at each stage. The performance consumption data and detailed execution time are converted to comparable units (such as percentages or normalized to the [0,1] range) and standardized to the same units (such as converting memory consumption to MB / s and execution time to milliseconds / ms) to ensure consistency in subsequent calculations and avoid distortion due to inconsistent units. Based on business needs or actual scenarios, different weighting factors are assigned to each execution stage of the SQL statement. These weighting factors represent the importance of each stage to the overall evaluation of the SQL statement. For example, if the business prioritizes execution speed, the weighting factor for execution time may be higher, while for resource-constrained scenarios, the weighting factor for performance consumption may be higher. The performance consumption data and detailed execution time of each execution stage for each type of SQL statement are multiplied by their respective weighting factors to obtain a weighted result for each execution stage. This process combines the importance of different stages, making the evaluation results more consistent with actual business needs. The weighted results of each execution stage for each type of SQL statement are then summed to obtain a comprehensive evaluation index value for that type of SQL statement. This index is a quantitative evaluation of the overall performance of the SQL, comprehensively reflecting execution efficiency and the rationality of resource utilization.

[0054] Optionally, in this embodiment, various performance data and time consumption indicators are unified into comparable values ​​through unit conversion and weighted calculation, giving the evaluation results standardized characteristics and facilitating horizontal comparisons between SQL types. The weighting factors can be flexibly adjusted according to different business scenarios, making the evaluation indicators more aligned with actual needs, such as increasing the focus on execution efficiency or resource consumption. Comprehensive evaluation indicator values ​​can help quickly identify the main performance bottlenecks of a certain type of SQL (such as excessively high resource consumption or excessively long processing times at specific stages), providing a clear direction for subsequent optimization. Evaluation indicators provide a quantitative basis for SQL classification optimization and parameter tuning, simplifying the optimization process and improving tuning efficiency and effectiveness.

[0055] As an optional example, based on the first dataset, the first set of parameters that needs to be optimized, analyzed from the full parameter set of the initial database, includes:

[0056] Based on the first dataset, the bottleneck stage was identified;

[0057] Select a third set of parameters related to the bottleneck stage from the full set of parameters;

[0058] Determine the optimization range for each parameter in the third parameter set;

[0059] By summarizing the third parameter set and the optimization range of each parameter, we obtain the first parameter set.

[0060] Optionally, in this embodiment, as Figure 5 The analysis flowchart shown in the diagram illustrates the analysis process of the processing module. Specifically, based on the first dataset (including SQL execution performance consumption data and detailed execution time), it analyzes the distribution of time and resource consumption in each execution stage (such as parsing, optimization, plan generation, and execution) when the database processes SQL statements. By comparing the time consumption and resource consumption ratio of each stage, the bottleneck stage with a significant impact on overall performance is identified. For example, if the execution stage's time consumption is significantly higher than other stages or the set value, it can be identified as the bottleneck stage. From the initial full set of parameters in the database, parameters directly related to the bottleneck stage are selected to form the third parameter set. For each parameter in the third parameter set, combined with the running data of the first dataset and the database parameter design document, its reasonable optimization range is determined. For example, the thread pool size may have an effective range (such as 4-64). Adjusting it too small may lead to insufficient concurrency, while adjusting it too large will consume additional resources. The optimization range can be reduced according to the actual bottleneck and database load. The selected third parameter set and its optimization range are summarized to form the first parameter set. The first parameter set is a subset of the initial database parameters, used for iterative parameter optimization. It focuses on parameters related to the bottleneck stage, with the aim of narrowing the scope of optimization and improving the efficiency of parameter adjustment.

[0061] Optionally, in this embodiment, the bottleneck stage analysis clarifies the key areas of performance problems, thereby focusing on the most critical parameters and avoiding the complexity of adjusting all parameters. By filtering relevant parameters and limiting the optimization scope, the set of parameters to be adjusted is narrowed, significantly reducing the time and computational cost of the tuning process. The first parameter set is centered on the bottleneck stage, ensuring the targeting and effectiveness of the optimization and enabling rapid performance improvements. The first parameter set is dynamically generated according to different bottleneck stages, allowing the optimization process to adapt to different SQL types and operating scenarios, enhancing the tuning flexibility of the distributed database.

[0062] As an optional example, the second parameter set is iteratively optimized using the initial database and prediction model to obtain the optimal parameter set for the initial database, which includes:

[0063] Obtain the second parameter set and database used in the Mth optimization, where M is a positive integer greater than 1. When M is 2, the second parameter set used in the Mth optimization is the second parameter set after the first optimization, and the database used in the Mth optimization is the initial database. When M is greater than 2, the second parameter set used in the Mth optimization is the second parameter set after the (M-1)th optimization, and the database used in the Mth optimization is the database after the (M-1)th optimization.

[0064] Based on the second parameter set used in the Mth optimization, optimize the database used in the Mth optimization to obtain the database after the Mth optimization;

[0065] Based on the SQL statement set and the database after the Mth optimization, the comprehensive evaluation index value of each type of SQL statement is recalculated to obtain the comprehensive evaluation index value of each type of SQL statement after the Mth optimization.

[0066] Input all the comprehensive evaluation index values ​​after the Mth optimization and the second parameter set used in the Mth optimization into the prediction model, so that the prediction model optimizes the second parameter set used in the Mth optimization based on all the comprehensive evaluation index values ​​after the Mth optimization, obtains the second parameter set after the Mth optimization, updates M = M+1, and performs the (M+1)th optimization until M equals the target threshold, and obtains the optimal parameter set.

[0067] Optionally, in this embodiment, the input for the first optimization is the second parameter set and the initial database after the initial optimization. As the iteration optimization progresses, the input for the Mth optimization includes the second parameter set generated in the (M-1)th optimization and the database instance generated in the (M-1)th optimization. Based on the second parameter set used in the Mth optimization, the Mth database is adjusted, including modifying configuration parameters (such as parallelism, caching strategy, etc.) and restarting or hot-updating the database to generate the database instance after the Mth optimization. The database after the Mth optimization is tested using a set of SQL statements, running multiple types of SQL statements and collecting performance data (time consumption, CPU, memory, disk I / O, etc.) for each type of SQL. The comprehensive evaluation index value after the Mth optimization is recalculated based on the weighting factors. The comprehensive evaluation index value after the Mth optimization and the current second parameter set are used as input to the prediction model. The prediction model further adjusts the parameter set by analyzing the changing trend of the new index value, generating the second parameter set for the next round of optimization. After each optimization is completed, the iteration number M is incremented by 1, and the next round of optimization begins with a new parameter set and database instance. The system manually monitors and analyzes the rationality of the current parameter tuning. If some parameters are unreasonable, manual intervention can be made to change the parameter prediction model, making it more suitable for application requirements. When M reaches the target threshold (e.g., when performance is stable or not significantly improved), the iteration terminates, and the final optimal parameter set is output.

[0068] Optionally, in this embodiment, through multiple iterative optimizations, each time using more accurate indicator values ​​to guide the next round of parameter adjustments, a gradual approximation of the optimal parameters is achieved. The predictive model can adjust its strategy based on historical optimization data, adapting to different SQL load characteristics and database architecture changes. The final output optimal parameter set can balance resource utilization and SQL execution efficiency, significantly improving database performance while ensuring stability. Automated iterative optimization reduces the complexity of manual parameter analysis, improving optimization efficiency and accuracy.

[0069] As an optional example, the comprehensive evaluation index value for each type of SQL statement is recalculated based on the SQL statement set and the database after the Mth optimization. The comprehensive evaluation index value for each type of SQL statement after the Mth optimization includes:

[0070] Each SQL statement in the SQL statement set is input into the database after the Mth optimization, so that the database after the Mth optimization executes each SQL statement and outputs the running data of each SQL statement, thus obtaining the first dataset after the Mth optimization.

[0071] The first dataset after the Mth optimization is categorized and summarized to obtain the running data of each type of SQL statement. Based on the running data of each type of SQL statement, the comprehensive evaluation index value of each type of SQL statement after the Mth optimization is calculated.

[0072] Optionally, in this embodiment, each SQL statement in the SQL statement set is input one by one into the database after the Mth optimization for execution. During the execution of the SQL statements, the database records the running data, including the performance consumption of each node (CPU, memory, disk I / O, etc.), the detailed execution time of the SQL statements at each node, and outputs the running data to form the first dataset after the Mth optimization. The first dataset after the Mth optimization is classified according to the execution plan tree fingerprint of the SQL statements, dividing all SQL statements into multiple categories. The running data (time consumption and performance consumption) of each category of SQL statements is summarized to obtain the running data for each category of SQL statements. For the running data of each category of SQL statements, the performance consumption data and detailed execution time of the execution stage are extracted and the units are standardized (e.g., converted to milliseconds, percentages, etc.). The performance consumption data and detailed execution time of each execution stage are multiplied by the corresponding weight factor to obtain the weighted result of each execution stage. The weighted results of all execution stages are summed to calculate the comprehensive evaluation index value of the current category of SQL statements. The above process is repeated to calculate the comprehensive evaluation index value of all categories of SQL statements to obtain the complete evaluation index after the Mth optimization.

[0073] Optionally, the core of this application lies in utilizing the database's time-consuming logs and server performance consumption information for data preprocessing, classification, summarization, calculation, and iterative optimization to ultimately obtain the optimal parameter set and its values ​​suitable for the database. The SQL feature classification can also be flexibly adjusted based on other rules, such as specified hint information and pre-compiled templated features. The prediction model can employ artificial intelligence algorithms, including but not limited to the following two alternatives: Model type: supervised learning (e.g., regression analysis), unsupervised learning (e.g., clustering), semi-supervised learning, reinforcement learning, etc.; Training algorithm: combining different algorithms, such as regression algorithms, decision trees, deep learning, association rule learning, etc., and selecting a suitable optimization method.

[0074] It should be noted that, for the sake of simplicity, the foregoing method embodiments are all described as a series of actions. However, those skilled in the art should understand that this application is not limited to the described order of actions, as some steps may be performed in other orders or simultaneously according to this application. Furthermore, those skilled in the art should also understand that the embodiments described in the specification are preferred embodiments, and the actions and modules involved are not necessarily essential to this application.

[0075] According to another aspect of the embodiments of this application, a database performance parameter optimization device is also provided, such as... Figure 6 As shown, it includes:

[0076] The execution module 602 is used to input each SQL statement in the SQL statement set into the initial database, so that the initial database executes each SQL statement and outputs the running data of each SQL statement to obtain the first dataset. The SQL statement set includes multiple types of SQL statements, and the running data includes the SQL statements executed by the initial database, the performance consumption data of the server of each node of the initial database, and the detailed execution time of the SQL statement on each node.

[0077] The calculation module 604 is used to classify and summarize the first dataset, obtain the running data of each type of SQL statement, and calculate the comprehensive evaluation index value of each type of SQL statement based on the running data of each type of SQL statement.

[0078] Analysis module 606 is used to analyze the first set of parameters that need to be optimized from the full set of parameters in the initial database based on the first dataset;

[0079] The first optimization module 608 is used to input all comprehensive evaluation index values ​​and the first parameter set into the prediction model, so that the prediction model performs initial optimization on the first parameter set based on all comprehensive evaluation index values ​​to obtain the second parameter set after initial optimization.

[0080] The second optimization module 610 is used to perform multiple iterations of optimization on the second parameter set using the initial database and the prediction model to obtain the optimal parameter set of the initial database.

[0081] It should be noted that the execution module 602 in this embodiment can be used to execute step S102 in this application embodiment, the calculation module 604 in this embodiment can be used to execute step S104 in this application embodiment, the analysis module 606 in this embodiment can be used to execute step S106 in this application embodiment, the first optimization module 608 in this embodiment can be used to execute step S108 in this application embodiment, and the second optimization module 610 in this embodiment can be used to execute step S110 in this application embodiment.

[0082] As an optional example, the computation module includes:

[0083] The first acquisition unit is used to acquire the execution plan tree generated by the initial database when executing each SQL statement;

[0084] The classification unit is used to classify all SQL statements based on the fingerprint of the execution plan tree of each SQL statement, resulting in multiple categories of SQL statements;

[0085] The first aggregation unit is used to aggregate the execution data of each SQL statement in the target class SQL statement to obtain the execution data of the target class SQL statement. The target class SQL statement can be any one of the multiple types of SQL statements.

[0086] As an optional example, the above-described apparatus further includes:

[0087] The removal module is used to remove all outliers in the first dataset using an adaptive algorithm before calculating the comprehensive evaluation index value for each type of SQL statement based on the execution data of each type of SQL statement, thus obtaining an updated first dataset.

[0088] As an optional example, the computation module includes:

[0089] The processing unit is used to treat each type of SQL statement as the current type of SQL statement and perform the following steps on the current type of SQL statement:

[0090] Obtain performance consumption data and detailed execution time for each execution stage of the current SQL statement, and convert them into comparable units;

[0091] Assign weighting factors to the performance consumption data and detailed execution time of each execution stage of the current SQL statement;

[0092] The performance consumption data and detailed execution time of each execution stage of the current SQL statement are multiplied by the corresponding weighting factor to obtain the weighted result of each execution stage of the current SQL statement.

[0093] The weighted results of each execution stage of the current SQL statement are summed to obtain the comprehensive evaluation index value of the current SQL statement.

[0094] As an optional example, the analysis module includes:

[0095] The first determining unit is used to determine the bottleneck stage based on the first dataset;

[0096] The filtering unit is used to filter out the third set of parameters related to the bottleneck stage from the full set of parameters;

[0097] The second determining unit is used to determine the optimization range of each parameter in the third parameter set;

[0098] The second summarization unit is used to summarize the third parameter set and the optimization range of each parameter to obtain the first parameter set.

[0099] As an optional example, the second optimization module includes:

[0100] The second acquisition unit is used to acquire the second parameter set and database used in the Mth optimization, where M is a positive integer greater than 1. When M is 2, the second parameter set used in the Mth optimization is the second parameter set after the first optimization, and the database used in the Mth optimization is the initial database. When M is greater than 2, the second parameter set used in the Mth optimization is the second parameter set after the (M-1)th optimization, and the database used in the Mth optimization is the database after the (M-1)th optimization.

[0101] The first optimization unit is used to optimize the database used in the Mth optimization based on the second parameter set used in the Mth optimization, so as to obtain the database after the Mth optimization.

[0102] The calculation unit is used to recalculate the comprehensive evaluation index value of each type of SQL statement based on the SQL statement set and the database after the Mth optimization, so as to obtain the comprehensive evaluation index value of each type of SQL statement after the Mth optimization.

[0103] The second optimization unit is used to input all the comprehensive evaluation index values ​​after the Mth optimization and the second parameter set used in the Mth optimization into the prediction model, so that the prediction model optimizes the second parameter set used in the Mth optimization based on all the comprehensive evaluation index values ​​after the Mth optimization, obtains the second parameter set after the Mth optimization, updates M = M+1, and performs the (M+1)th optimization until M equals the target threshold, and obtains the optimal parameter set.

[0104] As an optional example, the computing unit includes:

[0105] The execution subunit is used to input each SQL statement in the SQL statement set into the database after the Mth optimization, so that the database after the Mth optimization executes each SQL statement and outputs the running data of each SQL statement to obtain the first dataset after the Mth optimization.

[0106] The calculation subunit is used to classify and summarize the first dataset after the Mth optimization, obtain the running data of each type of SQL statement, and calculate the comprehensive evaluation index value of each type of SQL statement after the Mth optimization based on the running data of each type of SQL statement.

[0107] For other examples of this embodiment, please refer to the examples above, which will not be repeated here.

[0108] Figure 7 This is a schematic diagram of an optional electronic device according to an embodiment of this application, such as... Figure 7 As shown, it includes a processor 702, a communication interface 704, a memory 706, and a communication bus 708. The processor 702, communication interface 704, and memory 706 communicate with each other via the communication bus 708.

[0109] Memory 706 is used to store computer programs;

[0110] When processor 702 executes a computer program stored in memory 706, it performs the following steps:

[0111] Each SQL statement in the SQL statement set is input into the initial database so that the initial database executes each SQL statement and outputs the execution data of each SQL statement to obtain the first dataset. The SQL statement set includes multiple types of SQL statements, and the execution data includes the execution of SQL statements by the initial database, the performance consumption data of the server of each node of the initial database, and the detailed execution time of the SQL statement on each node.

[0112] The first dataset is categorized and summarized to obtain the execution data of each type of SQL statement. Based on the execution data of each type of SQL statement, the comprehensive evaluation index value of each type of SQL statement is calculated.

[0113] Based on the first dataset, the first set of parameters that needs to be optimized is analyzed from the full set of parameters in the initial database;

[0114] All comprehensive evaluation index values ​​and the first parameter set are input into the prediction model so that the prediction model can perform an initial optimization of the first parameter set based on all comprehensive evaluation index values, and obtain the second parameter set after the initial optimization.

[0115] The second parameter set is iteratively optimized using the initial database and the prediction model to obtain the optimal parameter set of the initial database.

[0116] Optionally, in this embodiment, the communication bus can be a PCI (Peripheral Component Interconnect) bus or an EISA (Extended Industry Standard Architecture) bus, etc. This communication bus can be divided into an address bus, a data bus, a control bus, etc. For ease of representation, Figure 7 The symbol is represented by a single thick line, but this does not indicate that there is only one bus or one type of bus. The communication interface is used for communication between the aforementioned electronic devices and other devices.

[0117] The memory may include RAM, or non-volatile memory, such as at least one disk storage device. Optionally, the memory may also be at least one storage device located remotely from the aforementioned processor.

[0118] As an example, the memory 706 described above may include, but is not limited to, the execution module 602, calculation module 604, analysis module 606, first optimization module 608, and second optimization module 610 of the database performance parameter optimization device described above. Furthermore, it may include, but is not limited to, other module units in the database performance parameter optimization device described above, which will not be elaborated upon in this example.

[0119] The processors mentioned above can be general-purpose processors, including but not limited to: CPU (Central Processing Unit), NP (Network Processor), etc.; they can also be DSP (Digital Signal Processor), ASIC (Application Specific Integrated Circuit), FPGA (Field-Programmable Gate Array), or other programmable logic devices, discrete gate or transistor logic devices, or discrete hardware components.

[0120] Optionally, specific examples in this embodiment can refer to the examples described in the above embodiments, and will not be repeated here.

[0121] Those skilled in the art will understand that Figure 7The structure shown is for illustrative purposes only. The device that implements the above database performance parameter optimization method can be a terminal device, such as a smartphone (e.g., Android phone, iOS phone), tablet computer, PDA, mobile Internet Devices (MID), PAD, etc. Figure 7 This does not limit the structure of the aforementioned electronic devices. For example, the electronic device may also include components that are more... Figure 7 The more or fewer components shown (such as network interfaces, display devices, etc.), or having the same Figure 7 The different configurations shown.

[0122] Those skilled in the art will understand that all or part of the steps in the various methods of the above embodiments can be implemented by a program instructing the hardware related to the terminal device. The program can be stored in a computer-readable storage medium, which may include: flash drive, ROM, RAM, disk or optical disk, etc.

[0123] According to another aspect of the embodiments of this application, a computer-readable storage medium is also provided, wherein a computer program is stored in the computer program, which, when executed by a processor, performs the steps in the above-described method for optimizing database performance parameters.

[0124] Optionally, in this embodiment, those skilled in the art will understand that all or part of the steps in the various methods of the above embodiments can be implemented by a program instructing the hardware related to the terminal device. The program can be stored in a computer-readable storage medium, which may include: flash drive, read-only memory (ROM), random access memory (RAM), disk or optical disk, etc.

[0125] The sequence numbers of the embodiments in this application are for descriptive purposes only and do not represent the superiority or inferiority of the embodiments.

[0126] If the integrated units in the above embodiments are implemented as software functional units and sold or used as independent products, they can be stored in the aforementioned computer-readable storage medium. Based on this understanding, the technical solution of this application, in essence, or the part that contributes to the prior art, or all or part of the technical solution, can be embodied in the form of a software product. This computer software product is stored in a storage medium and includes several instructions to cause one or more computer devices (which may be personal computers, servers, or network devices, etc.) to execute all or part of the steps of the methods described in the various embodiments of this application.

[0127] In the above embodiments of this application, the descriptions of each embodiment have different focuses. For parts not described in detail in a certain embodiment, please refer to the relevant descriptions of other embodiments.

[0128] In the several embodiments provided in this application, it should be understood that the disclosed client can be implemented in other ways. The device embodiments described above are merely illustrative; for example, the division of units is only a logical functional division, and in actual implementation, there may be other division methods. For example, multiple units or components may be combined or integrated into another system, or some features may be ignored or not executed. Furthermore, the coupling or direct coupling or communication connection shown or discussed may be through some interfaces, indirect coupling or communication connection between units or modules, and may be electrical or other forms.

[0129] The units described as separate components may or may not be physically separate. The components shown as units may or may not be physical units; that is, they may be located in one place or distributed across multiple network units. Some or all of the units can be selected to achieve the purpose of this embodiment according to actual needs.

[0130] Furthermore, the functional units in the various embodiments of this application 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.

[0131] The above description is only a preferred embodiment of this application. It should be noted that for those skilled in the art, several improvements and modifications can be made without departing from the principle of this application, and these improvements and modifications should also be considered within the scope of protection of this application.

Claims

1. A method for optimizing database performance parameters, characterized in that, include: Each SQL statement in the SQL statement set is input into the initial database, so that the initial database executes each SQL statement and outputs the execution data of each SQL statement to obtain the first dataset. The SQL statement set includes multiple types of SQL statements, and the execution data includes the performance consumption data of the server of each node of the initial database when the SQL statement is executed, and the detailed execution time of the SQL statement on each node. The first dataset is categorized and summarized to obtain the execution data of each type of SQL statement, and the comprehensive evaluation index value of each type of SQL statement is calculated based on the execution data of each type of SQL statement. Based on the first dataset, the first set of parameters that needs to be optimized is analyzed from the full set of parameters in the initial database; All the comprehensive evaluation index values ​​and the first parameter set are input into the prediction model, so that the prediction model performs an initial optimization of the first parameter set based on all the comprehensive evaluation index values, and obtains the second parameter set after the initial optimization. The second parameter set is iteratively optimized using the initial database and the prediction model to obtain the optimal parameter set of the initial database.

2. The method according to claim 1, characterized in that, The process of classifying and summarizing the first dataset to obtain the execution data for each type of SQL statement includes: Obtain the execution plan tree generated by the initial database when executing each of the SQL statements; Based on the fingerprint of the execution plan tree of each SQL statement, all SQL statements are classified to obtain multiple categories of SQL statements; The execution data of each SQL statement in the target class SQL statement is summarized to obtain the execution data of the target class SQL statement, wherein the target class SQL statement is any one of the multiple types of SQL statements.

3. The method according to claim 1, characterized in that, Before calculating the comprehensive evaluation index value for each type of SQL statement based on the execution data of each type of SQL statement, the method further includes: An adaptive algorithm is used to remove all outliers from the first dataset, resulting in an updated first dataset.

4. The method according to claim 1, characterized in that, The comprehensive evaluation index value for each type of SQL statement, calculated based on the execution data of each type, includes: For each type of SQL statement, treat it as the current type of SQL statement, and perform the following steps on the current type of SQL statement: Obtain the performance consumption data and detailed execution time of each execution stage of the current SQL statement, and convert them into comparable units; Assign weight factors to the performance consumption data and detailed execution time of each execution stage of the current SQL statement; The performance consumption data and detailed execution time of each execution stage of the current SQL statement are multiplied by the corresponding weighting factor to obtain the weighted result of each execution stage of the current SQL statement. The weighted results of each execution stage of the current SQL statement are summed to obtain the comprehensive evaluation index value of the current SQL statement.

5. The method according to claim 1, characterized in that, The step of analyzing the first set of parameters to be optimized from the full parameter set of the initial database based on the first dataset includes: Based on the first dataset, the bottleneck stage is determined; A third set of parameters related to the bottleneck stage is selected from the full set of parameters; Determine the optimization range for each parameter in the third parameter set; The first parameter set is obtained by summarizing the third parameter set and the optimization range of each parameter.

6. The method according to claim 1, characterized in that, The step of using the initial database and the prediction model to iteratively optimize the second parameter set multiple times to obtain the optimal parameter set of the initial database includes: Obtain the second parameter set and database used for the Mth optimization, wherein M is a positive integer greater than 1. When M is 2, the second parameter set used for the Mth optimization is the second parameter set after the initial optimization, and the database used for the Mth optimization is the initial database. When M is greater than 2, the second parameter set used for the Mth optimization is the second parameter set after the (M-1)th optimization, and the database used for the Mth optimization is the database after the (M-1)th optimization. Based on the second parameter set used in the Mth optimization, the database used in the Mth optimization is optimized to obtain the database after the Mth optimization; Based on the SQL statement set and the database after the Mth optimization, the comprehensive evaluation index value of each type of SQL statement is recalculated to obtain the comprehensive evaluation index value of each type of SQL statement after the Mth optimization. All the comprehensive evaluation index values ​​after the Mth optimization and the second parameter set used in the Mth optimization are input into the prediction model, so that the prediction model optimizes the second parameter set used in the Mth optimization based on all the comprehensive evaluation index values ​​after the Mth optimization, obtains the second parameter set after the Mth optimization, updates M = M + 1, and performs the (M+1)th optimization until M equals the target threshold, and obtains the optimal parameter set.

7. The method according to claim 6, characterized in that, The comprehensive evaluation index value for each type of SQL statement is recalculated based on the SQL statement set and the database after the Mth optimization, resulting in the comprehensive evaluation index value for each type of SQL statement after the Mth optimization, including: Each SQL statement in the SQL statement set is input into the database after the Mth optimization, so that the database after the Mth optimization executes each SQL statement and outputs the running data of each SQL statement to obtain the first dataset after the Mth optimization. The first dataset after the Mth optimization is classified and summarized to obtain the running data of each type of SQL statement. Based on the running data of each type of SQL statement, the comprehensive evaluation index value of each type of SQL statement after the Mth optimization is calculated.

8. A device for optimizing database performance parameters, characterized in that, include: An execution module is used to input each SQL statement in the SQL statement set into an initial database, so that the initial database executes each SQL statement and outputs the running data of each SQL statement to obtain a first dataset. The SQL statement set includes multiple types of SQL statements, and the running data includes the performance consumption data of the server of each node of the initial database executing the SQL statement and the detailed execution time of the SQL statement on each node. The calculation module is used to classify and summarize the first dataset to obtain the running data of each type of SQL statement, and calculate the comprehensive evaluation index value of each type of SQL statement based on the running data of each type of SQL statement. The analysis module is used to analyze the first set of parameters that need to be optimized from the full set of parameters in the initial database based on the first dataset; The first optimization module is used to input all the comprehensive evaluation index values ​​and the first parameter set into the prediction model, so that the prediction model performs an initial optimization on the first parameter set based on all the comprehensive evaluation index values ​​to obtain the second parameter set after the initial optimization. The second optimization module is used to perform multiple iterative optimizations on the second parameter set using the initial database and the prediction model to obtain the optimal parameter set of the initial database.

9. A computer-readable storage medium storing a computer program, characterized in that, The computer program is executed by the processor to perform the method described in any one of claims 1 to 7.

10. An electronic device comprising a memory and a processor, characterized in that, The memory stores a computer program, and the processor is configured to execute the method described in any one of claims 1 to 7 through the computer program.

Images