Database tuning method, device and system

Abstract

The present application discloses a database tuning method, device, and system. The method includes: generating at least one offline load request, and for one of the offline load requests, receiving performance parameters of offline training of the offline database by each slave node to determine the optimal feasible solution corresponding to the offline load request; obtaining a database configuration model using each offline load request and the optimal feasible solution corresponding to each offline load request; determining an online load request for the online database, and obtaining the optimal feasible solution for the online database using the database configuration model and the online load request; and updating the online database using the optimal feasible solution for the online database, wherein the operating environment and database version of the offline database on each slave node are the same.

Description

Technical Field

[0001] The present application relates to the technical field of database systems, and more specifically, to a database tuning method, device, and system. Background Art

[0002] A database is a computer software system that organizes, stores, and manages data according to its structure. Users can add, query, update, and delete data in the database.

[0003] For users who use databases for data storage and processing, database performance is crucial. Poor database performance can cause business systems to malfunction or even be interrupted. Database performance, in turn, is determined by database configuration parameters. These parameters affect database operations and significantly impact performance metrics such as overall throughput and response time. Therefore, selecting the appropriate configuration parameters for specific database application scenarios can significantly improve database performance.

[0004] However, database configuration parameters are numerous (for example, MySQL has hundreds of them), and most users only use databases for basic purposes and lack experience in database performance tuning. Therefore, efficient database tuning (i.e., selecting appropriate database configuration parameters) remains a pressing issue. Summary of the Invention

[0005] To solve related technical problems, embodiments of the present application provide a database tuning method, device, and system.

[0006] The technical solution of the embodiment of the present application is implemented as follows:

[0007] This application provides a database tuning method, applied to a master node, including:

[0008] generating at least one offline load request, and for one of the offline load requests, receiving performance parameters of offline training performed by each slave node on the offline database to determine an optimal feasible solution corresponding to the offline load request;

[0009] A database configuration model is obtained using each offline load request and the optimal feasible solution corresponding to each offline load request;

[0010] Determining an online load request of an online database, and obtaining an optimal feasible solution for the online database using the database configuration model and the online load request;

[0011] Updating the online database using the optimal feasible solution of the online database,

[0012] The operating environment and database version of the offline database on each slave node are the same.

[0013] In the above solution, the offline database is:

[0014] The slave node is based on the database built on the application container engine Docker.

[0015] In the above solution, the receiving of performance parameters of offline training of the offline database by each slave node to determine the optimal feasible solution corresponding to the offline load request includes:

[0016] Sending a feasible solution to each slave node, where the feasible solution is used to update the offline database on the corresponding slave node;

[0017] Receive performance parameters of the corresponding offline database under the corresponding offline load request sent by each slave node;

[0018] When there is at least one offline database whose corresponding performance parameter is greater than or equal to the performance threshold α, randomly select a feasible solution corresponding to the at least one offline database as the optimal feasible solution;

[0019] When the performance parameters corresponding to the multiple offline databases are all less than the performance threshold α, the multiple feasible solutions sent to the multiple slave nodes are iteratively processed to determine the optimal feasible solution.

[0020] In the above solution, the iterative processing of the multiple feasible solutions sent to the multiple slave nodes to determine the optimal feasible solution includes:

[0021] Using a genetic algorithm to update the multiple feasible solutions to obtain an optimized feasible solution;

[0022] Sending the optimized feasible solution to each slave node respectively;

[0023] The performance parameters of the offline database under the offline load request sent by each slave node are received to determine an optimal feasible solution corresponding to the offline load request.

[0024] In the above solution, the use of a genetic algorithm to update the multiple feasible solutions to obtain an optimized feasible solution includes:

[0025] Binary encoding the multiple feasible solutions to obtain multiple binary feasible solutions;

[0026] Selecting the multiple binary feasible solutions to obtain multiple binary feasible solution combinations of two each;

[0027] Cross the two binary feasible solutions in each binary feasible solution combination to obtain a cross feasible solution;

[0028] At least one binary bit in each crossover feasible solution is mutated to obtain the optimized feasible solution.

[0029] In the above solution, the selection of the multiple binary feasible solutions to obtain multiple binary feasible solution combinations of two each includes:

[0030] Calculate the fitness probability of each binary feasible solution;

[0031] The plurality of binary feasible solutions are selected according to the fitness probability to obtain a plurality of binary feasible solution combinations of two each.

[0032] The calculation formula of the fitness probability is:

[0033]

[0034] The fitness is proportional to the performance parameter.

[0035] In the above solution, the two binary feasible solutions include a first binary feasible solution and a second binary feasible solution, and the step of crossing the two binary feasible solutions in each binary feasible solution combination to obtain a crossed feasible solution includes:

[0036] The cross feasible solution is obtained by combining the first to n binary bits of the first binary feasible solution with the n+1th to last binary bits of the second binary feasible solution.

[0037] In the above solution, the receiving of the performance parameters of the offline database under the offline load request sent by each slave node to determine the optimal feasible solution corresponding to the offline load request includes:

[0038] When there is at least one updated offline database whose corresponding performance parameter is greater than or equal to the performance threshold α, randomly select a feasible solution corresponding to the at least one updated offline database as the optimal feasible solution; or

[0039] When the number of iterative processing times reaches a maximum number N, the feasible solution corresponding to the updated offline database with the largest performance parameter in the Nth iteration is taken as the optimal feasible solution.

[0040] In the above solution, the database configuration model is obtained by using each offline load request and the optimal feasible solution corresponding to each offline load request, including:

[0041] Determining a first characteristic parameter corresponding to each offline load request;

[0042] Perform multi-objective multivariate linear regression fitting on the first characteristic parameters and the optimal feasible solution corresponding to the multiple offline load requests to obtain a multi-objective multivariate linear function as the database configuration model. The multi-objective multivariate linear function is:

[0043]

[0044] in:

[0045] Independent variable A first characteristic parameter corresponding to an offline load request;

[0046] Dependent variable Multiple configuration parameters included in an optimal feasible solution for representing an offline load request;

[0047] is a constant,

[0048] Through the multi-objective multiple linear regression fitting, the parameters The parameter matrix of .

[0049] In the above solution, determining the online load request of the online database and obtaining the optimal feasible solution for the online database using the database configuration model and the online load request includes:

[0050] Determining a second characteristic parameter corresponding to the online load request of the online database;

[0051] The second characteristic parameter is input into the database configuration model as an independent variable to obtain an optimal feasible solution of the online database.

[0052] The present application also provides a database tuning device, which is provided on a master node and includes a processor and a memory for storing a computer program that can be run on the processor.

[0053] Wherein, when the processor runs the computer program, the steps of the method are performed.

[0054] The present application also provides a database tuning system, comprising: a master node and multiple slave nodes; wherein,

[0055] The multiple slave nodes are respectively used to build an offline database;

