Skip to content

workflow.md

Latest commit

 

History

History
402 lines (286 loc) · 12 KB

workflow.md

File metadata and controls

402 lines (286 loc) · 12 KB
Raw

Benchmarking Workflows

Common patterns and workflows for running benchmarks with BenchBox.

Quick Start Workflow

The simplest path from installation to results:

uv pip install benchbox

benchbox run --benchmark tpch --platform duckdb --scale 0.1

benchbox results --limit 1

What happens:

  1. BenchBox generates TPC-H data at scale factor 0.1 (~100MB)
  2. Loads data into DuckDB (stored in a database file in the runs root)
  3. Executes all 22 TPC-H queries
  4. Saves the result JSON in the runs root results/ directory

Time to complete: ~2-3 minutes

See: Getting Started Guide

Development Workflow

Typical workflow for developers testing query changes or experimenting:

benchbox datagen --benchmark tpch --scale 0.01 --output ./data/tpch_0.01

benchbox run --benchmark tpch --platform duckdb --scale 0.01 \
  --output ./data/tpch_0.01 \
  --queries Q1,Q3,Q7 \
  --verbose

benchbox run --benchmark tpch --platform duckdb --scale 0.01 \
  --output ./data/tpch_0.01

Benefits:

  • Data generation happens once (can be slow for large scale factors)
  • Iterate quickly on specific queries
  • Full validation before committing changes

Use Cases:

  • Testing query modifications
  • Debugging platform adapter issues
  • Developing custom benchmarks

Multi-Platform Comparison Workflow

Compare performance across different database platforms:

benchbox datagen --benchmark tpch --scale 1 --output ./data/tpch_1

for platform in duckdb clickhouse-local; do
  benchbox run --benchmark tpch --platform $platform --scale 1 \
    --output benchmark_runs/tpch_1_${platform}
done

benchbox results --limit 2

Find the two result files with benchbox results and pass them to benchbox compare.

Output:

  • Side-by-side query timing comparison
  • Geometric mean calculations
  • Performance regression detection

See: Platform Comparison Matrix

Cloud Platform Workflow

Running benchmarks on cloud platforms (BigQuery, Snowflake, Databricks):

export DATABRICKS_TOKEN="dapi..."
export DATABRICKS_HOST="https://....cloud.databricks.com"

benchbox run --benchmark tpcds --platform databricks --scale 1 \
  --dry-run ./preview

benchbox run --benchmark tpcds --platform databricks --scale 1 \
  --platform-option uc_catalog=hive_metastore \
  --platform-option uc_schema=benchbox_test

The steps are: set the cloud credentials, preview the queries without executing them (to avoid costs), run the benchmark, and read the results with benchbox results. Review the generated queries under ./preview/<benchmark>_<platform>_queries_<timestamp>/ before the real run, because the second command executes queries and incurs costs. The saved results include cloud execution metadata.

External Table Mode:

Skip native table materialization (COPY/CTAS) and query directly over staged Parquet files:

benchbox run --benchmark tpch --platform snowflake --scale 1 \
  --table-mode external \
  --platform-option staging_root=s3://my-bucket/benchbox/

This is useful for quick file-based comparisons across engines without paying for data loading. See the Platform Comparison Guide for supported platforms.

Cost Control:

  • Use --dry-run to preview queries before execution
  • Start with small scale factors (0.1, 1)
  • Set platform-specific cost limits (BigQuery maximum_bytes_billed)
  • Use auto-suspend/auto-resume for warehouse platforms

See: Platform Selection Guide

Dry Run Workflow

Preview benchmark execution without running queries:

benchbox run --benchmark tpcds --platform bigquery --scale 10 \
  --dry-run ./preview

ls ./preview

The dry run writes generated queries and configuration into ./preview/: one .sql file per query under <benchmark>_<platform>_queries_<timestamp>/ (such as query_18.sql), a matching schema file, the DDL preview, and timestamped JSON/YAML run previews.

Use Cases:

  • Query validation before cloud execution
  • Cost estimation (query complexity, data scanned)
  • Debugging query generation logic
  • Sharing queries with team members

See: Dry Run Guide

CI/CD Workflow

Automated benchmarking in continuous integration:

name: Benchmark Tests

on: [pull_request]

jobs:
  benchmark:
    runs-on: ubuntu-latest
    steps:
      - uses: actions/checkout@v3

      - name: Install BenchBox
        run: uv pip install benchbox

      - name: Run regression test
        run: |
          benchbox run --benchmark tpch --platform duckdb --scale 0.01 \
            --output results/

      - name: Compare against baseline
        run: |
          benchbox compare \
            baseline/tpch_0.01_duckdb.json \
            $(benchbox results --paths --limit 1) \
            --fail-on-regression 10%

Benefits:

  • Catch performance regressions before merge
  • Validate query compatibility across platforms
  • Track performance trends over time

See: Testing Guide

Compliance Workflow (Official TPC Benchmarks)

Running benchmarks according to TPC specifications for official results:

benchbox run --official --benchmark tpch --platform snowflake --scale 100 \
  --phases power \
  --seed 42 \
  --output results/power/

benchbox run --official --benchmark tpch --platform snowflake --scale 100 \
  --phases throughput \
  --concurrency 4 \
  --seed 42 \
  --output results/throughput/

