Cache affinity tuning for database systems is the practice of keeping database threads, data, and memory accesses aligned with specific CPU cores and their caches so that frequently used data stays “close” to the CPU that uses it.

The core idea is:

Reduce expensive memory accesses by maximizing cache reuse and locality, especially under high concurrency.

1. Why cache affinity matters in databases

Modern CPUs have:

• L1 / L2 (core-private caches)
• L3 (shared cache across cores)
• DRAM (much slower than cache)

Database workloads:

• repeatedly access hot data (indexes, buffer pools, lock tables)
• run many concurrent threads

👉 Without affinity:

• data keeps moving between cores
• caches get invalidated
• latency increases

2. What “affinity” means

A. CPU affinity

• pin a database thread to a specific CPU core

B. Memory affinity (NUMA)

• allocate memory close to the CPU that uses it

C. Cache affinity

• ensure the same thread repeatedly uses the same cache lines

👉 All three together improve locality.

3. Problems without cache affinity

A. Cache thrashing

• threads move across cores
• cache lines constantly evicted

B. Cache line bouncing

• multiple cores modify same data
• cache coherence traffic increases

C. NUMA penalties

• remote memory access increases latency

D. Higher latency variance

• unpredictable cache behavior
• impacts p99 response time

4. How cache affinity tuning works

A. Thread pinning

• assign DB worker threads to fixed CPU cores
• avoid frequent rescheduling

👉 improves L1/L2 cache reuse

B. Data partitioning

• split database into partitions (shards)
• assign each partition to specific cores

👉 reduces cross-core contention

C. Buffer pool locality

• divide buffer pool into segments
• align segments with CPU cores or NUMA nodes

👉 improves cache hit rate

D. Lock/data structure locality

• keep frequently accessed structures (locks, queues) near threads that use them

E. NUMA-aware allocation

• allocate memory from local NUMA node
• avoid remote memory access

5. Example (database worker threads)

Without affinity:

Thread → Core 1 → Core 5 → Core 2 → Core 7

👉 cache is constantly invalidated

With affinity:

Thread → Core 3 (consistent)

👉 cache stays warm → faster execution

6. Impact on performance

A. Lower latency

• faster data access from cache

B. Higher throughput

• less memory contention
• more efficient CPU usage

C. Reduced cache coherence traffic

• fewer inter-core invalidations

D. Improved tail latency (p99)

• more predictable execution

7. When it matters most

Cache affinity is critical for:

• high TPS OLTP systems
• in-memory databases
• lock-heavy workloads
• systems with many cores (SMP/NUMA)

8. Trade-offs

A. Reduced scheduling flexibility

• pinned threads cannot move freely

B. Load imbalance risk

• some cores may be overloaded

C. Complex tuning

• requires workload-specific tuning

9. IBM system perspective

In enterprise systems:

• hypervisors manage CPU allocation
• workload managers like IBM Workload Manager help maintain locality indirectly
• large caches and NUMA-aware designs support affinity optimization

10. Common tuning patterns

A. Core-to-partition mapping

• assign DB partitions to specific cores

B. NUMA node binding

• bind DB instance to memory node

C. Worker thread pools

• fixed threads per core

D. Cache-friendly data structures

• reduce sharing across cores

11. Simple mental model

Think of cache affinity as:

Giving each worker in a factory their own workstation with their own tools, instead of making them constantly move between stations and share tools—reducing movement and increasing efficiency.

12. Key takeaway

Cache affinity tuning in database systems:

• aligns threads, data, and memory with CPU cores
• improves cache reuse and reduces memory latency
• minimizes cache coherence overhead
• reduces latency variance and improves throughput

👉 Result: faster, more predictable database performance under high concurrency