背景

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

原因分析

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

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

看到此處有必要延申一個(gè)知識(shí)點(diǎn)。innodb_buffer_pool_size

一、innodb_buffer_pool_size作用

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

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

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

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

SHOW VARIABLES LIKE 'innodb_buffer_pool_size';

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

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

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

show engine performance_schema status;

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

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

查看session級(jí)別的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é)果如下：

總共加起來(lái)接近800M。

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

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

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

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

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

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

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

補(bǔ)充知識(shí)點(diǎn)一：臨時(shí)表

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

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

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

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

show global status like '%tmp%'

發(fā)現(xiàn)頻繁使用了臨時(shí)表，并且出現(xiàn)了因內(nèi)存臨時(shí)表不夠而使用到磁盤(pán)臨時(shí)表。由于臨時(shí)表占用的內(nèi)存具體大小可能無(wú)法準(zhǔn)確計(jì)算得出（因?yàn)槭褂猛陼?huì)回收，但是肯定存在當(dāng)前未被回收情況）。

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

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

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

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

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

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

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

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

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

最新評(píng)論