Dynamically optimizing queries over large scale data platforms

Abstract

Enterprises are adapting large-scale data processing platforms, such as Hadoop, to gain actionable insights from their "big data". Query optimization is still an open challenge in this environment due to the volume and heterogeneity of data, comprising both structured and un/semi-structured datasets. Moreover, it has become common practice to push business logic close to the data via userdefined functions (UDFs), which are usually opaque to the optimizer, further complicating cost-based optimization. As a result, classical relational query optimization techniques do not fit well in this setting, while at the same time, suboptimal query plans can be disastrous with large datasets.In this paper, we propose new techniques that take into account UDFs and correlations between relations for optimizing queries running on large scale clusters. We introduce "pilot runs", which execute part of the query over a sample of the data to estimate selectivities, and employ a cost-based optimizer that uses these selectivities to choose an initial query plan. Then, we follow a dynamic optimization approach, in which plans evolve as parts of the queries get executed. Our experimental results show that our techniques produce plans that are at least as good as, and up to 2x (4x) better for Jaql (Hive) than, the best hand-written left-deep query plans.

Big Data Management in the Cloud: Evolution or Crossroad?

“…This proposal can be seen as an elegant adaptation of [39], proposed in a parallel database system, to a cloud system. More generally, with respect to the issue of query optimization in cloud environments, the most recent and relevant proposals are described in [11,41,53,61].…”

Handling Estimation Inaccuracy in Query Optimization

“…Researches on this purpose fall into two main approaches [25]. A first approach, called Single Point-based Optimization [5,17,18,20] consists in monitoring a plan execution so as to detect estimation errors and a resulting sub-optimalty. This latter is corrected by interrupting the current execution and re-optimizing the remainder of the plan using up-to-date statistics.…”

“…A considerable body of literature was dedicated to find solutions to this problem. These solutions include mainly: (i) techniques for better quality of the statistical metadata [7,11,13,22,27,28], (ii) run-time techniques [5,[17][18][19][20] to monitor a query execution and trigger reoptimization of the plan when a sub-optimality is detected, and (iii) compile-time strategies [1-3, 9, 12] that permit the optimizer to generate an execution plan, being aware of the imprecision of used estimates.…”

Fast OLAP query execution in main memory on large data in a cluster

“…Han et al [20] and Karanasos et al [23] both present their approaches to query optimization for distributed query execution by re-optimizing during execution using accurate statistic information about the data at the current stage of query execution. As we optimize query execution by hand, [23] show that collecting information such as the selectivity of predicates before query optimization only causes minor overhead.…”

“…As we optimize query execution by hand, [23] show that collecting information such as the selectivity of predicates before query optimization only causes minor overhead. This accurate information is required to determine which of the strategies we use in this paper is most efficient.…”