Intelligent analysis method for database performance and storage medium

Abstract

The embodiment of the invention discloses an intelligent analysis method for database performance and a storage medium, and the method comprises the steps: collecting activity session data of an activity session, the activity session data comprises at least one activity session attribute, grouping the activity session data according to the activity session attribute, and storing the grouped activity session data in a database; determining target analysis data in the grouping result corresponding to each activity session attribute, summarizing the target analysis data according to a fixed acquisition frequency to obtain performance cost data corresponding to the target analysis data in the acquisition time, drawing a target performance baseline according to the performance cost data, analyzing database performance based on the target performance baseline, and obtaining the performance of the database. A clear comparison baseline is provided for database performance analysis, and the accuracy and efficiency of database performance analysis are effectively improved.

Description

Technical Field

[0001] The present disclosure relates to the field of computer technology, and in particular to an intelligent analysis method and storage medium for database performance. Background Art

[0002] Database performance analysis is a highly specialized skill area that requires long-term technical accumulation. In related technologies, database performance is usually analyzed with the help of external tools. However, with the introduction and promotion of domestic databases, the differences in monitoring methods between databases make it impossible to use a single method for database performance analysis, resulting in difficulties in data management and low efficiency of database performance analysis. Summary of the invention

[0003] The following is a summary of the subject matter of the detailed description of the present disclosure. This summary is not intended to limit the scope of the claims.

[0004] The disclosed embodiments provide an intelligent analysis method for database performance, which provides a clear comparison baseline for database performance analysis and effectively improves the accuracy and efficiency of database performance analysis.

[0005] On the one hand, an embodiment of the present disclosure provides a method for intelligent analysis of database performance, including: Collecting active session data of the active session, wherein the active session data includes at least one active session attribute; Grouping the activity session data according to the activity session attributes, and determining target analysis data in the grouping results corresponding to each of the activity session attributes; Summarizing the target analysis data according to a fixed collection frequency to obtain performance cost data corresponding to the target analysis data within a collection time, wherein the collection time is the duration of summarizing the target analysis data once according to the fixed collection frequency; A target performance baseline is drawn according to the performance cost data, and database performance is analyzed based on the target performance baseline.

[0006] On the other hand, an embodiment of the present disclosure further provides a computer-readable storage medium, wherein the storage medium stores a computer program, and the computer program is executed by a processor to implement the above-mentioned intelligent analysis method of database performance.

[0007] The disclosed embodiments include at least the following beneficial effects: collecting active session data of active sessions, grouping the active session data according to active session attributes, determining target analysis data in the grouping results of each active session attribute, being able to analyze the active session data from multiple angles, improving the comprehensiveness of database performance analysis, aggregating the target analysis data according to a fixed collection frequency, obtaining performance cost data corresponding to the target analysis data within the collection time, drawing a target performance baseline according to the performance cost data, analyzing database performance based on the target performance baseline, providing a clear comparison baseline for database performance analysis, and effectively improving the accuracy and efficiency of database performance analysis.

[0008] Other features and advantages of the present disclosure will be set forth in the following description, and in part will be apparent from the description, or may be learned by practicing the present disclosure. BRIEF DESCRIPTION OF THE DRAWINGS

[0009] The accompanying drawings are used to provide further understanding of the technical solution of the present disclosure and constitute a part of the specification. Together with the embodiments of the present disclosure, they are used to explain the technical solution of the present disclosure and do not constitute a limitation on the technical solution of the present disclosure.

[0010] Figure 1 A schematic diagram of an optional implementation environment provided for an embodiment of the present disclosure; Figure 2 An optional flow chart of an intelligent analysis method of database performance provided by an embodiment of the present disclosure; Figure 3 An optional schematic diagram of data collection provided by an embodiment of the present disclosure; Figure 4 An optional schematic diagram of an active session performance peak line provided by an embodiment of the present disclosure; Figure 5 An optional schematic diagram of a query statement performance decay line provided in an embodiment of the present disclosure; Figure 6 An optional schematic diagram showing a database performance trend provided in an embodiment of the present disclosure; Figure 7 Another optional schematic diagram for displaying database performance trends provided by the embodiment of the present disclosure; Figure 8 An optional overall framework of the intelligent analysis method of database performance provided in the embodiment of the present disclosure. DETAILED DESCRIPTION

[0011] In order to make the purpose, technical solution and advantages of the present disclosure more clear, the present disclosure is further described in detail below in conjunction with the accompanying drawings and embodiments. It should be understood that the specific embodiments described herein are only used to explain the present disclosure and are not used to limit the present disclosure.

[0012] It should be noted that in various specific embodiments of the present disclosure, when it comes to the need to perform relevant processing based on data related to the characteristics of the target object such as the target object attribute information or attribute information set, the permission or consent of the target object will be obtained first, and the collection, use and processing of these data will comply with relevant laws, regulations and standards. Among them, the target object can be a user. In addition, when the embodiment of the present disclosure needs to obtain the attribute information of the target object, the separate permission or separate consent of the target object will be obtained through a pop-up window or by jumping to a confirmation page. After clearly obtaining the separate permission or separate consent of the target object, the necessary target object-related data used to enable the normal operation of the embodiment of the present disclosure will be obtained.

[0013] In the embodiments of the present disclosure, the term "module" or "unit" refers to a computer program or a part of a computer program that has a predetermined function and works together with other related parts to achieve a predetermined goal, and can be implemented in whole or in part by using software, hardware (such as processing circuits or memories), or a combination thereof. Similarly, a processor (or multiple processors or memories) can be used to implement one or more modules or units. In addition, each module or unit can be part of an overall module or unit that includes the function of the module or unit.

