Aggregating database queries

• US 7,958,158 B2
• Filed: 06/19/2008
• Issued: 06/07/2011
• Est. Priority Date: 06/19/2008
• Status: Expired due to Fees

• Current Assignee: International Business Machines Corporation
• Original Assignee: International Business Machines Corporation
• Inventors: Hu, Wei, Bestgen, Robert Joseph, Kethireddy, Shantan, Thiemann, Ulrich, Passe, Andrew Peter
• Primary Examiner(s): Mofiz; Apu M
• Assistant Examiner(s): Nguyen; Cindy
• Application Number:
  US12/142,182
• Publication Number:
  US 20090319474A1
• Time in Patent Office: 1,083 Days
• Field of Search: 707/799, 707718-721
• US Class Current: 707/799
• CPC Class Codes: G06F 16/2453 Query optimisation
• CPC Class Codes: Y02D 10/00 Energy efficient computing, e.g. low power processors, power management or thermal management

Abstract

Embodiments of the invention provide techniques for aggregating database queries for energy efficiency. In one embodiment, queries received by a DBMS are aggregated and staged according to hard-disk drives required for query execution. Each group of queries accessing a given drive may be dispatched for execution together. Further, the queries received by a DBMS may be matched to patterns of previously received queries. The matching patterns may be used to predict other queries which are likely to be received by the DBMS. The received queries may be staged to be dispatched with the predicted queries. By aggregating queries to be executed, access to each hard-disk drive may be optimized, thus reducing the overall energy consumption required for executing the queries.

Claims

1. A computer-implemented method, comprising:

receiving a database query;

determining at least one data element required for executing the database query;

identifying, from a plurality of storage devices, a storage device storing the determined at least one data element;

adding the received database query to a first queue of a plurality of queues each having a plurality of queued queries, each of the queues having a predefined association with a respective storage device of the plurality of storage devices, wherein the predefined association of the first queue is a first predefined association with the identified storage device storing the determined at least one data element, wherein each of the queued queries of a given of one of the queues requires one or more data elements stored in the respective storage device in order to be executed, wherein each queued query is received during a specified time period, and wherein the specified time period is selected according to an energy consumption objective; and

after the specified time period;

dispatching the plurality of queued queries from the first queue;

retrieving, from the respective storage device, data elements required for executing the dispatched queries; and

executing the dispatched queries, using the retrieved data elements as inputs.

2. The computer-implemented method of claim 1, further comprising:

returning a set of query results; and

powering down the respective storage device.

3. The computer-implemented method of claim 1, wherein the storage device is a hard-disk drive.

4. The computer-implemented method of claim 3, wherein the energy consumption objective is directed to minimizing a number of times the hard-disk drive spins up to a predefined read speed.

5. The computer-implemented method of claim 1, wherein determining at least one data element comprises analyzing a history of query plans previously performed on the database.

6. The computer-implemented method of claim 1, wherein determining at least one data element comprises generating a new query plan for performing the received query.

7. The computer-implemented method of claim 1, wherein the at least one data element comprises at least one of:

(i) a table of a database, and (ii) an index of a database.

8. The computer-implemented method of claim 1, further comprising:

determining an amount of energy savings for executing the dispatched queries rather that executing the same queries as they are received; and

presenting the determined amount to a user.

9. A computer readable storage medium containing a program which, when executed, performs an operation, comprising:

receiving a database query;

determining at least one data element required for executing the database query;

identifying, from a plurality of storage devices, a storage device storing the determined at least one data element;

adding the received database query to a first queue of a plurality of queues each having a plurality of queued queries, each of the queues having a predefined association with a respective storage device of the plurality of storage devices, wherein the predefined association of the first queue is a first predefined association with the identified storage device storing the determined at least one data element, wherein each of the queued queries of a given of one of the queues requires one or more data elements stored in the respective storage device in order to be executed, wherein each queued query is received during a specified time period, and wherein the specified time period is selected according to an energy consumption objective; and

after the specified time period;

dispatching the plurality of queued queries from the first queue;

retrieving, from the respective storage device, data elements required for executing the dispatched queries; and

executing the dispatched queries, using the retrieved data elements as inputs.

10. The computer readable storage medium of claim 9, further comprising:

returning a set of query results; and

powering down the respective storage device.

11. The computer readable storage medium of claim 9, wherein the storage device is a hard-disk drive.

12. The computer readable storage medium of claim 11, wherein the energy consumption objective is directed to minimizing a number of times the hard-disk drive spins up to a predefined read speed.

13. The computer readable storage medium of claim 9, wherein determining at least one data element comprises analyzing a history of query plans previously performed on the database.

14. The computer readable storage medium of claim 9, wherein determining at least one data element comprises generating a new query plan for performing the received query.

15. The computer readable storage medium of claim 9, wherein the at least one data element comprises at least one of:

(i) a table of a database, and (ii) an index of a database.

16. The computer readable storage medium of claim 9, further comprising:

determining an amount of energy savings for executing the dispatched queries rather that executing the same queries as they are received; and

presenting the determined amount to a user.

17. A system, comprising:

a database;

a processor; and

a memory containing a program, which when executed by the processor is configured to perform an operation, comprising;

receiving a database query;

determining at least one data element required for executing the database query;

identifying, from a plurality of storage devices, a storage device storing the determined at least one data element;

adding the received database query to a first queue of a plurality of queues each having a plurality of queued queries, each of the queues having a predefined association with a respective storage device of the plurality of storage devices, wherein the predefined association of the first queue is a first predefined association with the identified storage device storing the determined at least one data element, wherein each of the queued queries of a given of one of the queues requires one or more data elements stored in the respective storage device in order to be executed, wherein each queued query is received during a specified time period, and wherein the specified time period is selected according to an energy consumption objective; and

after the specified time period;

dispatching the plurality of queued queries from the first queue;

retrieving, from the respective storage device, data elements required for executing the dispatched queries; and

executing the dispatched queries, using the retrieved data elements as inputs.

18. The system of claim 17, wherein the storage device is a hard-disk drive.

19. The system of claim 18, wherein the energy consumption objective is directed to minimizing a number of times the hard-disk drive spins up to a predefined read speed.

20. The system of claim 17, wherein determining at least one data element comprises analyzing a history of query plans previously performed on the database.