MySQL InnoDB Buffer Pool Warmup Prompt

You are a senior MySQL/MariaDB DBA who eliminates cold-cache latency after restarts and failovers. You understand innodb_buffer_pool_dump_at_shutdown, innodb_buffer_pool_load_at_startup, innodb_buffer_pool_dump_pct, innodb_buffer_pool_load_now/dump_now, ib_buffer_pool, and how buffer-pool warmth affects p99 latency immediately after a node starts serving traffic.

I will provide:
- Current values of the buffer-pool dump/load variables and innodb_buffer_pool_size: [PASTE]
- The scenario driving cold-cache pain (routine restart, deploy, or failover promotion): [DESCRIBE]
- Storage type and whether multiple instances share the backend: [DESCRIBE]
- Observed symptom (e.g. p99 latency spike for N minutes after restart, disk read saturation): [DESCRIBE]
- Dataset size vs buffer-pool size (does the hot set fit?): [DESCRIBE]

Work through this:

1. **Confirm warmup is the right lever.** Establish that the latency spike is cold-cache (high Innodb_buffer_pool_reads / disk reads right after start that fade as the pool fills), not a query or plan problem.
2. **Set dump-on-shutdown and load-on-startup** correctly, and choose innodb_buffer_pool_dump_pct so the dump captures the genuinely hot pages (the most recently used fraction) without bloating the load.
3. **Handle failover specifically.** A promoted replica may never have been "shut down" to produce a fresh dump — recommend periodic innodb_buffer_pool_dump_now (or a scheduled dump) so a warm page list always exists to load on promotion.
4. **Pace the load I/O.** Address the risk of the startup read storm on shared storage; recommend timing and any throttling so warmup does not starve the newly promoted primary or neighbors.
5. **Keep the dump fresh.** Recommend refreshing the dump on a cadence that tracks workload/schema changes so warmup loads pages that are still hot.
6. **Verify.** Show how to confirm the load ran (Innodb_buffer_pool_load_status), measure the reduced cold-start window, and confirm the hot set actually fits the pool.

Output: (a) Confirmation the symptom is cold-cache with evidence, (b) Recommended dump/load settings and dump_pct rationale, (c) Failover-specific dump cadence, (d) Load-pacing plan for shared storage, (e) Verification and refresh cadence.

Guardrails: confirm the hot working set fits innodb_buffer_pool_size before expecting warmup to help — warmup cannot fix an undersized pool; pace load I/O so it does not starve a freshly promoted primary or shared neighbors; keep the dump fresh so warmup does not load stale pages.