innodb_io_capacity
WARNINGINFO
2 automated checks cover this variable.
Overview
- Purpose
- Documented in the MySQL 8.4 manual as a server system variable (scope: Global). Purpose and semantics are described at the linked manual page.
- Dynamic (MySQL 8.4 reference)
- MySQL 8.4 marks this variable as dynamic (
Dynamic= Yes). Runtime changes useSET GLOBAL(global scope) orSET SESSION(session scope) — confirm syntax and persistence (SET PERSIST) in the manual. - Default value
- 200 (MySQL 8.4)
- Version and product notes
- MariaDB and Percona Server may use different names, defaults, or dynamic behavior; verify their documentation.
- Documentation
- https://dev.mysql.com/doc/refman/8.4/en/innodb-parameters.html#sysvar_innodb_io_capacity
- Other vendors
What is checked
Rules that reference this variable, with their severity and what each rule detects:
- INFO Tune to your device and workload rather than a fixed number: (1) measure sustained random-write IOPS (e.g. with fio) and set innodb_io_capacity to ~50-75% of that sustained figure; (2) set innodb_io_capacity_max to ~1.5-2x innodb_io_capacity; (3) expect benefit only under real write/flush activity — watch Innodb_buffer_pool_wait_free (want 0), dirty-page ratio (Innodb_buffer_pool_pages_dirty vs total / innodb_max_dirty_pages_pct), and checkpoint age; (4) on SSD also set innodb_flush_neighbors=0. Raise gradually and validate with before/after metrics — too high wastes I/O and increases flash wear.
- WARNING Increase innodb_io_capacity based on measured device throughput (see the informational io_capacity note): set it to ~50-75% of sustained random-write IOPS, set innodb_io_capacity_max to ~1.5-2x that, and set innodb_flush_neighbors=0 on SSD. Re-check that Innodb_buffer_pool_wait_free returns to 0 after the change; raise gradually to avoid excess write amplification.
Tuning guidance
- Recommended actions:
- Tune to your device and workload rather than a fixed number: (1) measure sustained random-write IOPS (e.g. with fio) and set innodb_io_capacity to ~50-75% of that sustained figure; (2) set innodb_io_capacity_max to ~1.5-2x innodb_io_capacity; (3) expect benefit only under real write/flush activity — watch Innodb_buffer_pool_wait_free (want 0), dirty-page ratio (Innodb_buffer_pool_pages_dirty vs total / innodb_max_dirty_pages_pct), and checkpoint age; (4) on SSD also set innodb_flush_neighbors=0. Raise gradually and validate with before/after metrics — too high wastes I/O and increases flash wear.
- Increase innodb_io_capacity based on measured device throughput (see the informational io_capacity note): set it to ~50-75% of sustained random-write IOPS, set innodb_io_capacity_max to ~1.5-2x that, and set innodb_flush_neighbors=0 on SSD. Re-check that Innodb_buffer_pool_wait_free returns to 0 after the change; raise gradually to avoid excess write amplification.
- Trade-offs: Higher I/O capacity values push more writes to disk, reducing lag and crash recovery time, but can saturate storage on spinning disks. Match settings to your storage hardware (SSD vs HDD).
Example
SET GLOBAL innodb_io_capacity = 2000;
SET GLOBAL innodb_io_capacity_max = 4000;
-- SSD: 2000-10000, HDD: 200-800
Always validate on a non-production instance first. Use SET PERSIST (MySQL 8.0+) for changes that should survive restarts.