WO2024066635A1 - Method and system for intelligently optimizing database performance - Google Patents

Abstract

Disclosed in the present invention are a method and system for intelligently optimizing database performance. The method comprises: establishing a database performance optimization model according to preset database performance parameters and operating environment parameters, and initializing the database performance optimization model; monitoring, in real time, a task instruction executed by a database system, calling the database performance optimization model according to a task type to execute the task instruction, and generating a model calling result after the task execution is finished; adjusting the database performance optimization parameters according to the model calling result, and training the database performance optimization model to obtain an optimized database performance optimization model; and performing automatic optimal configuration on the database system according to the optimized database performance optimization model. The steps of establishing the database performance optimization model comprises data preparation, feature association, and operation and determination of a final optimization model. Therefore, the real-time performance and comprehensiveness of database system performance optimization are improved.

Description

A method and system for intelligent optimization of database performance Technical Field

The present application relates to the field of database technology, and more specifically, to a method and system for intelligently optimizing database performance.

Background technique

Database performance depends on many factors. From the operating system, it involves the number of threads executing tasks, system parameter configuration, etc.; from the database level, it involves table structure, storage method, query plan, SQL execution order, parameter setting, data storage location, etc.; from the hardware level, it involves disk throughput, seek time, memory space, CPU resources, etc. Therefore, database performance optimization can be adjusted and optimized from multiple levels: hardware system, operating system, and database itself.

At present, the solutions for database performance optimization cover hardware optimization, operating system optimization and database system optimization. Specific solutions include disk seek optimization, disk read and write optimization, CPU computing resource optimization, memory optimization, sub-library and sub-table optimization, distributed cache optimization, one master and multiple backups, storage system optimization, command query separation (CQS), data synchronization, query plan optimization, etc. There are single optimization solutions and multiple optimization solutions combined. However, no matter which optimization solution is used, it depends on the manual configuration or deployment of technicians. The optimization content is only adjusted for the part of database performance that can be optimized, making it impossible to achieve automatic intelligent optimization of database performance optimization, and unable to provide overall optimization for improving database performance. The so-called performance improvement is only limited to the improvement of local performance, which leads to bottlenecks in database performance optimization, difficulty in improvement, and optimization solutions that always fail to keep up with system requirements, and consumes a lot of manpower, material resources and time costs.

Based on this, it is necessary to introduce a new method and system that can automatically integrate the influencing factors of various aspects of database performance and quickly provide an overall optimization solution covering the three layers of hardware system, operating system and database system to solve the limitations, delays and dependencies of database performance optimization solutions in the existing technology, thereby quickly and comprehensively improving the overall performance of the database system and realizing the automation and intelligence of database performance optimization.

Summary of the invention

In response to the technical problems mentioned above, the present invention provides a method and system for intelligent optimization of database performance. Through the correlation analysis of the performance parameters of the database and the operating environment parameters, a database performance optimization model is constructed, and the database operation type and the call result of the database performance optimization model are calculated. When the database system is idle, the operating parameters of the database system are dynamically adjusted to complete the intelligent training and configuration optimization of the database performance optimization model, thereby realizing intelligent optimization of database performance from multiple levels of software and hardware, solving the technical problem that the current database performance optimization relies on manual configuration or deployment by technicians, improving the real-time and comprehensiveness of the database system performance optimization, as well as the performance, operation and maintenance efficiency and resource utilization of the database system, and saving a lot of manpower, material resources and time costs.

The present invention provides a method for intelligently optimizing database performance, the method comprising:

Step 1: Model initialization: According to the preset database performance parameters and operating environment parameters, a database performance optimization model is established and initialized;

Step 2: Model call, monitor the task instructions executed by the database system in real time, call the database performance optimization model to execute the task instructions according to the task type, and generate the model call result after the task execution is completed;

Step 3: Model training, adjusting the database performance optimization parameters according to the model call results, training the database performance optimization model, and obtaining an optimized database performance optimization model;

Step 4: Model configuration optimization, automatically optimizing the configuration of the database system according to the optimized database performance optimization model;

The steps of establishing a database performance optimization model are as follows:

