Database statistical information collection adjusting and optimizing method and device, equipment and storage medium

Abstract

The invention belongs to the technical field of artificial intelligence, and discloses an adjusting and optimizing method and device for database statistical information collection, equipment and a storage medium, and the method comprises the steps: obtaining production data in a production database, and importing the production data into a test database; collecting response time for executing each database operation in the test database and configuration parameters of the statistical information corresponding to each response time, and formatting; inputting the formatted response time for executing each database operation and the configuration parameters of the statistical information corresponding to each response time into the initial neural network prediction model for training to obtain a trained neural network prediction model; and based on the trained neural network prediction model, predicting the response time of executing the database operation in real time, and searching a configuration parameter combination of the statistical information with the shortest prediction response time by adopting a machine learning optimization algorithm. The statistical information of the database can be collected more accurately.

Description

Technical Field

[0001] The present invention relates to the field of artificial intelligence technology, and in particular to a method, device, equipment and storage medium for collecting statistical information of a database. Background Art

[0002] The collection of database statistics is a key function in a database management system. Database statistics mainly refer to the metadata about tables, indexes, columns, and other database objects in the database. By collecting these statistics, we can understand the distribution of data in the database, the usage of indexes, the size of tables, and other information. These statistics are crucial for the query optimizer to determine query plans and for database performance monitoring and fault diagnosis.

[0003] However, existing database statistics collection generally has problems such as outdated statistics, inaccurate statistics for large tables and sparse data, unclear index usage, and unreasonable statistics collection strategies, which leads to long response times when performing subsequent database operations. Summary of the invention

[0004] The present invention provides a method, device, equipment and storage medium for optimizing the collection of database statistical information, so as to solve the technical problem that the collection of existing database statistical information is inaccurate, which results in a long response time when executing database operations.

[0005] In a first aspect, a method for tuning database statistical information collection is provided, comprising:

[0006] Obtaining production data from a production database, and importing the production data from the production database into a test database;

[0007] Collecting the response time of executing each database operation in the test database and the configuration parameters of the statistical information corresponding to each response time, and formatting the collected configuration parameters of the response time of executing each database operation and the statistical information corresponding to each response time;

[0008] Inputting the formatted response time of executing each database operation and the configuration parameters of the statistical information corresponding to each response time into the initial neural network prediction model for training, so as to obtain a trained neural network prediction model;

[0009] The response time of real-time database operations is predicted based on the trained neural network prediction model, and a machine learning optimization algorithm is used to search for the configuration parameter combination of statistical information with the shortest predicted response time.

[0010] In a second aspect, a tuning device for collecting database statistical information is provided, comprising:

[0011] An acquisition module is used to acquire production data from a production database and import the production data from the production database into a test database;

[0012] A collection module, used to collect the response time of executing each database operation in the test database and the configuration parameters of the statistical information corresponding to each response time, and format the collected response time of executing each database operation and the configuration parameters of the statistical information corresponding to each response time;

[0013] A training module, used for inputting the formatted response time of executing each database operation and the configuration parameters of the statistical information corresponding to each response time into the initial neural network prediction model for training, so as to obtain a trained neural network prediction model;

[0014] The search module predicts the response time of real-time database operations based on the trained neural network prediction model, and uses a machine learning optimization algorithm to search for the configuration parameter combination of statistical information with the shortest predicted response time.

[0015] In a third aspect, a computer device is provided, comprising a memory, a processor, and a computer program stored in the memory and executable on the processor, wherein the processor implements the steps of the above-mentioned method for tuning the collection of database statistical information when executing the computer program.

[0016] In a fourth aspect, a computer-readable storage medium is provided, wherein the computer-readable storage medium stores a computer program, and when the computer program is executed by a processor, the steps of the above-mentioned method for tuning the database statistical information collection are implemented.

[0017] In the scheme implemented by the above-mentioned tuning method, device, equipment and storage medium based on database statistical information collection, production data in the production database can be obtained, and the production data in the production database can be imported into the test database; the response time of executing each database operation in the test database and the configuration parameters of the statistical information corresponding to each response time are collected, and the collected response time of executing each database operation and the configuration parameters of the statistical information corresponding to each response time are formatted; the formatted response time of executing each database operation and the configuration parameters of the statistical information corresponding to each response time are input into the initial neural network prediction model for training to obtain a trained neural network prediction model; based on the trained neural network prediction model, the response time of executing database operations in real time is predicted, and a machine learning optimization algorithm is used to search for a configuration parameter combination of statistical information with the shortest predicted response time, and then the obtained statistical information configuration parameters are configured in the production database. In the present invention, for the collection of database statistical information, the response time of executing database operations can be first predicted by a neural network model, and then the configuration parameters of the statistical information with the shortest predicted response time can be searched by an optimization algorithm, so that the collection of database statistical information is more accurate, and the response time of subsequent execution of database operations is further shortened. BRIEF DESCRIPTION OF THE DRAWINGS

