Chapter 22 · Production Capstone: Design, Secure, Scale, Tune, and Recover MySQL

Define SLOs, Workload, Data Model, Capacity Forecast, and Architecture Decision Record

Turn ServiceHub business promises into measurable SLOs, a workload contract, normalized data model, capacity forecast, and an architecture decision record for MySQL/InnoDB and high availability.

Advanced capstone180–300 minServiceHub production capstoneMySQL Community Server 8.4.10 LTSInnoDBsingle local server mandatoryMySQL Shell 8.4.10 + Router 8.4.10 optional HA extension3-member single-primary InnoDB Cluster production targetLast reviewed: August 2026

Learning outcomes

The final ServiceHub capstone begins before any schema or configuration command. A production database exists to satisfy a service contract: technicians must open and close work orders, dispatchers must find active jobs, integrations must not duplicate requests, and operators must recover after failures. If those promises are not quantified, “high availability,” “fast,” and “safe” are slogans rather than engineering requirements. This lesson turns business expectations into service-level objectives (SLOs), a workload model, a capacity forecast, and an architecture decision record (ADR).

Define availability, latency, durability, recovery point objective (RPO), and recovery time objective (RTO) as measurable ServiceHub targets rather than generic best practices.

Describe the read/write mix, transaction boundaries, concurrency, growth, and data-lifecycle assumptions that drive the database design.

Turn business entities into a normalized InnoDB model with explicit keys and consistency requirements.

Build a capacity model that includes data, secondary indexes, binary logs, backups, replicas, and maintenance headroom.

Write an ADR that explains why MySQL/InnoDB plus a three-member single-primary InnoDB Cluster and Router fit the stated workload—and what evidence would invalidate that choice.

Capstone baseline

Mandatory labs use MySQL Community Server 8.4.10 LTS on one disposable local server. The defended production topology is a three-member single-primary InnoDB Cluster behind MySQL Router. MySQL Shell 8.4.10 and Router 8.4.10 are free and are used in the optional multi-instance HA extension. Numbers below are scenario requirements, not universal tuning recommendations.

Start with promises that can fail

An SLO is a measurable target for a service characteristic. It is not the same as a configuration value. For example, setting a connection timeout to three seconds does not create a three-second availability SLO. The SLO is measured at the system boundary and includes application, network, Router, MySQL, failover, and recovery behavior.

Dimension	ServiceHub capstone target	How it will be tested
availability	99.95% monthly for create/read/close work-order API	synthetic success/error ratio plus failure drills
read latency	p95 ≤ 150 ms; p99 ≤ 300 ms at API boundary for primary dispatch queries	repeatable load test with fixed dataset/workload
write latency	p95 ≤ 250 ms for short work-order transactions	same harness; separate write histogram
durability	acknowledged work-order writes survive a tested single-node failure in chosen HA topology	cluster failure drill plus business invariant checks
RPO	≤ 5 minutes for operator-caused logical damage	backup + binary-log continuity/PITR drill
RTO	≤ 30 minutes for logical restore; automatic HA failover target measured separately	timed restore and HA drills
freshness	read-after-write paths use primary; replica/RO reads have an explicit staleness contract	routing/session tests
retention	operational events kept online 24 months; older evidence archived under policy	lifecycle query and archive verification

These values are intentionally concrete so a later design defense can say met, not met, or not yet measured. If a learner's machine cannot sustain the scenario load, the local benchmark scales the dataset and concurrency down but preserves the method.

Model the workload before the tables

The capstone workload has four dominant paths. Dispatchers list open work by site and schedule. Technicians fetch an asset and its current work order, append events, and close jobs. Integrations create jobs with idempotency keys. Operators run bounded reports but heavy analytics are kept outside the primary transaction path.

text · workload contract — inputs to schema, indexes, HA, and capacity

Peak application connections: 120 pooled sessions across app instancesPeak write transactions:   measured locally; production target documented separatelyPeak read:write ratio:      approximately 6:1 during dispatch hoursTypical transaction:       1 work_order row + 1..4 event rowsRead-after-write:           required for technician confirmationLarge reporting scans:      not allowed on primary hot pathGrowth assumption:          250k work orders/month, 1.5M events/monthRetention:                  24 months hot/online, older data archivedLogical recovery objective: RPO <= 5 min, RTO <= 30 minHA objective:               tolerate one member loss without losing quorum

The point is not the exact rates. The point is that each design choice can point back to a workload statement. A composite index exists because a query shape needs it. A replica or Cluster exists because an availability/read contract needs it. An archive policy exists because growth and retention need it.

