Automatic adjusting and optimizing method and device for database parameters and electronic equipment

Abstract

The invention relates to an automatic tuning method and device for database parameters and electronic equipment. The method comprises the steps of obtaining an adjustable parameter set and state data of an initial database; analyzing and determining a load type of the initial database according to the state data, and determining a target parameter tuning model according to the load type; carrying out importance identification on the adjustable parameter set, and selecting a first parameter set having great influence on performance; inputting the first parameter set into a target parameter tuning model to enable the target parameter tuning model to perform primary optimization on the first parameter set to obtain a second parameter set after primary optimization, and optimizing the initial database according to the second parameter to obtain a database after primary optimization; and using the initially optimized database and the target parameter tuning model to perform multiple iterative optimization on the initially optimized second parameter set to obtain an optimal parameter set of the initial database. The technical problem that a manual adjustment and optimization mode is difficult to meet the requirements of the database for performance and efficiency is solved.

Description

Technical Field

[0001] The present application relates to the field of database technology, and in particular to a method, device and electronic device for automatically tuning database parameters. Background Art

[0002] With the rapid development of technologies such as the Internet and the Internet of Things, the amount of data has exploded. Traditional centralized databases face significant performance bottlenecks and storage limitations when processing large-scale data. Against this backdrop, distributed databases have emerged and are developing rapidly. Compared with stand-alone databases, distributed database systems have a more complex structure, involving the collaborative work of multiple nodes and components. The mutual influence between different components makes distributed databases have hundreds of adjustable parameters. The settings of these parameters directly determine the performance of the database, including read and write performance, storage capacity, stability, etc. However, the default parameter configuration of distributed databases is usually not optimal and must be adjusted according to specific scenarios and requirements to maximize the overall performance of the system. Currently, most practical application scenarios still rely on experienced database administrators (DBAs) to manually set and tune parameters. Although DBAs can make reasonable configurations based on their experience, manual tuning is time-consuming and labor-intensive, and is susceptible to human errors. With the continuous expansion of data scale and the increase in complexity, traditional manual tuning methods can no longer meet the performance and efficiency requirements of modern databases. Summary of the invention

[0003] The present application provides a method, device and electronic device for automatically tuning database parameters to solve the technical problem that manual tuning methods are difficult to meet the requirements of database performance and efficiency.

[0004] In a first aspect, the present application provides a method for automatically tuning database parameters, comprising: obtaining an adjustable parameter set and status data of an initial database, wherein the status data comprises status data of the initial database before operation and status data of the initial database before and after operation; analyzing and determining the load type of the initial database according to the status data, and determining a target parameter tuning model according to the load type of the initial database, wherein one load type corresponds to one target parameter tuning model; using the least squares method to identify the importance of the adjustable parameter set, and selecting a first parameter set that has a great impact on performance; inputting the first parameter set into the target parameter tuning model, so that the target parameter tuning model performs an initial optimization on the first parameter set to obtain a second parameter set after initial optimization, and optimizing the initial database according to the second parameter to obtain a database after initial optimization; using the database after initial optimization and the target parameter tuning model to perform multiple iterative optimizations on the second parameter set after initial optimization to obtain an optimal parameter set for the initial database.

[0005] In a second aspect, the present application provides an automatic tuning device for database parameters, comprising: an acquisition module, used to acquire an adjustable parameter set and status data of an initial database, wherein the above status data includes status data of the above initial database before operation and status data before and after operation of the above initial database; a determination module, used to analyze and determine the load type of the above initial database according to the above status data, and determine a target parameter tuning model according to the load type of the above initial database, wherein one of the above load types corresponds to one of the above target parameter tuning models; an identification module, used to use the least squares method to identify the importance of the above adjustable parameter set, and select a first parameter set that has a great impact on performance; a first optimization module, used to input the above first parameter set into the above target parameter tuning model, so that the above target parameter tuning model performs an initial optimization on the above first parameter set to obtain a second parameter set after the initial optimization, and optimize the above initial database according to the above second parameters to obtain a database after the initial optimization; a second optimization module, used to use the above initially optimized database and the above target parameter tuning model to perform multiple iterative optimizations on the above first optimized second parameter set to obtain the optimal parameter set of the above initial database.

[0006] As an optional example, the above-mentioned acquisition module includes: a first acquisition unit, used to restart the above-mentioned initial database and obtain the status data of the above-mentioned initial database before running; a second acquisition unit, used to run the above-mentioned initial database, and after the above-mentioned initial database runs, obtain the status data of the above-mentioned initial database after running; an initialization unit, used to initialize the above-mentioned initial database.

[0007] As an optional example, the above-mentioned determination module includes: an extraction unit, used to extract features from the above-mentioned status data; a first identification unit, used to compare the above-mentioned features with historical status data based on the above-mentioned features, and identify the load type most similar to the above-mentioned features, wherein the above-mentioned load types include transactional loads, analytical loads and mixed loads.

[0008] As an optional example, the above-mentioned determination module includes: a first determination unit, used to determine that the above-mentioned target parameter tuning model is a genetic algorithm model when the load type of the above-mentioned initial database is a transactional load; a second determination unit, used to determine that the above-mentioned target parameter tuning model is a Bayesian optimization model when the load type of the above-mentioned initial database is an analytical load; a third determination unit, used to determine that the above-mentioned target parameter tuning model is a deep reinforcement learning algorithm model when the load type of the above-mentioned initial database is a mixed load.

[0009] As an optional example, the above-mentioned device also includes: a processing module, which is used to use the least squares method to identify the importance of the above-mentioned adjustable parameter set, and before selecting the first parameter set with a greater impact on performance, normalize each parameter value of the above-mentioned adjustable parameter set so that each parameter value of the above-mentioned adjustable parameter set is normalized within a target range.

[0010] As an optional example, the above-mentioned device also includes: a training module, which is used to input the above-mentioned first parameter set into the above-mentioned target parameter tuning model so that the above-mentioned target parameter tuning model performs an initial optimization on the above-mentioned first parameter set, and before obtaining the second parameter set after the initial optimization, perform iterative training on the initial parameter tuning model for a target number of times according to historical state data and historical parameter sets, and obtain the above-mentioned target parameter tuning model after the target number of iterative training.

