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
SHOW GLOBAL STATUS LIKE ‘Innodb_buffer_pool_pages%’;
(Innodb_buffer_pool_pages_total – Innodb_buffer_pool_pages_free)
—————————————————————-
Innodb_buffer_pool_pages_total
Innodb_buffer_pool_pages_total | 8192 |
+———————————-+——
| Innodb_buffer_pool_pages_free | 1020
8192-1020/8192= 0.875488281 *100= 87.5%
SHOW GLOBAL STATUS LIKE ‘Innodb_buffer_pool_pages%’;
+———————————-+——-+
| Variable_name | Value |
+———————————-+——-+
| Innodb_buffer_pool_pages_data | 31484 |
| Innodb_buffer_pool_pages_dirty | 0 |
| Innodb_buffer_pool_pages_flushed | 20608 |
| Innodb_buffer_pool_pages_free | 33128 |
| Innodb_buffer_pool_pages_misc | 924 |
| Innodb_buffer_pool_pages_total | 65536 |
(65536-33128)/65536*100=49.4% (1GB i set maybe i can decrease to 512MB)
2nd time calculation
free -m
total used free shared buff/cache available
Mem: 3693 1726 208 134 1758 1560
free -m
total used free shared buff/cache available
Mem: 3693 1726 208 134 1758 1560
after reducing bp size
free -m
total used free shared buff/cache available
Mem: 3693 1200 584 135 1908 2091
total used free shared buff/cache available
Mem: 3693 1200 584 135 1908 2091
SHOW GLOBAL STATUS LIKE ‘Innodb_buffer_pool_pages%’;
+———————————-+——–+
| 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 | 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 |
+———————————-+——–+
Total – free /toal
(65536-28445)/65536*100=56.6 %
anyway i decreased to 512 MB
SHOW GLOBAL STATUS LIKE ‘Innodb_buffer_pool_pages%’;
+———————————-+——-+
| 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 |
+———————————-+——-+
+———————————-+——-+
| 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 |
+———————————-+——-+
and restarted
service mysql restart
determine the actual size of your buffer pool. For instance, this simple formula gives us the total size of buffer pool in bytes:
Innodb_buffer_pool_pages_total * innodb_page_size
The innodb_page_size can be retrieved using a SHOW VARIABLES query:
SHOW VARIABLES LIKE “innodb_page_size”
SHOW VARIABLES LIKE “innodb_page_size”;
innodb_page_size | 16384 |
16384
Innodb_buffer_pool_pages_total * innodb_page_size
65536*16384= (1073741824 bytes equal to 1GB)
innodb_log_file_size = 1G
innodb_log_buffer_size = 256M
innodb_io_capacity = 1000
buffer pool caches
innodb_read_io_threads=4
innodb_write_io_threads=8 #To stress the double write buffer
innodb_buffer_pool_size=20G
innodb_buffer_pool_load_at_startup=ON
innodb_log_file_size = 32M #Small log files, more page flush
innodb_log_files_in_group=2
innodb_file_per_table=1
innodb_log_buffer_size=8M
innodb_flush_method=O_DIRECT
innodb_flush_log_at_trx_commit=0
skip-innodb_doublewrite #commented or not depending on test
Allocating mysql memory
MySQL buffers, temporary tables, connection pools, and replication related buffers ,log file size,+ buffer pool
% of total RAM’ for OS needs
80% for dedicated mysql server
30-50% in shared server by free /available memory free -m
cat /proc/meminfo
SHOW GLOBAL VARIABLES LIKE “innodb_buffer_pool_size”;
innodb_buffer_pool_size | 134217728 // Bytes
134MB // it should be
nnodb_buffer_pool_size = chunk size *instances (automatically)
pool size upto 80-0% for dedicated but for wordpress below 50% will be better (moniotor later)
mycase 3.7gb ram always 1-2gb free /cached by linux.
SET GLOBAL innodb_buffer_pool_size=1073741824;
increased to 1gb
doc
innodb_buffer_pool_size=8G
always be equal to or a multiple of innodb_buffer_pool_chunk_size * innodb_buffer_pool_instances
SHOW GLOBAL VARIABLES LIKE “innodb_buffer_pool_instances”;
instance 8
size 1gb
mycase 1 & chunksize 134MB
134*8= 1072MB (Bytes actually 1024 in decimal but took 1072 KBytes)
SHOW GLOBAL VARIABLES LIKE “innodb_buffer_pool_chunk_size”;
SIZE
: it should be 2x for chunksize ( 134MB defaults mysql 8)* instances
check innodb buffer pool usage efficiency
mysql * default config 134 Mb & 1 instances.
innodb reads from disk vs memory
| Innodb_buffer_pool_read_requests | 4355260687 |
| Innodb_buffer_pool_reads | 35780
| Innodb_buffer_pool_reads | 35780
innodb_buffer_pool_reads/innodb_buffer_pool_read_requests*100= 0.001
35780/4355260687*100=0.000821
SHOW GLOBAL STATUS LIKE ‘Innodb_buffer_pool%’;
SHOW GLOBAL STATUS LIKE ‘innodb_buffer_pool_rea%’;
for (high performance) Total_Instances:= Total_IO_Threads=(innodb_read_io_threads + innodb_write_io_threads)
mycase 8
innodb_write_io_threads=4
innodb_read_io_threads =4 // default however we can increase based on ready or write heavy to know this
but 4+4=8 (should be equal to CPU cores )
than 64 × innodb_read_io_threads pending read requests in SHOW ENGINE INNODB STATUS
mysql> SHOW GLOBAL STATUS LIKE ‘Com_select’;
+—————+——-+
| Variable_name | Value |
+—————+——-+
| Com_select | 36811 |
+—————+——-+
1 row in set (0.00 sec)
SHOW GLOBAL STATUS WHERE Variable_name IN (‘Com_insert’, ‘Com_update’, ‘Com_replace’, ‘Com_delete’);
+—————+——-+
| Variable_name | Value |
+—————+——-+
| Com_delete | 453 |
| Com_insert | 5242 |
| Com_replace | 0 |
| Com_update | 631 |
5242+453+631=6326 vs 36811
wordpress blog read requests 6 times bigger than then
innodb_write_io_threads=4
innodb_read_io_threads =8
(I may change threds to 4 to 8 4 to 2
SHOW VARIABLES LIKE ‘innodb_read_io_threads’;
SHOW Global VARIABLES LIKE ‘innodb_read_io_threads’;
SET GLOBAL innodb_read_io_threads=8;
If you have a high end I/O subsystem and you see more than 64 × innodb_read_io_threads pending read requests in SHOW ENGINE INNODB STATUS output, you might improve performance by increasing the value of innodb_read_io_threads.
SHOW GLOBAL STATUS LIKE ‘Innodb_buffer_pool%’;
ptimizing InnoDB Disk I/O
top command to disk uage in linux
innodb_log_file_size
innodb_io_capacity 200 defaults
based on SSD caluclated system io
SHOW GLOBAL STATUS
SHOW GLOBAL STATUS; |\
read request Disk vs / memory
Innodb_buffer_pool_reads vs Innodb_buffer_pool_read_requests
monitoring Buffer pool efficiency reads & pages
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
innodb_buffer_pool_reads/innodb_buffer_pool_read_requests*100= 0.001
Innodb_buffer_pool_read_ahead | 68743954 |
| Innodb_buffer_pool_read_ahead_evicted | 236110 |
| Innodb_buffer_pool_read_requests | 5030235492 |
| Innodb_buffer_pool_reads | 8375112
8375112/5030235492*100= 0.166495426
6916/28835148*100= 0.0239846177
innodb_buffer_pool_reads/innodb_buffer_pool_read_requests*100= 0.001
SHOW GLOBAL STATUS LIKE ‘innodb_buffer_pool_rea%’;
+—————————————+———-+
| Variable_name | Value |
+—————————————+———-+
| Innodb_buffer_pool_read_ahead_rnd | 0 |
| Innodb_buffer_pool_read_ahead | 23083 |
| Innodb_buffer_pool_read_ahead_evicted | 0 |
| Innodb_buffer_pool_read_requests | 47049712 |
| Innodb_buffer_pool_reads | 7727 |
+—————————————+———-+
7727/47049712*100=0.016
|
SHOW ENGINE INNODB STATUS\G
Total large memory allocated 1099431936
Dictionary memory allocated 3427245
—BUFFER POOL 7
Buffer pool size 65536
Free buffers 33763
Database pages 30856
Old database pages 11546
Buffer pool hit rate 1000 / 1000
young 5/8 vs old pages 3/8
https://dev.mysql.com/doc/refman/5.7/en/innodb-standard-monitor.html
set global thread_cache_size = 16;
SET GLOBAL innodb_buffer_pool_size =4026531840;
InnoDB Write buffer efficiency”?
* Innodb_pages_written / Innodb_buffer_pool_write_requests — Write requests that had to hit disk — <15% is ‘good’
* Innodb_buffer_pool_wait_free / Innodb_buffer_pool_write_requests — Should not have to wait to get into the buffer pool. — <1% is ‘good’
* Innodb_buffer_pool_pages_flushed / Uptime — Writes actually going to disk
* Innodb_buffer_pool_pages_flushed / max(Questions, Queries) — Buffer pool writes per query
SHOW GLOBAL STATUS LIKE ‘innodb_buffer_pool_wri%’;
+———————————–+——–+
| Variable_name | Value |
+———————————–+——–+
| Innodb_buffer_pool_write_requests | 168685 |
SHOW GLOBAL STATUS LIKE ‘Innodb_pages_written%’;
Innodb_pages_written | 23182
23182/168685*100= 13 (<15%)
SHOW GLOBAL STATUS LIKE ‘Innodb_buffer_pool_wait_free’;
Innodb_buffer_pool_wait_free 0
SHOW GLOBAL STATUS LIKE ‘Innodb_buffer_pool_wait_free’;
0/168685= 0% (<1%)
show engine innodb status\G
Buffer pool hit rate 900 / 1000
innodb_log_file_size
default 128MB
The increase means that log flushes are less frequent
