How do you size innodb_buffer_pool_size and innodb_redo_log_capacity on a dedicated MySQL 8.4 server, and which status counters show either is too small?
answer
- both defaults are tiny
- most of RAM, not all of it
- one capacity, thirty-two files
- checkpoint age against capacity
basics
~20 sGive innodb_buffer_pool_size most of RAM (default 128 MB; often 75 to 80 percent on a dedicated host) and raise innodb_redo_log_capacity from 100 MB to absorb peak write bursts. Buffer pool disk reads, Innodb_buffer_pool_wait_free and in-use redo versus capacity show shortfalls.
solid answer
~40 sBoth defaults suit a laptop: `innodb_buffer_pool_size` is 128 MB and `innodb_redo_log_capacity` is 100 MB. On a dedicated server I give the buffer pool most of memory, leaving room for the roughly 10 percent InnoDB adds for control structures, per-connection buffers and the OS. `innodb_dedicated_server` does this automatically in 8.4, using 75 percent above 4 GB of RAM and a redo capacity of half a gigabyte per logical CPU, up to 16 GB. Both variables are dynamic. I watch `Innodb_buffer_pool_reads` against `Innodb_buffer_pool_read_requests` and `Innodb_buffer_pool_wait_free` for the buffer pool. For redo, I compare `Innodb_redo_log_logical_size`, or the checkpoint age `Innodb_redo_log_current_lsn` minus `Innodb_redo_log_checkpoint_lsn`, with `Innodb_redo_log_capacity_resized`. If in-use redo keeps approaching capacity, InnoDB is forced into aggressive flushing, and that shows up as write-latency spikes.
code
ini · 4 lines[mysqld]
# dedicated 64 GB host
innodb_buffer_pool_size = 48G
innodb_redo_log_capacity = 8Ggo deeper
Recall the two variables, their tiny defaults of 128 MB and 100 MB, and that the buffer pool caches pages while the redo log records changes for recovery.
Explain why the buffer pool gets most but not all of RAM, how online resizing rounds to chunks, and how redo capacity replaced the old log-file variables.
Show how you read buffer pool misses, wait_free and checkpoint age against capacity under peak load, and size redo from the measured write rate rather than a rule of thumb.
Decide whether the fleet uses innodb_dedicated_server or explicit sizes per host class, and own the memory budget that also covers connections and the OS.
## Two settings, two different jobs The **InnoDB buffer pool** is the memory area where InnoDB caches table and index pages. The **redo log** is the on-disk record of changes that lets InnoDB recover pages that had not been written back when the server stopped. How the buffer pool evicts pages and how write-ahead logging works in general are covered elsewhere. The sizing decisions here are MySQL's own. | Variable | MySQL 8.4 default | Dynamic | Controls | |---|---|---|---| | `innodb_buffer_pool_size` | 134217728 (128 MB) | Yes | Memory for cached pages | | `innodb_redo_log_capacity` | 104857600 (100 MB) | Yes | Total disk space for redo log files | Both defaults are far too small for a production server. ## Sizing the buffer pool The manual suggests that on a **dedicated** server you might set the buffer pool to 80 percent of physical memory, then scale back if needed. Several things eat into the rest: - **InnoDB overhead:** the total allocation is roughly 10 percent larger than the configured size. - **Per-connection memory:** sort, join and read buffers multiplied by the number of active connections. - **The operating system** and anything else on the host. If memory runs short the OS starts paging, and a swapping buffer pool is worse than a smaller one. Mechanics that matter in practice: 1. **Online resizing.** `SET GLOBAL innodb_buffer_pool_size = ...` resizes in chunks of `innodb_buffer_pool_chunk_size` (128 MB by default). The size is rounded to a multiple of `innodb_buffer_pool_chunk_size * innodb_buffer_pool_instances`. `Innodb_buffer_pool_resize_status` reports progress. 2. **Instances.** In 8.4, `innodb_buffer_pool_instances` defaults to 1 at or below 1 GiB. Above that it takes the smaller of half the chunk count and a quarter of the logical CPUs, capped at 64. In 8.0 the default was 8, or 1 below 1 GiB. 3. **Warm restarts.** `innodb_buffer_pool_dump_at_shutdown` and `innodb_buffer_pool_load_at_startup` are both `ON`, so a restart reloads the previously hot pages instead of starting cold. **Signals that it is too small:** - `Innodb_buffer_pool_reads` (reads that missed the pool and went to disk) rising fast relative to `Innodb_buffer_pool_read_requests`. - `Innodb_buffer_pool_wait_free` climbing steadily. It counts waits because no clean page was free, and the manual says it should be small when the pool is sized properly. - `Innodb_buffer_pool_pages_free` near zero while disk read latency climbs. Do not count on a query cache to take load off the pool. It was removed in MySQL 8.0, and an old `query_cache_size` line in `my.cnf` stops an 8.4 server from starting with an "unknown variable" error. ## Sizing the redo log In MySQL 8.4 one variable, `innodb_redo_log_capacity`, supersedes `innodb_log_file_size` and `innodb_log_files_in_group`. Both are deprecated and ignored when the capacity is set. InnoDB tries to keep **32 redo files** in the `#innodb_redo` directory, each about 1/32 of the capacity. The capacity can be changed at runtime: InnoDB flushes more or less aggressively until the files match the new size, and `Innodb_redo_log_resize_status` reports progress. Why the size matters: redo can be discarded only after a **checkpoint**, once the dirty pages it covers have been flushed. The distance between the newest redo and the last checkpoint is the **checkpoint age**. When it approaches capacity, InnoDB must flush dirty pages urgently, and writes slow down or stall. A small redo log under a write burst therefore shows up as periodic write-latency spikes and heavy page flushing, even with plenty of free memory. **Signals and sizing method:** - Compare `Innodb_redo_log_logical_size`, the in-use redo, or the checkpoint age (`Innodb_redo_log_current_lsn - Innodb_redo_log_checkpoint_lsn`) with `Innodb_redo_log_capacity_resized`. - Measure redo generated per minute at peak from the change in `Innodb_redo_log_current_lsn`, and size the capacity so the peak burst fits with headroom. - Keep the costs in mind: more disk space, and, as the manual notes for log files, larger logs make crash recovery slower. - `Innodb_log_waits` is a different signal. It counts waits on the in-memory **log buffer** (`innodb_log_buffer_size`, 64 MB by default in 8.4), not on redo capacity. ## Letting MySQL choose: innodb_dedicated_server With `innodb_dedicated_server=ON` (default `OFF`, not dynamic), InnoDB derives both settings when they are not set explicitly: | Detected memory | Buffer pool | |---|---| | Less than 1 GB | 128 MB | | 1 GB to 4 GB | Memory multiplied by 0.5 | | More than 4 GB | Memory multiplied by 0.75 | Redo capacity becomes (logical processors / 2) GB, up to 16 GB. In 8.4 this calculation is CPU-based rather than memory-based, and the option no longer sets `innodb_flush_method`. An explicitly set variable wins, and the server logs a warning that the option was ignored for it. Use it only on hosts or containers that run nothing but MySQL.
- Can you change innodb_buffer_pool_size on MySQL 8.4 without a restart, and what should you expect?Yes. It is dynamic, and InnoDB resizes in `innodb_buffer_pool_chunk_size` units, rounding the target to a multiple of chunk size times `innodb_buffer_pool_instances`. Track `Innodb_buffer_pool_resize_status`. The resize waits for active transactions to finish, and new transactions that need the buffer pool wait while it runs, so do it at a quiet time.
- What does innodb_dedicated_server set in MySQL 8.4, and when is it the wrong choice?It sets `innodb_buffer_pool_size` from detected memory (75 percent above 4 GB) and `innodb_redo_log_capacity` from logical CPUs (CPUs / 2 GB, up to 16 GB). Unlike 8.0, it no longer touches `innodb_flush_method`. It is wrong on a host shared with other services, because it assumes MySQL may use all the memory.
- Why not simply set innodb_redo_log_capacity to its maximum?The maximum is 512 GB of disk. A huge redo log takes space for little gain once peak bursts fit, and larger logs make crash recovery slower because there may be more redo to scan and apply. Size it to the measured peak write rate plus headroom.
- On a dedicated server, give the buffer pool 100 percent of RAM.
- In MySQL 8.4 you resize redo by changing innodb_log_file_size.
- Changing innodb_redo_log_capacity requires a server restart.
- Turn the query cache back on to take load off the buffer pool.
- Innodb_log_waits shows that innodb_redo_log_capacity is too small.
Assumes MySQL 8.4; innodb_redo_log_capacity supersedes the deprecated log-file variables, and 8.4.0 changed the buffer pool instance default and innodb_dedicated_server's redo formula.