Optimized database appliance

• US 7,010,521 B2
• Filed: 05/13/2002
• Issued: 03/07/2006
• Est. Priority Date: 05/13/2002
• Status: Expired due to Fees

• Current Assignee: International Business Machines Corporation
• Original Assignee: Netezza (International Business Machines Corporation)
• Inventors: Zane, Barry M., Hinshaw, Foster D., Metzger, John K.
• Primary Examiner(s): RIMELL, SAMUEL G
• Application Number:
  US10/145,571
• Publication Number:
  US 20030212668A1
• Time in Patent Office: 1,394 Days
• Field of Search: 707/2, 707/3, 707/104.1, 707/102, 707/10, 707/5, 711/170
• US Class Current: 1/1
• CPC Class Codes: G06F 16/2471 Distributed queries
• CPC Class Codes: Y10S 707/99932 Access augmentation or optimizing
• CPC Class Codes: Y10S 707/99933 Query processing, i.e. searching
• CPC Class Codes: Y10S 707/99942 Manipulating data structure, e.g. compression, compaction, compilation
• CPC Class Codes: Y10S 707/99943 Generating database or data structure, e.g. via user interface

Abstract

A system from processing database queries allows for cost and locale based distribution for execution of database queries. The database queries are executed on execution engines that provide flexible configuration and overlapping functionality. The system reduces various costs, including elapsed time, required to perform database queries. The system provides processing of a database query using a database catalog comprising database table locality information, record locality information and execution engine information. A query optimizer receives the query and accesses the database catalog to create a query execution plan comprising locality-based database operations. A central database operation processor providing a first execution engine executes the query execution plan by performing at least a portion of the locality-based database operations and distributing at least a portion of the locality-based database operations as a subplan. A second database operation processor providing a second execution engine executes the subplan received from the central database operation processor. At least one of the database operations can be executed on either the first execution engine or the second execution engine. A storage unit stores at least a portion of database tables and records. A data communications network connects the central database processor to the second database processor.

Claims

1. A system for processing a query on a database comprising:

a database catalog comprising database table locality information, record locality information, execution engine information and characteristics of the system components;

a query optimizer receiving the query and accessing the database catalog to create a query execution plan comprising locality-based database operations, the locality-based database operations in the query execution plan thus specifying a database operation as well as a processor locale from among multiple possible locales for each such database operation;

a central database operation processor providing a first execution engine as a first type of locale for executing database operations as specified by the query execution plan, by performing at least a portion of the locality-based database operations as specified by the query execution plan and distributing at least some other portions of the locality-based database operations as a subplan;

a second database operation processor providing a second execution engine as a second locale for executing portions of the query execution plan as specified by the subplan received from the central database operation processor, wherein the second execution engine locale has at least some different operation processing capabilities than the first execution engine locale, to provide a second type of locale for executing database operations;

a storage unit storing at least a portion of database tables and records connected to at least one of the central database operation processor and the second database operation processor;

a data communications network connecting the central database operation processor to the second database operation processor;

program means for comparing the processing capabilities of the first and second locale types for the database operation; and

means for changing the query execution plan, to direct specific database operations to the locales determined by the means for comparing.

2. The system of claim 1 wherein the central database operation processor is configured as a symmetric multi-processing system and the second database operation processor is configured as a component of a massively parallel processing system.

3. The system of claim 1 wherein the first execution engine or the second execution engine processes data records received from the storage unit by performing one or more operations on the data records without materializing the data record into a memory.

4. The system of claim 1 wherein the first execution engine or the second execution engine processes data records received from the data communications network by performing one or more operations on the data records without materializing the data record into a memory.

5. The system of claim 1 wherein the first execution engine locale and the second execution engine locale have different processing capabilities selected from a group of processing capabilities consisting of execution engine memory, execution engine processor speed, cache configuration, disk speed, disk controller speed, disk controller bandwidth, network interface bandwidth or network interface speed.

6. The system of claim 1 wherein the storage unit is electrically connected to the second database operation processor.

7. The system of claim 1 wherein the storage unit is optically connected to the second database operation processor.

8. The system of claim 1 wherein the storage unit is a disk drive.

9. The system of claim 1 wherein the storage unit is a random access memory.

10. The system of claim 1 wherein a portion of the database is stored on a storage unit connected to the central database operation processor.

11. The system of claim 1 wherein the first execution engine or the second execution engine reconfigures an executing subplan to redirect one or more operations to another execution engine.

