MySQL InnoDB Configuration Parameters and Optimization Guide

If your MySQL server includes InnoDB support but you do not plan to use it, you can disable it with #skip-innodb to save memory and disk space.

InnoDB caches all data and indexes; a larger innodb_buffer_pool_size reduces disk I/O. For a dedicated database server, set it to about 80% of physical memory, but on 32‑bit systems keep each process under 2‑3.5 GB.

Typical InnoDB file‑system settings:

innodb_data_file_path = ibdata1:1024M:autoextend

Set the number of I/O threads (default 4 on Unix, configurable on Windows):

innodb_file_io_threads = 4

Control concurrency with innodb_thread_concurrency (default 16):

innodb_thread_concurrency = 16

Flush log at transaction commit can be tuned for speed versus durability. Setting it to 2 is a good compromise for most workloads, while 0 gives the highest performance but risks data loss on crash.

innodb_flush_log_at_trx_commit = 2

Log buffer size (default 16 M) should be between 1‑8 M for typical workloads:

innodb_log_buffer_size = 16M

Log file size can be large (e.g., 1024 M) to reduce checkpoint frequency, but larger files increase recovery time:

innodb_log_file_size = 1024M

Number of log files in the group (usually 2‑3) improves performance:

innodb_log_files_in_group = 3

Maximum dirty page percentage controls when InnoDB starts flushing pages; 90 % is a common soft limit:

innodb_max_dirty_pages_pct = 90

Flush method can be set to O_DSYNC for better durability:

innodb_flush_method = O_DSYNC

Lock wait timeout helps resolve deadlocks when using non‑InnoDB engines or LOCK TABLES:

innodb_lock_wait_timeout = 30

For large dumps, enable quick and increase packet size:

max_allowed_packet = 64M

Adjust client timeout as needed (e.g., 10 seconds for a 4 GB server):

wait_timeout = 10

Optionally disable TCP/IP networking with skip_networking if only local connections are required.

skip_networking

Log queries that do not use indexes to help identify inefficient statements:

log-queries-not-using-indexes

These settings together provide a comprehensive approach to optimizing MySQL InnoDB performance while balancing memory usage and data safety.