How to Cut MySQL Storage Costs by 50%: A Systematic Approach

Background

The billing system stores more than 100 TB of MySQL data across 40 physical machines, 40 databases and 20 480 tables per shard, making storage a major cost center.

Challenges

Data safety : Deleting, compressing or separating hot/cold data on a 100 TB store risks data loss or inaccessibility.

System stability : Space‑saving techniques that trade time for space (e.g., compression) can increase read/write latency, reduce throughput and raise CPU/memory usage, potentially causing request rejections.

Benefit uncertainty : The actual gain from index deletion, metadata cleaning or hot‑cold separation varies widely and migration costs can be high.

Systematic Nine‑Grid Method

A nine‑grid matrix combines three dimensions (field, table, database) with three actions (delete, reduce, compress). Using this matrix the team identified six concrete actions:

Large‑table compression

Large JSON‑field serialization

Deletion of invalid data

Removal of unused tables

Removal of unused indexes

Hot‑cold data separation

These actions were prioritized based on expected ROI.

Benefit Estimation

Each proposal is evaluated through sampling, reduction estimation and proportion calculation. For JSON‑field serialization the calculation is:

Reduced characters = total_characters * (1 - compression_ratio)
Disk saved per table = (reduced_characters / total_characters) * table_disk_size
Total saved = disk_saved_per_table * number_of_tables

Measured reductions were: large‑table compression 32 %, JSON‑field serialization 12 %, invalid‑data deletion 10 %, unused‑table and unused‑index removal ≈ 1 % each. The combined effect reduced disk usage by 50.7 %.

Data Safety and System Stability Verification

Verification follows Google SRE’s four golden metrics: exceptions, latency (tp99), traffic (tps) and saturation (CPU, memory, disk, network). During compression the team monitors:

Read/write latency increase

Throughput impact

Exception occurrence and rollback capability

Potential data loss

After compression the same metrics are measured under peak‑traffic conditions to ensure SLA compliance.

Gray‑Scale Deployment and Rollback

Deploy the change on a single table (smallest granularity) and monitor the metrics.

Gradually expand to additional tables, proceeding only after each phase passes high‑traffic validation.

Rollback is performed with an ALTER command that reverses the compression; a ticket is raised to execute the rollback if needed.

Conclusion

The systematic workflow—proposal → benefit calculation → safety verification → stability verification → gray rollout → rollback—enabled the billing system to cut more than half of its storage footprint, dramatically lowering operational costs. The methodology provides a practical, repeatable guide for teams tackling MySQL storage cost‑reduction challenges.