Regulating the workload of a database system

• US 7,657,501 B1
• Filed: 08/10/2004
• Issued: 02/02/2010
• Est. Priority Date: 08/10/2004
• Status: Active Grant

• Current Assignee: Teradata US, Inc. (Teradata Corporation)
• Original Assignee: Teradata US, Inc. (Teradata Corporation)
• Inventors: Ramesh, Bhashyam, Richards, Anita, Brown, Douglas P.
• Primary Examiner(s): LE, THU NGUYET T
• Assistant Examiner(s): LE, THU NGUYET T
• Application Number:
  US10/915,609
• Time in Patent Office: 2,002 Days
• Field of Search: 707/2, 707/200
• US Class Current: 1/1
• CPC Class Codes: G06F 16/24549 Run-time optimisation
• CPC Class Codes: G06F 16/2471 Distributed queries
• CPC Class Codes: G06F 2209/508 Monitor
• CPC Class Codes: G06F 9/5083 Techniques for rebalancing the load in a distributed system
• CPC Class Codes: Y10S 707/99932 Access augmentation or optimizing

Abstract

A method, computer program and system for regulating the workload of a database system as it processes one or more requests are disclosed. The requests are grouped into workload groups and are processed by one or more of a plurality of subsystems within the database system. Each subsystem has resources. The system has resources. The subsystem resources and the system resources are allocated across the workload groups. The method, computer program and system include monitoring, at a subsystem level, the processing of the requests and adjusting the allocation of subsystem resources to workload groups to achieve a subsystem goal and monitoring, at a system level, the processing of the requests and adjusting the allocation of system resources to workload groups to achieve a system goal. The subsystems are arranged in a hierarchical network. The method includes each subsystem reporting through the hierarchy of the network the consumption level of its resources and each subsystem reporting through the hierarchy of the network the consumption level of the resources of other subsystems reporting to it according to the hierarchy.

Claims

1. A method for regulating the workload of a database system as it processes one or more requests, the requests being grouped into one or more workload groups, the requests being processed by one or more of a plurality of subsystems within the database system, each subsystem having resources, the system having resources, the subsystem resources and the system resources being allocated across the one or more workload groups, the method including:

monitoring, at a subsystem level, the processing of the requests and adjusting the allocation of subsystem resources to workload groups to achieve a subsystem goal for performance associated with the one or more workload groups; and

monitoring, at a system level, the processing of the requests and adjusting the allocation of system resources to workload groups to achieve a system goal for performance associated with the one or more workload groups.

2. The method of claim 1 where the subsystem resources and the system resources have respective levels of consumption, the subsystems are arranged in a hierarchical network, and monitoring at a system level includes:

each subsystem reporting through the hierarchy of the network the consumption level of its resources;

each subsystem reporting through the hierarchy of the network the consumption level of the resources of other subsystems reporting to it according to the hierarchy.

3. The method of claim 2 where the hierarchical network includes a tree having a root subsystem at the top of the tree, one or more leaf subsystems at the bottom of the tree, and zero or more intermediate subsystems hierarchically between the root subsystem and the one or more leaf subsystems, and where reporting through the hierarchy includes:

reporting in the direction of the root subsystem.

4. The method of claim 2 where the hierarchical network includes a tree having a root subsystem at the top of the tree, one or more leaf subsystems at the bottom of the tree, and zero or more intermediate subsystems hierarchically between the root subsystem and the one or more leaf subsystems, where each subsystem except the root subsystem has a parent subsystem immediately above it in the hierarchy and each subsystem except the leaf subsystems has one or more child subsystems immediately below it in the hierarchy and where reporting through the hierarchy includes:

each child subsystem reporting to its parent subsystem.

5. The method of claim 2 where the hierarchical network includes a binary tree.

6. The method of claim 2 where the hierarchical network includes a tree having a root subsystem at the top of the tree, one or more leaf subsystems at the bottom of the tree, and zero or more intermediate subsystems hierarchically between the root subsystem and the one or more leaf subsystems, where each subsystem except the root subsystem has a parent subsystem immediately above it in the hierarchy and each subsystem except the leaf subsystems has one or more child subsystems immediately below it in the hierarchy and where each subsystem reporting through the hierarchy of the network the consumption level of the resources of other subsystems reporting to it according to the hierarchy includes:

each child subsystem reporting its resource consumption level to its parent subsystem; and

each parent subsystem compiling its resource consumption level to include the resource consumption levels of its children subsystems.

7. A computer program, stored on a tangible storage medium, for regulating the workload of a database system as it processes one or more requests, the requests being grouped into one or more workload groups, the requests being processed by one or more of a plurality of subsystems within the database system, each subsystem having resources, the system having resources, the subsystem resources and the system resources being allocated across the one or more workload groups, the program including executable instructions that cause a computer to:

monitor, at a subsystem level, the processing of the requests and adjust the allocation of subsystem resources to workload groups to achieve a subsystem goal for performance associated with the one or more workload groups; and

