DBMS metrology: Measuring query time

Sabah Currim, Richard T. Snodgrass, Young Kyoon Suh, Rui Zhang

Research output: Contribution to journalArticle

4 Scopus citations

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 2016

    Fingerprint

Keywords

  • Accuracy
  • Database ergalics
  • Repeatability
  • Tucson timing protocol

ASJC Scopus subject areas

  • Information Systems

Cite this