Query Execution Plan Research Articles

Learned Query Optimizers: Evaluation and Improvement

Query Optimization is considered to be one of the most important challenges in database management. Existing built-in query optimizers are very complex and rely on various approximations and hand-picked rules. The rise of deep learning and deep reinforcement learning has aided many scientific and industrial fields, providing an opportunity to develop a <i>learnable query optimizer</i>. In this paper, we analyse and improve the state-of-the-art learned query optimizer, Neo for the JOB benchmark on two database systems: PostgreSQL and Huawei GaussDB.We describe our methods, based on combination of Neo, Tree-Transformers, auxiliary tasks, reward weighting. Combinations of these methods improve latency of the found query execution plans. We also conduct a thorough analysis of the resulting execution plans and devise a set of decision-based rules to indicate the cases when the learned optimizer will outperform the built-in one. We also provide a source code for the proposed methods and experiments. Finally, we provide possible directions for further improvement in this field.

A Gitbash-Generative Model for an Enhanced Query Processing In a Distributed Database System

This research study is directly focused on an enhanced query processing system using GitBash- Deep Generative Model, it is use to improve the performance of big data. Due to Poor query execution plan and absence of an improved aggregate query method which impose threats to qualities of good query processing in distributed databases which also cause memory pressure in a database, inflated central processing unit (CPU) and an overall reduction in concurrency. Also the volume of data use in any organization is progressively increasing in it seize, thereby resulting or demand a large storage location or space system. The availability of intended software that will confront the problem of storage contents and execution plan for query processing is a vital challenge facing big data in a processing system, which can prompt to information loss and missing record of vital document in an enterprises. The study will boost solution and addresses the problem of latencies, inability to transform and store queries, and poor query processing plans for future improvement of distributed databases, using an unsupervised learning approach such as deep generative model. The adopted methodology is dynamic system development method (DSDM). php programming language and mongoDB database is adopted for implementation. The datasets used for the proposed system was generated from e-library server/data.json (query) and is inserted into MongoDB database json format. The datasets are all trained and recognized by the Deep Generative algorithm through the Git Bash server. The proposed system result shows highly increase in the efficiency of management of big data and improved query processing. General Term Query Processing System Keywords Big Data, Deep Generative Model, Query Processing System, Information Management, Gitbash.

A new framework based on features modeling and ensemble learning to predict query performance.

A query optimizer attempts to predict a performance metric based on the amount of time elapsed. Theoretically, this would necessitate the creation of a significant overhead on the core engine to provide the necessary query optimizing statistics. Machine learning is increasingly being used to improve query performance by incorporating regression models. To predict the response time for a query, most query performance approaches rely on DBMS optimizing statistics and the cost estimation of each operator in the query execution plan, which also focuses on resource utilization (CPU, I/O). Modeling query features is thus a critical step in developing a robust query performance prediction model. In this paper, we propose a new framework based on query feature modeling and ensemble learning to predict query performance and use this framework as a query performance predictor simulator to optimize the query features that influence query performance. In query feature modeling, we propose five dimensions used to model query features. The query features dimensions are syntax, hardware, software, data architecture, and historical performance logs. These features will be based on developing training datasets for the performance prediction model that employs the ensemble learning model. As a result, ensemble learning leverages the query performance prediction problem to deal with missing values. Handling overfitting via regularization. The section on experimental work will go over how to use the proposed framework in experimental work. The training dataset in this paper is made up of performance data logs from various real-world environments. The outcomes were compared to show the difference between the actual and expected performance of the proposed prediction model. Empirical work shows the effectiveness of the proposed approach compared to related work.

Compliant geo-distributed data processing in action

In this paper we present our work on compliant geo-distributed data processing. Our work focuses on the new dimension of dataflow constraints that regulate the movement of data across geographical or institutional borders. For example, European directives may regulate transferring only certain information fields (such as non personal information) or aggregated data. Thus, it is crucial for distributed data processing frameworks to consider compliance with respect to dataflow constraints derived from these regulations. We have developed a compliance-based data processing framework, which (i) allows for the declarative specification of dataflow constraints, (ii) determines if a query can be translated into a compliant distributed query execution plan, and (iii) executes the compliant plan over distributed SQL databases. We demonstrate our framework using a geo-distributed adaptation of the TPC-H benchmark data. Our framework provides an interactive dashboard, which allows users to specify dataflow constraints, and analyze and execute compliant distributed query execution plans.

