CN115640278B - Method and system for intelligently optimizing database performance - Google Patents
Method and system for intelligently optimizing database performance Download PDFInfo
- Publication number
- CN115640278B CN115640278B CN202211219319.4A CN202211219319A CN115640278B CN 115640278 B CN115640278 B CN 115640278B CN 202211219319 A CN202211219319 A CN 202211219319A CN 115640278 B CN115640278 B CN 115640278B
- Authority
- CN
- China
- Prior art keywords
- database performance
- model
- execution
- database
- performance optimization
- Prior art date
- Legal status (The legal status is an assumption and is not a legal conclusion. Google has not performed a legal analysis and makes no representation as to the accuracy of the status listed.)
- Active
Links
- 238000000034 method Methods 0.000 title claims abstract description 28
- 238000005457 optimization Methods 0.000 claims abstract description 219
- 238000002360 preparation method Methods 0.000 claims abstract description 7
- 238000012544 monitoring process Methods 0.000 claims abstract description 6
- 238000012549 training Methods 0.000 claims description 26
- 238000009826 distribution Methods 0.000 claims description 9
- 238000007781 pre-processing Methods 0.000 claims description 9
- 238000003860 storage Methods 0.000 claims description 8
- 238000012545 processing Methods 0.000 claims description 4
- 238000013144 data compression Methods 0.000 claims description 3
- 238000007726 management method Methods 0.000 claims description 3
- 238000013507 mapping Methods 0.000 claims description 3
- 230000004044 response Effects 0.000 claims description 3
- 238000010219 correlation analysis Methods 0.000 description 2
- 238000010586 diagram Methods 0.000 description 2
- 206010063385 Intellectualisation Diseases 0.000 description 1
- 238000013500 data storage Methods 0.000 description 1
- 230000001934 delay Effects 0.000 description 1
- 238000012423 maintenance Methods 0.000 description 1
- 238000012986 modification Methods 0.000 description 1
- 230000004048 modification Effects 0.000 description 1
- 238000000926 separation method Methods 0.000 description 1
Classifications
-
- G—PHYSICS
- G06—COMPUTING; CALCULATING OR COUNTING
- G06F—ELECTRIC DIGITAL DATA PROCESSING
- G06F16/00—Information retrieval; Database structures therefor; File system structures therefor
- G06F16/20—Information retrieval; Database structures therefor; File system structures therefor of structured data, e.g. relational data
- G06F16/21—Design, administration or maintenance of databases
-
- Y—GENERAL TAGGING OF NEW TECHNOLOGICAL DEVELOPMENTS; GENERAL TAGGING OF CROSS-SECTIONAL TECHNOLOGIES SPANNING OVER SEVERAL SECTIONS OF THE IPC; TECHNICAL SUBJECTS COVERED BY FORMER USPC CROSS-REFERENCE ART COLLECTIONS [XRACs] AND DIGESTS
- Y02—TECHNOLOGIES OR APPLICATIONS FOR MITIGATION OR ADAPTATION AGAINST CLIMATE CHANGE
- Y02D—CLIMATE CHANGE MITIGATION TECHNOLOGIES IN INFORMATION AND COMMUNICATION TECHNOLOGIES [ICT], I.E. INFORMATION AND COMMUNICATION TECHNOLOGIES AIMING AT THE REDUCTION OF THEIR OWN ENERGY USE
- Y02D10/00—Energy efficient computing, e.g. low power processors, power management or thermal management
Landscapes
- Engineering & Computer Science (AREA)
- Databases & Information Systems (AREA)
- Theoretical Computer Science (AREA)
- Data Mining & Analysis (AREA)
- Physics & Mathematics (AREA)
- General Engineering & Computer Science (AREA)
- General Physics & Mathematics (AREA)
- Information Retrieval, Db Structures And Fs Structures Therefor (AREA)
Abstract
The invention discloses a method and a system for intelligently optimizing database performance, wherein the method comprises the following steps: according to preset database performance parameters and running environment parameters, a database performance optimization model is established, and initialized; monitoring task instructions executed by a database system in real time, calling the database performance optimization model to execute the task instructions according to task types, and generating a model calling result after the task execution is finished; according to the model calling result, the database performance optimization parameters are adjusted, and the database performance optimization model is trained to obtain an optimized database performance optimization model; according to the optimized database performance optimization model, automatically optimizing and configuring the database system; the step of establishing the database performance optimization model comprises data preparation, feature association, operation and final optimization model determination, so that the real-time performance and the comprehensiveness of database system performance optimization are improved.
Description
Technical Field
The application relates to the technical field of databases, in particular to a method and a system for intelligently optimizing database performance.
Background
Database performance depends on many factors, from the operating system, the number of threads involved in executing tasks, system parameter configuration, etc.; from the database level, the method relates to a table structure, a storage mode, a query scheme, SQL execution sequences, parameter setting, data storage positions and the like; from the hardware level, disk throughput, seek time, memory space, CPU resources, etc. are involved. Thus, optimization of database performance may be tuned and optimized from multiple levels of hardware systems, operating systems, and databases themselves.
At present, the scheme of optimizing the database performance covers hardware level optimization, operating system optimization and database system optimization, and the specific scheme comprises disk seek optimization, disk read-write optimization, CPU computing resource optimization, memory optimization, database splitting and table splitting, distributed cache optimization, one main multi-standby, storage system optimization, command Query Separation (CQS), data synchronization, query plan optimization and the like, wherein the single optimization scheme is applied, and the combination of multiple optimization schemes is also applied. However, any optimization scheme depends on manual configuration or deployment of technicians, and the optimization content is only adjusted for a part of optimizable database performance, so that the optimization of the database performance cannot realize automatic intelligent optimization, and cannot provide overall optimization for the improvement of the database performance, so-called performance improvement is limited to the improvement of local performance, so that the improvement of the database performance encounters a bottleneck, the improvement is difficult, the optimization scheme is not always in line with the system requirement, and a large amount of manpower, material resources and time cost are consumed.
Based on the above, a new method and system are necessary to be introduced, which can automatically integrate influencing factors of various database performances, and rapidly provide an overall optimization scheme covering three layers of a hardware system, an operating system and a database system, so as to solve the limitations, delays and dependencies of the database performance optimization scheme in the prior art, further rapidly and comprehensively improve the overall performance of the database system, and realize the automation and the intellectualization of the database performance optimization.
Disclosure of Invention
Aiming at the technical problems, the invention provides a method and a system for intelligently optimizing the database performance, which are used for constructing a database performance optimization model through the correlation analysis of the performance parameters and the operation environment parameters of a database, constructing the database operation type and the calling result of the database performance optimization model, and completing the intelligent training and configuration optimization of the database performance optimization model by dynamically adjusting the operation parameters of the database system when the database system is idle, so as to realize the intelligent optimization of the database performance from a plurality of layers of software and hardware, solve the technical problem that the current database performance optimization depends on manual configuration or deployment of technicians, improve the real-time performance and the comprehensiveness of the database system performance optimization, and improve the operation performance, the operation efficiency and the resource utilization rate of the database system, and save a great deal of manpower, material resources and time cost.
The invention provides an intelligent optimization method for database performance, which comprises the following steps:
step 1: initializing a model, namely establishing a database performance optimization model according to preset database performance parameters and running environment parameters, and initializing the database performance optimization model;
step 2: model calling, namely monitoring task instructions executed by a database system in real time, calling the database performance optimization model to execute the task instructions according to task types, and generating a model calling result after task execution is finished;
step 3: model training, namely 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;
step 4: model configuration optimization, namely automatically optimizing and configuring the database system according to the optimized database performance optimization model;
the database performance optimization model building step comprises the following steps:
data preparation, namely classifying database performance parameters and running environment parameters and marking key parameters;
characteristic association, namely analyzing the database performance parameters and the running environment parameters, and carrying out characteristic association according to analysis results to obtain a parameter characteristic association set;
the final optimization model is run and determined.
As described above, the steps of analyzing the database performance parameters and the operation environment parameters and performing feature association according to the analysis result are as follows:
1) Configuring the operation environment parameters, and taking the minimum value of each operation environment parameter as an initial value;
2) Inputting the initial value of the running environment parameter into the database performance optimization model according to the data operation type to obtain an initial database performance running result;
3) Increasing initial values of the operation environment parameters one by one according to preset increment values to obtain increment values of the operation environment parameters, and respectively inputting the increment 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, and determining the association characteristics of the operation environment parameters and the database performance parameters to obtain a parameter characteristic association set;
the operation types comprise adding, deleting, modifying and inquiring, and the default value is set for database management personnel by the preset increment value.
The preset database performance parameters comprise: the number of concurrent transactions processed in unit time, the request response time, the execution time of a single SQL instruction, the data compression ratio and the batch query speed;
the operating environment parameters include: processor number, cache type, cache space size, storage space size, disk read-write speed, core thread number, memory space size, execution optimization scheme, data distribution, and table query sequence.
As described above, the steps of calling the database performance optimization model to execute the task instruction according to the task type are:
analyzing the SQL instruction to obtain an execution plan, and judging and determining the operation type of the execution plan;
determining a corresponding database performance optimization model according to the operation type, executing the execution plan according to the database performance optimization model if the database performance optimization model corresponding to the operation type exists, and marking the model calling result as true; if the database performance optimization model corresponding to the type does not exist, executing the execution plan according to the initialized database performance optimization model, and marking the model calling result as false;
and completing task execution and returning an execution result.
As described above, according to the model calling result, the database performance optimization parameters are adjusted, and the specific steps for training the database performance optimization model are as follows:
1) Preprocessing the database performance optimization model according to the model calling result;
2) Training a database performance optimization model, analyzing the running environment parameters affecting the database performance according to the preprocessing result and the parameter characteristic association set, and performing optimization processing on the running environment parameters to obtain a database performance optimization result;
3) Analyzing a model training result, and comparing and analyzing the database performance optimization result and the model calling result to obtain the optimal parameter feature association set; determining the value of the running environment parameter according to the parameter characteristic association set;
4) Determining a database performance optimization model, and calculating the values of the running environment parameters to obtain an optimized database performance optimization model;
as described above, according to the model calling result, preprocessing is performed on the database performance optimization model, where the preprocessing steps are as follows:
if the model calling result is true, comparing the called database performance parameter value of the database performance model with a database performance parameter threshold;
and if the model calling result is false, initializing a corresponding database performance optimization model according to the operation type.
The database performance parameter threshold is a parameter index preset by a database manager according to the preset database performance parameter and is used for evaluating the database operation performance.
As described above, the tuning process is performed on the operating environment parameters, and the tuning process includes the following steps:
1) Respectively calling each execution optimization scheme 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 a data table involved in task execution according to the execution plan, rearranging the table inquiry sequence according to the sequence from small to large of the data table, executing the execution plan, and comparing the execution results to obtain a 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, and comparing the execution results to obtain a 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-write speed, the core thread number and the memory space size, respectively obtaining corresponding execution results, and comparing the execution results to obtain a fourth database performance optimization result;
5) And combining the operation 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.
Wherein, the execution optimization scheme comprises:
analyzing the execution plan to obtain a corresponding first execution plan tree and a first execution cost;
decomposing the execution plan tree according to an execution node, and mapping the decomposed execution node according to the operation environment parameters to obtain an execution tree matched with the operation environment parameters;
merging and outputting the execution nodes of the execution tree to obtain a second execution plan tree and a second execution cost;
comparing the first execution cost with the second execution cost, and if the first execution cost is greater than the second execution cost, selecting a first execution plan tree as the execution optimization scheme; and if the first execution cost is smaller than the second execution cost, selecting the second execution plan tree as the execution optimization scheme.
Correspondingly, the invention also provides a system for implementing the database performance intelligent optimization method, which comprises a model initialization module, a model calling module, a model training module and a configuration optimization module, wherein,
the model initialization module is used for establishing a database performance optimization model according to preset database performance parameters and running environment parameters and initializing the database performance optimization model;
the model calling module is used for monitoring task instructions executed by the database system in real time, calling the database performance optimization model to execute the task instructions according to task types, and generating a model calling result after the task execution is finished;
the model training module is used for adjusting the database performance optimization parameters according to the model calling result, training the database performance optimization model and obtaining an optimized database performance optimization model;
the configuration optimization module is used for automatically optimizing and configuring the database system according to the optimized database performance optimization model;
the step of establishing the database performance optimization model comprises the following steps:
data preparation, namely classifying database performance parameters and running environment parameters and marking key parameters;
characteristic association, namely analyzing the database performance parameters and the running environment parameters, and carrying out characteristic association according to analysis results to obtain a parameter characteristic association set;
the final optimization model is run and determined.
By applying the technical scheme, the invention realizes the correlation analysis of the performance parameters and the running environment parameters of the database, constructs the database performance optimization model, and the database operation type and the calling result of the database performance optimization model, and completes the intelligent training and configuration optimization of the database performance optimization model by dynamically adjusting the running parameters of the database system when the database system is idle, thereby avoiding the dependence of the database performance optimization on manual configuration or deployment of technicians or the limitation of the optimization of the database performance to the running parameters of partial database system, further realizing the intelligent optimization of the database performance from a plurality of layers of software and hardware, improving the real-time performance and comprehensiveness of the database system performance optimization, the operation and maintenance efficiency and the resource utilization rate of the database system, and saving a great deal of manpower, material resources and time cost.
Drawings
In order to more clearly illustrate the technical solutions of the embodiments of the present application, the drawings that are needed in the description of the embodiments will be briefly introduced below, it being obvious that the drawings in the following description are only some embodiments of the present application, and that other drawings may be obtained according to these drawings without inventive effort for a person skilled in the art.
FIG. 1 shows a schematic flow chart of a database performance intelligent optimization method according to an embodiment of the present invention;
FIG. 2 is a schematic flow chart showing the correlation between the database performance parameters and the running environment parameter characteristics of a database performance intelligent optimization method according to the embodiment of the invention;
FIG. 3 is a schematic diagram showing a training process of a database performance optimization model of the database performance intelligent optimization method according to the embodiment of the invention;
FIG. 4 is a schematic flow chart of a database performance intelligent optimization method for optimizing operation environment parameters according to an embodiment of the present invention;
fig. 5 shows a schematic structural diagram of an intelligent database performance optimization system according to an embodiment of the present invention.
Detailed Description
The following description of the embodiments of the present application will be made clearly and fully with reference to the accompanying drawings, in which it is evident that the embodiments described are only some, but not all, of the embodiments of the present application. All other embodiments, which can be made by one of ordinary skill in the art without undue burden from the present disclosure, are within the scope of the present disclosure.
As shown in fig. 1, the method for intelligently optimizing database performance of the present invention comprises the following steps:
s101, initializing a model, namely establishing a database performance optimization model according to preset database performance parameters and running environment parameters, and initializing the database performance optimization model;
the database performance optimization model building step comprises the following steps:
data preparation, namely classifying database performance parameters and running environment parameters and marking key parameters;
characteristic association, namely analyzing the database performance parameters and the running environment parameters, and carrying out characteristic association according to analysis results to obtain a parameter characteristic association set;
running and determining a final optimization model;
s102, model calling is carried out, task instructions executed by a database system are monitored in real time, the database performance optimization model is called according to task types to execute the task instructions, and a model calling result is generated after task execution is finished;
the step of calling the database performance optimization model to execute the task instruction according to the task type is as follows:
analyzing the SQL instruction to obtain an execution plan, and judging and determining the operation type of the execution plan;
determining a corresponding database performance optimization model according to the operation type, executing the execution plan according to the database performance optimization model if the database performance optimization model corresponding to the operation type exists, and marking the model calling result as true; if the database performance optimization model corresponding to the type does not exist, executing the execution plan according to the initialized database performance optimization model, and marking the model calling result as false;
and completing task execution and returning an execution result.
S103, training a model, namely 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;
s104, optimizing the model configuration, and automatically optimizing and configuring the database system according to the optimized database performance optimization model.
As shown in fig. 2, the steps of analyzing the database performance parameters and the operation environment parameters and performing feature association according to the analysis result are as follows:
s201, configuring the operation environment parameters, and taking the minimum value of each operation environment parameter as an initial value;
s202, inputting initial values of the running environment parameters into the database performance optimization model according to the data operation type to obtain initial database performance running results;
s203, increasing initial values of the operation environment parameters one by one according to preset increment values to obtain increment values of the operation environment parameters, and respectively inputting the increment values into the database performance optimization model to obtain incremental database performance operation results;
s204, analyzing the initial database performance operation result and the incremental database performance operation result, and determining the association features of the operation environment parameters and the database performance parameters to obtain a parameter feature association set;
the operation types comprise adding, deleting, modifying and inquiring, and the default value is set for database management personnel by the preset increment value.
The preset database performance parameters comprise: the number of concurrent transactions processed in unit time, the request response time, the execution time of a single SQL instruction, the data compression ratio and the batch query speed;
the operating environment parameters include: processor number, cache type, cache space size, storage space size, disk read-write speed, core thread number, memory space size, execution optimization scheme, data distribution, and table query sequence.
As shown in fig. 3, according to the model calling result, the database performance optimization parameters are adjusted, and the specific steps for training the database performance optimization model are as follows:
s301, preprocessing the database performance optimization model according to the model calling result;
wherein the pretreatment steps are as follows:
if the model calling result is true, comparing the called database performance parameter value of the database performance model with a database performance parameter threshold;
and if the model calling result is false, initializing a corresponding database performance optimization model according to the operation type.
The database performance parameter threshold is a parameter index preset by a database manager according to the preset database performance parameter and is used for evaluating the database operation performance.
S302, training a database performance optimization model, analyzing the running environment parameters affecting the database performance according to the preprocessing result and the parameter characteristic association set, and performing optimization processing on the running environment parameters to obtain a database performance optimization result;
s303, analyzing a model training result, and comparing and analyzing the database performance optimization result and the model calling result to obtain the optimal parameter feature association set; determining the value of the running environment parameter according to the parameter characteristic association set;
s304, determining a database performance optimization model, and calculating the values of the running environment parameters to obtain an optimized database performance optimization model;
as shown in fig. 4, the tuning process is performed on the running environment parameters, and the tuning process includes the following steps:
s401, respectively calling each execution optimization scheme provided by a 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;
s402, obtaining a data table involved in task execution according to the execution plan, rearranging the table inquiry sequence according to the sequence from small to large of the data table, executing the execution plan, and comparing the execution results to obtain a second database performance optimization result;
wherein, the execution optimization scheme comprises:
analyzing the execution plan to obtain a corresponding first execution plan tree and a first execution cost;
decomposing the execution plan tree according to an execution node, and mapping the decomposed execution node according to the operation environment parameters to obtain an execution tree matched with the operation environment parameters;
merging and outputting the execution nodes of the execution tree to obtain a second execution plan tree and a second execution cost;
comparing the first execution cost with the second execution cost, and if the first execution cost is greater than the second execution cost, selecting a first execution plan tree as the execution optimization scheme; if the first execution cost is smaller than the second execution cost, selecting a second execution plan tree as the execution optimization scheme;
s403, readjusting the data distribution according to the distribution area of the data in the data table in the database, executing the execution plan, and comparing the execution results to obtain 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-write speed, the core thread number and the memory space size, respectively obtaining corresponding execution results, and comparing the execution results to obtain a fourth database performance optimization result;
s405, combining the operation 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 fig. 5, the invention discloses an intelligent database performance optimization system, which comprises a model initialization module, a model calling module, a model training module and a configuration optimization module, wherein:
the model initialization module is used for establishing a database performance optimization model according to preset database performance parameters and running environment parameters and initializing the database performance optimization model;
the model calling module is used for monitoring task instructions executed by the database system in real time, calling the database performance optimization model to execute the task instructions according to task types, and generating a model calling result after the task execution is finished;
the model training module is used for adjusting the database performance optimization parameters according to the model calling result, training the database performance optimization model and obtaining an optimized database performance optimization model;
the configuration optimization module is used for automatically optimizing and configuring the database system according to the optimized database performance optimization model;
the step of establishing the database performance optimization model comprises the following steps:
data preparation, namely classifying database performance parameters and running environment parameters and marking key parameters;
characteristic association, namely analyzing the database performance parameters and the running environment parameters, and carrying out characteristic association according to analysis results to obtain a parameter characteristic association set;
the final optimization model is run and determined.
In this specification, each embodiment is described in a related manner, and identical and similar parts of each embodiment are all referred to each other, and each embodiment mainly describes differences from other embodiments.
The foregoing description is only of the preferred embodiments of the present invention and is not intended to limit the 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 (5)
1. A method for intelligently optimizing database performance, the method comprising:
step 1: initializing a model, namely establishing a database performance optimization model according to preset database performance parameters and running environment parameters, and initializing the database performance optimization model;
step 2: model calling, namely monitoring task instructions executed by a database system in real time, calling the database performance optimization model to execute the task instructions according to task types, and generating a model calling result after task execution is finished;
step 3: model training, namely adjusting the database performance optimization parameters according to the model calling result, training the database performance optimization model to obtain an optimized database performance optimization model, wherein the method comprises the following specific steps of:
1) Preprocessing the database performance optimization model according to the model calling result;
2) Training a database performance optimization model, analyzing the running environment parameters affecting the database performance according to the preprocessing result and the parameter characteristic association set, and performing optimization processing on the running environment parameters to obtain a database performance optimization result;
the tuning processing comprises the following steps:
i) Respectively calling each execution optimization scheme provided by the system to replace the execution scheme in the database performance optimization model, executing the execution plan, and comparing the execution results to obtain a first database performance optimization result;
wherein, the execution optimization scheme comprises:
analyzing the execution plan to obtain a corresponding first execution plan tree and a first execution cost;
decomposing the execution plan tree according to an execution node, and mapping the decomposed execution node according to the operation environment parameters to obtain an execution tree matched with the operation environment parameters;
merging and outputting the execution nodes of the execution tree to obtain a second execution plan tree and a second execution cost;
comparing the first execution cost with the second execution cost, and if the first execution cost is greater than the second execution cost, selecting a first execution plan tree as the execution optimization scheme; if the first execution cost is smaller than the second execution cost, selecting a second execution plan tree as the execution optimization scheme;
ii) obtaining a data table involved in task execution according to the execution plan, rearranging a table inquiry sequence according to the sequence from small to large of the data table, executing the execution plan, and comparing the execution results to obtain a second database performance optimization result;
iii) According to the distribution area of the data in the data table in the database, readjusting the data distribution, executing the execution plan, and comparing the execution results to obtain a third database performance optimization result;
iv) respectively adjusting the number of processors, the cache type, the cache space size, the storage space size, the disk read-write speed, the core thread number and the memory space size, respectively obtaining corresponding execution results, and comparing the execution results to obtain a fourth database performance optimization result;
v) combining the operation 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 a database performance optimization result;
3) Analyzing a model training result, and comparing and analyzing the database performance optimization result and the model calling result to obtain the optimal parameter feature association set; determining the value of the running environment parameter according to the parameter characteristic association set;
4) Determining a database performance optimization model, and calculating the values of the running environment parameters to obtain an optimized database performance optimization model;
step 4: model configuration optimization, namely automatically optimizing and configuring the database system according to the optimized database performance optimization model;
the method is characterized in that the step of establishing the database performance optimization model comprises the following steps:
data preparation, namely classifying database performance parameters and running environment parameters and marking key parameters;
characteristic association, namely analyzing the database performance parameters and the running environment parameters, and carrying out characteristic association according to analysis results to obtain a parameter characteristic association set;
running and determining a final optimization model;
the step of analyzing the database performance parameters and the operation environment parameters and performing characteristic association according to analysis results comprises the following steps:
a) Configuring the operation environment parameters, and taking the minimum value of each operation environment parameter as an initial value;
b) Inputting the initial value of the running environment parameter into the database performance optimization model according to the data operation type to obtain an initial database performance running result;
c) Increasing initial values of the operation environment parameters one by one according to preset increment values to obtain increment values of the operation environment parameters, and respectively inputting the increment values into the database performance optimization model to obtain incremental database performance operation results;
d) Analyzing the initial database performance operation result and the incremental database performance operation result, and determining the association characteristics of the operation environment parameters and the database performance parameters to obtain a parameter characteristic association set;
the operation types comprise adding, deleting, modifying and inquiring, and the default value is set for database management personnel by the preset increment value.
2. The method of claim 1, wherein the predetermined database performance parameters comprise: the number of concurrent transactions processed in unit time, the request response time, the execution time of a single SQL instruction, the data compression ratio and the batch query speed;
the operating environment parameters include: the number of processors, the cache type, the cache space size, the storage space size, the disk read-write speed, the core thread number, the memory space size, the execution optimization scheme, the data distribution and the table query sequence.
3. The method of claim 1, wherein invoking the database performance optimization model to execute the task instructions according to task type comprises:
analyzing the SQL instruction to obtain the execution plan, and judging and determining the operation type of the execution plan;
determining a corresponding database performance optimization model according to the operation type, executing the execution plan according to the database performance optimization model if the database performance optimization model corresponding to the operation type exists, and marking the model calling result as true; if the database performance optimization model corresponding to the type does not exist, executing the execution plan according to the initialized database performance optimization model, and marking the model calling result as false;
and completing task execution and returning an execution result.
4. The method of claim 1, wherein the database performance optimization model is preprocessed according to the model call result, the preprocessing comprising:
if the model calling result is true, comparing the called database performance parameter value of the database performance model with a database performance parameter threshold;
if the model calling result is false, initializing a corresponding database performance optimization model according to the operation type;
the database performance parameter threshold is a parameter index preset by a database manager according to the preset database performance parameter and is used for evaluating the database operation performance.
5. A system for implementing the database performance intelligent optimization method of claim 1, the system comprising a model initialization module, a model invocation module, a model training module, and a configuration optimization module, wherein,
the model initialization module is used for establishing a database performance optimization model according to preset database performance parameters and running environment parameters and initializing the database performance optimization model;
the model calling module is used for monitoring task instructions executed by the database system in real time, calling the database performance optimization model to execute the task instructions according to task types, and generating a model calling result after the task execution is finished;
the model training module is used for adjusting the database performance optimization parameters according to the model calling result, training the database performance optimization model and obtaining an optimized database performance optimization model;
the configuration optimization module is used for automatically optimizing and configuring the database system according to the optimized database performance optimization model;
the step of establishing the database performance optimization model comprises the following steps:
data preparation, namely classifying database performance parameters and running environment parameters and marking key parameters;
characteristic association, namely analyzing the database performance parameters and the running environment parameters, and carrying out characteristic association according to analysis results to obtain a parameter characteristic association set;
the final optimization model is run and determined.
Priority Applications (2)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN202211219319.4A CN115640278B (en) | 2022-09-30 | 2022-09-30 | Method and system for intelligently optimizing database performance |
| PCT/CN2023/105360 WO2024066635A1 (en) | 2022-09-30 | 2023-06-30 | Method and system for intelligently optimizing database performance |
Applications Claiming Priority (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN202211219319.4A CN115640278B (en) | 2022-09-30 | 2022-09-30 | Method and system for intelligently optimizing database performance |
Publications (2)
| Publication Number | Publication Date |
|---|---|
| CN115640278A CN115640278A (en) | 2023-01-24 |
| CN115640278B true CN115640278B (en) | 2023-08-08 |
Family
ID=84943021
Family Applications (1)
| Application Number | Title | Priority Date | Filing Date |
|---|---|---|---|
| CN202211219319.4A Active CN115640278B (en) | 2022-09-30 | 2022-09-30 | Method and system for intelligently optimizing database performance |
Country Status (2)
| Country | Link |
|---|---|
| CN (1) | CN115640278B (en) |
| WO (1) | WO2024066635A1 (en) |
Families Citing this family (2)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN115640278B (en) * | 2022-09-30 | 2023-08-08 | 北京柏睿数据技术股份有限公司 | Method and system for intelligently optimizing database performance |
| CN118363947B (en) * | 2024-06-20 | 2024-09-17 | 西安电子科技大学 | Database substitution method for electric power marketing business |
Citations (3)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN102622441A (en) * | 2012-03-09 | 2012-08-01 | 山东大学 | Automatic performance identification tuning system based on Oracle database |
| CN112486780A (en) * | 2020-12-17 | 2021-03-12 | 中职物联(湖北)信息科技有限公司 | Database performance real-time monitoring and diagnosing method and system based on message middleware |
| CN113010547A (en) * | 2021-05-06 | 2021-06-22 | 电子科技大学 | Database query optimization method and system based on graph neural network |
Family Cites Families (5)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN103853786B (en) * | 2012-12-06 | 2017-07-07 | 中国电信股份有限公司 | The optimization method and system of database parameter |
| KR102511927B1 (en) * | 2018-01-18 | 2023-03-21 | 한국전자통신연구원 | Database System based on JIT Compilation, Query Processing Method of the same and Method for Optimization of Stored Procedure of the same |
| CN113872788A (en) * | 2020-06-30 | 2021-12-31 | 中兴通讯股份有限公司 | Database configuration parameter adjusting method, device and storage medium |
| CN113064879B (en) * | 2021-03-12 | 2023-03-10 | 腾讯科技（深圳）有限公司 | Database parameter adjusting method and device and computer readable storage medium |
| CN115640278B (en) * | 2022-09-30 | 2023-08-08 | 北京柏睿数据技术股份有限公司 | Method and system for intelligently optimizing database performance |
-
2022
- 2022-09-30 CN CN202211219319.4A patent/CN115640278B/en active Active
-
2023
- 2023-06-30 WO PCT/CN2023/105360 patent/WO2024066635A1/en unknown
Patent Citations (3)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN102622441A (en) * | 2012-03-09 | 2012-08-01 | 山东大学 | Automatic performance identification tuning system based on Oracle database |
| CN112486780A (en) * | 2020-12-17 | 2021-03-12 | 中职物联(湖北)信息科技有限公司 | Database performance real-time monitoring and diagnosing method and system based on message middleware |
| CN113010547A (en) * | 2021-05-06 | 2021-06-22 | 电子科技大学 | Database query optimization method and system based on graph neural network |
Also Published As
| Publication number | Publication date |
|---|---|
| CN115640278A (en) | 2023-01-24 |
| WO2024066635A1 (en) | 2024-04-04 |
Similar Documents
| Publication | Publication Date | Title |
|---|---|---|
| CN115640278B (en) | Method and system for intelligently optimizing database performance | |
| CN107239335B (en) | Job scheduling system and method for distributed system | |
| US8271457B2 (en) | Database management system and method which monitors action results and adjusts user parameters in response | |
| US6339771B1 (en) | Method and system for managing connections to a database management system | |
| US20070094270A1 (en) | Method and apparatus for the processing of heterogeneous units of work | |
| CN107612886A (en) | A kind of Spark platforms Shuffle process compresses algorithm decision-making techniques | |
| JPH06214843A (en) | Data base management system and processing method for inquiry | |
| US20070094023A1 (en) | Method and apparatus for processing heterogeneous units of work | |
| CN101477524A (en) | System performance optimization method and system based on materialized view | |
| CN112540849A (en) | Parameter configuration optimization method and system for distributed computing operation | |
| CN112052082B (en) | Task attribute optimization method, device, server and storage medium | |
| Viswanathan et al. | Query and resource optimization: Bridging the gap | |
| US11675515B2 (en) | Intelligent partitioning engine for cluster computing | |
| CN114564726B (en) | Software vulnerability analysis method and system based on big data office | |
| CN110928666B (en) | Method and system for optimizing task parallelism based on memory in Spark environment | |
| CN115237885A (en) | Parameter adjusting method and device of data storage system | |
| CN116974994A (en) | High-efficiency file collaboration system based on clusters | |
| US20200356885A1 (en) | Service management in a dbms | |
| CN116775041A (en) | Big data real-time decision engine based on stream computing framework and RETE algorithm | |
| CN111625352A (en) | Scheduling method, device and storage medium | |
| CN115982230A (en) | Cross-data-source query method, system, equipment and storage medium of database | |
| CN114579280A (en) | Quasi-real-time scheduling method and system | |
| CN114020446A (en) | Cross-multi-engine routing processing method, device, equipment and storage medium | |
| US11822550B2 (en) | Query processing based on stochastic prediction model | |
| CN117992070A (en) | End-side-cloud deployment optimization method for intelligent operation and maintenance program |
Legal Events
| Date | Code | Title | Description |
|---|---|---|---|
| PB01 | Publication | ||
| PB01 | Publication | ||
| SE01 | Entry into force of request for substantive examination | ||
| SE01 | Entry into force of request for substantive examination | ||
| GR01 | Patent grant | ||
| GR01 | Patent grant |