TopolearnThese settings decide both throughput and how much data a crash can lose; tuning them blindly is dangerous.
InnoDB reads and writes data in 16 kB pages cached in the buffer pool. Its size, innodb_buffer_pool_size, is the most important setting: on a dedicated server 50–75% of RAM (or set innodb_dedicated_server = ON to size it automatically). A good hit rate means most reads never touch disk. The pool uses an LRU list with a "young" and "old" sublist so a big table scan doesn't evict the hot working set.
Changes modify pages in memory (they become dirty) and are recorded in the redo log, a write-ahead log. At commit only the redo log must reach disk; dirty pages are flushed later in the background. After a crash, InnoDB replays the redo log to recover committed changes. The redo log size is innodb_redo_log_capacity (8.0.30+); too small forces aggressive flushing and stalls under write bursts.
innodb_flush_log_at_trx_commit controls durability:
1(default): write and fsync the redo log at every commit. Full ACID durability.2: write at commit, fsync about once per second. An OS crash or power loss can lose about a second of commits.0: write and fsync about once per second; even a mysqld crash can lose commits.
sync_binlog = 1 (default) does the same for the binlog; keep both at 1 if you can't lose data.
The doublewrite buffer writes pages to a separate area before their real location, protecting against torn (partially written) pages after a crash.
Example
-- my.cnf for a dedicated 64 GB server (starting point)
-- innodb_buffer_pool_size = 44G
-- innodb_redo_log_capacity = 8G
-- innodb_flush_log_at_trx_commit = 1
-- sync_binlog = 1
SELECT @@innodb_buffer_pool_size / 1024 / 1024 / 1024 AS pool_gb,
@@innodb_flush_log_at_trx_commit, @@sync_binlog;
-- buffer pool hit ratio: logical reads vs reads that went to disk
SELECT (1 - d.VARIABLE_VALUE / r.VARIABLE_VALUE) * 100 AS hit_pct
FROM performance_schema.global_status r, performance_schema.global_status d
WHERE r.VARIABLE_NAME = 'Innodb_buffer_pool_read_requests'
AND d.VARIABLE_NAME = 'Innodb_buffer_pool_reads'; -- e.g. 99.97
SELECT * FROM sys.innodb_buffer_stats_by_table LIMIT 5; -- what's cachedRemember
- innodb_buffer_pool_size is the key setting: 50-75% of RAM on a dedicated host
- Commits only wait for the redo log; dirty pages are flushed later
- flush_log_at_trx_commit=1 and sync_binlog=1 for full durability
Watch out for
- Setting innodb_flush_log_at_trx_commit=0 for speed on a database whose data can't be lost.