Load Test, Capture Plans, Tune Queries/Server, and Establish Performance Baselines

server: MySQL Community Server 8.4.10 LTSschema migration version: capture from schema_migrationsclient: Python + MySQL Connector/Python version recorded at runtimedataset: ServiceHub seed script + row counts + distribution queryworkload mix: 70% dispatch reads, 20% event writes, 10% detail/history readsconcurrency levels: 1, 8, 32 (reduce on small machines; keep them recorded)warm-up: 30 seconds, excluded from measured statisticsmeasured duration: 120 seconds per runruns: >= 3 per configuration/cache conditioncache condition: warm; cold-cache experiments labeled separatelyreported: throughput, p50, p95, p99, max, error rateserver evidence: statement digests, connections, temp work, buffer reads, redo, I/O waitschange policy: one hypothesis-driven change at a time

USE servicehub_capstone;EXPLAIN FORMAT=TREESELECT work_order_id,site_id,asset_id,priority,scheduled_atFROM work_ordersWHERE status='OPEN' AND priority>=4ORDER BY scheduled_at,work_order_idLIMIT 100;EXPLAIN ANALYZESELECT work_order_id,site_id,asset_id,priority,scheduled_atFROM work_ordersWHERE status='OPEN' AND priority>=4ORDER BY scheduled_at,work_order_idLIMIT 100;SELECT INDEX_NAME,CARDINALITYFROM information_schema.STATISTICSWHERE TABLE_SCHEMA='servicehub_capstone'  AND TABLE_NAME='work_orders'ORDER BY INDEX_NAME,SEQ_IN_INDEX;

import getpass, json, random, statistics, timefrom collections import defaultdictfrom concurrent.futures import ThreadPoolExecutorimport mysql.connectorHOST = "127.0.0.1"PORT = 3306USER = "sh_cap_app"PASSWORD = getpass.getpass("Disposable capstone password: ")DURATION = 30.0           # Increase for real baseline runs.WARMUP = 5.0              # Keep warm-up outside measurements.CONCURRENCY = 8           # Record every change to this value.CFG = dict(host=HOST, port=PORT, user=USER, password=PASSWORD,           database="servicehub_capstone", ssl_disabled=False,           autocommit=True, connection_timeout=5)QUERIES = {    "dispatch": ("""SELECT work_order_id,site_id,asset_id,priority,scheduled_at                     FROM work_orders                     WHERE status=%s AND priority>=%s                     ORDER BY scheduled_at,work_order_id LIMIT 100""", ("OPEN", 4)),    "history":  ("""SELECT event_id,event_type,actor,occurred_at                     FROM work_order_events                     WHERE work_order_id=%s                     ORDER BY occurred_at,event_id LIMIT 50""", None),}def percentile(values, q):    if not values: return None    s = sorted(values)    i = min(len(s)-1, max(0, round((len(s)-1)*q)))    return s[i]def worker(seed, stop_at, warmup_until):    rng = random.Random(seed)    lat = defaultdict(list); errors = 0; ops = 0    cnx = mysql.connector.connect(**CFG)    cur = cnx.cursor()    try:        while time.perf_counter() < stop_at:            kind = "dispatch" if rng.random() < 0.8 else "history"            sql, params = QUERIES[kind]            if kind == "history": params = (rng.randint(1, 1000),)            t0 = time.perf_counter()            try:                cur.execute(sql, params); cur.fetchall()            except mysql.connector.Error:                errors += 1                continue            ms = (time.perf_counter()-t0)*1000            if time.perf_counter() >= warmup_until:                lat[kind].append(ms); ops += 1    finally:        cur.close(); cnx.close()    return lat, errors, opsstart = time.perf_counter(); warmup_until = start + WARMUP; stop = warmup_until + DURATIONwith ThreadPoolExecutor(max_workers=CONCURRENCY) as ex:    results = [f.result() for f in [ex.submit(worker, i, stop, warmup_until)                                   for i in range(CONCURRENCY)]]all_lat = defaultdict(list); errors = total_ops = 0for lat, err, ops in results:    errors += err; total_ops += ops    for k, vals in lat.items(): all_lat[k].extend(vals)report = {"concurrency": CONCURRENCY, "duration_s": DURATION,          "throughput_ops_s": total_ops/DURATION,          "errors": errors,          "error_rate": errors/max(1, total_ops+errors), "latency_ms": {}}for k, vals in all_lat.items():    report["latency_ms"][k] = {"count": len(vals), "p50": percentile(vals,.50),                               "p95": percentile(vals,.95), "p99": percentile(vals,.99),                               "max": max(vals) if vals else None}print(json.dumps(report, indent=2))

SELECT DIGEST_TEXT,COUNT_STAR,SUM_ROWS_EXAMINED,SUM_ROWS_SENT,       ROUND(SUM_TIMER_WAIT/1000000000000,3) AS total_secondsFROM performance_schema.events_statements_summary_by_digestWHERE SCHEMA_NAME='servicehub_capstone'ORDER BY SUM_TIMER_WAIT DESCLIMIT 10;SHOW GLOBAL STATUS WHERE Variable_name IN('Threads_connected','Threads_running','Connections','Aborted_connects', 'Created_tmp_tables','Created_tmp_disk_tables', 'Innodb_buffer_pool_reads','Innodb_buffer_pool_read_requests', 'Innodb_os_log_written','Innodb_log_waits');SELECT EVENT_NAME,COUNT_STAR,       ROUND(SUM_TIMER_WAIT/1000000000000,3) AS total_secondsFROM performance_schema.file_summary_by_event_nameORDER BY SUM_TIMER_WAIT DESCLIMIT 10;

CREATE INDEX idx_wo_status_priority_scheduleON work_orders(status,priority,scheduled_at,work_order_id);ANALYZE TABLE work_orders;EXPLAIN FORMAT=TREESELECT work_order_id,site_id,asset_id,priority,scheduled_atFROM work_ordersWHERE status='OPEN' AND priority>=4ORDER BY scheduled_at,work_order_idLIMIT 100;EXPLAIN ANALYZESELECT work_order_id,site_id,asset_id,priority,scheduled_atFROM work_ordersWHERE status='OPEN' AND priority>=4ORDER BY scheduled_at,work_order_idLIMIT 100;

Run ID / timestamp: ______________________________Server + client versions: _________________________Schema migration/checksum: _______________________Dataset row counts/distribution: __________________Concurrency / duration / warm-up: _________________Cache state: _____________________________________Throughput: ______________________________________Read p50 / p95 / p99: ____________________________Write p50 / p95 / p99: ___________________________Error rate: ______________________________________Top statement digests: ___________________________CPU / storage latency / queueing: _________________Temp-table / redo / buffer deltas: _______________Change tested: ___________________________________Before/after effect: ______________________________Residual bottleneck / uncertainty: _______________Decision: keep / revert / retest __________________