MySQL效能分析與硬體資源最佳化

– 14 分鐘閱讀

MySQL 效能調校是一個複雜的系統工程，涉及資料函式庫組態、查詢最佳化、硬體資源以及作業系統等多個層面。本文將重點關注如何利用 Performance Schema 進行效能監控，並結合硬體資源和 RAID 組態，最大限度地提升 MySQL 資料函式庫的效能。首先，我們將介紹 Performance Schema 的基本使用方法，包括查詢變數資訊、分析錯誤摘要以及檢查記憶體使用情況。接著，我們將討論如何根據工作負載選擇合適的 CPU、記憶體和磁碟資源，並深入分析 SSD 的效能特點以及 RAID 組態的最佳實務。最後，我們將總結一些實用的效能最佳化技巧，幫助讀者構建高效能的 MySQL 資料函式庫系統。

使用Performance Schema進行效能分析

Performance Schema是MySQL中一個非常有用的工具，用於監控和診斷資料函式庫的效能問題。它提供了詳細的效能資料，可以幫助開發者和DBA更好地理解資料函式庫的運作情況。

檢查變數資訊

variables_info表格提供了伺服器變數的詳細資訊，包括變數的來源、預設值、最小值和最大值等。這些資訊對於診斷和調整資料函式庫效能非常有幫助。

mysql> SELECT * FROM performance_schema.variables_info
    -> WHERE VARIABLE_SOURCE = 'DYNAMIC'\G

內容解密：

  • VARIABLE_NAME：變數的名稱。
  • VARIABLE_SOURCE：變數的來源，例如DYNAMIC表示動態修改的變數。
  • MIN_VALUE和MAX_VALUE：變數的最小值和最大值。
  • SET_TIME、SET_USER和SET_HOST：變數最後一次被修改的時間、使用者和主機。

分析錯誤摘要

Performance Schema提供了錯誤摘要表格，用於匯總錯誤資訊。這些表格可以幫助我們找出哪些使用者、主機或執行緒產生了最多的錯誤。

mysql> SELECT * FROM performance_schema.events_errors_summary_global_by_error\G

內容解密：

  • ERROR_NUMBER、ERROR_NAME和SQL_STATE：錯誤的編號、名稱和SQL狀態。
  • SUM_ERROR_RAISED和SUM_ERROR_HANDLED：錯誤被觸發和處理的次數。
  • FIRST_SEEN和LAST_SEEN：錯誤第一次和最後一次被看到的時間戳。

檢查記憶體使用情況

我們可以使用Performance Schema來檢查哪些事件分配了最多的記憶體。

mysql> SELECT SUBSTRING_INDEX(EVENT_NAME, '/', -1) AS EVENT,
    -> CURRENT_NUMBER_OF_BYTES_USED/1024/1024 AS CURRENT_MB,
    -> HIGH_NUMBER_OF_BYTES_USED/1024/1024 AS HIGH_MB
    -> FROM performance_schema.memory_summary_global_by_event_name
    -> WHERE EVENT_NAME LIKE 'memory/performance_schema/%'
    -> ORDER BY CURRENT_NUMBER_OF_BYTES_USED DESC LIMIT 10;

內容解密：

  • EVENT：事件的名稱。
  • CURRENT_MB和HIGH_MB：目前和歷史最高記憶體使用量（單位：MB）。

使用sys Schema進行分析

sys Schema提供了一個更簡潔的方式來查詢Performance Schema的資料。

mysql> SELECT SUBSTRING_INDEX(event_name, '/', -1), current_alloc
    -> FROM sys.memory_global_by_current_bytes
    -> WHERE event_name LIKE 'memory/performance_schema/%' LIMIT 10;

內容解密：

  • SUBSTRING_INDEX(event_name, '/', -1)：提取事件名稱的最後一部分。
  • current_alloc：目前記憶體分配量。

MySQL 效能最佳化：作業系統與硬體最佳化

MySQL 伺服器的效能取決於其最弱的環節，而作業系統和硬體往往是限制效能的關鍵因素。磁碟大小、可用記憶體和 CPU 資源、網路以及連線這些元件的元件都會限制系統的最終容量。因此，您需要謹慎選擇硬體並適當組態硬體和作業系統。

影響 MySQL 效能的因素