[0056] The master node performs offline training on an offline database constructed on each of the multiple slave nodes for each of the multiple offline load requests to determine an optimal feasible solution corresponding to each offline load request; obtains a database configuration model using the multiple offline load requests and the multiple optimal feasible solutions; determines an online load request of an online database, obtains an optimal feasible solution for the online database using the database configuration model and the online load request; and updates the online database using the optimal feasible solution for the online database.

[0057] The operating environments and database versions of the multiple offline databases constructed on the multiple slave nodes are the same.

[0058] The database tuning method, device, and system provided by the embodiments of the present application include: a master node generates at least one offline load request; for one of the offline load requests, receives performance parameters of offline training of the offline database by each slave node to determine the optimal feasible solution corresponding to the offline load request; obtains a database configuration model using each offline load request and the optimal feasible solution corresponding to each offline load request; determines an online load request for the online database, obtains the optimal feasible solution for the online database using the database configuration model and the online load request; and updates the online database using the optimal feasible solution of the online database, wherein the operating environment and database version of the offline database on each slave node are the same. The technical solution proposed in the present application constructs a distributed cluster by introducing a master node and multiple slave nodes, wherein the master node is used to uniformly schedule each slave node, and offline training is performed in parallel by multiple slave nodes to improve the computational efficiency of the offline training process, thereby improving the overall efficiency of database tuning; at the same time, offline databases with the same operating environment and database version are constructed on multiple slave nodes to ensure that the database performance parameters corresponding to each feasible solution calculated under the same offline load request are comparable, that is, to ensure the reliability of the database tuning results. BRIEF DESCRIPTION OF THE DRAWINGS

[0059] Figure 1 A flowchart of a database tuning method according to an embodiment of the present application is shown;

[0060] Figure 2 A schematic diagram of a process for determining an optimal feasible solution corresponding to an offline load request according to an embodiment of the present application;

[0061] Figure 3 A schematic diagram of a flow chart of iteratively processing multiple feasible solutions to determine an optimal feasible solution according to an embodiment of the present application;

[0062] Figure 4 A schematic diagram of a process of using a genetic algorithm to update the multiple feasible solutions to obtain an optimized feasible solution according to an embodiment of the present application;

[0063] Figure 5 A schematic diagram of the structure of a binary feasible solution of an embodiment of the present application;

[0064] Figure 6 A schematic diagram of a process for obtaining a cross-feasible solution according to an embodiment of the present application;

[0065] Figure 7 A schematic diagram of a process for obtaining the optimized feasible solution according to an embodiment of the present application;

[0066] Figure 8 A schematic diagram of a process for obtaining a database configuration model using multiple offline load requests and multiple optimal feasible solutions according to an embodiment of the present application;

[0067] Figure 9 A schematic diagram of a process for determining an optimal feasible solution for an online database according to a database configuration model according to an embodiment of the present application;

[0068] Figure 10 A flowchart of a database tuning method according to an embodiment of the present application;

[0069] Figure 11 A schematic diagram of the offline training process of an embodiment of the present application;

[0070] Figure 12 This is a schematic diagram of the structure of a database tuning device according to an embodiment of the present application;

[0071] Figure 13 This is a structural diagram of a database tuning device according to an embodiment of the present application. DETAILED DESCRIPTION

[0072] The present application will be described in further detail below with reference to the accompanying drawings and embodiments.

[0073] In related technologies, database tuning mainly includes two solutions: database administrator (DBA) tuning and tuning based on traditional machine learning.

[0074] DBA tuning means that the DBA monitors the database and optimizes the database configuration parameters based on the monitoring results. In related technologies, DBA tuning can include the following basic processes: (1) setting reasonable performance optimization goals, (2) measuring and recording the current database performance, (3) determining the current database performance bottleneck (for example, what are the database wait events, which SQL statements correspond to the wait events), (4) recording the wait events in the trace file, (5) determining the current operating system bottleneck (for example, CPU usage, device usage, and virtual memory usage, etc.), (6) optimizing the required parts (for example, applications, database parameters, I / O interfaces, resource competition, operating systems, etc.), (7) tracking and changing the control process in real time, (8) measuring and recording the current database performance, and (9) repeating steps 3-7 until the optimization goals are met.

[0075] Therefore, DBA tuning involves analyzing and adjusting the hardware, operating system, database version, and database load. The DBA then manually observes and re-tune the optimized database performance, ultimately determining the optimal database configuration parameters. Therefore, the effectiveness of DBA tuning relies on the DBA's database optimization experience, resulting in high labor costs. Furthermore, repeated observation and re-tuning by the DBA can lead to low efficiency, making this unrealistic for cloud server providers with large user bases.

[0076] Traditional machine learning-based database tuning typically involves two phases: offline training and online tuning. During offline training, the offline database is updated with different configuration parameters to obtain the performance parameters of the offline database under those parameters. Based on these performance parameters, offline training results are obtained, which are then used to optimize the online database.

[0077] It can be seen that in offline training, in order to obtain an offline training result set including multiple sets of offline training results, it is necessary to use the database configuration parameters to update the offline database, and usually multiple algorithm iterations need to be performed on the configuration parameters. In each iteration, the computing nodes used to build the offline database need to calculate the database performance parameters corresponding to the corresponding configuration parameters, which brings great computing pressure to the computing nodes; at the same time, it is necessary to use multiple sets of configuration parameters to update the offline database in sequence, so the training process has a serious training serialization problem, which makes the training time-consuming and the training efficiency low.

[0078] Related technologies include the use of clusters, that is, solutions that increase overall computing performance by adding computing nodes. However, during offline training for database tuning, it is necessary to ensure that the performance parameters calculated for each configuration parameter combination under the same load request are comparable. Therefore, the computing nodes used to build the offline database are usually restricted to the same computer to ensure the uniformity of the database computing environment (i.e., the same hardware and software environment such as CPU and memory). Therefore, simply using clusters to improve overall computing performance does not guarantee the comparability of the calculation results of each computing node in the cluster. In addition, since database optimization is an overall algorithm optimization, how to achieve unified scheduling of each node in the cluster after adding clusters is also a problem to be solved.

[0079] Therefore, how to improve database tuning efficiency and obtain reliable tuning results remains an urgent problem to be solved.

[0080] Based on this, in each embodiment of the present application, a master node and multiple slave nodes are introduced to construct a distributed cluster, wherein the master node is used to uniformly schedule the slave nodes, and offline training is performed in parallel by multiple slave nodes to improve the computational efficiency of the offline training process, thereby improving the overall efficiency of database tuning; at the same time, an offline database with the same operating environment and database version is constructed on multiple slave nodes to ensure that the database performance parameters corresponding to each feasible solution calculated under the same offline load request are comparable, that is, to ensure the reliability of the database tuning results.

