Method for EN passant workload shift detection

Patent metadata

Field	Value
Publication number	US-9852142-B2
Application number	US-201615040009-A
Country	US
Kind code	B2
Filing date	Feb 10, 2016
Priority date	Feb 19, 2015
Publication date	Dec 26, 2017
Grant date	Dec 26, 2017

Abstract

Embodiments of the present invention provide systems, methods, and computer program products for detecting shifts in types of workloads handled by a relational database management system. Embodiments of the present invention can afford relational database administrators with the ability to leverage information pertinent to the current type of workload being handled by the relational database management system. Furthermore, embodiments of the present invention provide relational database administrators with information in regard to system workload states and workload transitions.

First claim

What is claimed is:

1. A method for detecting workload changes processed by a database management system, the method comprising:
monitoring, by one or more computer processors, a plurality of workloads processed by the database management system in a first monitoring interval and a second monitoring interval, wherein monitoring the plurality of workloads comprises:
retrieving, by the one or more computer processors, one or more access plans for the first and the second monitoring intervals from a statement cache;
processing, by the one or more computer processors, the one or more access plans for the first and the second monitoring intervals to create one or more access plan hash codes, wherein the one or more access plan hash codes are hash codes of the one or more access plans for the first and the second monitoring intervals, wherein the access plan hash codes reduce workload;

detecting, by the one or more computer processors, a change in a type of workload for the first and second monitoring interval;
responsive to detecting the change in the type of workload for the first and second monitoring interval, identifying, by the one or more computer processors, the type of workload in the second monitoring interval based, at least in part, on one or more time invariant metrics; and
updating, by the one or more computer processors, an entry in a library to include information pertinent to the identified type of workload.

2. The method of claim 1 , further comprising:
retrieving, by the one or more computer processors, access plan costs for a set of statements associated with the type of workload for the first monitoring interval from the statement cache;
retrieving, by the one or more computer processors, access plan costs for a set of statements associated with the type of workload for the second monitoring interval from the statement cache;
estimating, by the one or more computer processors, an overall abstract cost for the first monitoring interval using the one or more access plan costs for the first monitoring interval; and
estimating, by the one or more computer processors, an overall abstract cost for the second monitoring interval using the one or more access plan costs for the second monitoring interval.

3. The method of claim 2 , wherein detecting the change in the type of workload for the first and second monitoring interval comprises:
calculating, by the one or more computer processors, a similarity coefficient that indicates a degree of similarity between the types of workloads in the first and second monitoring intervals;
determining, by the one or more computer processors, whether the similarity coefficient satisfies a first set of conditions; and
responsive to determining that the similarity coefficient satisfies the first set of conditions, detecting, by the one or more computer processors, a type of workload in the second monitoring interval, and storing the one or more access plan hash codes and the overall abstract cost associated with the detected type of workload in the second monitoring interval.

4. The method of claim 2 , wherein detecting the change in the type of workload for the first and second monitoring interval comprises:
calculating, by the one or more computer processors, a similarity coefficient that indicates a degree of similarity between the types of workloads in the first and second monitoring intervals;
calculating, by the one or more computer processors, a cost difference in percent that indicates a difference in percent between computational activity for the first monitoring interval and computational activity for the second monitoring interval;
determining, by the one or more computer processors, whether the similarity coefficient satisfies a second set of conditions;
responsive to determining that the similarity coefficient satisfies the second set of conditions, determining, by the one or more computer processors, whether the cost difference in percent satisfies a third set of conditions; and
responsive to determining that the cost difference in percent satisfies the third set of conditions, detecting, by the one or more computer processors, a type of workload in the second monitoring interval, and storing the one or more access plan hash codes and the overall abstract cost associated with the detected type of workload in the second monitoring interval.

5. The method of claim 3 , wherein the calculated similarity coefficient is based, at least in part, on one or more access plan hash codes for the first monitoring interval and one or more access plan hash codes for the second monitoring interval.

6. The method of claim 4 , wherein responsive to detecting the change in the type of workload for the first and second monitoring interval, identifying the type of workload in the second monitoring interval comprises:
retrieving, by the one or more computer processors, a previously identified type of workload, one or more access plan hash codes for the previously identified type of workload, and an overall abstract cost for the previously identified type of workload;
calculating, by the one or more computer processors, a cost difference in percent using the overall abstract cost for the previously identified type of workload and using the overall abstract cost associated with the detected type of workload in the second monitoring interval;
calculating, by the one or more computer processors, a similarity coefficient for the previously identified type of workload and the detected type of workload in the second monitoring interval;
determining, by the one or more computer processors, whether the similarity coefficient for the previously identified type of workload and the detected type of workload in the second monitoring interval satisfies a fourth set of conditions;
determining, by the one or more computer processors, whether the calculated cost difference in percent satisfies a fifth set of conditions;
responsive to determining that the similarity coefficient for the previously identified type of workload and the detected type of workload in the second monitoring interval satisfies the fourth set of conditions and the calculated cost difference in percent for the previously identified type of workload, and the detected type of workload in the second monitoring interval satisfies the fifth set of conditions, identifying, by the one or more computer processors, the detected type of workload in the second monitoring interval;
determining, by the one or more computer processors, whether the cost difference in percent for the previously identified type of workload and the detected type of workload in the second monitoring interval satisfies a sixth set of conditions; and
responsive to determining that the cost difference in percent for the previously identified type of workload and the detected type of workload in the second monitoring interval satisfies the sixth set of conditions, determining, by the one or more computer processors, an increase or a decrease in computational activity between computational activity for the previously identified type of workload and computational activity for the identified type of workload.

Assignees

• IBM

Inventors

• Reichert Michael
• Steinbach Torsten
• Tschaffler Matthias

Classifications

• G06F11/3006

  where the computing system is distributed, e.g. networked systems, clusters, multiprocessor systems (multiprogramming arrangements G06F9/46; allocation of resources G06F9/50) · CPC title

• G06F16/284

  Relational databases · CPC title

• G06F16/24542

  Plan optimisation · CPC title

• G06F16/1734 Primary

  Details of monitoring file system events, e.g. by the use of hooks, filter drivers, logs · CPC title

• G06F2201/80

  Database-specific techniques · CPC title