[0018] In order to more clearly illustrate the technical solutions of the embodiments of the present invention, the accompanying drawings required for use in the description of the embodiments of the present invention will be briefly introduced below. Obviously, the accompanying drawings in the following description are only some embodiments of the present invention. For ordinary technicians in this field, other accompanying drawings can be obtained based on these accompanying drawings without paying creative labor.

[0019] Figure 1 It is a schematic diagram of an application environment of a method for optimizing database statistical information collection in one embodiment of the present invention;

[0020] Figure 2 It is a flow chart of a method for optimizing the collection of database statistical information in one embodiment of the present invention;

[0021] Figure 3 yes Figure 2 A schematic flow chart of a specific implementation of step S30;

[0022] Figure 4 yes Figure 3 A schematic flow chart of a specific implementation of step S31;

[0023] Figure 5 yes Figure 3 A schematic flow chart of a specific implementation of step S32;

[0024] Figure 6 yes Figure 2 A schematic flow chart of a specific implementation of step S40;

[0025] Figure 7 It is a structural schematic diagram of a tuning device for collecting database statistical information in one embodiment of the present invention;

[0026] Figure 8 is a schematic diagram of a structure of a computer device in one embodiment of the present invention;

[0027] Fig. 9 It is another structural schematic diagram of a computer device in one embodiment of the present invention. DETAILED DESCRIPTION

[0028] The following will be combined with the drawings in the embodiments of the present invention to clearly and completely describe the technical solutions in the embodiments of the present invention. Obviously, the described embodiments are part of the embodiments of the present invention, not all of the embodiments. Based on the embodiments of the present invention, all other embodiments obtained by ordinary technicians in this field without creative work are within the scope of protection of the present invention.

[0029] The database statistical information collection tuning method provided by the embodiment of the present invention can be applied to Figure 1 In the application environment, Figure 1 The present invention is a schematic diagram of an application environment of a tuning method for collecting database statistical information in an embodiment of the present invention; wherein the client communicates with the server through a network. The server can receive production data in a production database through the client, and import the production data in the production database into a test database; collect the response time of executing each database operation in the test database and the configuration parameters of the statistical information corresponding to each response time, and format the collected response time of executing each database operation and the configuration parameters of the statistical information corresponding to each response time; input the formatted response time of executing each database operation and the configuration parameters of the statistical information corresponding to each response time into an initial neural network prediction model for training to obtain a trained neural network prediction model; predict the response time of executing database operations in real time based on the trained neural network prediction model, and use a machine learning optimization algorithm to search for a combination of configuration parameters of the statistical information with the shortest predicted response time, and then configure the obtained statistical information configuration parameters into the production database. In the present invention, for the collection of database statistical information, the response time of executing database operations can be first predicted by a neural network model, and then the configuration parameters of the statistical information with the shortest predicted response time can be searched by an optimization algorithm, so that the collection of database statistical information is more accurate, and the response time of subsequent execution of database operations is further shortened. The present invention is described in detail below through specific embodiments.

[0030] See also Figure 2 As shown, Figure 2 A flow chart of a tuning method based on database statistical information collection provided by an embodiment of the present invention includes the following steps:

[0031] S10: Acquire production data in a production database, and import the production data in the production database into a test database.

[0032] The database statistical information collection tuning method provided by the present invention can be applied to various database management systems, for example, relational database management systems and non-relational database management systems. Among them, relational database management systems use tables to store and manage data, and use SQL (Structured Query Language) to query and operate data. Non-relational database management systems use different data models such as key-value pairs, documents, graphs or column families to store data, and are usually used in large, distributed systems and cloud computing environments.

[0033] In an actual production environment, the production database is a key component for storing business data. If it is used directly for verification and rehearsal of implementation plans, the cost will be high. Therefore, it is generally necessary to regularly equip a test database with a configuration and scale comparable to or slightly different from the production environment. It is used for verification and rehearsal of some implementation plans. For example, data backup / recovery, data migration, software upgrades, etc. can be performed in the test database.

[0034] The present invention can first obtain production data in a production database, and after deploying and configuring a test environment as close as possible to the production environment, import the production data in the production database into the test database to verify and preview a specific implementation plan.

[0035] S20: Collecting the response time of executing each database operation in the test database and the configuration parameters of the statistical information corresponding to each response time, and formatting the collected configuration parameters of the response time of executing each database operation and the statistical information corresponding to each response time.