[0014] To facilitate understanding of the technical solution provided by the embodiments of the present disclosure, some key terms used in the embodiments of the present disclosure are explained here: Database: An important tool for organizing, storing, and managing data according to data structures. It is usually stored on computer storage devices and can be accessed and updated through a database management system.

[0015] SQL (Structured Query Language): A standard programming language used to manage and operate relational databases. There are many types of SQL statements, including data query, data update, data definition, data control, etc., among which data query statements are used to retrieve data from the database.

[0016] Database performance analysis is a highly specialized skill area that requires long-term technical accumulation. In related technologies, database performance is usually analyzed with the help of external tools, and due to the differences in monitoring methods between databases, corresponding monitoring tools need to be configured for each database. With the development of society and the introduction and promotion of more domestic databases, the uniqueness of different databases in monitoring methods, data formats, and management interfaces makes it difficult to directly apply traditional database performance analysis methods. Existing database analysis methods are not generalizable, which has caused a contradiction between the rapid growth of business and the lack of professional talents, and there are problems such as difficulty in data management and low efficiency of database performance analysis.

[0017] Based on this, the embodiments of the present disclosure provide an intelligent analysis method for database performance, clarify the performance cost threshold of active sessions, provide a clear comparison baseline for database performance analysis, and effectively improve the accuracy and efficiency of database performance analysis.

[0018] Reference Figure 1 , Figure 1 The schematic diagram of an optional implementation environment provided by the embodiment of the present disclosure includes a terminal 101 and a database 102. Specifically, the terminal 101 collects active session data of active sessions from the database 102, groups the active session data according to active session attributes, determines target analysis data in the grouping results corresponding to each active session attribute, summarizes the target analysis data according to a fixed collection frequency, obtains performance cost data corresponding to the target analysis data within the collection time, draws a target performance baseline according to the performance cost data, analyzes database performance based on the target performance baseline, and displays the target performance baseline and database performance analysis results in the terminal 101. It can be understood that the implementation environment of the intelligent analysis method of database performance provided by the embodiment of the present disclosure can also be a server.

[0019] Reference Figure 2 , Figure 2 An optional flow chart of an intelligent analysis method of database performance provided in an embodiment of the present disclosure may specifically include but not be limited to the following steps S201 to S204: Step S201: Collect active session data of active sessions.

[0020] Specifically, active session data of active sessions are collected from a database, wherein the active session data includes at least one active session attribute, and different data collection schemes can be adopted according to different configurations of the database. When the database itself has a view for storing active session data, the active session data of the database in the last 30 seconds can be queried every 30 seconds, that is, the collection is performed every 30 seconds to obtain the active session data of the active session; when the database itself does not have a view for storing active session data, the active session data of the database can be queried every 1 second, and then the collection is performed every 30 seconds to obtain the active session data of the active session. It should be noted that the collection frequency of collecting active session data is not necessarily to query once every 30 seconds, and the query frequency and collection frequency can be set according to actual conditions, and this application does not make specific limitations.

[0021] When collecting active session data of active sessions from a database, a query statement is required to perform database access and collection operations. For different types of databases, corresponding query statements can be set for different types of databases. After obtaining the active session data of active sessions in the database through query statements, the active session attributes of the currently active active session, such as the active session ID, client information, query statement ID, wait type, and wait event, can be obtained.

[0022] Step S202: grouping the activity session data according to the activity session attributes, and determining target analysis data in the grouping results corresponding to each activity session attribute.

[0023] Among them, the active session attributes are used to describe the status and related information of the active session, including query statement identifier, active session identifier, client information, waiting event information, etc.; the query statement identifier is used to distinguish different query statements, which is the unique identity identifier of the query statement, and can be a summary query statement identifier or an original query statement identifier; the active session identifier is used to distinguish different active sessions that are currently active, and the client information can be the client program name, client port, etc.; the target analysis data is the data that needs to be analyzed, which can be represented in the form of data entries or in the form of a list. When represented in the form of a list, the target analysis data can include a query statement list, an active session list, a client list, a waiting event list, etc.

[0024] In a possible implementation, in the process of grouping the active session data according to the active session attributes of the active session and determining the target analysis data in the grouping results corresponding to each active session attribute, the active session data may be grouped according to the active session attributes to obtain the data to be analyzed corresponding to each active session attribute, the data to be analyzed corresponding to each active session attribute may be looped to obtain the number of waiting type data in the data to be analyzed, and the target analysis data may be determined based on the waiting type data. The waiting type data is data related to the waiting state of the active session, and the waiting type data may be a waiting type provided in the database, or a waiting type set by oneself according to actual conditions, and the waiting type may be idle, lock acquisition, IO wait, etc.

[0025] Specifically, the active session data is grouped according to the session attributes of the active session. When the active session attributes are query statement identifier, active session identifier, client information, and waiting event information, query statement data to be analyzed obtained by grouping with the query statement identifier as the grouping condition, active session data to be analyzed obtained by grouping with the active session identifier as the grouping condition, client data to be analyzed obtained by grouping with the client information as the grouping condition, and waiting event data to be analyzed obtained by grouping with the waiting event information as the grouping condition. Then, the query statement data to be analyzed, the active session data to be analyzed, the client data to be analyzed, and the waiting event data to be analyzed are looped respectively to obtain the number of data items of the waiting type data in each of the data to be analyzed. In all the waiting type data, different query statement identifiers are sorted in order of the number of occurrences from most to least to obtain a query statement list; in all the waiting type data, different active session identifiers are sorted in order of the number of occurrences from most to least to obtain an active session list; in all the waiting type data, different client information is sorted in order of the number of client port occurrences from most to least to obtain a client list; in all the waiting type data, different waiting event information waiting types are sorted in order of the number of occurrences from most to least to obtain a waiting event list. By grouping the active session data with different grouping conditions, the active session data can be analyzed from multiple angles, effectively improving the comprehensiveness of the database performance analysis results.

