Database performance monitoring and tuning method based on artificial intelligence

Abstract

The invention relates to the technical field of database system monitoring, and discloses an artificial intelligence-based database performance monitoring and tuning method, which comprises the following steps of: 1, acquiring database performance data; step 2, sorting and classifying database performance data; 3, calculating a database performance evaluation control group according to the sorted and classified database performance data; 4, evaluating the database performance according to the database performance evaluation control group; and 5, optimizing and adjusting the performance of the database. The database performance is monitored comprehensively, defects and deficiencies in the database performance are found in time, the database performance, stability and user experience are greatly improved, diversified monitoring requirements of enterprises are met, the situation of error report missing is reduced, and judgment on the database performance is improved.

Description

Technical Field

[0001] The present invention relates to the technical field of database system monitoring, and specifically to a method for monitoring and optimizing database performance based on artificial intelligence. Background Art

[0002] As a core component of modern information systems, the database system encompasses databases, database management systems, as well as closely related application programs and professionals. It is an information system for data management, storage, and retrieval. It can efficiently handle massive data processing tasks while strictly ensuring the accuracy, integrity, and availability of data.

[0003] The database system not only has powerful data storage and management functions but also has many remarkable features, enabling multiple users or application programs to access and use the data in the database simultaneously, improving the utilization efficiency of data, and avoiding data redundancy. With the continuous innovation of information technology, the database system will continue to play a key role in various fields. Whether in the informatization management of enterprises, data processing in scientific research, or emerging fields such as intelligent transportation and healthcare, the database system will become an important force driving the continuous progress and development of the information society, helping various industries improve operational efficiency, innovate business models, and further enhance the overall informatization level of society.

[0004] However, the existing database performance monitoring tools lack comprehensive functions and cannot cover all database performance indicators that need to be monitored, failing to meet the diverse monitoring needs of enterprises, resulting in deviations in monitoring results and affecting the judgment of database performance. At the same time, the accuracy of monitoring data is easily affected by various factors, and the existing monitoring tools cannot meet the diverse database performance monitoring, resulting in false alarms or missed reports and unable to achieve comprehensive monitoring of database performance. Summary of the Invention

[0005] (1) Technical Problems to be Solved

[0006] In view of the deficiencies of the prior art, the present invention provides a method for monitoring and optimizing database performance based on artificial intelligence, which has the advantages of comprehensively and comprehensively monitoring the database performance, promptly discovering the defects and deficiencies in the database performance, greatly improving the performance, stability, and user experience of the database, meeting the diverse monitoring needs of enterprises, reducing the situation of error and missed reports, and improving the judgment of database performance.

[0007] (2) Technical Solutions

[0008] To achieve the above object, the present invention provides the following technical solutions: A method for monitoring and optimizing database performance based on artificial intelligence, comprising the following steps:

[0009] Step 1: Obtain database performance data;

[0010] Step 2: Organize and classify the database performance data;

[0011] Step 3: Calculate the database performance evaluation control group based on the organized and classified database performance data;

[0012] Step 4: Evaluate the database performance according to the database performance evaluation control group;

[0013] Step 5: Optimize and adjust the database performance.

[0014] Preferably, the database performance data after re - collecting, organizing, and classifying the database performance data includes database hardware data Ys, database configuration data Rs, and database external data Ws.

[0015] Preferably, the numbering expression of the database hardware data Ys is:

[0016] Ys = {Ycp, Ync, Ycp, Ywl}

[0017] In the expression, Ycp, Ync, Ycp, and Ywl respectively represent the CPU data, memory data, disk data, and network data of the database hardware data Ys;

[0018] Among them, the CPU data includes CPU usage rate Ycps, CPU waiting time Ycpd, and CPU context switch frequency Ycq; the memory data includes memory usage rate Yncs, buffer pool hit rate Yncm, and Swap usage rate Yncy; the disk data includes disk read - write speed Ycpd, disk queue length Ycpl, disk usage rate Ycps, and disk latency rate Ycpy; the network data includes network bandwidth usage rate Ywlk, network latency rate Ywly, and network packet loss rate Ywld.

[0019] Preferably, the numbering expression of the database configuration data Rs is:

[0020] Rs = {Rhc, Rlj, Rrz}

[0021] In the expression, Rhc, Rlj, and Rrz respectively represent cache configuration data, connection configuration data, and log configuration data;

[0022] Among them, the cache configuration data includes buffer pool size Rhcc, query cache size Rhcx, and temporary table space size Rhcl; the connection configuration data includes maximum connection number Rljs, connection timeout Rljc, and connection pool size Rljd; the log configuration data includes transaction log size Rrzs, execution time Rrzz, and log refresh frequency Rrzs.

