How to Cut MySQL Storage Costs by Over 50%: A Practical Framework

Background

As the platform grew, the bill‑processing system accumulated more than 100 TB of data across 40 physical servers, 40 databases, and over 20 000 tables per shard, leading to rapidly increasing hardware costs. Reducing database storage became a top priority.

Challenges

The main obstacles are:

Data safety : Deleting data, compressing, or separating hot and cold data risks loss or inaccessibility.

System stability : Space‑saving techniques trade time for space, potentially increasing read/write latency, reducing throughput, and causing thread contention or request rejection.

Benefit uncertainty : The ROI of index deletion, data cleaning, or hot‑cold separation varies widely and can be hard to predict.

Systematic Method (Nine‑grid Framework)

The author proposes a nine‑grid matrix that combines three dimensions (field, table, database) with three strategies (delete, reduce, compress). Using this matrix, the following concrete actions were defined for the bill‑system:

Large‑table compression

Large JSON‑field serialization

Deletion of invalid data

Dropping unused tables

Dropping unused indexes

Hot‑cold data separation

These actions are prioritized based on expected benefit and implementation cost.

Benefit Estimation

Each proposal is measured by sampling, estimating the reduction amount, and calculating its proportion of total storage. For example, compressing a large JSON field involves:

Reduction = (OriginalChars - CompressedChars) / TotalChars * SingleTableSpace * TableCount

Using the bill‑system data, the calculation yielded a 12 % reduction for JSON serialization. The field length can be obtained with SELECT LENGTH(column). The overall results were:

Large‑table compression: 32 %

JSON field serialization: 12 %

Invalid data deletion: 10 %

Unused tables/indexes: ~1 % each

Combined, these measures saved approximately 50.7 % of disk space.

Data Safety and System Stability Verification

Both safety and stability must be validated using Google SRE’s four golden metrics: exceptions, latency (e.g., tp99), traffic (tps), and saturation (CPU, memory, disk, network). Verification steps include:

Build a scaled test environment (e.g., 20:1 replica of production).

Simulate peak traffic using load generators.

During compression, monitor read/write latency, throughput, exceptions, rollback capability, and data loss.

After compression and during peak periods, repeat the monitoring.

If any metric fails, the proposal must be rejected.

Implementation and Rollout

Deployment follows a gray‑scale approach, focusing on three aspects:

Fault impact : Start with the smallest granularity (e.g., compress a single table) to limit potential damage.

Business coverage : Ensure the change passes all production scenarios before expanding.

Performance under peak : Each gray‑scale stage must survive at least one high‑traffic window to confirm that throughput and latency remain within SLA.

Rollback mechanisms must be timely and effective; for compression, an ALTER command can decompress the table.

Conclusion

The end‑to‑end process—methodology, benefit calculation, safety and stability validation, and staged rollout—enabled the bill‑system to achieve a 50.7 % reduction in disk usage, dramatically lowering storage costs while maintaining data integrity and system performance.