...

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

...

• Higher values improve performance for read-heavy workloads.

• Too low values can lead to excessive disk reads, slowing down queries.

Check System

To check how the database server behaves with the buffer, you can read the current setting with this command:

SHOW ENGINE INNODB STATUS;

This will print out the current InnoDB status.

What is interesting for us is the part about the BUFFER POOL AND MEMORY.

BUFFER POOL AND MEMORY
----------------------
Total large memory allocated 4294967296
Dictionary memory allocated 29275032
Buffer pool size   257536
Free buffers       183261
Database pages     74275
Old database pages 27397
Modified db pages  5447
Percent of dirty pages(LRU & free pages): 2.115
Max dirty pages percent: 90.000
Pending reads 0
Pending writes: LRU 0, flush list 0
Pages made young 1067518, not young 889010
0.00 youngs/s, 0.00 non-youngs/s
Pages read 70243, created 6819, written 22388
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 74275, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]

It will show what is the current value of the buffer size Buffer pool size.

How many bytes are still available Free buffers.

And how many queries are handled over the buffer Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000.

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.

...