MySQL InnoDB Memory pressure judgment and the presence of doubt

The source of this article: http://www.cnblogs.com/wy123/p/7259866.html
(It is not the original works right to retain the source, I my book still far to reach, just to link to the original text, because the following may exist some errors to amend or supplement, without him)

Like other data, memory has a critical impact on the performance of a database, and MySQL InnoDB caches data in memory to improve access to data by accessing data that is cached in memory.
MySQL through show variables like ' innodb_buffer_pool% ' command or direct access to performance_schema.global_status system tables,
Can get the database in the running process of memory or disk read, according to this data, can be calculated InnoDB in the data read process occurred in memory or physical disk read and write, that is, cache hit rate.
There is also this concept in SQL Server for "cache hit ratio", and the meaning is almost identical,
However, the buffer cache hit ratio computed by the buffer cache hit ratio performance counter or sys.dm_os_performance_counters in SQL Server does not directly reflect the memory pressure situation,
The reason for this is that when SQL Server calculates the buffer cache hit ratio, it contains the read-ahead portion of the data (the page of the read-ahead section is also counted as the cache hits),
For the MySQL InnoDB engine, there is the same logical read, physical read and read-ahead concept, so when calculating the MySQL cache hit ratio, it is necessary to read the information of this part of the data.
This knowledge point has been followed for SQL Server http://www.cnblogs.com/wy123/p/5272675.html

In determining the memory pressure, pay attention to the Performance_schema.global_status and InnoDB read-write related parameters are as follows, here the number of times is the MySQL storage default page size,
The page size can also be obtained by Performance_schema.global_status, in bytes, by default, with a size of 16kb

Innodb_buffer_pool_read_requests: Number of pages read from the buffer pool
Innodb_buffer_pool_reads: Number of pages read from physical case
Innodb_buffer_pool_reads_ahead: Number of read-ahead
Innodb_buffer_pool_read_ahead_evicted: Read-Ahead page, but the number of pages that have been replaced from the buffer pool without pre-reading is generally used to determine the efficiency of the read-ahead
Innodb_data_read: Number of bytes read
Innodb_data_reads: Number of Reads

These parameters are cumulative increase since the MySQL server started, and if you restart the MySQL server, the parameters will be 0 from the beginning of the new cumulative increase.
Buffer hit ratio in theory: Buffered reads/(buffer reads + physical reads + read-ahead times)
Also namely: innodb_buffer_pool_read_requests/(Innodb_buffer_pool_read_requests+innodb_buffer_pool_reads+innodb_buffer_ Pool_reads_ahead)

Personally, this value of the real-time calculation results of reference is not significant, if directly based on the value of the query to calculate, the current calculated value feedback from the service since the start of the average.
When measuring the actual pressure, because the pressure of the data is staged, it needs to be collected within a certain time period, according to a certain frequency,
The number of logical reads, the number of physical reads, the number of pre-reads, and the amount of cache hits within each time interval, respectively, are useful for reference.
It is possible that during busy periods, the memory pressure is large, while the pressure in the idle period is small, the calculated mean is not very significant.

In addition, the cache hit rate can only reflect the memory pressure from one aspect, and there is no absolute value to judge whether the pressure is large or not.
How high is the cache hit rate, and personally think that there is no one, not 99% or some value? The main thing is to look at the fluctuations in comparison to the baseline, depending on the specific environment.
For high-speed storage, for example, depending on the long-term observation of other databases, it is acceptable that physical IO latency is not very long, due to the fact that physical storage is optimized or inherently strong, even if there is a certain level of physical reading.
At the same time, the memory pressure situation is not only said "memory is not big enough", especially MySQL, affected by a variety of configurations, including the size of various memory allocations, there will be a situation affecting the cache hit ratio.

There are also two practical questions,
1,mysql how to empty the cached data of a table (or a specific table) when testing?
2, after forcing the cache to clear (restart MySQL service), query innodb_buffer_pool_read_requests and Innodb_buffer_pool_reads,
Then querying a physical table, querying innodb_buffer_pool_read_requests and innodb_buffer_pool_reads again, and discovering Innodb_buffer_pool_read_ Requests increase is greater than innodb_buffer_pool_reads
After restarting, the first time you query a physical table, see the following is the physical reading increased by 2, the logical read increased by 5 (no index on the test table)

Continue, once again to the physical table of the test query, found that the physical read no increase (can be understood as the data is cached), logical reading increased by 4 (the current situation many times the test is still the rule),
That is, 2 times the physical read cache data, logical read every time the increase of 4? It's not quite clear how this parameter is calculated (obviously no pre-reading is involved here).

Or: MySQL cache hit ratio calculation, not this formula: innodb_buffer_pool_read_requests/(innodb_buffer_pool_read_requests+innodb_buffer_pool _reads+innodb_buffer_pool_reads_ahead)?
I can not help but think of the SQL Server cache hit rate of understanding, at that time all the Chinese information is said to be 95% or so, the Chinese information is basically not correctly read this parameter,
In the actual observation of the server parameters, found that the actual situation with the theory is not at all, and later found that the English material is not so.

Reference

MySQL Technology insider InnoDB storage Engine
Https://dev.mysql.com/doc/refman/5.7/en/innodb-parameters.html#sysvar_innodb_buffer_pool_size
Https://dev.mysql.com/doc/refman/5.7/en/innodb-buffer-pool-resize.html#innodb-buffer-pool-online-resize

MySQL InnoDB Memory pressure judgment and the presence of doubt