Data preparation: classify database performance parameters and operating environment parameters and mark key parameters;

Feature association: Analyze database performance parameters and operating environment parameters, perform feature association based on the analysis results, and obtain a parameter feature association set;

Run and determine the final optimized model.

As described above, the database performance parameters and the operating environment parameters are analyzed, and the steps for feature association based on the analysis results are as follows:

1) configuring the operating environment parameters, taking the minimum value of each operating environment parameter as the initial value;

2) According to the data operation type, the initial value of the operating environment parameter is input into the database performance optimization model to obtain the initial database performance operation result;

3) increasing the initial values of the operating environment parameters one by one according to the preset incremental values to obtain incremental values of the operating environment parameters, and inputting the incremental values into the database performance optimization model to obtain incremental database performance operation results;

4) analyzing the initial database performance operation result and the incremental database performance operation result, determining the correlation characteristics between the operating environment parameters and the database performance parameters, and obtaining a parameter characteristic correlation set;

The operation types include adding, deleting, modifying and querying, and the preset increment value is a default value set by the database administrator.

The preset database performance parameters include: the number of concurrent transactions processed per unit time, request response time, single SQL instruction execution time, data compression ratio, and batch query speed;

The operating environment parameters include: number of processors, cache type, cache space size, storage space size, disk read and write speed, number of core threads, memory space size, execution optimization plan, data distribution, and table query order.

As described above, the steps of calling the database performance optimization model to execute the task instruction according to the task type are:

Parse the SQL instruction to obtain an execution plan, and determine the operation type of the execution plan;

Determine a corresponding database performance optimization model according to the operation type; if a database performance optimization model corresponding to the operation type exists, execute the execution plan according to the database performance optimization model, and mark the model call result as true; if no database performance optimization model corresponding to the type exists, execute the execution plan according to the initialized database performance optimization model, and mark the model call result as false;

The task is completed and the execution result is returned.

As described above, according to the model call result, the database performance optimization parameters are adjusted, and the specific steps of training the database performance optimization model are as follows:

1) Preprocessing the database performance optimization model according to the model call result;

2) database performance optimization model training, analyzing the operating environment parameters that affect database performance based on the preprocessing results and the parameter feature association set, and tuning the operating environment parameters to obtain database performance optimization results;

3) Analyze the model training results, compare and analyze the database performance optimization results and the model call results, and obtain the optimal parameter feature association set; and The set is connected to determine the value of the operating environment parameter;

4) Determine a database performance optimization model, calculate the values of the operating environment parameters, and obtain an optimized database performance optimization model;

As described above, according to the model call result, the database performance optimization model is preprocessed, and the preprocessing steps are:

If the model calling result is true, then comparing the database performance parameter value of the called database performance model with the database performance parameter threshold;

If the model call result is false, the corresponding database performance optimization model is initialized according to the operation type.

The database performance parameter threshold is a parameter indicator preset by a database administrator according to the preset database performance parameter, and is used to evaluate the database operation performance.

As described above, the operating environment parameters are tuned, and the steps of the tuning process are:

1) respectively calling each of the execution optimization schemes provided by the system to replace the execution optimization scheme in the database performance optimization model, executing the execution plan, and comparing the execution results to obtain a first database performance optimization result;

2) obtaining data tables involved in task execution according to the execution plan, rearranging the table query order according to the order of the data tables from small to large, executing the execution plan, and comparing the execution results to obtain the second database performance optimization result;

3) readjusting the data distribution according to the distribution area of the data in the data table in the database, executing the execution plan, comparing the execution results, and obtaining the third database performance optimization result;

4) respectively adjusting the number of processors, the cache type, the cache space size, the storage space size, the disk read and write speed, the number of core threads, and the memory space size, and obtaining corresponding execution results respectively, comparing the execution results, and obtaining a fourth database performance optimization result;

5) Combining the operating environment parameters corresponding to the first database performance optimization result, the second database performance optimization result, the third database performance optimization result, and the fourth database performance optimization result to obtain the database performance optimization result.

The execution optimization scheme includes:

Parsing the execution plan to obtain a corresponding first execution plan tree and a first execution cost;