Build the normalized core model first

ServiceHub uses relational identity and constraints for stable business facts. Flexible metadata can be added later, but the core transaction model remains explicit: sites own assets; work orders refer to sites and assets; work-order events form an append-oriented history; integration requests carry a unique idempotency key.

sql · create the disposable capstone database and first normalized tables

DROP DATABASE IF EXISTS servicehub_capstone;CREATE DATABASE servicehub_capstone  CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;USE servicehub_capstone;CREATE TABLE sites (  site_id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,  site_code VARCHAR(32) NOT NULL UNIQUE,  site_name VARCHAR(120) NOT NULL,  active BOOLEAN NOT NULL DEFAULT TRUE) ENGINE=InnoDB;CREATE TABLE assets (  asset_id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,  site_id BIGINT UNSIGNED NOT NULL,  asset_tag VARCHAR(64) NOT NULL,  asset_type VARCHAR(64) NOT NULL,  metadata JSON NULL,  UNIQUE KEY uq_asset_site_tag (site_id,asset_tag),  CONSTRAINT fk_assets_site FOREIGN KEY (site_id) REFERENCES sites(site_id)) ENGINE=InnoDB;CREATE TABLE work_orders (  work_order_id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,  site_id BIGINT UNSIGNED NOT NULL,  asset_id BIGINT UNSIGNED NOT NULL,  status ENUM('OPEN','IN_PROGRESS','CLOSED','CANCELLED') NOT NULL,  priority TINYINT UNSIGNED NOT NULL,  summary VARCHAR(240) NOT NULL,  scheduled_at DATETIME(6) NOT NULL,  created_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),  closed_at DATETIME(6) NULL,  CONSTRAINT fk_wo_site FOREIGN KEY (site_id) REFERENCES sites(site_id),  CONSTRAINT fk_wo_asset FOREIGN KEY (asset_id) REFERENCES assets(asset_id),  CHECK (priority BETWEEN 1 AND 5)) ENGINE=InnoDB;CREATE TABLE work_order_events (  event_id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,  work_order_id BIGINT UNSIGNED NOT NULL,  event_type VARCHAR(40) NOT NULL,  actor VARCHAR(80) NOT NULL,  payload JSON NULL,  occurred_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),  CONSTRAINT fk_event_work_order FOREIGN KEY (work_order_id)    REFERENCES work_orders(work_order_id)) ENGINE=InnoDB;CREATE TABLE integration_requests (  idempotency_key BINARY(16) PRIMARY KEY,  work_order_id BIGINT UNSIGNED NULL,  state ENUM('STARTED','COMMITTED','FAILED') NOT NULL,  created_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),  CONSTRAINT fk_request_work_order FOREIGN KEY (work_order_id)    REFERENCES work_orders(work_order_id)) ENGINE=InnoDB;SHOW TABLES;SELECT TABLE_NAME,ENGINE,TABLE_COLLATIONFROM information_schema.TABLESWHERE TABLE_SCHEMA='servicehub_capstone'ORDER BY TABLE_NAME;

MySQL will use InnoDB for these tables, so primary keys define clustered row organization and secondary indexes later carry the primary-key value as their row locator. That detail matters to the capacity forecast: wide primary keys make every secondary index wider.

Capacity is more than table data

A simple forecast begins with observed bytes per row/index after a representative seed, then adds growth and operational copies. Do not estimate a multi-terabyte future system from one tiny test row; use the small lab to learn the method, then recalibrate using production-like samples.

sql · capture the table/index baseline that future forecasts will recalibrate

SELECT TABLE_NAME,TABLE_ROWS,       DATA_LENGTH,INDEX_LENGTH,DATA_FREE,       AVG_ROW_LENGTHFROM information_schema.TABLESWHERE TABLE_SCHEMA='servicehub_capstone'ORDER BY TABLE_NAME;SELECT @@innodb_page_size AS innodb_page_size,       @@innodb_buffer_pool_size AS buffer_pool_bytes,       @@max_connections AS max_connections;SHOW VARIABLES LIKE 'binlog_expire_logs_seconds';SHOW VARIABLES LIKE 'innodb_redo_log_capacity';

text · capacity worksheet — use measured inputs, not fixed ratios