[0036] After the production data in the production database is imported into the test database, it is necessary to collect the response time of each database operation in the test database and the configuration parameters of the statistical information corresponding to each response time. The present invention can first write an automated script to execute different database operations to put pressure on the database, and then periodically adjust the configuration parameters of the collected statistical information to obtain the response time of the database operation and the corresponding configuration parameters of the statistical information collection, and generate a formatted data sample file. For example, please refer to Table 1-2, Table 1 is the configuration parameter characteristics of the automatically collected statistical information of the TiDB database, and Table 2 is the configuration parameter characteristics of the manually collected statistical information of the TiDB database.

[0037] Table 1

[0038] Automatic statistics collection parameter characteristics <![CDATA[x1:tidb_enable_auto_analyze]]> <![CDATA[x2:tidb_auto_analyze_ratio]]> <![CDATA[x3:tidb_auto_analyze_start_time]]> <![CDATA[x4:tidb_auto_analyze_end_time]]> <![CDATA[x5:tidb_auto_analyze_partition_batch_size]]> <![CDATA[x6:tidb_enable_auto_analyze_priority_queue]]>

[0039] Table 2

[0040] Manual statistics collection parameter characteristics <![CDATA[x7:WITH NUM BUCKETS]]> <![CDATA[x8:WITH NUM TOPN]]> <![CDATA[x9:WITH NUM CMSKETCH DEPTH]]> <![CDATA[x 10 :WITH NUM CMSKETCH WIDTH]]> <![CDATA[x 11 :WITH NUM SAMPLES]]> <![CDATA[x 12 :WITH FLOAT_NUM SAMPLERATE]]>

[0041] In some embodiments of the present invention, a configuration parameter scheme for collecting the response time of executing each database operation in a test database and the statistical information corresponding to each response time is provided, which specifically includes the following step S21:

[0042] S21: executing database operation instructions in the test database through SQL instructions, and collecting the response time of executing the database operation and the configuration parameters of the statistical information corresponding to each response time through an automated script.

[0043] In this implementation, since the parameter configuration of these statistical information can be completed through SQL statements, the response time of querying and executing database operations can also be obtained through SQL statements. The process of collecting the response time of executing database operations and the configuration parameters of the statistical information corresponding to each response time can be achieved by writing automated scripts.

[0044] S30: Inputting the formatted response time of executing each database operation and the configuration parameters of the statistical information corresponding to each response time into the initial neural network prediction model for training to obtain a trained neural network prediction model.

[0045] After collecting the response time for executing each database operation in the test database and the configuration parameters of the statistical information corresponding to each response time, it is necessary to establish a neural network model to train the response time for executing each database operation and the configuration parameters of the statistical information corresponding to each response time after the formatting processing in step S20. After obtaining the initial model, it is further updated to obtain a relationship model between the response time for executing further database operations and the statistical information parameters.

[0046] In some embodiments of the invention, Figure 3 As shown, Figure 3 yes Figure 2 A flowchart of a specific implementation method of step S30 in the embodiment of the present invention; a scheme for training a neural network model is provided, wherein in S30, the formatted response time for executing each database operation and the configuration parameters of the statistical information corresponding to each response time are input into the initial neural network prediction model for training to obtain a trained neural network prediction model, specifically comprising the following steps S31-S32:

[0047] S31: Build a fully connected neural network model based on a machine learning programming framework, and input the formatted response time of executing each database operation and the configuration parameters of the statistical information corresponding to each response time into the fully connected neural network model for training.

[0048] The problem of statistical information collection and tuning of the database to be solved by the present invention is actually the problem of the maximum value of executing database operations. If a simple linear relationship model is used between it and the configuration parameters of the input statistical information, it is not difficult to conclude that the maximum value point of the configuration parameter is the maximum value point of the performance indicator, but the combination of statistical information in the existing database is relatively complex. Therefore, it is possible to consider using a neural network model to establish a nonlinear relationship model between the statistical information in the database and the response time of executing database operations.

[0049] The present invention uses the existing machine learning programming framework to build a neural network model, and selects a fully connected neural network model to predict the response time of executing each database operation. Specifically, in step S31, the formatted response time of executing each database operation and the configuration parameters of the statistical information corresponding to each response time are input into the fully connected neural network model for training, such as Figure 4 As stated, Figure 4 yes Figure 3 A specific implementation flow diagram of step S31 in the embodiment of the present invention specifically includes the following steps S311-S313:

