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.