Virtual data maintenance

• US 8,392,461 B2
• Filed: 11/19/2007
• Issued: 03/05/2013
• Est. Priority Date: 11/19/2007
• Status: Active Grant

• Current Assignee: Teradata Corporation
• Original Assignee: Teradota US Incorporated
• Inventors: Brown, Douglas, Morris, John Mark, Walter, Todd
• Primary Examiner(s): Jalil, Neveen Abel
• Assistant Examiner(s): PEACH, POLINA G
• Application Number:
  US11/985,909
• Publication Number:
  US 20090132602A1
• Time in Patent Office: 1,933 Days
• Field of Search: 707/688, 707/694, 707/786, 707792-797
• US Class Current: 707/792
• CPC Class Codes: G06F 16/217 Database tuning G06F16/2282 takes precedence; database performance monitoring G06F11/3409

Abstract

A computer-implemented apparatus, method, and article of manufacture manage a plurality of database systems and perform data maintenance tasks in a data warehouse system. A domain includes a plurality of database systems. A virtual regulator manages the domain, detects a request to invoke a data maintenance task on a first system in the domain, routes the data maintenance task, for execution, to a second system in the domain, and applies results from the data maintenance task (executed by the second system) to the first system.

Claims

1. A computer-implemented apparatus for performing data maintenance tasks in a data warehouse system, comprising:

(a) a domain comprised of a plurality of database systems that are tuned for different objectives and particular workloads, wherein each of the plurality of database systems includes a closed-loop system management (CLSM) regulator, performed by a processor, for monitoring and managing its database system and for controlling resources within its database system, such that each CLSM regulator aggregates information related to its database system as well as that of its children CLSM regulators, and reports the aggregated information to its parent CLSM regulator; and

(b) a virtual regulator, performed by a processor and comprising a CLSM regulator at a highest level of the domain, that communicates with the CLSM regulators that manage the plurality of database systems, for monitoring and managing the domain and for controlling resources within the domain across the plurality of database systems, such that the virtual regulator compiles the aggregated information reported by the CLSM regulators in order to apply rules that adjust operational characteristics of the plurality of database systems and that adjust resources allocated to workloads performed by the plurality of database systems, in order to make sure that service level goals (SLGs) for the workloads are met;

(c) wherein the virtual regulator automatically;

(i) routes a workload to a first database system of the plurality of database systems, based on how the plurality of database systems are tuned for different objectives and particular workloads, as well as the aggregated information compiled by the virtual regulator, for workload management and resource optimization;

(ii) detects a request to perform a data maintenance task on data stored on the first database system of the plurality of database systems;

(iii) determines from how the plurality of database systems are tuned for different objectives and particular workloads, as well as the aggregated information compiled by the virtual regulator, that it is undesirable to perform the data maintenance task on the data stored on the first database system, due to limited availability of resources;

(iv) routes the data maintenance task to a second database system of the plurality of database systems for execution, when it is determined from how the plurality of database systems are tuned for different objectives and particular workloads, as well as the aggregated information compiled by the virtual regulator, that it is undesirable to perform the data maintenance task on the data stored on the first database system due to limited availability of resources, so that the data maintenance task is performed virtually on a replicated copy of the data stored on the second database system; and

(v) applies results from the data maintenance task performed by the second database system to the first database system, thereby freeing up resources, enhancing parallel processing across the plurality of database systems, and providing cost effective dedication of specific resources for the data maintenance task.

2. The apparatus of claim 1, wherein the data maintenance task comprises collecting statistics or demographics.

3. The apparatus of claim 2, wherein the statistics are maintained on a per access module processor (AMP) basis and the number of AMPs in each database system is identical.

4. The apparatus of claim 2, wherein through export of a hash map, the statistics are maintained on a per access module processor (AMP) basis and the number of AMPs in each database system is different.

5. The apparatus of claim 1, wherein the data maintenance task comprises space accounting.

6. The apparatus of claim 1, wherein:

the data maintenance task comprises compression analysis wherein data is examined for frequency of use of each value in each column domain; and

the results are applied by instantiating value compression on the first database system.

7. The apparatus of claim 1, wherein the data maintenance task comprises integrity checking of the data.

8. The apparatus of claim 1, wherein the data maintenance task comprises index analysis to identify opportunities for improved indexes for a workload.

9. The apparatus of claim 1, wherein the data maintenance task comprises statistics analysis to identify opportunities for improved statistics collection.

10. A computer-implemented method for performing data maintenance tasks in a data warehouse system, comprising:

(a) providing a domain comprised of a plurality of database systems that are tuned for different objectives and particular workloads;

(b) wherein each of the plurality of database systems includes a closed-loop system management (CLSM) regulator performed by a processor for monitoring and managing its database system and for controlling resources within its database system, such that each CLSM regulator aggregates information related to its database system as well as that of its children CLSM regulators, and reports the aggregated information to its parent CLSM regulator;

(c) wherein a virtual regulator, performed by a processor and comprising a CLSM regulator at a highest level of the domain, communicates with the CLSM regulators that manage the plurality of database systems, for monitoring and managing the domain and for controlling resources within the domain across the plurality of database systems, such that the virtual regulator compiles the aggregated information reported by the CLSM regulators in order to apply rules that adjust operational characteristics of the plurality of database systems and that adjust resources allocated to workloads performed by the plurality of database systems, in order to make sure that service level goals (SLGs) for the workloads are met;

