...
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:
| Code Block | ||
|---|---|---|
| ||
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.
| Code Block | ||
|---|---|---|
| ||
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)
...