許多不同的硬體元件都可能影響 MySQL 的效能，但最常見的瓶頸是 CPU 耗盡。當 MySQL 嘗試平行執行太多查詢或較少數量的查詢在 CPU 上執行時間過長時，就會發生 CPU 飽和。

CPU 耗盡的原因

CPU 飽和通常發生在以下情況：

  • MySQL 同時執行太多查詢
  • 較少數量的查詢在 CPU 上執行時間過長

I/O 飽和的情況

I/O 飽和仍然可能發生，但頻率遠低於 CPU 耗盡。這主要是由於轉向使用固態硬碟硬碟（SSD）。歷史上，不再使用記憶體而改用硬碟（HDD）所帶來的效能損失非常極端。SSD 通常比 SSH 快 10 到 20 倍。如今，如果查詢需要存取磁碟，仍然可以從中獲得不錯的效能。

最佳化建議

為了最佳化 MySQL 的效能，您應該：

  1. 謹慎選擇硬體，確保其符合您的需求。
  2. 適當組態硬體和作業系統，以最大限度地發揮其效能。
  3. 如果工作負載受 I/O 限制，請考慮升級 I/O 子系統、安裝更多記憶體或重新組態現有磁碟。

使用 Performance Schema 分析效能

Performance Schema 是 MySQL 的一個功能，可以用來監控和分析伺服器的效能。雖然早期的 MySQL 版本中，Performance Schema 的實作並不理想，導致資源消耗過高，但現在已經有了很大的改進。

如何使用 Performance Schema

  1. 啟用 Performance Schema：您應該保持 Performance Schema 啟用，並動態啟用有助於解決您可能遇到的問題（例如查詢效能、鎖定、磁碟 I/O、錯誤等）的儀器和消費者。
  2. 使用 sys 模式：您還應該利用 sys 模式作為捷徑來回答最常見的問題。這將為您提供一種可存取的方式來直接從 MySQL 中測量效能。
mysql> SHOW ENGINE PERFORMANCE_SCHEMA STATUS\G
*************************** 1. row ***************************
Type: performance_schema
Name: events_waits_current.size
Status: 176
*************************** 2. row ***************************
Type: performance_schema
Name: events_waits_current.count
Status: 1536
...

解讀 Performance Schema 的輸出

Performance Schema 的輸出包含有關特定事件的詳細資訊，例如儲存在消費者中的特定事件數量或特定指標的最大值。最後一行包含 Performance Schema 目前佔用的位元組數。

重點整理
  • 謹慎選擇硬體並適當組態硬體和作業系統。
  • 使用 Performance Schema 分析 MySQL 的效能。
  • 利用 sys 模式簡化 Performance Schema 的使用。
  • 根據實際需求調整 MySQL 的組態，以最佳化其效能。

如何為MySQL選擇適當的硬體資源

在升級現有硬體或購買新硬體時，應該考慮工作負載是否受限於CPU。識別CPU密集型工作負載的方法是檢查CPU利用率，但不僅僅是檢視CPU的整體負載情況，而是要觀察最重要的查詢中CPU使用率和I/O之間的平衡，並注意CPU是否均勻負載。

伺服器的兩大目標

  1. 低延遲（快速回應時間）：為了實作這一目標，需要快速的CPU，因為每個查詢只會使用一個CPU。
  2. 高吞吐量：如果可以同時執行多個查詢，則可能會受益於多個CPU來處理查詢。

即使工作負載沒有充分利用所有CPU，MySQL仍然可以使用額外的CPU執行後台任務，如清除InnoDB緩衝區、網路操作等。不過，這些任務通常與執行查詢相比是次要的。

平衡記憶體和磁碟資源

擁有大量記憶體的主要原因不是將大量資料儲存在記憶體中，而是為了避免磁碟I/O，因為磁碟I/O比存取記憶體中的資料慢幾個數量級。關鍵在於平衡記憶體和磁碟的大小、速度、成本和其他特性，以獲得適合工作負載的良好效能。

快取、讀取和寫入

如果有足夠的記憶體，可以完全將磁碟從讀取請求中隔離。如果所有資料都適合記憶體，那麼一旦伺服器的快取被預熱，每次讀取都會是快取命中。仍然會有從記憶體進行的邏輯讀取，但不會有從磁碟進行的實體讀取。不過，寫入是另一回事。寫入可以在記憶體中執行，就像讀取一樣，但遲早必須寫入磁碟以使其永久儲存。換句話說，快取可以延遲寫入，但不能像讀取一樣消除寫入。