A technique for parallel query optimization using MapReduce framework and a semantic-based clustering method.

Query optimization is the process of identifying the best Query Execution Plan (QEP). The query optimizer produces a close to optimal QEP for the given queries based on the minimum resource usage. The problem is that for a given query, there are plenty of different equivalent execution plans, each with a corresponding execution cost. To produce an effective query plan thus requires examining a large number of alternative plans. Access plan recommendation is an alternative technique to database query optimization, which reuses the previously-generated QEPs to execute new queries. In this technique, the query optimizer uses clustering methods to identify groups of similar queries. However, clustering such large datasets is challenging for traditional clustering algorithms due to huge processing time. Numerous cloud-based platforms have been introduced that offer low-cost solutions for the processing of distributed queries such as Hadoop, Hive, Pig, etc. This paper has applied and tested a model for clustering variant sizes of large query datasets parallelly using MapReduce. The results demonstrate the effectiveness of the parallel implementation of query workloads clustering to achieve good scalability.

Reasoning on the Efficiency of Distributed Complex Event Processing

Complex event processing (CEP) evaluates queries over streams of event data to detect situations of interest. If the event data are produced by geographically distributed sources, CEP may exploit in-network processing that distributes the evaluation of a query among the nodes of a network. To this end, a query is modularized and individual query operators are assigned to nodes, especially those that act as data sources. Existing solutions for such operator placement, however, are limited in that they assume all query results to be gathered at one designated node, commonly referred to as a sink. Hence, existing techniques postulate a hierarchical structure of the network that generates and processes the event data. This largely neglects the optimisation potential that stems from truly decentralised query evaluation with potentially many sinks. To address this gap, in this paper, we propose Multi-Sink Evaluation (MuSE) graphs as a formal computational model to evaluate common CEP queries in a decentralised manner. We further prove the completeness of query evaluation under this model. Striving for distributed CEP that can scale to large volumes of high-frequency event streams, we show how to reason on the network costs induced by distributed query evaluation and prune inefficient query execution plans. As such, our work lays the foundation for distributed CEP that is both, sound and efficient.

Re-optimization for Multi-objective Cloud Database Query Processing using Machine Learning

In cloud environments, hardware configurations, data usage, and workload allocations are continuously changing. These changes make it difficult for the query optimizer of a cloud database management system (DBMS) to select an optimal query execution plan (QEP). In order to optimize a query with a more accurate cost estimation, performing query re-optimizations during the query execution has been proposed in the literature. However, some of there-optimizations may not provide any performance gain in terms of query response time or monetary costs, which are the two optimization objectives for cloud databases, and may also have negative impacts on the performance due to their overheads. This raises the question of how to determine when are-optimization is beneficial. In this paper, we present a technique called ReOptML that uses machine learning to enable effective re-optimizations. This technique executes a query in stages, employs a machine learning model to predict whether a query re-optimization is beneficial after a stage is executed, and invokes the query optimizer to perform the re-optimization automatically. The experiments comparing ReOptML with existing query re-optimization algorithms show that ReOptML improves query response time from 13% to 35% for skew data and from 13% to 21% for uniform data, and improves monetary cost paid to cloud service providers from 17% to 35% on skewdata.

Degradation of Complexity for Join Enumeration via Weight Measure on CMP

Most contemporary database systems query optimizers exploit System-R's Bottom-Up dynamic programming method (DP) to find the optimal query execution plan (QEP). The dynamic pr ogramming algorithm has a worst case running time, thus for queries with more than 10 joins, it becomes infeasible. To resolve this problem, random strategies are used. In this paper we propose a parallel top-down join enumeration algorithm that is optimal with respect to the partial order graph based on Chip Multi-Processor (CMP). This paper firstly transforms the undirected query graph to W eighted Edge Join Graph (WEJG) according to the edge weight and constructs all partial order join and partial order graph wi thin WEJG. Then the global optimal query plan is achieved according to the parallelize top-down enumeration. Our theoretical result s and empirical evaluation show that our algorithm could gracefully degrade the complexity degree for top-down join enumeration with large number of tables and gains impressive in the performance in terms of both output quality and running time.