12. The system of claim 1 wherein each execution engine comprises a plurality of processing devices.

13. The system of claim 1 wherein each execution engine comprises a combination of processing devices.

14. The system of claim 13 wherein the execution engine comprises a general purpose central processing unit and a field programmable gate array.

15. The system of claim 13 wherein the execution engine comprises a general purpose central processing unit and an application specific integrated circuit.

16. The system of claim 13 wherein the execution engine comprises a field programmable gate array and an application specific integrated circuit.

17. The system of claim 1 wherein a plurality of the storage units are grouped into sections and the locality information comprises:

a section indicator indicating which section stores a database table; and

a distribution indicator indicating whether the database table is striped or replicated to the section.

18. The system of claim 1 wherein a system optimizer tracks performance results from the query and reallocates the database tables and records across the storage units in order to improve query execution.

19. The system of claim 18 wherein the reallocation comprises striping the database tables and records across the storage units.

20. The system of claim 18 wherein the reallocation comprises replicating the database tables and records across the storage units.

21. The system of claim 1 wherein at least one of the first execution engine or the second execution engine dynamically track performance results from the executing queries and modify the query execution plan to improve performance.

22. The system of claim 1 wherein the execution engines dynamically track system configuration and resource utilization from the executing queries and modify the query execution plan to improve system resource utilization.

23. The system of claim 1 wherein at least one of the first execution engine or the second execution engine are dynamically reconfigured to process different database operations.

24. The system of claim 1 wherein the storage unit is directly connected to the second database operation processor, without using a data network connection.

25. The system of claim 1 wherein access to the storage unit is exclusive to a predetermined second database processor, such that other database processors cannot access the storage unit.

26. A system as in claim 1 wherein the query execution plan is developed by the central database operation processor.

27. A system as in claim 1 wherein selection of execution locale for the operation is made before or during query execution.

28. A system as in claim 1 wherein the execution engine locale type at which such specific database operation is actually executed is additionally chosen by comparing operating conditions of the first and second locale types.

29. A system as in claim 28 wherein the operating conditions comprise a measure of resource utilization.

30. A system as in claim 29 wherein the resource is selected from a group consisting of execution engine memory, execution engine processor speed, cache configuration, disk speed, disk controller speed, disk controller bandwidth, network interface bandwidth or network interface speed.

31. A method for generating a distributed locality-based execution plan comprising:

receiving a database query;

parsing and validating the database query to produce a query tree; and

optimizing the query tree to produce a locality-based execution plan by accessing a database catalog comprising database table locality information, record locality information and execution engine information, the locality-based execution plan thus being a plan containing multiple locality-based database operations, and the plan specifying a processing locale type for each database operation in the plan from among multiple possible database execution engine locale types, with at least one type of database operation being optionally executable on either a first execution engine locale type or a second execution engine locale type, with the first execution engine locale type having at least some different processing capabilities than the second execution engine locale type;

executing first portions of the locality-based execution plan on at least one execution engine which is a first type of execution engine, the first portions being designated by the plan as having a locale which specifies the first execution engine locale type;

distributing second portions of the locality-based execution plan as a subplan to a second database execution engine which is a second type of execution engine, the second portions designated by the plan as having a locale which specifies the second execution engine locale type;

comparing the processing capabilities of the first and second locale types for the database operation; and

changing the query execution plan, to direct specific database operations to the locales determined by the step of comparing.

32. An apparatus for generating a distributed locality-based execution plan comprising:

means for receiving a database query;

means for parsing and validating the database query to produce a query tree; and

means for optimizing the query tree to produce a locality-based execution plan by accessing a database catalog comprising database table locality information, record locality information and execution engine information, the locality-based execution plan thus being a plan containing multiple locality-based database operations, and the plan specifying a processing locale type for each database operation in the plan from among multiple possible database execution engine locale types, with at least one type of database operation being optionally executable on either a first execution engine locale type or a second execution engine locale type, with the first execution engine locale type having at least some different processing capabilities than the second execution engine locale type;

executing first portions of the locality-based execution plan on at least one execution engine which is a first type of execution engine, the first portions being designated by the plan as having a locale which specifies the first execution engine locale type;

distributing second portions of the locality-based execution plan as a subplan to a second database execution engine which is a second type of execution engine, the second portions designated by the plan as having a locale which specifies the second execution engine locale type; and

comparing the processing capabilities of the first and second locale types for the database operation; and

