Chapter 17 · Memory, I/O, Temporary Work, and Server Performance Engineering
Benchmarking Methodology, Warm vs Cold Cache, Concurrency, Percentiles, and Repeatability
Build statistically defensible MySQL benchmarks with explicit workload, cache state, concurrency, percentiles, repetitions, error rates, and reproducible environment artifacts.
Learning outcomes
Performance tuning is an experiment. If a benchmark changes dataset, cache state, concurrency, duration, client behavior, configuration, and hardware at the same time, the number at the end is not evidence about any one change. This lesson turns ServiceHub tuning into a reproducible measurement protocol and explicitly refuses to invent performance numbers.
Define workload mix, dataset, distribution, client concurrency, duration, think time, warm-up, cache state, and hardware before testing.
Measure throughput, p50/p95/p99 latency, maximum latency, and error rate rather than average latency alone.
Separate warm-cache runs from cold-ish disposable-restart runs and state the limits of each.
Repeat runs, estimate normal noise, and compare effect size instead of celebrating one fast sample.
Capture SQL/schema/config/version/client artifacts so another engineer can reproduce the test.
The code in this lesson prints your local measurements. Example tables show field names and decision structure only; they intentionally contain no made-up throughput or latency values.
Define the experiment before launching load
A benchmark should answer a specific question such as “does index X reduce p95 latency for the north-region dashboard at 16 concurrent pooled sessions without increasing write cost beyond our acceptance limit?” That is testable. “Is MySQL fast after tuning?” is not.
| Dimension | Record before every run | Why it matters |
|---|---|---|
| server/client versions | MySQL Server, connector/mysqlslap version | optimizer/defaults/driver behavior can change |
| schema/data | DDL hash, row counts, distribution, seed/reset step | plan/cardinality and cache footprint depend on it |
| workload mix | exact SQL, read/write proportions, parameter distribution | different shapes exercise different resources |
| concurrency | workers/connections plus connection reuse behavior | latency/throughput change nonlinearly with contention |
| duration/warm-up | warm-up excluded from measurement; measured window | short runs exaggerate startup/cache noise |
| cache state | warm; restarted/cold-ish; buffer-pool restore policy | cache can dominate read performance |
| hardware/OS | CPU, RAM limit, storage, VM/container, OS | capacity and scheduler/I/O behavior differ |
| MySQL config | buffer pool, temp limits, redo, durability, sql_mode | a config change can invalidate comparison |
Prepare a least-privilege benchmark account
The benchmark only reads ServiceHub data, so it does not need administrative privileges. Create this disposable account as an administrator, then use it from the client script.
DROP USER IF EXISTS 'servicehub_bench'@'127.0.0.1';CREATE USER 'servicehub_bench'@'127.0.0.1' IDENTIFIED BY 'ServiceHub-Ch17-Lab-Only!';GRANT SELECT ON servicehub_perf_lab.* TO 'servicehub_bench'@'127.0.0.1';SHOW GRANTS FOR 'servicehub_bench'@'127.0.0.1';Use only a disposable local secret. The Python runner prompts with getpass so the password is not embedded in source or printed. In production benchmark automation, use your approved secret manager and TLS policy rather than this classroom account.
Use a small percentile-capable runner
MySQL Connector/Python is free and supports bound parameters. Install it in a virtual environment if needed, then save the script as servicehub_bench.py. Each worker opens one connection and reuses it; warm-up and measurement therefore model persistent sessions rather than measuring a new TCP/authentication handshake for every query.
python -m venv .venv# Windows PowerShell: .\.venv\Scripts\Activate.ps1# Linux/macOS shell: source .venv/bin/activatepython -m pip install --upgrade pippython -m pip install mysql-connector-python#!/usr/bin/env python3import argparse, getpass, statistics, threading, timefrom concurrent.futures import ThreadPoolExecutorimport mysql.connectorQUERY = """SELECT COUNT(*), COALESCE(SUM(labor_minutes),0)FROM work_ordersWHERE region=%s AND status=%s AND scheduled_at >= %s"""REGIONS = ['north','south','east','west','central']STATUSES = ['open','assigned','closed','cancelled']parser = argparse.ArgumentParser()parser.add_argument('--host', default='127.0.0.1')parser.add_argument('--port', type=int, default=3306)parser.add_argument('--user', default='servicehub_bench')parser.add_argument('--database', default='servicehub_perf_lab')parser.add_argument('--workers', type=int, default=4)parser.add_argument('--warmup', type=float, default=10.0)parser.add_argument('--seconds', type=float, default=30.0)args = parser.parse_args()password = getpass.getpass('Disposable lab password: ')latencies = []errors = []lock = threading.Lock()stop_at = Nonemeasure = Falsedef worker(worker_id): global measure cnx = mysql.connector.connect( host=args.host, port=args.port, user=args.user, password=password, database=args.database, autocommit=True ) cur = cnx.cursor() i = 0 while time.perf_counter() < stop_at: region = REGIONS[(worker_id + i) % len(REGIONS)] status = STATUSES[(worker_id * 3 + i) % len(STATUSES)] started = time.perf_counter() try: cur.execute(QUERY, (region, status, '2026-03-01')) cur.fetchone() elapsed_ms = (time.perf_counter() - started) * 1000.0 if measure: with lock: latencies.append(elapsed_ms) except Exception as exc: if measure: with lock: errors.append(type(exc).__name__) i += 1 cur.close(); cnx.close()def percentile(values, p): xs = sorted(values) if not xs: return float('nan') k = (len(xs)-1) * p lo = int(k); hi = min(lo+1, len(xs)-1) return xs[lo] + (xs[hi]-xs[lo]) * (k-lo)# Keep the same worker connections across warm-up and measurement.stop_at = time.perf_counter() + args.warmup + args.secondswith ThreadPoolExecutor(max_workers=args.workers) as ex: futures = [ex.submit(worker, i) for i in range(args.workers)] time.sleep(args.warmup) measure = True measured_start = time.perf_counter() for f in futures: f.result()measured_seconds = max(time.perf_counter() - measured_start, 1e-9)print(f'workers={args.workers} measured_seconds={measured_seconds:.3f}')total_attempts = len(latencies) + len(errors)error_rate = (len(errors) / total_attempts) if total_attempts else 0.0print(f'operations={len(latencies)} errors={len(errors)} error_rate={error_rate:.6f}')print(f'throughput_ops_s={len(latencies)/measured_seconds:.2f}')print(f'p50_ms={percentile(latencies,0.50):.3f}')print(f'p95_ms={percentile(latencies,0.95):.3f}')print(f'p99_ms={percentile(latencies,0.99):.3f}')print(f'max_ms={max(latencies) if latencies else float("nan"):.3f}')The runner uses deterministic parameter cycling so repetitions exercise the same distribution. It reports operation count, errors, throughput, p50, p95, p99, and maximum observed latency. It does not claim statistical significance; you supply repetitions and interpret run-to-run variation.
Warm-cache versus cold-ish runs
A warm test deliberately runs a warm-up window first and excludes it from the measured interval. That matches steady-state services where the same working set is frequently reused. A true cold-storage test is harder because restarting mysqld does not necessarily clear the operating-system or storage-controller cache.
For a classroom cold-ish comparison, use a disposable container/VM, stop it cleanly, start a fresh disposable instance with the same dataset/config, and record whether buffer-pool state loading is enabled. Do not run privileged host cache-dropping commands on your workstation or production server just to manufacture a cold cache.
SELECT @@GLOBAL.innodb_buffer_pool_dump_at_shutdown AS dump_at_shutdown, @@GLOBAL.innodb_buffer_pool_load_at_startup AS load_at_startup;SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_load_status';Run a concurrency matrix and repeat it
python .\servicehub_bench.py --workers 1 --warmup 10 --seconds 30python .\servicehub_bench.py --workers 4 --warmup 10 --seconds 30python .\servicehub_bench.py --workers 16 --warmup 10 --seconds 30# Repeat each cell several times; save output with timestamp/config notes.python ./servicehub_bench.py --workers 1 --warmup 10 --seconds 30python ./servicehub_bench.py --workers 4 --warmup 10 --seconds 30python ./servicehub_bench.py --workers 16 --warmup 10 --seconds 30# Repeat each cell several times; save output with timestamp/config notes.Concurrency is not “higher is better.” Throughput can rise while p99 degrades; at saturation, adding workers may increase queueing and reduce useful throughput. Plot both throughput and latency distribution against concurrency, then correlate CPU, disk latency/queue, connections, temp work, redo/checkpoint pressure, and top statement digests from Chapter 16.
Collect server evidence during the same window
SELECT NOW(6) AS sample_time;SHOW GLOBAL STATUS WHERE Variable_name IN ( 'Threads_connected','Threads_running','Questions', 'Innodb_buffer_pool_reads','Innodb_buffer_pool_read_requests', 'Created_tmp_tables','Created_tmp_disk_tables', 'Innodb_os_log_written','Innodb_log_waits', 'Innodb_redo_log_current_lsn','Innodb_redo_log_checkpoint_lsn');SELECT DIGEST_TEXT, COUNT_STAR, ROUND(SUM_TIMER_WAIT/1000000000000,6) AS total_seconds, SUM_ROWS_EXAMINED, SUM_ROWS_SENTFROM performance_schema.events_statements_summary_by_digestWHERE SCHEMA_NAME='servicehub_perf_lab'ORDER BY SUM_TIMER_WAIT DESCLIMIT 10;Use an external timestamped collector for serious testing rather than manually querying once. The important rule is temporal alignment: the server/OS evidence must cover the same measured window as the client benchmark.
A comparison table without fake numbers
| Run ID | change | workers | cache state | throughput ops/s | p50 ms | p95 ms | p99 ms | error rate | notes |
|---|---|---|---|---|---|---|---|---|---|
| A1/A2/A3 | baseline | record | warm | measure | measure | measure | measure | measure | same data/config |
| B1/B2/B3 | candidate change | same as A | same as A | measure | measure | measure | measure | measure | only one intended variable changed |
Compare distributions and repeat-to-repeat spread. If candidate p95 improves by less than ordinary run noise while CPU or memory cost rises materially, the change may not be worth deploying. If the result is large and repeatable, validate under a second representative workload and check correctness/durability before promotion.
Wrong benchmark patterns and repairs
| Wrong approach | Why it misleads | Repair |
|---|---|---|
| one run before, one run after | noise/cache/background jobs can dominate | multiple alternated repetitions with same conditions |
| average latency only | tail pain and bimodal behavior disappear in average | p50/p95/p99 + max + error rate |
| change buffer pool + index + redo + hardware at once | no causal attribution | one intended variable per experiment or factorial design |
| ignore warm-up/cache state | first-run I/O compared to warm cache | declare and control warm/cold-ish procedure |
| benchmark on 100 rows | optimizer/cache behavior unlike target scale | representative size/distribution and row-count artifact |
| drop durability for speed | changes correctness/RPO contract | hold durability policy constant unless that is the explicit experiment |
Optional coarse load generation with mysqlslap
mysqlslap is bundled as a MySQL diagnostic load-emulation client and can create concurrent clients quickly. It is useful as a secondary coarse tool, but its aggregate timing output does not replace the percentile-aware runner above or application-representative transaction logic.
mysqlslap --host=127.0.0.1 --user=servicehub_bench --password \ --create-schema=servicehub_perf_lab \ --query="SELECT COUNT(*) FROM work_orders WHERE region='north' AND status='open'" \ --concurrency=16 --iterations=5Optional cleanup after all Chapter 17 experiments
Keep the lab if you want to compare future tuning changes. If you are finished, remove only the disposable objects created by this chapter. Never substitute a production schema or account in these commands.
DROP USER IF EXISTS 'servicehub_bench'@'127.0.0.1';DROP DATABASE IF EXISTS servicehub_perf_lab;Production judgment and bridge to Chapter 18
A defensible performance change has a hypothesis, one intended causal change, reproducible input artifacts, a warm-up/cache procedure, a concurrency matrix, repeated measurements, percentile/error outputs, correlated server/OS evidence, and rollback/acceptance criteria. Archive the test manifest with the code/config change so future upgrades can rerun it.
Chapter 18 applies this same discipline to large-table lifecycle work: partitioning, online DDL algorithms, retention/purge operations, and multi-terabyte growth planning—where unmeasured maintenance work can be more dangerous than ordinary queries.
Knowledge check
- Why is p99 latency useful when throughput is unchanged?
- Why must a warm-up period be excluded from measured steady-state results?
- Why is a server restart not necessarily a truly cold-cache test?
- What makes a before/after result causally interpretable?
- Why should performance artifacts be retained with code/config changes?
Reveal answers
- It exposes tail latency experienced by the slowest fraction of requests, which averages or throughput can hide.
- It can include connection establishment, cache population, JIT/runtime/startup effects, and other transient work not representative of steady state.
- The OS/filesystem and storage-controller caches can retain data even when the MySQL buffer pool is empty.
- The same data/workload/environment with one intended change, repeated runs, controlled cache/concurrency, and correlated evidence.
- They let another engineer reproduce the result, detect regressions after upgrades, and verify that the original acceptance conditions still hold.