Cloud-based query workload optimization

Abstract

A cloud-based query workload optimization system disclosed herein: receives query logs from various query engines to a cloud data service; extracts various query entities from the query logs; parses the query entities to generate a set of common workload features; generates an intermediate representation of the query workload, wherein the intermediate representation is agnostic to the language of multiple queries; identifies multiple workload patterns based on the intermediate representation of the query workload; classifies the workload into one or more workload type categories based on the workload patterns and the workload features; and selects an optimization scheme based on the category of the workload pattern.

Description

Background Art

[0001] With the growth of the internet and cloud-based services, more and more companies are moving their data to cloud-based platforms. Cloud computing is changing the way users interact with databases. In a typical cloud-based data service, numerous databases can coexist on the same cloud-based server. In this environment, several different types of databases can coexist, and each of these databases can receive different types of query workloads. Database administrators (DBAs) have traditionally been responsible for optimizing on-site database workloads. However, with the rise of cloud data services, where cloud providers offer fully managed data processing capabilities, the DBA role has completely disappeared. Summary of the Invention

[0002] The implementation described herein discloses a system for optimizing cloud-based query workloads. In one implementation, the cloud-based query workload optimization system: receives query logs from various query engines to a cloud data service; extracts various query entities from the query logs; parses the query entities to generate a set of common workload features; generates an intermediate representation of the query workload, wherein the intermediate representation is agnostic to the language of the multiple queries; identifies multiple workload patterns based on the intermediate representation of the query workload; classifies the workload into one or more workload type categories based on the workload patterns and the workload features; and selects an optimization solution based on the category of the workload pattern.

[0003] 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 to limit the scope of the claimed subject matter.

[0004] Other implementations are also described and described herein. BRIEF DESCRIPTION OF THE DRAWINGS

[0005] Figure 1 An example implementation of the cloud-based query workload optimization system disclosed herein is illustrated.

[0006] Figure 2 An alternative block diagram of the cloud-based query workload optimization system disclosed herein is illustrated.

[0007] Figure 3 Illustrated are example relationships of various entities in a data repository of the cloud-based query workload optimization system disclosed herein.

[0008] Figure 4 An example workload intermediate representation of the cloud-based query workload optimization system disclosed herein is illustrated.

[0009] Figure 5 Illustrated are example workload patterns detected by the cloud-based query workload optimization system disclosed herein.

[0010] Figure 6 Illustrated are example operations of the cloud-based query workload optimization system disclosed herein.

[0011] Figure 7 Alternative example operations of the cloud-based query workload optimization system disclosed herein are illustrated.

[0012] Figure 8 An example computing system is illustrated that can be used to implement the described techniques. DETAILED DESCRIPTION

[0013] Database administrators (DBAs) have traditionally been responsible for optimizing on-premises database workloads. However, with the rise of cloud data services, where cloud providers offer fully managed data processing capabilities, the DBA role is disappearing. At the same time, workload optimization has become more important for reducing total operating costs and making data processing economically viable in the cloud. The technology disclosed herein provides workload optimization in the context of these emerging cloud-based data services. Specifically, a workload optimization platform for a cloud query engine disclosed herein includes: representing query workloads in a manner that is agnostic to the query engine and general enough to describe a variety of workloads; classifying workload patterns; optimizing the corresponding workloads in each category; and providing for adding workload awareness to the query engine via the concept of query annotations that are applied to the query engine at compile time.

[0014] The technology disclosed herein addresses the technical challenges associated with cloud-based databases, requiring technical solutions for database management and optimization. Cloud-based databases provide data services to a large number of clients, where diverse database structures may be used. In this environment, query workload optimization is difficult due to differences in query languages, query plans, and database structures. The technology disclosed herein provides for generating database-agnostic query representations, which allows for optimization of query workloads and provides feedback to developers and users of cloud-based databases.

[0015] While various implementations disclosed herein are implemented to optimize query workloads on cloud-based databases and provide workload awareness to query engines for queries on cloud-based databases, these implementations can also provide optimizations to private data centers that support a large number of queries from several different query engines. For example, such a private data center can be implemented on a single server, a collection of servers, a private cloud, or a public cloud.

[0016] Figure 1 The diagram illustrates an example implementation of the cloud-based query workload optimization system 100 disclosed herein. Specifically, the cloud-based query workload optimization system 100 may include a plurality of databases DS1 122, DS2 124, ..., DSn 126 on a cloud-based data server 120. For example, database DS1 122 may be an SQL database, database 124 may be a DB2 database, database DS3 may be a FileMaker database, and so on. The data server 120 may serve various developers 110 who manage these individual databases, as well as various users 112, 114, 116 who use databases DS1 through DSn.

