Intelligent analysis method for database performance and storage medium

Abstract

This disclosure presents an intelligent database performance analysis method and storage medium applied to a target database. It collects active session data from the target database based on a collection query statement. The active session data includes at least one active session attribute. The active session data is grouped according to these attributes. Target analysis data is determined from the grouping results of each active session attribute. The active session data is summarized at a fixed frequency to obtain performance cost data corresponding to the target analysis data within the collection period. A performance cost baseline is drawn based on the performance cost data. A target performance baseline is determined based on the performance cost baseline. Database performance is analyzed based on the target performance baseline, providing a clear comparative baseline for database performance analysis and effectively improving the accuracy and efficiency of database performance analysis.

Description

Technical Field

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

[0002] Database performance analysis is a highly specialized skill field that requires long-term technical accumulation. Related technologies typically rely on external tools for database performance analysis. However, with the introduction and widespread use of domestically produced databases, the differences in monitoring methods between various databases make it impossible to use a single method for database performance analysis, resulting in difficulties in data management and low efficiency in database performance analysis. Summary of the Invention

[0003] The following is an overview of the subject matter described in detail in this disclosure. This overview is not intended to limit the scope of the claims.

[0004] This disclosure provides an intelligent method for database performance analysis, offering a clear baseline for comparison and effectively improving the accuracy and efficiency of database performance analysis.

[0005] On the one hand, embodiments of this disclosure provide an intelligent analysis method for database performance, including:

[0006] Collect activity session data of active sessions, wherein the activity session data includes at least one activity session attribute;

[0007] The activity session data is grouped according to the activity session attributes, and the target analysis data is determined from the grouping results corresponding to each activity session attribute.

[0008] The target analysis data is aggregated at a fixed collection frequency to obtain the performance cost data corresponding to the target analysis data within the collection time, wherein the collection time is the duration of one aggregation of the target analysis data at the fixed collection frequency;

[0009] A target performance baseline is drawn based on the performance cost data, and database performance is analyzed based on the target performance baseline.

[0010] On the other hand, this disclosure also provides a computer-readable storage medium storing a computer program that is executed by a processor to implement the above-described intelligent analysis method for database performance.

[0011] The embodiments disclosed herein include at least the following beneficial effects: collecting activity session data from active sessions, grouping the activity session data according to activity session attributes, determining target analysis data from the grouping results of each activity session attribute, enabling analysis of activity session data from multiple perspectives, improving the comprehensiveness of database performance analysis, summarizing the target analysis data according to a fixed collection frequency to obtain performance cost data corresponding to the target analysis data within the collection time, drawing a target performance baseline based on the performance cost data, and analyzing database performance based on the target performance baseline, providing a clear comparative baseline for database performance analysis, and effectively improving the accuracy and efficiency of database performance analysis.

[0012] Other features and advantages of this disclosure will be set forth in the following description and will be apparent in part from the description or may be learned by practicing this disclosure. Attached Figure Description

[0013] The accompanying drawings are provided to further understand the technical solutions of this disclosure and constitute a part of the specification. They are used together with the embodiments of this disclosure to explain the technical solutions of this disclosure and do not constitute a limitation on the technical solutions of this disclosure.

[0014] Figure 1 A schematic diagram illustrating an optional implementation environment provided for an embodiment of this disclosure;

[0015] Figure 2 A schematic diagram of an optional process for an intelligent database performance analysis method provided in this embodiment of the disclosure;

[0016] Figure 3 An optional schematic diagram illustrating data acquisition provided in an embodiment of this disclosure;

[0017] Figure 4 This is an optional schematic diagram of the active session performance peak line provided in an embodiment of this disclosure;

[0018] Figure 5 This is an optional schematic diagram of the query performance degradation line provided in an embodiment of this disclosure;

[0019] Figure 6 An optional schematic diagram illustrating database performance trends provided for embodiments of this disclosure;

[0020] Figure 7 Another optional schematic diagram illustrating database performance trends provided for embodiments of this disclosure;

[0021] Figure 8 This is an optional overall framework for the intelligent analysis method of database performance provided in the embodiments of this disclosure. Detailed Implementation

[0022] To make the objectives, technical solutions, and advantages of this disclosure clearer, the following detailed description is provided in conjunction with the accompanying drawings and embodiments. It should be understood that the specific embodiments described herein are merely illustrative and are not intended to limit the scope of this disclosure.

[0023] It should be noted that in the various specific embodiments of this disclosure, when processing is required based on data related to the characteristics of the target object, such as target object attribute information or a set of attribute information, the permission or consent of the target object will be obtained first. Furthermore, the collection, use, and processing of this data will comply with relevant laws, regulations, and standards. The target object can be a user. In addition, when embodiments of this disclosure require obtaining target object attribute information, separate permission or consent from the target object will be obtained through pop-ups or redirection to a confirmation page. Only after obtaining the target object's separate permission or consent will the necessary target object-related data for the normal operation of the embodiments of this disclosure be obtained.

