首頁  >  文章  >  資料庫  >  一起聊聊MySQL資料查詢太多會OOM嗎

一起聊聊MySQL資料查詢太多會OOM嗎

WBOY
WBOY轉載
2022-01-07 18:14:412413瀏覽

這篇文章帶給大家如果MySQL資料查詢太多會不會OOM的相關知識,希望對大家有幫助。

一起聊聊MySQL資料查詢太多會OOM嗎

主機記憶體只有100G,現在要全表掃描一個200G大表,會不會把DB主機的記憶體用光?

邏輯備份時,可不就是做整庫掃描嗎?若這樣就會把記憶體吃光,邏輯備份不是早就掛了?

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

全表掃描對server層的影響

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

mysql -h$host -P$port -u$user -p$pwd -e 
 "select * from db1.t" > $target_file

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

那麼,這個「結果集」存在哪裡呢?

服務端無需保存一個完整結果集。取資料和發送資料的流程是這樣的:

取得一行,寫到**“net_buffer”。這塊記憶體的大小是由參數「net_buffer_length」**定義,預設16k

重複取得行,直到**“net_buffer”**寫滿,呼叫網路介面發出去

#若發送成功,就清空**“net_buffer”,然後繼續取下一行,並寫入“net_buffer”**

若發送函數返回**“EAGAIN”或“WSAEWOULDBLOCK”**,就表示本地網路棧(socket send buffer)寫滿了,進入等待。直到網路堆疊重新可寫,再繼續發送

查詢結果發送流程

一起聊聊MySQL資料查詢太多會OOM嗎

#可見:

  • 一個查詢在發送過程中,佔用的MySQL內部的記憶體最大就是**“net_buffer_length”**這麼大,不會達到200G

  • socket send buffer 也不可能達到200G(默認定義/proc/sys/net/core/wmem_default),若socket send buffer被寫滿,就會暫停讀取資料的流程

所以MySQL其實是“邊讀邊發” 。這意味著,若客戶端接收得慢,會導致MySQL服務端因為結果發不出去,這個交易的執行時間變長。

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

服務端發送阻塞

一起聊聊MySQL資料查詢太多會OOM嗎

若看到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要這麼久?

一個查詢語句的狀態變更是這樣的:

  • MySQL查詢語句進入執行階段後,先把狀態設定成「Sending data」

  • #然後,發送執行結果的列相關的資訊(meta data) 給客戶端

  • 再繼續執行語句的流程

  • ##執行完成後,把狀態設定成空字串。

即“Sending data”並不一定是指“正在傳送資料”,而可能是處於執行器過程中的任意階段。例如,你可以建構一個鎖等待場景,就能看到Sending data狀態。

讀取全表被鎖定:

一起聊聊MySQL資料查詢太多會OOM嗎

Sending data狀態

一起聊聊MySQL資料查詢太多會OOM嗎

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

  • 只當一個執行緒處於「等待客戶端接收結果」的狀態,才會顯示"Sending to client"

  • 若顯示成「Sending data ”,它的意思只是“正在執行”

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

以上是server層的處理邏輯,在InnoDB引擎裡又怎麼處理?

全表掃描對InnoDB的影響

#InnoDB記憶體的一個作用,就是儲存更新的結果,再配合redo log,避免隨機寫盤。

記憶體的資料頁是在Buffer Pool (簡稱BP)管理,在WAL裡BP扮演加速更新的角色。

BP還能加速查詢。

由於WAL,當交易提交時,磁碟上的資料頁是舊的,若這時馬上有個查詢來讀該資料頁,是不是要馬上把redo log應用到資料頁?

不需要。因為此時,記憶體資料頁的結果是最新的,直接讀取記憶體頁即可。這時查詢無需讀取磁碟,直接從內存取結果,速度很快。所以,Buffer Pool能加速查詢。

而BP對查詢的加速效果,依賴一個重要的指標,即:記憶體命中率。

可以在show engine innodb status結果中,查看一個系統目前的BP命中率。一般情況下,一個穩定服務的線上系統,要確保回應時間符合要求的話,記憶體命中率要在99%以上。

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

