Managing excess capacity of database systems in a capacity controlled computing environment

• US 9,747,334 B2
• Filed: 09/30/2011
• Issued: 08/29/2017
• Est. Priority Date: 09/30/2011
• Status: Active Grant

• Current Assignee: Teradata Corporation
• Original Assignee: Teradata US, Inc. (Teradata Corporation)
• Inventors: Burger, Louis, Brown, Douglas P., Julien, Thomas, Vandervort, Frank Roderic
• Primary Examiner(s): Chen, Susan
• Application Number:
  US13/250,150
• Publication Number:
  US 20130085984A1
• Time in Patent Office: 2,160 Days
• Field of Search: 709226, 705 7, 718104
• CPC Class Codes: G06F 16/2453 Query optimisation
• CPC Class Codes: G06F 16/24547 Optimisations to support specific applications; Extensibility of optimisers

Abstract

Excess capacity available to a database system in a capacity controlled environment can be effectively managed. In particular, excess capacity that is not made available for normal operations of a database system can be used to manage errors, especially situations that may hinder expected performance of the database system. In addition, excess capacity can be used to optimize or further optimize database queries, especially those that meet a criterion (e.g., not fully optimize, not optimized as expected).

Claims

1. A computer-implemented method to manage capacity of a database system, wherein the database system that includes one or more database nodes operable to process data associated with a database, wherein the one or more database nodes include one or more physical processors, and wherein the computer-implemented method comprises:

configuring the database system to operate at an allotted limited capacity below its full capacity to process database queries, by configuring at least one of the one or more database nodes to operate at an allotted usage capacity below its full usage capacity to process database queries, wherein an excess capacity in relation to the allotted limited usage capacity is available to the database system on demand;

determining by the one or more physical processors that one or more problematic operations associated with the database have occurred, wherein the one or more problematic operations include at least one of;

(i) one or more database queries that have been determined to encounter an error during execution, and (ii) execution of one or more database queries have been determined not to be optimal;

determining by the one or more physical processors whether to allow excess capacity that is available to the database system on demand in relation to the allotted limited capacity to be used to resolve the problematic operations by at least performing one or more selected operations pertaining to at least one of;

processing of a selected one or more of the database queries that have encountered an error, and optimization of a selected one or more database queries determined not to be optimal; and

when the determining determines to allow the excess capacity available to the database system to be used to perform the one or more selected operations;

allowing by the one or more physical processors the excess capacity of the database system to be used on demand to perform the one or more selected operations, by allowing the at least one database node to exceed its allotted usage capacity on demand to perform the one or more operations,

not allowing by the one or more physical processors the excess capacity of the database system to be used to process one or more of the database queries that have not been selected for least one of;

processing of the selected one or more of the database queries that has encountered an error, and the optimization of the selected one or more of the database queries.

2. The computer-implemented method of claim 1, wherein the allowing the access capacity to be used to perform the one or more selected operations comprises:

not allowing at least one or more operations associated with the database to use the excess capacity.

3. The computer-implemented method of claim 1, wherein the determining whether to allow excess capacity available to the database system to be used to perform one or more selected operations comprises:

determining whether an error condition has occurred.

4. The computer-implemented method of claim 3, wherein the error condition is associated with scheduling a database query for execution.

5. The computer-implemented method of claim 1, wherein the determining whether to allow excess capacity available to the database system to be used to perform the selected one or more operations comprises:

determining whether to optimize or further optimize a database query.

6. The computer-implemented method of claim 1, wherein the determining whether to allow excess capacity available to the database system to be used to perform the one or more selected operations comprises one or more of the following:

(a) identifying an execution plan with an estimated completion time that does meet a goal or a requirement,

(b) identifying an execution plan with an execution time and/or resource consumption time that exceed a threshold, and

(c) identifying a query with a number of operations that exceed a threshold.

7. An apparatus, comprising:

one or more processors operable to;

process and store data for a database system configured to operate at a limited capacity below its full capacity;

configure the database system to operate at an allotted limited capacity below its full capacity to process database queries, by configuring at least one of the one or more database nodes of the database system to operate at an allotted usage capacity below its full usage capacity to process database queries, wherein an excess capacity in relation to the allotted limited usage capacity is available to the database system on demand;

determine that one or more problematic operations associated with the database have occurred, wherein the one or more problematic operations associated with the database include at least one of;

(i) one or more database queries that have been determined to encounter and error, and (ii) execution of one or more database queries determined not to be optimal;

determine whether to allow excess capacity available to the database on demand to be used to resolve one or more problematic operations associated with the database;

allow the excess capacity to be used on demand to resolve the one or more problematic operations when the determining determines to allow the excess capacity available to the database system to be used on demand to perform the one or more operations associated with the database; and

not allowing the excess capacity to be used to perform one or more other operations associated with the database that are determined not be problematic.

8. The apparatus of claim 7, wherein the one or more operations pertain to processing a database query that has encountered an error.

9. The apparatus of claim 7, wherein the one or more operations pertain to optimization of a database query.

10. The apparatus of claim 7, wherein the allowing the access capacity to be used to perform the one or more operations only allows the excess capacity to be used to perform the one or more operations.

11. The apparatus of claim 7, wherein the allowing the access capacity to be used to perform the one or more operations comprises:

not allowing at least one or more operations associated with the database to use the excess capacity.

12. The apparatus of claim 7, wherein the determining whether to allow excess capacity available to the database system to be used to perform one or more operations comprises:

determining whether an error condition has occurred.

13. The apparatus of claim 12, wherein the error condition is associated with scheduling a database query for execution.

14. The apparatus of claim 7, wherein the determining whether to allow excess capacity available to the database system to be used to perform one or more operations comprises:

determining whether to optimize or further optimize a database query.

15. A non-transitory computer readable storage medium storing at least executable computer code for managing capacity of a database system that includes one or more database nodes operable to process data associated with a database, wherein the database system is configured to operate at a limited capacity below its full capacity, and wherein the non-transient computer readable storage medium includes:

executable computer code operable to configure the database system to operate at an allotted limited capacity below its full capacity to process database queries, by configuring at least one of the one or more database nodes of the database system to operate at an allotted usage capacity below its full usage capacity to process database queries, wherein an excess capacity in relation to the allotted limited usage capacity is available to the database system on demand;

executable computer code operable to determine whether to allow excess capacity available to the database system on demand to be used to perform one or more problematic operations associated with processing of the data by the one or more database nodes, wherein the one or more operations associated with the database include;

(i) one or more database queries that have been determined to encounter and error, and (ii) optimizations of one or more database queries determined not be optimal; and

executable computer code operable to allow the excess capacity to be used to perform the one or more problematic operations when the determining determines to allow the excess capacity available to the database system on demand to be used to perform the one or more problematic operations associated with processing of the data by the one or more database nodes; and

executable computer code operable not to allow the excess capacity to be used to perform one or more other operations associated with the database that are determined not to be problematic.

16. The non-transitory computer readable storage medium of claim 15, wherein the one or more operations pertain to processing a database query that has encountered an error.

17. The non-transitory computer readable storage medium of claim 16, wherein the database query is selected as a result of scheduling mistake due to a limitation of an estimation technology used to estimate resource usage of the database query.

18. The non-transitory computer readable storage medium of claim 15, wherein the one or more operations pertain to optimization of a database query.

19. The non-transitory computer readable storage medium of claim 18, wherein the database query is a selected database query requiring a more extensive and through optimizations than a plurality of other database queries.