[MySQL生产环境] Innodb存储引擎内存报警问题处理过程

分手后的思念是犯贱 2022-05-12 21:56 156阅读 0赞

**1 不停的收到email报警，内存值超过阀值80%了。  
  
  
2 top下，mysqld进程确实占据了77.5%，再加上一些其他的辅助进程，内存usage到了81%也可以理解。**  
\[xxx@00903 5.5.25a\]$ top  
top - 03:48:55 up 51 days, 17:11, 2 users, load average: 0.09, 0.09, 0.11  
Tasks: 202 total, 1 running, 201 sleeping, 0 stopped, 0 zombie  
Cpu(s): 0.2%us, 0.1%sy, 0.0%ni, 98.8%id, 0.8%wa, 0.0%hi, 0.0%si, 0.0%st  
Mem: 28743468k total, 28452540k used, 290928k free, 467048k buffers  
Swap: 4194296k total, 0k used, 4194296k free, 4589332k cached  
  
  
PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND  
23956 mysql 20 0 24.1g 21g 5408 S 1.0 77.5 390:56.59 mysqld  
 9 root 20 0 0 0 0 S 0.3 0.0 203:16.51 ksoftirqd/1  
23971 mmmd 20 0 687m 64m 1960 S 0.3 0.2 73:53.23 perl  
1 root 20 0 21444 1232 928 S 0.0 0.0 1:29.37 init  
2 root 20 0 0 0 0 S 0.0 0.0 0:00.44 kthreadd  
  
  
**3 看看free -m吧**  
\[xxx@00903 ~\]$ free -m  
total used free shared buffers cached  
Mem: 28069 27828 240 0 440  
\-/+ buffers/cache: 22820 5249  
Swap: 4095 0 4095  
\[xxx@00903 ~\]$  
  
  
**4 简要分析理解free -m参数值意义**  
total: 28069，总内存大小。  
used： 27828，已经使用过的内存大小。  
free：240，剩余的内存大小。  
total值是used+free的值总合：

mysql> select 27828+240;
    +-----------+
    | 27828+240 |
    +-----------+
    |     28068 |
    +-----------+
    1 row in set (0.00 sec)
    -- 总和28068与28069差了一个1，可能是-m四舍五入造成的。
    mysql>

shared 不同进程间用来进行数据交换的地方,一般都是0（多个进程共享的内存总额)。  
cached：4568，经常被用在磁盘的I/O请求上，如果有多个进程都要访问某个文件，于是该文件便被做成cache以方便下次被访问，这样可提供系统性能。

我们监控的是-/+ buffers/cache:中的useed一项22820, 总内存大小是 28069，因此一计算，确实为81%

mysql> 
    mysql> SELECT 22828/28069;
    +-------------+
    | 22828/28069 |
    +-------------+
    |      0.8133 |
    +-------------+
    1 row in set (0.00 sec)
    
    mysql>

而且-/+ buffers/cache中的后面的used+free的总合也与第一行的Mem的total值相等：

mysql> SELECT 22820+5249;
    +------------+
    | 22820+5249 |
    +------------+
    |      28069 |
    +------------+
    1 row in set (0.00 sec)

**目前没有找到问题所在，去查阅一些基础文档，有很多东西时间一长不用，都快要遗忘了**

**5 buffers与cached的分析**

对操作系统来讲是Mem的参数buffers和cached 都是属于被使用,它认为free只有752M。  
对应用程序来讲是(-/+ buffers/cach),buffers和cached 是等同可用的,buffer和cached是为了提高程序执行的性能,当程序使用内存时,buffer和cached会很快地被使用。  
以应用来看看,以(-/+ buffers/cache)的free和used为主.我们看这个就好了.Linux为了提高磁盘和内存存取效率, 开发人员做了很多精心的设计, 除了对dentry进行缓存(用于VFS,加速文件路径名到inode的转换), 还采取了两种主要Cache方式：Buffer Cache和Page Cache.前者针对磁盘块的读写,后者针对文件inode的读写.这些Cache能有效缩短了 I/O系统调用(比如read,write,getdents)的时间.

