背景

生產(chǎn)環(huán)境mysql 5.7內(nèi)存占用超過90%以上，且一直下不來。截圖如下：

原因分析

1、確定mysql具體的占用內(nèi)存大小，通過命令：cat /proc/Mysql進(jìn)程ID/status查看

命令執(zhí)行后的結(jié)果比較多。

看到此處有必要延申一個知識點。innodb_buffer_pool_size

一、innodb_buffer_pool_size作用

InnoDB存儲引擎是MySQL中最常用的存儲引擎之一，它使用內(nèi)存緩存池（buffer pool）來緩存表中的數(shù)據(jù)和索引等信息。通過調(diào)整innodb_buffer_pool_size參數(shù)的大小，可以控制InnoDB存儲引擎能夠利用的內(nèi)存空間，進(jìn)而影響其緩存的數(shù)據(jù)量和索引數(shù)量。

innodb_buffer_pool_size設(shè)置的值較大時，InnoDB存儲引擎能夠緩存更多的數(shù)據(jù)和索引，從而減少磁盤I/O的次數(shù)，提高數(shù)據(jù)庫的訪問速度和性能。相反，如果緩存池設(shè)置過小，可能會導(dǎo)致頻繁的磁盤I/O操作，影響數(shù)據(jù)庫性能。

一般為物理內(nèi)存的60%-70%。

二、查看當(dāng)前配置的pool_size:

SHOW VARIABLES LIKE 'innodb_buffer_pool_size';

發(fā)現(xiàn)結(jié)果是64G（配置文件也可查看），這里就發(fā)現(xiàn)問題：實際使用的內(nèi)存量比配置的量多出了60G左右。

暫且把64G當(dāng)成正常占用多出來的當(dāng)成異常占用分析。

三、performance schema內(nèi)存占用量分析

show engine performance_schema status;

查看結(jié)果中的最后一行。發(fā)現(xiàn)占用了200多M。

四、排查MySQL為當(dāng)前session會話分配的內(nèi)存

查看session級別的buffer和cache占用內(nèi)存大小。

show variables where variable_name in ('binlog_cache_size','join_buffer_size','read_buffer_size','read_rnd_buffer_size','sort_buffer_size')

結(jié)果如下：

總共加起來接近800M。

查看當(dāng)前活躍的連接數(shù)

SELECT * FROM information_schema.processlist WHERE command != 'Sleep';

結(jié)果顯示差不多只有9個，加入每個都分配了全量的會話內(nèi)存，則差不多就是9G。（實際分配了多少需要根據(jù)當(dāng)前會話執(zhí)行的SQL判斷，比如有無使用到排序、有沒有使用join等）。上邊的算完頂多才10G，還有50多G的消耗，也就意味著還有其他的占用。

五、排查當(dāng)前臨時表占用內(nèi)存情況

查看tmp_table_size臨時表配置的內(nèi)存大?。?/h4>

線程級別參數(shù)，實際限制從 tmp_table_size 和 max_heap_table_size 兩個變量的的值中取較小值。

show variables where variable_name in ('tmp_table_size','max_heap_table_size')

補(bǔ)充知識點一：臨時表

如果內(nèi)存中的臨時表超出限制，MySQL自動將其轉(zhuǎn)換為磁盤上的MyISAM表。如果要執(zhí)行許多 GROUP BY查詢，可以增加tmp_table_size的值（或如有必要，也可以使用max_heap_table_size）。

執(zhí)行計劃中Extra字段包含有“Using temporary” 時會產(chǎn)生臨時表。

MySQL中臨時表主要有兩類，包括外部臨時表和內(nèi)部臨時表。外部臨時表是通過語句create temporary table...創(chuàng)建的臨時表，臨時表只在本會話有效，會話斷開后，臨時表數(shù)據(jù)會自動清理。內(nèi)部臨時表主要有兩類，一類是information_schema中臨時表，另一類是會話執(zhí)行查詢時，如果執(zhí)行計劃中包含有“Using temporary”時，會產(chǎn)生臨時表。內(nèi)部臨時表與外部臨時表的一個區(qū)別在于，我們看不到內(nèi)部臨時表的表結(jié)構(gòu)定義文件frm。而外部臨時表的表定義文件frm，一般是以#sql{進(jìn)程id}_{線程id}_序列號組成，因此不同會話可以創(chuàng)建同名的臨時表。

查看當(dāng)前是否有臨時表產(chǎn)生

show global status like '%tmp%'

發(fā)現(xiàn)頻繁使用了臨時表，并且出現(xiàn)了因內(nèi)存臨時表不夠而使用到磁盤臨時表。由于臨時表占用的內(nèi)存具體大小可能無法準(zhǔn)確計算得出（因為使用完會回收，但是肯定存在當(dāng)前未被回收情況）。

補(bǔ)充知識點二：Mysql內(nèi)存管理模塊：

MySQL的內(nèi)存分配使用了系統(tǒng)glibc，而glibc本身的內(nèi)存分配算法存在缺陷，導(dǎo)致內(nèi)存釋放不完全，產(chǎn)生內(nèi)存碎片?？梢酝ㄟ^gdb命令手動回收內(nèi)存碎片：

gdb --batch --pid ‘pidof mysqld’ --ex 'call malloc_trim(0)'；

但是在生產(chǎn)環(huán)境這個操作應(yīng)該謹(jǐn)慎使用。

此外，將MySQL的內(nèi)存分配機(jī)制修改為jemalloc，可以更好的釋放內(nèi)存。

六、問題總結(jié)和解決思路

總結(jié)一下MySQL內(nèi)存使用率高且不釋放的應(yīng)對方法：

• 繼續(xù)加大內(nèi)存（如果參數(shù)調(diào)無可調(diào)時選擇）；
• 修改減小innodb_buffer_pool_size參數(shù)（犧牲一定innodb性能）；
• 排查消耗內(nèi)存的慢SQL，及時優(yōu)化；
• 檢查相關(guān)session參數(shù)是否設(shè)置合理，比如join_buffer_size、query_cache_size是否設(shè)置過大；
• 使用gdb回收內(nèi)存碎片（生產(chǎn)環(huán)境謹(jǐn)慎操作）：gdb --batch --pid ‘pidof mysqld’ --ex 'call malloc_trim(0)'；
• 對MySQL進(jìn)程配置jemalloc內(nèi)存管理模塊；
• 配置讀寫分離，將讀操作應(yīng)用到從庫，減少對主庫的影響；

以上就是MySQL內(nèi)存使用率高且不釋放問題排查與總結(jié)的詳細(xì)內(nèi)容，更多關(guān)于MySQL內(nèi)存使用率高且不釋放的資料請關(guān)注腳本之家其它相關(guān)文章！

最新評論