Storage Model Optimization and Performance Testing for Hot SKU Inventory Pre‑occupancy

Performance testing and tuning are essential for ensuring system capacity and stability, especially when handling high‑traffic inventory pre‑occupancy scenarios. This article delves into storage model optimization, call‑chain analysis, and practical pressure‑testing methods to boost data processing speed.

Inventory pre‑occupancy reserves SKU stock briefly during order intake. Two implementation paths are described: (1) department‑level pre‑occupancy using Redis cache, and (2) batch‑level pre‑occupancy handled directly by the database. During peak promotional periods, hotspot SKU requests can cause the system’s TP99 latency to spike, leading to order‑timeouts.

Test Scenario 2.1 – Hotspot Data Storage Model Pressure Test – The goal is to determine the maximum QPS the "pre‑occupancy interface" can sustain under database‑backed hotspot SKU requests while keeping TP99 ≤ 3000 ms, and to verify improvements after tuning (target TP99 ≤ 500 ms). The test starts at QPS = 10 and increments by 10.

Results: At QPS = 50 the system remains stable (TP99 ≈ 100 ms) with CPU ≤ 15 % and memory ≤ 35 % for the pre‑occupancy service. When QPS reaches 60, TP99 rapidly climbs to ~7000 ms, indicating a bottleneck. Further scaling to QPS = 1100 (post‑upgrade) shows TP99 ≈ 130 ms and TPS up to 1200, a 2300 % capacity increase.

Key metrics from the tests (CPU, memory, slow‑SQL, Redis usage) are presented in accompanying charts. The analysis identifies row‑level lock contention in the database as the primary bottleneck during high‑frequency hotspot SKU writes, while hardware resources remain sufficient.

Optimization: The storage layer was re‑architected by moving hotspot SKU pre‑occupancy from direct database writes to a Redis cache front‑end, leveraging Redis’s high‑throughput capabilities. Consistency is ensured through asynchronous write‑back to the database and extended cache key lifetimes for frequently accessed SKUs.

Additional improvements include eliminating invalid calls from the "order‑package‑detail" interface by adjusting call logic based on order status, fixing AB‑test alias misconfigurations, and implementing dynamic cache‑key expiration policies.

Post‑optimization results show a 60 % reduction in total calls and a 64 % drop in peak call volume for the "access‑return" service, confirming the effectiveness of the redesign.

The article concludes with recommendations for early risk identification during traffic analysis, standardized coding practices to avoid invalid queries, and continuous OpsReview processes to maintain system robustness under evolving load patterns.