Towards Adaptive Costing of Database Access Methods

Ye, Qin; Salem, Kenneth; Goel, Anil Kumar

doi:10.1109/icdew.2007.4401030

Cited by 5 publications

(5 citation statements)

References 12 publications

Supporting

Mentioning

Contrasting

Order By: Relevance

“…In [22] and [12] they focus on the accuracy of cost model. The impact of I/O cost estimation on quality of query optimizers has been studied in [17]. In [14] they present ways to quantify the contributions of cardinality estimation, the cost model and the plan enumeration algorithm and provide guidelines for the complete design of a query optimizer.A number of papers addressed the problem of testing cardinality estimation models.…”

Section: Related Workmentioning

confidence: 99%

OptMark

Papaemmanouil

Cherniack

2016

Proceedings of the 25th ACM International on Conference on Information and Knowledge Management

View full text Add to dashboard Cite

Query optimizers have long been considered as among the most complex components of a database engine, while the assessment of an optimizer's quality remains a challenging task. Indeed, existing performance benchmarks for database engines (like TPC benchmarks) produce a performance assessment of the query runtime system rather than its query optimizer. To address this challenge, this paper introduces OptMark, a toolkit for evaluating the quality of a query optimizer. OptMark is designed to offer a number of desirable properties. First, it decouples the quality of an optimizer from the quality of its underlying execution engine. Second it evaluates independently both the effectiveness of an optimizer (i.e., quality of the chosen plans) and its efficiency (i.e., optimization time). OptMark includes also a generic benchmarking toolkit that is minimum invasive to the DBMS that wishes to use it. Any DBMS can provide a system-specific implementation of a simple API that allows OptMark to run and generate benchmark scores for the specific system. This paper discusses the metrics we propose for evaluating an optimizer's quality, the benchmark's design and the toolkit's API and functionality. We have implemented Opt-Mark on the open-source MySQL engine as well as two commercial database systems. Using these implementations we are able to assess the quality of the optimizers on these three systems based on the TPC-DS benchmark queries.

show abstract

Section: Related Workmentioning

confidence: 99%

OptMark

Papaemmanouil

Cherniack

2016

Proceedings of the 25th ACM International on Conference on Information and Knowledge Management

View full text Add to dashboard Cite

show abstract

“…In [23] they introduce a metric to assess the accuracy across the entire search space while in [13] they develop a framework to quantify the accuracy for a given query workload. The impact of I/O cost estimation on quality of query optimizers has been studied in [17].…”

Section: Related Workmentioning

confidence: 99%

“…Some of our results also reveal this fact. [17] test the robustness of an optimizer. Finally, there is related work on testing queries instead of query optimizer.…”

Section: Related Workmentioning

confidence: 99%

OptMark: A Toolkit for Benchmarking Query Optimizers

Zhan¹,

Papaemmanouil²,

Cherniack³

2016

Preprint

View full text Add to dashboard Cite

Query optimizers have long been considered as among the most complex components of a database engine, while the assessment of an optimizer's quality remains a challenging task. Indeed, existing performance benchmarks for database engines (like TPC benchmarks) produce a performance assessment of the query runtime system rather than its query optimizer. To address this challenge, this paper introduces OptMark, a toolkit for evaluating the quality of a query optimizer. OptMark is designed to offer a number of desirable properties. First, it decouples the quality of an optimizer from the quality of its underlying execution engine. Second it evaluates independently both the effectiveness of an optimizer (i.e., quality of the chosen plans) and its efficiency (i.e., optimization time). OptMark includes also a generic benchmarking toolkit that is minimum invasive to the DBMS that wishes to use it. Any DBMS can provide a system-specific implementation of a simple API that allows OptMark to run and generate benchmark scores for the specific system. This paper discusses the metrics we propose for evaluating an optimizer's quality, the benchmark's design and the toolkit's API and functionality. We have implemented OptMark on the open-source MySQL engine as well as two commercial database systems. Using these implementations we are able to assess the quality of the optimizers on these three systems based on the TPC-DS benchmark queries.

show abstract

“…Reference [18] described how an inaccurate storage cost model in the database query optimizer can significantly impact the choice of query execution plans. Reference [17] proposed an end-to-end database and storage planning technique by characterizing the storage I/O workload of a given database workload using an independent combination of database and storage analysis. While sharing the same spirit, our work brings a much tighter coupling of database-level and storage-level information as well as capturing their interdependence using a novel Annotated Plan Graph abstraction described in Section 3.…”

Section: Related Workmentioning

confidence: 99%

“…For example, decisions like the choice of storage required for given database workloads or choice of DB query plan given the storage infrastructure can be intelligently made using these techniques. An early work by Salem et al [17] presented a similar approach for such integrated planning, though it uses a concatenation of independent database and storage analysis components. In contrast, annotated plan graphs provide a much tighter integration with information flow between the two layers aiding in analysis.…”

Section: The Potential Of Integrated Database and San Toolsmentioning

confidence: 99%

Why Did My Query Slow Down?

Borisov,

Babu,

Uttamchandani

et al. 2009

Preprint

View full text Add to dashboard Cite

Many enterprise environments have databases running on network-attached server-storage infrastructure (referred to as Storage Area Networks or SANs). Both the database and the SAN are complex systems that need their own separate administrative teams. This paper puts forth the vision of an innovative management framework to simplify administrative tasks that require an in-depth understanding of both the database and the SAN. As a concrete instance, we consider the task of diagnosing the slowdown in performance of a database query that is executed multiple times (e.g., in a periodic report-generation setting). This task is very challenging because the space of possible causes includes problems specific to the database, problems specific to the SAN, and problems that arise due to interactions between the two systems. In addition, the monitoring data available from these systems can be noisy.We describe the design of DIADS which is an integrated diagnosis tool for database and SAN administrators. DIADS generates and uses a powerful abstraction called Annotated Plan Graphs (APGs) that ties together the execution path of queries in the database and the SAN. Using an innovative workflow that combines domain-specific knowledge with machine-learning techniques, DIADS was applied successfully to diagnose query slowdowns caused by complex combinations of events across a PostgreSQL database and a production SAN.

show abstract

Towards Adaptive Costing of Database Access Methods

Cited by 5 publications

References 12 publications

OptMark

OptMark

OptMark: A Toolkit for Benchmarking Query Optimizers

Why Did My Query Slow Down?

Contact Info

Product

Resources

About