(d) wherein the virtual regulator automatically performs the steps of;

(i) routing a workload to a first database system of the plurality of database systems, based on how the plurality of database systems are tuned for different objectives and particular workloads, as well as the aggregated information compiled by the virtual regulator, for workload management and resource optimization;

(ii) detecting a request to perform a data maintenance task on data stored on the first database system of the plurality of database systems;

(iii) determining from how the plurality of database systems are tuned for different objectives and particular workloads, as well as the aggregated information compiled by the virtual regulator, that it is undesirable to perform the data maintenance task on the data stored on the first database system, due to limited availability of resources;

(iv) routing the data maintenance task to a second database system of the plurality of database systems for execution, when it is determined from how the plurality of database systems are tuned for different objectives and particular workloads, as well as the aggregated information compiled by the virtual regulator, that it is undesirable to execute the data maintenance task on the first database system, so that the data maintenance task is performed virtually on a replicated copy of the data stored on the second database system; and

(v) applying results from the data maintenance task executed by the second database system to the first database system, thereby freeing up resources, enhancing parallel processing across the plurality of database systems, and providing cost effective dedication of specific resources for the data maintenance task.

11. The method of claim 10, wherein the data maintenance task comprises collecting statistics or demographics.

12. The method of claim 11, wherein the statistics are maintained on a per access module processor (AMP) basis and the number of AMPs in each database system is identical.

13. The method of claim 11, wherein through export of a hash map, the statistics are maintained on a per access module processor (AMP) basis and the number of AMPs in each database system is different.

14. The method of claim 10, wherein the data maintenance task comprises space accounting.

15. The method of claim 10, wherein:

the data maintenance task comprises compression analysis wherein data is examined for frequency of use of each value in each column domain; and

the results are applied by instantiating value compression on the first database system.

16. The method of claim 10, wherein the data maintenance task comprises integrity checking of the data.

17. The method of claim 10, wherein the data maintenance task comprises index analysis to identify opportunities for improved indexes for a workload.

18. The method of claim 10, wherein the data maintenance task comprises statistics analysis to identify opportunities for improved statistics collection.

19. An article of manufacture comprising one or more storage devices tangibly embodying instructions that, when executed by one or more computer systems, result in the computer systems performing a method for performing data maintenance tasks in a data warehouse system, the method comprising:

(a) providing a domain comprised of a plurality of database systems that are tuned for different objectives and particular workloads;

(b) wherein each of the plurality of database systems includes a closed-loop system management (CLSM) regulator performed by a processor for monitoring and managing its database system and for controlling resources within its database system, such that each CLSM regulator aggregates information related to its database system as well as that of its children CLSM regulators, and reports the aggregated information to its parent CLSM regulator;

(c) wherein a virtual regulator, performed by a processor and comprising a CLSM regulator at a highest level of the domain, communicates with the CLSM regulators that manage the plurality of database systems, for monitoring and managing the domain and for controlling resources within the domain across the plurality of database systems, such that the virtual regulator compiles the aggregated information reported by the CLSM regulators in order to apply rules that adjust operational characteristics of the plurality of database systems and that adjust resources allocated to workloads performed by the plurality of database systems, in order to make sure that service level goals (SLGs) for the workloads are met;

(d) wherein the virtual regulator automatically;

(i) routes a workload to a first database system of the plurality of database systems, based on how the plurality of database systems are tuned for different objectives and particular workloads, as well as the aggregated information compiled by the virtual regulator, for workload management and resource optimization;

(ii) detects a request to perform a data maintenance task on data stored on the first database system of the plurality of database systems;

(iii) determines from how the plurality of database systems are tuned for different objectives and particular workloads, as well as the aggregated information compiled by the virtual regulator, that it is undesirable to perform the data maintenance task on the data stored on the first database system, due to limited availability of resources;

(iv) routes the data maintenance task to a second database system of the plurality of database systems for execution, when it is determined from how the plurality of database systems are tuned for different objectives and particular workloads, as well as the aggregated information compiled by the virtual regulator, that it is undesirable to perform the data maintenance task on the data stored on the first database system due to limited availability of resources, so that the data maintenance task is performed virtually on a replicated copy of the data stored on the second database system; and

(v) applies results from the data maintenance task performed by the second database system to the first database system, thereby freeing up resources, enhancing parallel processing across the plurality of database systems, and providing cost effective dedication of specific resources for the data maintenance task.

20. The article of claim 19, wherein the data maintenance task comprises collecting statistics or demographics.

21. The article of claim 19, wherein the data maintenance task comprises space accounting.

22. The article of claim 19, wherein:

the data maintenance task comprises compression analysis wherein data is examined for frequency of use of each value in each column domain; and

the results are applied by instantiating value compression on the first database system.

23. The article of claim 19, wherein the data maintenance task comprises integrity checking of the data.

24. The article of claim 19, wherein the data maintenance task comprises index analysis to identify opportunities for improved indexes for a workload.

25. The article of claim 19, wherein the data maintenance task comprises statistics analysis to identify opportunities for improved statistics collection.