[0011] As an optional example, the first optimization module includes: a third acquisition unit, used to acquire the second parameter set and database used for the Mth optimization, wherein the M is a positive integer greater than 1, the second parameter set used for the Mth optimization is the second parameter set after the M-1th optimization, and the database used for the Mth optimization is the database after the M-1th optimization; a fourth acquisition unit, used to acquire the adjustable parameter set and status data of the database used for the Mth optimization; a fourth determination unit, used to analyze and determine the load type of the database used for the Mth optimization according to the status data of the database used for the Mth optimization; a fifth determination unit, used to determine the target parameter tuning model used for the Mth optimization according to the load type of the database used for the Mth optimization; a second identification unit, used to use the least squares method to identify the target parameter tuning model used for the Mth optimization; The adjustable parameter sets of the database used in the M-th optimization are identified for importance, and the first parameter set used in the M-th optimization which has a great impact on the performance is selected; the first optimization unit is used to input the first parameter set used in the M-th optimization into the target parameter tuning model used in the M-th optimization, so that the target parameter tuning model used in the M-th optimization performs the M-th optimization on the first parameter set used in the M-th optimization to obtain the second parameter set after the M-th optimization; the third optimization unit is used to optimize the database used in the M-th optimization according to the second parameters after the M-th optimization to obtain the M-th optimized database; the fourth optimization unit is used to update M=M+1 and perform the M+1-th optimization until the M is equal to the target threshold value to obtain the second parameter set after the last optimization, and determine the second parameter set after the last optimization as the optimal parameter set.

[0012] In a third aspect, the present application provides a storage medium storing a computer program, wherein the computer program executes the above-mentioned method for automatically tuning database parameters when executed by a processor.

[0013] In a fourth aspect, the present application further provides an electronic device, comprising a memory and a processor, wherein the memory stores a computer program, and the processor is configured to execute the above-mentioned method for automatically tuning database parameters through the computer program.

[0014] The above technical solution provided by the embodiment of the present application has the following advantages compared with the prior art:

[0015] The present application adopts the method of obtaining the adjustable parameter set and status data of the initial database, wherein the above status data includes the status data of the above initial database before operation and the status data of the above initial database before and after operation; analyzing and determining the load type of the above initial database according to the above status data, and determining the target parameter tuning model according to the load type of the above initial database, wherein one of the above load types corresponds to one of the above target parameter tuning models; using the least squares method to identify the importance of the above adjustable parameter set, and selecting the first parameter set with the greatest impact on performance; inputting the above first parameter set into the above target parameter tuning model, so that the above target parameter tuning model performs the initial optimization on the above first parameter set, and obtains the second parameter set after the initial optimization, and optimizing the above initial database according to the above second parameters, and obtains the initial The method comprises the following steps: a) using the database after the initial optimization and the target parameter tuning model to perform multiple iterative optimizations on the second parameter set after the initial optimization to obtain the optimal parameter set of the initial database. In the above method, the optimal parameter configuration can be gradually obtained by accurately identifying the load type, selecting a suitable parameter tuning model, and performing parameter importance identification, and through multiple rounds of iterative optimization. This series of automated parameter tuning processes can not only improve the responsiveness and processing capabilities of the database under the current load, but also adapt to different load requirements in the future, achieve continuous optimization, and greatly improve the performance, stability and resource utilization efficiency of the distributed database system, thereby solving the technical problem that manual tuning methods are difficult to meet the performance and efficiency requirements of the database. BRIEF DESCRIPTION OF THE DRAWINGS

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

[0017] In order to more clearly illustrate the embodiments of the present application or the technical solutions in the prior art, the drawings required for use in the embodiments or the description of the prior art will be briefly introduced below. Obviously, for ordinary technicians in this field, other drawings can be obtained based on these drawings without paying any creative labor.

[0018] One or more embodiments are exemplarily described by pictures in the corresponding drawings, and these exemplified descriptions do not constitute limitations on the embodiments. Elements with the same reference numerals in the drawings represent similar elements, and unless otherwise stated, the figures in the drawings do not constitute proportional limitations.

[0019] Figure 1 is a flow chart of an optional automatic tuning method for database parameters according to an embodiment of the present application;

[0020] Figure 2 is an overall structural diagram of an optional automatic tuning method for database parameters according to an embodiment of the present application;

[0021] Figure 3 It is an overall architecture diagram of a parameter control module of an optional automatic tuning method of database parameters according to an embodiment of the present application;

[0022] Figure 4 It is an architecture diagram of a parameter recommendation module of an optional automatic tuning method of database parameters according to an embodiment of the present application;

[0023] Figure 5 is a schematic structural diagram of an optional automatic tuning device for database parameters according to an embodiment of the present application;

[0024] Figure 6 is a schematic diagram of an optional electronic device according to an embodiment of the present application. DETAILED DESCRIPTION

[0025] In order to make the purpose, technical solution and advantages of the embodiments of the present application clearer, the technical solution in the embodiments of the present application will be clearly and completely described below in conjunction with the drawings in the embodiments of the present application. Obviously, the described embodiments are part of the embodiments of the present application, not all of the embodiments. Based on the embodiments in the present application, all other embodiments obtained by ordinary technicians in this field without making creative work are within the scope of protection of this application.

[0026] The disclosure below provides many different embodiments or examples to realize the different structures of the present application. In order to simplify the disclosure of the present application, the parts and settings of specific examples are described below. Of course, they are only examples, and the purpose is not to limit the present application. In addition, the present application can repeat reference numbers and / or letters in different examples. This repetition is for the purpose of simplification and clarity, and does not itself indicate the relationship between the various embodiments and / or settings discussed.

[0027] According to a first aspect of an embodiment of the present application, a method for automatically tuning database parameters is provided. Optionally, as follows: Figure 1 As shown, the above method includes:

