Benchmarking Methodology, Warm vs Cold Cache, Concurrency, Percentiles, and Repeatability

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 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.

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

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

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=5

Optional 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.

Authoritative references

• MySQL 8.4 Reference Manual
• MySQL Community Server 8.4 downloads
• mysqlslap — A Load Emulation Client
• MySQL Connector/Python Developer Guide
• MySQLCursor.execute() bound-parameter API
• Performance Schema Statement Summary Tables