...

[mysqld]
innodb_buffer_pool_size = 5G
innodb_log_file_size = 512M
innodb_flush_log_at_trx_commit = 2
innodb_io_capacity = 2000
innodb_io_capacity_max = 5000
max_connections = 500
tmp_table_size = 64M
max_heap_table_size = 64M
join_buffer_size = 8M
query_cache_type = 1
query_cache_size = 64M
log_bin = OFF

InnoDB Buffer Pool Size (innodb_buffer_pool_size)

...

The pool hit rate should be 1000/1000 if not the pool size is too short.

InnoDB Log File Size (innodb_log_file_size)

Purpose

Controls the size of InnoDB redo logs.

...

• A larger size improves transaction commit speed but increases recovery time after crashes.

InnoDB Flush Log at Transaction Commit (innodb_flush_log_at_trx_commit)

Purpose

• Controls when logs are written to disk.

...

• 1 ensures durability but adds disk overhead.

• 2 improves performance without major data loss risks.

InnoDB I/O Capacity (innodb_io_capacity)

Purpose

• Determines how many IOPS InnoDB can perform for background tasks (flushing dirty pages, index merges, etc.).

...