[0024] In this disclosure, the terms "module" or "unit" refer to a computer program or part of a computer program that has a predetermined function and works with other related parts to achieve a predetermined goal, and can be implemented wholly or partially using software, hardware (such as processing circuitry or memory), or a combination thereof. Similarly, a processor (or multiple processors or memory) can be used to implement one or more modules or units. Furthermore, each module or unit can be part of an overall module or unit that includes the functionality of that module or unit.

[0025] To facilitate understanding of the technical solutions provided in the embodiments of this disclosure, some key terms used in the embodiments of this disclosure will be explained below:

[0026] Database: An important tool for organizing, storing and managing data according to a data structure. It is usually stored on computer storage devices and can be accessed and updated through a database management system.

[0027] SQL (Structured Query Language) is a standard programming language used to manage and manipulate relational databases. SQL statements come in various types, including data query, data update, data definition, and data control. Data query statements are used to retrieve data from the database.

[0028] Database performance analysis is a highly specialized skill field requiring extensive technical expertise. Related technologies typically rely on external tools for database performance analysis, and due to differences in monitoring methods between various databases, specific monitoring tools need to be configured for each database type. With societal development and the introduction and widespread use of more domestically produced databases, the unique characteristics of different databases in terms of monitoring methods, data formats, and management interfaces make traditional database performance analysis methods difficult to apply directly. Existing database analysis methods also lack generalization capabilities, leading to a contradiction between rapid business growth and a shortage of specialized talent, resulting in difficulties in data management and low efficiency in database performance analysis.

[0029] Based on this, the present disclosure provides an intelligent analysis method for database performance, which clarifies the performance cost threshold of active sessions, provides a clear comparative baseline for database performance analysis, and effectively improves the accuracy and efficiency of database performance analysis.

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

[0031] Reference Figure 2 , Figure 2 This is an optional flowchart illustrating an intelligent database performance analysis method provided in this embodiment of the disclosure, which may include, but is not limited to, the following steps S201-S204:

[0032] Step S201: Collect active session data of active sessions.

[0033] Specifically, activity session data is collected from a database. This activity session data includes at least one activity session attribute, and different data collection schemes can be adopted depending on the database configuration. When the database itself has a view storing activity session data, the database can be queried every 30 seconds to retrieve the activity session data for the most recent 30 seconds, i.e., data is collected every 30 seconds. When the database itself does not have a view storing activity session data, the database can be queried every 1 second, and then data is collected every 30 seconds. It should be noted that the collection frequency of activity session data is not necessarily every 30 seconds; the query frequency and collection frequency can be set according to the actual situation, and this application does not impose specific limitations.

[0034] When collecting activity session data from a database, query statements are required to perform database access and data collection operations. Different query statements can be set for different types of databases. After retrieving activity session data from the database using query statements, the activity session attributes of the currently active session, such as the activity session identifier, client information, query statement identifier, wait type, and wait events, can be obtained.

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

[0036] The active session attributes describe the status and related information of an active session, including query statement identifier, active session identifier, client information, wait event information, etc. The query statement identifier is used to distinguish different queries and is a unique identifier for the query statement. It can be a summary query statement identifier or a raw query statement identifier. The active session identifier is used to distinguish different currently active sessions. Client information can be the client program name, client port, etc. The target analysis data is the data to be analyzed. It 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 list of query statements, a list of active sessions, a list of clients, a list of wait events, etc.

[0037] In one possible implementation, the process of grouping activity session data according to activity session attributes and determining the target analysis data from the grouping results corresponding to each activity session attribute can specifically involve grouping the activity session data according to activity session attributes to obtain the data to be analyzed corresponding to each activity session attribute, iterating through the data to be analyzed corresponding to each activity session attribute to obtain the number of data entries of the waiting type in the data to be analyzed, and determining the target analysis data based on the waiting type data. Here, the waiting type data is data related to the waiting state of the activity session. The waiting type data can be a built-in waiting type in the database or a waiting type that can be set according to the actual situation. The waiting type can be idle, lock acquisition, IO wait, etc.

[0038] 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 wait event information, the following data are obtained: query statement data to be analyzed (grouped by query statement identifier), active session data to be analyzed (grouped by active session identifier), client data to be analyzed (grouped by client information), and wait event data to be analyzed (grouped by wait event information). Then, the data for each of the following groups is iterated through to obtain the number of data entries of the wait type in each group. In all waiting type data, different query statement identifiers are sorted in descending order of frequency of occurrence to obtain a query statement list; in all waiting type data, different active session identifiers are sorted in descending order of frequency of occurrence to obtain an active session list; in all waiting type data, different client information is sorted in descending order of frequency of client port occurrence to obtain a client list; in all waiting type data, different waiting event information is sorted in descending order of frequency of occurrence to obtain a waiting event list. Grouping active session data by different grouping conditions allows for analysis of active session data from multiple perspectives, effectively improving the comprehensiveness of database performance analysis results.