[0028] S102, obtaining an adjustable parameter set and state data of the initial database, wherein the state data includes state data before the initial database is run and state data before and after the initial database is run;

[0029] S104, analyzing and determining the load type of the initial database according to the state data, and determining a target parameter tuning model according to the load type of the initial database, wherein one load type corresponds to one target parameter tuning model;

[0030] S106, using the least square method to identify the importance of the adjustable parameter set, and selecting the first parameter set that has the greatest impact on the performance;

[0031] S108, inputting the first parameter set into the target parameter tuning model so that the target parameter tuning model performs a primary optimization on the first parameter set to obtain a second parameter set after the primary optimization, and optimizing the initial database according to the second parameter to obtain a database after the primary optimization;

[0032] S110, using the initially optimized database and the target parameter tuning model to perform multiple iterative optimizations on the first optimized second parameter set to obtain an optimal parameter set of the initial database.

[0033] Optionally, this application designs a system AutoTune that can provide automatic parameter tuning services for distributed databases. The overall structure is as follows: Figure 2 As shown in the figure, a parameter control module and a parameter recommendation module are added outside the distributed database. The parameter control module is used to realize data interaction and communication control between the database cluster and the parameter recommendation module. The parameter control module needs to collect the internal status and performance indicator data of the database cluster while the database cluster runs the workload, and send the data to the parameter recommendation module. The parameter recommendation module receives the data from the parameter control module, makes parameter recommendations, and feeds back the recommended parameters to the parameter control module.

[0034] Optionally, during a tuning process, the parameter control module obtains the runtime data of the database cluster as the data basis for parameter recommendation. The data includes the current configuration information of the database, internal status indicator information over a period of time, and performance. The parameter recommendation module obtains the tuning data provided by the parameter control module, extracts and analyzes the data through the tuning algorithm, and finally obtains the recommended configuration parameters. The parameter control module obtains the recommended configuration parameters obtained by the parameter recommendation module, converts the data format into the configuration file format of the database cluster, and facilitates the modification of the database cluster configuration. The database cluster obtains the newly generated configuration file issued by the parameter control module, backs up the original configuration file and replaces it with the new configuration file, and applies the new configuration to the cluster.

[0035] Optionally, the overall architecture of the parameter control module is as follows: Figure 3 As shown in the figure, it mainly includes the processes related to interaction with the database cluster and internal data processing. The data collection process is connected to the database cluster to complete the collection of database-related data. The collected data is transmitted to the parameter recommendation module after the data sorting process. The configuration parsing process is used to receive the recommended parameters passed by the parameter recommendation module, parse and modify them into the data format of the database configuration file. The configuration update process is used to send and apply the configuration file to the database cluster. The collected database cluster data includes database status information, such as the number of concurrent threads, CPU usage, memory usage, etc. of each node. This status information can reflect the internal operation status of the database. Based on this information, AutoTune can distinguish different loads. In the subsequent automatic parameter tuning, this status information will be divided into different load types and stored separately. By judging the status information, the same load type can be matched, and then further automatic parameter tuning can be performed based on the historical tuning experience of the same load type. In addition, AutoTune also needs to collect the adjustable parameter information of the database. Different parameter configurations will lead to different database status and performance. For example, when throughput is used as the tuning target of the system, the parameter configuration and throughput information of each tuning round will be collected and stored by AutoTune, and then used as historical experience in subsequent parameter recommendations. The entire data collection step includes two data collections, one is the collection of database status and parameter information before the load is run, and the other is the collection of database cluster status and performance information after the load is run. Among them, the cluster status information needs to be obtained twice before and after the load is run. Some statistical values ​​in this information will change before and after, and the required data can be obtained by calculating the difference. In the entire automatic parameter tuning process, multiple rounds of data collection will be performed, and the collected data will be sent to the parameter recommendation module, so as to complete the parameter recommendation task based on the collected data, and obtain the optimal recommended parameter configuration after multiple optimization algorithm iterations. When performing parameter tuning, it is not only based on historical data, but more importantly, it is to collect information and recommend parameters in real time according to the current load type, and iterate this process in a loop to dynamically recommend parameters to improve database performance.

[0036] Optionally, the parameter recommendation module is used to receive the data collected from the database cluster transmitted by the parameter control module, and then make parameter recommendations based on the data through various means with the goal of improving database performance. Figure 4As shown in the figure, it can be roughly divided into three parts: parameter importance identification, load matching and parameter recommendation. The information collected by the parameter control module from the database cluster includes a large amount of parameter information. These parameters first need to be identified by importance. The purpose of importance identification is to allow the parameter recommendation module to select the more important parameters for subsequent parameter recommendation work. The information collected by the parameter control module from the database cluster also includes the database status information when the load is run each time. According to the comparison results of the current status information and the historical status information, the parameter recommendation module can complete the load matching task and thus determine the load type. According to the historical experience data of the determined load, the parameter recommendation module can complete the final parameter recommendation task. This process can choose to use the genetic algorithm based on heuristic search, the Bayesian optimization algorithm or the DDPG reinforcement learning algorithm. AutoTune supports automatic parameter tuning of these three types of algorithms. Specifically, under different load environments, users can choose the method they want to use by themselves based on the historical tuning results of each algorithm. Finally, after obtaining the recommended results of the parameter configuration, the parameter recommendation module returns it to the parameter control module and finally applies it to the database cluster. Since there are many database parameters and the impact of each parameter on performance varies, each additional tuning parameter will significantly increase the scale of calculation, thereby increasing the difficulty of tuning and affecting the tuning effect. Therefore, it is very important to select the most important parameters from many parameters to narrow the tuning space. In order to select more important parameters, AutoTune uses ordinary least squares (OLS) to identify the importance of parameters. The OLS algorithm uses historical training data to perform correlation analysis on parameters and performance indicators to select more important parameters.

