Benchmark Design, Warmup, Concurrency, Percentiles, Regression Tests, and Capacity Headroom

Learning outcomes

After four tuning experiments, ServiceHub has several “faster” screenshots but no reproducible evidence that a release is safe. One run used a cold cache, another had fewer rows, and a third ran with different concurrency. Performance engineering becomes operational only when a benchmark is a versioned experiment contract: dataset, schema, software, hardware/container limits, warmup, cache state, transaction mix, concurrency, duration, seed, percentiles, errors and pass/fail criteria are all declared before results are interpreted.

1. Write the experiment card before running anything

A microbenchmark isolates one narrow behavior—such as a point lookup or sort. It helps explain mechanism but cannot prove application capacity. A representative workload test combines realistic transaction mix and concurrency, yet it still only represents the declared environment.

2. Create a deterministic local dataset

DROP DATABASE IF EXISTS servicehub18;CREATE DATABASE servicehub18 CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;USE servicehub18;CREATE TABLE tickets (  ticket_id BIGINT PRIMARY KEY AUTO_INCREMENT,  customer_id BIGINT NOT NULL,  status ENUM('open','waiting','closed') NOT NULL,  priority TINYINT NOT NULL,  opened_at DATETIME(6) NOT NULL,  updated_at DATETIME(6) NOT NULL,  summary VARCHAR(240) NOT NULL,  INDEX ix_status_opened(status, opened_at),  INDEX ix_customer_updated(customer_id, updated_at)) ENGINE=InnoDB;INSERT INTO tickets(customer_id,status,priority,opened_at,updated_at,summary)WITH RECURSIVE seq AS (  SELECT 1 AS n  UNION ALL SELECT n+1 FROM seq WHERE n < 1000)SELECT MOD(n,125)+1,       ELT(MOD(n,3)+1,'open','waiting','closed'),       MOD(n,5)+1,       TIMESTAMP('2026-08-01 00:00:00') + INTERVAL n MINUTE,       TIMESTAMP('2026-08-01 00:00:00') + INTERVAL n MINUTE,       CONCAT('ServiceHub ticket ',n)FROM seq;SELECT VERSION() AS server_version, @@version_comment AS build_comment,       @@innodb_buffer_pool_size AS buffer_pool_bytes;SELECT COUNT(*) AS seeded_rows FROM tickets;

CREATE USER IF NOT EXISTS 'bench18'@'127.0.0.1' IDENTIFIED BY 'replace-for-local-lab';GRANT SELECT, UPDATE ON servicehub18.* TO 'bench18'@'127.0.0.1';SHOW GRANTS FOR 'bench18'@'127.0.0.1';SELECT COUNT(*) AS rows,       COUNT(DISTINCT customer_id) AS customers,       MIN(opened_at) AS oldest,       MAX(opened_at) AS newestFROM servicehub18.tickets;

For a real course repository, do not commit a working password. Use an environment variable or local secret mechanism. The deterministic SQL fixture fixes row count and value pattern, making two runs more comparable than an uncontrolled production extract.

3. Free local percentile harness with MariaDB Connector/Python

The following harness is intentionally small. It uses the official free MariaDB Connector/Python, a fixed random seed, a warmup phase excluded from measurement, bounded concurrency, and per-operation latency samples. It is not a load-testing platform; it teaches the data you must preserve.

import os, random, time, statisticsfrom concurrent.futures import ThreadPoolExecutor, as_completedimport mariadbHOST=os.getenv("DB_HOST","127.0.0.1")USER=os.getenv("DB_USER","bench18")PASSWORD=os.environ["DB_PASSWORD"]DB="servicehub18"SEED=20260820def one_request(customer_id):    conn=mariadb.connect(host=HOST,user=USER,password=PASSWORD,database=DB)    cur=conn.cursor()    t0=time.perf_counter_ns()    cur.execute("SELECT ticket_id,status,priority FROM tickets WHERE customer_id=? ORDER BY updated_at DESC LIMIT 20", (customer_id,))    cur.fetchall()    ms=(time.perf_counter_ns()-t0)/1_000_000    cur.close(); conn.close()    return msdef percentile(values, p):    v=sorted(values)    i=max(0, min(len(v)-1, int((p/100)*(len(v)-1))))    return v[i]def run(concurrency, requests=400):    rng=random.Random(SEED)    ids=[rng.randint(1,125) for _ in range(requests)]    # Warmup: fixed sample, not included in measured latencies.    for cid in ids[:40]: one_request(cid)    start=time.perf_counter(); lat=[]; errors=0    with ThreadPoolExecutor(max_workers=concurrency) as ex:        fut=[ex.submit(one_request,cid) for cid in ids]        for f in as_completed(fut):            try: lat.append(f.result())            except Exception: errors += 1    elapsed=time.perf_counter()-start    print({"c":concurrency,"ok":len(lat),"errors":errors,           "rps":len(lat)/elapsed,           "p50_ms":percentile(lat,50),"p95_ms":percentile(lat,95),           "p99_ms":percentile(lat,99),"max_ms":max(lat)})for c in (1,2,4,8,16): run(c)

