MySQL 8.0不再擔心被垃圾SQL搞爆記憶體

来源:https://www.cnblogs.com/greatsql/archive/2023/05/16/17403600.html
-Advertisement-
Play Games

MySQL 8.0.28引入的新功能 MySQL 8.0.28開始,新增一個特性,支持監控統計並限制各個連接(會話)的記憶體消耗,避免大量用戶連接因為執行垃圾SQL消耗過多記憶體,造成可能被OOM kill的風險。 首先,需要先設置系統選項 global_connection_memory_tracki ...


MySQL 8.0.28引入的新功能

MySQL 8.0.28開始,新增一個特性,支持監控統計限制各個連接(會話)的記憶體消耗,避免大量用戶連接因為執行垃圾SQL消耗過多記憶體,造成可能被OOM kill的風險。

首先,需要先設置系統選項 global_connection_memory_tracking = 1,之後可以通過系統狀態變數 Global_connection_memory 查看當前所有連接消耗的記憶體總量:

mysql> show global status like 'Global_connection_memory';
+--------------------------+---------+
| Variable_name            | Value   |
+--------------------------+---------+
| Global_connection_memory | 1122912 |
+--------------------------+---------+

系統選項 global_connection_memory_tracking 可以全局開啟,也可以在單個會話中獨立開啟。如果是全局開啟,則會針對所有連接統計記憶體消耗情況,包括系統內部線程,以及root用戶創建的連接;如果是單個會話中獨立開啟,則只會統計當前會話連接的記憶體消耗。此外,InnoDB buffer pool不在統計範圍內。

可以通過設置選項 connection_memory_chunk_size 來控制記憶體統計更新頻率,該選項預設值為8KB,也就是當記憶體使用變化超過8KB時,才會更新統計結果。

可以調整每個會話連接可使用記憶體上限,由選項 connection_memory_limit 定義其限制,預設值及最大值都是 18446744073709551615,這個預設值太大了,等同於沒有限制。如果線上經常運行垃圾SQL導致MySQL記憶體消耗過大的話,可以適當調低這個選項。

如何在評估一條SQL可能要消耗多少記憶體呢?可以先調整選項值 connection_memory_limit = 2097152,即調低到2MB。然後以普通用戶身份(沒有SUPER、SYSTEM_VARIABLES_ADMIN、SESSION_VARIABLES_ADMIN等許可權)執行相應的SQL,如果預估需要消耗的記憶體超過2MB,則會發出類似下麵的報錯,並且這個連接會被殺掉斷開:

mysql> select @@global.connection_memory_limit;
+----------------------------------+
| @@global.connection_memory_limit |
+----------------------------------+
|                          2097152 |
+----------------------------------+

mysql> select count(c) from t group by c;
ERROR 4082 (HY000): Connection closed. Connection memory limit 2097152 bytes exceeded. Consumed 7079568 bytes.

可以看到上述報錯信息中提示這條SQL需要消耗約 7079568位元組 的記憶體。當然了,實際上這條SQL需要消耗的記憶體不止 7079568位元組,隨著我們細粒度逐步上調 connection_memory_limit 選項值,最後會發現這條SQL需要消耗的記憶體約為 13087952位元組。

當執行完這條SQL後,我們再次查詢狀態變數 Global_connection_memory,會發現它的值並沒這麼大,說明這條SQL執行完畢後,相應的記憶體也立即釋放,只保留維持會話連接所需的基本記憶體:

mysql> select count(c) from t group by c; show global status like 'Global_connection_memory'; show session status like 'Global_connection_memory';
+----------+
| count(c) |
+----------+
|        2 |
+----------+
1 row in set (0.04 sec)

+--------------------------+---------+
| Variable_name            | Value   |
+--------------------------+---------+
| Global_connection_memory | 2193153 |
+--------------------------+---------+
1 row in set (0.00 sec)

前面提到一點,只有普通用戶執行SQL才會受到記憶體使用上限約束,如果是用root用戶執行同一條SQL,則不受限制:

mysql> select user();
+----------------+
| user()         |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.00 sec)