[0081] The embodiment of the present application provides a database tuning method, which is applied to the master node, such as Figure 1 As shown, the method includes:

[0082] Step 101: Generate at least one offline load request. For one of the offline load requests, receive performance parameters of offline training of an offline database by each slave node to determine an optimal feasible solution corresponding to the offline load request.

[0083] Step 102: Obtain a database configuration model using each offline load request and the optimal feasible solution corresponding to each offline load request;

[0084] Step 103: determining an online load request of an online database, and obtaining an optimal feasible solution for the online database using the database configuration model and the online load request;

[0085] Step 104: Update the online database using the optimal feasible solution of the online database.

[0086] The operating environment and database version of the offline database on each slave node are the same.

[0087] In actual applications, the master node and multiple slave nodes can be electronic devices such as computers and servers with high data processing capabilities.

[0088] In one embodiment, the master node and each slave node communicate via the Transmission Control Protocol (TCP), enabling the master node to issue offline load requests, feasible solutions, and other control instructions to each slave node. Simultaneously, each slave node provides feedback to the master node, including performance parameters. This allows multiple slave nodes to form a cluster, replacing the database performance parameter calculation task typically performed by a single compute node with multiple compute nodes—in other words, switching from serial to parallel processing. This improves the overall computation speed during offline training, reduces the computational burden on individual compute nodes, and thus enhances database tuning efficiency. A distributed system is formed between the master node and multiple slave nodes, enabling the master node to centrally schedule multiple slave nodes.

[0089] In actual application, in step 101, the master node can use a standard load stress testing tool (such as Sysbench3) to randomly generate at least one offline load request for stress testing the offline database. An offline load request is a connection request to the offline database built on the slave node, primarily including operations such as adding (inserting data), deleting (deleting data), modifying (changing data), and querying (querying data) data in the offline database.

[0090] Specifically, the master node uses Sysbench3 to randomly generate an offline load request (e.g., the first offline load request) and sends it to each slave node. Each slave node uses the first offline load request to stress test the corresponding offline database, namely, accessing the corresponding offline database and performing the add, delete, modify, and query operations included in the first offline load request. Each slave node calculates the performance parameters of the offline database under the first offline load request and sends these performance parameters to the master node. The master node uses the performance parameters returned by each slave node to determine the first optimal feasible solution for the first offline load request and adds the first offline load request and the first optimal feasible solution to the optimal database configuration set. Subsequently, the master node continues to use Sysbench3 to randomly generate the next offline load request (e.g., the second offline load request) and performs the offline training process described above to determine the second optimal feasible solution corresponding to the second offline load request. The second offline load request and the second optimal feasible solution are added to the optimal database configuration set. By performing the above offline training process for at least one offline load request, the master node can calculate and obtain an optimal database configuration set including a correspondence between at least one set of offline load requests and an optimal feasible solution, and obtain a database configuration model using the optimal database configuration set in step 102.

[0091] The optimal feasible solution is a set of database configuration parameters, and the performance parameters of the offline database updated using the set of database configuration parameters under the stress test of the offline load request are equal to or greater than the threshold performance parameters.

[0092] As mentioned above, the operating environment (such as CPU, memory, etc.) and database version of the offline database built on each slave node are the same. Therefore, when stress testing each offline database using the same load request, since the operating environment and database version of each slave node are the same, the only difference lies in the configuration parameters of the offline database built on each slave node. Therefore, the performance parameters of the offline database calculated by each slave node can only represent the impact of different configuration parameters on database performance under the same load request. In other words, for database configuration parameter tuning, the calculation results on each slave node are comparable, making the database tuning results more reliable.

[0093] In one embodiment, each slave node builds an offline database based on the application container engine (Docker). Docker is an open-source application container engine that allows developers to package their applications and dependencies into a portable container and then deploy this container to any machine running a Linux or Windows operating system. Therefore, developers can create an identical database operating environment (e.g., CPU, memory, etc.) and build the same offline database version within Docker, then deploy the Docker container to each slave node. This ensures that each offline database maintains the same operating environment and database version based on Docker, while also reducing the need for identical computers on each slave node.

[0094] In step 102, the database configuration model may be a correspondence between each offline load request and an optimal feasible solution corresponding to each offline load request, for example, a functional relationship.

[0095] In step 103, the online database is optimized using the offline training result, that is, using the database configuration model, to obtain the optimal feasible solution of the online database.

[0096] In step 104, in actual application, the master node uses the Update statement in the SQL statement to update the optimal feasible solution of the online database to the online database to complete the tuning of the online database.

[0097] As can be seen from the above, in the embodiment proposed in the present application, a distributed cluster system consisting of a master node and multiple slave nodes is introduced, wherein the performance parameters of the offline database are calculated on the slave node, the performance parameters sent by each slave node are collected on the master node, and the database configuration model is obtained based on the offline load request and the collected performance parameters. Compared with the solution in the related art in which a single computing node undertakes the performance parameter calculation task, the efficiency of performance parameter calculation is greatly improved, thereby improving the overall time of database tuning; secondly, an offline database with the same operating environment and database version is constructed on multiple slave nodes to ensure that the database performance parameters corresponding to each feasible solution calculated under the same offline load request are comparable, that is, to ensure the reliability of the database tuning results; finally, the embodiment proposed in the present application does not require a professional DBA to analyze and adjust the hardware, operating system, database version, database load, etc., as well as observe and re-tune the optimized database performance, thereby saving labor costs.

[0098] The following is a detailed description of the process by which the master node determines the optimal feasible solution.

[0099] In the embodiment of the present application, the process of determining the optimal feasible solution is as follows: Figure 2 , including the following steps:

[0100] Step 201: Send a feasible solution to each slave node, where the feasible solution is used to update the offline database on the corresponding slave node;

[0101] Step 202: receiving the performance parameters of the corresponding offline database under the corresponding offline load request sent by each slave node;

[0102] Step 203: When there is at least one offline database whose corresponding performance parameter is greater than or equal to the performance threshold α, randomly select a feasible solution corresponding to one of the at least one offline databases as the optimal feasible solution; when the performance parameters corresponding to the multiple offline databases are all less than the performance threshold α, iteratively process the multiple feasible solutions sent to the multiple slave nodes to determine the optimal feasible solution.

[0103] In step 201, the feasible solution is a set of database configuration parameters that can be used to adjust database performance, such as the buffer pool size (innodb_buffer_pool_size), the number of buffer pools (innodb_buffer_pool_instances), the maximum number of active connections (maxActive), the maximum number of idle connections (maxIdle), etc. The master node generates different database configuration parameter combinations (i.e., different feasible solutions) by assigning different values ​​to the above database configuration parameters.

[0104] In actual application, each slave node uses the Update statement in the SQL command to update the feasible solution sent by the master node to the corresponding offline database.

[0105] In step 202, each slave node uses the offline

