InnoDB Tuning Reference

01

Buffer Pool Settings

CRITICAL

PARAMETER	DEFAULT	RECOMMENDED
innodb_buffer_pool_size InnoDB's most critical parameter. Caches all table and index data in memory.	128M	70-80% of RAM
innodb_buffer_pool_instances Divides buffer pool into instances to reduce concurrency contention.	8	1 per GB (max 64)
innodb_buffer_pool_chunk_size Buffer pool size must be a multiple of this value.	128M	pool_size / instances aligned
innodb_old_blocks_time Delay before moving blocks to LRU young sublist.	1000ms	1000ms (OLTP) 0 (Batch)
innodb_lru_scan_depth Page cleaner thread scan depth per buffer pool instance.	1024	256-512

CALCULATION EXAMPLE

# Buffer Pool Sizing Rule Total RAM: 64 GB Leave for OS: 6 GB (= 10%) Other MySQL: 4 GB (sort, tmp, connections) innodb_buffer_pool_size = 54 GB (= 84% of available) innodb_buffer_pool_instances = 54 (1 per GB)

04

Transaction & Lock Settings

PARAMETER	DEFAULT	RECOMMENDED
innodb_lock_wait_timeout Timeout in seconds waiting for an InnoDB row lock.	50	30-120
innodb_deadlock_detect Automatic deadlock detection. Disabling under extreme concurrency is debated.	ON	ON
innodb_rollback_on_timeout Rolls back entire transaction on timeout (not just last statement).	OFF	ON (optional)
transaction_isolation READ COMMITTED reduces gap locks. RR recommended for GTID replication.	REPEATABLE-READ	RC (high concurrency)
innodb_autoinc_lock_mode Lock mode used for generating AUTO_INCREMENT values in bulk inserts.	1	2 (interleaved)
innodb_print_all_deadlocks Prints all deadlocks to the error log. Keep enabled for diagnostics.	OFF	ON

06

SHOW ENGINE INNODB STATUS — Reading Guide

BACKGROUND THREAD Flush activity, log sequence number (LSN)

SEMAPHORES Wait counts and mutexes; high values indicate contention

TRANSACTIONS Active transactions, lock waiters, undo log history length

FILE I/O Async read/write thread status & pending operations

INSERT BUFFER AND AHI Change buffer and adaptive hash index statistics

LOG LSN, checkpoint LSN delta (checkpoint_age), flushed pages

BUFFER POOL AND MEMORY Pool size, dirty pages count, hit ratio

ROW OPERATIONS Inserts, updates, deletes, and reads per second

checkpoint_age = log_lsn - checkpoint_lsn → If this delta exceeds 75% of innodb_log_file_size × log_files_in_group, trigger critical alert!

02

Log & Flush Settings

PARAMETER	DEFAULT	RECOMMENDED
innodb_log_file_size Redo log file size. Larger value = less checkpoint I/O overhead.	48M	1-4 GB
innodb_log_buffer_size Redo log buffer size. Increase for large transactions.	16M	64-256 MB
innodb_flush_log_at_trx_commit 1=strict ACID, 2=up to 1s data loss risk on OS crash but 2-10x faster.	1	1 (prod) 2 (perf)
innodb_flush_method Prevents double buffering. Standard recommendation on Linux.	fsync	O_DIRECT
innodb_doublewrite Prevents torn page corruption. Can disable on ZFS / Fusion-io.	ON	ON (prod) OFF (ZFS/Fusion)
innodb_log_files_in_group Deprecated in MySQL 8.0.30+ in favor of dynamic redo log.	2	2 (MySQL 8.0)

▲ innodb_flush_log_at_trx_commit=2 → Max 1 second data loss on hardware crash. Transactions write to OS buffer without syncing to disk.

03

I/O & Thread Settings

