Database query optimizer that takes network choice into consideration

• US 9,934,271 B2
• Filed: 04/17/2012
• Issued: 04/03/2018
• Est. Priority Date: 12/05/2006
• Status: Active Grant

• Current Assignee: International Business Machines Corporation
• Original Assignee: International Business Machines Corporation
• Inventors: Barsness, Eric L., Darrington, David L., Randles, Amanda, Santosuosso, John M.
• Primary Examiner(s): Gorney, Boris
• Assistant Examiner(s): TRAN, BAO G
• Application Number:
  US13/448,483
• Publication Number:
  US 20120203763A1
• Time in Patent Office: 2,177 Days
• Field of Search: 707721, 3721, 709227, 709249
• CPC Class Codes: G06F 16/2453 Query optimisation
• CPC Class Codes: G06F 16/2471 Distributed queries

Abstract

In a networked computer system that includes multiple nodes and multiple networks interconnecting the nodes, a database query optimizer takes network choice into consideration when optimizing a query. The query optimizer logs historical information for queries that includes network information. A network monitor determines network characteristics of the multiple networks. The query optimizer then selects a network for a query based on the network characteristics, and optionally based on the historical information as well. The result is a query optimizer that takes network choice into consideration, thereby providing a query that is optimized according to the characteristics of the networks.

Claims

1. An apparatus comprising:

at least one processor;

a memory coupled to the at least one processor;

a plurality of network interfaces coupled to a plurality of networks that are coupled to a plurality of nodes, each of the plurality of networks comprising a communication path between at least two of the plurality of nodes, each node comprising at least one processor and memory, a first of the plurality of nodes including first data in a distributed database and a second of the plurality of nodes including second data in the distributed database;

a network monitor that monitors characteristics of the plurality of networks and generates a log of the characteristics of the plurality of networks;

first and second of the plurality of networks that provide alternative network connections to the first node; and

a query optimizer residing in the memory and executed by the at least one processor, the query optimizer logging historical information for queries that indicates past network selection when executing each of the queries, the query optimizer taking network choice into consideration when executing a database query to the distributed database that retrieves the first data and the second data in the distributed database by considering the characteristics of the plurality of networks between the apparatus and the first and second nodes logged by the network monitor and by considering the past network selection in selecting at least one of the plurality of networks for executing the query, wherein the query optimizer selects one of the first and second networks for executing the query based on the historical information that includes which of the first and second networks were used during at least one previous execution of the query and based on information in the log of the characteristics of the plurality of networks that indicates characteristics of the first and second networks.

2. A networked computer system comprising:

a plurality of nodes coupled to each other via a plurality of networks, each of the plurality of networks comprising a communication path between at least two of the plurality of nodes, each of the plurality of nodes comprising at least one processor and memory, the plurality of nodes comprising a distributed database with a first node including first data in the distributed database and a second node including second data in the distributed database and a third node;

a network monitor that monitors characteristics of the plurality of networks and generates a log of the characteristics of the plurality of networks;

first and second of the plurality of networks that provide alternative network connections between the third node and the first node; and

a query optimizer executing on the at least one processor of the third node, wherein the query optimizer logs historical information for queries that indicates past network selection when executing each of the queries, wherein the query optimizer takes network choice into consideration when executing a database query to the first and second nodes in the distributed database that retrieves the first data and the second data in the distributed database by considering the characteristics of the plurality of networks between the first, second and third nodes logged by the network monitor and by considering the past network selection in selecting at least one of the plurality of networks for executing the query, wherein the query optimizer selects one of the first and second networks for executing the query based on the historical information that includes which of the first and second networks were used during at least one previous execution of the query and based on information in the log of the characteristics of the plurality of networks that indicates characteristics of the first and second networks.

3. The networked computer system of claim 2 wherein the query optimizer selects the at least one of the plurality of networks based on at least one parameter in the query that specifies which network to select when executing the query.

4. The networked computer system of claim 2 wherein the query optimizer selects the at least one of the plurality of networks based on layout of the at least two nodes involved in processing the query.

5. The networked computer system of claim 2 wherein the characteristics of the plurality of networks comprise network topology.

6. The networked computer system of claim 2 wherein the characteristics of the plurality of networks comprise network utilization.

7. The networked computer system of claim 2 wherein the characteristics of the plurality of networks comprise network latency.

8. A program product comprising software residing on non-transitory computer-readable media, the software comprising:

a network monitor that monitors characteristics of a plurality of networks and generates a log of the characteristics of the plurality of networks;

a query optimizer that takes network choice into consideration when executing a database query to a plurality of nodes in a distributed database that retrieves first data on a first of the plurality of nodes in the distributed database and second data on a second of the plurality of nodes in the distributed database, the plurality of nodes coupled to each other via the plurality of networks, each of the plurality of networks comprising a communication path between at least two of the plurality of nodes in the distributed database, the plurality of networks comprising first and second networks that provide alternative network connections to the first node, the query optimizer considering characteristics of the plurality of networks between the at least two nodes in selecting at least one of the plurality of networks for executing the query, wherein the query optimizer logs historical information for queries that indicates past network selection when executing each of the queries and selects one of the first and second networks for executing the query based on the historical information that includes which of the first and second networks were used during at least one previous execution of the query and based on information in the log of the characteristics of the plurality of networks that indicates characteristics of the first and second networks.

9. The program product of claim 8 wherein the query optimizer selects the at least one of the plurality of networks based on at least one parameter in the query that specifies which network to select when executing the query.

10. The program product of claim 8 wherein the query optimizer selects the at least one of the plurality of networks based on layout of the at least two nodes involved in processing the query.

11. The program product of claim 8 wherein the characteristics of the plurality of networks comprise network topology.

12. The program product of claim 8 wherein the characteristics of the plurality of networks comprise network utilization.

13. The program product of claim 8 wherein the characteristics of the plurality of networks comprise network latency.

14. A networked computer system comprising:

a first node comprising a first processor and first memory and first data in the first memory;

a second node comprising a second processor and second memory and second data in the second memory;

a third node comprising a third processor and third memory;

the first, second and third nodes coupled together via a plurality of networks, each of the plurality of networks comprising a communication path between at least two of the first, second and third nodes, the first, second and third nodes comprising a distributed database;

a network monitor coupled to the plurality of networks, the network monitor monitoring execution of a plurality of database queries to at least two of the first, second and third nodes in the distributed database, the network monitor logging nodes involved in executing each of the plurality of database queries, network used in executing each of the plurality of database queries, and elapsed time in executing each of the plurality of database queries, the network monitor further logging network characteristics for the plurality of networks, the network characteristics comprising current network utilization, predicted future network utilization, and network availability; and

a query optimizer that takes network choice into consideration when executing a database query that retrieves the first data in the first node in the distributed database and the second data in the second node in the distributed database by reading historical information logged by the network monitor including the nodes involved in at least one previous execution of the database query, the one or more networks used in the at least one previous execution of the database query, the elapsed time in the at least one previous executions of the database query, the current network utilization for the plurality of networks, the predicted future network utilization for the plurality of networks, and the availability of the plurality of networks, the query optimizer selecting at least one of the plurality of networks for executing the query based on the historical information and based on the network characteristics for the plurality of networks.