CN114691641A - Database dynamic resource tuning system and method based on deep neural network - Google Patents

Abstract

The invention discloses a database dynamic resource tuning system and method based on a deep neural network. The system comprises: a collection service device and an tuning service device, the collection service device is installed on a database end, and the tuning service device It includes: a rule module, an authority module, an analysis module, a neural network module, a decision module, an audit module, a task module and a message module; the method includes: 1) deep neural network model training, 2) database dynamic resource tuning. The system and method for database dynamic resource tuning based on the deep neural network of the present invention reduces the degree of human intervention in database resource tuning, can achieve precise control of database resources, greatly improves tuning response efficiency and saves labor costs.

Description

A system and method for database dynamic resource tuning based on deep neural network

technical field

The invention relates to the technical field of databases, in particular to a database dynamic resource tuning system and method based on a deep neural network.

Background technique

Data is an important resource and property of an enterprise. Data is stored in the database, and database resources have always been regarded as scarce resources, so it is particularly important to manage the database well. Database vendors provide database dynamic management functions, such as DB2 configurable database system memory, various parameters, etc.; Oracle database system also provides memory management, instance parameter configuration, etc.

By optimizing database resources, the probability of failure can be reduced and losses can be reduced. Database resource tuning is relatively complex, and it has always been completed under manual intervention. It is highly dependent on database administrators. The quality of tuning is closely related to the experience of database administrators. In a complex maintenance and tuning environment, it is difficult to do To real-time response and precise control of resources, maintenance and tuning costs are high.

SUMMARY OF THE INVENTION

The technical problem to be solved by the present invention is to aim at the above-mentioned deficiencies in the prior art, and to provide a database dynamic resource tuning system and method based on a deep neural network, which reduces the degree of human intervention in database resource tuning and realizes the accuracy of database resources. Improve the efficiency of tuning response and save labor costs.

In order to achieve the above purpose of the invention, the technical scheme adopted is:

A database dynamic resource tuning system based on a deep neural network, comprising: a collection service device and a tuning service device, the tuning service device includes: a rule module, an authority module, an analysis module, a neural network module, a decision-making module, an auditing module modules, task modules and message modules;

The collection service device is installed on the database end, and is mainly responsible for collecting database running status data in real time, summarizing the collected data according to the agreed format, and submitting the summary data to the tuning service device; the rule module is mainly responsible for setting tuning rules, Task level, audit rules and message rules; the authority module is mainly responsible for setting login user authority; the analysis module is used to cache the summary data submitted by the collection service device, and parse and analyze the summary data to determine whether it is necessary Tuning the database; the neural network module is mainly responsible for training the business model, predicting and optimizing the adjustment parameters, and transmitting the optimization and adjustment parameters to the decision-making module; the decision-making module is mainly responsible for generating the tuning task, and assigning the The tuning task is sent to the auditing module; the auditing module is mainly responsible for auditing the tuning task, and classifies the tuning task as automatic auditing or manual auditing; the task module is mainly responsible for managing and executing the audited tuning tasks; the The message module is mainly responsible for message management.

Further, it also includes: a DATA module, where the DATA module is used to store rule information, authority information, task information and message information.

Further, the analysis module is pre-installed with kafka.

Further, the permission module is also used for web access module label control.

Further, the database running state data includes: model configuration, database configuration, data volume and read and write access volume.

Further, the neural network module is one of BP neural network, RNN neural network, and LSTM neural network.

A database dynamic resource tuning method based on deep neural network, comprising:

1) Deep neural network model training:

11) Prepare the training sample data and test sample data of the deep neural network;

12) Construct a deep neural network model;

13) Initialize the parameters of the deep neural network model;

14) Train and optimize the deep neural network model;

15) Verify and save the model training results;

2) Database dynamic resource tuning:

21) Collect database operating status data, and organize the data according to the agreed format;

