Define Workload, SLOs, Data Model, Engine Choices, Capacity, and Architecture Record

Learning outcomes

ServiceHub is moving from “one MariaDB server that seems fast enough” to a database service that must survive growth, operator error and host failure. The first capstone mistake would be to start with a technology answer—“three Galera nodes,” “a 64 GB buffer pool,” or “daily backups”—before defining what the workload and business actually require. This lesson starts with contracts that can be measured.

1. Freeze the workload contract before choosing topology

A Service-Level Objective (SLO) is an internal target you design and operate toward. It becomes useful only when paired with a measurement definition: which requests count, from where latency is measured, what maintenance is excluded (if anything), and which failure classes are included.

2. Turn user journeys into data and transaction boundaries

The capstone focuses on five user journeys: create a ticket, append a comment, move a ticket through a queue, load one customer’s recent tickets, and publish an integration event. These journeys suggest entities and invariants before they suggest indexes.

InnoDB is the default because the capstone requires transactions, foreign keys, crash recovery and row-level concurrency. An alternative engine must earn its place with an explicit invariant it can satisfy and a backup/replication story; “it benchmarked faster once” is not enough.

3. Choose one HA model and name what it does not solve

The capstone baseline is one writable primary plus one asynchronous GTID replica. That is intentionally conservative: the application has one write authority, the replica provides a second copy and read/offload option, and promotion remains an explicit operational action. It does not make commits synchronous across hosts, does not automatically fence the old primary, and does not replace backups.

4. Make the architecture observable before building it

SELECT VERSION() AS server_version, @@version_comment AS build_comment;SHOW ENGINES;SHOW VARIABLES WHERE Variable_name IN ( 'datadir','innodb_buffer_pool_size','innodb_log_file_size','log_bin', 'binlog_format','gtid_strict_mode','read_only','require_secure_transport', 'max_connections','thread_handling');SHOW GLOBAL STATUS WHERE Variable_name IN ( 'Uptime','Threads_connected','Threads_running','Questions','Bytes_received','Bytes_sent');

This is not a sizing exercise yet. It is an effective-state inventory. Configuration files, container environment variables and documentation can all disagree with the running server; production decisions use effective values plus their persistence source.

5. Capacity planning: budget the whole recovery envelope

Raw table bytes are only one consumer. Large operations need temporary free space; rebuilds may need a second copy; physical backups need their own capacity; binlog retention must cover recovery/replica outages; and growth uncertainty needs headroom.

SELECT table_schema,       ROUND(SUM(data_length)/1024/1024,1) AS data_mb,       ROUND(SUM(index_length)/1024/1024,1) AS index_mb,       ROUND(SUM(data_free)/1024/1024,1) AS data_free_mbFROM information_schema.TABLESWHERE table_schema NOT IN ('mysql','information_schema','performance_schema','sys')GROUP BY table_schemaORDER BY data_mb + index_mb DESC;SHOW BINARY LOGS;SHOW GLOBAL STATUS LIKE 'Innodb_os_log_written';

Demand inputs:  net_db_growth_per_month = 60 GiB   # scenario assumption  online_retention_months = 24  uncertainty_headroom = 0.40Budget categories:  current data + indexes  24-month growth  rebuild/online-DDL workspace  InnoDB redo + temporary files  binlog retention for PITR + replica outage  one local restore workspace  backup repository + retention generationsRule:  Do not accept the host if any credible rebuild/restore path consumes the safety margin.  Recalculate from observed monthly growth and measured operation sizes.

Headroom is a risk policy, not a magic percentage. The 40% worksheet value is a capstone assumption to be challenged. A database with unpredictable temp spills, large online DDL and long backup retention may need more; a carefully bounded appliance may need less.

6. Wrong approach: architecture by defaults and product names

Suppose the team immediately creates a three-node Galera cluster, assigns huge global buffers, enables every log and advertises “zero data loss.” The design has not stated write-conflict rate, network latency, quorum placement, log retention, restore time, or memory concurrency. It can fail every business objective while looking sophisticated.

The repair is to make each technology traceable to one requirement and one test. If no test exists, record the decision as provisional rather than “production ready.”

7. Reproducible architecture-record lab

DROP DATABASE IF EXISTS capstone22_meta;CREATE DATABASE capstone22_meta CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;USE capstone22_meta;CREATE TABLE architecture_requirement (  requirement_key VARCHAR(64) PRIMARY KEY,  requirement_value VARCHAR(255) NOT NULL,  evidence_method VARCHAR(500) NOT NULL,  status ENUM('ASSUMPTION','MEASURED','PASSED','FAILED') NOT NULL DEFAULT 'ASSUMPTION') ENGINE=InnoDB;INSERT INTO architecture_requirement VALUES('latency_p95','<=120ms DB time for critical transactions','connector percentile under representative load','ASSUMPTION'),('latency_p99','<=250ms DB time for critical transactions','connector percentile under representative load','ASSUMPTION'),('rpo','<=5m for destructive primary loss exercise','replica/binlog/restore drill','ASSUMPTION'),('rto','<=30m for restore/failover exercise','timed game-day','ASSUMPTION'),('retention','24 months online','partition/lifecycle + capacity evidence','ASSUMPTION');SELECT * FROM architecture_requirement ORDER BY requirement_key;

Title: ServiceHub MariaDB production baselineStatus: ProposedContext: workload/SLO/growth/compliance assumptions from capstoneDecision:  InnoDB transactional schema  one writable primary + one asynchronous GTID replica  physical backup + retained binary logs for PITR  application pools connect to one write endpoint; failover requires pool refresh  production network connections require verified TLSRejected for baseline:  single server only — RTO risk  multi-primary Galera — additional conflict/quorum/flow-control complexity not yet justifiedAcceptance criteria:  named p95/p99 transactions pass representative benchmark  restore and failover drills satisfy measured RPO/RTO  grant/TLS/config/index checks pass  capacity forecast retains approved headroom during rebuild/restoreExit criteria / revisit triggers:  write availability or geography requires quorum-based cluster  replica loss window exceeds business RPO  measured growth or restore time exceeds current capacity modelOwner / review cadence: database-platform team / quarterly and after material workload or version change

Production judgment and bridge to Lesson 2

A production architecture is a set of measurable contracts, not a diagram. The topology is acceptable only while the workload, recovery tests and operating team can defend it. Lesson 2 converts this proposed architecture into schema, privileges, connector contracts and migrations that can be checked automatically.

Authoritative references

• MariaDB Server Documentation
• MariaDB Documentation — Storage Engines
• MariaDB Documentation — Replication Overview
• MariaDB Galera Cluster Documentation
• MariaDB Documentation — Backup and Restore Overview