[[414461]]

本文轉(zhuǎn)載自微信公眾號(hào)「數(shù)據(jù)和云」，作者崔虎龍 。轉(zhuǎn)載本文請(qǐng)聯(lián)系數(shù)據(jù)和云公眾號(hào)。

MySQL使用內(nèi)存上升90%!在運(yùn)維過(guò)程中50%的幾率，會(huì)碰到這樣的問(wèn)題。算是比較普遍的現(xiàn)象。

MySQL內(nèi)存使用率過(guò)高，有諸多原因。普遍原因是使用不當(dāng)，還有MySQL本身缺陷導(dǎo)致的。到底是哪方面的問(wèn)題，那就需要一個(gè)一個(gè)進(jìn)行排查。

下面介紹排查思路：

1.參數(shù)配置需要確認(rèn)，內(nèi)存是否設(shè)置合理

MySQL內(nèi)存分為全局和線程級(jí)：

• 全局內(nèi)存(如：innodb_buffer_pool_size，key_buffer_size，innodb_log_buffer_size)。
• 線程級(jí)內(nèi)存：(如：thread，read，sort，join,tmp 等)只是在需要的時(shí)候才分配，并且在操作完畢之后就釋放。
• 線程級(jí)內(nèi)存：線程緩存每個(gè)連接到MySQL服務(wù)器的線程都需要有自己的緩沖。默認(rèn)分配thread_stack(256K,512k)，空閑時(shí)這些內(nèi)存是默認(rèn)使用，除此之外還有網(wǎng)絡(luò)緩存、表緩存等。大致評(píng)估會(huì)在1M~3M這樣的情況?？赏ㄟ^(guò)pmap觀察內(nèi)存變化：

mysql> SELECT @@query_cache_size, 
     @@key_buffer_size, 
     @@innodb_buffer_pool_size , 
     @@innodb_log_buffer_size , 
     @@tmp_table_size , 
     @@read_buffer_size, 
     @@sort_buffer_size, 
     @@join_buffer_size , 
     @@read_rnd_buffer_size, 
     @@binlog_cache_size, 
     @@thread_stack, 
     (SELECT COUNT(host) FROM  information_schema.processlist where command<>'Sleep')\G; 
*************************** 1. row *************************** 
@@query_cache_size:1048576 
@@key_buffer_size:8388608 
@@innodb_buffer_pool_size:268435456 
@@innodb_log_buffer_size:8388608 
@@tmp_table_size:16777216 
@@read_buffer_size:131072 
@@sort_buffer_size:1048576 
@@join_buffer_size:1048576 
@@read_rnd_buffer_size:2097152 
@@binlog_cache_size:8388608 
@@thread_stack:524288 
(select count(host) from information_schema.processlist where command<>'Sleep'): 1 

備注：query_cache_size 8.0版本已經(jīng)廢棄掉了。

2.存儲(chǔ)過(guò)程&函數(shù)&觸發(fā)器&視圖

目前積累的使用經(jīng)驗(yàn)中，存儲(chǔ)過(guò)程&函數(shù)&觸發(fā)器&視圖 在MySQL場(chǎng)景下是不適合的。性能不好，又容易發(fā)現(xiàn)內(nèi)存不釋放的問(wèn)題，所以建議盡量避免。

• 存儲(chǔ)過(guò)程&函數(shù)

MySQL 5.7

mysql> SELECT db,type,count(*)  
FROM mysql.proc 
WHERE db not in ('mysql','information_schema','performance_schema','sys') 
GROUP BY db, type; 

MySQL 8.0

mysql> SELECT  Routine_schema, Routine_type 
FROM information_schema.Routines 
WHERE  Routine_schema not in ('mysql','information_schema','performance_schema','sys') 
GROUP BY Routine_schema, Routine_type; 

• 視圖

mysql> SELECT  TABLE_SCHEMA , COUNT(TABLE_NAME)  
FROM information_schema.VIEWS 
WHERE TABLE_SCHEMA not in ('mysql','information_schema','performance_schema','sys') 
GROUP BY TABLE_SCHEMA ; 

• 觸發(fā)器

mysql> SELECT TRIGGER_SCHEMA, count(*)  
 FROM information_schema.triggers  
WHERE  TRIGGER_SCHEMA not in ('mysql','information_schema','performance_schema','sys') 
GROUP BY TRIGGER_SCHEMA; 