Dynamic solution for multiple question optimization

The problem of multiple query optimization is considered as a complete optimization problem, the input parameters for optimization are determined: queries, tasks, plans. Proposed and substantiated architecture of the system in which the DBMS with the query optimizer is used - a computer system in which one or more computers (information processing system, one or more client computers, control computer, one or more the number of database servers) are interconnected by the client network and the server network. The data processing system receives the primary request made by one of the client computers, creates one or more secondary requests and transmits them to the database servers, in the case, if necessary, performs links or data updates, as defined in the primary request, and then returns the resulting data to the computer of the client from which the initial request was made. The I / O processing module, query analyzer, query optimizer, query processing module, optimization controller, external memory device are the elements that make up the information processing module. The I / O processing module receives the request from the client computers and the control request from the control computer and responds to these requests. The query optimizer optimizes the query using the query tree generated by the query analyzer, and develops a procedure for a series of operations (query execution plan) to obtain the results of query processing. The query processing module executes the query execution plan developed by the query optimizer. The order of execution of elementary requests which provide the minimum time for one, two and three processors is developed. A comparison of multi-request execution time with joint and incompatible processing depending on the number of processors is presented. The minimum execution time of the query can be achieved by executing elementary queries in the appropriate order, determined by the condition of order.

Join query optimisation in the distributed databases using a hybrid harmony search and artificial bee colony algorithm

The query optimiser is a vital part of any distributed database mechanism. Reducing the execution period of the query depends on reaching an ideal query execution plan. Due to this issue's NP-hard nature, a hybrid harmony search and an artificial bee colony algorithm can be useful. The harmony is used to call query plans and signify them by S-dimension real vectors. A harmony memory is a place for creating and storing a primary population of harmony vectors. Then, bees explore harmony memory as a food source. The production of a novel nominate harmony out of all query plans in the harmony memory requires a pitch adjustment principle, a memory consideration one, and a random re-initialisation. Lastly, the new candidate vector replaces the worst harmony vector when it works better. The simulation outcomes have indicated that the introduced method reduces the expenses of evaluating a query compared to the harmony search and bee colony optimisation algorithms. However, this method has a longer execution time.

Ensuring availability of digital education platforms’ databases

The article offers a procedural model for optimization of making queries against the digital education platform under the conditions of negative information impacts. The model is developed on the basis of the formalized description of the process of database query processing. The model allows you to study the influence of negative information impacts and of the structure of a digital education platform on the availability indicators of its database. A model for determining plans of making queries against a digital education platform database is offered. On the basis of the genetic algorithm, a model for optimizing the allocation of database areas of a digital education platform is constructed. The effectiveness of the proposed models is confirmed by the results of a computational experiment, during which a fragment of a digital education platform was deployed. The probabilistic and temporal characteristics of database queries processing are presented. Assessment of modeling accuracy was carried out by comparing the data obtained experimentally with the data obtained during direct-analogy simulation. The developed models for optimizing the allocation of pieces of data and database query execution plans can be used to improve not only existing database of digital education platforms but also the ones under development.

LocationSpark: In-memory Distributed Spatial Query Processing and Optimization.

Due to the ubiquity of spatial data applications and the large amounts of spatial data that these applications generate and process, there is a pressing need for scalable spatial query processing. In this paper, we present new techniques for spatial query processing and optimization in an in-memory and distributed setup to address scalability. More specifically, we introduce new techniques for handling query skew that commonly happens in practice, and minimizes communication costs accordingly. We propose a distributed query scheduler that uses a new cost model to minimize the cost of spatial query processing. The scheduler generates query execution plans that minimize the effect of query skew. The query scheduler utilizes new spatial indexing techniques based on bitmap filters to forward queries to the appropriate local nodes. Each local computation node is responsible for optimizing and selecting its best local query execution plan based on the indexes and the nature of the spatial queries in that node. All the proposed spatial query processing and optimization techniques are prototyped inside Spark, a distributed memory-based computation system. Our prototype system is termed LocationSpark. The experimental study is based on real datasets and demonstrates that LocationSpark can enhance distributed spatial query processing by up to an order of magnitude over existing in-memory and distributed spatial systems.