22) Analyze the collected database operating status data, and judge whether the database needs to be tuned according to the tuning rules. If tuning is required, go to step 23); if tuning is not required, record the judgment information, and the task ends;

23) Normalize the collected database operating status data;

24) Send the normalized data into the trained deep neural network to predict and optimize the adjustment parameters;

25) Send the predicted optimization adjustment parameters to the optimization task queue;

26) Classify the tuning task as automatic review or manual review. If it is automatic review, the tuning task will be scheduled to the self-tuning task queue; if it is manually reviewed, the tuning task will be placed in the manual review queue and concurrently The message notifies the relevant personnel. If the manual review is passed, it will be placed in the self-tuning task queue. If the manual review fails, the task will be terminated;

27) Automatically execute the tuning task, send a message to notify the relevant personnel of the execution status, and register the task information.

Further, the deep neural network is one of BP neural network, RNN neural network and LSTM neural network.

Further, the training sample data and the test sample data are the model configuration, database configuration, data volume, read and write access volume data, and database index status during access to the database.

The deep neural network-based database dynamic resource tuning system and method of the present invention has the following beneficial effects:

(1) Using the system and method for database dynamic resource tuning based on the deep neural network of the present invention, the degree of human intervention in database resource tuning is reduced, thereby greatly reducing the possibility of errors by human factors, and positioning the database performance and load. It is more accurate; at the same time, it reduces the dependence of database administrators on work experience, and in a multi-version environment, the transparency of the tuning system is improved through regularization and processization.

(2) The system and method for dynamic database resource tuning based on the deep neural network of the present invention can realize precise control of database resources, greatly increase the cluster management capability, realize scale expansion, and manage large-scale clusters with very little manpower, thereby providing More stable database services, and can formulate personalized processes based on business needs, respond to tuning needs in a timely manner, improve tuning efficiency, and save labor costs.

Description of drawings

FIG. 1 is a structural diagram of a database dynamic resource tuning system based on a deep neural network according to an embodiment of the present invention.

Detailed ways

In order to make the objectives, technical solutions and advantages of the present invention clearer, the present invention will be further described in detail below with reference to the accompanying drawings and embodiments. It should be understood that the specific embodiments described herein are only used to explain the present invention, but not to limit the present invention.

The purpose of the present invention is to provide a database dynamic resource tuning system and method based on a deep neural network, which reduces the degree of human intervention in database resource tuning, realizes precise control of database resources, improves tuning response efficiency and saves labor costs. The principles and implementations of the deep neural network-based database dynamic resource tuning system and method of the present invention will be described in detail below, so that those skilled in the art can understand the technical content of the present invention without creative work.

FIG. 1 is a structural diagram of a database dynamic resource tuning system based on a deep neural network according to an embodiment of the present invention. As shown in FIG. 1 , the database dynamic resource tuning system based on the deep neural network of the present invention includes: a collection service device and an tuning service device. The collection service device is installed on the database end, and is mainly responsible for collecting database operating status data in real time, and according to the agreement The format summarizes the collected data, and submits the summary data to the tuning service device. The collected database running status data includes: model configuration, database configuration, data volume, and read and write access volume, etc. The tuning service device includes: rule module, Permission module, analysis module, neural network module, decision module, audit module, task module and message module. The rules module is mainly responsible for setting tuning rules, task levels, audit rules and message rules. The permission module is mainly responsible for setting login user permissions, user roles, and web access module label control.

Specifically, the tuning rules are as follows:

1) System level: Change database OS, CPU, storage and other optimizations to provide a good environment for database operation.

2) Parameter level: Change the configuration file, modify the parameters of the configuration file, and let the database run to the best state as a software.

Specifically, the task levels are divided according to risk as follows:

S-Class: This type of mission is high-risk, generally related to the direct interests of the company, and is usually not used.

Level A: Usually involves database core tuning or is indirectly related to the system, and is used more frequently.

Level B: A common class of advanced database tuning tasks that are used most frequently.

