MySQL InnoDB memory pressure judgment and doubts, mysqlinnodb

MySQL InnoDB memory pressure judgment and doubts, mysqlinnodb

 

 

Source: http://www.cnblogs.com/wy123/p/7259866.html
(The Source retained is not the right of original works. My work is far from reaching this level, just to link to the original article, because some possible errors will be corrected or supplemented later, without him)

Like other data, memory has a crucial impact on database performance. MySQL InnoDB also uses memory to cache data, when accessing data, you can access the cached data in the memory to improve data access efficiency.
In MySQL, run the show variables like 'innodb _ buffer_pool % 'command or directly access the performance_schema.global_status system table,
You can obtain the memory or disk read status of the database during operation. Based on this data, you can calculate the memory or physical disk read/write status of InnoDB during data reading, that is, the cache hit rate.
This concept also applies to "cache hit rate" in SQL Server and has almost the same meaning,
However, the Buffer Cache hit ratio calculated by Buffer Cache hit ratio or sys. dm_ OS _performance_counters in SQL Server does not directly reflect the memory pressure,
The reason is that when SQL Server calculates the Buffer Cache hit ratio, it includes the pre-read data (the pre-read page is also regarded as Cache hit ),
For the InnoDB Engine of MySQL, there are similar concepts of logical read, physical read, and pre-read. Therefore, when calculating the MySQL cache hit rate, you need to pre-read the data information.
This knowledge point http://www.cnblogs.com/wy123/p/5272675.html has been followed for SQL Server

When determining the memory pressure, pay attention to the following parameters related to InnoDB read/write in performance_schema.global_status. The number of times here is the default page size of MySQL storage,
The page size can also be obtained through performance_schema.global_status. The unit is the number of bytes. By default, the page size is 16 kb.

Innodb_buffer_pool_read_requests: the number of pages read from the buffer pool
Innodb_buffer_pool_reads: ········································ · number of page reads from the physical case
Innodb_buffer_pool_reads_ahead: Number of pre-reads
Innodb_buffer_pool_read_ahead_evicted, however, the number of pages that are replaced from the buffer pool without pre-reading is generally used to determine the efficiency of pre-reading.
Innodb_data_read: ········································ the number of bytes read
Innodb_data_reads: ········································ number of reads

These parameters have been accumulated since the MySQL server was started. If the MySQL server is restarted, the parameters will be cleared and increased from the beginning.
Theoretically, the buffer hit rate is: the number of buffer reads/(the number of buffer reads + the number of physical reads + the number of pre-reads)
That is, Innodb_buffer_pool_read_requests/(Innodb_buffer_pool_read_requests + Innodb_buffer_pool_reads + Innodb_buffer_pool_reads_ahead)

I personally think that the real-time computing result of this value is of little reference significance. If it is calculated directly based on the queried value, the current calculated value returns the average value since the service was started.
When measuring the actual pressure, because the data pressure is periodic, it is necessary to collect data at a certain frequency within a certain period of time,
The logical reads, physical reads, and pre-reads that occur within each time period Respectively calculate the cache hit rate within each time interval for reference.
During busy periods, the memory pressure may be high, while the idle period is low. The calculated average value is not significant.

In addition, the cache hit rate can only reflect the memory pressure from one aspect. There is no absolute value to determine whether the pressure is high or not.
How high is the cache hit rate? I personally think there is no fixed number, not 99% or a value? It mainly depends on the fluctuation of the baseline and the specific environment.
For example, for high-speed storage, according to the long-term observation of other databases, because physical storage is optimized or itself is relatively strong, even if there is a certain degree of physical read, when the physical IO latency is not very long, it is acceptable.
At the same time, the memory pressure does not only mean that the memory is insufficient to be large enough, especially for MySQL, but also affects the cache hit rate due to various configurations, including the size of memory allocation.

 

There are two other practical problems,
1. How can I clear the cached data of a table (or a specific table) during MySQL testing?
2. After forcibly clearing the cache (restart MySQL service), query Innodb_buffer_pool_read_requests and Innodb_buffer_pool_reads,
Then query a physical table and query Innodb_buffer_pool_read_requests and Innodb_buffer_pool_reads again. It is found that Innodb_buffer_pool_read_requests increases more than Innodb_buffer_pool_reads.
After the restart, the first time you query the front and back of a physical table, the following shows that physical read is increased by 2 and logical read is increased by 5 (the test table does not have any index)

Continue, perform another query on the physical table, and find that the physical read is not increased (it can be understood that the data is cached ), logical read is increased by 4 (this rule is still the case for multiple tests ),
That is to say, the number of physical read cached data increases by 4 each time? I don't quite understand how this parameter is calculated (it is obvious that pre-read is not involved here ).

 

In other words, this formula is not used to calculate the cache hit rate of MySQL: Innodb_buffer_pool_read_requests/(Innodb_buffer_pool_read_requests + Innodb_buffer_pool_reads_ahead )?
I couldn't help but remember my understanding of the SQL Server cache hit rate. At that time, all Chinese documents said 95% or something. Basically, I didn't correctly interpret this parameter.
When we observe the server parameters, we find that the actual situation is not the same as the theory. Later, we found that this was not the case in English.

 

 

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