[0106] The load request performs a stress test on the offline database and calculates performance parameters of the offline database under the offline load request.

[0107] In actual applications, the performance parameter is the throughput performance of the offline database under offline load requests. For example, the number of requests / queries processed per second (QPS) by the offline database is used as the performance parameter. Specifically, the QPS calculation process is as follows:

[0108] Use the SQL query statement "show global status like 'qestions'" to query the total number of database queries;

[0109] Total query time for querying data using the SQL query statement "show global status like 'status'";

[0110] Then QPS can be calculated by the following formula:

[0111]

[0112] In step 203, in actual application, the performance threshold α can be set by the database tuning personnel according to the tuning target.

[0113] The process of iteratively processing the multiple feasible solutions to determine the optimal feasible solution in step 203 is described in detail below.

[0114] In the embodiment of the present application, the process of iteratively processing the multiple feasible solutions to determine the optimal feasible solution is as follows: Figure 3 , including the following steps:

[0115] Step 301: using a genetic algorithm to update the multiple feasible solutions to obtain an optimized feasible solution;

[0116] Step 302: Send the optimized feasible solution to each slave node respectively;

[0117] Step 303: Receive the performance parameters of the offline database under the offline load request sent by each slave node to determine the optimal feasible solution corresponding to the offline load request.

[0118] In step 301, in order to use all slave nodes for offline training during the iteration process, the number of optimized feasible solutions obtained after the plurality of feasible solutions are updated using a genetic algorithm is the same as the number of the plurality of feasible solutions.

[0119] In actual application, in step 302, each slave node updates the optimized feasible solution sent by the master node to the corresponding offline database using the Update algorithm in the SQL statement.

[0120] In step 303, each slave node performs a stress test on the corresponding offline database using the offline load request sent by the master node, and then calculates the performance parameters of each offline database under the offline load request and sends the performance parameters to the master node.

[0121] The process of the master node determining the optimal feasible solution corresponding to the offline load request based on the performance parameters sent by each slave node specifically includes:

[0122] When there is at least one updated offline database whose corresponding performance parameter is greater than or equal to the performance threshold α, randomly select a feasible solution corresponding to the at least one updated offline database as the optimal feasible solution; or

[0123] When the number of iterative processing times reaches a maximum number N, the feasible solution corresponding to the updated offline database with the largest performance parameter in the Nth iteration is taken as the optimal feasible solution.

[0124] In step 301, the master node uses a genetic algorithm to update the multiple feasible solutions to obtain an optimized feasible solution. Figure 4 , including the following steps:

[0125] Step 401: binary-encode the multiple feasible solutions to obtain multiple binary feasible solutions;

[0126] Step 402: Select the multiple binary feasible solutions to obtain multiple binary feasible solution combinations of two each.

[0127] Step 403: Cross the two binary feasible solutions in each binary feasible solution combination to obtain a cross feasible solution;

[0128] Step 404: mutate at least one binary bit in each crossover feasible solution to obtain the optimized feasible solution.

[0129] In step 401, since the genetic algorithm cannot directly process feasible solutions of the database, it is necessary to encode the feasible solutions into binary form, that is, to encode them into binary feasible solutions, so as to facilitate selection, crossover and mutation operations.

[0130] In actual application, since the feasible solution is a combination of database configuration parameters, in the embodiment of the present application, the master node performs binary encoding on the feasible solution according to the mapping relationship between the predefined database configuration parameters and the binary string. Figure 5 For example, the binary feasible solution 500 includes binary string parts corresponding to database configuration parameters 1, 2, 3, 4 and 5 respectively.

[0131] In step 402, the master node may use a roulette algorithm to implement "selection", and the specific process is as follows:

[0132] The master node calculates the fitness probability of each binary feasible solution according to the fitness function, and then selects according to the fitness probability of each binary feasible solution to obtain multiple binary feasible solution combinations in groups of two.

[0133] The fitness function is an evaluation function used to measure the fitness of a feasible solution. In practical applications, the performance parameters of the offline database (such as the QPS value) calculated on each slave node are used as the fitness function of the corresponding feasible solution.

[0134] In practical applications, the fitness probability can be the weight of the fitness of a specific binary feasible solution relative to the sum of the fitness of all binary feasible solutions. Specifically, taking feasible solution i as an example, the fitness probability of feasible solution i is calculated as:

[0135]

[0136] Since fitness is measured using a fitness function, and in practical applications, performance parameters are used as fitness functions, the fitness is proportional to the performance parameters. Therefore, the greater the performance parameter, the greater the fitness of a feasible solution, and the higher the probability of that feasible solution being selected. This ensures that after several iterations, the probability of obtaining the optimal feasible solution increases.

[0137] In actual application, in order to make the number of optimized feasible solutions the same as the number of the multiple feasible solutions, the master node uses a roulette algorithm to select binary feasible solutions and the number of binary feasible solution combinations obtained is the same as the number of the multiple feasible solutions.

[0138] In step 403, refer to Figure 6 Taking a binary feasible solution combination including a first binary feasible solution 601 and a second binary feasible solution 602 as an example, the process of the master node obtaining a cross feasible solution 603 based on the first binary feasible solution 601 and the second binary feasible solution 602 specifically includes:

[0139] The 1st to nth binary bits of the first binary feasible solution 601 are combined with the n+1th to last binary bits of the second binary feasible solution 602 , and the combined binary feasible solution is used as the cross feasible solution 603 .

[0140] However, crossover still involves selecting from the original set of feasible solutions; it merely swaps the positions of the parameters in the different feasible solutions, resulting in a calculation result closer to the local optimal feasible solution. Therefore, in step 404, mutation operations are continued on the crossover feasible solutions to overcome the limitations of the current parameter range, which is more conducive to finding the global optimal feasible solution.

[0141] Reference Figure 7 The method of mutating at least one binary bit in the cross feasible solution 701 to obtain the optimized feasible solution 702 specifically includes:

[0142] At least one binary bit of crossover feasible solution 701 is randomly selected for mutation. For example, for crossover feasible solution 701, the mth bit is randomly selected and the original binary bit "0" is mutated to "1", thereby obtaining optimized feasible solution 702. Similarly, two or more binary bits can be randomly selected for mutation in crossover feasible solution 701.

[0143] The following describes in detail how the master node obtains a database configuration model using the multiple offline load requests and multiple optimal feasible solutions.

[0144] The process of the master node obtaining the database configuration model is as follows: Figure 8 , including the following steps:

[0145] Step 801: Determine a first characteristic parameter corresponding to each offline load request;

[0146] Step 802: Perform multi-objective multivariate linear regression fitting on the first characteristic parameters and the optimal feasible solution corresponding to the multiple offline load requests to obtain a multi-objective multivariate linear function as the database configuration model. The multi-objective multivariate linear function is:

[0147] ;

[0148] in:

[0149] Independent variable x i1 、 x i2 … x ip+1 A first characteristic parameter corresponding to an offline load request;

[0150] Dependent variables y1, y2, ... y nMultiple configuration parameters included in an optimal feasible solution for representing an offline load request;

[0151] ε 1i 、 ε 2i … ε ni is a constant,

[0152] Through the multi-objective multiple linear regression fitting, the parameters β The parameter matrix of .

[0153] In step 801, in actual application, the master node uses feature engineering to extract the first feature parameter of the offline load request.

[0154] The first characteristic parameter is a set of parameters corresponding to a feasible solution for the database. In practical applications, the first characteristic parameter can be a key value corresponding to the database workload. Database workload can be obtained by using the SQL command "showstatus" to obtain relevant internal database statistics, such as pg_stat_archiver.archived_count, pg_stat_archiver.failed_count, pg_stat_bgwriter.checkpoints_timed, and pg_stat_database.deadlocks.

[0155] Next, determine the online load request of the online database, and use the database configuration model and the online load request to obtain the optimal feasible solution of the online database, referring to Figure 9 , including the following steps:

[0156] Step 901: Determine a second characteristic parameter corresponding to the online load request of the online database;

[0157] Step 902: Input the second characteristic parameter as an independent variable into the database configuration model to obtain the optimal feasible solution of the online database.

[0158] In step 901, in actual application, the master node uses feature engineering to extract the second feature parameter corresponding to the online load request.

[0159] The online database is updated using the optimal feasible solution of the online database to complete database tuning.

[0160] The present application will be described in further detail below in conjunction with application examples.

[0161] like Figure 10As shown, the database tuning method provided by this application embodiment includes the following steps:

[0162] Step 1001: Perform offline training on the offline database built on each slave node, and then execute step 1002;

[0163] Here, the master node generates at least one offline load request. For one of the offline load requests, the master node sends a feasible solution to each slave node and uses the feasible solution to perform offline training on the offline database constructed on each slave node.

[0164] Among them, each slave node builds an offline database with the same operating environment and database version based on Docker.

[0165] Step 1002: Obtain the optimal feasible solution corresponding to each offline load request based on the offline training results, and then execute step 1003;

[0166] Specifically, the master node receives offline trained performance parameters from each slave node to determine an optimal feasible solution corresponding to the one offline load request.

[0167] Step 1003: Obtain a database configuration model using each offline load request and the optimal feasible solution corresponding to each offline load request, and then execute step 1005;

[0168] Specifically, the master node determines the first characteristic parameter corresponding to each offline load request. For example, the master node uses feature engineering to extract the first characteristic parameter corresponding to each offline load request; the first characteristic parameter corresponding to each offline load request and the optimal feasible solution are fitted with a multi-objective multivariate linear regression to obtain a multi-objective multivariate linear function as a database configuration model.

[0169] Step 1004: Determine a second characteristic parameter corresponding to the online load request of the online database, and then execute step 1005;

[0170] Specifically, the master node uses feature engineering to extract the second feature parameter corresponding to the online load request of the online database.

[0171] Step 1005: Substitute the second characteristic parameter into the database configuration model to obtain the optimal feasible solution of the online database, and then execute step 1006;

[0172] Step 1006: Update the online database using the optimal feasible solution of the online database.

[0173] Regarding the offline training part involved in the above steps 1001 and 1002, the following is combined with Figure 11 A further detailed description will be given.

[0174] like Figure 11As shown, the offline training part for an offline load request in the database tuning method provided by this application embodiment specifically includes the following steps:

[0175] Step 1101: Randomly generate an offline load request, and then execute step 1102;

[0176] The master node may randomly generate the at least one offline load request using a standard load stress testing tool (eg, Sysbench 3). The feasible solution is a set of database configuration parameters that can adjust database performance.

[0177] Step 1102: Initialize the offline database on each slave node, and then execute step 1103;

[0178] Specifically, each slave node uses the feasible solution sent by the master node and updates the corresponding offline database through the Update statement in the SQL command.

[0179] Step 1103: Calculate the performance parameters of each offline database under the stress test of the offline load request, and then execute step 1104;

[0180] Specifically, each slave node uses the offline load request sent by the master node to perform stress testing on the corresponding offline database, and then calculates the performance parameters of the corresponding offline database, for example, the QPS value of the offline database.

[0181] Step 1104: Collect the performance parameters of each offline database under the stress test of the offline load request, and then execute step 1105;

[0182] Step 1105: Determine whether the performance parameters of each offline database meet the threshold requirements. If there are performance parameters that are equal to or greater than the performance parameter threshold α, proceed to step 1109; if there are no performance parameters that are equal to or greater than the performance parameter threshold α, proceed to step 1106.

[0183] The master node compares the received at least one performance parameter with a preset performance parameter threshold α to determine whether there is a performance parameter in the received at least one performance parameter that is equal to or greater than the performance parameter threshold α.

[0184] Step 1106: Binary encode multiple feasible solutions, and then execute step 1107;

[0185] The master node performs binary encoding on multiple feasible solutions according to a predefined mapping relationship to obtain multiple binary feasible solutions.

[0186] Step 1107: Use a genetic algorithm to update multiple binary feasible solutions, and then execute step 1108;

[0187] Specifically, the master node first uses a roulette wheel algorithm to select multiple binary feasible solutions, obtaining multiple binary feasible solution combinations of two. During the roulette wheel algorithm's selection of multiple binary feasible solutions, the offline database's performance parameter calculated in step 1103 during the previous iteration is used as the fitness function for the corresponding feasible solution in the current iteration. Thus, offline databases with larger performance parameters have a greater probability of selecting the corresponding feasible solution, thereby improving the quality of the updated feasible solution in terms of improving database performance.

[0188] Then, the master node crosses the two binary feasible solutions in each binary feasible solution combination. Figure 6 , combining the 1st to nth binary bits of the first binary feasible solution 601 with the n+1th to last binary bits of the second binary feasible solution 602 to obtain a cross feasible solution 603 .

[0189] Finally, the master node performs mutation operations on each crossover feasible solution. Specifically, refer to Figure 7 , randomly select at least one binary bit of cross feasible solution 701 for mutation. For example, for cross feasible solution 701, randomly select the mth bit and mutate the original binary bit "0" to "1", thereby obtaining optimized feasible solution 702. Similarly, two or more binary bits can be randomly selected in cross feasible solution 701 for mutation.

[0190] Step 1108: Update the offline database using the optimized feasible solution, and then execute step 1103;

[0191] Specifically, the master node sends multiple optimized feasible solutions to each slave node respectively, and each slave node uses the optimized feasible solution and the Update statement in the SQL command to update the corresponding offline database.

[0192] Step 1109: Determine the optimal feasible solution for the offline load request.

[0193] Specifically, the master node randomly selects one offline database with a performance parameter equal to or greater than a performance parameter threshold α, and uses the current feasible solution (database configuration parameters) of the randomly selected offline database as the optimal feasible solution.