C-level: relatively simple tasks, generally at the business level, and tuning the part does not affect the whole.

Class D: The simplest tasks, ie fringe casual tasks.

Specifically, the audit rules are as follows:

The audit module automatically approves the tuning tasks with low risk and high business security (such as B, C, and D levels) according to the tuning rules, task levels, etc., and automatically approves the tuning tasks with high risk and low business security (such as A , S grade) will be transferred to manual review, and the corresponding reviewers will be notified in the form of a message. Task review status is divided into the following categories:

Pending review: After the optimization task is created, the status is "pending review", that is, it is still in the review stage. After the review is passed, it becomes the "passed review" status, and high-risk and difficult tasks will be transferred to manual review.

Approved: It means that the tuning task has passed the normal review, and the tuning task that has passed the review will be scheduled to be executed within the specified time period, and can be searched on the task review page. Before the task is tuned, click "Delete the task" and the task becomes the "Delete the task" state.

Manual review: High-risk or difficult tuning tasks will be classified as manual review. Manually perform the auditing and tuning task, and after passing the audit, it becomes the "passed audit" status, and if it does not pass, it becomes the "task removed" status.

The task is removed from the shelf: It means that the task is not in the normal review and approval process, and the task becomes the "approved" status after clicking "task listed".

Specifically, the message rules are as follows:

The message module manages various messages and configures business message channels in a unified manner, and is mainly responsible for sending tuning task information, tuning audit information, and tuning task execution information. The message sending status is divided into the following types:

To be sent: The message is about to be sent, and it will become "Sending" status after starting to send, which can be searched on the message management query page.

Sending: The message is being sent, which can be searched on the message management query page.

Sent: The message has been successfully sent to the contact, which can be searched on the message management query page.

Failed to send: The message failed to be sent for special reasons, which can be found on the message management query page.

The analysis module is prefaced with kafka, which is used to cache the summary data submitted by the collection service device, parse and analyze the summary data, and judge whether the database needs to be tuned according to the tuning rules.

The neural network module is mainly responsible for training the business model, predicting database optimization and adjustment parameters, and transmitting the optimization and adjustment parameters to the decision-making module; preferably, the neural network module is one of BP neural network, RNN neural network, and LSTM neural network.

The decision module is mainly responsible for generating tuning tasks and sending the tuning tasks to the review module. Specifically, after receiving the optimization and adjustment parameters, the decision-making module generates an optimization task according to information such as the optimization rules, task levels, and message rules, and notifies the audit module in the form of a message.

The auditing module is mainly responsible for auditing and tuning tasks, and categorizes tuning tasks as automatic auditing or manual auditing. Specifically, the audit module classifies the tuning tasks with low risk and high business security into the automatic review category according to the tuning rules, task levels, review rules and other information. Such tuning tasks will be set to be automatically reviewed and approved; Tuning tasks with low business security are classified as manual review, and the corresponding reviewers are notified in the form of messages for manual review.

The task module is mainly responsible for managing and executing audited tuning tasks. Pending tasks can be recalled and returned to review evaluation status. After the tuning task is completed, the task module will notify the relevant personnel of the project team in the form of a message of the completion status information.

The message module is mainly responsible for message management, including but not limited to: message channel selection, sending tuning task information, tuning audit information, and tuning task completion status information.

Another embodiment further includes: a DATA module, where the DATA module is used to store rule information, authority information, task information and message information.

An embodiment of the present invention also provides a method for optimizing database dynamic resources based on a deep neural network, comprising the following steps:

1) Deep neural network model training:

11) Prepare training sample data and test sample data for the deep neural network. Select an appropriate amount of normal and compliant historical database operation data as training sample data and test sample data, including: model configuration (CPU/RAM/hard disk/bandwidth), database configuration (type, specification, version, indicator value, associated indicator value) ), data volume, read and write access volume, database indicator status at the time of access and other indicator data, and normalize these data to prevent the original data from being too large or too small to cause adverse effects on the deep neural network.

