Recommended Minimum Buffer Pool Size
For a low‑traffic MySQL 8.0 instance where the database is the primary memory consumer, set innodb_buffer_pool_size to roughly 50‑60 % of total RAM. This keeps the pool resident, avoids swapping, and leaves memory for the OS and MySQL background threads.
If other services share the host, reduce the allocation to 30‑40 % of RAM. For very small datasets (< 100 MB) a pool as low as 128 MB can be adequate, provided at least 256 MB of RAM remains free after accounting for the OS and other processes.
Confirmed Facts
- The InnoDB buffer pool holds the majority of data and index pages; its size directly influences read/write I/O.
- On a memory‑constrained host, allocating 50‑60 % of RAM to the buffer pool (when MySQL is the main consumer) prevents the OOM killer and swap usage.
- After changing the setting, verify with
SHOW VARIABLES LIKE 'innodb_buffer_pool_size'; and check RAM usage with free -m or top.
- MySQL 8.0’s
innodb_buffer_pool_instances should be set to 1 or 2 on low‑memory hosts to avoid extra per‑instance overhead.
- The
innodb_flush_log_at_trx_commit variable controls redo log durability: mode 1 flushes on every commit (high I/O), mode 2 writes to the log buffer and flushes once per second (lower I/O), mode 0 behaves like mode 2 but also syncs the OS cache once per second.
Likely Explanation
When the buffer pool is reduced, pages are evicted more frequently, increasing disk reads during traffic bursts. Pairing a smaller pool with innodb_flush_log_at_trx_commit = 2 cuts redo‑log writes by up to 99 % while still providing durability within a second, which is usually acceptable for infrequent bursts. If strict durability is required, keep mode 1, accepting the higher I/O cost.
Concrete Steps
- Determine total RAM:
free -m or cat /proc/meminfo | grep MemTotal.
- Calculate the pool size: 0.5–0.6 × MemTotal (or 0.3–0.4 if other services run).
- Edit the MySQL configuration file (e.g.,
/etc/mysql/mysql.conf.d/mysqld.cnf) and set:
innodb_buffer_pool_size = 512M
innodb_buffer_pool_instances = 1
innodb_flush_log_at_trx_commit = 2
- Restart MySQL:
sudo systemctl restart mysql.
- Verify the change:
mysql -e "SHOW VARIABLES LIKE 'innodb_buffer_pool_size';"
free -m | grep Swap
- Monitor I/O and hit ratio with
iostat -x 1 or mysqltuner.pl to confirm low swap usage and a high buffer‑pool read‑hit ratio.
Diagnostic Question That Could Change the Recommendation
What is the total amount of RAM consumed by the OS and other services on the host? A higher baseline usage would require a smaller buffer pool to keep swap free.
mysql