[0037] Optionally, the present application applies automatic parameter tuning to distributed databases and designs the AutoTune system. Considering the characteristics of distributed databases such as multiple nodes, flexible expansion, and variable load types, it can use certain historical load data to find relatively important high-performance parameter configurations, and periodically obtain the real-time status of the database to make tuning recommendations for database parameters to achieve the goal of improving database performance. Based on machine learning technology, combined with several tuning algorithms, in different scenarios such as high-dimensional parameter space or less historical load data, the algorithm with scenario advantages is flexibly selected for parameter tuning according to the tuning effect. It supports automatic tuning of distributed database parameters, makes decisions based on historical data, reduces the need for manual configuration and monitoring, reduces the workload of operation and maintenance personnel, and also avoids configuration problems caused by human errors. It is realized that when the database changes with the workload, the automatic tuning system can continuously optimize the database without manual intervention, and the automatic tuning makes decisions based on real-time collected data, which makes the optimization process more scientific and accurate. Over time, the system will continue to learn and improve its tuning strategy, thereby continuously improving performance. Automatic parameter tuning of distributed databases can not only significantly improve the overall performance and stability of the database, but also significantly reduce operation and maintenance costs and provide users with a better service experience.

[0038] Optionally, in this embodiment, the load type of the database is automatically identified through systematic steps, and the adjustable parameters of the database are optimized according to the load type and historical data, and the overall performance of the database is finally improved. Specifically, the state data of the database is preliminarily collected, including the operating state of the database under different loads, and all adjustable parameters (such as memory size, cache settings, thread pool configuration, etc.). The state data includes: data before the initial operation, such as performance indicators when the database is started, initial configuration of system resources, etc.; data after the initial operation, after the load is run, the state changes of the database at this time are recorded, including load processing, response time, CPU and memory usage, etc. Through the analysis of the initial database state data, the load type of the current database is identified. The determination of the load type usually depends on whether the query mode is OLTP (transactional load), OLAP (analytical load) or mixed load (HTAP). Each load type corresponds to a specific target parameter tuning model. According to the load type (such as OLTP, OLAP or mixed load), the most suitable tuning strategy model is selected. Each model will contain parameter tuning rules and algorithms applicable to the load type. The least squares method is used to identify the importance of parameters. The OLS algorithm is a regression analysis method that models the correlation between each adjustable parameter and the performance index through historical data to find the key parameters that most affect the database performance. The first parameter set identified (i.e., the parameters that have a greater impact on performance) is input into the target parameter tuning model for initial optimization. Through this model, the parameter set is adjusted to obtain the optimized second parameter set, and then these parameters are applied to the initial database to generate a preliminarily optimized database. Using the database after the initial optimization, the second parameter set is optimized multiple times in combination with the target parameter tuning model. In each iteration, by continuously adjusting the parameters, the optimal configuration is gradually approached, and the optimal parameter set is finally obtained. After multiple rounds of optimization, an optimal parameter set is finally obtained. This parameter set not only performs best under the current load, but also provides a reference for the operation of similar loads in the future.

[0039] Optionally, in this embodiment, each step of the entire tuning process contributes to the automatic tuning of database parameters, reducing human intervention while improving the performance and stability of the system. By accurately identifying the load type, selecting a suitable tuning model, identifying the importance of parameters, and through multiple rounds of iterative optimization, the optimal parameter configuration can be gradually obtained. This series of automated parameter tuning processes can not only improve the responsiveness and processing capabilities of the database under the current load, but also adapt to different load requirements in the future, achieve continuous optimization, and greatly improve the performance, stability, and resource utilization efficiency of the distributed database system.

[0040] As an optional example, obtaining the tunable parameter set and status data of the initial database includes:

[0041] Restart the initial database and obtain the status data before the initial database runs;

[0042] Run the initial database, and after the initial database runs, obtain the status data after the initial database runs;

[0043] Initialize the initial database.

[0044] Optionally, in this embodiment, before the tuning process begins, the database needs to be restarted first to return it to its initial state. The purpose of the restart is to ensure that the state of the database is clear and has not been affected by the last run or adjustment. After the restart, the system is in a cold start state. The collected pre-run status data includes but is not limited to hardware resource utilization, such as CPU usage, memory usage, disk I / O, etc.; database configuration parameters, such as memory cache settings, number of thread pools, log configuration, etc.; database load characteristics, such as whether the current database is in a high concurrency state, whether the load is read-write intensive, or large-scale query, etc.; system health status, such as the number of database connections, number of transactions, response time, etc. After the database is restarted and initialized, start the database and run a specific load or operation to simulate a real usage scenario. After the run is completed, collect the status data at this time. These data will reflect the performance of the database under the actual load. The collected post-operation status data includes but is not limited to system resource consumption, such as CPU usage, memory usage, disk and network I / O, etc.; database performance indicators, such as query response time, transaction processing speed, throughput, latency, etc.; database operation logs, which record errors, warnings, exceptions and other log information that may appear during the operation of the database; load statistics, which record the behavior of the database under different loads, such as the number of concurrent connections, the number of transactions, etc. After collecting the initial database status data, the database needs to be initialized to ensure that the database configuration is in a standardized initial state, which can include clearing possible caches, resetting database parameters, and restoring the database to the default configuration state.

[0045] Optionally, in this embodiment, by comparing the state data before and after the database is run, the system can identify the changes caused by the load and further infer the impact of the load. For example, the load may cause an increase in CPU usage and memory consumption, and these changes can help the tuning system identify performance bottlenecks and adjustment points.

[0046] As an optional example, analyzing and determining the load type of the initial database according to the status data includes:

[0047] Extract features from state data;

[0048] Based on the comparison between the characteristics and historical status data, the load type most similar to the characteristics is identified, where the load types include transactional load, analytical load, and mixed load.

[0049] Optionally, in this embodiment, features representing the current workload characteristics of the database are extracted from the state data of the database, and these features will provide a basis for the judgment of the load type. The extracted features include but are not limited to system resource consumption, database performance indicators, database transaction characteristics, database locks and wait events, query patterns, etc. After extracting the above features, the next step is to compare these features with historical load data to identify the most similar load type. Historical load data refers to the load feature data collected when the database was running in the past, which is usually marked as different load types (such as transactional, analytical, and mixed). The characteristic pattern of each load type is extracted from the historical data, and each load type has its typical characteristic performance. The features extracted from the initial database are matched with the features of the historical load type, and the historical load type closest to the current load is identified by comparison and calculation of similarity. The load type includes but is not limited to transactional load, analytical load, and mixed load. According to the comparison of historical data, the load type of the initial database is finally determined. Different load types have different effects on the performance of the database. Therefore, when tuning the database according to the load type, different parameter configuration strategies should be selected.