[0039] Step S203: Summarize 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.

[0040] The collection time is the duration of a single summary of the target analysis data at a fixed collection frequency. The fixed collection frequency is the frequency at which the target analysis data is collected within a collection time. For example, if the data is summarized at a frequency of 10 minutes and 30 seconds, the target analysis data is collected at a fixed collection frequency of once every 30 seconds, and the total summary time is 10 minutes.

[0041] In one possible implementation, the process of aggregating target analysis data at a fixed collection frequency to obtain performance cost data corresponding to the target analysis data within the collection period can specifically involve collecting target analysis data at a fixed collection frequency and aggregating the target analysis data according to the collection time sequence to obtain performance cost data corresponding to the target analysis data within the collection period. More specifically, target analysis data is collected at a fixed collection frequency and aggregated according to the mobile phone time sequence to obtain performance cost data corresponding to the target analysis data within the collection period. For example, when the collection time is 10 minutes, the target analysis data summarized within the collection time can be obtained by continuously collecting data at a fixed collection frequency of once every 30 seconds for 10 minutes, resulting in performance cost data corresponding to the query statement list, active session list, client list, and wait event list within 10 minutes; or, when the collection time is 1 hour, the target analysis data summarized within the collection time can be obtained by continuously collecting data at a fixed collection frequency of once every 10 minutes for 1 hour, resulting in performance cost data corresponding to the query statement list, active session list, client list, and wait event list within 1 hour; or, when the collection time is 24 hours, the target analysis data summarized within the collection time can be obtained by continuously collecting data at a fixed collection frequency of once every 60 minutes for 24 hours, resulting in performance cost data corresponding to the query statement list, active session list, client list, and wait event list within 24 hours. Next, the summarized target analysis data is stored. The data storage time can be set according to the collection time and frequency. For example, the storage time for data collected every 30 seconds can be 7 days, for data collected every 10 minutes can be 30 days, for data collected every hour can be 90 days, and for data collected every 24 hours can be 365 days. By setting different fixed summarization frequencies for different collection times, the amount of data stored can be reduced, and it is possible to trace back to data from a longer period of time with minimal space usage, thus providing strong support for subsequent database performance analysis.

[0042] In one possible implementation, a time-continuous query performance collection list is obtained. Performance cost data for each first query in the query performance collection list is obtained within each time-continuous collection period. Performance cost data for the second query in the query statement list of the target analysis data is collected at least once. Based on the data collection results, the performance cost data for the second query in the query statement list is obtained. The incremental performance change between the performance cost data of the first and second queries is analyzed to obtain the performance data increment for the second query. Here, the query performance collection list is a continuously accumulated list of query statements stored in a database. The first query is the query statement included in the query performance collection list, and its performance cost data is the accumulated performance cost data within the same collection period. The second query is the query statement in the query statement list of the target analysis data obtained by analyzing the current active session data.

[0043] Specifically, a performance data collection list of consecutive query statements is collected from the production database at a fixed collection frequency. This yields performance cost data for each first query statement in the performance data collection list within each consecutive collection time period. Then, the performance cost data of the second query statement in the currently collected target analysis data's query statement list is collected at least once. Based on the results of this at least once collection, the performance cost data of the second query statement in the current query statement list is obtained. The performance cost data of the second query statement in the current query statement list from the previous collection time period is correlated with the performance cost data of the first query statement that matches the second query statement in the query statement performance data collection list of the next collection time period. The performance increment change between the second query statement and the corresponding first query statement is analyzed, and this performance increment is used as the performance data increment of the second query statement and persisted. By correlating and analyzing the performance cost data of the second query statement from the previous collection time period with the performance cost data of the first query statement in the next collection time period, it is ensured that the performance data increment of each query statement list is collected with a delay of one collection time period, thereby analyzing the impact of the query statements in the query statement list on database performance. Meanwhile, by continuously collecting query performance data, potential database performance problems can be identified in a timely manner. Based on this, the performance cost data of the query statements in the query statement list should be collected at least once to ensure that accurate performance cost data of the query statements can be obtained, which helps to improve the accuracy of database performance evaluation.

[0044] For example, refer to Figure 3 , Figure 3This is an optional schematic diagram of data acquisition provided in an embodiment of this disclosure. During acquisition 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 acquisition list include SQL1, SQL2, SQL3, and SQL4. During acquisition 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 acquisition list include SQL1, SQL2, SQL3, SQL4, and SQL5. At this time, the second query statement in the current query statement list of acquisition time period T1 is associated with the first query statement in the query statement performance acquisition list of acquisition time period T2 that matches the second query statement. For example... Figure 3 The SQL1 in the query performance collection list for the collection time period T2 is associated with the SQL1 in the current query statement list for the collection time period T1 (T2.SQL1-T1.SQL1), the SQL2 in the query performance collection list for the collection time period T2 is associated with the SQL2 in the current query statement list for the collection time period T1 (T2.SQL2-T1.SQL2), the SQL3 in the query performance collection list for the collection time period T2 is associated with the SQL3 in the current query statement list for the collection time period T1 (T2.SQL3-T1.SQL3), and the SQL4 in the query performance collection list for the collection time period T2 is associated with the SQL4 in the current query statement list for the collection time period T1 (T2.SQL4-T1.SQL4).

