Query and Resource Optimization: Bridging the Gap

Viswanathan, Lalitha; Jindal, Alekh; Karanasos, Konstantinos

doi:10.1109/icde.2018.00156

Cited by 17 publications

(7 citation statements)

References 7 publications

Supporting

Mentioning

Contrasting

Order By: Relevance

“…The degree of parallelism (i.e., the number of machines or containers allocated for each operator) is a key factor in determining the runtime of queries in massively parallel databases [46], which implicitly depends on the partition count. This makes partition count as an important feature in determining the cost of an operator (as noted in Figures 5-6).…”

Section: Resource-aware Query Planningmentioning

confidence: 99%

“…This stems from the observation that while some prior works have considered learning models for predicting query execution times for a given physical plan in traditional databases [2,5,19,32], none of them have integrated learned models within a query optimizer for selecting physical plans. Moreover, in big data systems, resources (in particular the number of machines) play a significant role in cost estimation [46], making the integration even more challenging. Thus, we investigate the effects of learned cost models on query plans by extending the SCOPE query optimizer in a minimally invasive way for predicting costs in a resource-aware manner.…”

mentioning

confidence: 99%

“…We extend the optimizer to invoke the learned models, instead of the default cost models, to estimate the cost of candidate operators. However, in big data systems, the cost depends heavily on the resources used (e.g., number of machines for each operator) by the optimizer [46]. Therefore, we extend the Cascades framework to explore resources, and propose mechanisms to explore and derive optimal number of machines for each stage in a query plan.…”

mentioning

confidence: 99%

See 2 more Smart Citations

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

Siddiqui

Jindal

Qiao

et al. 2020

Proceedings of the 2020 ACM SIGMOD International Conference on Management of Data

Self Cite

View full text Add to dashboard Cite

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.

show abstract

Section: Resource-aware Query Planningmentioning

confidence: 99%

mentioning

confidence: 99%

mentioning

confidence: 99%

See 1 more Smart Citation

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

Siddiqui

Jindal

Qiao

et al. 2020

Proceedings of the 2020 ACM SIGMOD International Conference on Management of Data

Self Cite

View full text Add to dashboard Cite

show abstract

“…Unfortunately, these reactive approaches take several minutes to react [21] and many of the optimization opportunities may already be missed. Additionally, reactively adjusting resources during the course of a query execution could even lead to expensive changes in the query plan [38]. Therefore, apart from the reactive approaches, we also need predictive resource allocation to provide a good starting point in the first place.…”

Section: Introductionmentioning

confidence: 99%

“…In this paper, we study predictive price-perf optimization in serverless query processing setting, i.e., resources are allocated and users are charged at the query level. We build on top of our prior work on the relationship between query performance and resources in Hive and Spark [38], predictive degree of parallelism in SQL Server [26], peak [36], adaptive [23] and optimal [32] allocation in SCOPE [25] jobs, and present an end-to-end framework for predictive price-perf optimization at the query level. We recently demonstrated this system design and concept [37], and in this paper we present more generalized models, detailed architecture, analysis, and results, apart from Spark optimizer extensions that combine both the predictive and the reactive approaches.…”

Section: Introductionmentioning

confidence: 99%

Predictive Price-Performance Optimization for Serverless Query Processing

Sen¹,

Roy²,

Jindal³

2021

Preprint

Self Cite

View full text Add to dashboard Cite

We present an efficient, parametric modeling framework for predictive resource allocations, focusing on the amount of computational resources, that can optimize for a range of price-performance objectives for data analytics in serverless query processing settings. We discuss and evaluate in depth how our system, AutoExecutor, can use this framework to automatically select near-optimal executor and core counts for Spark SQL queries running on Azure Synapse.Our techniques improve upon Spark's in-built, reactive, dynamic executor allocation capabilities by substantially reducing the total executors allocated and executor occupancy while running queries, thereby freeing up executors that can potentially be used by other concurrent queries or in reducing the overall cluster provisioning needs. In contrast with post-execution analysis tools such as Sparklens, we predict resource allocations for queries before executing them and can also account for changes in input data sizes for predicting the desired allocations.

show abstract