[0050] Optionally, in this embodiment, by extracting features from the state data and comparing them with historical data, the load type of the initial database can be identified. Accurately identifying the load type is crucial for database tuning, because different load types require different tuning strategies. Transactional loads, analytical loads, and mixed loads correspond to different performance bottlenecks and optimization directions, respectively. Only by correctly identifying the load type can the effectiveness of the tuning strategy and the improvement of database performance be ensured.

[0051] Optionally, after several rounds of database load operation and the above-mentioned parameter importance identification process, AutoTune has: a set of relatively more important parameters, historical database status and parameter data. The load types will be distinguished by the differences in internal indicators of the database, and the model will be trained to recommend corresponding configurations for different load types. AutoTune first compares the status information collected by the current parameter control module with the status information in the previous historical load, matches the historical load that is most similar to the current running load, and then performs training and configuration recommendations based on the historical data of the matched load, ultimately achieving the effect of using historical experience to accelerate the tuning process.

[0052] As an optional example, determining a target parameter tuning model according to the load type of the initial database includes:

[0053] When the initial database load type is a transactional load, the target parameter tuning model is determined to be a genetic algorithm model;

[0054] When the load type of the initial database is analytical load, the target parameter tuning model is determined to be a Bayesian optimization model;

[0055] When the load type of the initial database is a mixed load, the target parameter tuning model is determined to be a deep reinforcement learning algorithm model.

[0056] Optionally, in this embodiment, after the above load matching, using the same type of historical load data, AutoTune can use different parameter tuning models to recommend parameter configurations. In the design of the configuration recommendation process, AutoTune embeds three different parameter tuning models for selection: genetic algorithm model, Bayesian optimization model and DDPG (Deep Deterministic Policy Gradient) algorithm model. The genetic algorithm model is a heuristic search method that can search through given rules, which can reduce the search range and improve the effect to a certain extent. The Bayesian optimization model, a machine learning-based method, can improve the parameter tuning effect by learning historical experience, and the functional relationship between the Gaussian process regression fitting parameters and performance is used to give the possible value range of the function curve. By continuously adding sample points (i.e., the database parameters and performance data collected by AutoTune in each tuning round), the fitting function is updated to gradually approach the real curve, and the horizontal coordinate corresponding to the maximum value point on the function curve is found, which is the recommended configuration parameter. The DDPG algorithm model is a reinforcement learning method that uses a neural network to learn historical experience and uses a policy network and a value network to optimize the results.

[0057] Optionally, in this embodiment, transactional loads (OLTP) usually have high concurrency, small transactions, and low latency requirements. The optimization goal may be to increase throughput, reduce lock contention, increase query speed, etc. Typical tuning parameters may include connection pool size, cache settings, number of concurrent threads, lock strategy, etc. Then, the genetic algorithm model can be used preferentially to explore suitable database configurations. Analytical loads (OLAP) usually require large-scale data analysis, complex queries, and focus on query optimization and batch data processing. The optimization goal may be to reduce query response time, increase throughput, optimize data scanning and index usage, etc. Typical tuning parameters may include query cache, index optimization, memory allocation, parallel execution settings, etc., and the Bayesian optimization algorithm model can be used to efficiently find the optimal parameter configuration. Hybrid loads contain the characteristics of both OLTP and OLAP, and may need to optimize query performance and transaction processing performance at the same time. Deep reinforcement learning algorithm models can be used to perform dynamic tuning based on the combination of real-time feedback and historical data.

[0058] As an optional example, before using the least squares method to identify the importance of the adjustable parameter set and selecting the first parameter set with the greatest impact on the performance, the method further includes:

[0059] Each parameter value of the tunable parameter set is normalized so that each parameter value of the tunable parameter set is normalized to be within a target range.

[0060] Optionally, in this embodiment, the database data used in each part are normalized, and the maximum value of the historical data is used as the range boundary. The parameter, state and performance index values ​​are normalized to the range of [0,1]. For discrete parameters, the values ​​are equally divided from 0 to 1. The purpose of this is to make all data at the same level, so that when performing similar parameter importance identification or reinforcement learning training, the influence of each parameter is consistent.

[0061] As an optional example, before inputting the first parameter set into the target parameter tuning model so that the target parameter tuning model performs an initial optimization on the first parameter set to obtain a second parameter set after the initial optimization, the method further includes:

[0062] The initial parameter tuning model is iteratively trained for a target number of times according to the historical state data and the historical parameter set, and after the target number of iterative training, a target parameter tuning model is obtained.

[0063] Optionally, in this embodiment, by iteratively training the historical status data and historical parameter sets for a target number of times, a target parameter tuning model is eventually obtained. The model can recommend the optimal parameter configuration according to different load types and database states. In the process of automatic database tuning, this target model plays a core role. It can greatly improve the tuning efficiency, reduce manual intervention, and dynamically optimize database performance according to real-time load.

[0064] As an optional example, the first optimized database and the target parameter tuning model are used to perform multiple iterations of optimization on the second parameter set after the first optimization, and the optimal parameter set of the initial database is obtained, including:

[0065] Obtain the second parameter set and database used for the Mth optimization, wherein M is a positive integer greater than 1, the second parameter set used for the Mth optimization is the second parameter set after the M-1th optimization, and the database used for the Mth optimization is the database after the M-1th optimization;

[0066] Obtain the adjustable parameter set and status data of the database used for the Mth optimization;

[0067] According to the status data of the database used in the Mth optimization, the load type of the database used in the Mth optimization is analyzed and determined;

[0068] According to the load type of the database used in the Mth optimization, determine the target parameter tuning model used in the Mth optimization;