**6innodb\_buffer\_pool\_size（global）**

当我们使用InnoDB存储引擎的时候，innodb\_buffer\_pool\_size 参数可能是影响我们性能的最为关键的一个参数了，他用来设置用于缓存 InnoDB 索引及数据块的内存区域大小，类似于 MyISAM 存储引擎的 key\_buffer\_size 参数，当然，可能更像是 Oracle 的 db\_cache\_size。简单来说，当我们操作一个 InnoDB 表的时候，返回的所有数据或者去数据过程中用到的任何一个索引块，都会在这个内存区域中走一遭。  
  
和key\_buffer\_size 对于 MyISAM 引擎一样，innodb\_buffer\_pool\_size 设置了 InnoDB 存储引擎需求最大的一块内存区域的大小，直接关系到 InnoDB存储引擎的性能，所以如果我们有足够的内存，尽可将该参数设置到足够打，将尽可能多的 InnoDB 的索引及数据都放入到该缓存区域中，直至全部。  
  
我们可以通过 (Innodb\_buffer\_pool\_read\_requests – Innodb\_buffer\_pool\_reads) / Innodb\_buffer\_pool\_read\_requests \* 100% 计算缓存命中率，并根据命中率来调整 innodb\_buffer\_pool\_size 参数大小进行优化。

**7MySQL Query Cache**

在大部分的 MySQL 分发版本中，Query Cache 功能默认都是打开的，我们可以通过调整 MySQL Server 的参数选项打开该功能。主要由以下5个参数构成：

*  query\_cache\_limit：允许 Cache 的单条 Query 结果集的最大容量，默认是1MB，超过此参数设置的 Query 结果集将不会被 Cache
 *  query\_cache\_min\_res\_unit：设置 Query Cache 中每次分配内存的最小空间大小，也就是每个 Query 的 Cache 最小占用的内存空间大小
 *  query\_cache\_size：设置 Query Cache 所使用的内存大小，默认值为0，大小必须是1024的整数倍，如果不是整数倍，MySQL 会自动调整降低最小量以达到1024的倍数
 *  query\_cache\_type：控制 Query Cache 功能的开关，可以设置为0(OFF),1(ON)和2(DEMAND)三种，意义分别如下：
    
     *  0(OFF)：关闭 Query Cache 功能，任何情况下都不会使用 Query Cache
     *  1(ON)：开启 Query Cache 功能，但是当 SELECT 语句中使用的 SQL\_NO\_CACHE 提示后，将不使用Query Cache
     *  2(DEMAND)：开启 Query Cache 功能，但是只有当 SELECT 语句中使用了 SQL\_CACHE 提示后，才使用 Query Cache
 *  query\_cache\_wlock\_invalidate：控制当有写锁定发生在表上的时刻是否先失效该表相关的 Query Cache，如果设置为 1(TRUE)，则在写锁定的同时将失效该表相关的所有 Query Cache，如果设置为0(FALSE)则在锁定时刻仍然允许读取该表相关的 Query Cache。

重新补充了mysql的缓存基础知识，然后根据slow log查到了有一些慢sql，可是如何释放已经的缓存呢？ 用了flush query cache; 没有效果，内存使用率仍然在81%。

知道mysql在执行查询的时候，特别是需要table scan的时候，数据是一点点进入内存的，在mysql的buffer pool中有链表结构的存在，page是io的基本单位，一个个的page读进去，即使只访问一条记录，也要读一个page，这是没办法的事情，table scan的表记录数越多，读到内存的page就越多，这样内存就慢慢涨到了81%了。正常处理流程是：当内存涨的时候首先是swap，在swap加警告，然后告警后关掉swap，之后就是排查问题，没必要内存到90%就搞掉。

记得Mysql不都是会自动释放内存资源的吗？为什么线上的db没有释放，这只是一台replication从库，用来做备份恢复所用的，没有应用业务在使用。

之后过了2小时，在慢查询sql执行完毕过后2小时，内存使用率自动降下来了，回到75%了。

最后感谢群里,土豆,酱油,刘剑以及木木等提出的宝贵意见。