An automatic clustering technique for query plan recommendation

The query optimizer is responsible for identifying the most efficient Query Execution Plans (QEP’s). The distributed database relations may be kept in several places. These results in a dramatic increase in the number of alternative query’ plans. The query optimizer cannot exhaustively explore the alternative query plans in a vast search space at reasonable computational costs. Henceforth, reusing the previously generated plans instead of generating new plans for new queries is an efficient technique for query processing. To improve the accuracy of clustering, we’ve rewritten the queries to standardize their structures. Furthermore, TF representation schema has been used to convert the queries into vectors. In this paper, we’ve introduced a multi-objective automatic query plan recommendation method, a combination of incremental DBSCAN and NSGA-II. The quality of the results of incremental DBSCAN has been influenced by Minpts (minimum points) and Eps (epsilon). Two cluster validity indices, Dunn index and Davies–Bouldin index, have simultaneously been optimized to calculate the goodness of an answer. Comparative results have been shown against the incremental DBSCAN and K-means regarding an external cluster validity index, namely, the ARI. By comparing different types of query workloads, we’ve found that the introduced method outperforms the other well-known approaches.

Turbo: Dynamic and Decentralized Global Analytics via Machine Learning

Big data analytics are practiced in many fields to extract insights from massive amounts of data. With exponential growth in both the volume and variety of data, analytic queries have expanded from those executed in a single datacenter to those requiring inputs from multiple datacenters that are geographically separate or even globally distributed. Unfortunately, the software stack that supports data analytics is designed originally for a cluster environment and is not tailored to execute global analytic queries, where resources such as inter-datacenter networks may vary on the fly. Existing optimization strategies that determine the query execution plan before its execution are not able to adapt to resource variations at query runtime. In this article, we present Turbo, a lightweight and non-intrusive global analytics system that can dynamically adjust query execution plans for geo-distributed analytics in the presence of time-varying resources and network bandwidth across datacenters. Turbo uses machine learning to accurately predict the time cost of a query execution plan so that dynamic adjustments can be made to it when necessary. Turbo is non-intrusive in the sense that it does not require modifications to the existing software stack for data analytics. We have implemented a real-world prototype of Turbo, and evaluated it on a cluster of 33 instances across eight regions in the Google Cloud Platform. Our experimental results have shown that Turbo can achieve an accuracy of 95 percent for estimating time costs, and can reduce the query completion time by 41 percent.

AJoin

The processing model of state-of-the-art stream processing engines is designed to execute long-running queries one at a time. However, with the advance of cloud technologies and multi-tenant systems, multiple users share the same cloud for stream query processing. This results in many ad-hoc stream queries sharing common stream sources. Many of these queries include joins. There are two main limitations that hinder performing ad-hoc stream join processing. The first limitation is missed optimization potential both in stream data processing and query optimization layers. The second limitation is the lack of dynamicity in query execution plans. We present AJoin, a dynamic and incremental ad-hoc stream join framework. AJoin consists of an optimization layer and a stream data processing layer. The optimization layer periodically reoptimizes the query execution plan, performing join reordering and vertical and horizontal scaling at run-time without stopping the execution. The data processing layer implements pipelineparallel join architecture. This layer enables incremental and consistent query processing supporting all the actions triggered by the optimizer. We implement AJoin on top of Apache Flink, an open-source data processing framework. AJoin outperforms Flink not only at ad-hoc multi-query workloads but also at single-query workloads.

Guided automated learning for query workload re-optimization

Query optimization is a hallmark of database systems. When an SQL query runs more expensively than is viable or warranted, determination of the performance issues is usually performed manually in consultation with experts through the analysis of query's execution plan (QEP). However, this is an excessively time consuming, human error-prone, and costly process. GALO is a novel system that automates this process. The tool automatically learns recurring problem patterns in query plans over workloads in an offline learning phase, to build a knowledge base of plan-rewrite remedies. It then uses the knowledge base online to re-optimize queries often quite drastically. GALO's knowledge base is built on RDF and SPARQL, W3C graph database standards, which is well suited for manipulating and querying over SQL query plans, which are graphs themselves. GALO acts as a third-tier of re-optimization, after query rewrite and cost-based optimization, as a query plan rewrite . For generality, the context of knowledge base problem patterns, including table and column names, is abstracted with canonical symbol labels. Since the knowledge base is not tied to the context of supplied QEPs, table and column names are matched automatically during the re-optimization phase. Thus, problem patterns learned over a particular query workload can be applied in other query workloads. GALO's knowledge base is also an invaluable tool for database experts to debug query performance issues by tracking to known issues and solutions as well as refining the optimizer with new tuned techniques by the development team. We demonstrate an experimental study of the effectiveness of our techniques over synthetic TPC-DS and real IBM client query workloads.