[0023] Preferably, the number expression of the external data Ws of the database is:

[0024] Ws = {Wwd, Wsd}

[0025] In the expression, Wwd and Wsd respectively represent the ambient temperature and the ambient temperature.

[0026] Preferably, the database performance evaluation control group includes a database hardware evaluation index SYp and a database configuration evaluation index SPp.

[0027] Preferably, the calculation formula of the database hardware evaluation index SYp is:

[0028]

[0029] In the calculation formula, oWwd and oWsd respectively represent the standard values of the ambient temperature and the ambient temperature, represents the ratio between the total value of the ambient temperature and the ambient temperature and the total value of the standard values of the ambient temperature and the ambient temperature, represents the impact of the external data of the database on the database hardware evaluation, α represents the weight of the external data of the database, βi represents the weight of the i-th factor in the database hardware data, i represents the i-th factor in the database hardware data, and oi represents the maximum allowable value of the i-th factor in the database hardware data, represents the ratio between the i-th factor and the maximum allowable value, represents the total value of the product of the weights and ratios of all factors in the database hardware data calculated from i = Ycp to i = Ywl.

[0030] Preferably, the calculation formula of the database configuration evaluation index SPp is:

[0031]

[0032] In the calculation formula, oWwd and oWsd respectively represent the standard values of the ambient temperature and the ambient temperature, represents the ratio between the total value of the ambient temperature and the ambient temperature and the total value of the standard values of the ambient temperature and the ambient temperature, represents the impact of the external data of the database on the database hardware evaluation, α represents the weight of the external data of the database, βj represents the weight of the j-th factor in the database configuration data, j represents the j-th factor in the database configuration data, and oj represents the maximum allowable value of the j-th factor in the database configuration data, represents the ratio between the j-th factor and the maximum allowable value, represents the total value of the product of the weights and ratios of all factors in the database configuration data calculated from j = Rhc to j = Rrz.

[0033] Preferably, when the database hardware evaluation index SYp is greater than the database hardware evaluation threshold, it indicates that the current database performance is poor. At the same time, upgrade the hardware, perform load balancing, and send an alarm signal.

[0034] Preferably, when the database configuration evaluation index SPp is greater than the database configuration evaluation threshold, it indicates that the current database performance is poor. At the same time, dynamically adjust the configuration parameters, perform optimization, and send an alarm signal.

[0035] Compared with the prior art, the present invention provides a method for monitoring and optimizing database performance based on artificial intelligence, which has the following beneficial effects:

[0036] 1. The present invention evaluates the database performance through artificial intelligence algorithms, comprehensively monitors the database performance, timely discovers the defects and deficiencies in the database performance, and performs optimization and early warning processing for the hardware and configuration. This greatly improves the performance, stability, and user experience of the database, meets the diverse monitoring needs of enterprises, reduces the situation of error and omission reports, and improves the judgment of database performance. BRIEF DESCRIPTION OF THE DRAWINGS

[0037] Figure 1 It is a flowchart of the method steps of the present invention. DETAILED DESCRIPTION OF THE INVENTION

[0038] Next, the technical solutions in the embodiments of the present invention will be clearly and completely described in conjunction with the accompanying drawings in the embodiments of the present invention. Obviously, the described embodiments are only a part of the embodiments of the present invention, rather than all of the embodiments. Based on the embodiments of the present invention, all other embodiments obtained by those of ordinary skill in the art without creative efforts shall fall within the protection scope of the present invention.

[0039] Please refer to Figure 1 , a method for monitoring and optimizing database performance based on artificial intelligence, includes the following steps:

[0040] Step 1: Obtain database performance data;

[0041] Step 2: Organize and classify the database performance data to form database hardware data Ys, database configuration data Rs, and database external data Ws;

[0042] The number expression of the database hardware data Ys is:

[0043] Ys = {Ycp, Ync, Ycp, Ywl}

[0044] In the expression, Ycp, Ync, Ycp, and Ywl respectively represent the CPU data, memory data, disk data, and network data of the database hardware data Ys;

[0045] Among them, the CPU data includes the CPU usage rate Ycps, the CPU waiting time Ycpd, and the CPU context switching frequency Ycq; the memory data includes the memory usage rate Yncs, the buffer pool hit rate Yncm, and the Swap usage rate Yncy; the disk data includes the disk read / write speed Ycpd, the disk queue length Ycpl, the disk usage rate Ycps, and the disk latency rate Ycpy; the network data includes the network bandwidth usage rate Ywlk, the network latency rate Ywly, and the network packet loss rate Ywld. Through this data, the status of the database hardware is comprehensively reflected, the hardware resources are accurately located, and the situation of insufficient hardware resources or excessive load can be discovered in advance to avoid system crashes or performance degradation;

