DBMS metrology: Measuring query time

Sabah Currim, Richard Thomas Snodgrass, Young Kyoon Suh, Rui Zhang

Research output: Contribution to journalArticle

4 Citations (Scopus)

Abstract

It is surprisingly hard to obtain accurate and precise measurements of the time spent executing a query because there are many sources of variance. To understand these sources, we review relevant per-process and overall measures obtainable from the Linux kernel and introduce a structural causal model relating these measures. A thorough correlational analysis provides strong support for this model. We attempted to determine why a particular measurement wasn't repeatable and then to devise ways to eliminate or reduce that variance. This enabled us to articulate a timing protocol that applies to proprietary DBMSes, that ensures the repeatability of a query, and that obtains a quite accurate query execution time while dropping very few outliers. This resulting query time measurement procedure, termed the Tucson Timing Protocol Version 2 (TTPv2), consists of the following steps: (i) perform sanity checks to ensure data validity; (ii) drop some query executions via clearly motivated predicates; (iii) drop some entire queries at a cardinality, again via clearly motivated predicates; (iv) for those that remain, compute a single measured time by a carefully justified formula over the underlying measures of the remaining query executions; and (v) perform post-analysis sanity checks. The result is a mature, general, robust, self-checking protocol that provides a more precise and more accurate timing of the query. The protocol is also applicable to other operating domains in which measurements of multiple processes each doing computation and I/O is needed.

Original languageEnglish (US)
Article number3
JournalACM Transactions on Database Systems
Volume42
Issue number1
DOIs
StatePublished - Nov 1 2016

Fingerprint

Time measurement
Linux

Keywords

  • Accuracy
  • Database ergalics
  • Repeatability
  • Tucson timing protocol

ASJC Scopus subject areas

  • Information Systems

Cite this

DBMS metrology : Measuring query time. / Currim, Sabah; Snodgrass, Richard Thomas; Suh, Young Kyoon; Zhang, Rui.

In: ACM Transactions on Database Systems, Vol. 42, No. 1, 3, 01.11.2016.

Research output: Contribution to journalArticle

Currim, Sabah ; Snodgrass, Richard Thomas ; Suh, Young Kyoon ; Zhang, Rui. / DBMS metrology : Measuring query time. In: ACM Transactions on Database Systems. 2016 ; Vol. 42, No. 1.
@article{f0d7d73c445b43e78eb5d53a3178d182,
title = "DBMS metrology: Measuring query time",
abstract = "It is surprisingly hard to obtain accurate and precise measurements of the time spent executing a query because there are many sources of variance. To understand these sources, we review relevant per-process and overall measures obtainable from the Linux kernel and introduce a structural causal model relating these measures. A thorough correlational analysis provides strong support for this model. We attempted to determine why a particular measurement wasn't repeatable and then to devise ways to eliminate or reduce that variance. This enabled us to articulate a timing protocol that applies to proprietary DBMSes, that ensures the repeatability of a query, and that obtains a quite accurate query execution time while dropping very few outliers. This resulting query time measurement procedure, termed the Tucson Timing Protocol Version 2 (TTPv2), consists of the following steps: (i) perform sanity checks to ensure data validity; (ii) drop some query executions via clearly motivated predicates; (iii) drop some entire queries at a cardinality, again via clearly motivated predicates; (iv) for those that remain, compute a single measured time by a carefully justified formula over the underlying measures of the remaining query executions; and (v) perform post-analysis sanity checks. The result is a mature, general, robust, self-checking protocol that provides a more precise and more accurate timing of the query. The protocol is also applicable to other operating domains in which measurements of multiple processes each doing computation and I/O is needed.",
keywords = "Accuracy, Database ergalics, Repeatability, Tucson timing protocol",
author = "Sabah Currim and Snodgrass, {Richard Thomas} and Suh, {Young Kyoon} and Rui Zhang",
year = "2016",
month = "11",
day = "1",
doi = "10.1145/2996454",
language = "English (US)",
volume = "42",
journal = "ACM Transactions on Database Systems",
issn = "0362-5915",
publisher = "Association for Computing Machinery (ACM)",
number = "1",

}

TY - JOUR

T1 - DBMS metrology

T2 - Measuring query time

AU - Currim, Sabah

AU - Snodgrass, Richard Thomas

AU - Suh, Young Kyoon

AU - Zhang, Rui

PY - 2016/11/1

Y1 - 2016/11/1

N2 - It is surprisingly hard to obtain accurate and precise measurements of the time spent executing a query because there are many sources of variance. To understand these sources, we review relevant per-process and overall measures obtainable from the Linux kernel and introduce a structural causal model relating these measures. A thorough correlational analysis provides strong support for this model. We attempted to determine why a particular measurement wasn't repeatable and then to devise ways to eliminate or reduce that variance. This enabled us to articulate a timing protocol that applies to proprietary DBMSes, that ensures the repeatability of a query, and that obtains a quite accurate query execution time while dropping very few outliers. This resulting query time measurement procedure, termed the Tucson Timing Protocol Version 2 (TTPv2), consists of the following steps: (i) perform sanity checks to ensure data validity; (ii) drop some query executions via clearly motivated predicates; (iii) drop some entire queries at a cardinality, again via clearly motivated predicates; (iv) for those that remain, compute a single measured time by a carefully justified formula over the underlying measures of the remaining query executions; and (v) perform post-analysis sanity checks. The result is a mature, general, robust, self-checking protocol that provides a more precise and more accurate timing of the query. The protocol is also applicable to other operating domains in which measurements of multiple processes each doing computation and I/O is needed.

AB - It is surprisingly hard to obtain accurate and precise measurements of the time spent executing a query because there are many sources of variance. To understand these sources, we review relevant per-process and overall measures obtainable from the Linux kernel and introduce a structural causal model relating these measures. A thorough correlational analysis provides strong support for this model. We attempted to determine why a particular measurement wasn't repeatable and then to devise ways to eliminate or reduce that variance. This enabled us to articulate a timing protocol that applies to proprietary DBMSes, that ensures the repeatability of a query, and that obtains a quite accurate query execution time while dropping very few outliers. This resulting query time measurement procedure, termed the Tucson Timing Protocol Version 2 (TTPv2), consists of the following steps: (i) perform sanity checks to ensure data validity; (ii) drop some query executions via clearly motivated predicates; (iii) drop some entire queries at a cardinality, again via clearly motivated predicates; (iv) for those that remain, compute a single measured time by a carefully justified formula over the underlying measures of the remaining query executions; and (v) perform post-analysis sanity checks. The result is a mature, general, robust, self-checking protocol that provides a more precise and more accurate timing of the query. The protocol is also applicable to other operating domains in which measurements of multiple processes each doing computation and I/O is needed.

KW - Accuracy

KW - Database ergalics

KW - Repeatability

KW - Tucson timing protocol

UR - http://www.scopus.com/inward/record.url?scp=84997241532&partnerID=8YFLogxK

UR - http://www.scopus.com/inward/citedby.url?scp=84997241532&partnerID=8YFLogxK

U2 - 10.1145/2996454

DO - 10.1145/2996454

M3 - Article

AN - SCOPUS:84997241532

VL - 42

JO - ACM Transactions on Database Systems

JF - ACM Transactions on Database Systems

SN - 0362-5915

IS - 1

M1 - 3

ER -