mysql> select @@global.connection_memory_limit;
+----------------------------------+
| @@global.connection_memory_limit |
+----------------------------------+
|                          2097152 |
+----------------------------------+
1 row in set (0.00 sec)

mysql> select count(c) from t group by c;
+----------+
| count(c) |
+----------+
|        2 |
+----------+
1 row in set (0.05 sec)

所以不能頻繁用root等具備SUPER許可權的用戶執行需要大記憶體的SQL,避免被OOM kill。

另外,選項 connection_memory_chunk_size 如果設置太小,則會頻繁更新記憶體統計,對系統性能也會有影響;但也不建議設置太大,否則可能因為更新不及時而引發OOM問題,大部分情況下採用預設值即可。

綜上,假設有個伺服器物理記憶體是96GB,建議考慮做如下分配:

選項 設置值
innodb_buffer_pool_size 64G
global_connection_memory_limit 12G
connection_memory_chunk_size 8192
connection_memory_limit 96M
global_connection_memory_tracking ON

在上述規劃中,設置了每個會話中,普通用戶執行的SQL消耗記憶體不能超過96MB,所有會話消耗的記憶體總量不超過12GB,約可最高支撐128個併發連接;此外,innodb buffer pool + 各會話記憶體的和是 76G,約為物理記憶體的80%,已給系統預留出基本充足的剩餘記憶體,降低發生SWAP的風險。

延伸閱讀


Enjoy GreatSQL

您的分享是我們最大的動力!

-Advertisement-
Play Games
更多相關文章
  • Blazor是一種使用.NET和C#構建客戶端Web應用程式的新興技術。它允許開發者在瀏覽器中直接運行.NET代碼,而無需依賴JavaScript。Blazor的技術優點主要表現在以下幾個方面: 單一語言棧:Blazor允許開發者使用C#和.NET進行全棧開發。一種語言用於前端和後端可以大大簡化開發 ...
  • 在VB.NET中,您可以使用Substring方法或Split方法來截取字元串。 Substring方法允許您從字元串中提取一個子字元串,該子字元串從指定的起始索引開始,並繼續到字元串的末尾或指定的長度。以下是使用Substring方法截取字元串的示例: Dim str As String = "H ...
  • JwtBearer簡介 首先要搞清楚什麼是JwtBearer,JwtBearer是ASP.NET Core的OAuth 2.0 JWT Bearer身份驗證提供程式。它提供了對JWT令牌進行驗證的功能,然後允許將令牌中包含的聲明(claims)用於用戶身份驗證和授權控制。 Json Web Toke ...
  • 一:背景 1. 講故事 前幾天有位朋友找到我,說他們的軟體在客戶那邊卡死了,讓我幫忙看下是怎麼回事?我就讓朋友在程式卡死的時候通過 任務管理器 抓一個 dump 下來,雖然預設抓的是 wow64 ,不過用 soswow64.dll 轉還是可以的,參考命令如下: .load C:\soft\soswo ...
  • Ubuntu忘記密碼(五個小步驟) 可能用到的操作: | 按鍵/滑鼠操作 | 作用 | | | | | 進入虛擬機屏幕[點擊] | 滑鼠焦點在虛擬機中,接下來的操作都在虛擬機中響應 | | 退出虛擬機屏幕[ctrl+alt] | 將滑鼠焦點從虛擬機中移除,回到主屏幕 | 步驟一:重啟虛擬機,註意在= ...
  • 近日發現PG官方插件列表中新收錄了一款插件 pg_enterprise_views,因為官方已經數年未添新的插件了很是新奇,找了台設備測試過後果斷上了生產,得空分享給大家。 該插件提供了數十張系統表及一個GUI工具,用以監控從操作系統到資料庫方方面面的性能情況,並支持對任意時段歷史數據的回溯,基本等 ...
  • hive入門到精通 hive部署 啟動Hadoop # 啟動hadoop start-all.sh # 檢查hadoop進程 jps # 檢查各埠 netstat -aplnt | grep java 檢查MySQL是否啟動成功 ps -aux | grep mysql netstat -apln ...
  • 一直從事資料庫相關的工作,對於PG而言最大的問題其實是在運維管理方面,其缺乏有效且直觀成體系的系統表,苦覓良久,今日在PG官網中發現了一款新收錄的免費插件,其提供了數十張系統表,內容涵蓋了從操作系統到資料庫的負載指標、等待事件、會話、客戶端、SQL、SQL執行計劃、超時鎖、長事務、資料庫對象、寫進程 ...
