MySQL Config Generator
my.cnf Config Generator
Enter your server hardware specs and workload type to generate a production-tested MySQL config file. All parameters include calculation formulas and tuning notes.
This generator covers MySQL 5.7 / 8.0 / 8.4 and auto-handles parameter name and default value differences (e.g., 8.0.30+ uses innodb_redo_log_capacity instead of innodb_log_file_size, 8.0.27+ uses replica_* instead of slave_*). Generated configs include detailed comments explaining each parameter's calculation basis, facilitating team code review and ongoing maintenance. A memory budget summary at the bottom helps confirm total memory usage won't exceed physical RAM.
Generator Calculation Logic
To help you understand the decision logic behind generated results, here are the calculation rules for major parameters:
- Buffer Pool: Total RAM * 75% (OLAP uses 80%, under 4GB uses 50%). Result aligned down to 128MB multiples, since buffer_pool_size must be a multiple of chunk_size * instances
- Buffer Pool Instances: Equals buffer pool GB count (max 16), min 1GB per instance
- Redo Log: Allocated by RAM tier โ under 4GB: 1GB, 4-16GB: 2GB, 16-64GB: 4GB, 64GB+: 8GB. Doubled for OLAP with RAM >= 32GB (max 16GB)
- I/O Capacity: HDD 200/800, SSD 2000/4000, NVMe 10000/20000 (io_capacity / io_capacity_max)
- I/O Threads: Half of CPU cores (min 4, max 64). OLAP: read threads = cores, write threads = cores/4
- Per-connection Buffers: OLTP uses minimum (256K sort/join), OLAP uses 4M, Mixed uses 1M
- Flush Policy: Primary uses "dual-1" (flush_log=1, sync_binlog=1); Replica and OLAP use performance mode (flush_log=2, sync_binlog=0)
- Change Buffer: Disabled for SSD/NVMe (set 0 to save buffer pool space), keep default 25% for HDD
- Version Adaptation: 5.7 uses innodb_log_file_size + innodb_log_files_in_group and slave_* parameter names; 8.0+ uses innodb_redo_log_capacity and replica_* names; 8.0+ default collation uses utf8mb4_0900_ai_ci
These rules are based on Oracle official documentation, Percona performance tuning guides, and thousands of production environment validations. They are safe starting points โ they won't cause OOM, data loss, or performance cliffs. But every production environment is unique, so always benchmark and fine-tune based on actual workloads.
Scenario Presets
my.cnf
Key Parameters Explained (Top 30)
These are the 30 most impactful MySQL performance parameters. Each includes a recommended formula, what it does, and why the setting matters.
Memory & Buffer Pool
The InnoDB buffer pool is MySQL's most critical memory area, caching data and index pages. Allocate 70-80% of total RAM on a dedicated DB server. Reserve 20-30% for OS page cache, per-connection memory, and other MySQL buffers. If the server also runs applications, reduce to 50-60%. Buffer pool hit rate should stay above 99%, viewable via SHOW ENGINE INNODB STATUS.
Multiple buffer pool instances reduce internal mutex contention. Each instance manages its own LRU and flush lists independently. When buffer pool >= 8GB, 8-16 instances work best. MySQL 8.0.26+ deprecated this parameter (auto-managed), but 5.7 and earlier 8.0 still require manual setting.
The minimum unit for online buffer pool resizing. buffer_pool_size must be an integer multiple of chunk_size * instances, otherwise MySQL rounds up automatically. On large-memory servers, increase to 256M or 512M to reduce chunk count and management overhead.
Buffer allocated per join operation when no index is available. Setting too large causes memory blowup under high concurrency (max_connections * join_buffer_size). 256K is sufficient for most OLTP workloads; increase to 1-4M for heavy reporting queries. Always prefer adding indexes to fix join performance.
Memory allocated for each sort operation. MySQL allocates the full buffer at once (not incrementally). Setting too large wastes memory since even small sorts allocate the full size. Keep at 256K-2M, only increase when Sort_merge_passes status variable keeps growing.
Maximum size for in-memory temp tables; exceeding this converts to on-disk temp tables. MySQL uses the smaller of the two as the actual limit. Monitor Created_tmp_disk_tables / Created_tmp_tables ratio, increase if over 10%. Can be larger for OLAP. BLOB/TEXT columns always force disk temp tables regardless of this setting.
Redo Log
Unified redo log capacity parameter introduced in MySQL 8.0.30. Larger redo logs reduce checkpoint flush frequency, improving write-heavy performance, but increase crash recovery time. For OLTP workloads, 2-4GB is recommended; write-heavy or OLAP can increase to 8-16GB. Judge if increase is needed by comparing checkpoint age vs max checkpoint age ratio in SHOW ENGINE INNODB STATUS.
Size of each redo log file. Total redo capacity = innodb_log_file_size * innodb_log_files_in_group. Larger files reduce I/O contention but increase recovery time. Rule of thumb: size to hold 1-2 hours of peak write volume. Check hourly delta of Log sequence number during peak load to determine.
In-memory buffer for redo log. Transactions write here first before flushing to disk. Large transactions (bulk INSERTs) benefit from a bigger buffer, reducing redo log file writes. 64M works for most OLTP; increase to 128-256M for heavy BLOB writes or batch operations.
Flush Settings
Controls redo log flushing on each transaction commit. = 1: flush to disk on every commit, safest (full ACID), but highest I/O; = 2: write to OS cache per commit, flush to disk once/second, up to 1 second data loss on crash, ~2-3x performance gain; = 0: write and flush once/second, MySQL crash may lose 1 second of data. Replicas commonly use = 2. Financial systems must use = 1.
Controls binlog sync frequency. = 1 means sync binlog to disk on every commit, combined with innodb_flush_log_at_trx_commit=1 forms the "dual-1" setup for primary-replica consistency. = 0 relies on OS flush, best performance but risks data loss. Primary nodes should use = 1, replicas can use = 0.
Sets I/O method for InnoDB data and log files. O_DIRECT bypasses OS file cache, avoiding double buffering (InnoDB buffer pool + OS page cache), reducing memory waste and swap risk. Nearly all production Linux environments should use O_DIRECT. The only exception is some SAN storage that may need O_DSYNC.
Tells InnoDB the IOPS ceiling for background tasks (dirty page flushing, change buffer merging). Too low causes dirty page accumulation leading to flush storms; too high steals I/O from foreground queries. Benchmark actual IOPS with fio or sysbench, then set io_capacity to 50-75% and max to 2x.
Threads & Connections
Maximum concurrent connections allowed. Each connection consumes ~1-10MB RAM (depending on sort/join/tmp buffer settings), so don't blindly set high. Correct approach: determine actual application concurrency (usually limited by connection pool), then add 20% headroom. If Threads_running rarely exceeds 2x CPU cores, actual concurrency is not high.
Limits threads entering InnoDB kernel simultaneously. = 0 means unlimited (MySQL 8.0 default, works for most cases). When CPU > 32 cores and concurrent connections are very high, setting to CPU cores * 2 reduces context switching overhead. For servers with fewer than 16 cores, keeping 0 is usually optimal.
InnoDB background I/O thread count. read_io_threads handles read-ahead, write_io_threads handles dirty page flushing. 4-core servers use default 4; 8+ cores increase to 8; 16+ cores can go to 16. Read-heavy workloads can set read higher than write, and vice versa. Requires restart to take effect.
Background threads for undo log cleanup. MySQL 8.0 default of 4 works for most cases. Only increase to 8 if History list length keeps growing. More purge threads beyond that don't help and add scheduling overhead.
Number of idle threads kept in the thread cache, avoiding overhead of frequent thread creation/destruction. Monitor Threads_created status โ if more than 1-2 threads/second are created, increase this value. Less important when using connection pooling since connections are long-lived.
I/O Settings
InnoDB data page size. 16K is the decades-optimized default, suitable for most workloads. 4K pages may offer marginal improvement for SSD random-read-heavy loads but increase B+ tree height. 64K pages suit OLAP with lots of sequential scans. Can only be set when initializing the data directory, cannot be changed afterward.
How deep the page cleaner scans the LRU list per iteration. Larger values flush more dirty pages per round, but increase page cleaner CPU cost and lock hold time. SSD/NVMe can go higher since flushing is fast. HDD should keep default 1024 or lower.
Maximum percentage of buffer pool for the change buffer. It caches modifications to non-unique secondary indexes, reducing random disk I/O. Very useful on HDD; on SSD/NVMe where random I/O is cheap, set to 0 to free buffer pool space for data pages. Write-heavy HDD systems with many secondary indexes can increase to 50%.
Doublewrite buffer prevents data corruption from partial page writes. While adding ~5-10% write I/O overhead, protecting data integrity is critical. Only consider disabling on filesystems with atomic writes (ZFS, some Fusion-IO storage). When in doubt, keep it ON. MySQL 8.0.30 introduced innodb_doublewrite_dir to place doublewrite files on faster disk.
Replication Parameters
ROW format records actual per-row changes, safest for replication. STATEMENT records the SQL itself, which can cause primary-replica inconsistency. MIXED is a compromise but still risky. MySQL 8.0 defaults to ROW, strongly recommended not to change. ROW binlogs are larger, but binlog_row_image=MINIMAL significantly reduces size.
GTIDs (Global Transaction Identifiers) simplify replication topology management: auto-positioning, easier failover. In MySQL 8.0 there's almost no reason not to enable GTIDs. enforce_gtid_consistency=ON rejects GTID-unsafe statements (like CREATE TABLE ... SELECT), ensuring replication consistency.
Parallel worker threads for multi-threaded replication. MySQL 8.0.27+ defaults to LOGICAL_CLOCK type (writeset-based parallelization), more efficient than legacy DATABASE type. Set to half to all CPU cores. Use with replica_preserve_commit_order=ON to preserve commit order matching the primary.
Binlog auto-purge time in seconds. Replaces deprecated expire_logs_days (deprecated in 8.0). Balance disk space vs recovery needs: too short and replicas may not catch up after disconnect; too long wastes disk. When running CDC pipelines (Debezium, etc.), ensure retention exceeds the maximum possible consumer lag.
Number of cached open table file descriptors. Each concurrent query needs one descriptor per table involved. Monitor Opened_tables status โ continuous growth means cache is too small. Note each open table uses a small amount of memory and one file descriptor, ensure OS open_files_limit is large enough.
Number of cached table definitions (.frm files or data dictionary entries). MySQL 8.0 uses the data dictionary instead of .frm files; this parameter affects the in-memory dictionary cache. Setting to 1.5x total table count is usually sufficient. Too small causes frequent re-parsing of table structures from disk.
Adaptive Hash Index (AHI) automatically builds in-memory hash indexes for frequently accessed index pages, speeding up point lookups. However, under some write-heavy workloads, AHI maintenance overhead (especially btr_search_latch contention) can become a bottleneck. If you see btr_search waits in the SEMAPHORES section of SHOW ENGINE INNODB STATUS, consider setting to OFF.
Each table uses its own tablespace file (.ibd) instead of the shared system tablespace. Independent tablespaces allow immediate disk reclamation after DROP TABLE or TRUNCATE TABLE, and easier single-table backup/migration. There's almost no reason to disable this, unless managing a huge number of tiny tables (tens of thousands) where file descriptors may become an issue.
Persists index statistics to disk (mysql.innodb_table_stats / innodb_index_stats tables), no re-sampling needed after restart. When off, statistics are re-sampled on every restart or first table open, which can cause execution plan changes after restart. Keep on, and periodically run ANALYZE TABLE to refresh statistics.
Slow query log threshold. Queries exceeding this time are logged. Default 10 seconds is too long, many serious performance issues get missed. Production environments should use 1-2 seconds. For finer-grained analysis, temporarily lower to 0.1 seconds with pt-query-digest. Combine with log_queries_not_using_indexes=ON to catch queries not using indexes.
Log all deadlocks to the error log, not just the last one (SHOW ENGINE INNODB STATUS only shows the most recent deadlock). Must be enabled in production, otherwise intermittent deadlocks are nearly impossible to diagnose. Deadlock logs contain lock holding/waiting info, which is key data for root cause analysis.
Performance Schema provides detailed runtime monitoring data including wait events, lock info, memory usage, statement statistics. MySQL 8.0 defaults to ON, memory overhead ~200-400MB. The sys schema provides friendlier query views. Unless memory is extremely tight, don't disable. When off, many monitoring and diagnostic tools (MySQL Enterprise Monitor, PMM) won't work.
MySQL 8.0 auto-tuning parameter. When enabled, MySQL auto-sets buffer_pool_size (~75% RAM), redo_log_capacity, and log_buffer_size based on detected system RAM. Limitations: (1) only manages 3 parameters, 50+ others still need manual setup; (2) cannot distinguish OLTP/OLAP workloads; (3) in containers may incorrectly detect host memory instead of container limit; (4) cannot optimize I/O parameters for specific disk types. Good for quick-start simple dedicated DB servers, but production environments should still use this generator for fine-tuned config.
Page cleaner thread count, responsible for flushing dirty pages from buffer pool to disk. Ideally should equal buffer_pool_instances count โ one cleaner thread per instance. If set fewer than instances, some instances' dirty pages will queue for flushing, potentially causing uneven flushes. MySQL 8.0 defaults to 4; increase if buffer pool instances exceed 4.
Controls InnoDB mutex spin wait delay (microseconds). When a thread waits for a mutex, it spins for a while before entering the OS wait queue. On high-concurrency systems, increasing this reduces context switches โ letting threads spin longer, and if the lock releases quickly, the expensive context switch is avoided. But too large wastes CPU cycles. Usually only needs tuning when Performance Schema shows heavy mutex waits.
In-memory buffer for binlog events within a transaction. When a transaction's binlog exceeds this size, it spills to a temp file (disk I/O). Monitor Binlog_cache_disk_use status โ if frequent spills, increase. But since it's per-connection, don't set too large. For typical OLTP transactions (few KB binlog each), default 32K is sufficient. Transactions with many row changes (bulk UPDATE/DELETE) may need more.
Maximum size of a single packet. Affects max BLOB/TEXT insert size, stored procedure return result size, and binlog event size in replication. Default 64M works for most cases. Increase to 256M if the application stores large binary files (images, PDFs). Note: Primary and Replica must have the same value, otherwise large events may break replication.
Network read/write timeout. Default 30/60 seconds works for most LAN environments. Cross-datacenter or high-latency networks may need increase. Too small causes large result transfers to timeout mid-stream; too large lets abnormal connections hold resources too long. In replication, Replica's replica_net_timeout (default 60s) controls when Replica considers primary disconnected.
Parameter Verification & Health Check Commands
After deploying config, use these SQL commands to verify key parameters took effect and check runtime health:
-- Check Buffer Pool status
SELECT
FORMAT(@@innodb_buffer_pool_size/1073741824, 1) AS pool_size_gb,
@@innodb_buffer_pool_instances AS instances,
(SELECT ROUND(
(1 - (SELECT VARIABLE_VALUE FROM performance_schema.global_status WHERE VARIABLE_NAME = 'Innodb_buffer_pool_reads')
/ (SELECT VARIABLE_VALUE FROM performance_schema.global_status WHERE VARIABLE_NAME = 'Innodb_buffer_pool_read_requests')
) * 100, 2)
) AS hit_rate_pct;
-- Check redo log wait count (should be 0)
SHOW GLOBAL STATUS LIKE 'Innodb_log_waits';
-- Check temp table disk conversion rate
SELECT
(SELECT VARIABLE_VALUE FROM performance_schema.global_status WHERE VARIABLE_NAME='Created_tmp_disk_tables') AS disk_tmp,
(SELECT VARIABLE_VALUE FROM performance_schema.global_status WHERE VARIABLE_NAME='Created_tmp_tables') AS total_tmp,
ROUND(
(SELECT VARIABLE_VALUE FROM performance_schema.global_status WHERE VARIABLE_NAME='Created_tmp_disk_tables')
/ GREATEST(1, (SELECT VARIABLE_VALUE FROM performance_schema.global_status WHERE VARIABLE_NAME='Created_tmp_tables'))
* 100, 2
) AS disk_pct;
-- Check thread creation rate (should be near 0)
SHOW GLOBAL STATUS LIKE 'Threads_created';
SHOW GLOBAL STATUS LIKE 'Threads_running';
SHOW GLOBAL STATUS LIKE 'Threads_connected';
-- Check table cache hit situation
SHOW GLOBAL STATUS LIKE 'Opened_tables';
SHOW GLOBAL STATUS LIKE 'Table_open_cache_overflows';
-- View current Top 10 wait events
SELECT event_name,
count_star,
ROUND(sum_timer_wait/1e12, 2) AS total_sec,
ROUND(avg_timer_wait/1e9, 2) AS avg_ms
FROM performance_schema.events_waits_summary_global_by_event_name
WHERE count_star > 0 AND event_name NOT LIKE 'idle%'
ORDER BY sum_timer_wait DESC LIMIT 10;
-- Check replication lag (run on replica)
SHOW REPLICA STATUS\G
-- Focus on Seconds_Behind_Source fieldParameter Reference Table (50+)
The table below lists 50+ key MySQL parameters with defaults, recommended values, and brief descriptions. All recommendations assume a dedicated DB server with InnoDB engine.
| Parameter | Default | Recommended | Description |
|---|---|---|---|
| innodb_buffer_pool_size | 128M | RAM*0.7~0.8 | InnoDB data and index page cache, the single most critical MySQL performance parameter. Allocate 70-80% RAM on dedicated DB servers, reduce to 50% for mixed deployments. Hit rate should be > 99% |
| innodb_buffer_pool_instances | 8 (or 1) | MIN(pool_GB,16) | Multiple instances reduce mutex contention, each manages its own LRU/flush lists independently. Min 1GB per instance. Deprecated in 8.0.26+ (auto-managed) |
| innodb_buffer_pool_chunk_size | 128M | 128M~512M | Minimum unit for online buffer pool resize. pool_size must be integer multiple of chunk_size * instances. Increase to 256M-512M on large-memory servers |
| innodb_redo_log_capacity | 100M (8.0.30+) | 2G~16G | Unified redo log capacity (replaces legacy log_file_size * files). Larger values reduce checkpoint frequency but extend crash recovery time. OLTP 2-4G, write-heavy 8-16G |
| innodb_log_file_size | 48M | 512M~2G | Single redo log file size (used in 5.7 and early 8.0). Total capacity = file_size * files_in_group. Should hold 1-2 hours of peak write volume |
| innodb_log_files_in_group | 2 | 2 | Number of redo log files. Keep default 2, increase total capacity by enlarging log_file_size instead |
| innodb_log_buffer_size | 16M | 64M~256M | Redo log memory buffer. Transactions write here before flushing to disk. Large transactions/batch operations benefit from bigger buffer. OLTP 64M, batch/BLOB operations 128-256M |
| innodb_flush_log_at_trx_commit | 1 | 1 (safe) / 2 (perf) | Txn commit flush policy. =1 flush per commit (ACID); =2 flush once/sec (2-3x faster, lose 1s on crash); =0 fastest but riskiest. Financial =1, replicas =2 |
| sync_binlog | 1 | 1 (primary) / 0 (replica) | Binlog sync frequency. =1 sync per commit, with flush_log=1 forms "dual-1" for primary-replica consistency. =0 relies on OS flush, better performance but risky |
| innodb_flush_method | fsync | O_DIRECT | I/O flush method. O_DIRECT bypasses OS cache to avoid double buffering (buffer pool + page cache), reducing memory waste and swap risk. Must-set for Linux production |
| innodb_io_capacity | 200 | 200/2000/10000 | Background I/O ceiling (IOPS). Tells InnoDB the rate limit for dirty page flushing and change buffer merge. HDD 200, SSD 2000, NVMe 10000. Benchmark with fio and set to 50-75% |
| innodb_io_capacity_max | 2000 | io_capacity*2 | Max IOPS ceiling during urgent flushing. Set to 2x io_capacity. Too low causes dirty page buildup leading to synchronous flush storms |
| max_connections | 151 | need+20% | Max concurrent connections. Each consumes 1-10MB (sort/join/tmp buffers). Don't blindly set high, use with connection pooling. Monitor Threads_running, not Threads_connected |
| innodb_thread_concurrency | 0 | 0 / CPU*2 | InnoDB kernel thread concurrency limit. =0 unlimited (best for most cases). Set CPU*2 on >32 cores with high concurrency to reduce context switching |
| innodb_read_io_threads | 4 | 4~16 | InnoDB background read I/O threads, handle read-ahead requests. 4-core use 4, 8+ cores use 8, 16+ cores can go 16. Requires restart |
| innodb_write_io_threads | 4 | 4~16 | InnoDB background write I/O threads, handle dirty page flushing. Set lower for read-heavy, higher for write-heavy. Requires restart |
| innodb_purge_threads | 4 | 4~8 | Undo log cleanup threads. Default 4 is sufficient. Only increase to 8 if History list length keeps growing |
| thread_cache_size | -1 (auto) | 16~100 | Idle thread cache count, avoids frequent creation/destruction overhead. Monitor Threads_created/sec, increase if >2. Less important with connection pooling |
| table_open_cache | 4000 | 2000~10000 | Cached open table file descriptors. Each concurrent query needs one per table involved. Continuous Opened_tables growth means too small |
| table_definition_cache | -1 (auto) | 2000~4000 | Table definition (data dictionary entry) cache count. Set to 1.5x total table count. Too small causes frequent table structure re-parsing |
| join_buffer_size | 256K | 256K~4M | Buffer for joins without indexes (per-connection). OLTP 256K, reporting/OLAP 1-4M. Prefer adding indexes over increasing this |
| sort_buffer_size | 256K | 256K~2M | Memory allocated per sort (allocated at full size immediately). Keep 256K-2M, only increase if Sort_merge_passes keeps growing |
| read_buffer_size | 128K | 128K~1M | Sequential scan buffer (per-connection). Increase for OLAP with many full table scans. Does not affect index lookups |
| read_rnd_buffer_size | 256K | 256K~2M | Random read buffer used by Multi-Range Read optimization. Used when reading rows by primary key after sorting |
| tmp_table_size | 16M | 64M~256M | In-memory temp table limit, converts to disk when exceeded. MySQL uses the smaller of this and max_heap_table_size. Monitor Created_tmp_disk_tables ratio < 10% |
| max_heap_table_size | 16M | = tmp_table_size | Max size for MEMORY engine tables and internal temp tables. Must match tmp_table_size, otherwise the smaller value takes effect |
| binlog_format | ROW | ROW | Binlog format. ROW records actual row changes, safest for replication. STATEMENT may cause primary-replica inconsistency. Don't change the default |
| binlog_row_image | FULL | MINIMAL / FULL | ROW format row image mode. FULL records complete rows, MINIMAL only changed columns. MINIMAL reduces binlog size 50-90% but some CDC tools require FULL |
| binlog_expire_logs_seconds | 2592000 | 604800~2592000 | Binlog auto-purge time in seconds. Replaces deprecated expire_logs_days. Balance disk space vs replica catch-up / CDC consumer lag requirements |
| gtid_mode | OFF | ON | Global Transaction Identifiers. Simplifies replication topology management, auto-positioning, easier failover. Almost no reason not to enable in MySQL 8.0 |
| enforce_gtid_consistency | OFF | ON | Enforce GTID consistency. Rejects GTID-unsafe statements (like CREATE TABLE ... SELECT). Must be enabled alongside GTID mode |
| replica_parallel_workers | 4 (8.0.27+) | CPU/2~CPU | Parallel worker threads for multi-threaded replication. LOGICAL_CLOCK type is more efficient than DATABASE. Use with preserve_commit_order=ON |
| replica_preserve_commit_order | ON (8.0.27+) | ON | Ensures replica transaction commit order matches primary. Must be enabled for multi-threaded replication, otherwise inconsistent intermediate states may be visible |
| innodb_adaptive_hash_index | ON | ON / OFF | Adaptive hash index. Auto-builds hash index for hot index pages to speed up point lookups. May cause btr_search_latch contention bottleneck on write-heavy workloads, consider disabling |
| innodb_change_buffer_max_size | 25 | 25/0 | Change buffer percentage of buffer pool. Caches non-unique secondary index modifications to reduce random I/O. HDD keep 25%, SSD/NVMe set 0 to save buffer pool space |
| innodb_file_per_table | ON | ON | Separate .ibd tablespace file per table. Disk space reclaimed immediately after DROP/TRUNCATE TABLE. Almost no reason to disable |
| innodb_doublewrite | ON | ON | Doublewrite buffer prevents partial page write corruption. Adds ~5-10% write I/O but protects data integrity. Only consider disabling on ZFS/atomic-write storage |
| innodb_stats_persistent | ON | ON | Persist index statistics to disk tables. No re-sampling needed after restart, avoids execution plan changes after restart. Periodically run ANALYZE TABLE to refresh |
| innodb_page_size | 16K | 16K | InnoDB data page size. 16K is the decades-optimized default. Can only be set when initializing the data directory, cannot be changed afterward |
| innodb_lru_scan_depth | 1024 | 1024~2048 | Page cleaner LRU list scan depth per iteration. NVMe can use 2048, HDD keep 1024. Too large increases lock hold time |
| innodb_print_all_deadlocks | OFF | ON | Log all deadlocks to error log. Must enable in production, otherwise intermittent deadlocks are undiagnosable. SHOW ENGINE INNODB STATUS only shows the last one |
| long_query_time | 10 | 1~2 | Slow query threshold (seconds). Default 10s is too long, 2-5s severe performance issues get missed. Set 1-2s in production, analyze with pt-query-digest |
| slow_query_log | OFF | ON | Enable slow query log. Without it, performance optimization is flying blind. Minimal disk overhead but enormous diagnostic value |
| log_queries_not_using_indexes | OFF | ON | Log queries not using indexes to slow query log. Use with log_throttle_queries_not_using_indexes to throttle and prevent log explosion |
| performance_schema | ON | ON | Provides detailed monitoring: wait events, lock info, memory usage, statement statistics. Memory overhead ~200-400MB. When off, tools like PMM/Enterprise Monitor won't work |
| character_set_server | utf8mb4 | utf8mb4 | Server default charset. utf8mb4 fully supports Unicode including emoji (4-byte UTF-8). utf8mb3 only supports 3-byte BMP characters |
| collation_server | utf8mb4_0900_ai_ci | utf8mb4_0900_ai_ci | Server collation. 8.0 defaults to 0900_ai_ci based on Unicode 9.0, more accurate than general_ci. 5.7 uses general_ci |
| innodb_online_alter_log_max_size | 128M | 256M~1G | Max log size for recording concurrent DML during online DDL. If DML writes exceed this during large table ALTER, the DDL fails. Set 1G for high-write tables |
| innodb_open_files | -1 (auto) | table_open_cache | Max number of .ibd files InnoDB can have open simultaneously. Should match table_open_cache. Ensure OS open_files_limit is large enough |
| innodb_autoinc_lock_mode | 2 | 2 | Auto-increment lock mode. =2 (interleaved) no table lock for bulk inserts, best performance. Safe with ROW binlog format. =1 (consecutive) for legacy compatibility |
| innodb_deadlock_detect | ON | ON | Automatic deadlock detection. Keep enabled. In extremely high concurrency hot-row scenarios, can switch to innodb_lock_wait_timeout instead (disable detection), but generally not recommended |
| binlog_transaction_dependency_tracking | COMMIT_ORDER | WRITESET | Transaction dependency tracking method. WRITESET analyzes transaction conflicts via write sets, allowing more parallel replay, significantly improving replica replication speed |
| innodb_numa_interleave | OFF | ON (NUMA) | Enable memory interleave on NUMA architecture servers. Prevents buffer pool from allocating only on one NUMA node causing cross-node memory access latency. Must-set on multi-socket CPU servers |
| innodb_use_native_aio | ON (Linux) | ON | Use Linux native async I/O (libaio) instead of simulated AIO. Significantly better performance than sync I/O on modern Linux kernels. Requires libaio package |
| innodb_checksum_algorithm | crc32 | crc32 | Data page checksum algorithm. crc32 uses hardware acceleration (SSE4.2), several times faster than legacy innodb algorithm with stronger detection capability |
| innodb_dedicated_server | OFF | OFF / ON | Auto-tune buffer_pool/redo_log/log_buffer. Only manages 3 params, enable for simple dedicated servers |
| innodb_page_cleaners | 4 | = pool_instances | Dirty page flush threads. Should equal buffer_pool_instances for even flushing |
| innodb_spin_wait_delay | 6 | 6~50 | Mutex spin wait delay (microseconds). Increase with heavy mutex waits under high concurrency |
| max_allowed_packet | 64M | 64M~256M | Max single packet size. Affects BLOB inserts and replication. Must match on Primary and Replica |
| net_read_timeout | 30 | 30 | Network read timeout (seconds). Increase for cross-IDC environments |
| net_write_timeout | 60 | 60 | Network write timeout (seconds). Prevents premature disconnect during large result transfers |
| binlog_cache_size | 32K | 32K~1M | Txn binlog memory buffer (per-connection). Monitor Binlog_cache_disk_use |
| skip_name_resolve | OFF | ON | Skip DNS reverse lookup for faster connections. GRANT must use IP |
| log_throttle_queries_not_using_indexes | 0 | 60 | Max queries without indexes logged per minute, prevents log explosion |
| wait_timeout | 28800 | 600 | Idle connection timeout (seconds). Default 8 hours too long, recommend 600-3600 |
| max_connect_errors | 100 | 100000 | Consecutive connection error ban threshold. Default 100 too low, legitimate clients may get blocked |
Top 10 Common Misconfigurations
These are the most common MySQL configuration mistakes in production, each capable of causing severe performance issues or data safety risks.
This is the most common and highest-impact mistake. Using the default 128M means nearly all reads go to disk I/O, while the 64GB server has vast amounts of wasted memory. Buffer pool should be 70-80% of total RAM so hot data stays in memory.
Each connection consumes memory (sort_buffer + join_buffer + tmp_table, etc.), 10000 connections may consume 10-100GB RAM. More importantly, when Threads_running exceeds 2-3x CPU cores, context switching overhead increases dramatically, slowing all queries. The correct approach is using connection pooling (ProxySQL / HikariCP) to limit actual concurrency.
Not enabling slow query log is flying blind โ you don't know which queries are bottlenecks. The default 10-second threshold is too high; many 2-5 second queries (already disastrous for user experience) won't be captured. Set to 1 second and regularly analyze with pt-query-digest as the foundation for continuous optimization.
Replica data can be re-synced from the primary, so it doesn't need the strong durability guarantee of "dual-1". Using =2/0 on replicas can improve replication replay speed 2-5x, reducing replication lag. Note: if the replica also handles writes (failover scenarios), switch back to "dual-1" after promotion.
With fsync, data is cached in both InnoDB buffer pool and OS page cache, wasting memory. More dangerous: when buffer pool uses 70%+ RAM, the OS still tries to cache the same data, potentially causing memory exhaustion and swap storms. O_DIRECT bypasses OS cache, letting InnoDB fully manage memory.
These buffers are per-connection. 200 connections * 256M * 2 = 100GB memory needed, far exceeding server RAM. MySQL allocates sort_buffer at full size immediately (not on-demand), so even sorting 1KB of data allocates 256M. Keep in the 256K-4M range.
Redo log too small causes frequent checkpoints โ InnoDB must urgently flush dirty pages when the redo log fills up, causing "flush storms" and performance spikes. Write-heavy workloads are especially sensitive. Larger redo logs allow more dirty pages to accumulate in memory, and background threads can flush them evenly.
io_capacity=200 is the default designed for HDD. Using this on NVMe SSD (capable of 500K-1M IOPS) tells InnoDB to flush at HDD speed, causing dirty page ratio to keep climbing until synchronous flushes block foreground queries. Set according to actual disk capability.
MyISAM lacks transactions, row-level locking, and crash recovery โ writes lock the entire table, and data may corrupt on crash. MySQL 8.0 migrated all system tables to InnoDB. Except rare full-text search cases (FULLTEXT is supported in InnoDB since 5.6), there's no reason to use MyISAM.
By default MySQL performs DNS reverse lookup for each new connection. If the DNS server is slow or unreachable, connection establishment delays several seconds or times out. When enabled, MySQL only uses IP addresses for privilege matching, faster connections. Note: after enabling, GRANT statements must use IPs instead of hostnames.
Real-World Sizing Examples
Below are five typical production scenarios with configuration highlights and calculation processes, showing how to adjust generator output based on actual business needs.
Scenario 1: Startup Web Application
Specs: 4GB RAM / 2 vCPU / SSD / OLTP / 50 concurrent users / standalone
This is the most resource-constrained scenario. Buffer pool set to 2GB (50% RAM instead of 75%, since small-memory servers need more OS headroom). Per-connection buffers at minimum to save memory. max_connections set to 60 (50 users + 20% headroom). Redo log at 1GB โ sufficient for a small app with modest writes. Key parameter: innodb_flush_log_at_trx_commit=1 keeps safe default since performance difference is negligible at this scale.
Memory budget calculation: Global ~2.4GB (buffer pool 2GB + log buffer 32MB + misc 300MB), per-connection ~60 * 2MB = 120MB, total ~2.5GB / 4GB = 63%, within safe range. Special note: at this scale, performance_schema's 200-400MB overhead is significant โ if memory is extremely tight and advanced monitoring isn't needed, consider disabling to save ~5-10% total memory.
Scenario 2: E-Commerce Platform Primary
Specs: 64GB RAM / 16 vCPU / NVMe / OLTP / 500 max connections (via HikariCP connection pool) / Primary + 2 Replicas
Buffer pool 48GB (75%), 16 instances. NVMe disk io_capacity=10000. "Dual-1" config to ensure order data safety. GTID + ROW binlog enabled. Binlog retention 14 days (CDC pipeline needs sufficient catch-up time). binlog_row_image=MINIMAL to reduce binlog size (order tables have many columns but only a few change per update). innodb_print_all_deadlocks=ON is a must โ inventory deduction during sales peaks easily causes deadlocks. Monitoring focus: Threads_running peak, replication lag, deadlock frequency.
Scenario 3: Analytics / Reporting Database
Specs: 128GB RAM / 32 vCPU / NVMe / OLAP / 30 concurrent analysts / replicated from primary
Buffer pool 100GB (80%) โ OLAP queries scan large amounts of data, cache as much as possible. join_buffer_size=4M and sort_buffer_size=4M (analytics queries with heavy joins and sorts). tmp_table_size=256M (complex aggregations easily produce large temp tables). read_buffer_size=1M (frequent full table scans benefit from larger read buffer). innodb_flush_log_at_trx_commit=2 and sync_binlog=0 โ as a replica, strong durability isn't needed. read_only=ON and super_read_only=ON to prevent accidental writes. Redo log at 16GB โ incoming write volume from primary can be large.
Scenario 4: SaaS Multi-Tenant Database
Specs: 32GB RAM / 8 vCPU / SSD / Mixed / 2000 max connections (via ProxySQL multiplexing) / Primary
Multi-tenant scenario has unique challenges: many databases and tables (one schema or table set per tenant). table_open_cache needs to be 10000+. table_definition_cache also needs increase. max_connections=2000 is ProxySQL frontend connections, but ProxySQL-to-MySQL backend connections are typically only 50-100 โ so per-connection buffers aren't an issue. Buffer pool 24GB (75%), noting that buffer pool needs to cache hot data from all tenants. innodb_adaptive_hash_index=OFF may help โ many different tables and query patterns can cause frequent AHI rebuilds, increasing overhead.
Scenario 5: Log / Time-Series Write-Heavy
Specs: 64GB RAM / 16 vCPU / NVMe / OLTP (write-heavy) / 100 connections / standalone
The key for write-heavy scenarios is maximizing write throughput. innodb_flush_log_at_trx_commit=2 is acceptable โ losing 1 second of log data is usually tolerable. innodb_log_buffer_size=256M โ heavy writes benefit from larger log buffer. Redo log at 8-16GB โ fewer checkpoints make writes smoother. innodb_change_buffer_max_size=0 โ disable change buffer on NVMe, direct writes are more efficient. Write threads innodb_write_io_threads=16 maxed out. Consider table partitioning (by day/week) with ALTER TABLE ... DROP PARTITION for efficient old data removal instead of DELETE. binlog_row_image=MINIMAL to reduce binlog I/O.
If replication or CDC isn't needed, consider disabling binlog entirely (skip-log-bin) for an additional 30-50% write performance boost. Disabling binlog means point-in-time recovery is impossible โ ensure a regular full backup solution (e.g., daily xtrabackup) is in place.
Scenario Comparison Summary
| Parameter | Small Web | E-Commerce | Analytics | Multi-Tenant | Write-Heavy |
|---|---|---|---|---|---|
| buffer_pool | 2G (50%) | 48G (75%) | 100G (80%) | 24G (75%) | 48G (75%) |
| flush_log | 1 | 1 | 2 | 1 | 2 |
| sync_binlog | 1 | 1 | 0 | 1 | 0 |
| sort_buffer | 256K | 256K | 4M | 256K | 256K |
| io_capacity | 2000 | 10000 | 10000 | 2000 | 10000 |
| redo_log | 1G | 4G | 16G | 2G | 8-16G |
| change_buffer | 0 | 0 | 0 | 0 | 0 |
Cloud & Managed MySQL Configuration Notes
In managed MySQL services like AWS RDS, Google Cloud SQL, and Azure Database for MySQL, many parameters cannot be directly modified or behave differently. Below are key differences across major cloud platforms.
AWS RDS / Aurora
- Config managed via Parameter Groups, not direct my.cnf editing. Divided into "static" (require reboot) and "dynamic" (immediate) parameters
innodb_buffer_pool_sizedefaults to 75% of instance memory. RDS auto-sets based on instance type, usually no manual adjustment neededinnodb_flush_method: RDS fixes this to O_DIRECT, cannot be changedinnodb_file_per_table: RDS enforces ON, cannot be disabled- Aurora's storage layer is completely different โ shared distributed storage replaces local disk, redo log managed by storage layer,
innodb_log_file_sizeandinnodb_io_capacityparameters have no effect - Aurora's
innodb_flush_log_at_trx_commitdefaults to 1 and should not be changed โ Aurora's 6-way write replication already provides durability guarantee
Alibaba Cloud RDS
- Modified via the "Parameter Settings" page in the console, supports parameter templates for batch application to multiple instances
innodb_buffer_pool_sizedefaults to 60-70% of instance spec memory, varies by spec- Some parameters have different value ranges in "Basic" vs "High Availability" editions. For example, Basic edition has lower
max_connectionsceiling - Binlog retention defaults to 18 hours (via
loose_expire_logs_hours), much shorter than the 7-day recommendation for self-managed environments
Google Cloud SQL
- Configured via Database Flags, set on the instance detail page
- Some key parameters (like
innodb_flush_method,skip_name_resolve) cannot be modified innodb_buffer_pool_sizeauto-set by instance memory, can be overridden via flag- Auto-storage increase feature may affect I/O performance โ brief performance dip during auto-expansion when storage is nearly full
General advice: Regardless of which cloud platform's managed MySQL you use, first consult that platform's "non-modifiable parameters" and "parameter value ranges" documentation. The config generated here serves as a reference baseline โ compare modifiable parameters against generated results, identify parameters needing adjustment, and set them in Parameter Groups / Database Flags.
OS-Level Companion Tuning
MySQL config doesn't exist in isolation โ OS kernel parameters directly affect database performance. Below are the most important companion settings on Linux systems.
File Descriptor Limits
# /etc/security/limits.conf
mysql soft nofile 65535
mysql hard nofile 65535
mysql soft nproc 65535
mysql hard nproc 65535
# Or set in systemd service file:
# /etc/systemd/system/mysqld.service.d/override.conf
[Service]
LimitNOFILE=65535
LimitNPROC=65535MySQL uses one file descriptor per open .ibd file. table_open_cache = 4000 means at least 4000+ descriptors needed. Default Linux limit is usually 1024, far too low. Setting 65535 covers most scenarios.
Virtual Memory & Swap
# /etc/sysctl.conf or /etc/sysctl.d/99-mysql.conf
# Reduce swap tendency (don't completely disable swap)
vm.swappiness = 1
# Dirty page ratio threshold โ start writeback above this
vm.dirty_ratio = 20
vm.dirty_background_ratio = 5
# Max memory map areas when using address randomization
vm.max_map_count = 262144swappiness=1 tells the kernel to avoid swap as much as possible, but allows swapping under extreme memory pressure rather than OOM killing. Setting to 0 may make OOM killer more aggressive on some kernel versions. dirty_ratio and dirty_background_ratio control OS-level dirty page writeback โ when used with innodb_flush_method=O_DIRECT, these mainly affect binlog and redo log file I/O.
I/O Scheduler
# Check current scheduler
cat /sys/block/sda/queue/scheduler
# SSD/NVMe use none (noop) or mq-deadline
echo "none" > /sys/block/sda/queue/scheduler
# Persistent โ via udev rule
# /etc/udev/rules.d/60-ioscheduler.rules
ACTION=="add|change", KERNEL=="sd*", ATTR{queue/scheduler}="none"
ACTION=="add|change", KERNEL=="nvme*", ATTR{queue/scheduler}="none"SSD/NVMe don't need I/O scheduler seek optimization (no head), use none/noop to reduce I/O stack overhead. HDD should use mq-deadline or cfq. Wrong scheduler choice under high I/O pressure can cause 10-20% performance loss.
Transparent Huge Pages (THP)
# Disable THP (officially recommended by MySQL)
echo never > /sys/kernel/mm/transparent_hugepage/enabled
echo never > /sys/kernel/mm/transparent_hugepage/defrag
# Persistent โ set in /etc/rc.local or systemd serviceTHP can cause memory allocation latency spikes (stalls from defragmentation) and unpredictable memory usage under database workloads. MySQL, Oracle, MongoDB, and PostgreSQL all recommend disabling THP. This is a basic setting every database server should have.
NUMA
On multi-socket CPU servers, NUMA topology causes uneven memory access latency. Two solutions:
- Option A: Set
innodb_numa_interleave=ON(recommended), distributes buffer pool evenly across all NUMA nodes - Option B: Start MySQL with
numactl --interleave=all mysqld, similar effect but applies to all memory allocations
No need to worry about this on single-socket CPU servers. Check system NUMA topology with numactl --hardware.
Version-Specific Notes
MySQL 5.7, 8.0, and 8.4 have important configuration differences. When upgrading or migrating, pay special attention to the following changes.
MySQL 5.7 → 8.0
- query_cache fully removed:
query_cache_typeandquery_cache_sizeno longer work. Query Cache was proven to be a performance bottleneck under multi-core high concurrency in 5.7 (global mutex), 8.0 removed it entirely. Use ProxySQL's query cache or application-layer Redis/Memcached instead. - Default charset changed to utf8mb4: 5.7 defaults to latin1, 8.0 defaults to utf8mb4. Collation changed from utf8mb4_general_ci to utf8mb4_0900_ai_ci (Unicode 9.0 based, more accurate but slightly slower).
- Default auth plugin changed to caching_sha2_password: May cause old client/driver connection failures. For legacy compatibility, set
default_authentication_plugin=mysql_native_password. - binlog_format default changed to ROW: 5.7 defaults to STATEMENT, 8.0 defaults to ROW. This is a correct improvement, ROW format is safer for replication.
- Data dictionary replaces .frm files: 8.0 uses InnoDB data dictionary for table metadata, no more .frm files. DDL operations become atomic โ crashes won't leave half-finished table structures.
- SET PERSIST: New dynamic persistence command, changes auto-written to mysqld-auto.cnf, survive restarts. No longer need to manually edit my.cnf.
- innodb_dedicated_server: New parameter (default OFF), when enabled MySQL auto-tunes buffer_pool_size, redo_log_capacity, and log_buffer_size based on system memory. Good for simple dedicated DB servers.
MySQL 8.0.x Important Minor Version Changes
- 8.0.26:
innodb_buffer_pool_instancesdeprecated, InnoDB auto-manages partitioning. Legacyslave_*parameter names deprecated, usereplica_*instead. - 8.0.27: Multi-threaded replication default type changed to LOGICAL_CLOCK,
replica_parallel_workersdefault changed to 4. - 8.0.30: Introduced
innodb_redo_log_capacityunified parameter replacinginnodb_log_file_size+innodb_log_files_in_group, with online resize support. Addedinnodb_doublewrite_dirandinnodb_doublewrite_pages. - 8.0.34:
mysql_native_passwordauth plugin marked as deprecated. Generates warnings but still usable.
MySQL 8.0 → 8.4 (LTS)
- mysql_native_password disabled by default: The plugin is not loaded by default in 8.4. For environments needing legacy client compatibility, add
mysql-native-password=ONto my.cnf. - Multi-threaded replication becomes default:
replica_parallel_workersdefault increased to 4,replica_parallel_type=LOGICAL_CLOCKandreplica_preserve_commit_order=ONbecome defaults. - binlog_transaction_dependency_tracking removed: 8.4 automatically uses WRITESET dependency tracking, no manual config needed.
- innodb_buffer_pool_in_core_file defaults to OFF: Core dump files no longer include buffer pool data, significantly reducing dump file size.
- Long-Term Support (LTS): 8.4 is MySQL's first LTS release, receiving 5 years Premier Support + 3 years Extended Support. Recommended for new projects to use 8.4 directly.
Upgrade tip: Before upgrading from 5.7 to 8.0, use
mysqlcheck --all-databases --check-upgradeand MySQL Shell'sutil.checkForServerUpgrade()to check compatibility issues. The upgrade path must be 5.7 → 8.0 → 8.4, major versions cannot be skipped.
Configuration Tuning Workflow
Generating the initial config is just step one. Below is a production-proven systematic tuning workflow to help you optimize from baseline to optimal state.
Step 1: Establish Baseline
Use this generator to create the initial config, deploy to a test environment. Run sysbench or replay production traffic (capture with pt-query-digest --type tcpdump + replay with pt-log-player) to establish a performance baseline. Record key metrics: QPS, TPS, P95/P99 latency, Threads_running peak, Buffer pool hit rate.
Step 2: Identify Bottlenecks
Use the following commands and tools to identify current bottlenecks:
-- View InnoDB status (buffer pool, locks, I/O)
SHOW ENGINE INNODB STATUS\G
-- View global status variables (focus on deltas)
SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_read%';
SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_wait_free';
SHOW GLOBAL STATUS LIKE 'Innodb_log_waits';
SHOW GLOBAL STATUS LIKE 'Threads_%';
SHOW GLOBAL STATUS LIKE 'Created_tmp_disk_tables';
SHOW GLOBAL STATUS LIKE 'Sort_merge_passes';
-- Top 10 wait events via Performance Schema
SELECT event_name, count_star, sum_timer_wait/1e12 AS total_sec
FROM performance_schema.events_waits_summary_global_by_event_name
WHERE count_star > 0
ORDER BY sum_timer_wait DESC LIMIT 10;
-- View lock waits
SELECT * FROM sys.innodb_lock_waits\GStep 3: Adjust One at a Time
Based on bottlenecks identified in step 2, adjust only one parameter at a time, then re-run benchmarks to compare. Common tuning priority:
- Buffer pool hit rate < 99%: increase
innodb_buffer_pool_size(if RAM allows) - Innodb_log_waits > 0: increase
innodb_log_buffer_size - Innodb_buffer_pool_wait_free > 0: increase
innodb_io_capacityor enlarge buffer pool - Created_tmp_disk_tables ratio > 10%: increase
tmp_table_size/max_heap_table_size, or optimize queries to avoid temp tables - Sort_merge_passes keeps growing: moderately increase
sort_buffer_size(but no more than 4M) - Threads_running frequently > CPU*2: use connection pooling to limit concurrency, or set
innodb_thread_concurrency
Step 4: Memory Budget Validation
After modifying config, verify total memory usage won't exceed physical RAM. Here's a rough formula for MySQL memory consumption:
# MySQL Memory Usage Estimate
Global Memory:
innodb_buffer_pool_size # largest consumer
+ innodb_log_buffer_size
+ key_buffer_size # MyISAM (usually small)
+ query_cache_size # 5.7 only
+ performance_schema # ~200-400MB
+ other global buffers ~100-300MB
Per-Connection Memory (worst case):
max_connections * (
sort_buffer_size
+ join_buffer_size
+ read_buffer_size
+ read_rnd_buffer_size
+ tmp_table_size # worst case
+ thread_stack (512K)
+ net_buffer_length (16K)
)
Total should not exceed 90% of physical RAM, leave 10% for OS and other processes.Important: The "per-connection memory" in the formula above is the theoretical maximum โ not all connections simultaneously perform sorts and joins. Actual peak memory is usually global memory + max_connections * 1-5MB. Use
performance_schema.memory_summary_global_by_event_nameto see actual memory allocation.
Appendix: sysbench Benchmarking Quick Start
The following commands help you quickly establish a performance baseline and verify config changes:
# Install sysbench
# Ubuntu/Debian:
apt-get install sysbench
# CentOS/RHEL:
yum install sysbench
# Prepare test data (10 tables, 1M rows each)
sysbench /usr/share/sysbench/oltp_read_write.lua \
--mysql-host=127.0.0.1 --mysql-port=3306 \
--mysql-user=root --mysql-password='' \
--mysql-db=sbtest \
--tables=10 --table-size=1000000 \
prepare
# Run OLTP read-write test (64 threads, 120 seconds)
sysbench /usr/share/sysbench/oltp_read_write.lua \
--mysql-host=127.0.0.1 --mysql-port=3306 \
--mysql-user=root --mysql-password='' \
--mysql-db=sbtest \
--tables=10 --table-size=1000000 \
--threads=64 --time=120 --report-interval=10 \
run
# Run read-only test
sysbench /usr/share/sysbench/oltp_read_only.lua \
--mysql-host=127.0.0.1 --mysql-port=3306 \
--mysql-user=root --mysql-password='' \
--mysql-db=sbtest \
--tables=10 --table-size=1000000 \
--threads=64 --time=120 --report-interval=10 \
run
# Run write-only test
sysbench /usr/share/sysbench/oltp_write_only.lua \
--mysql-host=127.0.0.1 --mysql-port=3306 \
--mysql-user=root --mysql-password='' \
--mysql-db=sbtest \
--tables=10 --table-size=1000000 \
--threads=64 --time=120 --report-interval=10 \
run
# Cleanup test data
sysbench /usr/share/sysbench/oltp_read_write.lua \
--mysql-host=127.0.0.1 --mysql-port=3306 \
--mysql-user=root --mysql-password='' \
--mysql-db=sbtest \
--tables=10 cleanupKey output metrics explained:
- transactions (TPS): transactions per second, primary performance metric
- queries (QPS): queries per second, includes all SQL within transactions
- latency (avg/P95/P99): latency distribution, P99 better reflects actual user experience than average
Testing methodology: After each config change, run 3 times with identical parameters and take the median. Before testing, do a warmup run (30 seconds, discard results) to ensure buffer pool is warm. Increment thread count from low to high (8/16/32/64/128) and plot throughput curve โ the inflection point is optimal concurrency.
Step 5: Continuous Monitoring
After deployment, use monitoring tools to continuously track key metrics. Recommended tools:
- Percona Monitoring and Management (PMM) โ Free and open source, based on Prometheus + Grafana, provides MySQL-specific dashboards
- MySQL Enterprise Monitor โ Oracle's official commercial monitoring tool with built-in Query Analyzer and automatic recommendations
- pt-mysql-summary โ Percona Toolkit CLI quick diagnostic tool, one command outputs a complete MySQL status summary
- sys schema โ Built into MySQL 8.0, provides friendly query views over Performance Schema. E.g.,
SELECT * FROM sys.schema_index_statistics;
Periodically (monthly or quarterly) re-evaluate your configuration: as data volume grows, query patterns change, and hardware is upgraded, the optimal configuration changes too.
Configuration Change Log Template
Teams should maintain a configuration change log, recording the following for each parameter adjustment:
# Configuration Change Log โ Example
Date: 2025-03-15
Changed by: DBA Smith
Parameter: innodb_buffer_pool_size
Old value: 32G
New value: 48G
Reason: Buffer pool hit rate dropped from 99.2% to 97.8%, physical reads
(Innodb_buffer_pool_reads) increased to 500+/sec. Dataset grew to 40GB
exceeding old buffer pool size.
Validated: Applied on replica-2 and observed for 24 hours
Result: Hit rate recovered to 99.95%, P99 latency dropped from 28ms to 12ms
Rollback: SET GLOBAL innodb_buffer_pool_size = 34359738368; -- 32GBRecommended Monitoring Alert Thresholds
Complement continuous monitoring with these alert thresholds to catch config issues early:
| Metric | Warning | Critical | Possible Cause |
|---|---|---|---|
| Buffer pool hit rate | < 99% | < 95% | buffer_pool_size too small / dataset growth |
| Threads_running | > CPU*2 | > CPU*4 | Slow queries / lock waits / connection pool misconfigured |
| Replication lag | > 10s | > 60s | Insufficient parallel workers / large transactions / I/O bottleneck |
| Innodb_log_waits | > 0 | sustained > 0 | innodb_log_buffer_size too small |
| Disk tmp table ratio | > 10% | > 25% | tmp_table_size too small / queries using BLOB/TEXT |
| Connections used % | > 80% | > 95% | max_connections too small / connection leak / slow query buildup |
| Innodb_row_lock_waits | > 100/s | > 1000/s | Hot row contention / large transactions / missing indexes |
Pre-Launch Configuration Checklist
Before deploying new config to production, check each item below. Every item is based on real production incident lessons.
Data Safety
- innodb_flush_log_at_trx_commit must be 1 on Primary nodes (unless explicitly accepting 1-second data loss risk)
- sync_binlog must be 1 on Primary nodes (pairs with above for "dual-1")
- innodb_doublewrite keep ON (unless using ZFS or atomic-write storage)
- Confirm binlog_expire_logs_seconds is long enough to cover maximum possible replica disconnect time and CDC consumer lag
- Confirm backup solution (xtrabackup / mysqldump / mysqlpump) still works correctly under new config
Memory Safety
- Calculate worst-case memory usage (buffer_pool + max_connections * per-connection buffers) doesn't exceed 90% of physical RAM
- Confirm OS swap config is reasonable (swappiness=1, don't completely disable swap)
- If running in containers, calculate buffer pool based on container memory limit (not host RAM)
- Per-connection buffer sum (sort_buffer + join_buffer + read_buffer + read_rnd_buffer + tmp_table_size) doesn't exceed 10MB
Replication Safety
- server_id must differ between Primary and Replica
- If enabling GTID, all nodes must simultaneously enable gtid_mode=ON and enforce_gtid_consistency=ON
- Set read_only=ON and super_read_only=ON on Replicas to prevent accidental writes
- Confirm replica_parallel_workers and replica_preserve_commit_order are correctly configured
- binlog_format must match between Primary and Replica (both use ROW)
Performance Safety
- innodb_flush_method=O_DIRECT must be set on Linux
- innodb_io_capacity matches actual disk capability (don't use 200 on NVMe)
- slow_query_log=ON and long_query_time=1~2 ensure performance issues are captured
- performance_schema=ON ensures monitoring tools work properly
- Confirm OS file descriptor limit (
ulimit -n) >= table_open_cache * 2 - Confirm Transparent Huge Pages (THP) are disabled
Compatibility Check
- Confirm application driver/ORM supports the target MySQL version and auth plugin (8.0 defaults to caching_sha2_password)
- Confirm application doesn't depend on query_cache (removed in 8.0)
- If enabling skip_name_resolve, confirm all GRANT statements use IP addresses, not hostnames
- Confirm charset and collation match application expectations (especially collation changes when upgrading from 5.7 to 8.0)
Deployment strategy: Don't apply new config to all nodes simultaneously. Recommended: (1) Validate in test environment first; (2) Apply on one Replica and observe 24 hours; (3) Rolling deployment across Replicas; (4) Finally switch Primary. Any issue can be quickly rolled back.
FAQ
Can the generated config be used directly in production?
The generated config is a validated starting point, not a final solution. Every production environment has unique query patterns, data distributions, and hardware characteristics. Recommended workflow: (1) Use generated config as baseline; (2) Run actual workloads in a test environment for benchmarking (sysbench / real traffic replay); (3) Observe bottlenecks via Performance Schema and SHOW GLOBAL STATUS; (4) Adjust parameters one at a time and verify. Avoid changing multiple parameters simultaneously, as you can't determine which change had the effect.
How to determine if buffer pool is large enough?
Run SHOW ENGINE INNODB STATUS and check the BUFFER POOL AND MEMORY section. Key metric: Buffer pool hit rate should be > 99% (ideal 99.9%+). Below 99% means frequent disk reads, indicating the pool needs to be larger. Also check SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_reads' (physical reads) relative to Innodb_buffer_pool_read_requests (logical reads). You can also query information_schema.INNODB_BUFFER_POOL_STATS for detailed info.
Is innodb_flush_log_at_trx_commit = 2 safe?
Depends on your data loss tolerance. =2 means write to OS cache per commit, flush to disk once/second. If the MySQL process crashes but the OS is fine, no data is lost (data is in OS cache). Only on OS crash or power failure can up to 1 second of transactions be lost. For most business scenarios like e-commerce, social media, =2 is the best balance of performance and safety. But for financial transactions, payment systems where "not a single transaction can be lost", use =1.
Do I need to restart MySQL after modifying my.cnf?
Most parameters can be changed dynamically online without restart. Use SET GLOBAL variable_name = value; for immediate effect (new connections only), then write to my.cnf for persistence. However, these parameters require restart: innodb_buffer_pool_instances, innodb_page_size, innodb_read_io_threads, innodb_write_io_threads, innodb_log_file_size (5.7), innodb_undo_tablespaces (5.7). MySQL 8.0 introduced SET PERSIST, which auto-writes to mysqld-auto.cnf. Note: innodb_buffer_pool_size can be changed online in MySQL 5.7+, no restart needed.
What's different about config in container / Kubernetes environments?
Container environments need special attention: (1) Memory limits โ buffer pool should be based on the container's memory limit (not host RAM), typically 60-70% of the limit, leaving headroom for cgroup OOM; (2) CPU limits โ container CPUs are shared, innodb_thread_concurrency and I/O thread counts should be based on CPU limit, not physical cores; (3) Storage โ use PVC persistent volumes, avoid ephemeral storage; watch I/O latency (cloud disk vs local NVMe varies greatly); (4) Network โ container networking adds latency, max_connections can be reduced.
What are the main config differences between MySQL 5.7 and 8.0?
Key changes: (1) Redo log โ 8.0.30 replaced log_file_size + log_files_in_group with innodb_redo_log_capacity, with online resizing support; (2) Default improvements โ 8.0 defaults to utf8mb4 charset, ROW binlog_format, caching_sha2_password replaces mysql_native_password; (3) Removed/deprecated โ query_cache fully removed (deprecated in 5.7), innodb_buffer_pool_instances deprecated in 8.0.26+; (4) New features โ invisible indexes, descending indexes, window functions, CTEs, SET PERSIST, innodb_dedicated_server (auto-tunes buffer pool / redo log).
Can innodb_dedicated_server replace manual configuration?
innodb_dedicated_server=ON is MySQL 8.0's auto-tuning feature that sets innodb_buffer_pool_size (~75% RAM), innodb_redo_log_capacity, and innodb_log_buffer_size based on system memory. Good for simple cases: single-purpose DB servers with no special workload characteristics. Not suitable for: (1) servers running other applications; (2) OLAP/Mixed workloads needing per-connection buffer tuning; (3) needing fine-grained I/O control; (4) containers where MySQL may incorrectly detect host memory instead of container limit. Summary: it only manages 3 parameters, the other 50+ still need manual config. This generator's value is covering all key parameters.
How to safely change config in a production environment?
Safe config change workflow:
Dynamic parameters (no restart needed): (1) Test on one Replica with SET GLOBAL variable_name = value; first; (2) Observe 15-30 minutes for anomalies; (3) Apply on all Replicas one by one; (4) Finally apply on Primary; (5) After confirming effect, persist with SET PERSIST (8.0+) or manually edit my.cnf.
Static parameters (restart required): (1) Choose low-traffic window; (2) Modify my.cnf; (3) Restart one Replica first to test; (4) Observe 1-24 hours; (5) Restart remaining Replicas one by one; (6) Finally failover to restart the original Primary. Entire process can be zero-downtime (assuming Replicas exist).
Rollback preparation: Record original values before each change. Use version control for my.cnf (e.g., Git repo storing all server config files).
What are the config differences between MySQL 8.0 and MariaDB?
This generator is designed specifically for Oracle MySQL. MariaDB (10.x / 11.x) has these key config differences:
(1) Different parameter names: MariaDB keeps slave_* naming instead of replica_*; no innodb_redo_log_capacity, still uses innodb_log_file_size; innodb_dedicated_server doesn't exist.
(2) Thread pool: MariaDB has built-in thread pool (thread_handling=pool-of-threads), MySQL Community doesn't (requires Enterprise or Percona Server).
(3) Aria engine: MariaDB uses Aria instead of MyISAM for internal temp tables, with an additional aria_pagecache_buffer_size parameter.
(4) Encryption and compression: Different parameter names and support scope.
If using MariaDB, refer to the MariaDB official tuning guide and translate against this generator's output.
How does Percona Server config differ from Oracle MySQL?
Percona Server for MySQL is based on Oracle MySQL source code, so nearly all parameters are compatible, but it adds many extra parameters and features:
(1) Thread pool: Percona Server provides free thread pool (thread_handling=pool-of-threads), significantly better than MySQL Community under high connection counts.
(2) Extra InnoDB parameters: Such as innodb_empty_free_list_algorithm, innodb_buffer_pool_populate, and other performance optimization options.
(3) Backup locks: LOCK TABLES FOR BACKUP has less impact than FLUSH TABLES WITH READ LOCK.
(4) Audit log: Free audit log plugin, Oracle MySQL requires Enterprise edition.
Config generated by this tool is fully applicable to Percona Server. If using Percona Server, additionally consider enabling thread pool for better high-concurrency performance.
Summary
MySQL configuration tuning is an iterative process โ there is no "set and forget" optimal configuration. The generator and parameter reference on this page aim to help you:
- Quickly get a safe starting point: Avoid the most common config mistakes (buffer pool too small, redo log too small, no O_DIRECT), each of which can cause order-of-magnitude performance differences
- Understand the reasoning behind each parameter: Knowing "why" is more important than knowing "what value" โ when workloads change, DBAs who understand the reasoning can make correct tuning decisions
- Establish a systematic tuning workflow: From baseline to bottleneck identification to incremental adjustment to continuous monitoring, forming a repeatable methodology
Remember: query optimization usually has more impact than config tuning. A single query missing an index can make the most perfect configuration useless. While optimizing configuration, don't forget to use the EXPLAIN Analyzer to check your query execution plans, and the Slow Query Analyzer to identify bottleneck queries.