The execution plan tree is decomposed according to the execution nodes, and the decomposed execution nodes are Mapping is performed according to the operating environment parameters to obtain an execution tree matching the operating environment parameters;

Merge and output the execution nodes of the execution tree to obtain a second execution plan tree and a second execution cost;

The first execution cost is compared with the second execution cost. If the first execution cost is greater than the second execution cost, the first execution plan tree is selected as the execution optimization solution; if the first execution cost is less than the second execution cost, the second execution plan tree is selected as the execution optimization solution.

Accordingly, the present invention also provides a system for implementing the database performance intelligent optimization method, the system comprising a model initialization module, a model calling module, a model training module and a configuration optimization module, wherein:

The model initialization module is used to establish a database performance optimization model and initialize it according to preset database performance parameters and operating environment parameters;

The model calling module is used to monitor the task instructions executed by the database system in real time, call the database performance optimization model to execute the task instructions according to the task type, and generate the model calling result after the task execution is completed;

The model training module is used to adjust the database performance optimization parameters according to the model call result, train the database performance optimization model, and obtain an optimized database performance optimization model;

The configuration optimization module is used to automatically optimize the configuration of the database system according to the optimized database performance optimization model;

The steps of establishing the database performance optimization model are:

Data preparation: classify database performance parameters and operating environment parameters and mark key parameters;

Feature association: Analyze database performance parameters and operating environment parameters, perform feature association based on the analysis results, and obtain a parameter feature association set;

Run and determine the final optimized model.

By applying the above technical scheme, the present invention realizes the construction of a database performance optimization model through the correlation analysis of the performance parameters of the database and the operating environment parameters, and the database operation type and the call result of the database performance optimization model. When the database system is idle, the operating parameters of the database system are dynamically adjusted to complete the intelligent training and configuration optimization of the database performance optimization model, thereby avoiding the database performance optimization from relying on the manual configuration or deployment of technicians, or the optimization of database performance is limited to the operating parameters of some database systems, thereby realizing the intelligent optimization of database performance from multiple levels of software and hardware, and improving the real-time and comprehensiveness of the database system performance optimization. As well as the performance, operation and maintenance efficiency, and resource utilization of the database system, it saves a lot of manpower, material resources, and time costs.

BRIEF DESCRIPTION OF THE DRAWINGS

In order to more clearly illustrate the technical solutions in the embodiments of the present application, the drawings required for use in the description of the embodiments will be briefly introduced below. Obviously, the drawings described below are only some embodiments of the present application. For those skilled in the art, other drawings can be obtained based on these drawings without creative work.

FIG1 is a schematic diagram showing a flow chart of a method for intelligently optimizing database performance according to an embodiment of the present invention;

FIG2 is a schematic diagram showing a flow chart of associating database performance parameters with operating environment parameter characteristics in a method for intelligently optimizing database performance proposed in an embodiment of the present invention;

FIG3 shows a schematic diagram of a process of training a database performance optimization model in a method for intelligently optimizing database performance according to an embodiment of the present invention;

FIG4 is a schematic diagram showing a flow chart of tuning the operating environment parameters of a method for intelligently optimizing database performance according to an embodiment of the present invention;

FIG5 shows a schematic diagram of the structure of a database performance intelligent optimization system proposed in an embodiment of the present invention.

Detailed ways

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

As shown in FIG1 , a method for intelligently optimizing database performance according to the present invention comprises the following steps:

S101, model initialization, establishing a database performance optimization model according to preset database performance parameters and operating environment parameters, and initializing it;

The steps of establishing a database performance optimization model are as follows:

Data preparation: classify database performance parameters and operating environment parameters and mark key parameters;

Feature association, analyze database performance parameters and operating environment parameters, and Perform feature association to obtain a parameter feature association set;

Run and determine the final optimization model;

S102, model calling, monitoring the task instructions executed by the database system in real time, calling the database performance optimization model to execute the task instructions according to the task type, and generating a model calling result after the task execution is completed;

The steps of calling the database performance optimization model to execute the task instruction according to the task type are as follows:

