Skip to content

pgo_crossdb_non_io_bound_oltp_design.md

Latest commit

 

History

History
157 lines (111 loc) · 5.83 KB

pgo_crossdb_non_io_bound_oltp_design.md

File metadata and controls

157 lines (111 loc) · 5.83 KB
Raw

Cross-DB PGO Design (Non I/O Bound OLTP)

Scope

This document describes a cross-database abstraction for doing PGO (Profile-Guided Optimization) validation on non I/O bound OLTP workloads.

Key constraints:

  • focus on OLTP workloads that are expected to be primarily CPU-bound after warmup
  • allow the benchmark tool to vary per database (not limited to sysbench)
  • produce evidence that can explain why results changed (build/profile/config/system)

Non-goals:

  • do not prescribe production-safe configs
  • do not attempt to validate I/O-bound workload improvements (that is a different track)

Core Principle: Validate “Workload Shape” First

PGO results are only meaningful when the workload shape matches your intent.

For this track (“non I/O bound OLTP”), the baseline/PGO runs must satisfy:

  • low sustained iowait during the core OLTP cases
  • disk throughput is not the dominant limiter (no sustained high read MB/s for cache-friendly cases)
  • DB process CPU stays high during the measurement window

If the baseline is unintentionally I/O-bound (e.g. small buffer/cache config), fix config first and rerun baseline before discussing PGO deltas.

Abstract Architecture (Phase Graph)

Think of the whole workflow as a reproducible pipeline with guardrails:

  1. Prepare host
    • pin to a stable environment (CPU governor, turbo policy, background services)
    • install toolchain and sampling tools (mpstat/iostat/pidstat equivalents)
  2. Build normal (baseline) binary
    • record compiler/version/flags
  3. Provision runtime + benchmark config
    • generate DB config in a deterministic way
    • ensure “benchmark-tuned” defaults are applied
    • record a config snapshot (file + SHOW/SELECT-style variables)
  4. Initialize dataset
    • dataset size and access pattern must match the intended OLTP scenario
  5. Warmup + quiesce
    • wait for startup quiesce (engine warmup, background writes settle)
    • optionally add a fixed settle sleep
  6. Run baseline benchmark (with system sampling)
    • enforce stability rules (reject suspicious runs)
    • verify workload shape is non I/O bound
  7. Build PGO generate binary
  8. Profile generation run
    • run a representative training workload
    • record logs + system sampling
  9. Verify profile validity
    • non-empty profiles
    • profile paths match build root/object paths
  10. Build PGO use binary
    • verify -fprofile-use (or equivalent) is actually applied
  11. Run PGO validation benchmark
    • same benchmark parameters + same workload shape checks
  12. Package + evidence bundle
    • ship binaries + logs + summary + sampling

Evidence Bundle (Minimum)

For every “PGO result” report, keep:

  • normal benchmark log + summary
  • pgo-gen benchmark log + summary
  • pgo-use benchmark log + summary
  • pgo-use build log showing profile-use flags
  • profile stats (count/size/match)
  • system sampling logs for baseline + pgo (CPU/IO + DB process)
  • the final packaged binaries
  • a short run-info file capturing versions/paths/parameters

Training Workload Policy

PGO is sensitive to what you run during profile generation.

The default policy should:

  • cover the key read paths you care about
  • avoid injecting unnecessary I/O noise when you’re validating a CPU-bound target
  • remain stable across versions and environments

For Percona/MySQL in this repo, the standard profile-gen mode is joint_read and validation is readonly. See docs/pgo_train_modes.md.

DB-Specific Mapping

MySQL / Percona Server (InnoDB + sysbench)

Benchmark intent:

  • point_select / read_only should be mostly CPU-bound after warmup

Guardrails:

  • ensure the benchmark config does not fall back to upstream defaults (example real incident: innodb_buffer_pool_size=128MB caused point_select to become strongly I/O-bound and invalidated PGO comparisons)
  • record:
    • innodb_buffer_pool_size
    • performance_schema
    • innodb_flush_method
  • always apply startup quiesce and stability gating

Config basis in this repository:

  • 8.x runtime config is generated by lib/mysql.sh:mysql_emit_config()
  • historically the benchmark tuning came from the legacy template build-normal/init_conf.sh
  • refactors must explicitly migrate “workload-shape” parameters, otherwise baseline can silently become I/O-bound

Reference checklist:

  • docs/pgo_validation_checklist.md

PostgreSQL (example future target)

Benchmark tool:

  • typically pgbench for baseline/read-only/read-write style OLTP
  • for more realistic app mixes, consider ecosystem tools (e.g. OLTPBench) depending on the target scenario

Equivalent guardrails (high-level):

  • confirm the working set fits in memory caches for CPU-bound validation
  • tune memory/cache/logging/checkpoint settings to avoid the benchmark becoming WAL/checkpoint I/O dominated
  • record an explicit config snapshot (SHOW/SELECT/config file)
  • apply the same warmup/quiesce + stability gating + system sampling

Note:

  • the “right” knobs are DB-version and workload dependent; the abstraction is to prove workload shape and record the knobs, not to hard-code a single magic config.

Other open-source OLTP databases

Adopt the same abstraction:

  • pick the ecosystem-standard benchmark tool
  • define the CPU-bound target window
  • prove workload shape via system sampling
  • record config and runtime identity
  • keep evidence bundle complete

Common Failure Modes (Cross-DB)

  1. baseline accidentally becomes I/O-bound (cache too small / durability too strict / background jobs)
  2. benchmark starts before startup/quiesce completes
  3. profile generated but not actually consumed
  4. workload parsing/phase confusion (wrong log, wrong binary, wrong run)
  5. unstable runs accepted as “results”

The takeaway:

  • first make results trustworthy (shape + identity + stability + evidence)
  • then evaluate whether deltas are good or bad