[0050] S311: Inputting the configuration parameters of the statistical information corresponding to each response time after the formatting process into the input layer of the fully connected neural network model for matrix conversion process, thereby obtaining the configuration parameters of the statistical information corresponding to each response time after the matrix conversion process;

[0051] S312: Inputting the configuration parameters of the statistical information corresponding to each response time after the matrix conversion processing into the hidden layer of the fully connected neural network model for feature extraction to obtain feature information of the configuration parameters of the statistical information corresponding to each response time;

[0052] S313: Inputting characteristic information of configuration parameters of statistical information corresponding to each response time into the output layer of the fully connected neural network model, predicting the response time of executing each database operation, and obtaining a prediction result of the response time of executing each database operation.

[0053] For steps S311-S313, before inputting the configuration parameters of the statistical information corresponding to each response time into the fully connected neural network model, it is necessary to first format the features of the configuration parameters of the statistical information corresponding to each response time, and then input the formatted configuration parameters of the statistical information corresponding to each response time into the input layer of the fully connected neural network model for data preprocessing, and convert the collected configuration parameters of the statistical information corresponding to each response time into the required data shape to obtain the configuration parameters of the statistical information corresponding to each response time after matrix conversion processing. Among them, the number of neurons and the activation function of the input layer of the fully connected neural network model can be adjusted according to actual needs. For example, a sigmoid activation function or a relu activation function can be used in the input layer of the fully connected neural network model.

[0054] After the above step S311 obtains the configuration parameters of the statistical information corresponding to each response time after the matrix conversion processing, the feature information of the configuration parameters of the statistical information corresponding to each response time can be input into the hidden layer of the fully connected neural network model for feature extraction, and the feature information of the configuration parameters of the statistical information corresponding to each response time is captured in the hidden layer. Among them, the hidden layer is the middle layer in the fully connected neural network, there can be multiple layers, the number of neurons in the hidden layer can be freely set, and each neuron is connected to all neurons in the previous layer and the next layer.

[0055] After the above step S312 obtains the characteristic information of the configuration parameters of the statistical information corresponding to each response time, it is necessary to predict the response time for executing each database operation. The present invention inputs the characteristic information of the configuration parameters of the statistical information corresponding to each response time into the output layer of the fully connected neural network model to predict the response time for executing each database operation. Among them, the number of neurons in the output layer of the fully connected neural network model is 1, corresponding to the response time for executing each database operation, and the activation function can be adjusted according to actual needs. For example, a linear activation function can be used in the input layer of the fully connected neural network model.

[0056] S32: Evaluate the trained fully connected neural network model, and update various hyperparameters of the fully connected neural network model according to the evaluation results.

[0057] After the formatted response time for executing each database operation and the configuration parameters of the statistical information corresponding to each response time are input into the fully connected neural network model for training in the above step S31, the trained fully connected neural network model needs to be evaluated to improve the prediction accuracy of the trained fully connected neural network model. Specifically, Figure 5 As stated, Figure 5 yes Figure 3 A specific implementation flow diagram of step S32 in the embodiment of the present invention specifically includes the following steps S321-S322:

[0058] S321: Construct a target loss function of the fully connected neural network model, and calculate the loss value of the fully connected neural network model during training according to the target loss function.

[0059] S322: Adjust the iteration cycle and batch size during training of the fully connected neural network model according to the calculated loss value of the target loss function.

[0060] For steps S321-S322, when training the fully connected neural network model, the various hyperparameters of the fully connected neural network model can be adjusted according to the loss value. Specifically, the present invention uses a mean square error loss function to calculate the loss value during the training of the fully connected neural network model. The smaller the loss value of the mean square error loss function, the better the effect of the trained fully connected neural network model.

[0061] After the loss value of the fully connected neural network model during training is obtained in step S321, the iteration cycle and batch size during the training of the fully connected neural network model can be adjusted according to the loss value of the target loss function. Among them, the iteration cycle refers to the number of times the entire training data set is completely traversed by the fully connected neural network model. In each iteration cycle, the model will be trained again and again using different samples in the data set to update the weights of the model. The purpose of training multiple iteration cycles is to allow the model to continuously learn and improve performance until it converges to the best performance or reaches the stopping condition. The batch size is the number of samples selected for one training of the fully connected neural network model, which will affect the optimization degree and training speed of the model.

[0062] S40: predicting the response time of real-time database operations based on the trained neural network prediction model, and using a machine learning optimization algorithm to search for a configuration parameter combination of statistical information with the shortest predicted response time.

