innodb buffer pool size:
caches the data of tables & indexes in memory rather than disk so faster connection handling.
increased performance by reducing response time.
80% memory in dedicated,
30-40% in LEMP*(check mysql connections /se before do this other wise memory will be wasted. You can measure /view buffer pool cache usage).
larger than database size (2x*)
1gb database ,
2core 8gb ram.
allocating 2gb good idea even 1gb instead of 128M default.
innodb_log_file_size = 256M # 25% of buffer pool size
innodb_buffer_pool_instances (=1)
innodb_buffer_pool_size = innodb_buffer_pool_chunk_size
* innodb_buffer_pool_instances
(It’s automatically calculated)
128M, set to 4G, then instances will increase.
Buffer pool utilization
Innodb_buffer_pool_pages_total Total number of pages in the buffer pool
(Innodb_buffer_pool_pages_total – Innodb_buffer_pool_pages_free)
SHOW GLOBAL STATUS LIKE ‘Innodb_buffer_pool_pages%’;
+———————————-+——–+
| Variable_name | Value |
+———————————-+——–+
| Innodb_buffer_pool_pages_data | 40136 |
| Innodb_buffer_pool_pages_dirty | 41 |
| Innodb_buffer_pool_pages_flushed | 4218 |
| Innodb_buffer_pool_pages_free | 90032 |
| Innodb_buffer_pool_pages_misc | 888 |
| Innodb_buffer_pool_pages_total | 131056 |
+———————————-+——–+
(131056-90032)/131056=0.31302649249
0.31302649249*100=31.30%
(only 30% ram used remaining wasted)
innodb_page_size equals to buffer pool size.
Buffer pool efficiency
nnodb_buffer_pool_reads/innodb_buffer_pool_read_requests*100= 0.001
requests 14871553 |
| Innodb_buffer_pool_reads | 39678
39678/14871553*100=0.26680468408
-
Innodb_buffer_pool_pages_total Total number of pages in the buffer pool
-
Buffer pool utilization >> Ratio of used to total pages in the buffer pool
-
innodb_buffer_pool_read_requests >>>Requests made to the buffer pool
-
Innodb_buffer_pool_reads >> Requests the buffer pool could not fulfill
mysql query cache retired try redis/memcached.
redis preferred.