mysql資料庫oom（mysql資料庫的優點）

本文目錄一覽：

1、查看mysql是什麼對象導致被OOM了
2、敲重點！MySQL數據查詢太多會OOM嗎？
3、如何避免mysql被oom-killer殺死
4、oom 是什麼意思

查看mysql是什麼對象導致被OOM了

查看 /proc/meminfo

Tips：

「大內存頁」也稱傳統大頁、大頁內存等有助於 Linux 進行虛擬內存的管理，標準的內存頁為 4KB，這裡使用「大內存頁」最大可以定義 1GB 的頁面大小，在系統啟動期間可以使用「大內存頁」為應用程序預留一部分內存，這部分內存被佔用且永遠不會被交換出內存，它會一直保留在那裡，直到改變配置。（詳細介紹請看下面鏈接官方解釋）

那麼這麼大頁內存是分配給誰的呢？

查詢一下：

shell /proc/sys/vm/hugetlb_shm_group

shell id 27

uid=27(mysql) gid=27(mysql) groups=27(mysql)

hugetlb_shm_group 文件里填的是指定大頁內存使用的用戶組 id，這裡查看到是 MySQL 組 id，那既然是給 MySQL 的為什麼 free 等於 total，並且 mysql 還只有 20 多 G 實際使用內存呢？

原來在 MySQL 中還有專門啟用大內存頁的參數，在 MySQL 大內存頁稱為 large page。

查看 MySQL 配置文件

發現配置文件中確實有 large-page 配置，但出於禁用狀態。

後與業務確認，很早之前確實啟用過 mysql 的 large page，不過後面禁用了。排查到這基本就有了結論。

結論

這套環境之前開啟了 20000 的大內存頁，每頁大小為 2MB，佔用了 40G 內存空間，給 MySQL 使用，並且 MySQL 開啟了 large page，但後來不使用的時候，只關閉了 MySQL 端的 large page 參數，但沒有實際更改主機的關於大內存頁的配置，所以導致，實際上主機上的還存在 20000 的大內存頁，並且沒在使用，這一部分長期空閑，並且其他程序不能使用。

所以 MySQL 在使用 20G 內存左右，整個主機內存就飽和了，然後在部分條件下，就觸發了 OOM，導致 mysqld 被 kill，但主機上又有 mysqld_safe 守護程序，所以又再次給拉起來，就看到了文章初的偶爾連接不上的現象。

敲重點！MySQL數據查詢太多會OOM嗎？

我的主機內存只有100G，現在要全表掃描一個200G大表，會不會把DB主機的內存用光？

邏輯備份時，可不就是做整庫掃描嗎？若這樣就會把內存吃光，邏輯備份不是早就掛了？

所以大表全表掃描，看起來應該沒問題。這是為啥呢？

假設，我們現在要對一個200G的InnoDB表db1. t，執行一個全表掃描。當然，你要把掃描結果保存在客戶端，會使用類似這樣的命令：

InnoDB數據保存在主鍵索引上，所以全表掃描實際上是直接掃描表t的主鍵索引。這條查詢語句由於沒有其他判斷條件，所以查到的每一行都可以直接放到結果集，然後返回給客戶端。

那麼，這個「結果集」存在哪裡呢？

服務端無需保存一個完整結果集。取數據和發數據的流程是這樣的：

查詢結果發送流程

可見：

所以MySQL其實是「邊讀邊發」。這意味著，若客戶端接收得慢，會導致MySQL服務端由於結果發不出去，這個事務的執行時間變長。

比如下面這個狀態，就是當客戶端不讀 socket receive buffer 內容時，在服務端show processlist看到的結果。

若看到State一直是「Sending to client」，說明伺服器端的網路棧寫滿了。

若客戶端使用–quick參數，會使用mysql_use_result方法：讀一行處理一行。假設某業務的邏輯較複雜，每讀一行數據以後要處理的邏輯若很慢，就會導致客戶端要過很久才取下一行數據，可能就會出現上圖結果。

因此，對於正常的線上業務來說，若一個查詢的返回結果不多，推薦使用mysql_store_result介面，直接把查詢結果保存到本地內存。

當然前提是查詢返回結果不多。如果太多，因為執行了一個大查詢導致客戶端佔用內存近20G，這種情況下就需要改用mysql_use_result介面。

若你在自己負責維護的MySQL里看到很多個線程都處於「Sending to client」，表明你要讓業務開發同學優化查詢結果，並評估這麼多的返回結果是否合理。

若要快速減少處於這個狀態的線程的話，可以將net_buffer_length設置更大。

有時，實例上看到很多查詢語句狀態是「Sending data」，但查看網路也沒什麼問題，為什麼Sending data要這麼久？

一個查詢語句的狀態變化是這樣的：

即「Sending data」並不一定是指「正在發送數據」，而可能是處於執行器過程中的任意階段。比如，你可以構造一個鎖等待場景，就能看到Sending data狀態。

讀全表被鎖：

Sending data狀態

可見session2是在等鎖，狀態顯示為Sending data。

