How JD Logistics Scales Warehouse Databases with Automation and High‑Availability Strategies

Database Architecture

The warehouse management system (WMS) uses two deployment models for its MySQL database:

Local mode

Both the WMS application and the database run inside each warehouse to minimise network latency. This reduces response time for inbound/outbound operations but requires reliable power and network infrastructure at each site.

Centralized mode

A single WMS instance is hosted in an IDC data centre and regional warehouses connect via dedicated lines. This simplifies resource management and operations, but introduces additional network latency for distant sites.

Growth in warehouse count and transaction volume has driven the need for a high‑performance, high‑availability database solution.

UDBA Automation Platform

UDBA is an internal automation platform built by the DBA team to standardise routine MySQL operations, provide self‑service queries for developers, and reduce manual coordination.

Key functional modules include:

Automated backup, restore and binlog management.

Real‑time health dashboards and alert routing.

One‑click execution of common DBA tasks (e.g., user provisioning, schema changes).

Integration with monitoring and ticketing systems for end‑to‑end visibility.

Performance Optimization

Warehouse workloads generate large reporting tables (single tables >10 million rows) and complex joins. The migration from SQL Server to MySQL introduced additional tuning challenges.

Optimization workflow:

Continuous performance monitoring using MySQL performance_schema and external APM tools.

Daily extraction of slow‑query logs, aggregation of the top‑N offenders, and automated email reports to the owning development team.

Periodic database health inspections that focus on high‑load instances, index usage, and I/O patterns.

SQL training sessions covering MySQL syntax best‑practices, proper schema design, and query‑tuning techniques.

Documentation of recurring performance issues in a shared knowledge base to avoid duplicate work.

Close collaboration with developers to understand business‑critical queries and redesign them when necessary.

Example: a frequent InnoDB deadlock was traced to a “try‑update‑then‑insert” pattern. Using SHOW ENGINE INNODB STATUS the DBA reproduced the deadlock, demonstrated the root cause to developers, and recommended an upsert‑style rewrite. The fix was codified in an internal guideline.

Fault Self‑Healing

The platform implements an MHA‑style high‑availability layer that automatically promotes a replica when the primary experiences a failure and vice‑versa.

Key behaviours:

Automatic role switch on detection of primary/replica anomalies.

SMS, WeChat and email notifications to on‑call personnel.

Event logging for post‑mortem analysis.

Replication‑lag detection; when lag exceeds a threshold the system temporarily sets innodb_flush_log_at_trx_commit=2 and sync_binlog=0 to accelerate catch‑up, then restores the standard values.

Automatic restart of stalled IO or SQL replication threads.

Data Archiving (Migration)

Production databases retain three months of data; reporting databases retain one year. Data older than these windows is either deleted (production) or migrated to an IDC archive.

Prior to automation, DBAs manually executed migration scripts on each server, which was error‑prone. The new automated migration platform provides:

Centralised scheduling via a cron‑like UI.

Dynamic scaling of the archival store using CockroachDB, which offers distributed writes and horizontal expansion.

Separation of duties: DBAs manage the migration engine, while data administrators define retention policies.

Real‑time dashboards showing job status, row counts, and error logs.

Upgrade and Scaling

All warehouses originally ran MySQL 5.5. After extensive testing, the team migrated to MySQL 5.7, which provides:

Performance: Tens‑fold throughput increase under high concurrency, as shown in MySQL benchmark reports.

High availability: Multi‑threaded replication and semi‑synchronous (AfterSync) mode reduce replication lag.

Maintainability: GTID‑based replication, online DDL, richer system views and diagnostic functions.

Hardware upgrades accompanied the software upgrade: RAID controllers were replaced with SSD arrays, and CPU/Memory configurations were increased to meet I/O and compute demands.

Upgrade execution strategy:

Schedule the upgrade during nightly warehouse shutdown windows.

Break the process into granular steps (binary upgrade, data migration, schema validation, replication re‑configuration).

Use pre‑written validation scripts to verify data integrity after each step.

Maintain rollback plans and automated health checks before resuming production.