Learning-based Query Performance Modeling and Prediction

Abstract

Abstract-Accurate query performance prediction (QPP) is central to effective resource management, query optimization and query scheduling. Analytical cost models, used in current generation of query optimizers, have been successful in comparing the costs of alternative query plans, but they are poor predictors of execution latency. As a more promising approach to QPP, this paper studies the practicality and utility of sophisticated learningbased models, which have recently been applied to a variety of predictive tasks with great success, in both static (i.e., fixed) and dynamic query workloads.We propose and evaluate predictive modeling techniques that learn query execution behavior at different granularities, ranging from coarse-grained plan-level models to fine-grained operatorlevel models. We demonstrate that these two extremes offer a tradeoff between high accuracy for static workload queries and generality to unforeseen queries in dynamic workloads, respectively, and introduce a hybrid approach that combines their respective strengths by selectively composing them in the process of QPP. We discuss how we can use a training workload to (i) pre-build and materialize such models offline, so that they are readily available for future predictions, and (ii) build new models online as new predictions are needed. All prediction models are built using only static features (available prior to query execution) and the performance values obtained from the offline execution of the training workload.We fully implemented all these techniques and extensions on top of PostgreSQL and evaluated them experimentally by quantifying their effectiveness over analytical workloads, represented by well-established TPC-H data and queries. The results provide quantitative evidence that learning-based modeling for QPP is both feasible and effective for both static and dynamic workload scenarios.

Twitter Normalization via 1-to-N Recovering

“…Firstly, this information is based on the cost model estimation, which has been proven as ineffective [2,6]. Secondly, most of the open source triple stores fail to provide explicit query plans.…”

“…Evaluation Metric We followed the suggestion in [2] and used the mean relative error as our prediction metric:…”

“…Therefore, approaches that exploit the machine learning techniques to build predictive models have been proposed [2,6]. These approaches treat the database system as a black box and focus on learning a query performance predictive model, which are evaluated as feasible and effective [2]. These works extract the features of queries by exploring the query plan which can provide estimations such as execution time, row count and these two estimations for each operator.…”

Towards predicting query execution time for concurrent and dynamic database workloads

“…Predicting query execution time has recently gained significant interest in database research community [2,3,6,8,13,28]. In [8], the authors considered the problem of predicting multiple performance metrics such as execution time and disk I/O's for database queries, by representing the queries with a set of handpicked features and using Kernel Canonical Correlation Analysis (KCCA) [4] as the predictive model.…”

“…The current trend of offering database as a service (DaaS) makes this capacity even more attractive, since a DaaS provider needs to honor service level agreements (SLAs) to avoid loss of revenue and reputation. Recently, there has been substantial work on query execution time prediction [2,3,6,8,13,28]. Much of this work focuses on predicting the execution time for a single standalone query [3,8,13,28], while only a fraction of this work considers the more challenging problem of predicting the execution time for multiple concurrently-running queries [2,6].…”