Cache prefetch aggressiveness creates a tension between latency and throughput in mixed database workloads—especially on systems like IBM POWER10 where many threads and cores share caches and memory bandwidth.

🔷 The Core Trade-off

• OLTP (transactional) → needs low latency, high cache hit rate
• OLAP (analytical) → needs high bandwidth, deep streaming

👉 Prefetch aggressiveness directly shifts the balance between these two.

🔷 1. When Prefetching is Aggressive

🚀 Helps OLAP a lot

• Detects sequential patterns (table scans, joins)
• Pulls data into cache early
• Keeps execution units busy

👉 Result:

• Higher scan throughput
• Better CPU utilization

❌ Hurts OLTP performance

🔸 Cache pollution

• Prefetched OLAP data fills caches (L2/L3)
• Evicts OLTP’s hot data (indexes, small rows)

👉 OLTP sees:

• More cache misses
• Slower response times

🔸 Memory bandwidth contention

• Prefetch requests compete with real loads

👉 OLTP suffers:

• Increased memory latency
• Queueing delays in memory controller

🔸 Wasted work (low accuracy)

• OLTP access is random/unpredictable
• Aggressive prefetch often fetches unused data

👉 Net effect:

• Bandwidth + cache wasted

🔷 2. When Prefetching is Conservative

✅ Helps OLTP

• Preserves cache for hot working sets
• Reduces unnecessary memory traffic

👉 Result:

• Lower latency per transaction
• More stable performance

❌ Hurts OLAP

• More cache misses during scans
• CPU stalls waiting for memory

👉 Result:

• Lower analytical throughput

🔷 3. Interaction Effects in Mixed Workloads

🔹 Interference Amplification

• OLAP streams dominate shared resources
• OLTP gets “crowded out”

🔹 Tail Latency Impact

• OLTP is sensitive to worst-case latency
• Even small interference → large SLA impact

🔹 SMT Contention (important on POWER10)

• Multiple threads share:
  • Caches
  • Prefetch engines
  • Memory bandwidth

👉 Aggressive prefetch from one thread can degrade others.

🔷 4. How POWER10 Mitigates This

Modern POWER cores use adaptive, workload-aware prefetching:

✅ Pattern-aware tuning

• Sequential → increase aggressiveness
• Random → throttle or disable

✅ Dynamic throttling

• Detects:
  • Bandwidth saturation
  • Cache miss spikes

👉 Reduces prefetch rate automatically

✅ Multi-level prefetch strategy

• Aggressive prefetch → often directed to L3
• L1/L2 kept more selective

👉 Protects latency-sensitive data

✅ SMT-aware fairness

• Prefetch activity is balanced across threads
• Prevents one workload from monopolizing resources

🔷 5. Practical Database Impact

🔹 OLTP queries

• Index lookups
• Small random reads

👉 Prefer:

• Low or selective prefetch

🔹 OLAP queries

• Full table scans
• Aggregations

👉 Prefer:

• Deep, aggressive prefetch

🔹 Mixed system (real-world)

Example:

• Banking app (OLTP) + reporting dashboard (OLAP)

👉 If prefetch too aggressive:

• Reports fast
• Transactions slow ❌

👉 If too conservative:

• Transactions fast
• Reports slow ❌

🔷 6. Key Insight

👉 The goal is not maximum prefetching, but correct prefetching.

The best systems:

• Increase aggressiveness only when accuracy is high
• Back off when interference appears

✅ Bottom Line

Cache prefetch aggressiveness in mixed OLTP/OLAP workloads:

• Improves OLAP throughput (streaming efficiency)
• Degrades OLTP latency (cache + bandwidth contention)