[0063] The above step S30 trains a fully connected neural network model that predicts the response time of executing database operations through the configuration parameters of the collected statistical information. If you want to find the configuration parameter combination that minimizes the response time of executing database operations, you need to search in the parameter space to find the minimum value of the objective function. The present invention uses machine learning optimization algorithms such as gradient descent algorithm, Newton method, conjugate gradient algorithm, etc. to search for the configuration parameter combination of statistical information with the shortest prediction response time. Specifically, Figure 6 As stated, Figure 6 yes Figure 2 A specific implementation flow diagram of step S40 in the embodiment of the present invention specifically includes the following steps S41-S42:

[0064] S41: defining an objective function for predicting response time based on the trained neural network prediction model, and defining constraint conditions for parameters in the objective function for predicting response time;

[0065] S42: Input the defined objective function of the predicted response time and the constraint conditions of the parameters in the objective function of the predicted response time into the minimization function for searching, so as to obtain a configuration parameter combination of the statistical information with the shortest predicted response time.

[0066] For steps S41-S42, the present invention uses a minimization function to search for a configuration parameter combination of statistical information with the shortest predicted response time, first defines an objective function, uses a trained neural network to predict the response time, and returns the predicted response time as the return value of the objective function. Then, the constraint conditions of the parameters in the objective function of predicting the response time are defined, and the inequality constraint conditions, boundary constraint examples, and initial guess values ​​of the configuration parameters of the statistical information with the shortest predicted response time are defined to narrow the search range of the configuration parameters of the statistical information with the shortest predicted response time.

[0067] After defining the objective function and constraints of the predicted response time in the above step S41, the defined objective function of the predicted response time and the constraints of the parameters in the objective function of the predicted response time are input into the minimization function for search. The function accepts a set of parameters and returns a value, which is the target to be minimized, that is, the configuration parameter combination of the statistical information with the shortest predicted response time finally obtained.

[0068] The tuning method for collecting database statistical information proposed in the present invention establishes a prediction and optimization model for machine learning, and uses a non-invasive method to collect offline the response time for executing each database operation and the configuration parameters of the statistical information corresponding to each response time, and then trains the neural network model and calculates the optimal solution for the configuration parameter combination of the statistical information with the shortest predicted response time, effectively solving various problems caused by unreasonable strategies for configuring statistical information based on artificial experience.

[0069] In some embodiments of the present invention, after obtaining the configuration parameter combination of the statistical information with the shortest predicted response time in step S40, the tuning method further includes:

[0070] S50: Sending an SQL instruction through a database connection to configure a configuration parameter combination for obtaining statistical information with the shortest predicted response time into a production database.

[0071] After obtaining the configuration parameter combination of statistical information with the shortest predicted response time, the present invention configures it to the production database, and specifically can send SQL instructions through the database connection. In actual application scenarios, the above-mentioned S10-S50 database statistical information collection tuning method is not a one-time process, but a multiple or even continuous cycle iteration to continuously optimize and adapt to dynamic load scenarios. In addition, for the configuration parameters of key statistical information, manual security boundary checks can be added to intercept the configuration parameters of unreasonable statistical information that may have a greater impact on the production database.

[0072] It should be noted that the above database statistical information collection tuning method focuses on the tuning of statistical information collection strategies. In actual applications, more abundant statistical information configuration parameters, table structures, and data volumes can be introduced as independent variable features, and more dimensional performance indicators can be used as objective function dependent variables to conduct more complex and accurate model training and optimization.

[0073] It can be seen that in the above scheme, for the collection of statistical information of the database, the response time of executing database operations can be predicted by the neural network model first, and then the configuration parameters of the statistical information with the shortest predicted response time can be searched through the optimization algorithm, so that the collection of statistical information of the database is more accurate, and the response time of subsequent execution of database operations is further shortened.

[0074] It should be understood that the order of execution of the steps in the above embodiment does not necessarily mean the order of execution. The execution order of each process should be determined by its function and internal logic, and should not constitute any limitation on the implementation process of the embodiment of the present invention.

[0075] In one embodiment, a tuning device for collecting database statistical information is provided, and the tuning device for collecting database statistical information corresponds one-to-one to the tuning method for collecting database statistical information in the above embodiment. Figure 7 As shown, Figure 7 7 is a schematic diagram of a structure of a tuning device for collecting statistical information of a database in an embodiment of the present invention. The tuning device for collecting statistical information of a database includes an acquisition module 71, a collection module 72, a training module 73 and a search module 74. The functional modules are described in detail as follows:

[0076] An acquisition module 71 is used to acquire production data in a production database and import the production data in the production database into a test database;

[0077] A collection module 72, used for collecting the response time of executing each database operation in the test database and the configuration parameters of the statistical information corresponding to each response time, and formatting the collected configuration parameters of the response time of executing each database operation and the statistical information corresponding to each response time;