hot_table_bytes      = measured data + measured indexes at target retentionmonthly_growth_bytes  = measured bytes per new business unit * monthly volumebinary_log_headroom   = measured binlog bytes/day * required retention + safety marginbackup_footprint      = full backups + incrementals/logs + restore-test copyHA_footprint          = one full dataset per cluster member + local logs/temp/headroomDDL_headroom          = operation-specific copy/temp space where requiredarchive_staging       = export/checksum/restore-test workspaceOS_and_MySQL_headroom = memory + temp + crash/recovery + monitoring + patch marginrunway_months = usable_free_storage / measured_monthly_growth_bytes

A production plan records the assumptions, source of each measurement, and uncertainty. “Disk is 40% full” is not a capacity plan if growth, backups, binary logs, restore staging, and online DDL may compete for the same device.

Architecture decision record: choose the simplest topology that meets the contract

text · ADR-001 — ServiceHub transactional MySQL architecture

Decision: MySQL 8.4 LTS / InnoDB, three-member single-primary InnoDB Cluster,          MySQL Router adjacent to application tier, tested backup + binary-log PITR.Why:- relational constraints and short ACID transactions dominate the workload;- existing SQL/indexing/query requirements fit InnoDB;- three members preserve majority after one member loss;- Router removes direct primary-address coupling from applications;- binary logs and tested backups address logical recovery separately from HA.Not chosen now:- NDB: no proven distributed-write requirement that justifies its storage/ops model;- multi-primary Group Replication: conflict/operational complexity not justified;- external search/OLAP: add only when native/replica boundaries fail measured requirements.Revisit triggers:- sustained write bottleneck after query/schema tuning and vertical capacity review;- p99 SLO miss caused by topology rather than application/query design;- analytics/search workload harms OLTP or requires independent semantics;- recovery drills miss RPO/RTO despite tested process and adequate infrastructure.

High availability and backup solve different failures. A Cluster can survive a server outage yet faithfully replicate an accidental DELETE. A backup/PITR chain can recover the deleted data but does not automatically route live traffic during a node failure. The final design needs both.

Wrong approach: choose three replicas and call it an SLO

A common capstone failure is to copy an architecture diagram and infer reliability from component count. Three database nodes on one host, one Router process, one power supply, and no tested restore do not satisfy a production availability objective. The repair is to map each SLO to a failure mode, evidence source, and owner.

SLO/failure	Design control	Evidence before production
single DB member loss	3-member Cluster, separate failure domains	quorum/member-loss drill
application DB endpoint loss	Router deployment/restart policy	client reconnect test
operator deletes rows	backup + binlog PITR	restore-to-disposable-target drill
slow query overload	index/query baseline + alerts	EXPLAIN ANALYZE and load test
credential abuse	least privilege + TLS + rotation	positive/negative authorization tests
capacity exhaustion	growth model + alerts + archive policy	forecast recalculated from measured growth

Lab acceptance and cleanup boundary

sql · prove the architecture inputs exist before moving to implementation

USE servicehub_capstone;SHOW CREATE TABLE work_orders;SELECT COUNT(*) AS capstone_tablesFROM information_schema.TABLESWHERE TABLE_SCHEMA='servicehub_capstone';SELECT @@version AS server_version,       @@version_comment AS edition,       @@default_storage_engine AS default_engine,       @@transaction_isolation AS isolation_level,       @@sql_mode AS session_sql_mode;SELECT NAME,ENABLEDFROM performance_schema.setup_consumersWHERE NAME IN ('events_statements_current','events_transactions_current');

Keep the database after this lesson: every later capstone lesson evolves and tests the same schema. The final cleanup occurs only after the design-defense evidence has been exported.

Knowledge check

What is the difference between an SLO and a configuration value?
Why does the primary-key width affect secondary-index capacity?
Why are HA and PITR both required?
Why choose single-primary instead of multi-primary here?
What should trigger an architecture revisit?

Reveal answers

An SLO is a measured service target; a configuration value is only one implementation input and does not prove the target.
InnoDB secondary index records include the primary-key value as the row locator.
HA addresses service/node failure; PITR addresses logical/history recovery such as accidental changes. One does not replace the other.
The workload has no proven need for concurrent multi-primary writes, so avoiding conflict and routing complexity is the simpler defensible choice.
A measured requirement failure or workload change—not feature availability or fashion.

Authoritative references

Keep knowledge open

Help the academy stay free and grow.

If these tutorials save you time, a small donation supports new lessons, technical review, diagrams, examples, and long-term maintenance.

ETHEthereum / ERC-20 only

0x716c4Ab160C4B66F31a28AE2448BfF68fc3a2ef0

Send only assets compatible with the Ethereum/ERC-20 network. Do not send TRC-20/TRON assets.