MySQL InnoDB Buffer Pool & Config Tuning 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.