How We Cut DB QPS by 80% with a Custom DAO Cache Layer

Background

In early 2020 the value‑added business system saw a sharp traffic increase after the pandemic, pushing single‑instance MySQL QPS above 30,000. The resulting high latency, thread contention, and costly context switches exposed a DB‑layer bottleneck that existing Redis and Guava caches could not solve, prompting the design of a dedicated DAO cache component.

First Cache Component (daoCache)

Design Considerations

Internal factors : only six key tables needed caching, the codebase exceeded 300k lines, and changes to existing DAO code had to be minimal.

External factors : company‑wide Redis client restrictions (no batch ops, no Lua scripts) limited lock‑free optimizations.

Consistency : evaluated four classic update orders (DB‑then‑cache, cache‑then‑DB, delete‑cache‑then‑DB, DB‑then‑delete‑cache) and chose a pragmatic mix that favors most cases while falling back to DB reads for edge cases.

Performance : aim to reduce Redis round‑trips, keep cache hit rate above 80 %, handle abnormal flows, limit lock contention, and keep the solution lightweight.

Cost : low operational overhead and fast rollout.

Implementation

The component uses a MyBatis plugin to intercept DAO calls. At application start, all methods annotated with @DaoCache are scanned and a proxy is generated. The plugin intercepts CRUD operations as follows:

Read path : a cache hit returns data directly; a miss triggers a DB query followed by a Redis write.

Write path : updates never write to the cache directly; they delete relevant keys, letting the next read repopulate the cache.

No distributed locks are used; an auto‑expire marker (default 10 s) prevents stale writes during concurrent updates. All keys have a TTL of 10–20 minutes to guarantee eventual consistency.

Key data structures:

bizKey : business primary key (e.g., orderId).

bizValue : value associated with the bizKey.

selectKey / selectKeyValue : auxiliary keys used when the query does not contain the primary key.

methodReturnValue : the DAO method’s return payload.

Mappings are stored in Redis SET and HASH structures, enabling fast reverse look‑ups and bulk invalidation.

Global Cache Hierarchy

The system adopts a two‑level cache design:

Level‑1 (process‑local) : in‑memory cache for ultra‑low latency, but without cross‑instance consistency.

Level‑2 (Redis) : shared cache across instances, logically divided into four zones—generic service cache, daoCache, application‑specific cache, and shared memory cache.

A Provider/Consumer model is used: providers own the data source and can read/write the cache; consumers only read and trigger a provider‑side fill on a miss. Redis also serves as a registration center for cache keys.

Shared Cache Mechanics

MQ messages carry only the business primary key; consumers delete local entries and lazily reload on next access.

Providers mark updates in Redis; a leader node periodically checks for stale markers (e.g., >30 s) and cleans up.

Periodic full‑sync (default 20 min) ensures eventual consistency between DB, Redis, and local caches.

Cache updates are de‑duplicated and batched to avoid message storms during bulk data changes.

Deployment & Operations

Rollout is performed gradually: low‑risk, low‑frequency DAO methods are enabled first, with a switch to bypass the cache if anomalies appear. Continuous diffing between cache and DB responses helps catch inconsistencies early.

Redis failures are tolerated by falling back to DB reads; connection timeouts are shortened and a sliding‑window splitter limits Redis traffic to 50 % of total requests.

Limitations & Lessons Learned

Placing the cache near the request tail yields limited overall benefit; higher gains are achieved when caching earlier in the request chain.

DAO methods must include the primary key in updates; otherwise the cache cannot reliably invalidate related entries.

One‑to‑many queries require explicit annotation ( @DaoCacheSelectMethod) and are not automatically handled.

Complex multi‑table joins are not supported by the current mapping logic.

Results

The systematic cache redesign reduced database QPS by more than 80 %, cut interface latency from 150 ms to 90 ms, and lowered inter‑service RPC calls by over 40 %. While the solution is tightly coupled to the project’s coding conventions, the underlying design principles—layered caching, clear consistency trade‑offs, and lightweight deployment—are applicable to many high‑throughput backend systems.