Methods and Practices for Reducing MySQL Database Storage Costs

Background – Rapid platform growth has caused backend data volume and hardware costs to rise sharply; a billing system stores over 100 TB across 40 machines and 20 480 tables, making cost‑reduction a critical issue.

Challenges – Data safety (risk of loss during deletion, compression, or hot‑cold separation), system stability (performance degradation, increased latency, higher CPU/memory usage), and uncertain benefit estimation for actions such as index removal or hot‑cold segregation.

Systematic Method – A nine‑grid framework classifies reduction tactics across three dimensions (field, table, database) and three actions (delete, reduce, shrink). The author’s practical plan includes six items: large‑table compression, large JSON field serialization, removal of invalid data, dropping unused tables, dropping unused indexes, and hot‑cold separation.

Benefit Calculation – Benefits are measured by sampling, estimating reduction, and computing proportion. Example: compressing large JSON fields yields a 12 % disk reduction, calculated via SELECT LENGTH(...) and compression ratios, leading to an overall 50.7 % space saving across the system.

Data Safety & System Stability – Validation follows Google SRE’s four golden metrics (exceptions, latency, traffic, saturation). Tests cover compression‑time read/write latency, throughput impact, exception handling, rollback capability, and post‑compression performance under peak traffic.

Verification Steps – 1) Build a 20:1 replica environment; 2) Simulate production traffic; 3) Observe throughput, slow queries, and latency; 4) Ensure any exception triggers a safe rollback. An unexpected duplicate‑key error during testing demonstrated the rollback mechanism.

Gray Deployment & Rollback – Deploy changes gradually, starting with a single table, monitoring the four golden metrics, and expanding only after successful validation. Rollback is performed via ALTER commands to revert compression.

Conclusion – By following the systematic method, benefit calculation, safety verification, and staged rollout, the billing system achieved 32 % reduction on large tables, 12 % on JSON fields, 10 % on invalid data, and overall >50 % disk space savings, demonstrating a repeatable approach for MySQL cost optimization.