Managing database utilities to improve throughput and concurrency

• US 20070271570A1
• Filed: 05/17/2006
• Published: 11/22/2007
• Est. Priority Date: 05/17/2006
• Status: Active Grant

• Current Assignee: Teradata US, Inc. (Teradata Corporation)
• Original Assignee: Teradata US, Inc. (Teradata Corporation)
• Inventors: Richards, Anita, Brown, Douglas P., Walter, Todd A., Britton, Bruce Wayne
• Granted Patent: US 8,555,288 B2
• Time in Patent Office: Days
• US Class Current: 718/105
• CPC Class Codes: G06F 2209/5011 Pool
• CPC Class Codes: G06F 2209/5015 Service provider selection
• CPC Class Codes: G06F 2209/5016 Session
• CPC Class Codes: G06F 2209/5017 Task decomposition
• CPC Class Codes: G06F 2209/5021 Priority
• CPC Class Codes: G06F 2209/508 Monitor
• CPC Class Codes: G06F 9/5033 considering data affinity
• CPC Class Codes: G06F 9/505 considering the load

Abstract

A method, computer program, and system are disclosed for executing a utility on a database system having a plurality of database system nodes. Each database system node has an existing load. An increased load on the database system required to execute the utility is determined. The existing load on each of the database system nodes is determined. The increased load is distributed in such a way as to balance the load among the database system nodes.

Claims

1. A method for executing a utility on a database system having a plurality of database system nodes, each database system node having an existing load, the method including:

determining an increased load on the database system required to execute the utility;

determining the existing load on each of the database system nodes; and

distributing the increased load in such a way as to balance the load among the database system nodes.

2. The method of claim 1 where the increased load is measured in sessions and where distributing the increased load includes:

assigning sessions to the database system nodes on a round robin basis.

3. The method of claim 1 where the nodes are heterogeneous in terms of their ability to bear a load and where distributing the increased load includes:

distributing the increased load among the nodes in such a way as to balance the load among the nodes according to the ability of each node to bear the load.

4. The method of claim 1 where the increased load is measured in sessions and where determining the increased load includes:

receiving with the utility, when it arrives for execution, information from which the number of sessions required to execute the utility can be determined.

6. The method of claim 1, wherein the utility is characterized by one or more of types, phases, and contents, and the method further includes:

applying a dynamic limit on the database system resources to be applied to execution of the utility with the limit varying according to one or more of the following;

the type of the utility, the phase of the utility, and the content of the utility.

7. The method of claim 1, wherein the utility is divided into tasks, one of the tasks being configured to run concurrently with other of the utility'"'"'s tasks, the one task being such that if it were assigned the resources normally assigned to the one concurrent task it would finish in much less time than the other concurrent tasks, the method including:

reducing the resources assigned to the one concurrent task so that it will finish in about the same time as the other concurrent tasks.

8. The method of claim 1, wherein the utility is divided into a plurality of phases, the method including:

overlapping execution of two or more of the plurality of phases.

9. The method of claim 1, further including:

assigning the utility to one of a plurality of workload groups, each workload group having an associated level of service desired from the database system, each workload group having one or more requests assigned to it, the utility being one of the requests;

executing the one or more requests in an order intended to achieve the levels of service associated with each of the workload groups;

assigning database system resources to the one or more workload groups as necessary to provide the level of service associated with each workload group;

monitoring on a short-term basis the execution of requests to detect a deviation from the expected level of service greater than a short-term threshold, and if such a deviation is detected;

adjusting the assignment of database system resources to workload groups to reduce the deviation;

monitoring on a long-term basis the execution of requests to detect a deviation from the expected level of service greater than a long-term threshold, and if such a deviation is detected;

adjusting the execution of requests to better provide the expected level of service.

5. The method of claim I where the increased load is measured in sessions and where determining the increased load includes:

considering one or more of the following categories of information;

(a) information included with the utility regarding the number of sessions to assign to execute the utility;

(b) configuration information concerning the database system;

(c) resource consumption information concerning the database system;

(d) capabilities of a source system that originated the utility;

(e) capabilities of a network through which data used to execute the utility will flow; and

(f) dimensions of the data to be loaded.

10. A computer program, stored on a tangible storage medium, for use in executing a utility on a database system having a plurality of database system nodes, each database system node having an existing load, the program including executable instructions that cause a computer to:

determine an increased load on the database system required to execute the utility;

determine the existing load on each of the database system nodes; and

distribute the increased load in such a way as to balance the load among the database system nodes.

11. The computer program of claim 10 where the increased load is measured in sessions and where, when distributing the increased load, the computer:

assigns sessions to the database system nodes on a round robin basis.

12. The computer program of claim 10 where the nodes are heterogeneous in terms of their ability to bear a load and where, when distributing the increased load, the computer:

distributes the increased load among the nodes in such a way as to balance the load among the nodes according to the ability of each node to bear the load.

13. The computer program of claim 10 where the increased load is measured in sessions and where, when determining the increased load, the computer:

receives with the utility, when it arrives for execution, information from which the number of sessions required to execute the utility can be determined.