為什麼快取對寫入有益

快取可以將多個寫入操作合併在一起，以兩種重要的方式進行：

  • 多次寫入，一次重新整理：單個資料可以在記憶體中被多次修改，而無需將所有新值寫入磁碟。當資料最終被重新整理到磁碟時，自上次實體寫入以來發生的所有修改都變得永久。例如，多個陳述式可以更新記憶體中的計數器。如果計數器被遞增一百次，然後寫入磁碟，那麼一百次修改就被合併成一次寫入。
  • I/O合併：多個不同的資料可以在記憶體中被修改，並且這些修改可以被收集在一起，以便將實體寫入作為單個磁碟操作執行。

什麼是工作集？

每個應用程式都有一個「工作集」資料——即它真正需要完成工作所需的資料。許多資料函式庫也有大量不在工作集中的資料。可以將資料函式庫想像成一個帶有檔案櫃的桌子。工作集由需要在桌面上處理的檔案組成，代表主記憶體，而檔案櫃則是硬碟。就像不需要將每張紙都放在桌面上來完成工作一樣，為了獲得最佳效能，不需要將整個資料函式庫放入記憶體中——只需要工作集。

固態硬碟儲存裝置

固態硬碟（快閃）儲存裝置是大多數資料函式庫系統的標準，尤其是線上交易處理（OLTP）。只有在非常大的資料倉儲或舊系統中，通常才會找到HDD。隨著SSD價格在2015年左右大幅下降，這種情況發生了變化。

快閃記憶體的效能特點

高品質的快閃裝置具有：

  • 更好的隨機讀寫效能：與硬碟相比，快閃裝置通常在讀取方面略優於寫入。
  • 更好的順序讀寫效能：與硬碟相比，快閃裝置的改進不如隨機I/O那麼顯著，因為硬碟在隨機I/O方面的速度比順序I/O慢得多。
  • 更好的平行支援：快閃裝置可以支援更多的平行操作，事實上，只有在具有大量平行操作時，它們才能達到最佳的吞吐量。

快閃記憶體最重要的特點是它可以快速、小單位地讀取多次，但寫入更具挑戰性。儲存單元在沒有特殊擦除操作的情況下無法被重寫，並且只能以大區塊（例如512 KB）擦除。擦除週期很慢，最終會磨損該區塊。區塊可以容忍的擦除週期次數取決於其所使用的底層技術。

程式碼範例：無（本段落無程式碼）

內容解密：

本章節主要討論了MySQL在硬體選擇和最佳化方面的策略。首先強調了在升級或購買新硬體時考慮工作負載是否受限於CPU的重要性，並提出了伺服器的兩大目標：低延遲和高吞吐量。接著，討論瞭如何平衡記憶體和磁碟資源，以獲得最佳效能，並詳細介紹了快取、讀取和寫入操作的最佳化策略。同時，也解釋了工作集的概念和固態硬碟儲存裝置的效能特點。

固態硬碟硬碟（SSD）效能最佳化與RAID組態最佳化

固態硬碟硬碟的魔力與效能最佳化

固態硬碟硬碟（SSD）的效能魔力主要源自其專有的韌體、驅動程式以及其他使裝置運作的元件。要使寫入操作效能良好並避免過早磨損快閃記憶體區塊，裝置必須能夠重新定位頁面並執行垃圾回收（Garbage Collection）及所謂的磨損平衡（Wear Leveling）。寫入放大（Write Amplification）一詞用於描述因行動資料、多次寫入資料和中繼資料而導致的額外寫入操作。

垃圾回收的重要性

垃圾回收對於保持SSD的效能至關重要。為了保持某些區塊的新鮮度並準備好進行新的寫入，裝置會回收區塊。這需要裝置上有一定的可用空間。裝置內部可能會有您看不到的保留空間，或者您需要透過不將裝置完全填滿來自行保留空間；這取決於裝置的型別。無論哪種方式，當裝置填滿時，垃圾回收器必須更努力地保持某些區塊的乾淨，因此寫入放大係數會增加。