[0045] Step S204: Draw the target performance baseline based on the performance cost data, and analyze the database performance based on the target performance baseline.

[0046] According to steps S201 to S203, firstly, activity session data of the activity sessions is collected. Then, the activity session data is grouped according to the activity session attributes. Target analysis data is determined in the group corresponding to each activity session attribute. Then, the activity session data is summarized according to a fixed collection frequency to obtain the performance cost data corresponding to the target analysis data within the collection time. Finally, the target performance baseline is drawn based on the performance cost data, and the database performance is analyzed based on the target performance baseline.

[0047] In one possible implementation, the process of drawing a target performance baseline based on performance cost data and analyzing database performance based on the target performance baseline can specifically involve: acquiring performance cost data of the target analysis data; extracting performance cost data for the same collection time period from the performance cost data; determining a first performance cost threshold corresponding to the collection time period based on the performance cost data of the collection time period; drawing a performance cost baseline based on the first performance cost threshold corresponding to each consecutive collection time period; determining the target performance baseline based on the performance cost baseline; and analyzing database performance based on the target performance baseline. Here, the collection time period is a fixed specific time period within the collection time. It can be a collection time period with the same collection duration as the fixed collection frequency. For example, if collection is performed at a fixed collection frequency of once every hour within 24 hours, the collection time period could be 6:00-7:00, 7:00-8:00, etc. Alternatively, the collection time period can also be a specific time period with the same duration as the collection time. For example, if the current collection starts at 6:00 and the collection time is 1 hour, then the collection time period could be 6:00-7:00. The performance cost data for the same collection time period is the highest performance cost data found in all historical performance cost data. The data collection period includes performance cost data; the performance cost baseline describes the maximum database performance consumption of historical active session data within each collection period. The performance cost baseline can be the session data performance baseline or the cost baseline for a single query execution. The first performance cost threshold describes the maximum database performance consumption within a certain collection period and is used to plot the performance cost baseline; the target performance baseline describes the maximum consumption of current active session data within each collection period and corresponds to the performance cost baseline. The target performance baseline can be the active session performance peak line or the query statement performance decay line.

[0048] Specifically, when the target analysis data for a given collection period is aggregated, the performance cost data of the most recently aggregated target analysis data is retrieved. This performance cost data includes the performance cost data corresponding to the query statement list, active session list, client list, and wait event list. Performance cost data for the same collection time period within the same collection period is extracted from this data. Then, one performance cost data point is selected from the performance cost data for that collection time period according to preset rules, and its value is determined as the first performance cost threshold corresponding to that collection time period. A session data performance baseline is drawn based on the first performance cost thresholds for each different and consecutive collection time period. Then, an active session performance peak line is determined based on the session data performance baseline, and 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 the active session performance peak line provided in an embodiment of this disclosure, wherein the session data performance baseline in the figure is the 90th percentile of historical active sessions (…). Figure 4 (The dashed line in the middle) determines the performance peak line based on the 90th percentile of historical active sessions. Figure 4 (The solid line in the image) 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. 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.

[0049] Alternatively, when the target analysis data for a given collection period is aggregated, the single-execution cost data for each query in the query statement list of the most recently aggregated target analysis data is retrieved. For any given query, the single-execution cost data for queries within the same collection time period is extracted from the single-execution cost data. From this single-execution cost data, one single-execution cost data point is selected according to a preset rule, and its value is determined as the first performance cost threshold for that query within the collection time period. When the query appears for at least m days (n>m) out of the n days in the query statement list for acquiring the target analysis data, a single-execution cost baseline for the query is drawn based on the first performance cost threshold for the query within each consecutive collection time period. Then, the query performance degradation line is determined based on the single-execution cost baseline, and database performance is analyzed based on the query performance degradation line. (Refer to...) Figure 5 , Figure 5 This is an optional schematic diagram of the query performance degradation line provided in the embodiments of this disclosure. The baseline of the single execution cost of the query statement in the figure is the 90th percentile of the historical single logical read of the query statement list. The query performance degradation line is determined based on the 90th percentile of the historical single logical read of the query statement list. When the single logical read cost (average cost of reading rows per session) of the query statement list of the current active session is greater than the query performance degradation line, the period exceeding the query performance degradation line is the query performance degradation period.

[0050] By plotting the performance peaks of active sessions and the performance degradation lines of query statements, a clear baseline can be provided for database performance analysis, effectively improving the efficiency of database performance analysis.