14. The computer program of claim 10 where the increased load is measured in sessions and where, when determining the increased load, the computer:

considers one or more of the following categories of information;

(a) information included with the utility regarding the number of sessions to assign to execute the utility;

(b) configuration information concerning the database system;

(c) resource consumption information concerning the database system;

(d) capabilities of a source system that originated the utility;

(e) capabilities of a network through which data used to execute the utility will flow; and

(f) dimensions of the data to be loaded.

15. The computer program of claim 10, wherein the utility is characterized by one or more of types, phases, and contents, and the program further including executable instructions that cause a computer to:

apply a dynamic limit on the database system resources to be applied to execution of the utility with the limit varying according to one or more of the following;

the type of the utility, the phase of the utility, and the content of the utility.

16. The computer program of claim 10, wherein the utility is divided into tasks, one of the tasks being configured to run concurrently with other of the utility'"'"'s tasks, the one task being such that if it were assigned the resources normally assigned to the one concurrent task it would finish in much less time than the other concurrent tasks, the program further including executable instructions that cause a computer to:

reduce the resources assigned to the one concurrent task so that it will finish in about the same time as the other concurrent tasks.

17. The computer program of claim 10, wherein the utility is divided into a plurality of phases, the program further including executable instructions that cause a computer to:

overlap execution of two or more of the plurality of phases.

18. The computer program of claim 10, the program further including executable instructions that cause a computer to:

assign the utility to one of a plurality of workload groups, each workload group having an associated level of service desired from the database system, each workload group having one or more requests assigned to it, the utility being one of the requests;

execute the one or more requests in an order intended to achieve the levels of service associated with each of the workload groups;

assign database system resources to the one or more workload groups as necessary to provide the level of service associated with each workload group;

monitor on a short-term basis the execution of requests to detect a deviation from the expected level of service greater than a short-term threshold, and if such a deviation is detected;

adjust the assignment of database system resources to workload groups to reduce the deviation;

monitor on a long-term basis the execution of requests to detect a deviation from the expected level of service greater than a long-term threshold, and if such a deviation is detected;

adjust the execution of requests to better provide the expected level of service.

19. A system including:

a massively parallel processing system including;

one or more nodes;

a plurality of CPUs, each of the one or more nodes providing access to one or more CPUs;

a plurality of data storage facilities, each of the one or more CPUs providing access to one or more data storage facilities;

a process for executing a utility on a database system, each node having an existing load, the process including;

determining an increased load on the database system required to execute the utility;

determining the existing load on each of the nodes; and

distributing the increased load in such a way as to balance the load among the nodes.

20. The system of claim 19 where the increased load is measured in sessions and where distributing includes:

assigning sessions to the nodes on a round robin basis.

21. The method of claim 19 where the nodes are heterogeneous in terms of their ability to bear a load and where distributing the increased load includes:

distributing the increased load among the nodes in such a way as to balance the load among the nodes according to the ability of each node to bear the load.

22. The method of claim 19 where the increased load is measured in sessions and where determining the increased load includes:

receiving with the utility, when it arrives for execution, information from which the number of sessions required to execute the utility can be determined.

23. The method of claim 19 where the increased load is measured in sessions and where determining the increased load includes:

considering one or more of the following categories of information;

(a) information included with the utility regarding the number of sessions to assign to execute the utility;

(b) configuration information concerning the database system;

(c) resource consumption information concerning the database system;

(d) capabilities of a source system that originated the utility;

(e) capabilities of a network through which data used to execute the utility will flow; and

(f) dimensions of the data to be loaded.

24. The method of claim 19, wherein the utility is characterized by one or more of types, phases, and contents, and the method further includes:

applying a dynamic limit on the database system resources to be applied to execution of the utility with the limit varying according to one or more of the following;

the type of the utility, the phase of the utility, and the content of the utility.

25. The method of claim 19, wherein the utility is divided into tasks, one of the tasks being configured to run concurrently with other of the utility'"'"'s tasks, the one task being such that if it were assigned the resources normally assigned to the one concurrent task it would finish in much less time than the other concurrent tasks, the method including:

reducing the resources assigned to the one concurrent task so that it will finish in about the same time as the other concurrent tasks.

26. The method of claim 19, wherein the utility is divided into a plurality of phases, the method including:

overlapping execution of two or more of the plurality of phases.

27. The method of claim 19, further including:

assigning the utility to one of a plurality of workload groups, each workload group having an associated level of service desired from the database system, each workload group having one or more requests assigned to it, the utility being one of the requests;

executing the one or more requests in an order intended to achieve the levels of service associated with each of the workload groups;

assigning database system resources to the one or more workload groups as necessary to provide the level of service associated with each workload group;

monitoring on a short-term basis the execution of requests to detect a deviation from the expected level of service greater than a short-term threshold, and if such a deviation is detected;

adjusting the assignment of database system resources to workload groups to reduce the deviation;

monitoring on a long-term basis the execution of requests to detect a deviation from the expected level of service greater than a long-term threshold, and if such a deviation is detected;

adjusting the execution of requests to better provide the expected level of service.