Scaling Rental MDM: What One Database Optimisation Round Involved | MDM.Plus

Industry Insights 2026-06-13 Views 46

Scaling Rental MDM: What One Database Optimisation Round Involved

Growth pressure on a rental MDM platform rarely starts with page views. It shows up in read/write contention, slow queries, connection-pool use, index hit rates, lock waits, cache performance, queue backlog and statistics latency. A recent optimisation round at MDM.Plus was therefore a systematic pass from business path to database architecture, not a simple server upgrade.

Where pressure appeared first

Each merchant generates constant work: bulk device entry, status queries, overdue filtering, lock and unlock actions, policy checks, exports and dashboards. Devices write just as continuously, with online and offline events, policy results, command receipts, information updates, risk changes and logs. Reads and writes arrive together, and the data grows fast. Early signs were not outages but more slow queries, higher database CPU, fluctuating buffer-pool hit rates, fuller connection pools, longer P95 and P99 times, slower peak-hour filtering, delayed state writes and exports slowing ordinary queries.

Finding the source

Starting from slow-query logs, the team reviewed execution time, rows scanned and returned, index usage, temporary tables, filesort, full scans, repeated queries, deep pagination and large joins. Pressure came from overlapping high-frequency scenarios, above all the device list query, which combines merchant ID, device SN, customer number, group, online, lock and overdue state, and several time fields. Single-column indexes that worked at small scale failed once a merchant held thousands of devices.

Rebuilding the query path

EXPLAIN checks confirmed whether access types moved from ALL to range, ref or const, whether expected indexes were used, and whether Using temporary or Using filesort disappeared. Composite indexes were rebuilt around real patterns: merchant plus status, lock state, due time, last report, group plus status and registration sort. Index sprawl was avoided because heavy device writes make every extra index expensive. The goal was a read/write balance.

  • Deep pagination was reduced, sort indexes tuned, cursor paging used where suitable, lists limited to needed fields, detail loaded on demand, and exports separated from normal paging.
  • Hot and cold data were split: current state in high-frequency tables, history archived by time, logs tiered by period, reports off the main tables.
  • Cache policies were tiered: long TTL for configuration, short for dashboards, cache plus refresh for state summaries, invalidation on policy change, and guards against penetration, breakdown and avalanche.
  • Non-urgent work moved to queues: logs, statistics, risk calculations, notifications, reports and bulk exports.
  • Statistics moved from real-time counting to periodic aggregation, event-driven updates and per-merchant precomputation.
  • Export jobs were backgrounded, batched and rate-limited.
  • Monitoring shifted from uptime to health: QPS, TPS, slow queries, P95/P99, connections, buffer-pool hits, disk I/O, replication lag, lock waits, deadlocks, queue backlog, cache hits and callback delay.

Slow queries and scan counts fell, CPU and pools steadied, and lists, filtering, dashboards and reporting became more predictable. The value of SaaS is catching trends before an outage and sharing the fix across tenants. Final scope is subject to the confirmed product specification and service agreement.

Email 400 816 5855