[0194] The solution provided by this application embodiment introduces a master node and multiple slave nodes to build a distributed cluster, wherein the master node is used to uniformly schedule the slave nodes, and offline training is performed in parallel by multiple slave nodes to improve the computing efficiency of the offline training process, thereby improving the overall efficiency of database tuning; at the same time, an offline database with the same operating environment and database version is built on multiple slave nodes based on Docker, which can ensure that the database performance corresponding to each feasible solution calculated by each slave node under the same offline load request is comparable, that is, the reliability of the database tuning results is guaranteed. At the same time, building an offline database based on Docker can reduce the requirements for the identity of each slave node computer.

[0195] In order to implement the database tuning method of the embodiment of the present application, the embodiment of the present application also proposes a database tuning device, which is set on the master node, such as Figure 12 As shown, the device includes:

[0196] A generating unit 1201 is configured to generate at least one offline load request;

[0197] Communication unit 1202, used for exchanging information with each slave node;

[0198] The computing unit 1203 is configured to perform offline training on the offline database using the performance parameters of each slave node received by the communication unit 1202 for an offline load request; determine an optimal feasible solution corresponding to the offline load request; obtain a database configuration model using each offline load request and the optimal feasible solution corresponding to each offline load request; determine an online load request for the online database, and obtain an optimal feasible solution for the online database using the database configuration model and the online load request;

[0199] An updating unit 1204 is configured to update the online database using the optimal feasible solution of the online database.

[0200] The operating environment and database version of the offline database on each slave node are the same.

[0201] In one embodiment, the slave node builds an offline database based on the application container engine docker.

[0202] In one embodiment, the communication unit 1202 is configured to send a feasible solution to each slave node, where the feasible solution is used to update an offline database on the corresponding slave node; receive performance parameters of the corresponding offline database under the corresponding offline load request from each slave node;

[0203] The calculation unit 1203 is configured to randomly select a feasible solution corresponding to one of the at least one offline databases as the optimal feasible solution when a performance parameter corresponding to at least one offline database is greater than or equal to a performance threshold α; and iteratively process the multiple feasible solutions sent to the multiple slave nodes to determine the optimal feasible solution when the performance parameters corresponding to the multiple offline databases are all less than the performance threshold α.

[0204] In one embodiment:

[0205] The calculation unit 1203 is configured to update the multiple feasible solutions using a genetic algorithm to obtain an optimized feasible solution;

[0206] The communication unit 1202 is configured to send the optimized feasible solution to each slave node respectively; and receive performance parameters of the offline database under the offline load request sent by each slave node to determine the optimal feasible solution corresponding to the offline load request.

[0207] In one embodiment, the computing unit 1203 is configured to perform binary encoding on the multiple feasible solutions to obtain multiple binary feasible solutions; select the multiple binary feasible solutions to obtain multiple binary feasible solution combinations in groups of two; cross two binary feasible solutions in each binary feasible solution combination to obtain a cross feasible solution; and mutate at least one binary bit in each cross feasible solution to obtain the optimized feasible solution.

[0208] In one embodiment, the calculation unit 1203 is configured to calculate the fitness probability of each binary feasible solution; select the multiple binary feasible solutions according to the fitness probability to obtain multiple binary feasible solution combinations of two.

[0209] The calculation formula of the fitness probability is:

[0210]

[0211] The fitness is proportional to the performance parameter.

[0212] In one embodiment, the two binary feasible solutions include a first binary feasible solution and a second binary feasible solution.

[0213] The calculation unit 1203 is configured to combine the first to nth binary bits of the first binary feasible solution with the n+1th to last binary bits of the second binary feasible solution to obtain the cross feasible solution.

[0214] In one embodiment, the calculation unit 1203 is configured to randomly select a feasible solution corresponding to one of the at least one updated offline databases as the optimal feasible solution when there is at least one updated offline database corresponding to a performance parameter greater than or equal to a performance threshold α; or, when the number of iterative processing times reaches a maximum number N, select a feasible solution corresponding to the updated offline database having the largest performance parameter in the Nth iteration as the optimal feasible solution.

[0215] In one embodiment, the calculation unit 1203 is configured to determine a first characteristic parameter corresponding to each offline load request; perform multi-objective multivariate linear regression fitting on the first characteristic parameter corresponding to each offline load request and the optimal feasible solution to obtain a multi-objective multivariate linear function as the database configuration model, where the multi-objective multivariate linear function is:

[0216]

[0217] in:

[0218] Independent variable x i1 、 x i2 … x ip+1 A first characteristic parameter corresponding to an offline load request;

[0219] Dependent variables y1, y2, ... y n Multiple configuration parameters included in an optimal feasible solution for representing an offline load request;

[0220] ε 1i 、 ε 2i … ε ni is a constant,

[0221] Through the multi-objective multiple linear regression fitting, the parameters β The parameter matrix of .

[0222] In one embodiment, the calculation unit 1203 is configured to determine a second characteristic parameter corresponding to the online load request of the online database; and input the second characteristic parameter as an independent variable into the database configuration model to obtain an optimal feasible solution for the online database.

[0223] In actual application, the communication unit 1202 can be implemented by a communication interface in the database tuning device; the generation unit 1201, the calculation unit 1203 and the update unit 1204 can be implemented by a processor in the database tuning device.

[0224] It should be noted that the database optimization device provided in the above embodiment only uses the division of the above-mentioned program modules as an example to illustrate database optimization. In actual applications, the above-mentioned processing can be assigned to different program modules as needed, that is, the internal structure of the device can be divided into different program modules to complete all or part of the above-mentioned processing. In addition, the database configuration device provided in the above embodiment and the database configuration method embodiment are based on the same concept. The specific implementation process is detailed in the method embodiment and will not be repeated here.

[0225] Based on the hardware implementation of the above functional modules, and in order to implement the method of the embodiment of the present application, the embodiment of the present application also provides a database tuning device (such as a master node device), such as Figure 13 The database tuning device includes: a communication interface 1301, a processor 1302 and a memory 1303, wherein:

[0226] Communication interface 1301, used for information exchange with each slave node;

[0227] Processor 1301 is configured to generate at least one offline load request; for each offline load request, perform offline training on the offline database using performance parameters of each slave node received by communication receiving 1301; determine an optimal feasible solution corresponding to the offline load request; obtain a database configuration model using each offline load request and the optimal feasible solution corresponding to each offline load request; determine an online load request for the online database, obtain an optimal feasible solution for the online database using the database configuration model and the online load request; and update the online database using the optimal feasible solution for the online database.

[0228] The operating environment and database version of the offline database on each slave node are the same.

[0229] In one embodiment, the slave node builds an offline database based on the application container engine docker.