結果是，許多裝置在填滿時會變慢。每個廠商和型號的變慢程度都不同，這取決於裝置的架構。有些裝置即使在相當滿的時候也設計為高效能，但一般來說，100 GB的檔案在160 GB的SSD上的表現會與在320 GB的SSD上不同。變慢的原因是當沒有可用區塊時必須等待擦除操作完成。寫入到可用區塊需要幾百微秒，但擦除操作要慢得多——通常是幾毫秒。

RAID效能最佳化

儲存引擎通常將其資料和/或索引儲存在單個大檔案中，這意味著RAID通常是儲存大量資料最可行的選項。RAID可以幫助實作冗餘、儲存大小、快取和速度。但是，與我們一直在研究的其他最佳化一樣，RAID組態有很多變化，選擇適合您需求的組態非常重要。

主要的RAID級別

  • RAID 0：成本最低、效能最高的RAID組態，但不提供任何冗餘。我們認為RAID 0永遠不適合用於生產資料函式庫，但在開發環境中，如果伺服器完全故障不會成為事故，那麼它可以是一種選擇。

  • RAID 1：提供良好的讀取效能，並將資料跨磁碟複製，因此具有良好的冗餘性。RAID 1對於處理日誌記錄和類別似工作負載的伺服器來說是一個不錯的選擇，因為順序寫入很少需要多個底層磁碟來實作良好的效能。它也是對於需要冗餘但只有兩個硬碟的低端伺服器的典型選擇。

  • RAID 5：曾經對於資料函式庫系統來說是相當可怕的，主要是由於效能影響。但隨著SSD變得普遍，它現在成為了一個可行的選項。它將資料分散在多個磁碟上，並具有分散式奇偶校驗區塊，因此如果任何一個磁碟故障，資料可以從奇偶校驗區塊重建。如果兩個磁碟故障，整個磁碟區將無法還原。從儲存成本的角度來看，它是最經濟的冗餘組態，因為在整個陣列中只會損失一個磁碟的儲存空間。

RAID 5 的挑戰與最佳化

RAID 5最大的問題是當磁碟故障時陣列的效能會受到影響。這是因為資料必須透過讀取所有其他磁碟來重建。這對HDD的效能影響很大，因此通常不被鼓勵。如果您嘗試在重建期間保持伺服器線上，不要指望重建或陣列的效能會很好。其他效能成本包括由於奇偶校驗區塊而導致的可擴充套件性受限——RAID 5不能很好地擴充套件到10個以上的磁碟——以及快取問題。良好的RAID 5效能嚴重依賴於RAID控制器的快取，這可能與資料函式庫伺服器的需求相衝突。

然而，隨著SSD的出現，情況有所改善。SSD在IOPS和吞吐量方面提供了顯著改進的效能，並且隨機讀/寫效能不佳的問題也消失了。此外，由於RAID 5非常流行，RAID控制器通常針對RAID 5進行了高度最佳化，因此儘管理論上有限制，但使用良好快取的智慧型控制器有時對於某些工作負載可以表現得幾乎與RAID 10控制器一樣好。

RAID 技術深度解析與最佳實踐

RAID（獨立磁碟冗餘陣列）技術在資料儲存領域扮演著至關重要的角色，不同的RAID級別提供不同的效能、冗餘和成本效益特性。瞭解各種RAID級別的優缺點對於設計高效能和可靠的儲存系統至關重要。

RAID 5 的侷限性與改進方案

RAID 5曾經是廣泛採用的RAID級別，但其最大的問題在於當兩個磁碟同時故障時，資料將無法還原。隨著陣列中磁碟數量的增加，磁碟故障的機率也隨之提高。RAID 6透過增加第二個奇偶校驗磁碟來解決這個問題，從而允許在兩個磁碟故障的情況下仍然能夠重建陣列。然而，這也意味著寫入效能會因為額外的奇偶校驗計算而降低。

RAID 10：高效能與高可靠性的選擇

RAID 10是一種極佳的資料儲存選擇，它結合了映象和條帶化的優勢，不僅能夠擴充套件讀寫效能，還具備快速重建的能力。與RAID 5相比，RAID 10在重建速度和效能上更具優勢。然而，當一個硬碟故障時，效能可能會下降多達50%，具體取決於工作負載。此外，需要注意的是，一些RAID控制器的「串聯映象」實作方式可能會導致效能不佳，因為資料可能集中在某個磁碟對上。

RAID 50：大規模資料儲存的最佳化方案

