Intelligent database performance evaluation method

Abstract

This invention relates to an intelligent database performance evaluation method, belonging to the field of database performance evaluation. It addresses the problems of low accuracy, long training times, and inefficient model construction in existing database performance evaluation methods. In the early stages of model training, this invention categorizes multiple variables into frequent and infrequent variables. A linear regression model is used to learn the impact of infrequent variables on database performance. The system parameters of the linear regression model are then input into a second-stage hierarchical machine learning model. Frequent variables are directly input into this model to learn their impact on database performance. The trained hierarchical machine learning model is then used as the database performance evaluation model. This invention is primarily used for evaluating database performance.

Description

Technical Field

[0001] This invention belongs to the field of database performance evaluation. Background Technology

[0002] A database system includes the database itself and the relational database management system (RDBMS). The RDBMS was introduced in the 1970s. Simulating database performance is a crucial task of a database management system. This involves complex combinations of various variables in the database configuration, with the goal of making hypothetical estimates of the database's performance under these different variables.

[0003] Most existing database performance estimation models are partial simulation models of RDBMS, utilizing the database management system to configure the database. Some researchers have proposed an optimizer cost model that uses data source and workload as variables to comprehensively measure query cost; this model can quickly simulate the query performance of a specific data source. Other researchers have proposed end-to-end learning-based estimators that use data source and workload as simulation variables. However, in general, existing work mainly focuses on partial simulation of data source and workload, failing to consider the complex changes in the database environment. Most only consider the impact of table schema, query schema, and cardinality on database performance, offering only a partial view of factors affecting database performance. They cannot comprehensively consider database performance, such as the impact of changes in index selection, tuning, hardware, operating system, and storage format, leading to low accuracy in database performance evaluation. Furthermore, existing database performance evaluation methods do not distinguish the characteristics of variables, directly establishing an end-to-end evaluation model. This results in excessively high input dimensionality, leading to a large performance evaluation model size, excessively long training time, and low evaluation efficiency. With the development of database self-tuning technology, database tasks are becoming increasingly complex, and more and more users lack database experience. There is an even more urgent need to evaluate the performance impact of database behavior on workloads. Therefore, the above problems need to be solved. Summary of the Invention

[0004] The purpose of this invention is to address the problems of low accuracy, long training time, and inefficient model building in existing database performance evaluation methods. This invention provides an intelligent database performance evaluation method.

[0005] Intelligent database performance evaluation methods, including:

[0006] S1. Collect the configuration data corresponding to each configuration category under each configuration combination and the corresponding load execution time under each configuration combination from the database; the configuration categories within each configuration combination are the same, and each configuration category corresponds to one set of configuration data;

[0007] S2. Classification: Classify the configuration categories under each configuration combination into frequent variables and infrequent variables;

[0008] S3. Take the configuration data corresponding to the infrequent variables under each configuration combination as input and the load execution time under each configuration combination as output to train the linear regression model and determine the system parameters of the linear regression model.

[0009] S4. Use the system parameters of the linear regression model as the infrequent variable layer system parameters of the machine learning hierarchical model;

[0010] S5. Using the configuration data corresponding to the frequent variables under each configuration combination as input and the load execution time under each configuration combination as output, train the machine learning hierarchical model, determine the frequent variable layer system parameters of the machine learning hierarchical model, and use the trained machine learning hierarchical model as a database performance evaluation model.

[0011] S6. Use the database performance evaluation model to evaluate the configuration data under the configuration combination to be tested, obtain the load execution time under the configuration combination to be tested, and complete the database performance evaluation.

[0012] Preferably, the configuration categories include database table mode, database query mode, computer operating system type on which the database depends, database data storage format, computer hard disk type on which the database depends, database index mode, database cardinality, and database knob type.

[0013] Preferably, the database table pattern, database query pattern, type of computer operating system on which the database depends, database data storage format, and type of computer hard drive on which the database depends are considered infrequent variables, while the database index pattern, database cardinality, and database knob type are considered frequent variables.