12) Construct a deep neural network model. Specifically, the deep neural network model can be selected to construct models such as BP neural network, RNN neural network, LSTM neural network, etc. In this embodiment, a three-layer BP neural network model is preferably constructed.

13) Initialize the parameters of the deep neural network model. Specifically, parameters such as weights and bias values of the deep neural network model can be randomly set, or parameters such as weights and bias values can be generated according to certain rules.

14) Train and optimize the deep neural network model. Input the training sample data into the constructed three-layer BP neural network model, calculate the error according to the loss function, and then adjust the weight, bias value and other parameters of each layer of neural network according to the error back propagation. The training is repeated until the error calculated by the loss function is less than a predetermined value, or the change in the error no longer decreases.

15) Verify and save the model training results. After the training of the deep neural network model is completed, use the test sample data to verify its accuracy, and save the model training results.

2) Database dynamic resource tuning:

21) The collection service device collects database operating status data, including: model configuration, database configuration, data volume, read and write access volume, database indicator status during access, etc., and organizes the data according to the agreed format;

22) The analysis module analyzes and collects the database running status data, and judges whether the database needs to be tuned according to the tuning rules. If tuning is required, go to step 23); Mission over. Specifically, according to the overall evaluation and analysis of the tuning rules, the analysis module classifies each indicator data in an agreed format, determines the indicators that need to be tuned, and proceeds to step 23). More specifically, as business data accumulates and queries become slower, the analysis module finds that the join indicator setting is unreasonable after evaluation and analysis, and it is necessary to optimize the configuration (CPU/RAM/hard disk/bandwidth) and model of the join indicator. Index data such as remaining resources, database configuration (type, specification, version, associated index value), data volume, read and write access volume, and database index status during access, go to step 23).

23) Normalize the collected database running state data. In the above specific embodiment, the collected model configuration (CPU/RAM/hard disk/bandwidth), database configuration (type, specification, version, associated indicator value), data volume, read and write access volume, and database indicators during access Status and other indicator data are normalized.

24) Send the normalized data into the trained deep neural network to predict and optimize the adjustment parameters. Specifically, the data normalized in step 23) is input into the three-layer BP neural network model. More specifically, the join index and related configuration information are used as the input data of each neuron in the input layer, and the output layer only sets the unique The neuron predicts the output target tuning value and obtains the optimized tuning parameters.

25) The decision-making module sends the predicted optimization adjustment parameters to the optimization task queue. Specifically, after receiving the optimization and adjustment parameters, the decision-making module generates an optimization task according to information such as the optimization rules, task levels, and message rules, and notifies the audit module in the form of a message.

26) The audit module audits the tuning tasks, and classifies the tuning tasks as automatic audit or manual audit according to the audit rules. If it is in the automatic review category, the tuning task will be scheduled to the self-tuning task queue; if it is in the manual review category, the tuning task will be placed in the manual review queue, and a message will be sent to the relevant personnel. Tuning the task queue, if the manual review fails, the task will be terminated.

27) The task module automatically executes the tasks in the self-tuning task queue, notifies the relevant personnel of the execution status through the message module, and registers the task information.

The system and method for database dynamic resource tuning based on the deep neural network of the present invention adopts the deep neural network model to predict and optimize the adjustment parameters, which reduces the degree of human intervention in database resource tuning, thereby greatly reducing the possibility of errors caused by human factors. The positioning of database performance and load is more accurate; at the same time, the dependence of database administrators on work experience is reduced, and the transparency of the tuning system is improved through regularization and processization in a multi-version environment.

By adopting the system and method for database dynamic resource tuning based on the deep neural network of the present invention, the precise control of database resources can be realized, the cluster management capability can be greatly increased, the scale expansion can be realized, and a large-scale cluster can be managed with very little manpower, thereby providing a more stable Database services, and can formulate personalized processes based on business needs, respond to tuning needs in a timely manner, improve tuning efficiency, and save labor costs.