Parse the SQL instruction to obtain an execution plan, and determine the operation type of the execution plan;

Determine a corresponding database performance optimization model according to the operation type; if a database performance optimization model corresponding to the operation type exists, execute the execution plan according to the database performance optimization model, and mark the model call result as true; if no database performance optimization model corresponding to the type exists, execute the execution plan according to the initialized database performance optimization model, and mark the model call result as false;

The task is completed and the execution result is returned.

S103, model training, adjusting the database performance optimization parameters according to the model call result, training the database performance optimization model, and obtaining an optimized database performance optimization model;

S104, model configuration optimization, automatically optimizing the configuration of the database system according to the optimized database performance optimization model.

As shown in FIG2 , the database performance parameters and the operating environment parameters are analyzed, and the steps of correlating features according to the analysis results are as follows:

S201, configuring the operating environment parameters, taking the minimum value of each operating environment parameter as an initial value;

S202, inputting the initial value of the operating environment parameter into the database performance optimization model according to the data operation type to obtain an initial database performance operating result;

S203, increasing the initial value of the operating environment parameter one by one according to the preset incremental value to obtain the incremental value of the operating environment parameter, and inputting the incremental value into the database performance optimization model respectively to obtain the incremental database performance operation result;

S204, analyzing the initial database performance operation result and the incremental database performance operation result, determining the correlation characteristics between the operation environment parameters and the database performance parameters, and obtaining the parameter characteristics. The associated set;

The operation types include adding, deleting, modifying and querying, and the preset increment value is a default value set by the database administrator.

The preset database performance parameters include: the number of concurrent transactions processed per unit time, request response time, single SQL instruction execution time, data compression ratio, and batch query speed;

The operating environment parameters include: number of processors, cache type, cache space size, storage space size, disk read and write speed, number of core threads, memory space size, execution optimization plan, data distribution, and table query order.

As shown in FIG3 , according to the model call result, the database performance optimization parameters are adjusted, and the specific steps of training the database performance optimization model are as follows:

S301, preprocessing the database performance optimization model according to the model calling result;

Wherein, the pre-processing steps are:

If the model calling result is true, then comparing the database performance parameter value of the called database performance model with the database performance parameter threshold;

If the model call result is false, the corresponding database performance optimization model is initialized according to the operation type.

The database performance parameter threshold is a parameter indicator preset by a database administrator based on the preset database performance parameter, and is used to evaluate the database operation performance.

S302, database performance optimization model training, analyzing the operating environment parameters that affect database performance according to the preprocessing results and the parameter feature association set, and tuning the operating environment parameters to obtain database performance optimization results;

S303, analyzing the model training results, comparing and analyzing the database performance optimization results and the model call results, and obtaining the optimal parameter feature association set; and determining the value of the operating environment parameter according to the parameter feature association set;

S304, determining a database performance optimization model, calculating the values of the operating environment parameters, and obtaining an optimized database performance optimization model;

As shown in FIG4 , the operating environment parameters are tuned, and the steps of the tuning process are:

S401, respectively calling each of the execution optimization schemes provided by the system to replace the execution optimization schemes in the database performance optimization model, executing the execution plan, and comparing the execution results to obtain a first database performance optimization result;

S402, obtaining data tables involved in task execution according to the execution plan, rearranging the table query order according to the order of the data tables from small to large, executing the execution plan, and comparing the execution results to obtain a second database performance optimization result;

The execution optimization scheme includes:

Parsing the execution plan to obtain a corresponding first execution plan tree and a first execution cost;

Decomposing the execution plan tree according to the execution nodes, and mapping the decomposed execution nodes according to the operating environment parameters to obtain an execution tree matching the operating environment parameters;

Merge and output the execution nodes of the execution tree to obtain a second execution plan tree and a second execution cost;

Compare the first execution cost with the second execution cost, and if the first execution cost is greater than the second execution cost, select the first execution plan tree as the execution optimization solution; if the first execution cost is less than the second execution cost, select the second execution plan tree as the execution optimization solution;

S403, readjusting the data distribution according to the distribution area of the data in the data table in the database, executing the execution plan, comparing the execution results, and obtaining a third database performance optimization result;