[0078] The training module 73 is used to input the formatted response time of executing each database operation and the configuration parameters of the statistical information corresponding to each response time into the initial neural network prediction model for training, so as to obtain a trained neural network prediction model;

[0079] The search module 74 predicts the response time of real-time database operations based on the trained neural network prediction model, and uses a machine learning optimization algorithm to search for a configuration parameter combination of statistical information with the shortest predicted response time.

[0080] In one embodiment, the collection module 72 is specifically configured to:

[0081] In the test database, database operation instructions are executed through SQL instructions, and the response time of executing the database operation and the configuration parameters of the statistical information corresponding to each response time are collected through an automated script.

[0082] In one embodiment, the training module 73 is specifically configured to:

[0083] A fully connected neural network model is constructed based on a machine learning programming framework, and the formatted response time of each database operation and the configuration parameters of the statistical information corresponding to each response time are input into the fully connected neural network model for training;

[0084] The trained fully connected neural network model is evaluated, and various hyperparameters of the fully connected neural network model are updated according to the evaluation results.

[0085] In one embodiment, the training module 73 is specifically configured to:

[0086] Inputting the configuration parameters of the statistical information corresponding to each response time after the formatting process into the input layer of the fully connected neural network model for matrix conversion process, thereby obtaining the configuration parameters of the statistical information corresponding to each response time after the matrix conversion process;

[0087] Inputting the configuration parameters of the statistical information corresponding to each response time after the matrix conversion processing into the hidden layer of the fully connected neural network model for feature extraction to obtain feature information of the configuration parameters of the statistical information corresponding to each response time;

[0088] The characteristic information of the configuration parameters of the statistical information corresponding to each response time is input into the output layer of the fully connected neural network model, and the response time of executing each database operation is predicted to obtain the prediction result of the response time of executing each database operation.

[0089] In one embodiment, the training module 73 is specifically configured to:

[0090] Constructing a target loss function of the fully connected neural network model, and calculating a loss value during training of the fully connected neural network model according to the target loss function;

[0091] The iteration cycle and batch size during training of the fully connected neural network model are adjusted according to the calculated loss value of the objective loss function.

[0092] In one embodiment, the search module 74 is specifically configured to:

[0093] Defining an objective function for predicting response time based on the trained neural network prediction model, and defining constraint conditions for parameters in the objective function for predicting response time;

[0094] The defined objective function of the predicted response time and the constraint conditions of the parameters in the objective function of the predicted response time are input into the minimization function for searching, so as to obtain the configuration parameter combination of the statistical information with the shortest predicted response time.

[0095] In one embodiment, the database statistics collection and tuning device is further used for:

[0096] Send SQL instructions through the database connection to configure the configuration parameter combination that obtains the statistical information with the shortest predicted response time into the production database.

[0097] The present invention provides a tuning device for collecting database statistical information. For the collection of database statistical information, a neural network model can be used to first predict the response time of executing database operations, and then an optimization algorithm can be used to search for configuration parameters of statistical information with the shortest predicted response time, thereby making the collection of database statistical information more accurate and further shortening the response time of subsequent execution of database operations.

[0098] For the specific limitations on the tuning device for collecting statistical information from a database, please refer to the limitations on the tuning method for collecting statistical information from a database mentioned above, which will not be repeated here. Each module in the above-mentioned tuning device for collecting statistical information from a database can be implemented in whole or in part through software, hardware, and a combination thereof. Each of the above-mentioned modules can be embedded in or independent of a processor in a computer device in the form of hardware, or can be stored in a memory in a computer device in the form of software, so that the processor can call and execute operations corresponding to each of the above modules.

[0099] In one embodiment, a computer device is provided. The computer device may be a server, and its internal structure diagram may be as follows: Figure 8 As shown, Figure 8 It is a structural diagram of a computer device in one embodiment of the present invention. The computer device includes a processor, a memory, a network interface and a database connected through a system bus. Among them, the processor of the computer device is used to provide computing and control capabilities. The memory of the computer device includes a non-volatile and / or volatile storage medium and an internal memory. The non-volatile storage medium stores an operating system, a computer program and a database. The internal memory provides an environment for the operation of the operating system and the computer program in the non-volatile storage medium. The network interface of the computer device is used to communicate with an external client through a network connection. When the computer program is executed by the processor, it realizes the functions or steps of the server side of a tuning method for collecting database statistical information.

