MySQL InnoDB Buffer Pool & Config Tuning Prompt
Right-size the InnoDB buffer pool and related memory/IO settings for a described workload and host.
- Target user
- DBAs and SREs tuning InnoDB memory and durability settings
- Difficulty
- Advanced
- Tools
- Claude, ChatGPT, Cursor
The prompt
You are a senior MySQL/MariaDB DBA who tunes InnoDB for throughput and durability. You understand the buffer pool, buffer pool hit ratio, dirty page flushing, the redo log (innodb_log_file_size / innodb_redo_log_capacity), innodb_flush_log_at_trx_commit, innodb_flush_method, innodb_io_capacity, and the trade-off between durability and speed. I will provide: - Host resources (total RAM, CPU, storage type — NVMe/SSD/network): [PASTE] - Current relevant settings from `SHOW GLOBAL VARIABLES` (innodb_buffer_pool_size, instances, log file size, flush settings): [PASTE] - Buffer pool and IO metrics from `SHOW ENGINE INNODB STATUS` and `SHOW GLOBAL STATUS` (Innodb_buffer_pool_read_requests, _reads, dirty pages, _os_log_written): [PASTE] - The workload: read/write ratio, working set size, OLTP vs analytics, durability requirements: [DESCRIBE] Work through this: 1. **Compute the buffer pool hit ratio** from reads vs read_requests and compare the buffer pool size to the working set. State whether the pool is undersized. 2. **Size innodb_buffer_pool_size** against total RAM, leaving headroom for the OS, connections, and other memory grants; recommend buffer pool instances for the size. 3. **Right-size the redo log.** Too small causes frequent checkpoint flushing and write stalls; size it from the observed redo write rate. 4. **Tune durability vs speed.** Explain innodb_flush_log_at_trx_commit (1 = ACID-safe, 2/0 = faster but data-loss window) and innodb_flush_method (O_DIRECT) against the stated durability requirement — do not silently weaken durability. 5. **Set IO capacity** (innodb_io_capacity / _max) to the storage's real capability, and address dirty-page flushing if checkpoints are bursty. Output: (a) Findings with the numbers, (b) Recommended my.cnf values with before/after and rationale, (c) Which changes need a restart vs SET GLOBAL, (d) Metrics to watch after applying. Guardrails: change one or two variables at a time and measure; apply on a replica or staging host first and back up my.cnf and data; never reduce innodb_flush_log_at_trx_commit below the durability the application needs without explicit sign-off; restart-required changes need a maintenance window — never bounce prod without review.
Why this prompt works
InnoDB tuning is full of cargo-cult advice — “set the buffer pool to 80% of RAM” repeated without context. This prompt instead forces a measurement-first approach: compute the actual buffer pool hit ratio, compare the pool to the working set, and read the redo write rate before recommending anything. That turns tuning from guesswork into a calculation grounded in SHOW ENGINE INNODB STATUS and SHOW GLOBAL STATUS counters.
It keeps the dangerous knobs honest. The single most abused InnoDB setting is innodb_flush_log_at_trx_commit, which people quietly drop to 2 for a speed win without realizing they have opened a window to lose committed transactions on a crash. By making the model weigh this explicitly against the stated durability requirement, and by labelling restart-required changes, the prompt prevents the kind of “fast but lossy” config that bites you only during an outage.
The guardrails enforce the discipline experienced DBAs actually use: change one variable at a time, measure, and never let the buffer pool grow so large the host swaps or gets OOM-killed. Pairing the recommendation with before/after values and follow-up metrics means a human can review the change and verify it helped, rather than discovering regressions in production.