[0046] The numbering expression of the database configuration data Rs is:

[0047] Rs = {Rhc, Rlj, Rrz}

[0048] In the expression, Rhc, Rlj, and Rrz represent cache configuration data, connection configuration data, and log configuration data respectively;

[0049] Among them, the cache configuration data includes the buffer pool size Rhcc, the query cache size Rhcx, and the temporary table space size Rhcl; the connection configuration data includes the maximum number of connections Rljs, the connection timeout Rljc, and the connection pool size Rljd; the log configuration data includes the transaction log size Rrzs, the execution time Rrzz, and the log refresh frequency Rrzs, so as to fully and comprehensively understand the performance and operation of the database configuration and timely discover the performance problems existing in the database;

[0050] The numbering expression of the database external data Ws is:

[0051] Ws = {Wwd, Wsd}

[0052] In the expression, Wwd and Wsd represent the environmental temperature and humidity respectively. By monitoring the environmental temperature and humidity, the possible impacts of environmental factors on the database can be timely reflected, making the evaluation results more accurate and comprehensive and improving the evaluation accuracy;

[0053] Step 3: Calculate the database performance evaluation control group based on the sorted and classified database performance data;

[0054] The database performance evaluation control group includes the database hardware evaluation index SYp and the database configuration evaluation index SPp;

[0055] The calculation formula of the database hardware evaluation index SYp is:

[0056]

[0057] In the calculation formula, oWwd and oWsd represent the ambient temperature and the standard value of the ambient temperature respectively. represents the ratio of the total value of ambient temperature to the total value of ambient temperature and ambient temperature standard value, represents the influence of database external data on database hardware evaluation, α represents the weight of database external data, βi represents the weight of the i-th factor in database hardware data, i represents the i-th factor in database hardware data, oi represents the maximum allowable value of the i-th factor in database hardware data, represents the ratio between the ith factor and the maximum allowed value, It represents the total value of the product of all factor weights and ratios in the database hardware data calculated from i=Ycp to i=Ywl. This formula evaluates the database hardware by considering the impact of ambient temperature and humidity on the database hardware, comprehensively understands the performance of the database hardware, and promptly discovers the deficiencies and defects of the database hardware in the database performance so as to intervene in time.

[0058] The calculation formula of the database configuration evaluation index SPp is:

[0059]

[0060] In the calculation formula, oWwd and oWsd represent the ambient temperature and the standard value of the ambient temperature respectively. represents the ratio of the total value of ambient temperature and ambient temperature to the total value of ambient temperature and ambient temperature standard value, represents the influence of database external data on database hardware evaluation, α represents the weight of database external data, βj represents the weight of the jth factor in database configuration data, j represents the jth factor in database configuration data, oj represents the maximum allowable value of the jth factor in database configuration data, represents the ratio between the jth factor and the maximum allowed value, Represents the total value of the product of all factor weights and ratios in the database configuration data calculated from j=Rhc to j=Rrz. This formula evaluates the database configuration by considering the impact of ambient temperature and humidity on the database configuration, comprehensively understands the performance of the database configuration, and promptly discovers the deficiencies and defects of the database configuration in the database performance, so as to intervene in time;

[0061] Step 4: Evaluate the database performance according to the database performance evaluation control group;

[0062] Step 5: Optimize and adjust database performance;

[0063] When the database hardware evaluation index SYp is greater than the database hardware evaluation threshold, it means that the current database performance is poor, and hardware and load balancing are upgraded and an alarm signal is issued;

[0064] When the database configuration evaluation index SPp is greater than the database configuration evaluation threshold, it indicates that the current database performance is poor. At the same time, the configuration parameters are dynamically adjusted, optimized, and an alarm signal is issued.

[0065] The performance of the database is evaluated through artificial intelligence algorithms, and the database performance is comprehensively monitored. Defects and deficiencies in the database performance are promptly discovered, and the hardware and configuration are optimized and pre-warned. This greatly improves the performance, stability, and user experience of the database, meets the diverse monitoring needs of enterprises, reduces the situation of error and omission reports, and improves the judgment of the database performance.

[0066] Although the embodiments of the present invention have been shown and described, for those of ordinary skill in the art, it can be understood that various changes, modifications, substitutions, and variations can be made to these embodiments without departing from the principles and spirit of the present invention. The scope of the present invention is defined by the appended claims and their equivalents.

Claims