[0026] Step S203: Summarize the target analysis data according to the fixed collection frequency to obtain the performance cost data corresponding to the target analysis data within the collection time.

[0027] Among them, the collection time is the duration for the target analysis data to be summarized according to a fixed collection frequency. The fixed collection frequency is to collect the target analysis data at a fixed and unchanged frequency within a collection time. For example, it is summarized at a frequency of 10 minutes and 30 seconds, that is, the target analysis data is collected at a fixed collection frequency of once every 30 seconds. The total time for summarization is 10 minutes, that is, the collection time is 10 minutes.

[0028] In a possible implementation, in the process of aggregating the target analysis data according to a fixed collection frequency to obtain the performance cost data corresponding to the target analysis data within the collection time, the target analysis data may be collected according to the fixed collection frequency, and the target analysis data may be aggregated according to the collection time sequence to obtain the performance cost data corresponding to the target analysis data within the collection time. Specifically, the target analysis data may be collected according to the fixed collection frequency, and the target analysis data may be aggregated according to the mobile phone time sequence to obtain the performance cost data corresponding to the target analysis data within the collection time. For example, when the collection time is 10 minutes, the target analysis data summarized during the collection time may be obtained by continuously collecting for 10 minutes at a fixed collection frequency of once every 30 seconds, and the performance cost data corresponding to the query statement list, active session list, client list, and waiting event list within 10 minutes is obtained; or, when the collection time is 1 hour, the target analysis data summarized during the collection time may be obtained by continuously collecting for 1 hour at a fixed collection frequency of once every 10 minutes, and the performance cost data corresponding to the query statement list, active session list, client list, and waiting event list within 1 hour is obtained; or, when the collection time is 24 hours, the target analysis data summarized during the collection time may be obtained by continuously collecting for 24 hours at a fixed collection frequency of once every 60 minutes, and the performance cost data corresponding to the query statement list, active session list, client list, and waiting event list within 24 hours is obtained. Next, the summarized target analysis data is stored. The data storage time can be set according to the collection time and collection frequency. For example, the storage time for data with a collection frequency of 30 seconds can be 7 days, the storage time for data with a collection frequency of 10 minutes can be 30 days, the storage time for data with a collection frequency of 1 hour can be 90 days, and the storage time for data with a collection frequency of 24 hours can be 365 days. By setting differentiated fixed summary frequencies at different collection times, the amount of data storage can be reduced, and it can be achieved to trace back older data with minimal space occupation, thereby providing strong support for subsequent database performance analysis.

[0029] In a possible implementation, a time-continuous query statement performance collection list is obtained, and the performance cost data of each first query statement in the query statement performance collection list in each time-continuous collection time period is obtained. The performance cost data of the second query statement in the query statement list of the target analysis data is collected at least once, and the performance cost data of the second query statement in the query statement list is obtained according to the data collection result. The incremental performance change between the performance cost data of the first query statement and the performance cost data of the second query statement is analyzed to obtain the performance data increment of the second query statement. The query statement performance collection list is a list stored in a database for continuously accumulating query statements, the first query statement is a query statement included in the query statement performance collection list, the performance cost data of the first query statement is the performance cost data accumulated in the same collection time period, and the second query statement is a query statement in the query statement list of the target analysis data obtained by analyzing the current active session data.

[0030] Specifically, a query statement performance collection list with continuous time is collected at a fixed collection frequency in the production database, and the performance cost data of each first query statement in the query statement performance collection list in each continuous collection time period is obtained. Then, the performance cost data of the second query statement in the query statement list of the target analysis data currently collected is collected at least once, and the performance cost data of the second query statement in the current query statement list is obtained according to the at least one data collection result. The performance cost data of the second query statement in the current query statement list of the previous collection time period is associated with the performance cost data of the first query statement in the query statement performance collection list of the next collection time period that is consistent with the second query statement, and the performance increment change between the second query statement and the corresponding first query statement is analyzed, and the performance increment is used as the performance data increment of the second query statement, and the performance data increment is persisted. By associating and analyzing the performance cost data of the second query statement in the previous collection time period with the performance cost data of the first query statement in the next collection time period, it can be ensured that each query statement list can delay a collection time period to collect the performance data increment, so as to analyze the impact of the query statements in the query statement list on the database performance. At the same time, through continuous query statement performance data collection, potential problems in database performance can be discovered in a timely manner. On this basis, the performance cost data of the query statements in the query statement list is collected at least once to ensure that accurate query statement performance cost data can be obtained, which helps to improve the accuracy of database performance evaluation.

[0031] For example, refer to Figure 3 , Figure 3An optional schematic diagram of data collection provided by an embodiment of the present disclosure, in the collection time period T1, the query statements in the current query statement list include SQL1, SQL2, SQL3 and SQL4, and the query statements in the query statement performance collection list include SQL1, SQL2, SQL3 and SQL4. In the collection time period T2, the query statements in the current query statement list include SQL1, SQL2, SQL4 and SQL5, and the query statements in the query statement performance collection list include SQL1, SQL2, SQL3, SQL4 and SQL5. At this time, the second query statement in the current query statement list of the collection time period T1 is associated with the first query statement that is consistent with the second query statement in the query statement performance collection list of the collection time period T2, such as Figure 3 SQL1 in the query statement performance collection list of collection time period T2 is associated with SQL1 in the current query statement list of collection time period T1 (T2.SQL1-T1.SQL1), SQL2 in the query statement performance collection list of collection time period T2 is associated with SQL2 in the current query statement list of collection time period T1 (T2.SQL2-T1.SQL2), SQL3 in the query statement performance collection list of collection time period T2 is associated with SQL3 in the current query statement list of collection time period T1 (T2.SQL3-T1.SQL3), and SQL4 in the query statement performance collection list of collection time period T2 is associated with SQL4 in the current query statement list of collection time period T1 (T2.SQL4-T1.SQL4).

