Cost Models for Big Data Query Processing: Learning, Retrofitting, and Our Findings

Abstract

Query processing over big data is ubiquitous in modern clouds, where the system takes care of picking both the physical query execution plans and the resources needed to run those plans, using a cost-based query optimizer. A good cost model, therefore, is akin to better resource efficiency and lower operational costs. Unfortunately, the production workloads at Microsoft show that costs are very complex to model for big data systems. In this work, we investigate two key questions: (i) can we learn accurate cost models for big data systems, and (ii) can we integrate the learned models within the query optimizer. To answer these, we make three core contributions. First, we exploit workload patterns to learn a large number of individual cost models and combine them to achieve high accuracy and coverage over a long period. Second, we propose extensions to Cascades framework to pick optimal resources, i.e, number of containers, during query planning. And third, we integrate the learned cost models within the Cascade-style query optimizer of SCOPE at Microsoft. We evaluate the resulting system, Cleo, in a production environment using both production and TPC-H workloads. Our results show that the learned cost models are 2 to 3 orders of magnitude more accurate, and 20× more correlated with the actual runtimes, with a large majority (70%) of the plan changes leading to substantial improvements in latency as well as resource usage.

A Survey on Advancing the DBMS Query Optimizer: Cardinality Estimation, Cost Model, and Plan Enumeration

“…Furthermore, when the cost-based optimizer is deployed in a distributed or parallel database, the cloud environment, or the cross-platform query engines, the complexity of cost model is increasing dramatically. Moreover, even with the true cardinality, the cost estimation of a query is not linear to the running time, which may lead to a suboptimal execution plan [45,81].…”

“…Due to the difficulty in collecting statistics and the needs of picking the resources in big data systems, particularly in modern cloud data services, Siddiqui et al [81] propose a learning-based cost model and integrate it into the optimizer of SCOPE [7]. They build large number of small models to predict the costs of common (sub)queries, which are extracted from the workload history.…”

“…It is interesting to consider these related information into the cost model. For example, Siddiqui et al [81] consider the number of container into their cost model. (2) Learning-based methods Learning-based methods to estimate the cost also suffer from the same problems with methods in cardinality estimation (Sect.…”

“…3.4.2). The model that has been adopted in a real system is a light model [81]. The trade-off between accuracy and training time is still a problem.…”

Modified Firefly Algorithm for Optimizing Biomedical Breast Cancer Queries

“…The different cost models associated with the big data systems were studied in research [16]. The currently available cost models depending on the cardinality measure and the learned cost model depending on operators, common sub expression were also discussed.…”