[0100] In one embodiment, a computer device is provided. The computer device may be a client, and its internal structure diagram may be as follows: Fig. 9 As shown, Fig. 9 1 is another structural diagram of a computer device in an embodiment of the present invention. The computer device includes a processor, a memory, a network interface, a display screen, and an input device connected via a system bus. The processor of the computer device is used to provide computing and control capabilities. The memory of the computer device includes a non-volatile storage medium and an internal memory. The non-volatile storage medium stores an operating system and a computer program. The internal memory provides an environment for the operation of the operating system and the computer program in the non-volatile storage medium. The network interface of the computer device is used to communicate with an external server via a network connection. When the computer program is executed by the processor, it implements the functions or steps of the client side of a tuning method for collecting database statistical information.

[0101] In one embodiment, a computer device is provided, including a memory, a processor, and a computer program stored in the memory and executable on the processor, wherein the processor implements the following steps when executing the computer program:

[0102] Obtaining production data from a production database, and importing the production data from the production database into a test database;

[0103] Collecting the response time of executing each database operation in the test database and the configuration parameters of the statistical information corresponding to each response time, and formatting the collected configuration parameters of the response time of executing each database operation and the statistical information corresponding to each response time;

[0104] Inputting the formatted response time of executing each database operation and the configuration parameters of the statistical information corresponding to each response time into the initial neural network prediction model for training, so as to obtain a trained neural network prediction model;

[0105] The response time of real-time database operations is predicted based on the trained neural network prediction model, and a machine learning optimization algorithm is used to search for the configuration parameter combination of statistical information with the shortest predicted response time.

[0106] In one embodiment, a computer readable storage medium is provided, on which a computer program is stored, and when the computer program is executed by a processor, the following steps are implemented:

[0107] Obtaining production data from a production database, and importing the production data from the production database into a test database;

[0108] Collecting the response time of executing each database operation in the test database and the configuration parameters of the statistical information corresponding to each response time, and formatting the collected configuration parameters of the response time of executing each database operation and the statistical information corresponding to each response time;

[0109] Inputting the formatted response time of executing each database operation and the configuration parameters of the statistical information corresponding to each response time into the initial neural network prediction model for training, so as to obtain a trained neural network prediction model;

[0110] The response time of real-time database operations is predicted based on the trained neural network prediction model, and a machine learning optimization algorithm is used to search for the configuration parameter combination of statistical information with the shortest predicted response time.

[0111] It should be noted that the above functions or steps that can be implemented by the computer-readable storage medium or computer device can refer to the relevant descriptions on the server side and the client side in the aforementioned method embodiment. To avoid repetition, they will not be described one by one here.

[0112] Those skilled in the art can understand that all or part of the processes in the above-mentioned embodiment methods can be completed by instructing the relevant hardware through a computer program, and the computer program can be stored in a non-volatile computer-readable storage medium. When the computer program is executed, it can include the processes of the embodiments of the above-mentioned methods. Among them, any reference to memory, storage, database or other media used in the embodiments provided in this application can include non-volatile and / or volatile memory. Non-volatile memory can include read-only memory (ROM), programmable ROM (PROM), electrically programmable ROM (EPROM), electrically erasable programmable ROM (EEPROM) or flash memory. Volatile memory can include random access memory (RAM) or external cache memory. As an illustration and not limitation, RAM is available in many forms, such as static RAM (SRAM), dynamic RAM (DRAM), synchronous DRAM (SDRAM), double data rate SDRAM (DDRSDRAM), enhanced SDRAM (ESDRAM), synchronous link (Synchlink) DRAM (SLDRAM), memory bus (Rambus) direct RAM (RDRAM), direct memory bus dynamic RAM (DRDRAM), and memory bus dynamic RAM (RDRAM).

[0113] Those skilled in the art can clearly understand that for the convenience and simplicity of description, only the division of the above-mentioned functional units and modules is used as an example. In actual applications, the above-mentioned functions can be distributed and completed by different functional units and modules as needed, that is, the internal structure of the device can be divided into different functional units or modules to complete all or part of the functions described above.

[0114] The embodiments described above are only used to illustrate the technical solutions of the present invention, rather than to limit the same. Although the present invention has been described in detail with reference to the aforementioned embodiments, those skilled in the art should understand that the technical solutions described in the aforementioned embodiments may still be modified, or some of the technical features may be replaced by equivalents. Such modifications or replacements do not deviate the essence of the corresponding technical solutions from the spirit and scope of the technical solutions of the embodiments of the present invention, and should all be included in the protection scope of the present invention.

Claims

