SET GLOBAL innodb_buffer_pool_size=bytes;//8G 512M 256M
innodb_buffer_pool_size adjusted to multiple of innodb_buffer_pool_chunk_size * innodb_buffer_pool_instances
innodb_buffer_pool_reads/innodb_buffer_pool_read_requests*100= 0.001
Innodb_buffer_pool_read_requests are the number of requests to read a row from the buffer pool and
Innodb_buffer_pool_reads is the number of times Innodb has to perform read data from disk >> should be low
read efficiency:
write efficiency:
Adjusting buffer pool size according usage
after you set you can measure how much buffer pool utilization by this possible way its one of other things.
buffer pool pages /free vs
(Innodb_buffer_pool_pages_total – Innodb_buffer_pool_pages_free)
—————————————————————-
Innodb_buffer_pool_pages_total
Previously ISET Buffer pool size to 1GB
i am calculating it
buffer pool Pages
free -m
total used free shared buff/cache available
Mem: 3693 1726 208 134 1758 1560
total used free shared buff/cache available
Mem: 3693 1200 584 135 1908 2091
+———————————-+——–+
| Variable_name | Value |
+———————————-+——–+
| Innodb_buffer_pool_pages_data | 36099 |
| Innodb_buffer_pool_pages_dirty | 0 |
| Innodb_buffer_pool_pages_flushed | 702960 |
| Innodb_buffer_pool_pages_free | 28445 |
| Innodb_buffer_pool_pages_misc | 992 |
| Innodb_buffer_pool_pages_total | 65536 |
+———————————-+——–+
+———————————-+——-+
| Variable_name | Value |
+———————————-+——-+
| Innodb_buffer_pool_pages_data | 30080 |
| Innodb_buffer_pool_pages_dirty | 0 |
| Innodb_buffer_pool_pages_flushed | 180 |
| Innodb_buffer_pool_pages_free | 1834 |
| Innodb_buffer_pool_pages_misc | 854 |
| Innodb_buffer_pool_pages_total | 32768 |
+———————————-+——-+
Allocating mysql memory
check innodb buffer pool usage efficiency
| Innodb_buffer_pool_reads | 35780
Innodb_buffer_pool_reads vs Innodb_buffer_pool_read_requests
InnoDB Write buffer efficiency”?
innodb_log_file_size
default 128MB
The increase means that log flushes are less frequent