Patent Application
20190205448
N/A
A relational database is a collection of data items organized as a set of formally described tables from which data can be easily accessed. A relational database system facilitates access to a relational database by receiving queries from users, applications, or other entities, executing such queries against the relational database to produce a results dataset, and returning the results dataset to the entities that submitted the queries. The queries may be represented using Structured Query Language (SQL) or another suitable database query language.
A relational database system may include a query optimizer and a query execution engine. The query optimizer may be configured to receive a query from a query-generating entity and to generate an efficient execution plan for the query. The query execution engine may be responsible for executing the plan in order to generate answers to the query. Query execution involves the use of operators that perform functions involving one or more tables.
Query execution is an important part of system performance. The way in which a query is executed can prevent or create problems regarding the quantity of space that needs to be utilized to execute the query and the amount of time required to retrieve the query results. Accordingly, benefits may be realized by improvements to the manner in which query execution plans are created and carried out.
In accordance with one aspect of the present disclosure, a relational database system may include a query optimizer and a query execution engine. The query optimizer may be configured to receive a query from a query-generating entity and to determine a sequence of operations for executing the query. The query execution engine may use real-time statistics to select physical operators for performing the sequence of operations.
The query optimizer may be configured to generate a high-level execution plan that specifies the sequence of operations. The high-level execution plan may be structured so that it does not indicate the physical operators that should be used to perform the sequence of operations.
The query execution engine may be configured to request and receive cost estimates from a plurality of available physical operators. The query execution engine may also be configured to provide real-time statistics to the plurality of available physical operators. The plurality of available physical operators may use the real-time statistics to produce the cost estimates. The query execution engine may also be configured to select the physical operators to perform the sequence of operations based at least in part on the cost estimates.
For each operation in the sequence of operations, the relational database system may include a pipeline of one or more physical operators that are capable of performing the operation, and the query execution engine may request and receive a separate cost estimate from each physical operator that is part of the pipeline.
In some embodiments, the physical operators may be selected based only on the cost estimates. Alternatively, the physical operators may also be selected based at least in part on past performance information associated with the physical operators. The past performance information may include error margins corresponding to prior execution of the physical operators.
The query execution engine may be additionally configured to determine an actual cost associated with execution of an operation by a physical operator, compare the actual cost with a cost estimate provided by the physical operator, and update an error margin associated with the physical operator based on the comparison.
The query optimizer may be additionally configured to use batched real-time statistics provided by the query execution engine to determine the sequence of operations.
In accordance with another aspect of the present disclosure, a relational database system may include a query optimizer and a query execution engine. The query optimizer may be configured to receive a query from a query-generating entity and to generate a high-level execution plan that specifies a sequence of operations for executing the query. The query execution engine may be configured to request and receive cost estimates from a plurality of available physical operators. The query execution engine may also be configured to provide real-time statistics to the plurality of available physical operators. The plurality of available physical operators may use the real-time statistics to produce the cost estimates. The query execution engine may also be configured to select physical operators to perform the sequence of operations based at least in part on the cost estimates.
In accordance with another aspect of the present disclosure, a method may include providing an application programming interface (API) that defines requirements for physical operators that are supported by a relational database system, loading a custom physical operator that implements the API into a pipeline of the relational database system, and requesting and receiving cost estimates from the custom physical operator and providing real-time statistics to the custom physical operator.
In some embodiments, the API may require a function that receives an input tuple and provides an output tuple, a class that defines the real-time statistics for current conditions of the relational database system, and a procedure that takes the class as input and returns a cost estimate.
This summary is provided to introduce a selection of concepts in a simplified form that are further described below in the detailed description. This summary is not intended to identify key features or essential features of the claimed subject matter, nor is it intended to be used as an aid in determining the scope of the claimed subject matter.
Additional features and advantages of implementations of the disclosure will be set forth in the description that follows, and in part will be apparent from the description, or may be learned by the practice of the teachings herein. The features and advantages of such implementations may be realized and obtained by means of the instruments and combinations particularly pointed out in the appended claims. These and other features will become more fully apparent from the following description and appended claims, or may be learned by the practice of such implementations as set forth hereinafter.
In order to describe the manner in which the above-recited and other features of the disclosure can be obtained, a more particular description will be rendered by reference to specific embodiments thereof which are illustrated in the appended drawings. For better understanding, similar reference numbers have been used for similar features in the various embodiments. Unless indicated otherwise, these similar features may have the same or similar attributes and serve the same or similar functions. Understanding that the drawings depict some examples of embodiments, the embodiments will be described and explained with additional specificity and detail through the use of the accompanying drawings in which:
The query-generating entity 102 is in electronic communication with a query processing system 106 and is operable to submit the query 104 thereto. Generally speaking, the query processing system 106 is configured to receive the query 104 from the query-generating entity 102, execute the query 104 against relational data store(s) 108 to obtain data responsive to the query 104, and return such data as query results 110 to the query-generating entity 102.
The query processing system 106 includes a query optimizer 112 and a query execution engine 114. As indicated above, the query optimizer 112 may be configured to receive the query 104 submitted by the query-generating entity 102 and generate an efficient execution plan 116 for the query 104. The query optimizer 112 may utilize various statistics 120 that have been accumulated by the relational database system 100 in order to generate the execution plan 116. The statistics 120 may be related to the characteristics of the relational data store(s) 108. For example, for a particular column, the statistics 120 may indicate approximately how many distinct values are in the column, approximately how many nulls are in the column, an approximate histogram of the values distribution in the column, and so forth. Once the execution plan 116 has been generated, the query execution engine 114 may carry out the execution plan 116 in order to generate the query results 110, and provide the query results 110 to the query-generating entity 102.
The query execution engine 114 implements a set of physical operators 118. A physical operator 118 takes as input one or more data streams and produces an output data stream. Some examples of physical operators 118 are sequential scan, index scan, nested loop join, hash join, merge join, plain aggregate, group aggregate, sorted aggregate, and hash aggregate.
In known query processing systems 106, the execution plan 116 indicates the specific physical operators 118 that should be used to carry out the execution plan 116. The query optimizer 112 passes the execution plan 116 to the query execution engine 114, which carries out the execution plan 116 using the physical operators 118 specified therein.
In some known query processing systems 106, the query execution engine 114 does not take into consideration real-time statistics (i.e., current conditions of the relational database system 100) when carrying out the execution plan 116. The statistics 120 that the query optimizer 112 uses to generate the execution plan 116 are not computed at run time. Thus, if conditions change after the time that the execution plan 116 is created, the execution plan 116 may be inefficient in view of the conditions at the time of execution. For example, suppose that the execution plan 116 indicates that a hash join operator (a physical operator 118 that performs a type of join operation) should be used. Although a hash join operator is typically faster than other types of join operators, it also requires a significant amount of memory. If overall memory usage is high at the time the execution plan 116 is being carried out by the query execution engine 114, then it might be better to use another type of join operator (e.g., a nested loop join operator, which is slower but is also less memory intensive) instead of a hash join operator. However, if the query execution engine 114 does not take real-time statistics into consideration when it carries out the execution plan 116, then the query execution engine 114 may use the less optimal hash join operator because that is what the execution plan 116 requires.
There are some query processing systems 106 in which the query execution engine 114 does take real-time statistics into consideration. In such query processing systems 106, however, if the execution plan 116 is deemed to be inefficient in view of the real-time statistics (i.e., in view of current conditions), the query execution engine 114 rebuilds the execution plan 116, either partially or wholly. This process of having the query optimizer 112 create the execution plan 116 and then having the query execution engine 114 rebuild the execution plan 116 is also inefficient.
In the relational database system 200 shown in
The query execution engine 214 receives the high-level execution plan 216 from the query optimizer 212. For each operation specified by the high-level execution plan 216, the query execution engine 214 requests cost estimates 222 from physical operators 218 that are capable of performing the operation. The query execution engine 214 also provides real-time statistics 224 to the physical operators 218. The real-time statistics 224 are different from the other statistics 220 that the query optimizer 212 utilizes to generate the high-level execution plan 216. The real-time statistics 224 reflect current conditions of the relational database system 200 when the query 204 is being executed, whereas the other statistics 220 do not. For example, if a cluster of computing systems is used to implement the relational database system 200, the real-time statistics 224 may indicate the extent to which the resources of the cluster are being used when the query 204 is being executed. The physical operators 218 use the real-time statistics 224 to produce the requested cost estimates 222.
A cost estimate 222 provided by a particular physical operator 218 in relation to a particular operation indicates how efficiently that physical operator 218 can perform the operation under current conditions, taking the real-time statistics 224 into account. For example, a cost estimate 222 may indicate how many computing resources (e.g., memory, processing capabilities, network bandwidth) the physical operator 218 would utilize to perform the operation. In some embodiments, each cost estimate 222 may be a metric between a minimum value (e.g., 0) and a maximum value (e.g., 1).
For each operation specified by the high-level execution plan 216, the query execution engine 214 selects a physical operator 218 to perform the operation based at least in part on the cost estimates 222 that it receives from the physical operators 218. For example, when selecting a physical operator 218 to perform a particular operation, the query execution engine 214 may select the physical operator 218 that provides the most favorable cost estimate 222 for performing that operation.
Alternatively, past performance information 226 associated with the physical operators 218 may also be taken into consideration. For example, if a particular physical operator 218 provides a highly favorable cost estimate 222 but has proven to be unreliable in the past, the query execution engine 214 may select a different physical operator 218 whose cost estimate 222 is deemed to be more reliable even if it is somewhat less favorable.
Thus, the relational database system 200 shown in
The query execution engine 314 receives the high-level execution plan 316 from the query optimizer 312. For each operation 328a-c specified by the high-level execution plan 316, the query execution engine 314 requests and receives cost estimates 322a-i from physical operators 318a-i that are capable of performing the operations 328a-c.
More specifically, for each operation 328a-c specified by the high-level execution plan 316, the relational database system 300 includes a pipeline 330a-c of one or more registered physical operators 318a-i that are capable of performing the operation 328a-c. Thus, in the depicted example there is a scan pipeline 330a that includes some registered physical scan operators 318a-b that are capable of performing the scan operation 328a, a join pipeline 330b that includes some registered physical join operators 318c-e that are capable of performing the join operation 328b, and an aggregate pipeline 330c that includes some registered physical aggregate operators 318f-i that are capable of performing the aggregate operation 328c. In particular, the scan pipeline 330a includes a sequential scan operator 318a and an index scan operator 318b. The join pipeline 330b includes a nested loop join operator 318c, a hash join operator 318d, and a merge join operator 318e. The aggregate pipeline 330c includes a plain aggregate operator 318f, a group aggregate operator 318g, a sorted aggregate operator 318h, and a hash aggregate operator 318i. Of course, the specific physical operators 318a-i shown in
For each operation 328a-c specified by the high-level execution plan 316, the query execution engine 314 may request and receive a separate cost estimate 322a-i from each physical operator 318a-i that is part of the corresponding pipeline 330a-c. Thus, for the scan operation 328a, the query execution engine 314 may request and receive a cost estimate 322a from the sequential scan operator 318a and a cost estimate 322b from the index scan operator 318b. Similarly, for the join operation 328b, the query execution engine 314 may request and receive a cost estimate 322c from the nested loop join operator 318c, a cost estimate 322d from the hash join operator 318d, and a cost estimate 322e from the merge join operator 318e. Also, for the aggregate operation 328c, the query execution engine 314 may request and receive a cost estimate 322f from the plain aggregate operator 318f, a cost estimate 322g from the group aggregate operator 318g, a cost estimate 322h from the sorted aggregate operator 318h, and a cost estimate 322i from the hash aggregate operator 318i.
The query execution engine 314 may provide real-time statistics 324a-c to the physical operators 318a-i in each pipeline 330a-c, and the physical operators 318a-i may use the real-time statistics 324a-c to produce the cost estimates 322a-i. For example, suppose that the real-time statistics 324a-c indicate that the amount of memory being used by the relational database system 300 is relatively high at the time the query 304 is being executed. In view of this information about high memory usage, the cost estimate 322d produced by the hash join operator 318d (which generally requires a considerable amount of memory) may be less favorable than the cost estimates 322c, 322e produced by the other physical operators 318c, 318e in the join pipeline 330b.
The cost estimates 322a-i may influence the selection of physical operators 318a-i. Continuing with the previous example, if the cost estimates 322c-e produced by the physical operators 318c-e in the join pipeline 330b indicate that the hash join operator 318d cannot perform the join operation 328b as efficiently as other physical operators 318c, 318e in the join pipeline 330b under current conditions, then the query execution engine 314 may select a different physical join operator 318c, 318e, other than the hash join operator 318d, to perform the join operation 328b. For example, the query execution engine 314 may select the nested loop join operator 318c to perform the join operation 328b. In a similar way, the cost estimates 322a-b produced by the physical operators 318a-b in the scan pipeline 330a may influence which of these physical operators 318a-b is selected to perform the scan operation 328a, and the cost estimates 322f-i produced by the physical operators 318f-i in the aggregate pipeline 330c may influence which of these physical operators 318f-i is selected to perform the aggregate operation 328c.
In some embodiments, the query execution engine 414 may simply select the physical operator 418a-b that provides the most favorable cost estimate 422a-b for performing the corresponding operation. For example, if the cost estimate 422a provided by the sequential scan operator 418a is more favorable than the cost estimate 422b provided by the index scan operator 418b, the query execution engine 414 may simply select the sequential scan operator 418a to perform the scan operation without taking other factors into consideration.
Alternatively, the query execution engine 414 may be configured to consider one or more other factors, in addition to the cost estimates 422a-b, when selecting a physical operator 418a-b. In some embodiments, the query execution engine 414 may consider past performance information 426 associated with the physical operators 418a-b. The past performance information 426 may include error margins 432a-b corresponding to prior execution of the physical operators 418a-b.
The error margin 432a-b associated with a physical operator 418a-b may be a measure of the accuracy of previous cost estimates 422a-b provided by that physical operator 418a-b. In other words, the error margin 432a-b associated with a physical operator 418a-b may indicate how closely previous cost estimates 422a-b provided by that physical operator 418a-b corresponded to the actual cost incurred by that physical operator 418a-b when it performed the corresponding operations.
Suppose, for example, that a physical operator 418a-b provides a highly favorable cost estimate 422a-b for performing a particular operation. In other words, the physical operator 418a-b estimates that it will be able to perform the operation very efficiently. If the physical operator 418a-b is actually able to perform the operation as efficiently as it estimated (or at least relatively close), then the error margin 432a-b for that operation is relatively small. If, however, the physical operator 418a-b is not able to perform the operation efficiently and the actual cost for performing the operation greatly exceeds what was estimated, then the error margin 432a-b for that operation is relatively large.
The query execution engine 414 may keep track of error margins 432a-b associated with physical operators 418a-b over time, and use the error margins 432a-b in connection with the selection of physical operators 418a-b. Suppose, for example, that the cost estimate 422a provided by the sequential scan operator 418a is only slightly more favorable than the cost estimate 422b provided by the index scan operator 418b, but the error margin 432a associated with the sequential scan operator 418a is considerably higher than the error margin 432b associated with the index scan operator 418b. In this case, the query execution engine 414 may select the index scan operator 418b to perform the scan operation, even though the index scan operator 418b did not produce the most favorable cost estimate 422b.
In some embodiments, if the error margin 432a-b associated with a particular physical operator 418a-b exceeds a particular threshold, then the query execution engine 414 may not select the physical operator 418a-b under any circumstances, regardless of how favorable its cost estimates 422a-b are. In other words, physical operators 418a-b may reach a point where they may be deemed too unreliable to be used.
The query execution engine 514 may maintain an error margin 532a for each physical operator 518 that is included in the relational database system 500. When the query execution engine 514 has compared the actual cost 534 of an operation performed by a physical operator 518 to the cost estimate 522 for the operation that the physical operator 518 provided, the query execution engine 514 may generate an updated error margin 532b for that physical operator 518. The query execution engine 514 may then use this updated error margin 532b the next time that the physical operator 518 is being considered in relation to performing another operation.
In addition, the query optimizer 612 may communicate with physical operators 618 when it is generating the high-level execution plan 616. For example, the query optimizer 612 may request cost estimates 622 from the physical operators 618 based on the batched real-time statistics 624. The query optimizer 612 may use the cost estimates 622 when selecting the sequence of operations for the high-level execution plan 616. More specifically, the query optimizer 612 may select the sequence of operations for the high-level execution plan 616 so as to minimize the overall cost of the high-level execution plan 616. In this way, a factor of real-time goal optimization may be introduced into the query optimizer 612.
For each operation specified by the high-level execution plan 216, the query execution engine 214 requests 706 cost estimates 222 from physical operators 218 that are capable of performing the operation. The query execution engine 214 also provides 708 real-time statistics 224 to the physical operators 218. The query execution engine 214 selects 710 physical operators 218 to perform the operations specified by the high-level execution plan 216 based at least in part on the cost estimates 222 that it receives from the physical operators 218.
Referring now to
The present disclosure proposes an improved approach to defining and using physical operators 818a-b. In accordance with the present disclosure, an application programming interface (API) 836 may be defined for a relational database system 800. The API 836 may include a set of requirements 838 for physical operators 818a-b for the relational database system 800. The relational database system 800 may be configured so that it can use any physical operators 818a-b that implement the API 836 (i.e., that satisfy the requirements 838 specified by the API 836).
In some embodiments, the API 836 may include a requirement 838 to provide a function 840 that receives a tuple as input and provides a tuple as output. The API 836 may also include a requirement 838 to provide a class 842 that defines the real-time statistics 824a for current conditions of the relational database system 800. The API 836 may also include a procedure 844 (which alternatively may be referred to as a method) that takes the class 842 as input and returns a cost estimate 822a.
Thus, in accordance with the present disclosure, it is not necessary to make internal changes to the relational database system 800 (such as modifying the query optimizer 812 and query execution engine 814) in order to create and use a custom physical operator 818a. Instead, once a custom physical operator 818a has been defined that implements the API 836, the custom physical operator 818a may be loaded into a pipeline 830 of the relational database system 800 and used in the same way as other physical operators 818b that have been loaded into the pipeline 830. In other words, the query execution engine 814 may request and receive cost estimates 822a from the custom physical operator 818a in the same way that it requests and receives cost estimates 822b from other physical operators 818b. The query execution engine 814 may also provide real-time statistics 824a to the custom physical operator 818a in the same way that it provides real-time statistics 824b to the other physical operators 818b. The custom physical operator 818a may use the real-time statistics 824a to produce its cost estimates 822a.
The computer system 1000 includes a processor 1001. The processor 1001 may be a general purpose single- or multi-chip microprocessor (e.g., an Advanced RISC (Reduced Instruction Set Computer) Machine (ARM)), a special purpose microprocessor (e.g., a digital signal processor (DSP)), a microcontroller, a programmable gate array, etc. The processor 1001 may be referred to as a central processing unit (CPU). Although just a single processor 1001 is shown in the computer system 1000 of
The computer system 1000 also includes memory 1003. The memory 1003 may be any electronic component capable of storing electronic information. For example, the memory 1003 may be embodied as random access memory (RAM), read-only memory (ROM), magnetic disk storage media, optical storage media, flash memory devices in RAM, on-board memory included with the processor, erasable programmable read-only memory (EPROM), electrically erasable programmable read-only memory (EEPROM) memory, registers, and so forth, including combinations thereof.
Instructions 1005 and data 1007 may be stored in the memory 1003. The instructions 1005 may be executable by the processor 1001 to implement some or all of the methods disclosed herein. Executing the instructions 1005 may involve the use of the data 1007 that is stored in the memory 1003. When the processor 1001 executes the instructions 1005, various portions of the instructions 1005a may be loaded onto the processor 1001, and various pieces of data 1007a may be loaded onto the processor 1001.
Any of the various examples of modules and components described herein (such as the query optimizer 212 and the query execution engine 214) may be implemented, partially or wholly, as instructions 1005 stored in memory 1003 and executed by the processor 1001. Any of the various examples of data described herein (such as the high-level execution plan 216, the cost estimates 222, the real-time statistics 224, and the query results 210) may be among the data 1007 that is stored in memory 1003 and used during execution of the instructions 1005 by the processor 1001.
A computer system 1000 may also include one or more communication interfaces 1009 for communicating with other electronic devices. The communication interfaces 1009 may be based on wired communication technology, wireless communication technology, or both. Some examples of communication interfaces 1009 include a Universal Serial Bus (USB), an Ethernet adapter, a wireless adapter that operates in accordance with an Institute of Electrical and Electronics Engineers (IEEE) 802.11 wireless communication protocol, a Bluetooth® wireless communication adapter, and an infrared (IR) communication port.
A computer system 1000 may also include one or more input devices 1011 and one or more output devices 1013. Some examples of input devices 1011 include a keyboard, mouse, microphone, remote control device, button, joystick, trackball, touchpad, and lightpen. Some examples of output devices 1013 include a speaker, printer, etc. One specific type of output device that is typically included in a computer system is a display device 1015. Display devices 1015 used with embodiments disclosed herein may utilize any suitable image projection technology, such as liquid crystal display (LCD), light-emitting diode (LED), gas plasma, electroluminescence, or the like. A display controller 1017 may also be provided, for converting data 1007 stored in the memory 1003 into text, graphics, and/or moving images (as appropriate) shown on the display device 1015.
The various components of the computer system 1000 may be coupled together by one or more buses, which may include a power bus, a control signal bus, a status signal bus, a data bus, etc. For the sake of clarity, the various buses are illustrated in
The techniques described herein may be implemented in hardware, software, firmware, or any combination thereof, unless specifically described as being implemented in a specific manner. Any features described as modules, components, or the like may also be implemented together in an integrated logic device or separately as discrete but interoperable logic devices. If implemented in software, the techniques may be realized at least in part by a non-transitory processor-readable storage medium comprising instructions that, when executed by at least one processor, perform one or more of the methods described herein. The instructions may be organized into routines, programs, objects, components, data structures, etc., which may perform particular tasks and/or implement particular data types, and which may be combined or distributed as desired in various embodiments.
The steps and/or actions of the methods described herein may be interchanged with one another without departing from the scope of the claims. In other words, unless a specific order of steps or actions is required for proper operation of the method that is being described, the order and/or use of specific steps and/or actions may be modified without departing from the scope of the claims.
The term “determining” encompasses a wide variety of actions and, therefore, “determining” can include calculating, computing, processing, deriving, investigating, looking up (e.g., looking up in a table, a database or another data structure), ascertaining and the like. Also, “determining” can include receiving (e.g., receiving information), accessing (e.g., accessing data in a memory) and the like. Also, “determining” can include resolving, selecting, choosing, establishing and the like.
The terms “comprising,” “including,” and “having” are intended to be inclusive and mean that there may be additional elements other than the listed elements. Additionally, it should be understood that references to “one embodiment” or “an embodiment” of the present disclosure are not intended to be interpreted as excluding the existence of additional embodiments that also incorporate the recited features. For example, any element or feature described in relation to an embodiment herein may be combinable with any element or feature of any other embodiment described herein, where compatible.
The present disclosure may be embodied in other specific forms without departing from its spirit or characteristics. The described embodiments are to be considered as illustrative and not restrictive. The scope of the disclosure is, therefore, indicated by the appended claims rather than by the foregoing description. Changes that come within the meaning and range of equivalency of the claims are to be embraced within their scope.
