Logo
ScienceStack
Table of Contents
Fetching ...
Sign In

ResQ: Realistic Performance-Aware Query Generation

Zhengle Wang, Yanfei Zhang, Chunwei Liu

TL;DR

ResQ tackles the challenge of generating executable SQL workloads that faithfully mirror per-query execution targets observed in anonymized cloud traces while preserving privacy. It introduces a two-phase, execution-aware query graph generation pipeline that first bounds the search space using data-access and structure constraints and then instantiates predicates via a Bayesian-optimization-guided search, augmented by repetition-aware query pools and lightweight local performance models. By formalizing RPQG and deploying data-driven cost models, ResQ achieves high fidelity to target execution profiles on Bendset, Snowset, and Redset, delivering substantial efficiency gains and robust per-query accuracy. The approach enables realistic benchmarking, regression testing, and system tuning under privacy constraints, with Bendset released to catalyze reproducible research in the field.

Abstract

Database research and development rely heavily on realistic user workloads for benchmarking, instance optimization, migration testing, and database tuning. However, acquiring real-world SQL queries is notoriously challenging due to strict privacy regulations. While cloud database vendors have begun releasing anonymized performance traces to the research community, these traces typi- cally provide only high-level execution statistics without the origi- nal query text or data, which is insufficient for scenarios that require actual execution. Existing tools fail to capture fine-grained perfor- mance patterns or generate runnable workloads that reproduce these public traces with both high fidelity and efficiency. To bridge this gap, we propose ResQ, a fine-grained workload synthesis sys- tem designed to generate executable SQL workloads that faithfully match the per-query execution targets and operator distributions of production traces. ResQ constructs execution-aware query graphs, instantiates them into SQL via Bayesian Optimization-driven pred- icate search, and explicitly models workload repetition through reuse at both exact-query and parameterized-template levels. To ensure practical scalability, ResQ combines search-space bounding with lightweight local cost models to accelerate optimization. Ex- periments on public cloud traces (Snowset, Redset) and a newly released industrial trace (Bendset) demonstrate that ResQ signif- icantly outperforms state-of-the-art baselines, achieving 96.71% token savings and a 86.97% reduction in runtime, while lowering maximum Q-error by 14.8x on CPU time and 997.7x on scanned bytes, and closely matching operator composition.

ResQ: Realistic Performance-Aware Query Generation

TL;DR

ResQ tackles the challenge of generating executable SQL workloads that faithfully mirror per-query execution targets observed in anonymized cloud traces while preserving privacy. It introduces a two-phase, execution-aware query graph generation pipeline that first bounds the search space using data-access and structure constraints and then instantiates predicates via a Bayesian-optimization-guided search, augmented by repetition-aware query pools and lightweight local performance models. By formalizing RPQG and deploying data-driven cost models, ResQ achieves high fidelity to target execution profiles on Bendset, Snowset, and Redset, delivering substantial efficiency gains and robust per-query accuracy. The approach enables realistic benchmarking, regression testing, and system tuning under privacy constraints, with Bendset released to catalyze reproducible research in the field.

Abstract

Database research and development rely heavily on realistic user workloads for benchmarking, instance optimization, migration testing, and database tuning. However, acquiring real-world SQL queries is notoriously challenging due to strict privacy regulations. While cloud database vendors have begun releasing anonymized performance traces to the research community, these traces typi- cally provide only high-level execution statistics without the origi- nal query text or data, which is insufficient for scenarios that require actual execution. Existing tools fail to capture fine-grained perfor- mance patterns or generate runnable workloads that reproduce these public traces with both high fidelity and efficiency. To bridge this gap, we propose ResQ, a fine-grained workload synthesis sys- tem designed to generate executable SQL workloads that faithfully match the per-query execution targets and operator distributions of production traces. ResQ constructs execution-aware query graphs, instantiates them into SQL via Bayesian Optimization-driven pred- icate search, and explicitly models workload repetition through reuse at both exact-query and parameterized-template levels. To ensure practical scalability, ResQ combines search-space bounding with lightweight local cost models to accelerate optimization. Ex- periments on public cloud traces (Snowset, Redset) and a newly released industrial trace (Bendset) demonstrate that ResQ signif- icantly outperforms state-of-the-art baselines, achieving 96.71% token savings and a 86.97% reduction in runtime, while lowering maximum Q-error by 14.8x on CPU time and 997.7x on scanned bytes, and closely matching operator composition.
Paper Structure (44 sections, 1 theorem, 11 equations, 4 figures, 5 tables, 2 algorithms)

This paper contains 44 sections, 1 theorem, 11 equations, 4 figures, 5 tables, 2 algorithms.

Table of Contents

  1. Introduction
  2. Problem Definition
  3. System Architecture of ResQ
  4. Generator Data Preparation
  5. Workload Trace Preparation
  6. Test Data Preparation
  7. Two-Phase Query Graph Generation
  8. Query Graph Translator
  9. Performance Prediction
  10. Query Pool
  11. Two-Phase Query Graph Generation
  12. From Realistic Query Generation to Query Graph Generation
  13. Why Query Graph
  14. Query Graph
  15. Problem Reformulation
  16. ...and 29 more sections

Key Result

proposition 1

For the execution targets considered in this paper (e.g., scanned bytes and CPU time), feasibility and optimization are largely determined by (i) operator composition and dependencies and (ii) accessed data volume and predicate selectivity. These factors are explicitly represented in a query graph.

Figures (4)

  • Figure 1: Workload synthesis.
  • Figure 2: System architecture of ResQ.
  • Figure 3: Q-error CDFs for CPUTime (top) and ScannedBytes (bottom): (a) Bendset Trace 2, (b) Redset trace, and (c) Snowset trace.
  • Figure 4: Ablation Study of Local Model

Theorems & Definitions (12)

  • Definition 2.1: Testing Environment $\mathbf{D}$
  • Definition 2.2: Parametric Query Template
  • Definition 2.3: Constraint Function and Constraint
  • Definition 2.4: Targeted Query Generation
  • Definition 2.5: Structure-Aware Constraints $\mathbf{C}$
  • Example 2.1: Structure-aware constraints
  • Definition 2.6: Execution Performance Targets $\mathbf{Y}$
  • Example 2.2: Execution targets
  • Definition 2.7: Realistic Performance-Aware Query Generation (RPQG)
  • Definition 4.1: Query Graph $\mathbf{G}$
  • ...and 2 more