Introduction

MySQL InnoDB buffer pool flushing stuck when dirty pages percentage too high and IO capacity limited. This guide provides step-by-step diagnosis and resolution.

Symptoms

Typical error output:

bash
ERROR: InnoDB buffer pool dirty page percentage exceeds 75%
Flushing stuck: adaptive flushing not aggressive enough
IO capacity: 200 IOPS, Required: 500 IOPS

Common Causes

  1. 1.Dirty page percentage exceeds adaptive flushing threshold
  2. 2.IO capacity configuration too low for workload
  3. 3.LRU flushing not aggressive enough
  4. 4.innodb_max_dirty_pages_pct_lwm not configured

Step-by-Step Fix

Step 1: Check Current State

sql
SHOW ENGINE INNODB STATUS\G
SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool%';
SELECT * FROM performance_schema.file_summary_by_instance WHERE file_name LIKE '%ibdata%';

Step 2: Identify Root Cause

sql
-- Check for blocking processes
SELECT * FROM pg_stat_activity WHERE state != 'idle';
SELECT * FROM information_schema.processlist WHERE time > 30;

Step 3: Apply Primary Fix

```sql -- Increase IO capacity SET GLOBAL innodb_io_capacity = 2000; SET GLOBAL innodb_io_capacity_max = 4000;

-- Increase dirty pages low water mark SET GLOBAL innodb_max_dirty_pages_pct_lwm = 10; SET GLOBAL innodb_max_dirty_pages_pct = 75; ```

Step 4: Apply Alternative Fix

```sql -- Alternative fix: Check configuration SELECT * FROM pg_settings WHERE name LIKE '%vacuum%';

-- Adjust parameters dynamically ALTER SYSTEM SET autovacuum_vacuum_cost_delay = 10; SELECT pg_reload_conf();

-- Verify the fix SELECT * FROM pg_stat_user_tables WHERE relname = 'target_table'; ```

Step 5: Verify the Fix

sql
SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool%';
SELECT dirty_page_ratio FROM performance_schema.file_summary_by_instance WHERE file_name LIKE '%ib_buffer_pool%';
SHOW ENGINE INNODB STATUS\G

Common Pitfalls

  • Running vacuum during peak hours without resource management
  • Forgetting to analyze after vacuum for statistics update
  • Not monitoring autovacuum progress on large tables
  • Setting cost delay too high for high-churn tables

Best Practices

  • Schedule maintenance windows for vacuum full operations
  • Monitor bloat ratio and autovacuum frequency
  • Tune autovacuum parameters per table based on churn rate
  • Use pg_stat_progress_vacuum to monitor vacuum progress
  • PostgreSQL Autovacuum Not Running
  • PostgreSQL Dead Tuple Accumulation
  • PostgreSQL Transaction ID Wraparound
  • PostgreSQL Table Size Excessive