A query system for efficiently investigating complex attack behaviors for enterprise security

The need for countering Advanced Persistent Threat (APT) attacks has led to the solutions that ubiquitously monitor system activities in each enterprise host, and perform timely attack investigation over the monitoring data for uncovering the attack sequence. However, existing general-purpose query systems lack explicit language constructs for expressing key properties of major attack behaviors, and their semantics-agnostic design often produces inefficient execution plans for queries. To address these limitations, we build Aiql, a novel query system that is designed with novel types of domain-specific optimizations to enable efficient attack investigation. Aiql provides (1) a domain-specific data model and storage for storing the massive system monitoring data, (2) a domain-specific query language, Attack Investigation Query Language (Aiql) that integrates critical primitives for expressing major attack behaviors, and (3) an optimized query engine based on the characteristics of the data and the semantics of the query to efficiently schedule the execution. We have deployed Aiql in NEC Labs America comprising 150 hosts. In our demo, we aim to show the complete usage scenario of Aiql by (1) performing an APT attack in a controlled environment, and (2) using Aiql to investigate such attack by querying the collected system monitoring data that contains the attack traces. The audience will have the option to perform the APT attack themselves under our guidance, and interact with the system and investigate the attack via issuing queries and checking the query results through our web UI.

Plan-structured deep neural network models for query performance prediction

Query performance prediction, the task of predicting a query's latency prior to execution, is a challenging problem in database management systems. Existing approaches rely on features and performance models engineered by human experts, but often fail to capture the complex interactions between query operators and input relations, and generally do not adapt naturally to workload characteristics and patterns in query execution plans. In this paper, we argue that deep learning can be applied to the query performance prediction problem, and we introduce a novel neural network architecture for the task: a plan-structured neural network . Our neural network architecture matches the structure of any optimizer-selected query execution plan and predict its latency with high accuracy, while eliminating the need for human-crafted input features. A number of optimizations are also proposed to reduce training overhead without sacrificing effectiveness. We evaluated our techniques on various workloads and we demonstrate that our approach can out-perform the state-of-the-art in query performance prediction.

Heuristic and Cost-Based Optimization for Diverse Provenance Tasks

A well-established technique for capturing database provenance as annotations on data is to instrument queries to propagate such annotations. However, even sophisticated query optimizers often fail to produce efficient execution plans for instrumented queries. We develop provenance-aware optimization techniques to address this problem. Specifically, we study algebraic equivalences targeted at instrumented queries and alternative ways of instrumenting queries for provenance capture. Furthermore, we present an extensible heuristic and cost-based optimization framework utilizing these optimizations. Our experiments confirm that these optimizations are highly effective, improving performance by several orders of magnitude for diverse provenance tasks.

Query optimization mechanisms in the cloud environments: A systematic study

SummaryThe goal of a query optimizer is to provide an optimal Query Execution Plan (QEP) by comparing alternative query plans. In a distributed database system over cloud environment, the relations required by a query plan may be stored at multiple sites. This leads to an exponential increase in the number of possible equivalent plan alternatives to find an optimal QEP. Although it is not computationally reasonable to explore exhaustively all possible plans in such large search space. Although query optimization mechanisms are important in the cloud environments, to the best of our knowledge, there exists no complete and systematic review on investigating these issues. Therefore, in this paper, four categories to study these mechanisms are considered which are search‐based, machine learning‐based, schema‐based, and security‐based mechanisms. Also, this paper represents the advantages and disadvantages of the selected query optimization techniques and investigates the metrics of their techniques. Finally, the important challenges of these techniques are reviewed to develop more efficient query optimization techniques in the future.