[0051] In one possible implementation, the process of determining the first performance cost threshold corresponding to each collection time period based on the performance cost data of each collection time period can be specifically as follows: The performance cost data for each collection time period can be sorted in ascending order. Based on the product of a preset ratio and the number of performance cost data entries, the performance cost data with the sequence code corresponding to the product value can be selected from the sorted performance cost data. This performance cost data with the sequence code corresponding to the product value is then determined as the first performance cost threshold corresponding to the collection time period. Here, the sequence code is a position identifier assigned to each data element after a set of data is arranged in a certain order. For example, a set of data arranged in ascending order could be 1, 3, 8, 10, 16, 22. Then, the sequence code for data element 1 is 1, the sequence code for data element 3 is 2, and the sequence code for data element 8 is 3.

[0052] Specifically, the performance cost data for each collection time period is arranged in ascending order. Assuming a portion of the performance cost data from these collection time periods is normal, this proportion is set as a preset proportion. For example, if 90% of the performance cost data for a collection time period is considered normal, then 90% is set as the preset proportion. The preset proportion is multiplied by the number of performance cost data entries to obtain the product value. When the product value is a positive integer, the performance cost data corresponding to the sequence code of the product value is selected from the sorted performance cost data. This value is determined as the first performance cost threshold for the collection time period. For example, if the performance cost data is arranged in ascending order 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 value is a floating-point number, extract the performance cost data corresponding to the two sequence codes adjacent to the sequence code of the product value from the sorted performance cost data. Perform linear interpolation based on these two adjacent performance cost data, and use the result of the linear interpolation as the first performance cost threshold corresponding to the collection period. For example, if the performance cost data is arranged in ascending order as 8, 15, 18, 27, 31, 38, 47, 50, the calculated product value is 7.2. Perform interpolation on 47 and 50, and use the interpolation result as the first performance cost threshold. Alternatively, assign weights to these two adjacent sequence codes based on the distance from the product value to the two sequence codes adjacent to the product value. Calculate the performance cost based on the weights and the performance cost data corresponding to the adjacent sequence codes, and use the calculated result as the first performance cost threshold corresponding to the collection period. Continuing the previous 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. Assign weights to data element 47 based on these distances. Assign weights to data element 50. According to 47 +50 Perform the calculation and use the result as the first performance cost threshold.

[0053] In one possible implementation, when determining the target performance baseline based on the performance cost baseline and analyzing database performance based on the target performance baseline, the specific steps could be as follows: First, determine the target performance baseline by acquiring the performance cost data of currently active sessions. Then, compare the performance cost data of currently active sessions within the acquisition period with a second performance cost threshold of the target performance baseline. Analyze database performance based on the comparison result. The comparison result indicates either stable or abnormal database performance. The second performance cost threshold describes the maximum performance cost data of currently active sessions within a certain acquisition period and is used to draw the target performance baseline.

[0054] Specifically, a target performance baseline is determined based on the performance cost baseline. When the target performance baseline is the peak line of active session performance, the performance cost data of currently active sessions within each collection period is compared with the second performance cost threshold of the active session performance peak line. When the performance cost data of currently active sessions exceeds the second performance cost threshold of the active session performance peak line, the comparison result indicates that the database is under high load at the current collection time. At this time, the performance cost data corresponding to the query statement list, active session list, client list, and wait event list summarized within the current collection period are obtained. By analyzing the performance cost data corresponding to the query statement list within the collection period, the query statements causing database performance problems can be identified; by analyzing the performance cost data corresponding to the active session list within the collection period, the active sessions causing database performance problems can be located; by analyzing the performance cost data corresponding to the wait event list within the collection period, the wait events or resources containing the query statements causing database performance problems can be located; and by analyzing the performance cost data corresponding to the client list within the collection period, the client ports causing database performance problems can be located. By comparing and analyzing the performance peaks of active sessions with the performance cost data of the current active session, when the database is under high load, the time period in which the database performance problem occurs can be quickly located. Within that time period, the query statements, wait events, resources consumed, and the client causing the problem can be identified, effectively improving the efficiency of handling database performance problems.

[0055] Reference Figure 6 , Figure 6This is an optional schematic diagram illustrating database performance trends provided in this embodiment of the disclosure. The dashed line in the diagram represents the performance peak line of active sessions. The performance cost of active sessions during the time period T4 to T5 exceeds the performance peak line of active sessions. Therefore, the analysis is performed on the T4 to T5 time period. A list of query statements is displayed below. Selecting the top 10 query statements in terms of performance consumption, the query statement with the highest performance consumption is SQL20101010101 from instance A, accounting for 20% of the performance cost. The second highest performance consumption is SQL20101010102 from instance A, accounting for 18% of the performance cost. The third highest performance consumption is SQL20101010103 from instance A, accounting for 10% of the performance cost.

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