[0032] Step S204: Draw a target performance baseline according to the performance cost data, and analyze database performance based on the target performance baseline.

[0033] According to steps S201 to S203, firstly, active session data of active sessions are collected, and then the active session data are grouped according to the active session attributes of the active sessions. Then, target analysis data are determined in the groups corresponding to the respective active session attributes. Then, the active session data are aggregated according to a fixed collection frequency to obtain performance cost data corresponding to the target analysis data within the collection time. Finally, a target performance baseline is drawn according to the performance cost data, and database performance is analyzed based on the target performance baseline.

[0034] In a possible implementation, in the process of drawing a target performance baseline based on performance cost data and analyzing database performance based on the target performance baseline, the performance cost data of the target analysis data may be obtained, the performance cost data of the same collection time period in the collection time may be extracted from the performance cost data, the first performance cost threshold corresponding to the collection time period may be determined based on the performance cost data of the collection time period, the performance cost baseline may be drawn based on the first performance cost threshold corresponding to each collection time period that is continuous in time, the target performance baseline may be determined based on the performance cost baseline, and the database performance may be analyzed based on the target performance baseline. The collection time period is a specific time period fixed in the collection time, and may be a collection time period with the same collection duration in the fixed collection frequency. For example, if the collection is performed at a fixed collection frequency of once every hour within 24 hours, the collection time period may be 6:00-7:00, 7:00-8:00, etc. Alternatively, the collection time period may also be a specific time period with the same duration as the collection time. For example, if the collection starts at 6:00 and the collection time is 1 hour, then the collection time period may be 6:00-7:00. The performance cost data of the same collection time period is the specific time period with the same duration as the collection time in all historical performance cost data. Performance cost data collected during the collection time period; the performance cost baseline is used to describe the maximum consumption value of database performance of historical active session data during each collection time period. The performance cost baseline can be a session data performance baseline or a query statement single execution cost baseline. The first performance cost threshold is used to describe the maximum consumption value of database performance during a certain collection time period and is used to draw the performance cost baseline; the target performance baseline is used to describe the maximum consumption value of the current active session data during each collection time period. Corresponding to the performance cost baseline, the target performance baseline can be an active session performance peak line or a query statement performance decay line.

