Patent Application
20170249358
The present disclosure described herein, in general, relates to database management systems, and more particularly to a system and method for improving the performance of query execution where clustering improves the performance.
A database system is generally used to answer queries requesting information from the database stored. A query may be defined as a logical expression over the data and the data relationships expressed in the database, and results in the identification of a subset of the database. In the recent advancements, database systems enable a single query execution to be run in parallel.
With the advancements of powerful hardware having parallel computing resources (such as processors and processor cores), the computing is shifted from single threaded to multithreaded enabling a single query execution to be run in parallel. Many algorithms including parallel sorting algorithms like bitonic sort, parallel radix sort, advanced parallel algorithms like a werbuch-shiloach algorithm for graph processing, and the like have been designed to take advantage of multiple cores in parallel, and thus to improve the system performance.
One of the advancements in parallel computing was introduced by the use of asymmetric memory. Asymmetric memory is computer memory constructed in a way that it is divided into memory banks, each closely associated with a processor, and the cost of accessing the memory depends on whether the accessed bank is local, to the processor, or remote. An example of asymmetric memory implementation is Non-Uniform Memory Access (NUMA) architecture applied by most chip manufacturers.
However, with the increase in the number of CPUs in the systems, more and more work are being done in parallel. This leads to the disclosure of new and efficient intra query algorithms for example, parallel merge sort algorithm. But most of these algorithms have bottleneck of a merge phase.
In analytical query processing, the operation link group aggregate, SORT, JOINS, but not limited to these, are considered to be the most frequently used and most expensive operations. One of the exemplary prior-art patent documents, U.S. Pat. No. 5,850,547 provides such solution by performing parallel processing of aggregate functions in a computing system, as shown in
In some of the prior art, load time clustering is done. But the load time clustering will restrict the portioning to be limited to only single column and thus heavily restricts the number of queries which gets benefitted from the partitioning.
Hence, there was a need for a method that will adopt the user's working environment and uses the latest technology to provide a solution which will reduces the group aggregate cost and thus improves the overall system performance.
In order to achieve the above mentioned need and to solve the above mentioned problems in the prior-art, various solutions were provided, few of them include but not limited to (1) A solution provided in the prior-art document U.S. Pat. No. 7,779,008 that provides techniques for performing a parallel aggregation operation on data that resides in a container, such as a relational table. The partition the initial data to multiple pieces (or the data could be already partitioned) is as shown in the
However, the above mentioned solutions and other existing solutions in the prior-art have at least two major problems. The first problem is that for optimizing GROUPED AGGREGATIONS, fast clustering is not performed. The second problem is that for JOIN algorithms clustering, which is already recommended by multiple papers, the clustering is done on the fly each time the query is executed.
Hence the major challenge in the existing/traditional query execution process is, based on above problems it may be clearly understood that, at a situation where each time the clustering has to be performed which is sub-optimal. So considering a case where either the same query is executed twice or two distinct queries are executed which need the same partitioning, and hence it may be understood by the person skilled in the art that both the queries will need to perform the clustering.
This summary is provided to introduce concepts related to a system and method for parallel optimization of database query using cluster cache are further described below in the detailed description. This summary is not intended to identify essential features of the claimed disclosure nor is it intended for use in determining or limiting the scope of the claimed disclosure.
TECHNICAL PROBLEM: There is a need to provide a mechanism that adapts to the users working environment and improves the group aggregate/sort/JOIN performance is required. Further, there is also a need to provide a mechanism to decide the amount of system memory that can be used to store the clustering information of a column. Further, the provided mechanism must reduce the group aggregate/sort/JOIN performance cost during the parallel intra query aggregation. Furthermore, in order to avoid the query execution time and to enhance the overall performance of the central processing unit (CPU), a mechanism such that clustering done for one operation/query can be reused by additional operations (like GROUP->SORT->JOIN) is required.
TECHNICAL SOLUTION: For solving the above mentioned problems and the other problems available in the prior-art a new concept of dynamic clustering for caching the clustering information is disclosed. The disclosed mechanism adapts to the users working environment and improves the group aggregate/sort/JOIN performance by caching the clustering information. Further, the disclosed mechanism improves the performance of group by aggregates/sort/JOIN (by avoiding the merge phase of parallel aggregation) with the use of dynamic clustering and further the overall performance of the CPU is optimized by caching the clustering information and storing them in local memory of worker thread. The caching of cluster information is based on user configuration to avoid overuse of system memory. For example, in the present disclosure the merge phase is avoided using clustering. Clustering ensures the same data is not present in the two clusters. Hence there is no need to merge thereby avoiding excess time for the execution. Each cluster can be treated as independent entities.
The plurality of aspects provides a system and method for parallel optimization of database query using cluster cache.
In one aspect, a system for parallel optimization of a database query requesting data from a database is disclosed. The system comprises a database, a processor coupled to a memory, and the processor configured to determine, based on a nature and/or a statistics of the database query received, if the database query needs clustering, cluster, if determined the query needs clustering, data present in a database based on the database query received, and displaying result of the query to a user after query processing, retain the clustered data, and thereby create at least one cluster cache comprising a cluster metadata, at least one data element, and the cluster data retained, and reuse the clustered data from the cluster cache, when a new query is received and if the new query is matched with the data elements and/or cluster metadata, for query processing.
In another aspect, a method for parallel optimization of a database query requesting data from a database is disclosed. The method comprises determining, based on a nature and/or a statistics of the database query received, if the database query needs clustering, clustering, if determined the query needs clustering, data present in a database based on the database query received, and displaying result of the query to a user, retaining the clustered data, creating at least one cluster cache comprising a cluster metadata, at least one data element, and the cluster data retained, and reusing the clustered data from the cluster cache, when a new query is received and if the new query is matched with the data elements and/or cluster metadata, for the processing of the new query.
In yet another aspect of the present disclosure, a system and method is provided to improve the performance of group by aggregates by avoiding the merge phase of parallel aggregation with the use of dynamic clustering and by caching the clustering information and storing them in local memory of worker thread. The caching of cluster information is based on user configuration to avoid overuse of system memory.
In one implementation, by the use of the above mentioned technical solution, the present disclosure provides a mechanism to decide the amount of system memory that can be used to store the clustering information of a column.
In one implementation, by the use of the above mentioned technical solution, the present disclosure provides a mechanism to reduce the group aggregate cost by avoiding the second merge phase of the parallel intra query aggregation.
In one implementation, by the use of the above mentioned technical solution, the present disclosure provides a mechanism to caches the cluster information to avoid the clustering operation for each query execution and thus reducing the cost of query execution.
In one implementation, by the use of the above mentioned technical solution, the present disclosure provides a mechanism to evict the unused clustering information to accommodate space for the frequently used columns.
In one implementation, by the use of the above mentioned technical solution, the present disclosure provides a mechanism to distribute the clustering information in different NUMA nodes to increase the local memory usage and to reduce the cache misses.
In one implementation, by the use of the above mentioned technical solution, the present disclosure provides a mechanism such that clustering done for one operation can be reused by additional operations (like GROUP->SORT->JOIN).
The detailed description is described with reference to the accompanying figures. In the figures, the left-most digit(s) of a reference number identifies the FIG. in which the reference number first appears. The same numbers are used throughout the drawings to refer like features and components.
The following clearly describes the technical solutions in the embodiments of the present disclosure with reference to the accompanying drawings in the embodiments of the present disclosure. Apparently, the described embodiments are merely a part rather than all of the embodiments of the present disclosure. All other embodiments obtained by a person of ordinary skill in the art based on the embodiments of the present disclosure without creative efforts shall fall within the protection scope of the present disclosure.
The disclosure can be implemented in numerous ways, including as a process, an apparatus, a system, a composition of matter, a computer readable medium such as a computer readable storage medium or a computer network wherein program instructions are sent over optical or electronic communication links. In this specification, these implementations, or any other form that the disclosure may take, may be referred to as techniques. In general, the order of the steps of disclosed processes may be altered within the scope of the disclosure.
A detailed description of one or more embodiments of the disclosure is provided below along with accompanying figures that illustrate the principles of the disclosure. The disclosure is described in connection with such embodiments, but the disclosure is not limited to any embodiment. The scope of the disclosure is limited only by the claims and the disclosure encompasses numerous alternatives, modifications and equivalents. Numerous specific details are set forth in the following description in order to provide a thorough understanding of the disclosure. These details are provided for the purpose of example and the disclosure may be practiced according to the claims without some or all of these specific details. For the purpose of clarity, technical material that is known in the technical fields related to the disclosure has not been described in detail so that the disclosure is not unnecessarily obscured.
Systems and methods for parallel optimization of database query using cluster cache are disclosed.
Referring now to
In one implementation, systems and methods are provided to improve the performance of group by aggregates by avoiding the merge phase of parallel aggregation with the use of dynamic clustering and by caching the clustering information and storing them in local memory of worker thread. The caching of cluster information is based on user configuration to avoid overuse of system memory.
While aspects are described for improving the performance of group by aggregates by avoiding the merge phase of parallel aggregation with the use of dynamic clustering may be implemented in any number of different computing systems, environments, and/or configurations, the embodiments are described in the context of the following exemplary systems, devices, and methods.
Referring now to
In one implementation, the system comprises a database 110, a processor 104 coupled to a memory 108, and the processor 110 configured to determine 112, based on a nature and/or a statistics of the database query received, if the database query needs clustering, cluster 114, if determined the query needs clustering, data present in a database based on the database query received, and displaying result of the query to a user after query processing, retain 116 the clustered data, and thereby create at least one cluster cache comprising a cluster metadata, at least one data element, and the cluster data retained, and reuse 118 the clustered data from the cluster cache, when a new query is received and if the new query is matched with the data elements and/or cluster metadata, for query processing.
In one implementation, the system 100 is communicably coupled with the user devices/database client systems 102. Although the present disclosure is explained considering that the system 100 is implemented as a separate computing unit it may be understood that the system 100 may also be implemented on a server, in a variety of computing systems, such as a laptop computer, a desktop computer, a notebook, a workstation, a mainframe computer, a server, a network server, and the like. It will be understood that the system 100 may be accessed by multiple users through one or more user devices/client systems 102-1, 102-2 . . . 102-N, collectively referred to as user 102 hereinafter, or applications residing on the user devices 102. Examples of the user devices 102 may include, but are not limited to, a portable computer, a personal digital assistant, a handheld device, and a workstation. The user devices 102 are communicatively coupled to the system 100 through a network (not shown).
In one implementation, the network may be a wireless network, a wired network or a combination thereof. The network can be implemented as one of the different types of networks, such as intranet, local area network (LAN), wide area network (WAN), the internet, and the like. The network may either be a dedicated network or a shared network. The shared network represents an association of the different types of networks that use a variety of protocols, for example, Hypertext Transfer Protocol (HTTP), Transmission Control Protocol/Internet Protocol (TCP/IP), Wireless Application Protocol (WAP), and the like, to communicate with one another. Further the network may include a variety of network devices, including routers, bridges, servers, computing devices, storage devices, and the like.
In one implementation, the at least one processor 104 may be implemented as one or more microprocessors, microcomputers, microcontrollers, digital signal processors, central processing units, state machines, logic circuitries, and/or any devices that manipulate signals based on operational instructions. Among other capabilities, the at least one processor 104 is configured to fetch and execute computer-readable instructions stored in the memory 108.
The interface 106 may include a variety of software and hardware interfaces, for example, a web interface, a graphical user interface, and the like. The interface 204 may allow the client systems/users 102 to interact with a user directly or through the system 100. Further, the interface 106 may enable the system 100 to communicate with other computing devices, such as web servers and external data servers (not shown). The interface 106 can facilitate multiple communications within a wide variety of networks and protocol types, including wired networks, for example, LAN, cable, etc., and wireless networks, such as WLAN, cellular, or satellite. The interface 106 may include one or more ports for connecting a number of devices to one another or to another server.
The memory 108 may include any computer-readable medium known in the art including, for example, volatile memory, such as static random access memory (SRAM) and dynamic random access memory (DRAM), and/or non-volatile memory, such as read only memory (ROM), erasable programmable ROM, flash memories, hard disks, optical disks, and magnetic tapes. The memory 108 may include at least one query compiler configured to prepare an execution plan in a tree structure, with a plurality of plan nodes, for the database query received. It shall be noted that the query compiler is a conventional compiler and the execution plan generation done in the tradition/convention approaches as available in the prior-art.
In one implementation the user 102 interacts with the system 100 by sending/firing a query to the database 110 located at the database server (not shown) or in the system 100.
In one implementation, the system 100 is configured to check if a query request from the database client is a candidate for/requires clustering. The check may be performed based on nature of the query and statistics. An example of the nature of the query may include but not limited to the type of query like JOIN or GROUP AGGREGATEs. If the nature of the query is amenable to clustering (typically JOIN, GROUP AGGREGATES, SORTs) then it can be chosen. Then the statistics is checked. If the query is going to select many records, then clustering is an option. The statistics are generally histogram based statistics and are basic concepts in database domain. The database then clusters the data and performs the operation provide in the query. At the end of the operation the result data is projected to the client.
In one implementation, after projecting the data to the client, the clustered data is retained by the system. This retention is termed as the “cluster cache”. The detail of the cluster cache is explained in sections below.
In one implementation, when a second (or further or new) query comes from a client to the database/database server. The database determines if this query can use clustering or the cluster cache. If the query can use the cluster cache, the database server checks if the clustered information is already available in the cluster cache. If the data is already available the database server will reuse the information in the cluster cache.
In one implementation, the clustered data from the cluster cache may be reused, if the clustered data is already available in the cluster cache and if available, reuse the clustered data for the processing of the new query.
In one implementation, the cluster cache is updated using an incremental cache update model.
In one implementation, the nature of the database query received is the characteristics of the database query selected from the group comprising: (i) attributes of a GROUP BY clause, (ii) attributes of a WHERE clause, (ii) attributes of a JOIN clause, (iv) attributes of a HAVING clause, and (v) attributes of the grouped aggregation function.
In one implementation, the statistics is histogram based statistics. Statistics are traditionally computed in all DBMS. These statistics may be sample based and is stored as a histogram. The classical usage of statistics is for “selectivity estimation”. The resent disclosure uses the same statistics to cluster our data. This histogram for selectivity estimation is further described in the document entitled “Improved histograms for selectivity estimation of range predicates,” by Poosala, Viswanath, et al. ACM SIGMOD Record. Vol. 25. No. 2. ACM, 1996, which is hereby incorporated by reference in its entirety. In one implementation, the present disclosure may use only one kind of query statistics in this disclosure namely the “selectivity statistics”. They are random sampled statistics. One kind of random sampling is the reservoir random sampling as explained in the document entitled “Random sampling with a reservoir,” by Vitter, Jeffrey S ACM Transactions on Mathematical Software (TOMS) 11.1 (1985) 37-57.
In one implementation, the clustering is performed by any one of the techniques for clustering selected from radix based clustering, or hash based clustering, or any combination thereof.
In one implementation, the clustered data comprises at least one of global row identifiers (ids) or records stored in tables of the database, or subset of records stored in tables of the database based on the systems configuration.
In one implementation, the data elements comprise at least one of table Id, cluster column, clustering model, filter condition, memory affinity, or any combination thereof.
In one implementation, the query processing is performed by a query optimizer.
In one implementation, the grouped aggregation function included in the database query is selected from the following aggregation functions: COUNT, AVG, SUM, MIN, MAX, VARIANCE, and STANDARD_DEVIATION aggregation functions.
In one implementation, the database query is composed in the Structured Query Language (SQL), and the memory is an asymmetric memory.
In one implementation, to avoid the merge phase of parallel group aggregate operation, the table records are divided into different groups based on some radix bit.
In one implementation, the division of tables makes sure that, the aggregate can be calculated for each group independently and there is no need of any merge.
In one implementation, the grouping will impact the group aggregate performance if it is done for each query execution. So a cache is designated for storing the clustering information of frequently used columns.
In one implementation, the clustering information may be the global row ids or the actual records or subset of records based on user input.
In one implementation, when a group aggregate plan is generated, clustering information is used to assign the groups to each worker thread based on the data distribution.
In one implementation, to improve the performance of parallel algorithms the clustering mechanism is used. This clustering could be radix based or hash based.
In one implementation, the clustering information can be the global row ids or the actual records or subset of records based on database server configuration.
In one implementation, the technical advancement in the present disclosure is achieved by caching the clustered results so as to avoid clustering each time a specific query arrives. Clustering is an expensive operation even though it brings significant performance benefits for the parallel algorithms.
In one implementation, the size of the cluster cache may be managed through user input configuration values and can be increased or decreased through SQL commands. The size of the cluster cache can be expressed in terms of bytes and can be typically 10 megabytes (MB).
In one implementation, if the cache size is reached to maximum and a new request comes for adding cluster information for a new column, then the least recently or the least frequently used column's clustering information may be removed from the cache.
In one implementation, the addition, if new clustering information is received, may be done by user, using a SQL command or can be done internally by the optimizer based on system load information. For example, assume we are executing a new query like “SELECT max(f1) FROM t1 GROUP BY f2”. This query can explicitly clustered and cached by a hint from the user of the form: “SELECT max(f1) FROM t1 GROUP BY f2/*Hint:Cluster Cache*/”This query can also be implicitly clustered and cached by the optimizer
In one implementation, the cluster information is stored in separate memory location based on the number of NUMA node and other system configuration.
In one implementation, when system data changes, snapshot may be taken for the newly modified data and an incremental update is done of the cluster information to reduce the overhead of write operations. It may be understood by the person skilled in the art that, one of the critical problems of caching is when to update the cache. The naïve approach towards this is to periodically say once in 1 min, delete the cache and rebuild the cache by scanning all relevant rows. If the cache was holding 10M data then this approach requires us to write 10M. The present disclosure provides an approach to use a delta update on the histogram based cache. In one example, one idea on the delta update is provided in U.S. Pat. No. 6,278,989, which is hereby incorporated by reference in its entirety.
In one implementation, the cluster is cached as described in the cluster cache section. When writes happen to the database, it is possible that the cached data does not reflect the current information. One obvious choice is to recreate the whole cache on data change. But this is expensive. So embodiments of the present disclosure enable to update the existing cache using an incremental cache update model. In this case when a write happens to the data, system determines if the data is already cached. This can be trivially computed by the data structures mentioned in the previous (cluster cache) section. If the data is already cached suitable modifications is done to the cache. For example if a record is deleted, then the corresponding data is deleted from the cache too.
The order in which the method is described is not intended to be construed as a limitation, and any number of the described method blocks can be combined in any order to implement the method or alternate methods. Additionally, individual blocks may be deleted from the method without departing from the scope of the disclosure described herein. Furthermore, the method can be implemented in any suitable hardware, software, firmware, or combination thereof. However, for ease of explanation, in the embodiments described below, the method may be considered to be implemented in the above described system 100.
At block 602, the database query is received and the system determines if the query received needs clustering.
At block 604, the database query received is executed in the conventional manner and the result based on the execution is displayed to the user.
At block 606, after the query is executed and the result is displayed to the user, the system retains the clustered data and thereupon creates cluster caches.
At block 608, when the system receives a new query the clustered pre-stored caches are checked if the query received may use the clustered data from them. If the system determines that the data may be reused for processing the query it fetches the cluster cache and the data associated with the cache for further processing.
In one implementation, the method comprises reusing the clustered data for the processing of the new query, if the clustered data is already available in the cluster cache.
In one implementation, the method comprises updating the cluster cache using an incremental cache update model.
In one implementation, the nature of the query received is a type of query like JOIN or GROUP AGGREGATE, SORT (ORDER BY), or any combination thereof.
In one implementation, wherein the clustering is performed by any one of the techniques for clustering selected from radix based clustering, or hash based clustering, or any combination thereof.
In one implementation, the clustered data comprises at least one of global row ids or records stored in tables of the database, or subset of records stored in tables of the database based on the systems configuration.
In one implementation, the data elements comprise at least one of table id, cluster column, clustering model, filter condition, memory affinity, or any combination thereof.
In one implementation, the grouped aggregation function included in the database query is selected from the following aggregation functions: the COUNT, AVG, SUM, MIN, MAX, VARIANCE, and STANDARD_DEVIATION aggregation functions.
In example provided below, it is shown that how the system/database/server can determine if the query can use clustering. It may be known and understood by the persons skilled in that art that there is a fixed set of queries which benefit from clustering, which includes but not limited to, GROUP AGGREGATES, JOIN, SORT (ORDER BY), which may be collectively referred to herein as the “cluster clauses.” These queries benefit from clustering based on two conditions: (1) Is the selectivity of the cluster clauses of the query high enough to be partitioned? For example a filter is applied on the query, and the resultant records are only 100. This is the selectivity of the cluster clause (100), which is too low for partitioning. (2) Is the cardinality of the cluster clauses of the query high enough to be partitioned? For example a filter is applied on the query, and the resultant records are of 10 types. This is the cardinality of the cluster clause (100), which is too low for partitioning. The above two conditions are explained with the help of below mentioned example.
Assume a table has 1 million (M) records. A sample set of records with the schema is as shown in
Consider the following query:
“SELECT SUM(sales_value), SUM(sales_piece) FROM dimension_tab GROUP BY pdt_id WHERE pdt_type=‘car’;”
The clustering clause is the “GROUP BY” (GROUPED AGGREGATE) clause. The number of entries in the table for the product type ‘car’ forms the selectivity of the clustering clause. The number of unique products (represented by PDT_ID) of the type ‘car’ forms the cardinality of the clustering clause. If for example in 1M records, there are only 1000 products of type “car,” then the selectivity is 1000. If for example in 1M records, there are 1000 products of type “car”, but there are only 10 kinds of ‘car’ products, then the cardinality is 10.
In the example provided below, the notion of how the cluster cache is stored in the database/database server and how a cluster set on the arrival of a query is searched/looked up is described. In one implementation, the structure of cluster cache is as given below:
From cluster structure it may be understood that the structure it is mainly made up of two parts: the metadata and the cluster data itself The cluster data is fairly straight forward. Some notion of the row should be cached in the cluster data. It could be only row_id or could be the entire row itself. Both choices are fine. In the above shown example the row_id is used. However the metadata is trickier. It is explained in the below table and with the help of example query: “SELECT SUM(sales_value), SUM(sales_piece) FROM dimension_tab GROUP BY pdt_id WHERE pdt_type=‘car’;”
Based on the meta-data of the cluster-cache it may be determined for a given query if it is already cached or not. This is explained with the three queries and the calculations are performed:
Exemplary embodiments discussed above may provide certain advantages. Though not required to practice aspects of the disclosure, there are several advantages to the mechanisms disclosed herein. The mechanism disclosed in the present disclosure improves performance of OLAP queries which tend to be grouped on the same set of columns. The key benefit is achieved when there are less writes and more queries. The mechanism discloses the concept of intermediate result caching to grouped aggregates. It caches the cluster information to avoid the clustering operation for each query execution and thus reducing the cost of query execution. The mechanism in the “Continuous ETL” scenario of smart policy charging control (PCC) improves the performance of the queries by more than 70%. The mechanism uses a cache for storing clustering information/cluster data of frequently used columns in group aggregate operations to improve the performance of parallel group aggregate operation. The mechanism distributes the clustering information in different NUMA node to avoid remote read of memory and thereby increase the local memory usage and to reduce the cache misses. The mechanism manages the cluster cache using user given information to control the use of system memory through system configuration. The mechanism manages the cache with the help of usage statistics so to avoid having stale data in the cluster cache. The mechanism allows user to add or remove cluster information using SQL command to increase the flexibility for the user. The mechanism improves group aggregate execution speed by caching intermediate results to benefit many queries. The mechanism provides user configurable and manageable cache to store the clustering information about columns used in group aggregate. The mechanism enables evacuation of unused clustering information from the cache to make space for the new clustering information (to accommodate space for the frequently used columns).
Although implementations for a system and method for parallel optimization of database query using cluster cache have been described in language specific to structural features and/or methods, it is to be understood that the appended claims are not necessarily limited to the specific features or methods described. Rather, the specific features and methods are disclosed as examples of implementations of a system and method for parallel optimization of database query using cluster cache.
| Number | Date | Country | Kind |
|---|---|---|---|
| IN1493/CHE/2015 | Mar 2015 | IN | national |
This application is a continuation of International Application PCT/CN2015/095808, filed on Nov. 27, 2015, which claims priority to Indian Patent Application IN1493/CHE/2015, filed on Mar. 24, 2015. The disclosures of the aforementioned applications are hereby incorporated by reference in their entireties.
| Number | Date | Country | |
|---|---|---|---|
| Parent | PCT/CN2015/095808 | Nov 2015 | US |
| Child | 15592446 | US |