RAID 50結合了多個RAID 5陣列的條帶化，能夠在經濟性和效能之間取得良好的平衡，特別適合於大型資料倉儲或極大型OLTP系統。

各種RAID級別的比較

RAID級別簡述冗餘性所需磁碟數更快的讀取更快的寫入
RAID 0便宜、快速、危險無N是是
RAID 1快速讀取、簡單、安全是2（通常）是否
RAID 5便宜、搭配SSD時快速是N + 1是取決於情況
RAID 6比RAID 5更具彈性是N + 2是取決於情況
RAID 10昂貴、快速、安全是2N是是
RAID 50適用於非常大的資料儲存是2(N + 1)是是

RAID 的故障、還原和監控

雖然RAID組態提供了冗餘性，但這並不代表資料是絕對安全的。事實上，多個磁碟同時故障的風險不容忽視。因此，定期監控RAID陣列的狀態至關重要。大多數RAID控制器都提供相關的軟體工具來報告陣列狀態。此外，定期的一致性檢查和Background Patrol Read功能可以幫助預防潛在的資料損壞。

使用熱備援磁碟提升可靠性

熱備援磁碟可以在磁碟故障時自動被控制器使用，從而加快還原速度。對於依賴每台伺服器的系統來說，這是一個很好的選擇。

監控RAID控制器的電池備份單元和寫入快取策略

RAID控制器的電池備份單元和寫入快取策略對於效能有著重要影響。當電池故障時，大多數控制器會預設停用寫入快取，從而導致效能大幅下降。較新的RAID控制器採用了快閃記憶體備份的快取，避免了電池學習週期的問題。

綜上所述，選擇合適的RAID級別並實施有效的監控和維護策略，是確保資料儲存系統高效能和高可靠性的關鍵。

RAID 效能最佳化

RAID 組態和快取設定是影響儲存系統效能的關鍵因素。適當的設定可以顯著提高資料存取效率和系統整體效能。

RAID 條帶區塊大小

RAID 條帶區塊大小（stripe chunk size）是影響 RAID 效能的重要引數。理論上，較大的區塊大小有利於隨機 I/O 操作，因為這樣可以從單一磁碟滿足更多的讀取請求。

內容解密：

  1. 隨機 I/O 操作的區塊大小匹配：當區塊大小至少與典型隨機 I/O 操作的大小相匹配時，如果資料不跨越區塊邊界，則只需一個磁碟參與讀取操作。
  2. 實際硬體限制：許多 RAID 控制器在處理大區塊時效能不佳，例如將區塊大小作為快取單元可能導致資源浪費。
  3. 檔案系統碎片化問題：即使區塊大小設定為 16 KB（與 InnoDB 頁面大小相符），也無法保證所有讀取操作都對齊在 16 KB 邊界上，因為檔案系統可能會將檔案碎片化並以 4 KB 的區塊大小對齊。

RAID 快取

RAID 快取是安裝在硬體 RAID 控制器上的相對較小的記憶體，用於緩衝在磁碟和主機系統之間傳輸的資料。

RAID 快取的使用場景：

  1. 讀取快取：將已讀取的資料儲存在快取中，以便未來請求相同資料時可以直接從快取中提供，而無需再次存取磁碟。然而，由於作業系統和資料函式庫伺服器有自己的更大容量快取，這通常是對記憶體的浪費。

    ############## 最佳實踐

  2. 測試不同的 RAID 組態：根據實際工作負載測試不同的區塊大小和快取策略，以找到最佳組態。

  3. 監控效能指標：持續監控系統的效能指標，根據需要調整 RAID 設定。

  4. 考慮使用寫入回寫（write-back）快取：如果工作負載涉及大量寫入操作，使用寫入回寫快取可以顯著提高效能，但需注意資料安全的風險。

  5. 適當設定 innodb_flush_log_at_trx_commit 和 sync_binlog：在必要時調整這些引數以降低永續性要求，從而提高效能，但需權衡資料安全的風險。

綜上所述，RAID 的最佳化需要綜合考慮工作負載特性、硬體規格以及資料安全性需求。正確的設定和持續的監控調整是確保系統高效執行的關鍵。

玄貓 BlackCat

玄貓 BlackCat

技術愛好者，專注於分享程式開發、雲端技術與 AI 應用的心得體會。