[0035] Specifically, when the target analysis data of a collection time is summarized, the performance cost data of the most recently summarized target analysis data is obtained. The performance cost data includes the performance cost data corresponding to the query statement list, the active session list, the client list, and the waiting event list. The performance cost data of the same collection time period within the collection time is extracted from the performance cost data. One of the performance cost data is selected from the performance cost data of the collection time period according to the preset rules, and the value of the performance cost data is determined as the first performance cost threshold corresponding to the collection time period. The session data performance baseline is drawn according to the first performance cost threshold corresponding to each different and temporally continuous collection time period. Then, the active session performance peak line is determined according to the session data performance baseline, and the database performance is analyzed based on the active session performance peak line. Refer to Figure 4 , Figure 4 This is an optional schematic diagram of an active session performance peak line provided in an embodiment of the present disclosure, in which the session data performance baseline is the 90th percentile line of the historical active session ( Figure 4 The dotted line in the figure shows the performance peak line based on the 90th percentile line of historical active sessions ( Figure 4 ), when the performance cost data of the current active session exceeds the performance peak line, the period exceeding the performance peak line is the performance peak period, and the target analysis data of the active session corresponding to the peak period is stored in the specified directory of the database, and the target analysis data of the peak period is analyzed separately.

[0036] Alternatively, when the target analysis data of a collection time is summarized, obtain the single execution cost data of each query statement in the query statement list of the most recently summarized target analysis data, and for any query statement, extract the single execution cost data of the query statement in the same collection time period within the collection time from the single execution cost data, select one of the single execution cost data from the single execution cost data of the query statement according to a preset rule, and determine the value of the single execution cost data as the first performance cost threshold corresponding to the query statement in the collection time period. When the query statement appears on at least m (n>m) days out of the n collection days in the query statement list for obtaining the target analysis data, draw a single execution cost baseline for the query statement based on the first performance cost threshold of the query statement in each continuous collection time period. Then, determine the query statement performance decay line based on the query statement single execution cost baseline, and analyze the database performance based on the query statement performance decay line. Refer to Figure 5 , Figure 5 An optional schematic diagram of a query statement performance decay line provided in an embodiment of the present disclosure, wherein the baseline of the query statement single execution cost in the figure is the 90th percentile line of the historical single logical read of the query statement list, and the query statement performance decay line is determined based on the 90th percentile line of the historical single logical read of the query statement list. When the single logical read cost (average cost of single read rows) of the query statement list of the current active session is greater than the query statement performance decay line, the period exceeding the query statement performance decay line is the query statement performance decay period.

[0037] By drawing the active session performance peak line and the query statement performance decay line, a clear comparison baseline can be provided for database performance analysis, effectively improving the efficiency of database performance analysis.

[0038] In a possible implementation, in the process of determining the first performance cost threshold corresponding to the collection time period according to the performance cost data of each collection time period, the performance cost data corresponding to each collection time period may be specifically sorted in ascending order, and according to the product value of the preset ratio and the number of performance cost data, the value of the performance cost data corresponding to the product value is selected from the sorted performance cost data, and the performance cost data corresponding to the product value with the sequence code being the sequence code is determined as the first performance cost threshold corresponding to the collection time period. The sequence code is a position identifier assigned to each data element after a group of data is arranged in a certain order. For example, a group of data arranged in ascending order may be 1, 3, 8, 10, 16, 22, then the sequence code of data element 1 is 1, the sequence code of data element 3 is 2, and the sequence code of data element 8 is 3.

[0039] Specifically, the performance cost data corresponding to each collection time period is arranged in order from small to large. Assuming that a certain proportion of the performance cost data in these collection time periods is normal data, the proportion is set to a preset proportion. For example, if it is assumed that 90% of the performance cost data in the collection time period is normal data, then 90% is set to the preset proportion. The preset proportion is multiplied by the number of performance cost data to obtain a product value. When the product value is a positive integer, the value of the performance cost data corresponding to the sequence code of the product value is selected from the sorted performance cost data, and the value of the performance cost data corresponding to the sequence code of the product value is determined as the first performance cost threshold corresponding to the collection time period. For example, the performance cost data is arranged in order from small to large as 8, 15, 18, 22, 25, 27, 31, 38, 47, 50, and the calculated product value is 9, then the first performance cost threshold is 47; when the product value is a positive integer, the value of the performance cost data corresponding to the sequence code of the product value is selected from the sorted performance cost data, and the value of the performance cost data corresponding to the product value is determined as the first performance cost threshold corresponding to the collection time period. For example, the performance cost data is arranged in order from small to large as 8, 15, 18, 22, 25, 27, 31, 38, 47, 50, and the calculated product value is 9, then the first performance cost threshold is 47; when the product value is When the value is a floating point number, the performance cost data corresponding to the two sequence codes adjacent to the sequence code of the product value are extracted from the sorted performance cost data, and linear interpolation is performed based on the two adjacent performance cost data, and the result obtained by linear interpolation is used as the first performance cost threshold corresponding to the collection time period. For example, the performance cost data is arranged in order from small to large as 8, 15, 18, 27, 31, 38, 47, 50, and the calculated product value is 7.2. Interpolation calculation is performed on 47 and 50, and the interpolation result is used as the first performance cost threshold. Alternatively, according to the distance from the product value to the two sequence codes adjacent to the product value, weights are configured for the two adjacent sequence codes, and calculation is performed based on the weights and the performance cost data corresponding to the adjacent sequence codes, and the result obtained by calculation is used as the first performance cost threshold corresponding to the collection time period. Continuing with the above example, the sequence code of data element 47 is 7, the sequence code of data element 50 is 8, the distance between sequence code 7 and 7.2 is 0.2, and the distance between sequence code 8 and 7.2 is 0.8. The weight is configured for data element 47 according to the distance. , configure the weight for data element 50 , according to 47 +50 A calculation is performed, and the calculation result is used as the first performance cost threshold.

[0040] In a possible implementation, when determining a target performance baseline based on a performance cost baseline and analyzing database performance based on the target performance baseline, the target performance baseline may be determined based on the performance cost baseline, performance cost data of a currently ongoing session activity is obtained, the performance cost data of the currently ongoing session activity within the collection time is compared with a second performance cost threshold of the target performance baseline, and the database performance is analyzed based on the comparison result. The comparison result is used to indicate that the database performance is stable or abnormal; the second performance cost threshold is used to describe the maximum value of the performance cost data of the currently active session within a certain collection time period, and is used to draw the target performance baseline.

[0041] Specifically, the target performance baseline is determined according to the performance cost baseline. When the target performance baseline is the active session performance peak line, the performance cost data of the current session activity in each collection time period is compared with the second performance cost threshold of the active session performance peak line. When the performance cost data of the current active session is greater than the second performance cost threshold of the active session performance peak line, the comparison result indicates that the database is highly loaded at the current collection time. At this time, the performance cost data corresponding to the query statement list, the active session list, the client list, and the waiting event list summarized in the current collection time period are obtained. By analyzing the performance cost data corresponding to the query statement list in the collection time period, the query statement causing the database performance problem can be obtained; by analyzing the performance cost data corresponding to the active session list in the collection time period, the active session causing the database performance problem can be located; by analyzing the performance cost data corresponding to the waiting event list in the collection time period, the waiting event or resource where the query statement causing the database performance problem is located can be located; by analyzing the performance cost data corresponding to the client list in the collection time period, the client port causing the database performance problem can be located. By comparing and analyzing the performance peak line of the active session with the performance cost data of the current active session, when the database is under high load, it is possible to quickly locate the time period when the database has performance problems, and find out the query statements, wait events, consumed resources, clients causing the problem, etc. that cause the database performance problems within this time period, effectively improving the efficiency of handling database performance problems.

[0042] Reference Figure 6 , Figure 6An optional schematic diagram for displaying database performance trends provided by the embodiment of the present disclosure, in which the dotted line is the peak line of the performance of the active session, and the performance cost data of the active session in the time period from T4 to T5 exceeds the peak line of the performance of the active session, and the time period from T4 to T5 is analyzed at this time. The query statement list information is displayed below, and the top 10 query statements in the query statement list are selected. The query statement SQL20101010101 in instance A ranks first in performance consumption, and its performance consumption accounts for 20%. The query statement SQL20101010102 in instance A ranks second in performance consumption, and its performance consumption accounts for 18%. The query statement SQL20101010103 in instance A ranks third in performance consumption, and its performance consumption accounts for 10%.

[0043] Alternatively, when the target performance baseline is the query statement performance decay line, the single execution cost data of each query statement in the query statement list of the active session within the collection time period is obtained, and for any query statement, the single execution cost data of the query statement in each collection time period is compared with the second performance cost threshold of the query statement performance decay line. When the single execution cost data of the query statement is greater than the second performance cost threshold of the query statement performance decay line, the comparison result indicates that the query statement has experienced performance decay within the collection time period, that is, a database performance problem has occurred within the collection time period. By comparing and analyzing the query statement performance decay line with the query statements in the query statement list of the current active session, the time period in which the query statement has experienced performance decay can be quickly located, and database performance problems can be checked, effectively improving the accuracy and efficiency of handling database performance problems.

[0044] Reference Figure 7 , Figure 7 Another optional schematic diagram for displaying database performance trends provided in an embodiment of the present disclosure, wherein performance data of query statement SQL20101010101 in instance A is displayed, and the dotted line in the performance cost baseline chart of the performance data is the query statement performance decay line. The trend of the number of cost blocks of a single execution of query statement SQL20101010101 is displayed below. In the time interval T0-T3, query statement SQL20101010101 was executed a total of 7 times, and the cost of these 7 times did not exceed the query statement performance decay line. Therefore, in the time interval T0-T3, query statement SQL20101010101 did not have any performance problems.

[0045] In a possible implementation, in the process of determining the target performance baseline according to the performance cost baseline, specifically, for a first performance cost threshold corresponding to any collection time period of the performance cost baseline, a first floating value is determined according to the product between the first performance cost threshold and the floating coefficient, a second floating value is determined according to the sum of the first performance cost threshold and the floating parameter, a maximum value between the first floating value and the second floating value is selected as the second performance cost threshold corresponding to the collection time, and the target performance baseline is determined based on the second performance cost threshold corresponding to each collection time that is continuous in time. Specifically, for any collection time, the first performance cost threshold is multiplied by the floating coefficient to obtain a first floating value, wherein the floating coefficient is the sum of the fixed parameter and the floating ratio, for example, the floating coefficient=100+floating ratio. Then, the second floating value is determined according to the sum of the first performance cost threshold and the floating parameter, and when the first floating value is greater than the second floating value, the first floating value is used as the second performance cost threshold corresponding to the collection time period; or when the second floating value is greater than the first floating value, the second floating value is used as the second performance cost threshold corresponding to the collection time period. The target performance baseline is determined based on the second performance cost thresholds corresponding to each of the temporally continuous collection time periods.

[0046] According to the above description, the method for determining the second performance cost threshold in the acquisition time period can be represented by the following MAX(·) function, where p is the first performance cost threshold, For floating ratio, (100+ ) is the floating coefficient, It is a floating parameter. The floating parameter can be a preset fixed value or adjusted according to the actual situation. The first floating value is , the second floating value is , when the first floating value is greater than the second floating value, the MAX(·) function returns the first floating value.

[0047]

[0048] In a possible implementation, the intelligent analysis method of database performance of the embodiment of the present disclosure can be applied to multi-database management and multi-database performance analysis. Figure 8 , Figure 8 An optional overall framework of the intelligent analysis method for database performance provided in the embodiment of the present disclosure is provided below to fully describe the principle of the intelligent analysis method for database performance in the embodiment of the present disclosure: The intelligent analysis method of database performance of the embodiment of the present disclosure includes a data collection module, a data management module, a database and a target database. The data collection module is used to collect performance cost data of active session data and analyze target analysis data; the data management module is used to summarize the performance cost data of active sessions and interact with the database; the target databases include DAMO, PostgreSQL, openGauss, MogDB, VastBase, and MySQL. The intelligent analysis method of database performance provided by the embodiment of the present disclosure implements multi-database performance analysis based on these 6 databases. In addition, the intelligent analysis method of database performance provided by the embodiment of the present disclosure can also analyze and manage the performance of other types of databases.

[0049] First, configure a corresponding collection query statement for each database, and store the collection query statement in the database. The data management module obtains the collection query statement from the database and sends it to the data collection module. The data collection module collects the active session data of the active session in the corresponding database according to the collection query statement. In the process of collecting the active session data of the active session, different collection schemes are adopted according to the different attributes of the database. When the database itself has a view to store the active session data, the active session data of the database in the last 30 seconds can be queried every 30 seconds, that is, the collection is performed every 30 seconds to obtain the active session data of the active session; when the database itself does not have a view to store the active session data, the active session data of the database can be queried every 1 second, and then the collection is performed every 30 seconds to obtain the active session data of the active session, wherein the active session data includes the active session identifier, client information, query statement identifier, wait type, wait event and other active session attributes of the currently active active session.

[0050] Next, in the data collection module, the active session data is grouped according to the active session attributes, the query statement data to be analyzed is obtained by grouping with the query statement identifier as the grouping condition, the active session data to be analyzed is obtained by grouping with the active session identifier as the grouping condition, the client data to be analyzed is obtained by grouping with the client information as the grouping condition, and the waiting event data to be analyzed is obtained by grouping with the waiting event information as the grouping condition. Then, the query statement data to be analyzed, the active session data to be analyzed, the client data to be analyzed, and the waiting event data to be analyzed are looped respectively to obtain the number of waiting type data in each data to be analyzed, and the target analysis data is determined based on the waiting type data. According to the above-mentioned active session attributes used for grouping, the target analysis data includes a query statement list, an active session list, a client list, and a waiting event list.

[0051] Next, the performance cost data of each query statement in the query statement list is obtained in the data collection module. Since the performance cost data of the query statement is stored in the database as a cumulative value, the performance cost data of the query statement needs to calculate the increment between adjacent time periods when statistics are collected, so the collection of the performance cost data of the query statement needs to have time continuity. Specifically, according to the query statement list of the target analysis data collected at a fixed collection frequency, the performance cost data of each first query statement in the query statement list within the collection time period is obtained, and the cumulative performance cost data of each second query statement in the query statement performance collection list within the collection time period of the same period as the current collection time is collected from the production library. During the collection process, each first query statement entering the query statement list needs to undergo performance data collection twice to ensure the accuracy of the performance cost data of the first query statement. Then, the performance cost data of the second query statement in the current query statement list of the previous collection time period is associated with the performance cost data of the first query statement consistent with the second query statement in the query statement performance collection list of the next collection time period, and the performance incremental change between the performance cost data of the second query statement and the corresponding performance cost data of the first query statement is analyzed, and the performance increment is used as the performance data increment of the second query statement to ensure that each query statement list can collect the performance data increment with a delay of 30 seconds, and the performance data increment is persisted.

[0052] Next, the target analysis data is summarized in the data collection module. The summary levels are 10 minutes, 1 hour, and 24 hours, and the frequencies corresponding to the summary levels are 10 minutes, 1 hour, and 24 hours. Specifically, the 10-minute summary data is obtained by continuously collecting data for 10 minutes at a collection frequency of once every 30 seconds, the 1-hour summary data is obtained by continuously collecting data for 1 hour at a fixed collection frequency of once every 10 minutes, and the 24-hour summary data is obtained by continuously collecting data for 24 hours at a fixed collection frequency of once every 60 minutes. The performance cost data obtained at these three summary levels include query statement lists, active session lists, client lists, waiting event lists, and performance cost data corresponding to query statements.

[0053] The above summary is to reduce the amount of data storage. The storage time for data with a collection frequency of 30 seconds can be 7 days, the storage time for data with a collection frequency of 10 minutes can be 30 days, the storage time for data with a collection frequency of 1 hour can be 90 days, and the storage time for data with a collection frequency of 24 hours can be 365 days. By setting different storage times for different frequencies, you can use the smallest storage space to trace back to longer-term data.

[0054] Next, the active session data is obtained, and it is assumed that the active session business is in a normal state for 90% of the time, and there may be performance problems that need attention in the remaining part. Based on this, the active session performance baseline is drawn. The active session performance baseline can be the 90th percentile line of the performance cost data of the active session in the same period (which can be hours) in the most recent (which can be 60 days). Then, based on the active session performance baseline, a value is floated up to obtain the active session performance peak line. Whenever the data at the 1-hour level is aggregated, the active session performance peak line is triggered to be drawn, and the performance peak line corresponding to the instance involved in the analysis is recorded for each instance. When the performance peak period occurs, it can be judged that the database is under high load, and the target analysis data corresponding to the performance peak period is collected, and the reasons that may cause database performance problems are analyzed. The target analysis data corresponding to the performance peak period is stored in the specified directory of the database through the data management module for subsequent analysis.

[0055] Next, obtain the single execution cost of the query statements in the query statement list in the active session data, and assume that the query statements are running normally 90% of the time. Analyze the query statement list in the collection time period that is the same as the most recent collection time (which can be 60 days) and the current collection time (which can be the most recent 1 hour), and confirm that the query statements in the query statement list appear for at least 7 days in 60 days, and draw the query statement single execution cost baseline based on this. Then, based on the query statement single execution cost baseline, float a value upward to obtain the query statement performance decay line. Whenever the performance cost data corresponding to the query statement list at the 1-hour level is summarized, trigger the drawing of the query statement performance decay line, and record the query statement performance decay line of each query statement in the query statement list for each instance. Later, based on the query statement performance decay line, analyze whether the query statement is in performance decay.

[0056] The intelligent analysis method of database performance provided by the embodiment of the present disclosure configures different collection query statements for each database, groups active session data based on active session attributes, summarizes target analysis data, draws active session performance peak lines and query statement performance decay lines based on the summarized data, and analyzes the current database performance based on the active session performance peak lines and query statement performance decay lines. It realizes multiple database performance analysis and multiple database joint management, and can provide support for data of multiple databases. On this basis, the method for analyzing the current database performance based on the active session performance peak lines and query statement performance decay lines can support second-level database query statement performance analysis and second-level detection and killing, thereby improving the efficiency of analyzing database performance and solving database performance problems, and further improving the security of the database.

[0057] The terms "first", "second", "third", "fourth", etc. (if any) in the specification of the present disclosure and the above-mentioned drawings are used to distinguish similar objects, and are not necessarily used to describe a specific order or sequence. It should be understood that the data used in this way can be interchanged where appropriate to describe the embodiments of the present disclosure, such as being able to be implemented in an order other than those illustrated or described herein. In addition, the terms "including" and "having" and any variations thereof are intended to cover non-exclusive inclusions, for example, a process, method, system, product or device that includes a series of steps or units is not necessarily limited to those steps or units clearly listed, but may include other steps or units that are not clearly listed or inherent to these processes, methods, products or devices.

[0058] It should be understood that in the present disclosure, "at least one (item)" means one or more, and "plurality" means two or more. "And / or" is used to describe the association relationship of associated objects, indicating that three relationships may exist. For example, "A and / or B" can mean: only A exists, only B exists, and A and B exist at the same time, where A and B can be singular or plural. The character " / " generally indicates that the objects associated before and after are in an "or" relationship. "At least one of the following" or similar expressions refers to any combination of these items, including any combination of single or plural items. For example, at least one of a, b or c can mean: a, b, c, "a and b", "a and c", "b and c", or "a and b and c", where a, b, c can be single or multiple.

[0059] It should be understood that in the description of the embodiments of the present disclosure, the meaning of multiple (or multiple items) is more than two, greater than, less than, exceed, etc. are understood as not including the number, and above, below, within, etc. are understood as including the number.

[0060] In the several embodiments provided in the present disclosure, it should be understood that the disclosed systems, devices and methods can be implemented in other ways. For example, the device embodiments described above are only schematic. For example, the division of units is only a logical function division. There may be other division methods in actual implementation. For example, multiple units or components can be combined or integrated into another system, or some features can be ignored or not executed. Another point is that the mutual coupling or direct coupling or communication connection shown or discussed can be an indirect coupling or communication connection through some interfaces, devices or units, which can be electrical, mechanical or other forms.

[0061] The units described as separate components may or may not be physically separated, and the components shown as units may or may not be physical units, that is, they may be located in one place or distributed on multiple network units. Some or all of the units may be selected according to actual needs to achieve the purpose of the solution of this embodiment.

[0062] In addition, each functional unit in each embodiment of the present disclosure may be integrated into one processing unit, or each unit may exist physically separately, or two or more units may be integrated into one unit. The above-mentioned integrated unit may be implemented in the form of hardware or in the form of software functional units.

[0063] If the integrated unit is implemented in the form of a software functional unit and sold or used as an independent product, it can be stored in a computer-readable storage medium. Based on this understanding, the technical solution of the present disclosure is essentially or the part that contributes to the prior art or all or part of the technical solution can be embodied in the form of a software product, and the computer software product is stored in a storage medium, including a number of instructions for a computer device (which can be a personal computer, a server, or a network device, etc.) to perform all or part of the steps of the various embodiments of the present disclosure. The aforementioned storage medium includes: U disk, mobile hard disk, read-only memory (Read-Only Memory, referred to as ROM), random access memory (Random Access Memory, referred to as RAM), disk or optical disk and other media that can store program codes.

[0064] It should also be understood that the various implementations provided in the embodiments of the present disclosure can be combined arbitrarily to achieve different technical effects.

[0065] The above is a specific description of the preferred implementation of the present disclosure, but the present disclosure is not limited to the above-mentioned implementation mode. Technical personnel familiar with the field can also make various equivalent deformations or substitutions under the shared conditions without violating the spirit of the present disclosure. These equivalent deformations or substitutions are all included in the scope defined by the claims of the present disclosure.

Claims

1. A method for intelligent analysis of database performance, characterized in that: include: Collecting active session data of the active session, wherein the active session data includes at least one active session attribute; Grouping the activity session data according to the activity session attributes, and determining target analysis data in the grouping results corresponding to each of the activity session attributes; Summarizing the target analysis data according to a fixed collection frequency to obtain performance cost data corresponding to the target analysis data within a collection time, wherein the collection time is the duration of summarizing the target analysis data once according to the fixed collection frequency; A target performance baseline is drawn according to the performance cost data, and database performance is analyzed based on the target performance baseline.

2. The intelligent analysis method of database performance according to claim 1, characterized in that: The step of drawing a target performance baseline according to the performance cost data and analyzing database performance based on the target performance baseline includes: Acquire the performance cost data of the target analysis data, extract the performance cost data of the same collection time period in the collection time from the performance cost data, and determine a first performance cost threshold corresponding to the collection time period based on the performance cost data of the collection time period; Draw a performance cost baseline according to the first performance cost threshold corresponding to each of the consecutive collection time periods; The target performance baseline is determined according to the performance cost baseline, and database performance is analyzed based on the target performance baseline.

3. The intelligent analysis method of database performance according to claim 2, characterized in that: The determining, according to the performance cost data of each collection time period, a first performance cost threshold corresponding to the collection time period includes: The performance cost data corresponding to each of the collection time periods are sorted in ascending order, and according to the product value of a preset ratio and the number of the performance cost data, the performance cost data whose sequence code corresponds to the product value is selected from the sorted performance cost data, and the performance cost data whose sequence code corresponds to the product value is determined as the first performance cost threshold corresponding to the collection time period.

4. The intelligent analysis method of database performance according to claim 2, characterized in that: Determining a target performance baseline according to the performance cost baseline, and analyzing database performance based on the target performance baseline, includes: Determining the target performance baseline according to the performance cost baseline; Obtain the performance cost data of the currently ongoing active session, compare the performance cost data of the currently ongoing active session within the collection time period with a second performance cost threshold of the target performance baseline, and analyze database performance according to the comparison result, wherein the comparison result is used to indicate that the database performance is stable, or to indicate that the database performance is abnormal.

5. The intelligent analysis method of database performance according to claim 4, characterized in that: The determining the target performance baseline according to the performance cost baseline includes: For any of the first performance cost thresholds corresponding to the collection time period, a first floating value is determined according to the product of the first performance cost threshold and a floating coefficient, a second floating value is determined according to the sum of the first performance cost threshold and the floating parameter, and a maximum value between the first floating value and the second floating value is selected as the second performance cost threshold corresponding to the collection time period; The target performance baseline is determined based on the second performance cost threshold corresponding to each of the consecutive collection time periods.

6. The intelligent analysis method of database performance according to claim 1, characterized in that: The active session data includes waiting type data, and the active session data is grouped according to active session attributes, and target analysis data is determined in grouping results corresponding to each of the active session attributes, including: Grouping the activity session data according to the activity session attributes to obtain data to be analyzed corresponding to each activity session attribute; The data to be analyzed corresponding to each of the active session attributes is looped to obtain the number of data items of the waiting type data in the data to be analyzed, and the target analysis data is determined based on the waiting type data.

7. The intelligent analysis method of database performance according to claim 1, characterized in that: The target analysis data is aggregated according to a fixed collection frequency to obtain performance cost data corresponding to the target analysis data within the collection time, including: The target analysis data is collected according to the fixed collection frequency, and the target analysis data is summarized according to the collection time sequence to obtain the performance cost data corresponding to the target analysis data within the collection time.

8. The intelligent analysis method of database performance according to claim 7, characterized in that: The target analysis data includes a query statement list, and the intelligent analysis method for database performance also includes: Acquire a time-continuous query statement performance collection list, and obtain the performance cost data of each first query statement in the query statement performance collection list within each of the time-continuous collection time periods; Performing at least one data collection on the performance cost data of the second query statement in the query statement list of the target analysis data, and obtaining the performance cost data of the second query statement in the query statement list according to the data collection result; An incremental performance change between the performance cost data of the first query statement and the performance cost data of the second query statement is analyzed to obtain a performance data increment of the second query statement.

9. A computer-readable storage medium storing a computer program, characterized in that: When the computer program is executed by a processor, the intelligent analysis method of database performance described in any one of claims 1 to 8 is implemented.

Images