[0014] Preferably, the machine learning hierarchical model is implemented using a random forest model or a neural network model.

[0015] A computer-readable storage device stores a computer program that, when executed, implements a method such as an intelligent database performance evaluation method.

[0016] An intelligent performance evaluator includes a storage device, a processor, and a computer program stored in the storage device and executable on the processor, the processor executing the computer program to implement a method such as an intelligent database performance evaluation method.

[0017] The beneficial effects of this invention are as follows:

[0018] This invention categorizes multiple variables into frequent and infrequent variables during the early stages of model training. A linear regression model is used to learn the impact of infrequent variables on database performance. The system parameters of the linear regression model are then input into a second-stage hierarchical machine learning model. Frequent variables are directly input into this model to learn their impact on database performance. The trained hierarchical machine learning model is then used as a database performance evaluation model. This invention distinguishes variables with different characteristics, reducing feature dimensionality in advance and significantly improving the efficiency of building the performance evaluation model. Based on the variable classification results, a hierarchical machine learning model is established to simulate the impact of each variable at each layer on database performance. Building hierarchical models based on the characteristics of different categories of variables improves model accuracy while reducing model complexity.

[0019] The intelligent database performance evaluation method proposed in this invention fully considers the impact of various database variables on performance evaluation, improves the accuracy of database performance evaluation, establishes a machine learning hierarchical model, and uses a combination of statistical learning and machine learning to reduce the size of the machine learning hierarchical model (i.e., the database performance evaluation model), greatly improving the training efficiency of the machine learning hierarchical model, and obtaining the trained machine learning hierarchical model, i.e., the database performance evaluation model.

[0020] This invention constructs hierarchical models based on the characteristics of different categories of variables to capture patterns from the data. This results in models with fast training speed, rapid model construction, and accurate evaluation results.

[0021] This invention combines the evaluation of infrequent variables using a linear regression model based on statistical learning with the evaluation of frequent variables based on machine learning. By using a hierarchical machine learning model, it is possible to improve the evaluation accuracy while reducing the time cost of the evaluation model.

[0022] Databases can be used in various scenarios, such as book searching, connected vehicle services, and bank data analysis and query services. Attached Figure Description

[0023] Figure 1 This is a schematic diagram illustrating the principle of the intelligent database performance evaluation method described in this invention;

[0024] Figure 2 This is a diagram illustrating the category division;

[0025] Figure 3 A schematic diagram illustrating the principle of an intelligent performance evaluator in practical applications;

[0026] Figure 4 This is a screenshot of the interface that displays database performance evaluation results when the knob configuration is changed. Detailed Implementation

[0027] The technical solutions of the embodiments of the present invention will be clearly and completely described below with reference to the accompanying drawings. Obviously, the described embodiments are only some embodiments of the present invention, and not all embodiments. Based on the embodiments of the present invention, all other embodiments obtained by those skilled in the art without creative effort are within the scope of protection of the present invention.

[0028] It should be noted that, unless otherwise specified, the embodiments and features described in the present invention can be combined with each other.

[0029] See Figure 1 This first embodiment provides an intelligent database performance evaluation method that comprehensively supports variables involved in database performance evaluation, such as storage format, hardware conditions, and operating system, and also covers the characteristics and impacts of these variables. This invention designs a linear regression model based on statistical learning to fit the relationship between simple variables and the final simulated test results. For more complex variables, such as index patterns, cardinality, and knob types, a machine learning-based model is used to construct the relationship between the variables and performance latency. The evaluation method includes:

[0030] S1. Collect the configuration data corresponding to each configuration category under each configuration combination and the corresponding load execution time under each configuration combination; the configuration categories within each configuration combination are the same, and each configuration category corresponds to one set of configuration data; specifically, the configuration categories include the database data table mode, the database data query mode, the computer operating system type on which the database depends, the database data storage format, the computer hard disk type on which the database depends, the database index mode, the database cardinality, and the database knob type; the load execution time refers to the time consumed by the database to execute a set of queries for a certain task, where different users execute different queries in different business scenarios;