S404, respectively adjusting the number of processors, the cache type, the cache space size, the storage space size, the disk read and write speed, the number of core threads, and the memory space size, and respectively obtaining corresponding execution results, and comparing the execution results to obtain a fourth database performance optimization result;

S405, combining the operating environment parameters corresponding to the first database performance optimization result, the second database performance optimization result, the third database performance optimization result, and the fourth database performance optimization result to obtain the database performance optimization result.

As shown in FIG5 , the present invention discloses a database performance intelligent optimization system, which includes a model initialization module, a model calling module, a model training module and a configuration optimization module, wherein:

The model initialization module is used to establish a database performance optimization model and initialize it according to preset database performance parameters and operating environment parameters;

The model calling module is used to monitor the task instructions executed by the database system in real time, call the database performance optimization model to execute the task instructions according to the task type, and generate the model calling result after the task execution is completed;

The model training module is used to adjust the database performance optimization parameters according to the model call results, train the database performance optimization model, and obtain the optimized database performance optimization model. type;

The configuration optimization module is used to automatically optimize the configuration of the database system according to the optimized database performance optimization model;

The steps of establishing the database performance optimization model are:

Data preparation: classify database performance parameters and operating environment parameters and mark key parameters;

Feature association: Analyze database performance parameters and operating environment parameters, perform feature association based on the analysis results, and obtain a parameter feature association set;

Run and determine the final optimized model.

Each embodiment in this specification is described in a related manner, and the same or similar parts between the embodiments can be referenced to each other, and each embodiment focuses on the differences from other embodiments.

The above description is only a preferred embodiment of the present invention and is not intended to limit the protection scope of the present invention. Any modification, equivalent replacement, improvement, etc. made within the spirit and principle of the present invention are included in the protection scope of the present invention.

Claims (9)