上面通過(guò)MySQL配置參數(shù)和設(shè)計(jì)層面檢查了是否有可能內(nèi)存泄露的問(wèn)題。下面看看怎樣分析實(shí)際使用的內(nèi)存情況。

3.系統(tǒng)庫(kù)統(tǒng)計(jì)查詢

• 總內(nèi)存使用

mysql> SELECT  
SUM(CAST(replace(current_alloc,'MiB','')  as DECIMAL(10, 2))  )  
FROM sys.memory_global_by_current_bytes 
WHERE current_alloc like '%MiB%'; 

• 分事件統(tǒng)計(jì)內(nèi)存

mysql> SELECT event_name,     
SUM(CAST(replace(current_alloc,'MiB','')  as DECIMAL(10, 2))  )     
FROM sys.memory_global_by_current_bytes     
WHERE current_alloc like '%MiB%' GROUP BY event_name   
     ORDER BY SUM(CAST(replace(current_alloc,'MiB','')  as DECIMAL(10, 2))  ) DESC ; 
 
mysql> SELECT event_name, 
       sys.format_bytes(CURRENT_NUMBER_OF_BYTES_USED) 
FROM performance_schema.memory_summary_global_by_event_name 
ORDER BY  CURRENT_NUMBER_OF_BYTES_USED DESC 
LIMIT 10; 

• 賬號(hào)級(jí)別統(tǒng)計(jì)

mysql> SELECT user,event_name,current_number_of_bytes_used/1024/1024 as MB_CURRENTLY_USED 
FROM performance_schema.memory_summary_by_account_by_event_name 
WHERE host<>"localhost" 
ORDER BY  current_number_of_bytes_used DESC LIMIT 10; 

備注：有必要統(tǒng)計(jì)用戶級(jí)別內(nèi)存，因?yàn)楹芏喹h(huán)境對(duì)接了第三方插件，模擬從庫(kù)，這些插件容易內(nèi)存不釋放。

• 線程對(duì)應(yīng)sql語(yǔ)句，內(nèi)存使用統(tǒng)計(jì)

SELECT thread_id, 
       event_name, 
       sys.format_bytes(CURRENT_NUMBER_OF_BYTES_USED)  
FROM performance_schema.memory_summary_by_thread_by_event_name 
ORDER BY  CURRENT_NUMBER_OF_BYTES_USED DESC 
LIMIT 20; 

SELECT m.thread_id tid, 
       m.user, 
       esc.DIGEST_TEXT, 
       m.current_allocated, 
       m.total_allocated 
FROM sys.memory_by_thread_by_current_bytes m, 
     performance_schema.events_statements_current esc 
WHERE m.`thread_id` = esc.THREAD_ID \G 

• 打開所有內(nèi)存性能監(jiān)控，會(huì)影響性能，需注意

#打開 
UPDATE performance_schema.setup_instruments SET ENABLED = 'YES' WHERE NAME LIKE 'memory/%'; 
#關(guān)閉 
UPDATE performance_schema.setup_instruments SET ENABLED = 'NO' WHERE NAME LIKE 'memory/%'; 
#查看使用 
SELECT * FROM performance_schema.memory_summary_global_by_event_name         
WHERE EVENT_NAME LIKE 'memory/%'  
ORDER BY CURRENT_NUMBER_OF_BYTES_USED DESC; 

• 系統(tǒng)表內(nèi)存監(jiān)控信息

select * from sys.x$memory_by_host_by_current_bytes; 
select * from sys.x$memory_by_thread_by_current_bytes; 
select * from sys.x$memory_by_user_by_current_bytes; 
select * from sys.x$memory_global_by_current_bytes; 
select * from sys.x$memory_global_total; 
select * from performance_schema.memory_summary_by_account_by_event_name; 
select * from performance_schema.memory_summary_by_host_by_event_name; 
select * from performance_schema.memory_summary_by_thread_by_event_name; 
select * from performance_schema.memory_summary_by_user_by_event_name; 
select * from performance_schema.memory_summary_global_by_event_name; 

備注：找到對(duì)應(yīng)問(wèn)題事件或線程后，可以進(jìn)行排查，解決內(nèi)存高的問(wèn)題。

4.系統(tǒng)工具查看內(nèi)存

1)top命令

顯示系統(tǒng)中各個(gè)進(jìn)程的資源占用狀況。

• Shift + m 鍵 查看內(nèi)存排名實(shí)際使用內(nèi)存情況，關(guān)注RES指標(biāo)。

2)free命令