[0031] S2. Categorization: Classify the configuration categories under each configuration combination into frequent variables and infrequent variables; see [link to relevant documentation]. Figure 2 When applying this method, the database table schema, database query schema, type of computer operating system on which the database depends, database data storage format, and type of computer hard drive on which the database depends are treated as infrequent variables, while the database index schema, database cardinality, and database knob type are treated as frequent variables.

[0032] S3. Using the configuration data corresponding to the infrequent variables under each configuration combination as input and the load execution time under each configuration combination as output, train the linear regression model to determine the system parameters of the linear regression model; the linear regression model is a model that can be implemented using existing technology;

[0033] S4. Use the system parameters of the linear regression model as the infrequent variable layer system parameters of the machine learning hierarchical model;

[0034] S5. Using the configuration data corresponding to the frequent variables under each configuration combination as input and the load execution time under each configuration combination as output, train the machine learning hierarchical model to determine the system parameters of the frequent variable layer of the machine learning hierarchical model. The trained machine learning hierarchical model is used as a database performance evaluation model. The specific machine learning hierarchical model is implemented using a random forest model or a neural network model.

[0035] S6. Use the database performance evaluation model to evaluate the configuration data under the configuration combination to be tested, obtain the load execution time under the configuration combination to be tested, and complete the database performance evaluation.

[0036] The intelligent database performance evaluation method is mainly divided into two stages:

[0037] In the first stage, a linear regression model is constructed to learn the impact of infrequent variables on database performance, determining the system parameters of the linear regression model. These system parameters are then input into the second-stage machine learning hierarchical model. Frequent variables are used to train the machine learning hierarchical model in the second stage. Through data-driven learning, the learning results from the first stage are combined with the frequent variables. This invention classifies multiple variables in the early stages of model training, reducing the dimensionality of features and improving model construction efficiency. When infrequent variables include database table patterns, database query patterns, the type of computer operating system the database relies on, the database data storage format, and the type of computer hard drive the database relies on, the learning results from the first stage represent the impact of infrequent variables on database performance, specifically the system parameters of the linear regression model. These system parameters are used as the infrequent variable layer system parameters of the machine learning hierarchical model. When frequent variables include database index patterns, database cardinality, and database knob type, the learning results from the first stage represent the frequent variable layer system parameters of the machine learning hierarchical model, thus completing the modeling of the machine learning hierarchical model, which is also the modeling of the database performance evaluation model. In practical applications, it can be used for performance evaluation of various database tasks, such as index selection and knob tuning.

[0038] This invention considers all factors affecting database performance (various configuration categories), including hardware operating systems and control knobs, thus improving the accuracy of database performance evaluation. Compared to existing database evaluation methods, this invention can simulate the performance impact of various database behaviors.

[0039] This invention categorizes multiple variables into frequent and infrequent variables during the early stages of model training. A linear regression model is used to learn the impact of infrequent variables on database performance. The system parameters of the linear regression model are then input into a second-stage hierarchical machine learning model. Frequent variables are directly input into this model to learn their impact on database performance. The trained hierarchical machine learning model serves as the database performance evaluation model. This invention distinguishes variables with different characteristics, reducing feature dimensionality in advance and significantly improving the efficiency of building the performance evaluation model. Based on the variable classification results, a hierarchical machine learning model is established to simulate the impact of each variable at each layer on database performance. Building hierarchical models based on the features of different categories of variables improves model accuracy while reducing model complexity. When the dataset changes, part of the model needs to be retrained. Because the model is lightweight, the retraining process is relatively rapid.

[0040] Example 2: A computer-readable storage device storing a computer program, characterized in that the computer program, when executed, implements the method described in the intelligent database performance evaluation method.

[0041] Example 3: Intelligent performance evaluator, including a storage device, a processor, and a computer program stored in the storage device and executable on the processor, wherein the processor executes the computer program to implement the method as described in the intelligent database performance evaluation method.