changing the query execution plan, to direct specific database operations to the locales determined by the step of comparing.

33. A computer program product comprising:

a computer usable medium for generating a distributed locality-based execution plan;

a set of computer program instructions embodied on the computer usable medium, including instructions to;

receive a database query;

parse and validating the database query to produce a query tree; and

optimize the query tree to produce a locality-based execution plan by accessing a database catalog comprising database table locality information, record locality information and execution engine information, the locality-based execution plan thus being a plan containing multiple locality-based database operations, and the plan specifying a processing locale type for each database operation in the plan from among multiple possible database execution engine locale types, with at least one type of database operation being optionally executable on either a first execution engine locale type or a second execution engine locale type, with the first execution engine locale type having at least some different processing capabilities than the second execution engine locale type;

executing first portions of the locality-based execution plan on at least one execution engine which is a first type of execution engine, the first portions being designated by the plan as having a locale which specifies the first execution engine locale type;

distributing second portions of the locality-based execution plan as a subplan to a second database execution engine which is a second type of execution engine, the second portions designated by the plan as having a locale which specifies the second execution engine locale type;

comparing the processing capabilities of the first and second locale types for the database operation; and

changing the query execution plan, to direct specific database operations to the locales determined by the step of comparing.

34. A system for processing a query on a database comprising:

a plurality of database operation processor components, with at least two of the database operation processor components having different operation processing capabilities, such that the database operation processor components provide at least two different locales for executing database operations, with the two locales having different operation processing capabilities and such that more than one database operation processor component locale is capable of executing a given selected database operation;

a database catalog comprising database table information, database operation processor component locale information, and database operation processor characteristic information;

a query optimizer receiving the query and accessing the database catalog to develop a query execution plan, the query execution plan defining a list of database operations to carry out the query, and the query execution plan also defining a respective database operation processor component locale at which to carry out each such database operation in the query execution plan, program means for comparing the processing capabilities of the first and second locale types for the database operation; and

means for changing the query execution plan, to direct specific database operations to the locales determined by the means for comparing.

35. A system as in claim 34 additionally comprising:

a central database operation processor comprising a first execution engine to provide a first one of the database operation components;

a second database operation processor comprising a second execution engine to provide a second one of the database operation components;

a storage unit storing at least a portion of database tables and records connected to at least one of the central database operation processor and the second database operation processor; and

a data communications network connecting the central database operation processor to the second database operation processor.

36. The system of claim 35 wherein the central database operation processor is configured as a symmetric multi-processing system and the second database operation processor is configured as a component of a massively parallel processing system.

37. The system of claim 35 wherein the first execution engine or the second execution engine processes data records received from the storage unit by performing one or more operations on the data records without materializing the data record into a memory.

38. The system of claim 35 wherein the first execution engine or the second execution engine processes data records received from the data communications network by performing one or more operations on the data records without materializing the data record into a memory.

39. The system of claim 35 wherein the first execution engine and the second execution engine have at least some different processing capabilities.

40. The system of claim 35 wherein the first execution engine or the second execution engine reconfigures an assigned execution subplan to redirect one or more operations to another execution engine.

41. The system of claim 35 wherein first execution engine comprises a general purpose central processing unit and a field programmable gate array.

42. The system of claim 35 wherein second execution engine comprises a general purpose central processing unit and an application specific integrated circuit.

43. The system of claim 35 wherein a system optimizer tracks performance results from the query and reallocates the database tables and records across the storage units in order to improve query execution.

44. The system of claim 43 wherein the reallocation comprises striping the database tables and records across the storage units.

45. The system of claim 43 wherein the reallocation comprises replicating the database tables and records across the storage units.

46. The system of claim 35 wherein at least one of the first execution engine or the second execution engine dynamically track performance results from the executing queries and modify a query execution plan to improve performance.

47. The system of claim 35 wherein the execution engines dynamically track system configuration and resource utilization from the executing queries and modify the query execution plan to improve system resource utilization.

48. The system of claim 35 wherein at least one of the first execution engine or the second execution engine are dynamically reconfigured to process different database operations.

49. A system for processing a query on a database comprising:

a plurality of database operation processor components, with at least two of the database operation processor components have different operation processing capabilities, and such that more than one database operation processor component is capable of executing a given database operation;

a database catalog comprising database table information, database operation processor component locale information, and database operation processor characteristic information;

a query optimizer receiving the query and accessing the database catalog to develop a query execution plan, the query execution plan defining a list of database operations to carry out the query, and the query execution plan also defining a respective database operation processor component locale for each such database operation from among multiple possible such locales; and