[0069] Use the least square method to identify the importance of the adjustable parameter set of the database used in the Mth optimization, and select the first parameter set used in the Mth optimization that has a great impact on performance;

[0070] Inputting the first parameter set used in the M-th optimization into the target parameter tuning model used in the M-th optimization, so that the target parameter tuning model used in the M-th optimization performs the M-th optimization on the first parameter set used in the M-th optimization, and obtaining the second parameter set after the M-th optimization;

[0071] Optimizing the database used for the M-th optimization according to the second parameter after the M-th optimization to obtain the M-th optimized database;

[0072] Update M=M+1, and perform the M+1th optimization until M is equal to the target threshold, obtain the second parameter set after the last optimization, and determine the second parameter set after the last optimization as the optimal parameter set.

[0073] Optionally, in this embodiment, during the automatic parameter tuning process of the database, the second parameter set is optimized multiple times iteratively using the database after the initial optimization and the target parameter tuning model, and finally the optimal parameter set is obtained. Specifically, the second parameter set and the database are obtained: the second parameter set used for the Mth optimization is obtained by the second parameter set after the M-1th optimization, and the database used for the Mth optimization is the database after the M-1th optimization. The current adjustable parameter set and the database status data (such as resource usage, load information, etc.) are extracted from the database. According to the status data of the current database (such as memory usage, CPU load, number of concurrent threads, etc.), the load type of the current database is analyzed and determined. The corresponding target parameter tuning model is selected according to the load type of the database. For example, if it is a transactional load, the model may focus on cache size, number of concurrency, etc.; if it is an analytical load, the model may optimize query cache, index strategy, etc. The adjustable parameter set of the database is regressed and analyzed using the least squares method to evaluate the impact of each parameter on performance, and the first parameter set that most significantly affects performance is screened out to ensure that only the most critical parameters are optimized, reducing unnecessary calculations and optimization space. The first parameter set is input into the selected target parameter tuning model, and the model is used to optimize the first parameter set to obtain the second parameter set after the Mth optimization. The database is optimized according to the second parameter set after the Mth optimization. This process adjusts the performance of the database by modifying the configuration parameters of the database, which may involve modifying the cache size, the number of concurrent threads, the I / O scheduling strategy, etc. The optimized database state should be more efficient than before the optimization, with better performance indicators such as response speed and resource utilization. After completing the Mth optimization, the number of iterations is updated (M=M+1), and then the next round of optimization is entered. Repeat the above steps, use the second parameter set after the Mth optimization and the optimized database for the next round of optimization, and gradually improve the performance of the database. When the number of optimizations reaches the set target threshold (for example, set a maximum of 10 rounds of optimization), stop the iteration. At this time, the second parameter set after multiple iterations of optimization is the optimal parameter set, which can provide the best performance of the database under the current load.

[0074] Optionally, in this embodiment, the optimal parameter set finally obtained will be used for the long-term configuration of the database to ensure that the database continues to run efficiently under a specific load. Through this multiple iterative optimization method, the automatic tuning system can optimize database performance by gradually adjusting parameters under complex load conditions, reducing human intervention while improving system stability.

[0075] It should be noted that, for the aforementioned method embodiments, for the sake of simplicity, they are all expressed as a series of action combinations, but those skilled in the art should be aware that the present application is not limited by the described order of actions, because according to the present application, certain steps can be performed in other orders or simultaneously. Secondly, those skilled in the art should also be aware that the embodiments described in the specification are all preferred embodiments, and the actions and modules involved are not necessarily required by the present application.

[0076] According to another aspect of the embodiment of the present application, a device for automatically tuning database parameters is also provided. Figure 5 As shown, including:

[0077] An acquisition module 502 is used to acquire an adjustable parameter set and state data of the initial database, wherein the state data includes state data before the initial database is run and state data before and after the initial database is run;

[0078] The determination module 504 is used to analyze and determine the load type of the initial database according to the state data, and determine the target parameter tuning model according to the load type of the initial database, wherein one load type corresponds to one target parameter tuning model;

[0079] An identification module 506 is used to identify the importance of the adjustable parameter set using the least square method, and select a first parameter set that has a great impact on the performance;

[0080] A first optimization module 508 is used to input the first parameter set into the target parameter tuning model so that the target parameter tuning model performs a primary optimization on the first parameter set to obtain a second parameter set after the primary optimization, and optimize the initial database according to the second parameter to obtain a database after the primary optimization;

[0081] The second optimization module 510 is used to perform multiple iterative optimizations on the second parameter set after the initial optimization using the database after the initial optimization and the target parameter tuning model to obtain the optimal parameter set of the initial database.

[0082] It should be noted that the acquisition module 502 in this embodiment can be used to execute step S102 in the embodiment of the present application, the determination module 504 in this embodiment can be used to execute step S104 in the embodiment of the present application, the identification module 506 in this embodiment can be used to execute step S106 in the embodiment of the present application, the first optimization module 508 in this embodiment can be used to execute step S108 in the embodiment of the present application, and the second optimization module 510 in this embodiment can be used to execute step S110 in the embodiment of the present application.

[0083] As an optional example, the acquisition module includes:

[0084] A first acquisition unit, used to restart the initial database and acquire the state data of the initial database before it runs;

[0085] A second acquisition unit is used to run the initial database and, after the initial database is run, acquire the state data of the initial database after the run;

[0086] The initialization unit is used to initialize the initial database.

[0087] As an optional example, the determination module includes:

[0088] An extraction unit, used for extracting features from the state data;

[0089] The first identification unit is used to identify the load type most similar to the feature based on the comparison between the feature and the historical status data, wherein the load type includes a transactional load, an analytical load, and a mixed load.

[0090] As an optional example, the determination module includes:

[0091] A first determining unit is used to determine that the target parameter tuning model is a genetic algorithm model when the load type of the initial database is a transaction load;

[0092] A second determination unit is used to determine that the target parameter tuning model is a Bayesian optimization model when the load type of the initial database is an analytical load;

[0093] The third determination unit is used to determine that the target parameter tuning model is a deep reinforcement learning algorithm model when the load type of the initial database is a mixed load.