[0042] As an example, combined Figure 3 To illustrate the use of the intelligent performance evaluator for performance evaluation, the database to be evaluated is a cloud database. First, the user, such as a database administrator, specifies the database configuration information in the user database configuration section of the intelligent performance evaluator. The intelligent performance evaluator connects to the cloud database, performs spatial screening, integrates information such as database workload and dataset, and performs intelligent variable classification. The classified variables are analyzed to train a linear regression model based on statistical learning and a hierarchical machine learning model based on machine learning. The updated configuration is then input, and the hierarchical machine learning model analyzes the possible changes in database performance before and after the two configurations to derive the database performance evaluation results.

[0043] Examples of cloud databases: Cloud vendors, such as Tencent Cloud or Alibaba Cloud, need to serve numerous customers with a vast array of business needs. Customer 1: To achieve efficient book access, Customer 1 may frequently configure the cloud database's hardware, storage structure, indexes, and other settings to improve book retrieval efficiency. Customer 2: To support connected vehicle services, Customer 2 needs to frequently improve the database's indexes, settings, and other configurations based on query and analysis needs to enhance the speed of connected vehicle data access and analysis. In general, the types of customers and businesses encountered in cloud scenarios are not limited to the above examples, but also include banks, textile companies, and many others. This invention can support diverse evaluation needs in cloud database scenarios, automatically and intelligently ensuring database performance.

[0044] Figure 4 The results show the database performance evaluation results due to the change in knob configuration, which resulted in a 1.8% increase in database throughput.

[0045] While the invention has been described herein with reference to specific embodiments, it should be understood that these embodiments are merely examples of the principles and applications of the invention. Therefore, it should be understood that many modifications can be made to the exemplary embodiments, and other arrangements can be designed without departing from the spirit and scope of the invention as defined by the appended claims. It should be understood that different dependent claims and features described herein can be combined in ways different from those described in the original claims. It is also understood that features described in conjunction with individual embodiments can be used in other described embodiments.

Claims

1. An intelligent database performance evaluation method, characterized in that, The evaluation methods include: S1. Collect the configuration data corresponding to each configuration category under each configuration combination and the corresponding load execution time under each configuration combination from the database; the configuration categories within each configuration combination are the same, and each configuration category corresponds to one set of configuration data; S2. Classification: Classify the configuration categories under each configuration combination into frequent variables and infrequent variables; S3. Take the configuration data corresponding to the infrequent variables under each configuration combination as input and the load execution time under each configuration combination as output to train the linear regression model and determine the system parameters of the linear regression model. S4. Use the system parameters of the linear regression model as the infrequent variable layer system parameters of the machine learning hierarchical model; S5. Using the configuration data corresponding to the frequent variables under each configuration combination as input and the load execution time under each configuration combination as output, train the machine learning hierarchical model, determine the frequent variable layer system parameters of the machine learning hierarchical model, and use the trained machine learning hierarchical model as a database performance evaluation model. S6. Use the database performance evaluation model to evaluate the configuration data under the configuration combination to be tested, obtain the load execution time under the configuration combination to be tested, and complete the database performance evaluation.

2. The intelligent database performance evaluation method according to claim 1, characterized in that, Configuration categories include database table mode, database query mode, computer operating system type on which the database depends, database data storage format, computer hard drive type on which the database depends, database index mode, database cardinality, and database knob type.

3. The intelligent database performance evaluation method according to claim 2, characterized in that, Database table schema, database query schema, type of computer operating system on which the database depends, database data storage format, and type of computer hard drive on which the database depends are considered infrequent variables, while database index schema, database cardinality, and database knob type are considered frequent variables.

4. The intelligent database performance evaluation method according to claim 1, characterized in that, Hierarchical machine learning models are implemented using random forest models or neural network models.

5. A computer-readable storage device storing a computer program, characterized in that, When the computer program is executed, it implements the method as described in any one of claims 1 to 4.

6. An intelligent performance evaluator, comprising a storage device, a processor, and a computer program stored in the storage device and executable on the processor, characterized in that, The processor executes the computer program to implement the method as described in any one of claims 1 to 4.

Images