free-h 命令顯示系統(tǒng)內(nèi)存的使用情況，包括物理內(nèi)存、交換內(nèi)存(swap)和內(nèi)核緩沖區(qū)內(nèi)存。

• used列顯示已經(jīng)被使用的物理內(nèi)存和交換空間。
• buff/cache列顯示被buffer和cache使用的物理內(nèi)存大小。
• available列顯示還可以被應(yīng)用程序使用的物理內(nèi)存大小。
• Swap行(第三行)是交換空間的使用情況。

3)ps命令

MySQL相關(guān)進(jìn)程使用內(nèi)存情況。

shell > ps eo user,pid,vsz,rss $(pgrep -f 'mysqld') 
USER         PID    VSZ   RSS 
root      215945  12960  2356 
mysql     217246 1291540 241824 
root      221056  12960  2428 
mysql     374243 1336924 408752 

4)pmap 命令

pmap是Linux調(diào)試及運(yùn)維一個(gè)很好的工具，查看進(jìn)程的內(nèi)存映像信息。

用法1：執(zhí)行一段時(shí)間記錄數(shù)據(jù)變化，最少20個(gè)記錄，下面22837是MySQL pid

while true; do pmap -d  22837  | tail -1; sleep 2; done 

用法2：linux 命令pmap MySQL pid導(dǎo)出內(nèi)存，下面22837是MySQL pid

pmap -X -p 22837 > /tmp/memmysql.txt 

RSS就是這個(gè)process實(shí)際占用的物理內(nèi)存。

Dirty: 臟頁(yè)的字節(jié)數(shù)(包括共享和私有的)。

Mapping: 占用內(nèi)存的文件、或[anon](分配的內(nèi)存)、或[stack](堆棧)。

writeable/private：進(jìn)程所占用的私有地址空間大小，也就是該進(jìn)程實(shí)際使用的內(nèi)存大小。

1.首先使用/top/free/ps在系統(tǒng)級(jí)確定是否有內(nèi)存泄露。如有，可以從top輸出確定哪一個(gè)process。

2.pmap工具是能幫助確定process是否有memory leak。確定memory leak的原則：writeable/private (‘pmap –d’輸出)如果在做重復(fù)的操作過(guò)程中一直保持穩(wěn)定增長(zhǎng)，那么一定有內(nèi)存泄露。

總結(jié)

對(duì)于MySQL內(nèi)存泄露來(lái)說(shuō):

• 從參數(shù)設(shè)置和設(shè)計(jì)上盡量合理
• 通過(guò)ps庫(kù)進(jìn)行排查
• linux工具進(jìn)一步確認(rèn)
• 官方bug里memory leak查找，是否存在修復(fù)的版本

以上排查里都沒(méi)有找到原因，可以換下服務(wù)器或主從切換觀察。也可以進(jìn)行版本升級(jí)(代價(jià)不小)。

如能提供一個(gè)實(shí)際環(huán)境，也可以一步一步進(jìn)行調(diào)試，抓取內(nèi)存變化，確定是什么導(dǎo)致內(nèi)存泄露的問(wèn)題。之后提交bug，讓官方提供修復(fù)。

關(guān)于作者

崔虎龍，云和恩墨MySQL技術(shù)顧問(wèn)，長(zhǎng)期服務(wù)于金融、游戲、物流等行業(yè)的數(shù)據(jù)中心，設(shè)計(jì)數(shù)據(jù)存儲(chǔ)架構(gòu)，并熟悉數(shù)據(jù)中心運(yùn)營(yíng)管理的流程及規(guī)范，自動(dòng)化運(yùn)維等。擅長(zhǎng)MySQL、Redis、MongoDB數(shù)據(jù)庫(kù)高可用設(shè)計(jì)和運(yùn)維故障處理、備份恢復(fù)、升級(jí)遷移、性能優(yōu)化。自學(xué)通過(guò)了MySQL OCP 5.6和MySQL OCP 5.7認(rèn)證。2年多開發(fā)經(jīng)驗(yàn)，10年數(shù)據(jù)庫(kù)運(yùn)維工作經(jīng)驗(yàn)，其中專職做MySQL工作8年;曾經(jīng)擔(dān)任過(guò)項(xiàng)目經(jīng)理、數(shù)據(jù)庫(kù)經(jīng)理、數(shù)據(jù)倉(cāng)庫(kù)架構(gòu)師、MySQL技術(shù)專家、DBA等職務(wù);涉及行業(yè)：金融(銀行、理財(cái))、物流、游戲、醫(yī)療、重工業(yè)等。