Systematic Approach to Reducing MySQL Database Storage Costs

In the context of cost‑reduction pressures across the internet industry, database storage (MySQL) has become a primary cost driver; the article introduces methods to lower database costs, outlines associated risks, and suggests mitigation measures.

The author’s department faces a billing system with >100 TB of data, 40 physical servers, and tens of thousands of tables, making cost‑reduction challenging; key challenges include data‑loss risk, system‑stability impact, limited benefits of index deletion, and high migration cost of hot‑cold separation.

A systematic nine‑grid method is proposed, mapping three dimensions (field, table, database) to three strategies (delete, reduce, shrink). Six concrete actions are identified: large‑table compression, large‑JSON field serialization, deletion of invalid data, dropping unused tables, dropping unused indexes, and hot‑cold separation.

Benefit calculation examples (e.g., JSON field compression) show how to estimate disk‑space savings using sample data, compression ratios, and table counts; results indicate 32% reduction from table compression, 12% from JSON serialization, 10% from invalid‑data deletion, and ~1% from other actions, achieving a total of 50.7% disk‑space reduction.

Data‑safety and system‑stability verification relies on Google SRE’s four golden metrics—exceptions, latency, traffic, and saturation—monitoring these during and after compression to ensure no data loss and acceptable performance impact.

Validation steps include building a 20:1 replica environment, simulating peak traffic, and observing throughput, latency, and slow‑SQL occurrences; decisions are based on SLA impact and tolerable performance degradation.

Rollback is possible via Online DDL’s ALTER commands; a timely and effective rollback plan is essential to mitigate failures such as duplicate‑key errors encountered during compression tests.

Rollout follows a gray‑release strategy with fine‑grained granularity, monitoring faults, business impact, and performance under peak load at each stage to ensure safe, incremental adoption.

In summary, the systematic approach reduced storage by over 50% with manageable risk, providing a practical template for teams seeking to lower MySQL storage costs.