Multi-Terabyte Capacity Planning, Maintenance Windows, and Growth Forecasting

Learning outcomes

A multi-terabyte MariaDB system fails capacity planning when “database size” means only DATA_LENGTH. Large-table operations need simultaneous space for indexes, redo/undo, binary logs, temporary files, online-change buffers, backups, replicas or Galera state transfer, and restore staging. This final lesson turns Chapters 13–16 into an evidence-based capacity and maintenance model.

2. Build a disposable capacity fixture and record the first snapshot

The capacity queries in this lesson must be reproducible without depending on earlier chapters. Create a small ServiceHub dataset, measure it, then repeat the snapshot after adding rows or an index. The absolute MiB values are local observations; the workflow is the lesson.

DROP DATABASE IF EXISTS servicehub16_l5;CREATE DATABASE servicehub16_l5;USE servicehub16_l5;CREATE TABLE capacity_events (  event_id BIGINT PRIMARY KEY AUTO_INCREMENT,  event_day DATE NOT NULL,  customer_id BIGINT NOT NULL,  payload VARCHAR(500) NOT NULL,  KEY ix_day_customer(event_day,customer_id)) ENGINE=InnoDB;INSERT INTO capacity_events(event_day,customer_id,payload)SELECT CURRENT_DATE - INTERVAL (seq % 365) DAY,       seq % 20000,       RPAD('capacity',300,'x')FROM seq_1_to_100000;ANALYZE TABLE capacity_events;SELECT TABLE_ROWS,DATA_LENGTH,INDEX_LENGTH,DATA_FREEFROM INFORMATION_SCHEMA.TABLESWHERE TABLE_SCHEMA='servicehub16_l5'  AND TABLE_NAME='capacity_events';

Record this result with a timestamp. Add another 50,000 rows, rerun ANALYZE TABLE, and capture a second snapshot. Two observations are not enough for a production forecast, but they prove the measurement pipeline and make the later formulas concrete.

2. Capacity is a set of interacting budgets

3. Build a server-side inventory from observable metadata

SELECT TABLE_SCHEMA,       ROUND(SUM(DATA_LENGTH+INDEX_LENGTH)/1024/1024,1) AS allocated_mib,       ROUND(SUM(DATA_FREE)/1024/1024,1) AS data_free_mibFROM INFORMATION_SCHEMA.TABLESWHERE TABLE_SCHEMA NOT IN ('mysql','information_schema','performance_schema','sys')GROUP BY TABLE_SCHEMAORDER BY SUM(DATA_LENGTH+INDEX_LENGTH) DESC;SELECT TABLE_SCHEMA,TABLE_NAME,ENGINE,TABLE_ROWS,       ROUND(DATA_LENGTH/1024/1024,1) AS data_mib,       ROUND(INDEX_LENGTH/1024/1024,1) AS index_mib,       ROUND(DATA_FREE/1024/1024,1) AS data_free_mibFROM INFORMATION_SCHEMA.TABLESWHERE TABLE_SCHEMA NOT IN ('mysql','information_schema','performance_schema','sys')ORDER BY DATA_LENGTH+INDEX_LENGTH DESCLIMIT 20;SHOW BINARY LOGS;SHOW GLOBAL VARIABLES WHERE Variable_name IN('log_bin','binlog_expire_logs_seconds','tmpdir','innodb_file_per_table');

InnoDB row counts and metadata sizes can be estimates. Pair SQL inventory with operating-system filesystem usage and actual backup sizes. Chapter 17 will formalize that correlation.

4. Create a growth ledger instead of extrapolating from one snapshot

Capture the same inventory daily/weekly and store the observation time. For each capacity class, calculate both recent and longer-window growth so seasonal bursts are visible.

observed_daily_growth = (bytes_now - bytes_30_days_ago) / 30forecast_bytes_at_horizon = bytes_now + observed_daily_growth * horizon_daysheadroom_days = free_bytes / conservative_daily_growth# Use a conservative growth rate such as a high recent percentile or scenario,# not only the long-term average.

A straight line is a scenario, not a law. Product launches, retention changes, index additions, binlog policy, compaction/rebuilds, and migration can all change the slope. Record best/base/stress scenarios and the event that would invalidate each one.

5. Maintenance work needs temporary capacity as well as time

rebuild_time ≈ bytes_processed / measured_effective_rebuild_throughputbackup_time  ≈ backup_bytes / measured_backup_throughputrestore_time ≈ restore_bytes / measured_restore_throughput + prepare/validation_timepeak_required_space ≈ current_dataset                    + rebuild_or_copy_working_space                    + temp/change_buffer_growth                    + redo/binlog_growth_during_window                    + safety_margin

Measure each throughput on target-like storage with representative concurrency. A sequential filesystem copy benchmark is not an ALTER TABLE benchmark, and a backup creation rate is not a restore rate. Restore includes preparation, ownership, startup/recovery, and validation.

6. Worked planning example—without pretending the numbers are universal

At 210 MiB/s, a 2.4 TiB rebuild has a raw transfer-equivalent duration of roughly 3.3 hours before final synchronization, lock waits, or validation. That does not prove a 3.3-hour production window. Add observed online-change overhead, peak binlog growth, safety margin, and rollback time. If the tested full restore exceeds the 8-hour RTO, the problem is not solved by writing “RTO = 8h” in a runbook; the architecture/backup strategy must change.

7. Replication and Galera change the maintenance admission test

Never consume the final healthy redundancy unit for convenience. A three-node Galera cluster doing maintenance on one node has only two voting members left; a second failure becomes qualitatively different. A primary with one replica may lose its read/failover safety margin while the replica is being rebuilt.

8. Wrong approach: use 80% disk as a universal alert threshold

A percentage-only alert ignores growth rate and required maintenance headroom. Ten terabytes free on a slow-growing archive may be comfortable, while 20% free on a fast-growing primary may be only three days of runway—and still insufficient for a table copy.

trigger if headroom_days < lead_time_to_add_capacity + safety_daystrigger if free_bytes < largest_planned_rebuild_working_set + log_growth_margintrigger if tested_restore_time > RTOtrigger if retained_binlog_window < required_PITR_or_replica_reseed_windowtrigger if backup_age_or_restore_drill_age exceeds policy

These are policy shapes, not universal constants. Choose thresholds from your procurement/cloud expansion lead time, observed growth distribution, workload criticality, and recovery requirements.

9. Build the maintenance evidence packet

10. Production judgment and bridge to observability

Capacity planning is successful when it tells you when an operation is no longer safe, not merely how much space exists. Maintain a measured inventory, forecast multiple scenarios, test restores and rebuilds, and reserve enough headroom for the worst planned operation plus recovery. Chapter 17 will turn these manually collected signals into Performance Schema/sys/log/OS baselines and alerts.

DROP DATABASE IF EXISTS servicehub16_l5;