1. A method for monitoring and optimizing database performance based on artificial intelligence, characterized in that, The following steps are involved: Step 1: Obtain database performance data; Step 2: Arrange and classify database performance data; Step 3: Calculate the database performance evaluation control group based on the sorted and classified database performance data; Step 4: Evaluate the database performance according to the database performance evaluation control group; Step 5: Optimize and adjust database performance.

2. The method for monitoring and optimizing database performance based on artificial intelligence according to claim 1, wherein: The database performance data after recollecting, sorting and classifying the database performance data includes database hardware data Ys, database configuration data Rs and database external data Ws.

3. The method for monitoring and optimizing database performance based on artificial intelligence according to claim 2, wherein: The numbering expression of the database hardware data Ys is: Ys={Ycp、Ync、Ycp、Ywl} In the expression, Ycp, Ync, Ycp, and Ywl represent the CPU data, memory data, disk data, and network data of the database hardware data Ys, respectively; Among them, CPU data includes CPU utilization rate Ycps, CPU waiting time Ycpd, and CPU context switching frequency Ycq; memory data includes memory utilization rate Yncs, buffer pool hit rate Yncm, and Swap utilization rate Yncy; disk data includes disk read and write speed Ycpd, disk queue length Ycpl, disk utilization rate Ycps, and disk delay rate Ycpy; network data includes network bandwidth utilization rate Ywlk, network delay rate Ywly, and network packet loss rate Ywld.

4. The method for monitoring and optimizing database performance based on artificial intelligence according to claim 3, characterized in that: The numbering expression of the database configuration data Rs is: Rs={Rhc、Rlj、Rrz} In the expression, Rhc, Rlj, and Rrz represent cache configuration data, connection configuration data, and log configuration data, respectively; Among them, the cache configuration data includes the buffer pool size Rhcc, the query cache size Rhcx, and the temporary table space size Rhcl; the connection configuration data includes the maximum number of connections Rljs, the connection timeout Rljc, and the connection pool size Rljd; the log configuration data includes the transaction log size Rrzs, the execution time Rrzz, and the log refresh frequency Rrzs.

5. The method for monitoring and optimizing the performance of a database based on artificial intelligence according to claim 4, wherein: The numbering expression of the database external data Ws is: Ws={Wwd、Wsd} In the expression, Wwd and Wsd represent the ambient temperature and the ambient temperature respectively.

6. The method for monitoring and optimizing database performance based on artificial intelligence according to claim 5, wherein: The database performance evaluation control group includes a database hardware evaluation index SYp and a database configuration evaluation index SPp.

7. The method for monitoring and optimizing database performance based on artificial intelligence according to claim 6, wherein: The calculation formula of the database hardware evaluation index SYp is: In the calculation formula, oWwd and oWsd represent the ambient temperature and the standard value of the ambient temperature respectively. represents the ratio between the total value of the ambient temperature, the ambient temperature and the total value of the standard value of the ambient temperature, represents the impact of external data of the database on the evaluation of the database hardware, α represents the weight of the external data of the database, βi represents the weight of the i-th factor in the database hardware data, i represents the i-th factor in the database hardware data, and oi represents the maximum allowable value of the i-th factor in the database hardware data. represents the ratio between the i-th factor and the maximum allowable value. represents the total value of the product of the weights and ratios of all factors in the database hardware data calculated from i = Ycp to i = Ywl.

8. The method for monitoring and optimizing database performance based on artificial intelligence according to claim 7, wherein: The calculation formula of the database configuration evaluation index SPp is: In the calculation formula, oWwd and oWsd represent the ambient temperature and the standard value of the ambient temperature respectively. represents the ratio between the total value of the ambient temperature and the total value of the standard value of the ambient temperature, represents the impact of external data of the database on the evaluation of the database hardware, α represents the weight of the external data of the database, βj represents the weight of the j-th factor in the database configuration data, j represents the j-th factor in the database configuration data, and oj represents the maximum allowable value of the j-th factor in the database configuration data. represents the ratio between the j-th factor and the maximum allowable value. represents the total value of the product of the weights and ratios of all factors in the database configuration data calculated from j = Rhc to j = Rrz.

9. The method for monitoring and optimizing database performance based on artificial intelligence according to claim 8, wherein: When the database hardware evaluation index SYp is greater than the database hardware evaluation threshold, it means that the current database performance is poor, and the hardware is upgraded, the load is balanced, and an alarm signal is issued.

10. The method for monitoring and optimizing database performance based on artificial intelligence according to claim 9, wherein: When the database configuration evaluation index SPp is greater than the database configuration evaluation threshold, it means that the current database performance is poor, and configuration parameters are dynamically adjusted, optimized, and an alarm signal is issued.

Images