Mysql5.7内存持续增高的解决方案及说明-20200327

阳光穿透心脏的1/2处 2023-07-18 12:51 14阅读 0赞

2019-04-17 14:10:11

阿里云服务器搭建了主从服务器，近期发现mysql5.7占用了服务器71%的内存。并且每天仍然有不断扩充的趋势。为了避免内存爆炸，调查了一下内存增加的原因和解决方法。调研的过程学习了很多资料，这里只说结论供大家参考。

服务器：4cpu 8GB内存  
解决方案

降低innodb\_buffer\_pool\_size，并修改配置文件

\#\# 修改前的值根据内存的80%来设定  
mysql> SELECT @@innodb\_buffer\_pool\_size;  
\+---------------------------+  
| @@innodb\_buffer\_pool\_size |  
\+---------------------------+  
| 7516192768 |  
\+---------------------------+  
1 row in set (0.00 sec)  
\#\# 修改后的值2048M,这台服务器是从库不是很重要，我不想给太多资源。大家要结合自身情况  
mysql> SET GLOBAL innodb\_buffer\_pool\_size=2048\*1024\*1024  
\-> ;  
Query OK, 0 rows affected (0.10 sec)

mysql> SELECT @@innodb\_buffer\_pool\_size;  
\+---------------------------+  
| @@innodb\_buffer\_pool\_size |  
\+---------------------------+  
| 2147483648 |  
\+---------------------------+  
1 row in set (0.00 sec)

修改后内存占用量直接下降了40%  
在这里插入图片描述  
推荐工具innotop

安装过程可以参考其他博客。用这个工具连接mysql之后可以shift+b看到innodb buffer pool的使用状态。  
我参考的是Memory 2.05G(修改后)，这个值修改前是7G，相当于内存的90%都被innodb buffer pool占用了。单凭这个参数就足以判定，是buffer pool占用了内存。

\[RO\] InnoDB Buffers (? for help) server1, 5d, InnoDB 10s :-), 9.25 QPS, 6/3/2 con/run/cac thds, 5.7.24-l

\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_ Buffer Pool \_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_  
Size Free Bufs Pages Dirty Pages Hit Rate Memory Add'l Pool  
128.00k 8192 122876 154 1000 / 1000 2.05G

\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_ Page Statistics \_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_  
Reads Writes Created Reads/Sec Writes/Sec Creates/Sec  
141879 850350 157386 0.00 0.00 0.10

\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_ Insert Buffers \_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_  
Inserts Merged Recs Merges Size Free List Len Seg. Size  
0 0 1 0 2

\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_ Adaptive Hash Index \_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_  
Size Cells Used Node Heap Bufs Hash/Sec Non-Hash/Sec  
540.29k 1 3.80 3.90  
\------------------------------------------------------------------------------  
Buffer Pool  
Size : 某sql使用Buffer Pool的大小  
Free Bufs : Innodb\_buffer\_pool\_pages\_free的值,空页数;  
Pages: Innodb\_buffer\_pool\_pages\_data的值, 包含数据的页数(脏或干净)  
Dirty Pages : Innodb\_buffer\_pool\_pages\_dirty的值,当前的脏页数  
Hit Rate: 命中率  
Memory : Innodb\_buffer\_pool\_size 的值.  
Add'l Pool : innodb\_additional\_mem\_pool\_size的值  
Page Statistics  
Reads : Innodb\_pages\_read 的值,读取的页数  
Writes : Innodb\_pages\_written 的值,写入的页数  
Created : Innodb\_pages\_created 的值,创建的页数  
Reads/Sec : 每秒读取的页数  
Writes/Sec : 每秒写入的页数  
Creates/Sec : 每秒创建的页数  
Insert Buffers  
Inserts : 表示执行insert 次数  
Merged Recs : 表示执行 insert 索引树合并的次数  
Merges: insert 语句合并的次数  
Size: 写缓冲的大小  
Free List Len : 空闲列表长度  
Seg. Size: 段块大小  
Adaptive Hash Index 自适应哈希索引  
Size: 哈希索引占用大小  
Cells Used:  
Node Heap Bufs:  
Hash/Sec: 每秒哈希索引量  
Non-Hash/Sec: 每秒非哈希索引的量

推荐大家了解的知识

内存分为全局内存和线程内存。判断你的问题在哪里。  
全局内存你需要了解

1，innodb\_buffer\_pool\_size 。Innodb在mysql启动的时候一次性分配整个内存给bufferpool。占得比重很大，要重点关注。  
这个内存和你数据库访问量connections无关。如果你的数据访问量不大，但内存占用很高的话，就要要重点关注buffer pool相关的参数  
2，key\_buffer\_size。如果数据里面有很多myisam的表。这个参数就需要重点关注。所有相关表的请求都会占用到这块内存。  
线程内存你需要了解

首先这些参数并不是固定的，max\_connections \* 参数的值 然后加起来就可能是线程占用的缓存大小。如果你的访问量很大，连接数很多。下面参数就可能影响内存的占用。

每个参数都对应一种数据操作，所以你需要思考业务场景和sql代码到底哪一种操作多一些。  
read\_buffer\_size 顺序读rows  
sort\_buffer\_size 对表排序 sort by  
read\_rnd\_buffer\_size 随机读rows where条件多的  
join\_buffer\_size 表关联join多的  
tmp\_table\_size 临时表，通常是单行数据大，而且table整体很大的  
注意上面只是部分参数，要总体判断自己的原因大的方向在进入一点点找出根本原因。  
不定期更新一些数据库的经验总结，可以翻翻其他博客，我可能对某一个细节展开讲解。  
————————————————  
版权声明：本文为CSDN博主「如何在3年拿到50K」的原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接及本声明。  
原文链接：https://blog.csdn.net/kouryoushine/article/details/89354949