Computer systems and methods for predictive performance management of data transactions
View Patent ↗Systems and methods are disclosed for monitoring and managing data transactions, such as SQL transactions. In certain examples, a management subsystem generates an alert identifying degrading database transactions to facilitate preventative tuning or other maintenance. In particular, a monitor module tracks performance measurements (e.g., logical reads) of select transactions. A modeler correlates the performance measurements and assigns first performance model(s) to represent the performance measurements and predicted performance measurements of a particular transaction. A trend change module detects a significant change in a trend and/or variance of the performance measurements and can cause the modeler module to generate a second performance model to represent at least a portion of the performance measurements and the predicted performance measurements of the particular transaction. An interface module triggers an alert when the second performance model indicates that the predicted performance measurements of the particular transaction are degrading at or above a threshold rate.
1. A system for managing performance of database transactions, the system comprising:
at least one database configured to store data;
at least one application configured to interact with the data of the at least one database through a plurality of database transactions that comprise structured query language (SQL) statements; and
a management subsystem configured to generate one or more alerts identifying degrading ones of the SQL statements, the management subsystem configured to execute on one or more computing devices and comprising,
a monitor module comprising one or more computer hardware processors, the monitor module configured to track one or more performance measurements of the plurality of the SQL statements, the one or more performance measurements comprising at least one of (i) a total cost of execution of a particular SQL statement of the plurality of SQL statements, (ii) an average cost of all executions of the particular SQL statement, and (iii) an execution rate of the particular SQL statement,
a modeler module comprising one or more computer hardware processors, the modeler module configured to correlate the one or more tracked performance measurements beginning at a first data point of the particular SQL statement with one or more independent variables and to assign at least a first performance model to represent the one or more tracked performance measurements to generate a first trend of predicted performance measurements of the particular SQL statement,
a trend change module configured to automatically detect within the one or more tracked performance measurements at least one substantial trend change in a the first trend of the one or more tracked performance measurements,
wherein the trend change module automatically detects a trend change by iteratively selecting a second data point for the modeler module to generate at least a second performance model for a second trend of predicted performance measurements beginning at the second data point, wherein the trend change module determines a trend change at the second data point when:
1) at least three measured data points prior to the second data point fall outside of the second trend, and
2) a comparison of the first trend and the second trend of predicted performance measurements with tracked performance measurements indicates that the second trend is significantly better than the first trend at predicting the tracked performance measurements, and
an interface module configured to trigger an alert when the second trend of the second performance model indicates that the predicted performance measurements of the particular SQL statement are degrading at or above a threshold rate to identify whether the particular SQL statement needs to be tuned;
wherein the second performance model indicates whether I/O channels associated with the execution of the plurality of SQL statements will near capacity in the future.
2. The system of claim 1 , wherein the one or more first performance models and the second performance model are selected from a set of predetermined performance models.
3. The system of claim 2 , wherein the management subsystem further comprises a memory configured to store the set of predetermined performance models.
4. A system for managing performance of database transactions, the system comprising:
at least one database configured to store data;
at least one application configured to interact with the data of the at least one database through a plurality of database transactions that comprise structured query language (SQL) statements; and
a management subsystem configured to generate one or more alerts identifying degrading ones of the SQL statements, the management subsystem configured to execute on one or more computing devices and comprising,
a monitor module comprising one or more computer hardware processors, the monitor module configured to track one or more performance measurements of the plurality of the SQL statements, the one or more performance measurements comprising at least one of (i) a total cost of execution of a particular SQL statement of the plurality of SQL statements, (ii) an average cost of all executions of the particular SQL statement, and (iii) an execution rate of the particular SQL statement,
a modeler module comprising one or more computer hardware processors, the modeler module configured to correlate the one or more tracked performance measurements beginning at a first data point of the particular SQL statement with one or more independent variables and to assign at least a first performance model to represent the one or more tracked performance measurements to generate a first trend of and predicted performance measurements of the particular SQL statement,
a trend change module configured to automatically detect within the one or more tracked performance measurements at least one substantial trend change in a the first trend of the one or more tracked performance measurements,
wherein the trend change module automatically detects a trend change by iteratively selecting a second data point for the modeler module to generate at least a second performance model for a second trend of predicted performance measurements beginning at the second data point, wherein the trend change module determines a trend change at the second data point when:
1) at least three measured data points prior to the second data point fall outside of the second trend, and
2) a comparison of the first trend and the second trend of predicted performance measurements with tracked performance measurements indicates that the second trend is significantly better than the first trend at predicting the tracked performance measurements, and
an interface module configured to trigger an alert when the second trend of the second performance model indicates that the predicted performance measurements of the particular SQL statement are degrading at or above a threshold rate to identify whether the particular SQL statement needs to be tuned;
wherein the one or more first performance models and the second performance model are selected from a set of predetermined performance models;
wherein the management subsystem further comprises a memory configured to store the set of predetermined performance models; and
wherein the set of predetermined performance models comprises logarithmic, linear and exponential curves.
5. The system of claim 1 , wherein the threshold rate comprises an exponential rate.
6. A method for managing database performance, the method comprising:
monitoring with one or more computer hardware processors, performance values of a plurality of database transactions that comprise structured query language, (SQL) statements;
storing the performance values in a storage device;
correlating with one or more computer hardware processors, the performance values of at least one of the SQL statements with one or more independent variables;
assigning at least a first performance model to represent past and predicted performance of the at least one SQL statement;
detecting among the correlated one or more performance values a first performance value representing a change in a first trend of performance of the at least one SQL statement;
dividing at the first performance value the correlated one or more performance values into a plurality of segments;
generating at least a second performance model to represent at least one of the plurality of segments, the at least the second performance model indicating a trend change in the first trend that determines a second trend of predicted performance of the at least one SQL statement;
automatically detecting the trend change by iteratively selecting a second data point wherein:
1) at least three data points prior to the second data point fall outside of the second trend, and
2) the second performance model is significantly better than the first performance module at predicting the tracked performance measurements over the second trend; and
issuing an alert when the second performance model indicates that the predicted second trend of performance of the at least one SQL statement is degrading at or above a threshold rate to identify whether the particular SQL statement needs to be tuned;
wherein the second performance model indicates whether I/O channels associated with the execution of the plurality of SQL statements will near capacity in the future.
7. The method of claim 6 , wherein the one or more independent variables comprises a time variable.
8. The method of claim 6 , further comprising associating a prediction interval with the second performance model.
9. The method of claim 8 , wherein said detecting further comprises identifying a predetermined number of data values immediately preceding the first performance value that reside outside the prediction interval.
10. A method for managing database performance, the method comprising:
monitoring with one or more computer hardware processors, performance values of a plurality of database transactions that comprise structured query language (SQL) statements;
storing the performance values in a storage device;
correlating with one or more computer hardware processors, the performance values of at least one of the SQL statements with one or more independent variables;
assigning at least a first performance model to represent past and predicted performance of the at least one SQL statement;
detecting among the correlated one or more performance values a first performance value representing a change in a first trend of performance of the at least one SQL statement;
wherein said detecting further comprises identifying a predetermined number of data values immediately preceding the first performance value that reside outside the prediction interval;
dividing at the first performance value the correlated one or more performance values into a plurality of segments;
generating at least a second performance model to represent at least one of the plurality of segments, the at least the second performance model indicating a trend change in the first trend that determines a second trend of predicted performance of the at least one SQL statement;
automatically detecting the trend change by iteratively selecting a second data point wherein:
1) at least three data points prior to the second data point fall outside of the second trend, and
2) the second performance model is significantly better than the first performance module at predicting the tracked performance measurements over the second trend; and
issuing an alert when the second performance model indicates that the predicted second trend of performance of the at least one SQL statement is degrading at or above a threshold rate to identify whether the particular SQL statement needs to be tuned;
associating a prediction interval with the second performance model; and
calculating the prediction interval such that a probability that a selected performance value resides outside the prediction interval is at most approximately five percent.
11. The method of claim 9 , further comprising displaying on a user interface at least a portion of the correlated one or more performance values, the second performance model and a trend change identifier at the first performance value.
12. The method of claim 6 , further comprising comparing the second performance model with the first performance model to determine if the second performance model represents a statistical improvement over the first performance model in representing the past and predicted performance of the at least one database transaction.
13. The method of claim 6 , further comprising automatically issuing the alert when the second performance model comprises an exponential curve.