[0094] As an optional example, the above device further includes:

[0095] The processing module is used to normalize each parameter value of the adjustable parameter set before using the least square method to identify the importance of the adjustable parameter set and selecting the first parameter set with the greatest impact on performance, so that each parameter value of the adjustable parameter set is normalized to a target range.

[0096] As an optional example, the above device further includes:

[0097] The training module is used to input the first parameter set into the target parameter tuning model so that the target parameter tuning model performs an initial optimization on the first parameter set, and before obtaining the second parameter set after the initial optimization, perform an iterative training of the initial parameter tuning model for a target number of times according to the historical state data and the historical parameter set, and obtain the target parameter tuning model after the target number of iterative training.

[0098] As an optional example, the first optimization module includes:

[0099] A third acquisition unit is used to acquire a second parameter set and a database used for the M-th optimization, wherein M is a positive integer greater than 1, the second parameter set used for the M-th optimization is the second parameter set after the M-1-th optimization, and the database used for the M-th optimization is the database after the M-1-th optimization;

[0100] A fourth acquisition unit, used to acquire an adjustable parameter set and state data of the database used for the Mth optimization;

[0101] A fourth determining unit, configured to analyze and determine a load type of a database used in the Mth optimization according to the state data of the database used in the Mth optimization;

[0102] A fifth determining unit, configured to determine a target parameter tuning model used for the Mth optimization according to a load type of a database used for the Mth optimization;

[0103] The second identification unit is used to identify the importance of the adjustable parameter set of the database used in the M-th optimization by using the least square method, and select the first parameter set used in the M-th optimization that has a great impact on the performance;

[0104] A first optimization unit is used to input the first parameter set used in the M-th optimization into the target parameter tuning model used in the M-th optimization, so that the target parameter tuning model used in the M-th optimization performs the M-th optimization on the first parameter set used in the M-th optimization to obtain a second parameter set after the M-th optimization;

[0105] A third optimization unit is used to optimize the database used for the M-th optimization according to the second parameter after the M-th optimization to obtain the M-th optimized database;

[0106] The fourth optimization unit is used to update M=M+1 and perform the M+1th optimization until M is equal to the target threshold, obtain the second parameter set after the last optimization, and determine the second parameter set after the last optimization as the optimal parameter set.

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

[0108] Figure 6 is a schematic diagram of an optional electronic device according to an embodiment of the present application, such as Figure 6 As shown, it includes a processor 602, a communication interface 604, a memory 606 and a communication bus 608, wherein the processor 602, the communication interface 604 and the memory 606 communicate with each other through the communication bus 608, wherein,

[0109] Memory 606, used to store computer programs;

[0110] The processor 602 is used to implement the following steps when executing the computer program stored in the memory 606:

[0111] Obtaining an adjustable parameter set and state data of the initial database, wherein the state data includes state data before the initial database runs and state data before and after the initial database runs;

[0112] According to the status data, the load type of the initial database is analyzed and determined, and the target parameter tuning model is determined according to the load type of the initial database, wherein one load type corresponds to one target parameter tuning model;

[0113] Use the least squares method to identify the importance of the adjustable parameter set and select the first parameter set that has the greatest impact on performance;

[0114] Inputting the first parameter set into the target parameter tuning model so that the target parameter tuning model performs a primary optimization on the first parameter set to obtain a second parameter set after the primary optimization, and optimizing the initial database according to the second parameter to obtain a database after the primary optimization;

[0115] The second parameter set after the initial optimization is optimized multiple times by using the database after the initial optimization and the target parameter tuning model to obtain the optimal parameter set of the initial database.

[0116] Optionally, in this embodiment, the communication bus may be a PCI (Peripheral Component Interconnect) bus or an EISA (Extended Industry Standard Architecture) bus. The communication bus may be divided into an address bus, a data bus, a control bus, etc. For ease of representation, Figure 6 The communication interface is used for communication between the above electronic device and other devices.

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

[0118] As an example, the memory 606 may include, but is not limited to, the acquisition module 502, the determination module 504, the identification module 506, the first optimization module 508, and the second optimization module 510 in the automatic tuning device for database parameters. In addition, other module units in the automatic tuning device for database parameters may also be included but are not limited to, which will not be described in detail in this example.

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

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

[0121] It can be understood by those skilled in the art that Figure 6 The structure shown is for illustration only. The device for implementing the above-mentioned automatic tuning method of database parameters may be a terminal device, which may be a smart phone (such as an Android phone, an iOS phone, etc.), a tablet computer, a PDA, a mobile Internet device (Mobile Internet Devices, MID), a PAD, and other terminal devices. Figure 6 The structure of the electronic device is not limited. For example, the electronic device may also include Figure 6 More or fewer components (such as network interfaces, display devices, etc.) shown in, or having Figure 6 Different configurations shown.

[0122] A person of ordinary skill in the art can understand that all or part of the steps in the various methods of the above embodiments can be completed by instructing the hardware related to the terminal device through a program, and the program can be stored in a computer-readable storage medium, which can include: a flash drive, ROM, RAM, a magnetic disk or an optical disk, etc.

[0123] According to another aspect of an embodiment of the present application, a computer-readable storage medium is provided, in which a computer program is stored, wherein the computer program executes the steps in the above-mentioned method for automatically tuning database parameters when executed by a processor.

[0124] Optionally, in this embodiment, a person of ordinary skill in the art may understand that all or part of the steps in the various methods of the above embodiments may be completed by instructing hardware related to the terminal device through a program, and the program may be stored in a computer-readable storage medium, and the storage medium may include: a flash drive, a read-only memory (ROM), a random access memory (RAM), a disk or an optical disk, etc.

[0125] The serial numbers of the above-mentioned embodiments of the present application are for description only and do not represent the advantages or disadvantages of the embodiments.

[0126] If the integrated units in the above embodiments are implemented in the form of software functional units and sold or used as independent products, they can be stored in the above computer-readable storage medium. Based on this understanding, the technical solution of the present application, 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, which is stored in a storage medium and includes several instructions for enabling 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 the present application.

