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

This article shows you how MySQL InnoDB memory pressure is determined and what the existing questions are. The content is concise and easy to understand. It will definitely make your eyes shine. I hope you can gain something through the detailed introduction of this article.

Like other data, memory has a critical impact on database performance, MySQL InnoDB also caches data through memory, and improves data access efficiency by accessing cached data in memory when accessing data.

MySQL can be accessed by using the show variables like 'Innodb_buffer_pool%' command or directly accessing the performance_schema.global_status system table.

You can get the reading of memory or disk in the database during operation. According to this data, you can calculate the reading and writing of memory or physical disk in InnoDB during the data reading process, that is, the cache *** rate.

For "cache *** ratio," this concept also exists in SQL Server, and the meaning is almost consistent,

However, Buffer Cache hit ratio calculated by Buffer Cache hit ratio performance counters or sys.dm_os_performance_counters in SQL Server does not directly reflect memory pressure.

The reason is that SQL Server includes pre-reading this part of the data when calculating Buffer Cache hit ratio (the pre-reading page is also counted as cache ***).

For MySQL's InnoDB engine, there are similar concepts of logical read, physical read and pre-read, so when calculating MySQL cache *** rate, you need to rely on pre-read information for this part of the data.

When determining the memory pressure, pay attention to the following parameters related to InnoDB reading and writing in performance_schema.global_status. The number of times here is the default page size stored in MySQL.

page size can also be obtained by performance_schema.global_status, in bytes. By default, the next page size is 16kb.

Innodb_buffer_pool_read_requests: Number of pages read from buffer pool

Innodb_buffer_pool_reads: ···············

Innodb_buffer_pool_reads_ahead: ························

Innodb_buffer_pool_read_ahead_evicted: ························

Innodb_data_read: ·······················································

Innodb_data_reads: ······················································

These parameters are cumulative increments since MySQL Server was started, and if MySQL Server is restarted, the parameters will be reset and cumulative increments from the start.

Buffer *** ratio is theoretically: buffer read times/(buffer read times + physical read times + prefetch times)

That is: Innodb_buffer_pool_read_requests/(Innodb_buffer_pool_read_requests+Innodb_buffer_pool_reads+Innodb_buffer_pool_reads_ahead)

Personally, the real-time calculation result of this value has little reference significance. If the calculation is carried out directly according to the query value, the current calculation value feedback is the average value since the service was started.

When measuring the actual pressure, because the pressure of data is phased, it needs to be collected within a certain period of time according to a certain frequency. Within this period of time,

The number of logical reads, physical reads, and pre-reads occurring within each time period, and the cache *** rate within each time interval are calculated respectively, which has reference significance.

It is possible that during busy periods, memory pressure is high, while during idle periods, pressure is low, and the calculated average value is not significant.

In addition, the cache *** ratio can only reflect the pressure of memory from one aspect, and there is no absolute value to judge whether the pressure is large or not.

How high is the cache *** rate? I don't think there is a fixed number, but 99% or some value? It depends mainly on fluctuations compared to the baseline and also on the specific environment.

For example, for high-speed storage, according to the long-term observation of other databases, because the physical storage is optimized or itself is relatively strong, even if there is a certain degree of physical read, the physical IO latency is not very long, it is acceptable.

At the same time, the memory pressure situation is not just to say that "memory is not large enough," especially MySQL, affected by a variety of configurations, including the size of various memory allocations, there will be cases that affect the cache ** rate.

There are two other practical problems,

1, MySQL in the test, how to empty the table (or specific table) cache data?

2. After the cache is forcibly emptied (restart MySQL service), query Innodb_buffer_pool_read_requests and Innodb_buffer_pool_reads.

Then query a physical table, query Innodb_buffer_pool_read_requests and Innodb_buffer_pool_reads again, and find that the increase of Innodb_buffer_pool_read_requests is greater than Innodb_buffer_pool_reads

After the restart, *** times before and after querying a physical table, the screenshot below shows that the physical read increases by 2 and the logical read increases by 5(there is no index on the test table)

Continue, query the physical table under test again, and find that the physical read has not increased (it can be understood that the data has been cached), and the logical read has increased by 4(the current situation is still the same for many tests).

That is to say, 2 physical reads of cached data, logical reads each increase by 4? I don't understand how this parameter is calculated (obviously there is no pre-reading involved here).

Innodb_buffer_pool_read_requests/(Innodb_buffer_pool_read_requests+Innodb_buffer_pool_reads+Innodb_buffer_pool_reads_ahead)

I can't help but think of the understanding of sqlserver cache *** rate at that time. At that time, all the Chinese data said it was 95%, and the Chinese data basically did not correctly interpret this parameter.

In fact, when observing the server parameters, I found that the actual situation did not match the theory at all. Later, the English data found that this was not the case.

The above content is MySQL InnoDB memory stress judgment and what kind of questions exist. Have you learned knowledge or skills? If you want to learn more skills or enrich your knowledge reserves, please pay attention to the industry information channel.

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.