...

[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.).

Recommended Value

• HDD: 200-400

• SSD: 1000-2000

• Example:

  Code Block
  innodb_io_capacity = 2000; innodb_io_capacity_max = 5000;

...

• Low values cause slow disk writes, leading to performance spikes.

• High values improve write performance and prevent bottlenecks.

Max Connections (max_connections)

Purpose

• Defines the maximum number of concurrent connections MySQL can handle.

...