[0127] In the above embodiments of the present application, the description of each embodiment has its own emphasis. For parts that are not described in detail in a certain embodiment, please refer to the relevant description of other embodiments.

[0128] In the several embodiments provided in the present application, it should be understood that the disclosed client can be implemented in other ways. Among them, the device embodiments described above are only schematic. For example, the division of the units is only a logical function division. There may be other division methods in actual implementation. For example, multiple units or components can be combined or integrated into another system, or some features can be ignored or not executed. Another point is that the mutual coupling or direct coupling or communication connection shown or discussed can be through some interfaces, indirect coupling or communication connection of units or modules, which can be electrical or other forms.

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

[0130] In addition, each functional unit in each embodiment of the present application may be integrated into one processing unit, or each unit may exist physically separately, or two or more units may be integrated into one unit. The above-mentioned integrated unit may be implemented in the form of hardware or in the form of software functional units.

[0131] The above is only a preferred embodiment of the present application. It should be pointed out that for ordinary technicians in this technical field, several improvements and modifications can be made without departing from the principles of the present application. These improvements and modifications should also be regarded as the scope of protection of the present application.

Claims

1. A method for automatically tuning database parameters, characterized in that: include: Acquire an adjustable parameter set and state data of an initial database, wherein the state data includes state data before the initial database is run and state data before and after the initial database is run; Analyze and determine the load type of the initial database according to the state data, and determine a target parameter tuning model according to the load type of the initial database, wherein one load type corresponds to one target parameter tuning model; Using the least square method to identify the importance of the adjustable parameter set, and selecting the first parameter set that has the greatest impact on performance; Inputting the first parameter set into the target parameter tuning model so that the target parameter tuning model performs a primary optimization on the first parameter set to obtain a second parameter set after the primary optimization, and optimizing the initial database according to the second parameter to obtain a database after the primary optimization; The first optimized database and the target parameter tuning model are used to perform multiple iterative optimizations on the first optimized second parameter set to obtain an optimal parameter set of the initial database.

2. The method according to claim 1, characterized in that The step of obtaining the adjustable parameter set and status data of the initial database includes: Restarting the initial database to obtain state data of the initial database before it runs; Running the initial database, and after the running of the initial database is finished, obtaining the state data of the initial database after the running; Initialize the initial database.

3. The method according to claim 1, characterized in that The analyzing and determining the load type of the initial database according to the state data includes: extracting features from the state data; Based on the comparison between the feature and the historical status data, the load type most similar to the feature is identified, wherein the load type includes a transactional load, an analytical load, and a mixed load.

4. The method according to claim 3, characterized in that Determining a target parameter tuning model according to the load type of the initial database includes: In a case where the load type of the initial database is a transactional load, determining that the target parameter tuning model is a genetic algorithm model; In a case where the load type of the initial database is an analytical load, determining that the target parameter tuning model is a Bayesian optimization model; When the load type of the initial database is a mixed load, the target parameter tuning model is determined to be a deep reinforcement learning algorithm model.

5. The method according to claim 1, characterized in that Before using the least square method to identify the importance of the adjustable parameter set and selecting the first parameter set with the greatest impact on performance, the method further includes: Normalization is performed on each parameter value of the adjustable parameter set so that each parameter value of the adjustable parameter set is normalized to within a target range.

6. The method according to claim 1, characterized in that Before inputting the first parameter set into the target parameter tuning model so that the target parameter tuning model performs a primary optimization on the first parameter set to obtain a second parameter set after the primary optimization, the method further includes: The initial parameter tuning model is iteratively trained for a target number of times according to the historical state data and the historical parameter set, and after the target number of iterative training, the target parameter tuning model is obtained.

7. The method according to claim 1, characterized in that The method of performing multiple iterations of optimization on the first optimized second parameter set using the first optimized database and the target parameter tuning model to obtain the optimal parameter set of the initial database includes: Obtaining a second parameter set and a database used for the Mth optimization, wherein M is a positive integer greater than 1, the second parameter set used for the Mth optimization is the second parameter set after the M-1th optimization, and the database used for the Mth optimization is the database after the M-1th optimization; Obtaining an adjustable parameter set and status data of the database used in the M-th optimization; Analyze and determine the load type of the database used in the M-th optimization according to the state data of the database used in the M-th optimization; Determining a target parameter tuning model used for the Mth optimization according to a load type of the database used for the Mth optimization; Using the least squares method to identify the importance of the adjustable parameter set of the database used in the M-th optimization, and selecting the first parameter set used in the M-th optimization that has a great impact on performance; Inputting the first parameter set used in the M-th optimization into the target parameter tuning model used in the M-th optimization, so that the target parameter tuning model used in the M-th optimization performs an M-th optimization on the first parameter set used in the M-th optimization to obtain a second parameter set after the M-th optimization; Optimizing the database used in the M-th optimization according to the second parameter after the M-th optimization to obtain the M-th optimized database; Update M=M+1, and perform the M+1th optimization until M is equal to the target threshold, obtain the second parameter set after the last optimization, and determine the second parameter set after the last optimization as the optimal parameter set.

8. An automatic tuning device for database parameters, characterized in that: include: An acquisition module, used to acquire an adjustable parameter set and state data of an initial database, wherein the state data includes state data before the initial database is run and state data before and after the initial database is run; A determination module, configured to analyze and determine the load type of the initial database according to the state data, and determine a target parameter tuning model according to the load type of the initial database, wherein one load type corresponds to one target parameter tuning model; An identification module, used to identify the importance of the adjustable parameter set using a least square method, and select a first parameter set that has a great impact on performance; A first optimization module, configured to input the first parameter set into the target parameter tuning model, so that the target parameter tuning model performs a primary optimization on the first parameter set to obtain a second parameter set after the primary optimization, and optimize the initial database according to the second parameter to obtain a database after the primary optimization; The second optimization module is used to perform multiple iterative optimizations on the second parameter set after the initial optimization using the database after the initial optimization and the target parameter tuning model to obtain the optimal parameter set of the initial database.

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

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

Images