一起聊聊MySQL資料查詢太多會OOM嗎

若所有查詢需要的資料頁都能夠直接從記憶體得到,那是最好的,對應命中率100%。

InnoDB Buffer Pool的大小是由參數 **“innodb_buffer_pool_size”**確定,一般建議設定成可用實體記憶體的60%~80%。

在大約十年前,單機的資料量是上百個G,而實體內存是幾個G;現在雖然很多伺服器都能有128G甚至更高的內存,但是單機的資料量卻達到了T級。

所以,**「innodb_buffer_pool_size」**小於磁碟資料量很常見。若一個 Buffer Pool滿了,而又要從磁碟讀入一個資料頁,那肯定是要淘汰一個舊資料頁的。

InnoDB記憶體管理

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

基本LRU演算法

InnoDB管理BP的LRU演算法,是用鍊錶實作的:

  • state1,鍊錶頭部是P1,表示P1是最近剛被存取過的資料頁

  • 此時,一個讀取請求存取P3,因此變成狀態2,P3被移到最前面

  • 狀態3表示,這次存取的資料頁不存在於鍊錶,所以需要在BP中新申請一個資料頁Px,加到鍊錶頭。但由於記憶體已滿,不能申請新記憶體。於是清空鍊錶末端Pm資料頁內存,存入Px的內容,放到鍊錶頭部

最終就是最久沒有被存取的資料頁Pm被淘汰。

若此時要做一個全表掃描,會咋樣?若要掃描一個200G的表,而這個表是一個歷史資料表,平時沒有業務存取它。

那麼,按此演算法掃描,就會把目前BP裡的資料全部淘汰,存入掃描過程中存取到的資料頁的內容。也就是說BP裡主要放的是這個歷史資料表的資料。

對於一個正在做業務服務的函式庫,這可不行呀。你會看到,BP記憶體命中率急遽下降,磁碟壓力增加,SQL語句回應變慢。

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

一起聊聊MySQL資料查詢太多會OOM嗎

改進的LRU演算法

InnoDB以5:3比例將鍊錶分成New區和Old區。圖中LRU_old指向的就是old區域的第一個位置,是整個鍊錶的5/8處。即靠近鍊錶頭部的5/8是New區域,靠近鍊錶尾部的3/8是old區域。

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

狀態1,要存取P3,由於P3在New區,和最佳化前LRU一樣,將其移到鍊錶頭部=》狀態2

之後要存取一個新的不存在於目前鍊錶的資料頁,這時依然是淘汰掉資料頁Pm,但新插入的資料頁Px,是放在**「LRU_old」**處

處於old區的資料頁,每次被存取的時候都要做如下判斷:

若該資料頁在LRU鍊錶中存在的時間超過1s,就把它移到鍊錶頭部

若此資料頁在LRU鍊錶中存在的時間短於1s,位置保持不變。 1s是由參數**“innodb_old_blocks_time”**控制,預設值1000,單位ms。

此策略,就是為了處理類似全表掃描的操作量身訂做。還是掃描200G歷史資料表:

4. 掃描過程中,需要新插入的資料頁,都被放到old區域

5. 一個資料頁裡面有多筆記錄,這個資料頁會被多次存取到,但由於是順序掃描,這個資料頁第一次被存取和最後一次被造訪的時間間隔不會超過1秒,因此還是會被保留在old區域

6. 再繼續掃描後續的數據,之前的這個數據頁之後也不會再被訪問到,於是始終沒有機會移到鍊錶頭部(New區),很快就會被淘汰出去。

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

小結

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

而對於InnoDB引擎內部,由於有淘汰策略,大查詢也不會導致記憶體飆升。而且,由於InnoDB對LRU演算法做了改進,冷資料的全表掃描,對Buffer Pool的影響也能做到可控。

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

推薦學習:mysql影片教學

#

以上是一起聊聊MySQL資料查詢太多會OOM嗎的詳細內容。更多資訊請關注PHP中文網其他相關文章!

陳述:
本文轉載於:juejin.im。如有侵權,請聯絡admin@php.cn刪除