It can be understood that the above embodiments are only exemplary embodiments adopted to illustrate the principle of the present invention, but the present invention is not limited thereto. Any modifications, equivalent substitutions and improvements made within the spirit and principle of the present invention shall be included in the protection scope of the present invention.

Claims (9)

1. A database dynamic resource tuning system based on a deep neural network is characterized by comprising: gather service device and tuning service device, tuning service device includes: the system comprises a rule module, a permission module, an analysis module, a neural network module, a decision module, an audit module, a task module and a message module;

the acquisition service device is arranged at a database end and is mainly responsible for acquiring running state data of the database in real time, summarizing the acquired data according to an agreed format and reporting the summarized data to the tuning service device;

the rule module is mainly responsible for setting tuning rules, task levels, audit rules and message rules;

the authority module is mainly responsible for setting the authority of the login user;

the analysis module is used for caching the summarized data reported by the acquisition service device, analyzing and analyzing the summarized data and judging whether the database needs to be optimized or not;

the neural network module is mainly responsible for training a business model, predicting an optimization adjustment parameter and transmitting the optimization adjustment parameter to the decision module;

the decision module is mainly responsible for generating a tuning task and sending the tuning task to the auditing module;

the auditing module is mainly responsible for auditing the tuning tasks and classifying the tuning tasks into automatic auditing or manual auditing categories;

the task module is mainly responsible for managing and executing tuning and optimizing tasks which pass the examination and verification;

the message module is mainly responsible for message management.

2. The deep neural network-based database dynamic resource tuning system of claim 1, further comprising: a DATA module to store rule information, permission information, task information, and message information.

3. The deep neural network-based database dynamic resource tuning system of claim 1, wherein the analysis module is preceded by kafka.

4. The deep neural network-based database dynamic resource tuning system of claim 1, wherein the permission module is further configured for web access module tag control.

5. The deep neural network-based database dynamic resource tuning system of claim 1, wherein the database run state data comprises: model configuration, database configuration, data volume and read-write access volume.

6. The deep neural network-based database dynamic resource tuning system of claim 1, wherein the neural network module is one of a BP neural network, an RNN neural network, and an LSTM neural network.

7. A database dynamic resource tuning method based on a deep neural network is characterized by comprising the following steps:

1) deep neural network model training:

11) preparing training sample data and test sample data of the deep neural network;

12) constructing a deep neural network model;

13) initializing parameters of a deep neural network model;

14) training and optimizing the deep neural network model;

15) verifying and storing the model training result;

2) optimizing dynamic resources of the database:

21) collecting database running state data, and sorting the data according to an agreed format;

22) analyzing the collected database running state data, judging whether the database needs to be tuned according to tuning rules, and if so, performing step 23); if the tuning is not needed, recording judgment information, and ending the task;

23) normalizing the collected database running state data;

24) sending the normalized data into a trained deep neural network to predict, optimize and adjust parameters;

25) sending the predicted optimized adjusting parameters into an adjusting and optimizing task queue;

26) classifying the tuning task into automatic audit or manual audit, and if the tuning task is automatic audit, scheduling the tuning task to a self-tuning task queue; if the task is the manual review, the tuning task is put into a manual review queue, and related personnel are notified by a message, if the manual review is passed, the tuning task is put into a self-tuning task queue, and if the manual review is not passed, the task is terminated;

27) and automatically executing the tuning task, sending a message to inform related personnel of the execution condition, and registering task information.

8. The deep neural network-based database dynamic resource tuning method of claim 7, wherein the deep neural network is one of a BP neural network, an RNN neural network and an LSTM neural network.

9. The deep neural network-based database dynamic resource tuning method according to claim 7, wherein the training sample data and the test sample data are model configuration, database configuration, data volume, read-write access volume data of historical operation of the database and index states of the database during access.

Images