[0230] In one embodiment, the communication interface 1301 is configured to send a feasible solution to each slave node, where the feasible solution is used to update an offline database on the corresponding slave node; receive performance parameters of the corresponding offline database under the corresponding offline load request from each slave node;

[0231] The processor 1301 is configured to: when there is at least one offline database whose corresponding performance parameter is greater than or equal to a performance threshold α, randomly select a feasible solution corresponding to one of the at least one offline database as an optimal feasible solution; and when the performance parameters corresponding to the multiple offline databases are all less than the performance threshold α, iteratively process the multiple feasible solutions sent to the multiple slave nodes to determine an optimal feasible solution.

[0232] In one embodiment:

[0233] The processor 1302 is configured to update the multiple feasible solutions using a genetic algorithm to obtain an optimized feasible solution;

[0234] The communication interface 1301 is used to send the optimized feasible solution to each slave node respectively; receive the performance parameters of the offline database under the offline load request sent by each slave node to determine the optimal feasible solution corresponding to the offline load request.

[0235] In one embodiment, the processor 1302 is configured to perform binary encoding on the multiple feasible solutions to obtain multiple binary feasible solutions; select the multiple binary feasible solutions to obtain multiple binary feasible solution combinations in groups of two; cross two binary feasible solutions in each binary feasible solution combination to obtain a cross feasible solution; and mutate at least one binary bit in each cross feasible solution to obtain the optimized feasible solution.

[0236] In one embodiment, the processor 1302 is configured to calculate a fitness probability of each binary feasible solution; select the multiple binary feasible solutions according to the fitness probability to obtain multiple binary feasible solution combinations of two.

[0237] The calculation formula of the fitness probability is:

[0238]

[0239] The fitness is proportional to the performance parameter.

[0240] In one embodiment, the two binary feasible solutions include a first binary feasible solution and a second binary feasible solution.

[0241] The processor 1302 is configured to combine the first to nth binary bits of the first binary feasible solution with the n+1th to last binary bits of the second binary feasible solution to obtain the cross feasible solution.

[0242] In one embodiment, the processor 1302 is configured to, when there is at least one updated offline database corresponding to a performance parameter greater than or equal to a performance threshold α, randomly select a feasible solution corresponding to one of the at least one updated offline databases as the optimal feasible solution; or, when the number of iterative processing times reaches a maximum number N, select a feasible solution corresponding to an updated offline database having the largest performance parameter in the Nth iteration as the optimal feasible solution.

[0243] In one embodiment, the processor 1302 is configured to determine a first characteristic parameter corresponding to each offline load request; perform multi-objective multivariate linear regression fitting on the first characteristic parameter corresponding to each offline load request and an optimal feasible solution to obtain a multi-objective multivariate linear function as the database configuration model, where the multi-objective multivariate linear function is:

[0244]

[0245] in:

[0246] Independent variable x i1 、 x i2 … x ip+1 A first characteristic parameter corresponding to an offline load request;

[0247] Dependent variables y1, y2, ... y n Multiple configuration parameters included in an optimal feasible solution for representing an offline load request;

[0248] ε 1i 、 ε 2i … ε ni is a constant,

[0249] Through the multi-objective multiple linear regression fitting, the parameters β The parameter matrix of .

[0250] In one embodiment, the processor 1302 is configured to determine a second characteristic parameter corresponding to the online load request of the online database; and input the second characteristic parameter as an independent variable into the database configuration model to obtain an optimal feasible solution for the online database.

[0251] Of course, in actual application, the various components of the database tuning device 1300 are coupled together through the bus system 1304. It can be understood that the bus system 1304 is used to realize the connection and communication between these components. In addition to the data bus, the bus system 1304 also includes a power bus, a control bus, and a status signal bus. However, for the sake of clarity, Figure 13 Various buses are labeled as bus system 1304.

[0252] The memory 1303 in the embodiment of the present application is used to store various types of data to support the operation of the database tuning device 1300. Examples of such data include: any computer program used to operate on the database tuning device 1300.

[0253] The methods disclosed in the above embodiments of the present application can be applied to the processor 1302. The processor 1302 may be an integrated circuit chip with signal processing capabilities. During implementation, each step of the above method can be completed by hardware integrated logic circuits or software instructions in the processor 1302. The processor 1302 may be a general-purpose processor, a digital signal processor (DSP), or other programmable logic device, discrete gate or transistor logic device, discrete hardware components, etc. The processor 1302 can implement or execute the various methods, steps, and logic block diagrams disclosed in the embodiments of the present application. A general-purpose processor may be a microprocessor or any conventional processor. The steps of the methods disclosed in the embodiments of the present application can be directly implemented and executed by a hardware decoding processor, or by a combination of hardware and software modules in the decoding processor. The software module may be located in a storage medium located in the memory 1303. The processor 1302 reads the information in the memory 1303 and, in conjunction with its hardware, completes the steps of the above method.

[0254] The memory 1303 of the embodiment of the present application can be a volatile memory or a non-volatile memory, or can include both volatile and non-volatile memories. Among them, the non-volatile memory can be a read-only memory (ROM), a programmable read-only memory (PROM), an erasable programmable read-only memory (EPROM), an electrically erasable programmable read-only memory (EEPROM), a magnetic random access memory (FRAM), a flash memory, a magnetic surface memory, an optical disc, or a compact disc read-only memory (CD-ROM); the magnetic surface memory can be a magnetic disk or a magnetic tape. The volatile memory can be a random access memory (RAM), which is used as an external cache. By way of example and not limitation, many forms of RAM are available, such as static random access memory (SRAM), synchronous static random access memory (SSRAM), dynamic random access memory (DRAM), synchronous dynamic random access memory (SDRAM), double data rate synchronous dynamic random access memory (DDRSDRAM), enhanced synchronous dynamic random access memory (ESDRAM), synchronous link dynamic random access memory (SLDRAM), and direct rambus random access memory (DRRAM).The memories described in the embodiments of this application are intended to include, but are not limited to, these and any other suitable types of memories.

[0255] In an exemplary embodiment, the database tuning device 1300 may be implemented by one or more application-specific integrated circuits (ASICs), DSPs, programmable logic devices (PLDs), complex programmable logic devices (CPLDs), field-programmable gate arrays (FPGAs), general-purpose processors, controllers, microcontrollers (MCUs), microprocessors, or other electronic components to perform the aforementioned method.

[0256] In order to implement the method of the embodiment of the present application, the embodiment of the present application further provides a database tuning system, which includes: a master node and multiple slave nodes; wherein,

[0257] The multiple slave nodes are respectively used to build an offline database;

[0258] The master node performs offline training on an offline database constructed on each of the multiple slave nodes for each of the multiple offline load requests to determine an optimal feasible solution corresponding to each offline load request; obtains a database configuration model using the multiple offline load requests and the multiple optimal feasible solutions; determines an online load request of an online database, obtains an optimal feasible solution for the online database using the database configuration model and the online load request; and updates the online database using the optimal feasible solution for the online database.

