Managing database utilities to improve throughput and concurrency

• US 8,555,288 B2
• Filed: 05/17/2006
• Issued: 10/08/2013
• Est. Priority Date: 05/17/2006
• Status: Active Grant

• Current Assignee: Teradata US, Inc. (Teradata Corporation)
• Original Assignee: Teradata US, Inc. (Teradata Corporation)
• Inventors: Brown, Douglas P., Richards, Anita, Britton, Bruce Wayne, Walter, Todd A.
• Primary Examiner(s): Puente, Emerson
• Assistant Examiner(s): Huaracha, Willy W
• Application Number:
  US11/435,523
• Publication Number:
  US 20070271570A1
• Time in Patent Office: 2,701 Days
• Field of Search: 718/102, 718/104, 718/105, 718/100
• 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 comprising:

receiving a utility to be executed by a database system, wherein the database system comprises a plurality of database system nodes and each database system node has an existing load defined by;

a number of sessions the database system node is capable of running, and

a number of sessions the database system node is running;

receiving with the utility, when it arrives for execution, a specification of a number of utility sessions to be used to execute the utility, the number being greater than one, each utility session requiring a separate logon to the database system;

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

distributing the utility sessions among the database system nodes in such a way as to balance the resulting load for each of the database system nodes wherein the resulting load for each database system node is the existing load for that database system node plus number of utility sessions distributed to that database system node.

2. The method of claim 1 wherein distributing the utility sessions comprises:

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

3. The method of claim 1 where distributing the utility sessions comprises:

distributing the utility sessions among the database system nodes, which are heterogeneous in their ability to bear a load when they are not loaded, in such a way as to balance the load among the database system nodes according to the ability of each database system node to bear the load.

4. The method of claim 1 wherein distributing the utility sessions among the database system nodes comprises:

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.

5. The method of claim 1, wherein the utility is characterized by one or more of types and phases, 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 and the phase of the utility.

6. 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.

7. 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.

8. 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.

9. A computer program, stored on a non-transitory tangible storage medium, the program including executable instructions that cause a computer to:

receive a utility to be executed by a database system, wherein the database system comprises a plurality of database system nodes and each database system node has an existing load defined by;

a number of sessions the database system node is capable of running, and

a number of sessions the database system node is running;

receive with the utility, when it arrives for execution, a specification of a number of utility sessions to be used to execute the utility, the number being greater than one, each utility session requiring a separate logon to the database system;

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

distribute the utility sessions among the database system nodes in such a way as to balance the resulting load for each of the database system nodes wherein the resulting load for each database system node is the existing load for that database system node plus number of utility sessions distributed to that database system node.

10. The computer program of claim 9 where, when distributing the utility sessions, the computer:

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

11. The computer program of claim 9 where, when distributing the utility sessions, the computer:

distributes the utility sessions among the database system nodes, which are heterogeneous in their ability to bear a load when they are not loaded, in such a way as to balance the load among the database system nodes according to the ability of each database system node to bear the load.

12. The computer program of claim 9 where, when distributing the utility sessions among the database system nodes, 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.

13. The computer program of claim 9, wherein the utility is characterized by one or more of types and phases, 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 and the phase of the utility.

14. The computer program of claim 9, 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.

15. The computer program of claim 9, 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.

16. The computer program of claim 9, 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.

17. A system including:

a massively parallel processing system including;

one or more parallel processing system nodes;

a plurality of CPUs, each of the one or more parallel processing system 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 comprising;

receiving a utility to be executed by a database system running on the one or more parallel processing system nodes, wherein the database system comprises a plurality of database system nodes and each database system node has an existing load defined by;

a number of sessions the database system node is capable of running, and

a number of sessions the database system node is running;

receiving with the utility, when it arrives for execution, a specification of a number of utility sessions to be used to execute the utility, the number being greater than one, each utility session requiring a separate logon to the database system;

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

distributing the utility sessions among the database system nodes in such a way as to balance the resulting load for each of the database system nodes wherein the resulting load for each database system node is the existing load for that database system node plus number of utility sessions distributed to that database system node.

18. The system of claim 17 wherein distributing the utility sessions comprises:

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

19. The system of claim 17 where distributing the utility sessions comprises:

distributing the utility sessions among the database system nodes, which are heterogeneous in their ability to bear a load when they are not loaded, in such a way as to balance the load among the database system nodes according to the ability of each database system node to bear the load.

20. The system of claim 17 wherein distributing the utility sessions among the database system nodes comprises:

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.

21. The system of claim 17, wherein the utility is characterized by one or more of types and phases, 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 and the phase of the utility.

22. The system of claim 17, 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.

23. The system of claim 17, wherein the utility is divided into a plurality of phases, the method including:

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

24. The system of claim 17, 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.