1. A method for optimizing the collection of database statistical information, characterized in that: include: Obtaining production data from a production database, and importing the production data from the production database into a test database; Collecting the response time of executing each database operation in the test database and the configuration parameters of the statistical information corresponding to each response time, and formatting the collected configuration parameters of the response time of executing each database operation and the statistical information corresponding to each response time; Inputting the formatted response time of executing each database operation and the configuration parameters of the statistical information corresponding to each response time into the initial neural network prediction model for training, so as to obtain a trained neural network prediction model; The response time of real-time database operations is predicted based on the trained neural network prediction model, and a machine learning optimization algorithm is used to search for the configuration parameter combination of statistical information with the shortest predicted response time.

2. The method for optimizing the collection of database statistics according to claim 1, characterized in that: The configuration parameters for collecting the response time of executing each database operation in the test database and the statistical information corresponding to each response time include: In the test database, database operation instructions are executed through SQL instructions, and the response time of executing the database operation and the configuration parameters of the statistical information corresponding to each response time are collected through an automated script.

3. The method for optimizing the collection of database statistics according to claim 2, characterized in that: The formatted response time for executing each database operation and the configuration parameters of the statistical information corresponding to each response time are input into the initial neural network prediction model for training to obtain a trained neural network prediction model, including: A fully connected neural network model is constructed based on a machine learning programming framework, and the formatted response time of each database operation and the configuration parameters of the statistical information corresponding to each response time are input into the fully connected neural network model for training; The trained fully connected neural network model is evaluated, and various hyperparameters of the fully connected neural network model are updated according to the evaluation results.

4. The method for optimizing the collection of database statistics according to claim 3, characterized in that: The formatted response time for executing each database operation and the configuration parameters of the statistical information corresponding to each response time are input into the fully connected neural network model for training, including: Inputting the configuration parameters of the statistical information corresponding to each response time after the formatting process into the input layer of the fully connected neural network model for matrix conversion process, thereby obtaining the configuration parameters of the statistical information corresponding to each response time after the matrix conversion process; Inputting the configuration parameters of the statistical information corresponding to each response time after the matrix conversion processing into the hidden layer of the fully connected neural network model for feature extraction to obtain feature information of the configuration parameters of the statistical information corresponding to each response time; The characteristic information of the configuration parameters of the statistical information corresponding to each response time is input into the output layer of the fully connected neural network model, and the response time of executing each database operation is predicted to obtain the prediction result of the response time of executing each database operation.

5. The method for optimizing the collection of database statistics according to claim 4, characterized in that: The trained fully connected neural network model is evaluated, and various hyperparameters of the fully connected neural network model are updated according to the evaluation results, including: Constructing a target loss function of the fully connected neural network model, and calculating a loss value during training of the fully connected neural network model according to the target loss function; The iteration cycle and batch size during training of the fully connected neural network model are adjusted according to the calculated loss value of the objective loss function.

6. The method for optimizing the collection of database statistics according to claim 5, characterized in that: The response time of executing database operations in real time is predicted based on the trained neural network prediction model, and a configuration parameter combination of statistical information with the shortest predicted response time is searched using a machine learning optimization algorithm, including: Defining an objective function for predicting response time based on the trained neural network prediction model, and defining constraint conditions for parameters in the objective function for predicting response time; The defined objective function of the predicted response time and the constraint conditions of the parameters in the objective function of the predicted response time are input into the minimization function for searching, so as to obtain the configuration parameter combination of the statistical information with the shortest predicted response time.

7. The method for optimizing the collection of database statistics according to claim 1, characterized in that: After obtaining the configuration parameter combination of the statistical information with the shortest predicted response time, the tuning method further includes: Send SQL instructions through the database connection to configure the configuration parameter combination that obtains the statistical information with the shortest predicted response time into the production database.

8. A tuning device for collecting database statistical information, characterized in that: include: An acquisition module is used to acquire production data from a production database and import the production data from the production database into a test database; A collection module, used to collect the response time of executing each database operation in the test database and the configuration parameters of the statistical information corresponding to each response time, and format the collected response time of executing each database operation and the configuration parameters of the statistical information corresponding to each response time; A training module, used for inputting the formatted response time of executing each database operation and the configuration parameters of the statistical information corresponding to each response time into the initial neural network prediction model for training, so as to obtain a trained neural network prediction model; The search module predicts the response time of real-time database operations based on the trained neural network prediction model, and uses a machine learning optimization algorithm to search for the configuration parameter combination of statistical information with the shortest predicted response time.

9. A computer device comprising a memory, a processor, and a computer program stored in the memory and executable on the processor, characterized in that: When the processor executes the computer program, the steps of the method for tuning database statistical information collection as described in any one of claims 1 to 7 are implemented.

10. A computer-readable storage medium storing a computer program, characterized in that: When the computer program is executed by a processor, the steps of the method for tuning database statistical information collection as claimed in any one of claims 1 to 7 are implemented.

Images