PARAMETER	DEFAULT	RECOMMENDED
innodb_io_capacity Target background I/O ops/sec. Lower for HDD, higher for SSD.	200	2000-20000 (SSD)
innodb_io_capacity_max Maximum background I/O ops/sec during heavy flush spikes.	2000	4x io_capacity
innodb_read_io_threads Number of async read I/O threads.	4	4-8
innodb_write_io_threads Number of async write I/O threads.	4	4-8
innodb_purge_threads Background threads dedicated to undo log purge operations.	4	4
innodb_page_cleaners Number of dirty page cleaner threads.	4	= innodb_buffer_pool_instances
innodb_thread_concurrency 0=unlimited/auto. Tune to 2x CPU cores only under severe contention.	0	0 (auto)

08

Quick Checklist & my.cnf Template

PRODUCTION MY.CNF

# /etc/mysql/my.cnf - Production InnoDB Tuning # dbaguide.com · MySQL 8.0/8.4 [mysqld] # -- Buffer Pool innodb_buffer_pool_size = 54G # RAM * 0.75-0.80 innodb_buffer_pool_instances = 54 # 1 per GB innodb_buffer_pool_chunk_size = 1G # -- Log & Flush innodb_log_file_size = 2G innodb_log_buffer_size = 64M innodb_flush_log_at_trx_commit = 1 # ACID (2=perf) innodb_flush_method = O_DIRECT innodb_doublewrite = ON # -- I/O innodb_io_capacity = 4000 # For SSD innodb_io_capacity_max = 16000 innodb_read_io_threads = 8 innodb_write_io_threads = 8 innodb_page_cleaners = 54 # = buffer_pool_instances # -- Transaction & Lock innodb_lock_wait_timeout = 50 innodb_deadlock_detect = ON innodb_print_all_deadlocks = ON transaction_isolation = READ-COMMITTED # -- Monitoring performance_schema = ON innodb_monitor_enable = all

QUICK CHECKLIST

Buffer pool 70-80% of RAM; hit ratio > 99%

instances = 1 per GB, page_cleaners matched

Redo log 1-4 GB; checkpoint_age < 75%

flush_log_at_trx_commit=1 enforced in prod

Linux flush_method=O_DIRECT

SSD io_capacity tuned to 2000+

print_all_deadlocks=ON enabled for triage

Long transactions tracked via INNODB_TRX

Settings requiring restart planned ahead

Values sized for 64 GB RAM / SSD server. Scale proportionally to hardware.

05

Monitoring & Diagnostic Queries

BUFFER POOL UTILIZATION RATIO

SELECT FORMAT(pool_size * 16 / 1024, 0) AS pool_size_MB, FORMAT(database_pages * 16 / 1024, 0) AS used_MB, FORMAT((1 - database_pages/pool_size) * 100, 1) AS free_pct FROM information_schema.INNODB_BUFFER_POOL_STATS;

DIRTY PAGE RATIO

SELECT (SELECT variable_value FROM performance_schema.global_status WHERE variable_name = 'Innodb_buffer_pool_pages_dirty') AS dirty_pages, (SELECT variable_value FROM performance_schema.global_status WHERE variable_name = 'Innodb_buffer_pool_pages_total') AS total_pages;

LONGEST RUNNING TRANSACTIONS

SELECT trx_id, trx_state, trx_started, TIMESTAMPDIFF(SECOND, trx_started, NOW()) AS duration_sec, trx_mysql_thread_id, trx_query FROM information_schema.INNODB_TRX ORDER BY trx_started LIMIT 10;

LATEST DEADLOCK LOG

SHOW ENGINE INNODB STATUS\G -- Inspect the "LATEST DETECTED DEADLOCK" section

07

Adaptive Hash Index & Change Buffer

PARAMETER	DEFAULT
innodb_adaptive_hash_index Builds in-memory hash indexes for frequently accessed pages. Useful for OLTP.	ON
innodb_adaptive_hash_index_parts Number of AHI partitions to reduce contention (max 512).	8
innodb_change_buffering Buffers INSERT/DELETE/PURGE operations. 'all' is usually optimal.	all
innodb_change_buffer_max_size Maximum % of buffer pool allocated to change buffer (max 50).	25
innodb_stats_auto_recalc Automatically recalculates table statistics for query optimizer.	ON
innodb_stats_persistent Persists optimizer statistics on disk (survives restarts).	ON