[0057] Reference Figure 7 , Figure 7 This is another optional schematic diagram illustrating database performance trends provided by an embodiment of this disclosure. The diagram shows the performance data of the query statement SQL20101010101 in instance A. The dashed line in the performance cost baseline chart of the performance data is the query statement performance decay line. Below, the trend of the number of blocks consumed per execution of the query statement SQL20101010101 is shown. It shows that within the time interval T0-T3, the query statement SQL20101010101 was executed a total of 7 times, and the cost of these 7 executions did not exceed the query statement performance decay line. Therefore, within the time interval T0-T3, the query statement SQL20101010101 did not experience any performance problems.

[0058] In one possible implementation, the process of determining the target performance baseline based on the performance cost baseline can specifically involve: for any acquisition time period corresponding to the performance cost baseline, a first performance cost threshold is defined; a first floating value is determined by multiplying the first performance cost threshold by a floating coefficient; a second floating value is determined by summing the first performance cost threshold by a floating parameter; the larger of the first and second floating values ​​is selected as the second performance cost threshold corresponding to the acquisition time; and the target performance baseline is determined based on the second performance cost thresholds corresponding to each consecutive acquisition time. Specifically, for any acquisition time, the first performance cost threshold is multiplied by the floating coefficient to obtain the first floating value, where the floating coefficient is the sum of a fixed parameter and a floating ratio, for example, floating coefficient = 100 + floating ratio. Then, the second floating value is determined based on the sum of the first performance cost threshold and the floating parameter. 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 acquisition 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 acquisition time period. The target performance baseline is determined based on the second performance cost threshold corresponding to each time-continuous acquisition period.

[0059] Based on the above description, the method for determining the second performance cost threshold within the data collection period can be represented by the following MAX(·) function, where p is the first performance cost threshold. For floating ratios, (100+) ) is the floating coefficient. This is a floating parameter, which can be a preset fixed value or adjusted according to actual conditions. 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.

[0060]

[0061] In one possible implementation, the intelligent database performance analysis method of this disclosure can be applied to multi-database management and multi-database performance analysis, referring to... Figure 8 , Figure 8 This disclosure provides an optional overall framework for the intelligent database performance analysis method. The principles of the intelligent database performance analysis method in this disclosure are described in general below:

[0062] The intelligent database performance analysis method of this 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 the 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 database includes DM, PostgreSQL, openGauss, MogDB, VastBase, and MySQL. The intelligent database performance analysis method provided by this disclosure is based on these six databases to achieve multi-database performance analysis. In addition, the intelligent database performance analysis method provided by this disclosure can also analyze and manage the performance of other types of databases.

[0063] First, a corresponding data collection query statement is configured for each database and stored in the database. The data management module retrieves the data collection query statement from the database and sends it to the data collection module. The data collection module then collects activity session data from the corresponding database based on the query statement. During the collection of activity session data, different collection schemes are adopted according to the different attributes of the database. When the database itself has a view to store activity session data, it can query the database for the most recent 30 seconds of activity session data every 30 seconds, i.e., data collection is performed every 30 seconds to obtain the activity session data. When the database itself does not have a view to store activity session data, it can query the database for activity session data every 1 second, and then perform data collection every 30 seconds to obtain the activity session data. The activity session data includes activity session attributes such as the activity session identifier of the currently active activity session, client information, query statement identifier, wait type, and wait events.

[0064] Next, in the data collection module, the active session data is grouped according to the active session attributes. This includes query statement data grouped by query statement identifier, active session data grouped by active session identifier, client data grouped by client information, and wait event data grouped by wait event information. Then, the data is iterated through for each of these categories to obtain the number of wait-type data entries. Based on the wait-type data, the target analysis data is determined. According to the active session attributes used for grouping, the target analysis data includes a query statement list, an active session list, a client list, and a wait event list.

[0065] Next, the performance cost data for each query statement in the query statement list is obtained in the data collection module. Since the performance cost data for query statements is stored as cumulative values ​​in the database, the incremental values ​​between adjacent time periods need to be calculated during statistical analysis. Therefore, the collection of query statement performance cost data requires temporal continuity. Specifically, the performance cost data for each first query statement in the query statement list is obtained based on a fixed collection frequency. Then, the cumulative performance cost data for each second query statement in the query statement performance collection list within the same collection time period as the current collection time is collected from the production database. During the collection process, each first query statement entering the query statement list requires two performance data collections to ensure the accuracy of the performance cost data for 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 correlated with the performance cost data of the first query statement that matches the second query statement in the query statement performance collection list of the next collection time period. The performance increment change between the performance cost data of the second query statement and the corresponding performance cost data of the first query statement is analyzed. This performance increment is used as the performance data increment of the second query statement to ensure that the performance data increment of each query statement list can be collected after a 30-second delay, and the performance data increment is persisted.