[0017] The data server 120 includes a workload optimization engine 102 that analyzes data query logs for databases 122 to 126, generates optimization solutions based on the analysis of the databases, and provides feedback on the selected optimization solutions to the developers 110 and users 112 to 116. In one implementation, the workload optimization engine 102 accesses data query logs for databases 122 to 124 to analyze workloads 132 to 136 for these databases. The workload optimization engine 102 may include a workload representation module 104, a workload optimization module 106, and a workload feedback module 108. The functionality of each of these modules 104 to 108 is described below. Figures 2 to 8 Further described in .

[0018] The workload optimization engine 102 may use a computing device (such as Figure 8 The workload optimization engine 102 may be implemented on a computing device disclosed in , wherein one or more of the modules and their associated operations may be implemented by computer-executable instructions stored in a computer-readable medium. Although in the illustrated implementation, the workload optimization engine 102 is illustrated as being implemented on a cloud-based data server 120, in alternative implementations, the workload optimization engine 102 may be implemented on a different server that is communicatively connected to the cloud-based data server 120.

[0019] The workload representation module 104 takes as input logs from the query engines 140 to 146 related to one or more of the databases 122 to 126 and related runtime information from the underlying platform (job scheduler, job manager, resource manager, storage service). The workload representation module 104 can process these logs and generate one or more intermediate workload representations that are common between the workloads 132 to 136 and the query engines 140 to 146.

[0020] These intermediate representations can be input to the engine-agnostic optimization algorithms of the workload optimization module 106. The workload optimization module 106 can be configured to mine the query workload to generate various query patterns and run the optimization algorithms to tune those query patterns. This identification of patterns and optimization for these patterns makes workload optimization practical and less open-ended. In one implementation, each pattern class in the pattern class can be associated with one or more optimization algorithms.

[0021] The workload feedback module 108 collects the output of the workload optimization module 106 and converts it into actionable feedback that can be consumed by users 112 to 116 and developers 110 in the form of insights and recommendations. Alternatively, the workload feedback module 108 can feed the output of the workload optimization module 106 back to the query engines 140 to 146 for self-tuning. Specifically, for self-tuning, the feedback is encoded as query annotations and loaded onto a feedback server.

[0022] The workload optimization engine 102 provides an interface for using query logs, which can be implemented for different query engines 140 to 146. In one implementation, the workload optimization engine 102 provides an extensible infrastructure, where one or more instruments, parsers, patterns, optimizations, and feedback can be added based on evolving workloads 132 to 136. In addition, the workload optimization engine 102 can also provide an implementation library for each of one or more instruments, parsers, patterns, and optimizations, which can serve as a starting point for covering more scenarios.

[0023] Figure 2 FIGURE 2 illustrates an alternative block diagram of a cloud-based query workload optimization system 200 disclosed herein. Specifically, the workload optimization system 200 provides Figure 1 An example implementation of the workload optimization engine 102 disclosed in

[0026] is provided. The workload optimization system 200 may include a workload-aware query engine 202, which includes query engines QE1, QE2, ..., QEn. The query engine 202 may receive queries from various users of a cloud-based database. Various query logs 204 (also referred to as workload logs 204) from the query engine 202 are fed into a workload representation module 210.

[0024] The implementation of the workload representation module 210 provides a mechanism for capturing query plan characteristics 212 from the query log 204. In one implementation, the query plan characteristics 212 can be recorded in the query log 204 as signatures. Specifically, the signature can capture the internal optimizer state corresponding to different query plan characteristics into a fixed-size hash and output the fixed-size hash as part of the query log 204. For example, the signature can have different types to capture different query plan characteristics. In one implementation, the signature can be composed to identify combined characteristics so that these combined characteristics can be used across multiple query engines QE1 to QEn. Table 1 below illustrates example signatures, which depend on whether the base subgraph, subgraph parameters, and subgraph inputs of a given operator are hashed.

[0025] Table 1

[0026] Signature Type Operators Subgraph parameter enter strict √ √ √ √ Circular √ √ √ Parameters √ √ √ Semantic √ √ domain √ √ kind √

[0027] In one implementation of the workload representation module 210, an application programming interface (API) may be provided to obtain query plan sub-expressions from a query engine to generate a signature as output.

[0028] The workload representation module 210 also includes a feature repository 214 that stores features from the query log 204. Specifically, the workload representation module 210 parses the query log 204 to generate a common feature set that is stored in the feature repository 214. For example, the workload representation module 210 extracts one or more relevant entities from the query log 204 (examples of such entities are described below). Figure 3 Specifically, information about a query entity may include query metadata, query plans, and runtime statistics—collectively referred to as a query trace.

[0029] Sample query metadata can include tags and parameters provided with the query, user and account names, query submission, start and end times, available resources, etc. Sample query plans include the logical (input, analysis, optimization), physical, and execution plans for the query. Sample runtime statistics can include row counts, latency, CPU time, I / O time, memory usage, etc.

[0030] The workload representation module 210 parses query traces from the various query logs 204. In one implementation, several parsers may be used, with each parser configured to parse query traces from a specific type of query engine (such as a parser for XML queries, a parser for JSON queries, a parser for plain text queries, etc.). These parsers output a common set of workload features that are stored in a query feature repository 214. In one implementation, the query feature repository 214 has an extensible design to add more query engines, extract additional pieces of information from the query 204 logs, add new parsers for custom query formats, and add updated query workload features as they become available.

[0031] The workload representation module 210 uses the entities in the query feature repository 214 to generate an intermediate workload representation 216. Specifically, the intermediate workload representation 216 is generalized between various query processors so that they can be used to run various optimization algorithms. An example of the intermediate workload representation 216 is shown below. Figure 4 This is discussed in more detail in .

[0032] The intermediate workload representation 216 is analyzed to identify query patterns 218. For example, such query patterns may include loop query patterns, similarity query patterns, dependency query patterns, etc. Example structures of these query patterns 218 are shown below. Figure 5 Once the query pattern 218 is identified, the workload optimization module 220 selects an optimization type based on the query pattern 218 .

[0033] Specifically, if query pattern 218 is identified as a loop query pattern, learning optimization algorithm 224 is used to optimize the workload. Similarly, if query pattern 218 is identified as a similarity query pattern, multiple query optimization algorithm 222 is used to optimize the workload. On the other hand, if query pattern 218 is identified as a dependency query pattern, one of dependency-driven optimization algorithms 226 is used to optimize the workload.

[0034] Examples of learning optimization algorithms 224 include models that analyze cyclic workloads and provide them as feedback to a query engine, such as, for example, a neural network. Examples of multi-query optimization algorithms 222 may involve caching data at various layers in a data service to serve multiple query engines without having to execute a query multiple times. On the other hand, a dependency-driven query optimization algorithm 226 may include calculating the relative importance of queries in a data pipeline and scheduling those queries based on their importance. Another example of a dependency-driven query optimization algorithm 226 may consist of a pipeline of queries with data dependencies between the queries, so that the output of a producer query can be used in a subsequent consumer query.

[0035] The workload optimization module 220 outputs feedback 230 that can be used by users 204 and developers 206 for various actions. Feedback 230 can include insights 232, recommendations 234, and self-tuning 236. Specifically, insights 232 can be summaries and reports about the intermediate representation of the workload to help users understand their workload and take any appropriate tuning actions based on their interpretation. An example of such a summary is a summary about the intermediate representation of sub-expressions. On the other hand, recommendations 234 are outputs of the optimization algorithm that are provided as hints to the user. The user can apply these hints using the tuning knob provided by the query engine. For example, such hints can include row count hints, operator algorithm hints, and forced join order hints.

[0036] Finally, self-tuning 236 may include encoding workload optimization decisions into a query annotation 238 in a format that is extensible to add more optimizations and can be integrated with multiple query engines. Query annotations 238 provide an interface between workload optimization feedback and changes in the query engine to consume the feedback. In one implementation, query annotations 238 may include a signature, an action, and a set of parameters. Here, a signature is a query plan identifier as described in Table 1 above. An action is the name of the self-tuning to be performed by the query engine, such as (for example) a configuration to be applied, a tuning knob to be set, a query optimizer rule to be invoked, etc. Parameters provide the information required for the action, such as (for example) a configuration value or an optimizer rule parameter. It should be noted that in some cases, a signature can have several actions, or a given action can be applied to several signatures. Thus, query annotations 238 use parameters to specify self-tuning actions and are conditional on query plan signatures.

[0037] Query annotations 238 are fed back to query engine 202 by feedback service engine 240 and can be consumed by query engine 202 during compilation. In one implementation, query annotations 238 are output to a file in a cloud storage location. In this implementation, feedback service engine 240 periodically polls this file for new query annotations 238 and batch-loads any new query annotations 238 into query engine 202. Alternatively, feedback service engine 240 provides an API to search for query annotations 238 by their signature, enabling feedback service engine 240 to return all query annotations 238 for a given signature. Furthermore, each query annotation in query annotations 238 can be associated with a customer account, enabling queries from a customer account to only load those query annotations 238 associated with that customer account. In an alternative implementation, feedback service engine 240 allows tags to be added to query annotations 238 and batch-searches all annotations for a given tag(s). An example of such a tag might be a recurring job name, such as a recurring job that appears with a similar name each time. For such a job, the query engine may load all query annotations 238 corresponding to the loop job name in a single lookup.

[0038] An implementation of the feedback service engine 240 provides an index of the signatures and tags of query annotations 238 to facilitate batch lookups. Furthermore, the feedback service engine 240 batch-loads annotations for customer accounts, eliminating the need to incrementally update the index. Because many queries can have common subexpressions and, therefore, common query annotations 238, in another implementation, the feedback service engine 240 caches query annotations 238 in the application layer. In another implementation, when new annotations for the same signature and action become available, the feedback service engine 240 expires the query annotations 238, causing the new query annotations 238 to overwrite older query annotations 238.

[0039] Providing query annotations 238 via the feedback service engine 240 makes the query engines 202 workload-aware, so that they learn from past workloads and consider optimized actions for future queries. An example implementation of a workload-aware query engine is provided below. Figure 8 It was made public.

[0040] Figure 31 illustrates example entity relationships 300 for various entities in a feature repository for a cloud-based query workload optimization system disclosed herein. Specifically, entity relationships 300 illustrate common workload features output by a parser that parses query traces from a query log. As illustrated in entity relationships 300, a workload entity 302 is associated with an application entity 304. Thus, workload 302 may be for an application 304, such as a SQL-based application, a DB2-based application, or the like. Application 304 is associated with queries 308, metadata 306, and aggregate metrics 320 generated from the application. Each of application 304 and query 308 may have associated metadata 306. Query 308 is also associated with a query plan. Furthermore, query plan 310 has query operators 312, and the operators have query operator instances 314.

[0041] like Figure 3 As shown in FIG, each of application 304, query 308, query plan 310, query operator 312, and query operator instance 314 is associated with its respective aggregate metric 320a-320e.

[0042] Figure 4 An example intermediate workload representation 400 of the cloud-based query workload optimization system disclosed herein is illustrated. Specifically, the intermediate workload representation 400 includes a plan intermediate representation 410, a sub-expression intermediate representation 412, and an operator instance intermediate representation 414. Specifically, the intermediate workload representation 400 enables workload entities (such as Figure 3 ) for more efficient processing by various optimization applications. For example, an optimization application can create and use one or more of these intermediate workload representations 400 depending on the granularity of information it requires. The intermediate workload representations 400 can be stored using the same set of connectors used to access the query log 204 and can be used to quickly draw insights and build optimizations for query workloads. In addition, the intermediate workload representations 400 can also be shared across multiple workload optimization applications. For example, the sub-expression intermediate representation 412 can be used to find sub-expressions to implement, learn cardinalities on loop workloads, mine physical design hints, etc. In one implementation, the intermediate workload representations 400 are summarized across query engines.

[0043] Figure 55. Example workload patterns 500 detected by the cloud-based query workload optimization system disclosed herein are illustrated. Specifically, a recurring query pattern 512 represents a repetitive query workload where the same query is periodically executed with new inputs and parameters. Recurring query pattern 512 indicates the predictive nature of the workload. On the other hand, a similarity query pattern 514 represents the similarity between queries on the same data. This occurs when queries are written by multiple users accessing the same set of inputs in the cloud infrastructure. Finally, a dependency query pattern 516 illustrates a data pipeline where the output of one query (from a previous recurrence interval) is consumed by a subsequent query (in a subsequent recurrence interval).

[0044] Figure 6 The diagram illustrates example operations 600 of the cloud-based query workload optimization system disclosed herein. Operation 602 receives query logs for a cloud-based data service from various query engines. For example, the cloud-based data service may have databases based on SQL, DB2, etc., and operation 602 receives queries for one or more of these databases. Query logs are generated over time based on queries to these databases. Subsequently, operation 604 extracts query entities from the query logs. Examples of various query entities are shown above. Figure 3 This is discussed in more detail in .

[0045] Operation 606 parses the query entities to generate a set of common workload features and the relationships between these features. The workload features may include, for example, the count and cardinality of each operator for the query, the size of the data returned for the query, etc. Operation 608 generates an intermediate workload representation that is agnostic to the query engine and general enough to describe various workloads. Specifically, operation 608 generates a more efficient workload intermediate representation (IR) that generalizes across query processors and can be used to run various optimization algorithms on top. Example intermediate workload representations 410 to 414 are shown above in Figure 4 As explained in , these example intermediate workloads represent denormalizing the workload entities for more efficient processing.

[0046] Operation 610 uses the intermediate workload representation to identify one or more workload patterns. Example workload patterns can be cyclic patterns, similarity patterns, dependency patterns, etc., as described above in Figure 5As described in . Operation 612 classifies the workload into various workload categories based on the workload pattern and based on the classification, and operation 614 selects an optimization scheme based on the workload category. Thus, for example, a workload classified as having a cyclic pattern can be optimized based on a learning optimization scheme, a workload classified as having a similarity pattern can be optimized based on a multi-query optimization scheme, and a workload classified as having a dependency pattern can be optimized based on a dependency-driven optimization scheme. Operation 616 optimizes the workload using the selected optimization scheme. Subsequently, operation 618 generates query annotations for feedback and actions of users and developers of the cloud-based database. The feedback engine 240 that provides feedback to the various workload-aware query engines is described above with respect to Figure 2 Disclosed in more detail.

[0047] Figure 7 An example implementation of a workload-aware query engine system 700 is illustrated. A query engine 702 can be configured for one or more queries from a user using various configuration rules 704. The query engine 702 is configured to learn from past workloads and take optimization actions for future queries. In the illustrated implementation, the query engine 702 has a mechanism for loading query annotation feedback for past workloads from a feedback service 740 and the ability to use an optimizer 714 to take optimization decisions based on this feedback.

[0048] Specifically, the feedback service 740 is configured to load the query annotations 832 into the feedback loop and action module 710, which includes the compiler 712 and the optimizer 714. In one implementation, the optimizer 714 looks up the query annotations from the query annotations 832 for each signature in the optimizer 714. Alternatively, relevant signatures using tags defined in the feedback service 740 are preloaded into the compiler 712. Alternatively, for smaller, self-contained applications, all available signatures are preloaded into the compiler 712 or optimizer 714 for later use as applicable.

[0049] In one implementation, query annotations 832 are uploaded to feedback service 740 using HTTP requests. Alternatively, a file containing query annotations 832 can be loaded directly into feedback loop and action module 710 for debugging purposes. In addition, developers of query engine 710 can also create and test new annotation feedback without having to go through feedback service 740. Optimization decisions can be made at compiler 712, optimizer 714, scheduler 716, or runtime 718. Accordingly, each of these stages can communicate with workload representation module 820, which stores an intermediate representation of the workload used by workload optimization module 730.

[0050] Figure 8 Illustrated is an example system 500 that may be used to implement the multimodal video identification system disclosed herein. Figure 8 Example hardware and operating environments for implementing the described techniques include computing devices, such as general-purpose computing devices in the form of computers 20, mobile phones, personal data assistants (PDAs), tablets, smart watches, game remotes, or other types of computing devices. Figure 8 In an implementation, for example, computer 20 includes a processing unit 21, a system memory 22, and a system bus 23 that operatively couples various system components, including the system memory, to processing unit 21. There may be only one processing unit 21 or there may be more than one processing unit 21, such that the processor of computer 20 includes a single central processing unit (CPU) or multiple processing units, commonly referred to as a parallel processing environment. Computer 20 may be a conventional computer, a distributed computer, or any other type of computer; implementations are not limited thereto.

[0051] The system bus 23 may be any of several types of bus structures, including a memory bus or memory controller, a peripheral bus, a switched fabric, a point-to-point connection, and a local bus using any of a variety of bus architectures. The system memory may also be referred to simply as memory, and includes read-only memory (ROM) 24 and random access memory (RAM) 25. A basic input / output system (BIOS) 26, which contains the basic routines that help transfer information between elements within the computer 20, such as during startup, is stored in the ROM 24. The computer 20 also includes a hard disk drive 27 for reading from and writing to a hard disk (not shown), a magnetic disk drive 28 for reading from or writing to a removable magnetic disk 29, and an optical disk drive 30 for reading from or writing to a removable optical disk 31, such as a CD ROM, DVD, or other optical media.

[0052] The hard disk drive 27, magnetic disk drive 28, and optical disk drive 30 are connected to the system bus 23 by a hard disk drive interface 32, a magnetic disk drive interface 33, and an optical drive interface 34, respectively. The drives and their associated tangible, computer-readable media provide non-volatile storage of computer-readable instructions, data structures, program modules, and other data for the computer 20. Those skilled in the art will appreciate that any type of tangible, computer-readable media may be used in the exemplary operating environment.

[0053] Several program modules may be stored on the hard drive 27, magnetic disk 28, optical disk 30, ROM 24, or RAM 25 (including an operating system 35, one or more application programs 36, other program modules 37, and program data 38). A user may generate reminders on the personal computer 20 through input devices such as a keyboard 40 and a pointing device 42. Other input devices (not shown) may include a microphone (e.g., for voice input), a camera (e.g., for a natural user interface (NUI)), a joystick, a game controller, a satellite dish, a scanner, and the like. These and other input devices are typically connected to the processing unit 21 via a serial port interface 46 coupled to the system bus 23, but may be connected via other interfaces such as a parallel port, a game port, or a universal serial bus (USB) (not shown). A monitor 47 or other type of display device is also connected to the system bus 23 via an interface such as a video adapter 48. In addition to a monitor, computers typically include other peripheral output devices (not shown), such as speakers and printers.

[0054] The computer 20 can operate in a network environment using logical connections to one or more remote computers, such as remote computer 49. These logical connections are implemented by a communication device coupled to the computer 20 or a portion thereof; the implementation is not limited to a particular type of communication device. The remote computer 49 can be another computer, a server, a router, a network PC, a client, a peer device, or other public network node, and typically includes many or all of the elements described above with respect to the computer 20. The logical connections depicted in FIG10 include a local area network (LAN) 51 and a wide area network (WAN) 52. Such a network environment is common in office networks, enterprise-wide computer networks, intranets, and the Internet, which are various types of networks.

[0055] The computer 20, when used in a LAN networking environment, is connected to a local network 51 through a network interface or adapter 53, which is a type of communication device. The computer 20, when used in a WAN networking environment, typically includes a modem 54, a network adapter, a type of communication device, or any other type of communication device for establishing communications over the wide area network 52. The modem 54 (which may be internal or external) is connected to the system bus 23 via the serial port interface 46. In a network environment, the program engine or portions thereof depicted with respect to the personal computer 20 may be stored in a remote memory storage device. It should be understood that the network connections shown are examples, and other means of communication devices for establishing a communication link between computers may be used.

[0056] In an example implementation, software or firmware instructions for providing a provable and destructible device identity may be stored in memory 22 and / or storage device 29 or 31 and processed by processing unit 21. One or more ML, NLP, or DLP models disclosed herein may be stored in memory 22 and / or storage device 29 or 31 as a persistent data repository. For example, a cloud-based query workload optimization system 802 may be implemented as an application 36 on computer 20 (alternatively, the cloud-based query workload optimization system 802 may be implemented on a server or in a cloud environment). The cloud-based query workload optimization system 802 may utilize one or more of the following: processing unit 21, memory 22, system bus 23, and other components of personal computer 20.

[0057] In contrast to tangible computer-readable storage media, intangible computer-readable communication signals may embody computer-readable instructions, data structures, program modules, or other data residing in a modulated data signal, such as a carrier wave or other signal transport mechanism. The term "modulated data signal" refers to a signal that has one or more of its characteristics set or changed in such a manner as to encode information in the signal. By way of example, and not limitation, intangible communication signals include wired media, such as a wired network or direct-wired connection, and wireless media, such as acoustic, RF, infrared, and other wireless media.

[0058] The physical article disclosed herein includes one or more tangible computer-readable storage media that encode computer-executable instructions for executing a computer process on a computer system, the computer process comprising: receiving query logs from various query engines to a cloud data service; extracting various query entities from the query logs; parsing the query entities to generate a set of common workload characteristics; generating an intermediate representation of the query workload, wherein the intermediate representation is agnostic to the language of the multiple queries; identifying multiple workload patterns based on the intermediate representation of the query workload; classifying the workload into one or more workload type categories based on the workload patterns and the workload characteristics; and selecting an optimization scheme based on the category of the workload pattern.

[0059] In an alternative implementation, the computer process further comprises optimizing the workload using the selected optimization scheme. Alternatively, classifying the workload further comprises classifying the workload based on a cyclic workload pattern, a similarity workload pattern, and a dependency workload pattern. Alternatively, selecting the optimization scheme based on the category of the workload pattern further comprises selecting a dependency-driven optimization scheme if the workload pattern is a dependency pattern. Still further alternatively, selecting the optimization scheme based on the category of the workload pattern further comprises selecting a learning optimization scheme if the workload pattern is a cyclic pattern.

[0060] In an alternative implementation, selecting an optimization solution based on the workload pattern category further includes: if the workload pattern is a similarity pattern, selecting a multi-query optimization solution. In another implementation, the computer process further includes: generating a query annotation that can be used by the query engine during query compilation. Alternatively, the query annotation includes a signature, an action, and a set of parameters to be consumed by the query compiler. Alternatively, the query annotation is output as a file on a cloud storage location to be accessed by the query engine. In one implementation, the query entity also includes query metadata, a query plan, and runtime statistics.

[0061] A method for providing cloud-based query workload optimization includes: receiving query logs from various query engines to a cloud data service; extracting various query entities from the query logs; parsing the query entities to generate a set of common workload characteristics; generating an intermediate representation of the query workload, wherein the intermediate representation is agnostic to the language of multiple queries; identifying multiple workload patterns based on the intermediate representation of the query workload; classifying the workload into one or more workload type categories based on the workload patterns and the workload characteristics; and selecting an optimization scheme based on the category of the workload pattern.

[0062] In one implementation, the method further includes optimizing the workload using the selected optimization scheme. In an alternative implementation, classifying the workload further includes classifying the workload based on a cyclic workload pattern, a similarity workload pattern, and a dependency workload pattern. The alternative implementation further includes selecting a dependency-driven preferred scheme if the workload pattern is a dependency pattern; selecting a learning optimization scheme if the workload pattern is a cyclic pattern; and selecting a multi-query optimization scheme if the workload pattern is a similarity pattern. The alternative implementation further includes generating a query annotation that can be used by the query engine during query compilation. In another implementation, the query annotation includes a signature, an action, and a set of parameters to be consumed by the query compiler. Alternatively, the query annotation is output as a file on a cloud storage location to be accessed by the query engine.

[0063] Implementations disclosed herein include a system implemented in a computing environment, wherein the system includes: a memory; one or more processor units; and a cloud-based query workload optimization system stored in the memory and executable by the one or more processor units, the cloud-based query workload optimization system encoding computer-executable instructions on the memory for executing a computer process on the one or more processor units, the computer process comprising: receiving query logs from various query engines to a cloud data service; extracting various query entities from the query logs; parsing the query entities to generate a set of common workload characteristics; generating an intermediate representation of the query workload, wherein the intermediate representation is agnostic to the language of the multiple queries; identifying multiple workload patterns based on the intermediate representation of the query workload; classifying the workload into one or more workload type categories based on the workload patterns and the workload characteristics; selecting an optimization scheme based on the category of the workload pattern; and optimizing the workload using the selected optimization scheme. In one implementation, classifying the workload further comprises: classifying the workload based on a cyclic workload pattern, a similarity workload pattern, and a dependency workload pattern. In another implementation, the computer process further includes selecting a dependency-driven optimization scheme if the workload pattern is a dependency pattern, selecting a learning optimization scheme if the workload pattern is a cyclic pattern, and selecting a multi-query optimization scheme if the workload pattern is a similarity pattern.

[0064] The implementations described herein are implemented as logical steps in one or more computer systems. Logical operations can be implemented as (1) a sequence of processor-implemented steps executed in one or more computer systems and (2) interconnected machine or circuit modules within one or more computer systems. Implementation is a matter of choice, depending on the performance requirements of the computer systems utilized. Thus, the logical operations that make up the implementations described herein are variously referred to as operations, steps, objects, or modules. Furthermore, it should be understood that the logical operations may be performed in any order unless otherwise expressly required or a particular order is inherently required by the claim language.

[0065] The above description, examples, and data provide a complete description of the structure and use of the exemplary embodiments of the present invention. Since many implementations of the present invention can be made without departing from the spirit and scope of the present invention, the present invention resides in the claims appended hereto. In addition, the structural features of the different embodiments can be combined in yet another implementation without departing from the recited claims.

Claims

1. A physical article comprising one or more non-transitory computer-readable storage media encoding computer-executable instructions for executing a computer process on a computer system, the computer process comprising: Receive query logs from various query engines to cloud data services; extracting various query traces from the query log; parsing a query trace to generate a set of common workload characteristics, wherein the query trace includes at least one of: query metadata, a query plan, and query runtime statistics; generating an intermediate representation of the query workload using the set of common workload features, wherein the intermediate representation is agnostic to the language of multiple queries and the intermediate representation is common across workloads and query engines; identifying a plurality of workload patterns based on the intermediate representation of the query workload; classifying the workload into one or more workload type categories based on the workload pattern and the workload characteristics; as well as Based on the type of workload pattern, select an optimization solution.

2. The physical article of claim 1 , wherein the computer process further comprises: The workload is optimized using the selected optimization scheme.

3. The physical article of claim 1 , wherein classifying the workload further comprises: The workload is classified based on a cyclic workload pattern, a similarity workload pattern, and a dependency workload pattern.

4. The physical article of claim 1 , wherein selecting an optimization solution based on a category of workload pattern further comprises: If the workload pattern is a dependency pattern, a dependency-driven optimization scheme is selected.

5. The physical article of claim 1 , wherein selecting an optimization solution based on a category of workload pattern further comprises: If the workload pattern is a cyclic pattern, a learning optimization scheme is selected.

6. The physical article of claim 1 , wherein selecting an optimization solution based on a category of workload pattern further comprises: If the workload pattern is a similarity pattern, a multi-query optimization solution is selected.

7. The physical article of claim 1 , wherein the computer process further comprises: A query annotation is generated that can be used by the query engine during query compilation.

8. The physical artifact of claim 7, wherein the query annotation comprises a signature, an action, and a set of parameters to be consumed by a query compiler.

9. The physical article of claim 7, wherein the query annotation is output as a file on a cloud storage location to be accessed by the query engine.

10. A method for providing cloud-based query workload optimization, the method comprising: Receive query logs from various query engines to cloud data services; extracting various query traces from the query log; parsing a query trace to generate a set of common workload characteristics, wherein the query trace includes at least one of: query metadata, a query plan, and query runtime statistics; generating an intermediate representation of the query workload using the set of common workload features, wherein the intermediate representation is agnostic to the language of multiple queries and the intermediate representation is common across workloads and query engines; identifying a plurality of workload patterns based on the intermediate representation of the query workload; classifying the workload into one or more workload type categories based on the workload pattern and the workload characteristics; as well as Based on the type of workload pattern, select an optimization solution.

11. The method according to claim 10, further comprising: The workload is optimized using the selected optimization scheme.

12. The method according to claim 10, wherein classifying the workload further comprises: The workload is classified based on a cyclic workload pattern, a similarity workload pattern, and a dependency workload pattern.

13. The method according to claim 10, further comprising: If the workload pattern is a dependency pattern, selecting a dependency-driven optimization scheme; If the workload pattern is a cyclic pattern, selecting a learning optimization scheme; as well as If the workload pattern is a similarity pattern, a multi-query optimization solution is selected.

14. The method according to claim 10, further comprising: A query annotation is generated that can be used by the query engine during query compilation.

15. The method of claim 14, wherein the query annotation comprises a signature, an action, and a set of parameters to be consumed by a query compiler.

16. The method of claim 14, wherein the query annotation is output as a file on a cloud storage location to be accessed by the query engine.

17. A system in a computing environment, comprising: Memory; one or more processor units; A cloud-based query workload optimization system is stored in the memory and is executable by the one or more processor units, the cloud-based query workload optimization system encoding computer-executable instructions on the memory for executing a computer process on the one or more processor units, the computer process comprising: Receive query logs from various query engines to cloud data services; extracting various query traces from the query log; parsing a query trace to generate a set of common workload characteristics, wherein the query trace includes at least one of: query metadata, a query plan, and query runtime statistics; generating an intermediate representation of the query workload using the set of common workload features, wherein the intermediate representation is agnostic to the language of multiple queries and the intermediate representation is common across workloads and query engines; identifying a plurality of workload patterns based on the intermediate representation of the query workload; classifying the workload into one or more workload type categories based on the workload pattern and the workload characteristics; Select an optimization solution based on the type of workload pattern; and The workload is optimized using the selected optimization scheme.

18. The system of claim 17, wherein classifying the workload further comprises: The workload is classified based on a cyclic workload pattern, a similarity workload pattern, and a dependency workload pattern.

19. The system of claim 17, wherein the computer process further comprises: If the workload pattern is a dependency pattern, selecting a dependency-driven optimization scheme; If the workload pattern is a cyclic pattern, selecting a learning optimization scheme; as well as If the workload pattern is a similarity pattern, a multi-query optimization solution is selected.

Images