How to view buffer pool hit ratio by mysql
This article is about how mysql looks at buffer pool hit rates. The editor thinks it is very practical, so share it with you as a reference and follow the editor to have a look.
Method 1:
Mysql > show engine innodb status\ G
* * 1. Row *
Type: InnoDB
Name:
Status:
= =
2016-06-07 09:40:38 7f593136f700 INNODB MONITOR OUTPUT
= =
Per second averages calculated from the last 6 seconds # the following information is the average of the last 6 seconds
.
.
Buffer pool size 393208
Buffer pool size, bytes 6442319872
Free buffers 8807
Database pages 373575
Old database pages 137869
Modified db pages 43
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 1132357, not young 92318292
2.33 youngs/s, 2496.75 non-youngs/s
Pages read 2127203, created 1317816, written 1762250
18.00 reads/s, 3.83 creates/s, 14.33 writes/s
Buffer pool hit rate 998 / 1000, young-making rate 0 / 1000 not 314 / 1000 # buffer pool hit ratio
Result: the buffer pool hit rate in the last 6 seconds is 99.8%
Method 2:
Mysql > show global status like 'innodb%read%'
+-+ +
| | Variable_name | Value |
+-+ +
| | Innodb_buffer_pool_read_ahead_rnd | 0 | |
| | Innodb_buffer_pool_read_ahead | 839015 | # number of pre-reads |
| | Innodb_buffer_pool_read_ahead_evicted | 0 | |
| | Innodb_buffer_pool_read_requests | 1497697965 | # number of times pages are read from the buffer pool |
| | Innodb_buffer_pool_reads | 1227641 | # number of times pages are read from physical disk |
| | Innodb_data_pending_reads | 0 | |
| | Innodb_data_read | 34861846528 | # Total number of bytes read |
| | Innodb_data_reads | 2128711 | # Total number of reads |
| | Innodb_master_thread_active_loops | 63667 | |
| | Innodb_master_thread_idle_loops | 24896 | |
| | Innodb_pages_read | 2127692 | |
| | Innodb_rows_read | 803908452 | |
| | Innodb_read_views_memory | 2176 | |
+-+ +
13 rows in set (0.00 sec)
Buffer pool hit ratio = Innodb_buffer_pool_read_requests/ (Innodb_buffer_pool_read_requests+Innodb_buffer_pool_reads+Innodb_buffer_pool_read_ahead) = 1497697965 / (1497697965 / 1227641 / 839015) = 99.85%
Average number of bytes read = Innodb_data_read/ Innodb_data_reads=16376
It can be seen that the memory usage efficiency of the system is still very high.
Thank you for reading! This is the end of the article on "how to check the buffer pool hit rate of mysql". I hope the above content can be of some help to you, so that you can learn more knowledge. if you think the article is good, you can share it for more people to see!