monitor, at a system level, the processing of the requests and adjust the allocation of system resources to workload groups to achieve a system goal for performance associated with the one or more workload groups.

8. The computer program of claim 7 where the subsystem resources and the system resources have respective levels of consumption, the subsystems are arranged in a hierarchical network, and where, when monitoring at a system level, the computer:

causes each subsystem to report through the hierarchy of the network the consumption level of its resources;

causes each subsystem to report through the hierarchy of the network the consumption level of the resources of other subsystems reporting to it according to the hierarchy.

9. The computer program of claim 8 where the hierarchical network includes a tree having a root subsystem at the top of the tree, one or more leaf subsystems at the bottom of the tree, and zero or more intermediate subsystems hierarchically between the root subsystem and the one or more leaf subsystems, and where, when reporting through the hierarchy, the computer:

causes each subsystem to report in the direction of the root subsystem.

10. The computer program of claim 8 where the hierarchical network includes a tree having a root subsystem at the top of the tree, one or more leaf subsystems at the bottom of the tree, and zero or more intermediate subsystems hierarchically between the root subsystem and the one or more leaf subsystems, where each subsystem except the root subsystem has a parent subsystem immediately above it in the hierarchy and each subsystem except the leaf subsystems has one or more child subsystems immediately below it in the hierarchy and where, when reporting through the hierarchy, the computer:

causes each child subsystem to report to its parent subsystem.

11. The computer program of claim 8 where the hierarchical network includes a binary tree.

12. The computer program of claim 8 where the hierarchical network includes a tree having a root subsystem at the top of the tree, one or more leaf subsystems at the bottom of the tree, and zero or more intermediate subsystems hierarchically between the root subsystem and the one or more leaf subsystems, where each subsystem except the root subsystem has a parent subsystem immediately above it in the hierarchy and each subsystem except the leaf subsystems has one or more child subsystems immediately below it in the hierarchy and where, when causing each subsystem reporting through the hierarchy of the network the consumption level of the resources of other subsystems reporting to it according to the hierarchy, the computer:

causes each child subsystem to report its resource consumption level to its parent subsystem; and

causes each parent subsystem to compile its resource consumption level to include the resource consumption levels of its children subsystems.

13. 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 regulating the workload of a database system executing on the massively parallel processing system as it processes one or more requests, the requests being grouped into one or more workload groups, the requests being processed by one or more of a plurality of subsystems within the database system, each subsystem having resources, the system having resources, the subsystem resources and the system resources being allocated across the one or more workload groups, the process including;

monitoring, at a subsystem level, the processing of the requests and adjusting the allocation of subsystem resources to workload groups to achieve a subsystem goal for performance associated with the one or more workload groups; and

monitoring, at a system level, the processing of the requests and adjusting the allocation of system resources to workload groups to achieve a system goal for performance associated with the one or more workload groups.

14. The system of claim 13 wherein the process includes:

one or more parsing engines per node;

one or more dispatchers for each parsing engine; and

one or more subsystem condition detector and adjustors for each dispatcher for performing the subsystem level monitoring for a subsystem.

15. The system of claim 14 where the subsystem resources and the system resources have respective levels of consumption, the subsystems are arranged in a hierarchical network, and monitoring at a system level includes:

each subsystem reporting through the hierarchy of the network the consumption level of its resources;

each subsystem reporting through the hierarchy of the network the consumption level of the resources of other subsystems reporting to it according to the hierarchy.

16. The system of claim 15 where the hierarchical network includes a tree having a root subsystem at the top of the tree, one or more leaf subsystems at the bottom of the tree, and zero or more intermediate subsystems hierarchically between the root subsystem and the one or more leaf subsystems, and where reporting through the hierarchy includes:

reporting in the direction of the root subsystem.

17. The system of claim 15 where the hierarchical network includes a tree having a root subsystem at the top of the tree, one or more leaf subsystems at the bottom of the tree, and zero or more intermediate subsystems hierarchically between the root subsystem and the one or more leaf subsystems, where each subsystem except the root subsystem has a parent subsystem immediately above it in the hierarchy and each subsystem except the leaf subsystems has one or more child subsystems immediately below it in the hierarchy and where reporting through the hierarchy includes:

each child subsystem reporting to its parent subsystem.

18. The system of claim 15 where the hierarchical network includes a binary tree.

19. The system of claim 15 where the hierarchical network includes a tree having a root subsystem at the top of the tree, one or more leaf subsystems at the bottom of the tree, and zero or more intermediate subsystems hierarchically between the root subsystem and the one or more leaf subsystems, where each subsystem except the root subsystem has a parent subsystem immediately above it in the hierarchy and each subsystem except the leaf subsystems has one or more child subsystems immediately below it in the hierarchy and where each subsystem reporting through the hierarchy of the network the consumption level of the resources of other subsystems reporting to it according to the hierarchy includes:

each child subsystem reporting its resource consumption level to its parent subsystem; and

each parent subsystem compiling its resource consumption level to include the resource consumption levels of its children subsystems.