[0066] Next, the target analysis data is summarized in the data collection module. The summarization levels are 10 minutes, 1 hour, and 24 hours, with corresponding frequencies of 10 minutes, 1 hour, and 24 hours. Specifically, the 10-minute summary data is obtained by continuously collecting data at a frequency of once every 30 seconds for 10 minutes; the 1-hour summary data is obtained by continuously collecting data at a fixed frequency of once every 10 minutes for 1 hour; and the 24-hour summary data is obtained by continuously collecting data at a fixed frequency of once every 60 minutes for 24 hours. The performance cost data obtained from these three summary levels includes a list of query statements, a list of active sessions, a list of clients, a list of wait events, and the performance cost data corresponding to the query statements.

[0067] The above summary aims to reduce the amount of data stored. The storage time for data collected every 30 seconds can be 7 days, for data collected every 10 minutes can be 30 days, for data collected every hour can be 90 days, and for data collected every 24 hours can be 365 days. By setting different storage times for different frequencies, it is possible to trace back to data from a longer period of time with minimal storage space.

[0068] Next, active session data is acquired, and it is assumed that 90% of the active session business is in a normal state, while the remaining portion may have performance issues requiring attention. Based on this, an active session performance baseline is drawn. The active session performance baseline can be the 90th percentile of the performance cost data of the most recent (e.g., 60 days) active sessions within the same time period (e.g., hourly). Then, a value is added up to the active session performance baseline to obtain the active session performance peak line. Whenever hourly data is aggregated, the active session performance peak line is drawn, and the performance peak line corresponding to each participating instance is recorded. When a performance peak period occurs, it can be determined that the database is under high load. Target analysis data corresponding to the performance peak period is collected to analyze the causes of database performance issues, and the target analysis data corresponding to the performance peak period is stored in a designated directory of the database through the data management module for subsequent analysis.

[0069] Next, the single execution cost of the queries in the query statement list from the active session data is obtained. Assuming that the queries run normally 90% of the time, the query statement list is analyzed within the same collection time period (e.g., the most recent 60 days) and the current collection time (e.g., the most recent hour). It is confirmed that the queries in the list appear at least 7 days out of the last 60 days. Based on this, a baseline for the single execution cost of the queries is drawn. Then, a value is added upwards to this baseline to obtain the query performance degradation curve. Whenever the performance cost data corresponding to the query statement list at the hourly level is summarized, the query performance degradation curve is drawn, and the performance degradation curve for each query in the query statement list is recorded for each instance. Subsequent analysis can be conducted based on the query performance degradation curve to determine if the query is experiencing performance degradation.

[0070] The intelligent database performance analysis method provided in this disclosure configures different collection query statements for each database, groups active session data based on active session attributes, summarizes target analysis data, plots 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. This achieves multi-database performance analysis and multi-database joint management, and provides support for data from multiple databases. Furthermore, the method for analyzing the current database performance based on active session performance peak lines and query statement performance decay lines supports second-level database query statement performance analysis and second-level detection and removal, thereby improving the efficiency of analyzing database performance and resolving database performance problems, and further enhancing database security.

[0071] The terms “first,” “second,” “third,” “fourth,” etc. (if present) in this disclosure and the foregoing drawings are used to distinguish similar objects and are not necessarily used to describe a particular order or sequence. It should be understood that such data can be interchanged where appropriate to describe embodiments of this disclosure, for example, those that can be implemented in orders other than those illustrated or described herein. Furthermore, the terms “comprising” and “having,” and any variations thereof, are intended to cover a non-exclusive inclusion; for example, a process, method, system, product, or apparatus that comprises a series of steps or units is not necessarily limited to those steps or units explicitly listed, but may include other steps or units not explicitly listed or inherent to such processes, methods, products, or apparatuses.

[0072] It should be understood that in this disclosure, "at least one item" means one or more, and "more than one" means two or more. "And / or" is used to describe the relationship between related objects, indicating that three relationships can exist. For example, "A and / or B" can represent three cases: only A exists, only B exists, and both A and B exist simultaneously, where A and B can be singular or plural. The character " / " generally indicates that the preceding and following related objects are in an "or" relationship. "At least one of the following" or similar expressions refer 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 represent: a, b, c, "a and b", "a and c", "b and c", or "a and b and c", where a, b, and c can be single or multiple.

[0073] It should be understood that in the description of the embodiments of this disclosure, "multiple" means two or more, "greater than", "less than", "exceeding" etc. are understood to exclude the number itself, and "above", "below", "within" etc. are understood to include the number itself.

[0074] In the several embodiments provided in this disclosure, it should be understood that the disclosed systems, apparatuses, and methods can be implemented in other ways. For example, the apparatus embodiments described above are merely illustrative; for instance, the division of units is only a logical functional division, and in actual implementation, there may be other division methods. For example, multiple units or components may be combined or integrated into another system, or some features may be ignored or not executed. Furthermore, the coupling or direct coupling or communication connection shown or discussed may be through some interfaces, indirect coupling or communication connection between apparatuses or units, and may be electrical, mechanical, or other forms.

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