一周排行
    -Advertisement-
    Play Games
  • Timer是什麼 Timer 是一種用於創建定期粒度行為的機制。 與標準的 .NET System.Threading.Timer 類相似,Orleans 的 Timer 允許在一段時間後執行特定的操作,或者在特定的時間間隔內重覆執行操作。 它在分散式系統中具有重要作用,特別是在處理需要周期性執行的 ...
  • 前言 相信很多做WPF開發的小伙伴都遇到過表格類的需求,雖然現有的Grid控制項也能實現,但是使用起來的體驗感並不好,比如要實現一個Excel中的表格效果,估計你能想到的第一個方法就是套Border控制項,用這種方法你需要控制每個Border的邊框,並且在一堆Bordr中找到Grid.Row,Grid. ...
  • .NET C#程式啟動閃退,目錄導致的問題 這是第2次踩這個坑了,很小的編程細節,容易忽略,所以寫個博客,分享給大家。 1.第一次坑:是windows 系統把程式運行成服務,找不到配置文件,原因是以服務運行它的工作目錄是在C:\Windows\System32 2.本次坑:WPF桌面程式通過註冊表設 ...
  • 在分散式系統中,數據的持久化是至關重要的一環。 Orleans 7 引入了強大的持久化功能,使得在分散式環境下管理數據變得更加輕鬆和可靠。 本文將介紹什麼是 Orleans 7 的持久化,如何設置它以及相應的代碼示例。 什麼是 Orleans 7 的持久化? Orleans 7 的持久化是指將 Or ...
  • 前言 .NET Feature Management 是一個用於管理應用程式功能的庫,它可以幫助開發人員在應用程式中輕鬆地添加、移除和管理功能。使用 Feature Management,開發人員可以根據不同用戶、環境或其他條件來動態地控制應用程式中的功能。這使得開發人員可以更靈活地管理應用程式的功 ...
  • 在 WPF 應用程式中,拖放操作是實現用戶交互的重要組成部分。通過拖放操作,用戶可以輕鬆地將數據從一個位置移動到另一個位置,或者將控制項從一個容器移動到另一個容器。然而,WPF 中預設的拖放操作可能並不是那麼好用。為瞭解決這個問題,我們可以自定義一個 Panel 來實現更簡單的拖拽操作。 自定義 Pa ...
  • 在實際使用中,由於涉及到不同編程語言之間互相調用,導致C++ 中的OpenCV與C#中的OpenCvSharp 圖像數據在不同編程語言之間難以有效傳遞。在本文中我們將結合OpenCvSharp源碼實現原理,探究兩種數據之間的通信方式。 ...
  • 一、前言 這是一篇搭建許可權管理系統的系列文章。 隨著網路的發展,信息安全對應任何企業來說都越發的重要,而本系列文章將和大家一起一步一步搭建一個全新的許可權管理系統。 說明:由於搭建一個全新的項目過於繁瑣,所有作者將挑選核心代碼和核心思路進行分享。 二、技術選擇 三、開始設計 1、自主搭建vue前端和. ...
  • Csharper中的表達式樹 這節課來瞭解一下表示式樹是什麼? 在C#中,表達式樹是一種數據結構,它可以表示一些代碼塊,如Lambda表達式或查詢表達式。表達式樹使你能夠查看和操作數據,就像你可以查看和操作代碼一樣。它們通常用於創建動態查詢和解析表達式。 一、認識表達式樹 為什麼要這樣說?它和委托有 ...
  • 在使用Django等框架來操作MySQL時,實際上底層還是通過Python來操作的,首先需要安裝一個驅動程式,在Python3中,驅動程式有多種選擇,比如有pymysql以及mysqlclient等。使用pip命令安裝mysqlclient失敗應如何解決? 安裝的python版本說明 機器同時安裝了 ...