benchbox metrics qphh \
  --power-results results/power/results.json \
  --throughput-results results/throughput/results.json

The first command is the Power Test (a single query stream), the second is the Throughput Test (concurrent streams), and benchbox metrics qphh calculates the composite metric from both.

run-official is deprecated (kept for backward compatibility) in favor of benchbox run --official; see CLI Reference → Deprecated: run-official for the full accepted option list (--test-type was never a real option — official test selection is via --phases).

Requirements:

  • Specific scale factors (1, 10, 30, 100, 300, 1000, 3000, 10000, 30000, 100000)
  • Random seed management
  • Refresh function execution
  • Full validation

See:

Performance Tuning Workflow

Optimizing query performance with platform-specific tunings:

benchbox run --benchmark tpcds --platform clickhouse-local --scale 10 \
  --output baseline/

benchbox run --benchmark tpcds --platform clickhouse-local --scale 10 \
  --tuning tunings/clickhouse_tpcds.yaml \
  --output tuned/

benchbox results --limit 2

The first run is the baseline with no tunings. The second applies tunings, and benchbox compare (with the two result files from benchbox results) compares them. List built-in templates with benchbox tuning list or generate a starter file with benchbox tuning init --platform clickhouse.

Example Tuning Config (tunings/clickhouse_tpcds.yaml, excerpt — see examples/tunings/clickhouse/tpcds_tuned.yaml for the full file):

table_tunings:
  STORE_SALES:
    table_name: STORE_SALES
    sorting:
    - name: SS_ITEM_SK
      type: INTEGER
      order: 1
    - name: SS_TICKET_NUMBER
      type: INTEGER
      order: 2

See: Performance Guide

Data Generation Workflow

Generating benchmark data separately from execution:

for sf in 0.01 0.1 1 10; do
  benchbox datagen --benchmark tpch --scale $sf \
    --output ./data/tpch_${sf}
done

tar -czf tpch_data.tar.gz ./data/

benchbox run --benchmark tpch --platform duckdb --scale 1 \
  --output ./data/tpch_1

Benefits:

  • Generate data once, use many times
  • Share data across team members
  • Version control data generation parameters
  • Faster iteration on query/platform testing

See: Data Generation Guide

Validation Workflow

Verifying benchmark results for correctness:

benchbox run --benchmark tpch --platform duckdb --scale 0.1 \
  --validation full

Validation compares query row counts against expected values:

Validation Modes:

  • disabled: No validation (fastest)
  • loose: Row-count checks with scale tolerance
  • exact: Exact row-count match
  • range: Row count must fall within the expected min/max
  • full: Full result validation (slowest, most thorough)

See: TPC Validation Guide

Monitoring Workflow

Tracking benchmark performance over time:

benchbox run --benchmark tpch --platform duckdb --scale 1 \
  --output benchmark_runs/$(date +%Y%m%d)/

benchbox aggregate \
  --input-dir benchmark_runs/ \
  --output-file performance_trends.csv

benchbox visualize benchmark_runs/results/*.json

Metrics Tracked:

  • Query execution times (p50, p95, p99)
  • Geometric mean
  • Total execution time
  • Data loading time
  • Memory usage
  • Failure rates

See: Performance Monitoring

Debugging Workflow

Troubleshooting benchmark issues:

benchbox run --benchmark tpch --platform duckdb --scale 0.01 \
  -vv > debug.log 2>&1

benchbox run --benchmark tpch --platform duckdb --scale 0.01 \
  --queries Q1 \
  --verbose \
  --show-plans

benchbox shell --platform duckdb --last

The first command enables debug logging and uses the shell to redirect output to a file. The second runs a single query with maximum detail, and --show-plans shows the query plan. The last command opens an interactive SQL shell on the most recent database to inspect its state.

Common Issues:

  • Data generation failures → Check disk space, permissions
  • Query failures → Check SQL dialect compatibility
  • Performance issues → Check scale factor vs. available memory
  • Connection errors → Verify credentials, network access

See: Troubleshooting Guide

Custom Benchmark Workflow

Creating and running a custom benchmark:

from benchbox.base import BaseBenchmark

class MyBenchmark(BaseBenchmark):
    def generate_data(self):
        ...

    def get_queries(self):
        return {
            "q1": "SELECT ...",
            "q2": "SELECT ...",
        }

    def get_query(self, query_id):
        return self.get_queries()[query_id]

See: Custom Benchmarks Guide

Best Practices

General Recommendations

  1. Start Small: Begin with a scale factor of 0.01 or 0.1 for testing
  2. Use Dry Run: Preview queries before expensive cloud execution
  3. Generate Once: Reuse generated data across multiple runs
  4. Version Control: Track benchmark configurations and results
  5. Monitor Costs: Set budget alerts for cloud platforms

Performance Optimization

  1. Match Platform to Workload: Use DuckDB for development, cloud platforms for production
  2. Optimize Data Loading: Use platform-specific bulk loading (COPY, external tables)
  3. Apply Tunings: Use partitioning, clustering, indexes for large datasets
  4. Measure Baselines: Establish baseline performance before optimizations

Compliance and Validation

  1. Follow TPC Specs: Use official runners for compliance testing
  2. Validate Results: Enable validation for correctness verification
  3. Document Configuration: Record all benchmark parameters and system info
  4. Reproduce Results: Use fixed seeds and version-locked dependencies

Related Documentation