[0076] Furthermore, the functional units in the various embodiments of this disclosure can be integrated into one processing unit, or each unit can exist physically separately, or two or more units can be integrated into one unit. The integrated unit can be implemented in hardware or as a software functional unit.

[0077] If the integrated unit is implemented as 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 this disclosure, in essence, 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. This computer software product is stored in a storage medium and includes several instructions to cause a computer device (which may be a personal computer, server, or network device, etc.) to execute all or part of the steps of the methods of the various embodiments of this disclosure. The aforementioned storage medium includes various media capable of storing program code, such as USB flash drives, portable hard drives, read-only memory (ROM), random access memory (RAM), magnetic disks, or optical disks.

[0078] It should also be understood that the various implementation methods provided in this disclosure can be combined arbitrarily to achieve different technical effects.

[0079] The above is a detailed description of the preferred embodiments of this disclosure. However, this disclosure is not limited to the above embodiments. Those skilled in the art can make various equivalent modifications or substitutions without departing from the spirit of this disclosure. All such equivalent modifications or substitutions are included within the scope defined by the claims of this disclosure.

Claims

1. An intelligent analysis method for database performance, characterized in that, Applied to a target database, which comprises multiple databases, including: Based on the collection query statement, activity session data of active sessions is collected from the target database, wherein the activity session data includes at least one activity session attribute; The activity session data is grouped according to the activity session attributes, and the target analysis data is determined from the grouping results corresponding to each activity session attribute. The target analysis data is aggregated at a fixed collection frequency to obtain the performance cost data corresponding to the target analysis data within the collection time, wherein the collection time is the duration of one aggregation of the target analysis data at the fixed collection frequency; A performance cost baseline is plotted based on the performance cost data. For any collection time period of the performance cost baseline, a first performance cost threshold is determined by multiplying the first performance cost threshold by a floating coefficient. A second floating value is determined by summing the first performance cost threshold and the floating parameter. The maximum value between the first and second floating values ​​is selected as the second performance cost threshold corresponding to the collection time period. A target performance baseline is determined based on the second performance cost thresholds corresponding to each collection time period that is consecutive in time. Database performance is analyzed based on the target performance baseline. The target performance baseline includes an active session performance peak line and a query statement performance decay line. The active session performance peak line is used to analyze the performance cost data corresponding to the active session. The query statement performance decay line is used to analyze the single execution cost data of the query statements used during the active session. The collection time period is a fixed specific time period within the collection time.

2. The intelligent database performance analysis method according to claim 1, characterized in that, Before analyzing database performance based on the target performance baseline, the intelligent database performance analysis method further includes: The performance cost data of the target analysis data is obtained, and the performance cost data of the same collection time period in the collection time is extracted from the performance cost data. Based on the performance cost data of the collection time period, a first performance cost threshold corresponding to the collection time period is determined. A performance cost baseline is drawn based on the first performance cost threshold corresponding to each consecutive collection time period. The target performance baseline is determined based on the performance cost baseline, and the database performance is analyzed based on the target performance baseline.

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

4. The intelligent database performance analysis method according to claim 2, characterized in that, The step of determining a target performance baseline based on the performance cost baseline and analyzing database performance based on the target performance baseline includes: The target performance baseline is determined based on the performance cost baseline; Obtain the performance cost data of the currently active session, compare the performance cost data of the currently active session within the collection time period with the second performance cost threshold of the target performance baseline, and analyze the database performance based on 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 for database performance according to claim 1, characterized in that, The activity session data includes waiting type data. The step of grouping the activity session data according to the activity session attributes and determining the target analysis data from the grouping results corresponding to each activity session attribute includes: The activity session data is grouped according to the activity session attributes to obtain the data to be analyzed corresponding to each activity session attribute. The data to be analyzed is looped through each of the activity session attributes to obtain the number of data entries of the waiting type data in the data to be analyzed, and the target analysis data is determined based on the waiting type data.

6. The intelligent database performance analysis method according to claim 1, characterized in that, The process of summarizing the target analysis data at a fixed collection frequency to obtain performance cost data corresponding to the target analysis data within the collection period includes: The target analysis data is collected at the fixed collection frequency, and the target analysis data is summarized in the order of collection time to obtain the performance cost data corresponding to the target analysis data within the collection time.

7. The intelligent database performance analysis method according to claim 6, characterized in that, The target analysis data includes a list of query statements, and the intelligent database performance analysis method further includes: Obtain a performance collection list of query statements with continuous time, and obtain the performance cost data of each first query statement in the performance collection list of query statements within each continuous collection time period. 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 based on the data collection results. Analyze the incremental performance changes between the performance cost data of the first query statement and the performance cost data of the second query statement to obtain the performance data increment of the second query statement.

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

Images