A method for intelligently optimizing database performance, the method comprising: Step 1: Model initialization: According to the preset database performance parameters and operating environment parameters, a database performance optimization model is established and initialized; Step 2: Model call, monitor the task instructions executed by the database system in real time, call the database performance optimization model to execute the task instructions according to the task type, and generate the model call result after the task execution is completed; Step 3: Model training, adjusting the database performance optimization parameters according to the model call results, training the database performance optimization model, and obtaining an optimized database performance optimization model; Step 4: Model configuration optimization, automatically optimizing the configuration of the database system according to the optimized database performance optimization model; It is characterized in that the steps of establishing the database performance optimization model are: Data preparation: classify database performance parameters and operating environment parameters and mark key parameters; Feature association: Analyze database performance parameters and operating environment parameters, perform feature association based on the analysis results, and obtain a parameter feature association set; Run and determine the final optimized model. The method according to claim 1 is characterized in that the step of analyzing the database performance parameters and the operating environment parameters and correlating the characteristics according to the analysis results comprises: 1) configuring the operating environment parameters, taking the minimum value of each operating environment parameter as the initial value; 2) According to the data operation type, the initial value of the operating environment parameter is input into the database performance optimization model to obtain the initial database performance operation result; 3) increasing the initial values of the operating environment parameters one by one according to the preset incremental values to obtain incremental values of the operating environment parameters, and inputting the incremental values into the database performance optimization model to obtain incremental database performance operation results; 4) analyzing the initial database performance operation result and the incremental database performance operation result, determining the correlation characteristics between the operating environment parameters and the database performance parameters, and obtaining a parameter characteristic correlation set; The operation types include adding, deleting, modifying and querying, and the preset increment value is a default value set by the database administrator. The method according to claim 1, characterized in that the preset database performance parameters include: the number of concurrent transactions processed per unit time, request response time, single SQL instruction execution time, data compression ratio, batch query speed; The operating environment parameters include: number of processors, cache type, cache space size, storage space size, disk read and write speed, number of core threads, memory space size, execution optimization plan, data distribution, and table query order. The method according to claim 1, characterized in that the step of calling the database performance optimization model to execute the task instruction according to the task type is: Parse the SQL instruction to obtain an execution plan, and determine the operation type of the execution plan; Determine a corresponding database performance optimization model according to the operation type; if a database performance optimization model corresponding to the operation type exists, execute the execution plan according to the database performance optimization model, and mark the model call result as true; if no database performance optimization model corresponding to the type exists, execute the execution plan according to the initialized database performance optimization model, and mark the model call result as false; The task is completed and the execution result is returned. The method according to claim 1 is characterized in that the database performance optimization parameters are adjusted according to the model call results, and the specific steps of training the database performance optimization model are: 1) Preprocessing the database performance optimization model according to the model call result; 2) database performance optimization model training, analyzing the operating environment parameters that affect database performance based on the preprocessing results and the parameter feature association set, and tuning the operating environment parameters to obtain database performance optimization results; 3) analyzing the model training results, comparing and analyzing the database performance optimization results and the model call results to obtain the optimal parameter feature association set; and determining the value of the operating environment parameter according to the parameter feature association set; 4) Determine a database performance optimization model, calculate the values of the operating environment parameters, and obtain an optimized database performance optimization model; The method according to claim 5, characterized in that, according to the model calling result, The database performance optimization model is preprocessed, and the preprocessing steps are: If the model calling result is true, then comparing the database performance parameter value of the called database performance model with the database performance parameter threshold; If the model call result is false, the corresponding database performance optimization model is initialized according to the operation type. The database performance parameter threshold is a parameter indicator preset by a database administrator according to the preset database performance parameter, and is used to evaluate the database operation performance. The method according to claim 5, characterized in that the operating environment parameters are tuned, and the tuning steps are: 1) respectively calling each of the execution optimization schemes provided by the system to replace the execution optimization scheme in the database performance optimization model, executing the execution plan, and comparing the execution results to obtain a first database performance optimization result; 2) obtaining data tables involved in task execution according to the execution plan, rearranging the table query order according to the order of the data tables from small to large, executing the execution plan, and comparing the execution results to obtain the second database performance optimization result; 3) readjusting the data distribution according to the distribution area of the data in the data table in the database, executing the execution plan, comparing the execution results, and obtaining the third database performance optimization result; 4) respectively adjusting the number of processors, the cache type, the cache space size, the storage space size, the disk read and write speed, the number of core threads, and the memory space size, and obtaining corresponding execution results respectively, comparing the execution results, and obtaining a fourth database performance optimization result; 5) Combining the operating environment parameters corresponding to the first database performance optimization result, the second database performance optimization result, the third database performance optimization result, and the fourth database performance optimization result to obtain the database performance optimization result. The method according to claim 7, wherein the execution optimization scheme comprises: Parsing the execution plan to obtain a corresponding first execution plan tree and a first execution cost; Decomposing the execution plan tree according to the execution nodes, and mapping the decomposed execution nodes according to the operating environment parameters to obtain an execution tree matching the operating environment parameters; Merge and output the execution nodes of the execution tree to obtain a second execution plan tree and a second execution cost; The first execution cost is compared with the second execution cost. If the first execution cost is greater than the second execution cost, the first execution plan tree is selected as the execution optimization solution; if the first execution cost is less than the second execution cost, the second execution plan tree is selected as the execution optimization solution. A system for implementing the database performance intelligent optimization method according to claim 1, the system comprising a model initialization module, a model calling module, a model training module and a configuration optimization module, wherein: The model initialization module is used to establish a database performance optimization model and initialize it according to preset database performance parameters and operating environment parameters; The model calling module is used to monitor the task instructions executed by the database system in real time, call the database performance optimization model to execute the task instructions according to the task type, and generate the model calling result after the task execution is completed; The model training module is used to adjust the database performance optimization parameters according to the model call result, train the database performance optimization model, and obtain an optimized database performance optimization model; The configuration optimization module is used to automatically optimize the configuration of the database system according to the optimized database performance optimization model; It is characterized in that the steps of establishing the database performance optimization model are: Data preparation: classify database performance parameters and operating environment parameters and mark key parameters; Feature association: Analyze database performance parameters and operating environment parameters, perform feature association based on the analysis results, and obtain a parameter feature association set; Run and determine the final optimized model.