wherein at least one of the execution engines dynamically tracks resource utilization during execution of the query, while the database operations are still being executed by the database operation processor components, so that the query plan is dynamically re-optimized while the query plan is being executed, and thereby modifying the locales originally specified by the query execution plan to optimize performance.

50. A system as in claim 49 wherein the resource tracked is selected from a group consisting of execution engine memory, execution engine processor speed, cache configuration, disk speed, disk controller speed, disk controller bandwidth, network interface bandwidth or network interface speed.

51. A system as in claim 49 wherein the plurality of database operation components further comprises:

a central database operation processor comprising a first execution engine to provide a first one of the database operation components;

a second database operation processor comprising a second execution engine to provide a second one of the database operation components;

a storage unit storing at least a portion of database tables and records connected to at least one of the central database operation processor and the second database operation processor; and

a data communications network connecting the central database operation processor to the second database operation processor.

52. The system of claim 51 wherein the central database operation processor is configured as a symmetric multi-processing system and the second database operation processor is configured as a component of a massively parallel processing system.

53. The system of claim 51 wherein the first execution engine or the second execution engine reconfigures an assigned execution subplan to redirect one or more operations to another execution engine.

54. The system of claim 51 wherein a system optimizer tracks performance results from the query and reallocates the database tables and records across the storage units in order to improve query execution.

55. The system of claim 54 wherein the reallocation comprises striping the database tables and records across the storage units.

56. The system of claim 54 wherein the reallocation comprises replicating the database tables and records across the storage units.

57. The system of claim 51 wherein at least one of the first execution engine or the second execution engine dynamically track performance results from the executing queries and modify a query execution plan to improve performance.

58. The system of claim 51 wherein the execution engines dynamically track system configuration and resource utilization from the executing queries and modify the query execution plan to improve system resource utilization.

59. The system of claim 51 wherein the dynamically re-optimized plan specifies that at least one of the first execution engine or the second execution engine are dynamically reconfigured to process different database operations.

60. A system as in claim 49 wherein the database catalog further includes, for at least a specified one of the database operation components, configuration information that specifies a set of preprogrammed database operations that can be carried out by the specified database processor component.

61. A system as in claim 60 wherein the query optimizer further determines a smaller set of performable database operations for the specified component selected from the larger set of preprogrammed database operations, and the query optimizer further selecting only from the set of performable database operations when determining portions of the query execution plan to be carried out for the specified component.

62. A system as in claim 61 wherein the set of performable database operations can be modified for the specified component while the query plan is being executed.

63. A system as in claim 62 wherein the query optimizer adjusts the generation of the re-optimized query plan to take into account the change in configuration of the specified component, with the re-optimized query plan thus using the modified set of performable database operations.

64. The system of claim 51 wherein the dynamically re-optomized plan specifies that at least one of the first execution engine or the second execution engine processes a different database operation than that respective execution engine executed in the query execution plan prior to its re-optimization.

65. A system for processing a query on a database comprising:

a plurality of database operation processor components, with at least two of the database operation processor components having different operation processing capabilities, and such that on more than one database operation processor component is capable of executing a selected database operation;

a database catalog comprising database table information, database operation processor component locale information, and database operation processor characteristic information;

a query optimizer, running on a first database operation processor component, for receiving the query and accessing the database catalog to develop a query execution plan, the query execution plan defining a list of database operations to carry out the query, the query execution plan also defining a respective database operation processor component locale for each such database operation;

wherein the first database operation processor component not only executes portions of the query execution plan designated to be handled itself but also forwards at least a selected portion of the query execution plan to a second database operation processor component having different operation processing capabilities than the first database operation processor component; and

wherein the second database operation processor component executes portions of the query execution plan designated for itself, and also forwards other selected portions of the query execution plan to a third database operation component for execution,

and further wherein the plurality of the database operation processor components further comprise;

a central database operation processor comprising a first execution engine to provide a first one of the database operation components;

a second database operation processor comprising a second execution engine to provide a second one of the database operation components;

a storage unit storing at least a portion of database tables and records connected to at least one of the central database operation processor and the second database operation processor; and

a data communications network connecting the central database operation processor to the second database operation processor

wherein either the first execution engine or the second execution engine dynamically reconfigures an assigned execution subplan to redirect one or more operations to the third execution engine, so that the query execution plan is re-optimized while still being executed.