Shulou(Shulou.com)05/31 Report--

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!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.