python -m pip install mariadbexport DB_PASSWORD='your-disposable-lab-password'   # PowerShell: $env:DB_PASSWORD='...'python benchmark.py

Creating a new connection for each request deliberately includes connection cost, which may or may not match your application. Record that fact. Chapter 19 will introduce connector pools and session contracts; if you later add pooling, that is a new benchmark configuration, not a directly comparable continuation unless both versions are measured.

4. Warmup, cache state and percentiles

Warmup allows code paths, connection behavior and database caches to approach the intended steady state. A cold-cache test is also valid when restart/cold-start behavior matters, but it must be labeled and reproduced intentionally. Never discard slow early samples merely because they are inconvenient.

Percentiles answer a different question than averages. p50 describes the median experience; p95/p99 expose tail behavior; maximum can identify rare stalls but is sample-size sensitive. Always report request count and errors alongside percentiles. If errors rise under load, do not calculate impressive throughput only from successful requests and hide rejected work.

5. The wrong approach: compare two runs that changed three variables

An operator doubles the buffer pool, changes concurrency from 16 to 32, and updates MariaDB from 11.8 to 12.3, then credits the throughput difference to the buffer pool. This experiment cannot identify causality. The repair is an A/B sequence where one independent variable changes and all declared conditions remain fixed.

experiment_id: ch18-bufferpool-A-vs-Bserver_version: <record SELECT VERSION()>cpu_quota: <record>memory_limit: <record>storage: <record>dataset_rows: 1000seed: 20260820warmup_requests: 40measurement_requests: 400concurrency: 8durability: innodb_flush_log_at_trx_commit=<record>; sync_binlog=<record>A_setting: innodb_buffer_pool_size=<record>B_setting: innodb_buffer_pool_size=<record>metrics: rps, p50_ms, p95_ms, p99_ms, errors, CPU, storage_latencyacceptance: <define before run>rollback: restore A setting

6. Turn measurements into regression and headroom criteria

A regression gate compares a new build/configuration with a known baseline under the same test contract. Avoid a single-number rule such as “TPS must not fall 5%” when natural run-to-run variance is larger. Run multiple trials, retain raw samples, and define an investigation band based on observed variance plus service requirements.

Capacity headroom is the distance between expected peak offered load and the point where a service objective, error budget, or resource saturation criterion fails. It is not simply “CPU must stay below 70%.” For example, your headroom trigger might be the lowest concurrency/load where p99 breaches the service SLO while CPU or storage queueing shows the relevant saturation mechanism. The exact number comes from your measured system.

SELECT NOW(6) AS captured_at, VERSION() AS version,       @@GLOBAL.innodb_buffer_pool_size,       @@GLOBAL.innodb_log_file_size,       @@GLOBAL.thread_handling,       @@GLOBAL.innodb_flush_log_at_trx_commit;SHOW GLOBAL STATUS WHERE Variable_name IN ( 'Uptime','Threads_connected','Threads_running','Questions', 'Innodb_buffer_pool_reads','Innodb_buffer_pool_read_requests', 'Innodb_buffer_pool_pages_dirty','Innodb_row_lock_waits');

Export the same context for every run. Otherwise, a test result becomes an orphaned number that cannot explain a later regression.

7. Reproducible lab: Chapter 18 performance acceptance report

Prerequisites: free local MariaDB Community Server, Python 3, and the free official MariaDB Connector/Python for the percentile harness. If compiling/installing the connector is impractical on your platform, use another free local load generator but preserve the same experiment fields and percentile/error requirements.

1. Create the deterministic schema and least-privilege benchmark account.
2. Record the full experiment card: server/client/connector versions, resources, durability, schema/data, cache policy and seed.
3. Choose one query/transaction and one independent variable from Lessons 1–4.
4. Run at least three trials for baseline and candidate after identical warmup; save raw results, server status deltas and host metrics.
5. Compare p50/p95/p99, throughput, errors and saturation evidence. Do not accept a candidate that improves mean throughput by violating the service latency/error objective.
6. Write a pass/fail conclusion, uncertainty/limitations, rollback, and next experiment. Then remove the lab account/schema.

DROP USER IF EXISTS 'bench18'@'127.0.0.1';DROP DATABASE IF EXISTS servicehub18;

Production judgment and bridge to Chapter 19

Keep a benchmark only if it answers an operational question and remains reproducible. Treat local results as conditional evidence, not vendor-neutral or production-wide guarantees. Preserve raw samples, configuration snapshots, versions, plans, host metrics and experiment IDs so future upgrades can run the same regression suite. Chapter 19 now moves the workload boundary outward: connectors, TLS, timeouts, application pools and session contracts can change the very concurrency and connection costs measured here.

Authoritative references

Use the target-version tab or release notes when a variable or default differs from the course baseline. These lessons intentionally avoid treating old tuning folklore as current MariaDB behavior.

• MariaDB Connector/Python
• InnoDB Buffer Pool
• Thread Pool in MariaDB
• InnoDB system variables