MySQL InnoDB内存压力判断以及存在的疑问

 

 

 本文出处：http://www.cnblogs.com/wy123/p/7259866.html
（保留出处并非什么原创作品权利，本人拙作还远远达不到，仅仅是为了链接到原文，因为后续对可能存在的一些错误进行修正或补充，无他）

与其他数据一样，内存对数据库的性能有着至关重要的影响，MySQL InnoDB也一样通过内存来缓存数据，在访问数据的时候通过访问内存中缓存的数据来提高数据的访问效率。
MySQL中通过show variables like 'Innodb_buffer_pool%'命令或者直接访问performance_schema.global_status系统表，
可以得到数据库在运行过程中对内存或者磁盘的读取情况，根据这个数据，可以计算出来InnoDB在对数据读取过程中发生的内存或者物理磁盘读写情况，也即缓存命中率。
对于“缓存命中率”，在SQL Server中也有这一概念，而且含义几乎是一致的，
不过SQL Server中通过Buffer Cache hit ratio性能计数器或者 sys.dm_os_performance_counters计算出来的Buffer Cache hit ratio并不能直接反应内存压力情况，
原因归结为SQL Server在计算Buffer Cache hit ratio的时候，是包含了预读这部分数据的（把预读部分的page也算做缓存命中），
对于MySQL的InnoDB引擎，有同样类似的逻辑读，物理读与预读的概念，因此在计算MySQL缓存命中率的时候，需要靠预读这部分数据的信息。
对于SQL Server之前关注过这个知识点http://www.cnblogs.com/wy123/p/5272675.html

在判定内存压力的时候，关注performance_schema.global_status中与InnoDB读写相关的参数有如下几个，这里的次数也就是MySQL存储的默认page大小，
page大小同样可以通过performance_schema.global_status 来获取，单位是字节数，默认情况下页大小是16kb

Innodb_buffer_pool_read_requests：································从缓冲池中读取的页的次数
Innodb_buffer_pool_reads：············································从物理此案读取页的次数
Innodb_buffer_pool_reads_ahead：··································预读的次数
Innodb_buffer_pool_read_ahead_evicted：························预读的页，但是没有被预读就从缓冲池中被替换的页的数量，一般用来判断预读的效率
Innodb_data_read：·······················································读取的字节数
Innodb_data_reads：······················································读取的次数

这些参数是MySQL服务器启动以来累计增加的，如果重启MySQL服务，如果重启服务器（可能有参数配置），默认情况下这些参数将清零从新开始累计增加。
缓冲命中率理论上就是：缓冲读取次数/（缓冲读取次数+物理读取次数+预读次数）
也即：Innodb_buffer_pool_read_requests/(Innodb_buffer_pool_read_requests+Innodb_buffer_pool_reads+Innodb_buffer_pool_reads_ahead)

个人认为，这个值的实时计算结果参考意义并不大，如果直接根据查询出来的值进行计算，当前计算值反馈的是自服务启动以来的平均值。
在衡量实际压力的时候，因为数据的压力是阶段性的，需要在一定的时间段之内，按照某一个频率收集这一段时间之内，
每个时间段之内发生的逻辑读次数，物理读次数，预读次数，分别计算每个时间间隔之内的缓存命中率，才具备参考意义。
可能在业务繁忙期，内存压力较大，而在空闲期压力较小，计算出来的平均值意义并不大。

另外，缓存命中率只能从一个方面反映内存的压力情况，并没有一个绝对值去判断压力大还是不大。
究竟缓存命中率有多高，个人认为没有一个定数，非要是99%或者某个值？主要是看与基线相比其波动情况，另外取决于具体的具体的环境。
比如对于高速存储，根据其他数据库的长期观察，由于物理存储经过优化或者本身就比较强，即便是存在一定程度的物理读，物理IO延迟不是非常长的情况下，都是可以接受的。
同时，内存压力情况也不仅仅是说“内存不足够大”，尤其是MySQL，受多种配置的影响，包括各种内存分配的大小，都会存在影响缓存命中率的情况。

 

另外有两个实际问题，
1，MySQL在测试的时候，如何清空表（或者特定表）的缓存的数据？
2，在（重启MySQL服务）强制清空缓存之后，查询Innodb_buffer_pool_read_requests和Innodb_buffer_pool_reads，
　  然后查询某个物理表，再次查询Innodb_buffer_pool_read_requests和Innodb_buffer_pool_reads，发现Innodb_buffer_pool_read_requests的增幅大于Innodb_buffer_pool_reads
　   重启完之后，第一次查询一张物理表的前后，如下截图看到的是物理读增加了2，逻辑读增加了5（测试表上没有任何索引）

　　继续，再次对测试的物理表进行一次查询，发现物理读没有增加（可以理解为数据被缓存了），逻辑读增加了4（当前情况多次测试依旧是该规律），
　　也就是说2次物理读缓存的数据，逻辑读每次都增加4？不太理解，这个参数具体是怎么计算出来的（很明显这里不涉及预读）。

 

 　　或者说：MySQL缓存命中率的计算，并非这个公式：Innodb_buffer_pool_read_requests/(Innodb_buffer_pool_read_requests+Innodb_buffer_pool_reads+Innodb_buffer_pool_reads_ahead)？
　　不由得想起了当时对于sqlserver缓存命中率的理解，当时所有的中文资料上都说是95%什么的，基本上没有正确解读这个参数的。
　　实际在观察服务器参数的时候，发现实际情况跟理论根本不搭嘎，后来英文资料才发现不是这么回事。

 

 

参考

MySQL技术内幕InnoDB存储引擎
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