[0259] The operating environments and database versions of the multiple offline databases constructed on the multiple slave nodes are the same.

[0260] Here, it should be noted that the specific processing procedures of the master node and each slave node have been described in detail above and will not be repeated here.

[0261] In an exemplary embodiment, the present application also provides a storage medium, namely, a computer storage medium, specifically, a computer-readable storage medium, such as a memory storing a computer program. The computer program can be executed by the processor 1302 of the database tuning device 1300 to complete the steps of the aforementioned database tuning method. The computer-readable storage medium can be a memory such as FRAM, ROM, PROM, EPROM, EEPROM, Flash Memory, magnetic surface mount storage, optical disk, or CD-ROM.

[0262] It should be noted that: "first", "second", etc. are used to distinguish similar objects, and are not necessarily used to describe a specific order or sequence.

[0263] In addition, the technical solutions described in the embodiments of the present application can be arbitrarily combined without conflict.

[0264] The above description is merely a preferred embodiment of the present application and is not intended to limit the scope of protection of the present application.

Claims

1. A database tuning method, characterized in that: Applied to the master node, including: Generate at least one offline load request, and for one of the offline load requests, send a feasible solution to each slave node, where the feasible solution is used to update the offline database on the corresponding slave node; Receive performance parameters of the corresponding offline database under the corresponding offline load request sent by each slave node; When there is at least one offline database whose corresponding performance parameter is greater than or equal to the performance threshold α, randomly selecting a feasible solution corresponding to one of the at least one offline databases as the optimal feasible solution corresponding to the offline load request; When the performance parameters corresponding to the multiple offline databases are all less than the performance threshold α, the multiple feasible solutions are updated using a genetic algorithm to obtain an optimized feasible solution; the optimized feasible solution is sent to each slave node respectively; the performance parameters of the offline database under the offline load request sent by each slave node are received to determine the optimal feasible solution corresponding to the offline load request; A database configuration model is obtained using each offline load request and the optimal feasible solution corresponding to each offline load request; Determining an online load request of an online database, and obtaining an optimal feasible solution for the online database using the database configuration model and the online load request; Updating the online database using the optimal feasible solution of the online database; The operating environment and database version of the offline database on each slave node are the same.

2. The method according to claim 1, characterized in that The offline database is: The slave node is based on the database built on the application container engine Docker.

3. The method according to claim 1, characterized in that The method of updating multiple feasible solutions using a genetic algorithm to obtain an optimized feasible solution includes: Binary encoding the multiple feasible solutions to obtain multiple binary feasible solutions; Selecting the multiple binary feasible solutions to obtain multiple binary feasible solution combinations of two each; Cross the two binary feasible solutions in each binary feasible solution combination to obtain a cross feasible solution; At least one binary bit in each crossover feasible solution is mutated to obtain the optimized feasible solution.

4. The method according to claim 3, characterized in that The selecting the multiple binary feasible solutions to obtain multiple binary feasible solution combinations of two each includes: Calculate the fitness probability of each binary feasible solution; Selecting the multiple binary feasible solutions according to the fitness probability to obtain multiple binary feasible solution combinations in groups of two; The fitness probability is the weight of the fitness of a specific binary feasible solution relative to the sum of the fitness of all binary feasible solutions. The fitness probability is calculated as follows: The feasible solution i is a specific binary feasible solution, and the fitness is proportional to the performance parameter.

5. The method according to claim 3, characterized in that The two binary feasible solutions include a first binary feasible solution and a second binary feasible solution, and the step of crossing the two binary feasible solutions in each combination of binary feasible solutions to obtain a crossed feasible solution includes: The cross feasible solution is obtained by combining the first to n binary bits of the first binary feasible solution with the n+1th to last binary bits of the second binary feasible solution.

6. The method according to claim 1, characterized in that The receiving the performance parameters of the offline database under the offline load request sent by each slave node to determine the optimal feasible solution corresponding to the offline load request includes: When there is at least one updated offline database whose corresponding performance parameter is greater than or equal to the performance threshold α, randomly select a feasible solution corresponding to the at least one updated offline database as the optimal feasible solution; or When the number of iterative processing reaches the maximum number N, the feasible solution corresponding to the updated offline database with the largest performance parameter in the Nth iteration is taken as the optimal feasible solution.

7. The method according to claim 1, characterized in that The method of obtaining a database configuration model by using each offline load request and the optimal feasible solution corresponding to each offline load request includes: Determining a first characteristic parameter corresponding to each offline load request; A multi-objective multivariate linear regression fitting is performed on the first characteristic parameter and the optimal feasible solution corresponding to each offline load request to obtain a multi-objective multivariate linear function as the database configuration model. The multi-objective multivariate linear function is: in, Independent variable A first characteristic parameter used to characterize the offline load request; Dependent variable Multiple configuration parameters included in an optimal feasible solution for representing an offline load request; is a constant; Through the multi-objective multiple linear regression fitting, the parameters The parameter matrix of .

8. The method according to claim 7, characterized in that The determining of the online load request of the online database and obtaining the optimal feasible solution of the online database using the database configuration model and the online load request includes: Determining a second characteristic parameter corresponding to the online load request of the online database; The second characteristic parameter is input into the database configuration model as an independent variable to obtain an optimal feasible solution of the online database.

9. A database tuning device, characterized in that: provided on a master node, comprising a processor and a memory for storing a computer program capable of running on the processor, Wherein, when the processor runs the computer program, it executes the steps of the method according to any one of claims 1 to 8.

10. A database tuning system, characterized in that: include: A master node and multiple slave nodes; among them, The multiple slave nodes are respectively used to build an offline database; The master node is configured to send a feasible solution to each slave node for each of a plurality of offline load requests, wherein the feasible solution is used to update an offline database on the corresponding slave node; receive performance parameters of the corresponding offline database under the corresponding offline load request from each slave node; when the performance parameter corresponding to at least one offline database is greater than or equal to a performance threshold α, randomly select a feasible solution corresponding to one of the at least one offline databases as the optimal feasible solution corresponding to the offline load request; when the performance parameters corresponding to multiple offline databases are all less than the performance threshold α, update the multiple feasible solutions using a genetic algorithm to obtain an optimized feasible solution; send the optimized feasible solution to each slave node respectively; receive the performance parameters of the offline database under the offline load request sent by each slave node to determine the optimal feasible solution corresponding to the offline load request; obtain a database configuration model using each offline load request and the optimal feasible solution corresponding to each offline load request; determine an online load request of an online database, and obtain an optimal feasible solution for the online database using the database configuration model and the online load request; and update the online database using the optimal feasible solution of the online database; The operating environments and database versions of the multiple offline databases constructed on the multiple slave nodes are the same.

Images