所以，查詢的結果是分段發給客戶端，因此掃描全表，查詢返回大量數據，並不會把內存打爆。

以上是server層的處理邏輯，在InnoDB引擎里又是怎麼處理？

InnoDB內存的一個作用，是保存更新的結果，再配合redo log，避免隨機寫盤。

內存的數據頁是在Buffer Pool (簡稱為BP)管理，在WAL里BP起加速更新的作用。

BP還能加速查詢。

而BP對查詢的加速效果，依賴於一個重要的指標，即：內存命中率。

可以在show engine innodb status結果中，查看一個系統當前的BP命中率。一般情況下，一個穩定服務的線上系統，要保證響應時間符合要求的話，內存命中率要在99%以上。

執行show engine innodb status ，可以看到「Buffer pool hit rate」字樣，顯示的就是當前的命中率。比如下圖命中率，就是100%。

若所有查詢需要的數據頁都能夠直接從內存得到，那是最好的，對應命中率100%。

InnoDB Buffer Pool的大小是由參數 innodb_buffer_pool_size確定，一般建議設置成可用物理內存的60%~80%。

在大約十年前，單機的數據量是上百個G，而物理內存是幾個G；現在雖然很多伺服器都能有128G甚至更高的內存，但是單機的數據量卻達到了T級別。

所以，innodb_buffer_pool_size小於磁碟數據量很常見。若一個 Buffer Pool滿了，而又要從磁碟讀入一個數據頁，那肯定是要淘汰一個舊數據頁的。

使用的最近最少使用 (Least Recently Used, LRU)演算法，淘汰最久未使用數據。

InnoDB管理BP的LRU演算法，是用鏈表實現的：

最終就是最久沒有被訪問的數據頁Pm被淘汰。

若此時要做一個全表掃描，會怎樣？若要掃描一個200G的表，而這個表是一個歷史數據表，平時沒有業務訪問它。

那麼，按此演算法掃描，就會把當前BP里的數據全部淘汰，存入掃描過程中訪問到的數據頁的內容。也就是說BP里主要放的是這個歷史數據表的數據。

對於一個正在做業務服務的庫，這可不行呀。你會看到，BP內存命中率急劇下降，磁碟壓力增加，SQL語句響應變慢。

所以，InnoDB不能直接使用原始的LRU。InnoDB對其進行了優化。

InnoDB按5:3比例把鏈表分成New區和Old區。圖中LRU_old指向的就是old區域的第一個位置，是整個鏈表的5/8處。即靠近鏈表頭部的5/8是New區域，靠近鏈表尾部的3/8是old區域。

改進後的LRU演算法執行流程：

該策略，就是為了處理類似全表掃描的操作量身定製。還是掃描200G歷史數據表：

可以看到，這個策略最大的收益，就是在掃描這個大表的過程中，雖然也用到了BP，但對young區完全沒有影響，從而保證了Buffer Pool響應正常業務的查詢命中率。

MySQL採用的是邊算邊發的邏輯，因此對於數據量很大的查詢結果來說，不會在server端保存完整的結果集。所以，如果客戶端讀結果不及時，會堵住MySQL的查詢過程，但是不會把內存打爆。

而對於InnoDB引擎內部，由於有淘汰策略，大查詢也不會導致內存暴漲。並且，由於InnoDB對LRU演算法做了改進，冷數據的全表掃描，對Buffer Pool的影響也能做到可控。

全表掃描還是比較耗費IO資源的，所以業務高峰期還是不能直接在線上主庫執行全表掃描的。

如何避免mysql被oom-killer殺死

OOM Killer(Out of Memory Killer) 是當系統內存嚴重不足時 linux 內核採用的殺掉進程，釋放內存的機制。

OOM Killer 通過檢查所有正在運行的進程，然後根據自己的演算法給每個進程一個 badness 分數，擁有最高 badness 分數的進程將會在內存不足時被殺掉。

它打分的演算法如下:

某一個進程和它所有的子進程都佔用了很多內存的將會打一個高分。

為了釋放足夠的內存來解決這種情況，將殺死最少數量的進程（最好是一個進程）。

內核進程和其他較重要的進程會被打成相對較低的分。

上面打分的標準意味著，當 OOM killer 選擇殺死的進程時，將選擇一個使用大量內存，有很多子進程且不是系統進程的進程。

簡單來講，oom-killer 的原則就是損失最小、收益最大，因此它會讓殺死的進程數儘可能小、釋放的內存儘可能大。在資料庫伺服器上，MySQL 被分配的內存一般不會小，因此容易成為 oom-killer 選擇的對象。

「既然發生了 OOM，那必然是內存不足，內存不足這個問題產生原因很多。

首先第一個就是 MySQL 自身內存的規劃有問題，這就涉及到 mysql 相應的配置參數。

另一個可以想到的原因就是一般部署 MySQL 的伺服器，都會部署很多的監控和定時任務腳本，而這些腳本往往缺少必要的內存限制，導致在高峰期的時候佔用大量的內存，導致觸發 Linux 的 oom-killer 機制，最終 MySQL 無辜躺槍犧牲。」

oom 是什麼意思