返回文章列表
玄貓（BlackCat）

MySQL 效能最佳化

本探討 MySQL 效能最佳化策略，涵蓋硬體資源選擇、作業系統組態及伺服器引數設定。文章深入剖析 CPU、記憶體、磁碟 I/O 對效能的影響，並提供監控工具與效能指標解讀方法。同時，也強調 MySQL 設定檔的管理及最佳化技巧，並提供最小化組態範例，協助讀者建立高效能的 MySQL 伺服器環境。

資料函式庫 系統管理
MySQL 效能最佳化 硬體組態 作業系統 設定檔 監控工具

MySQL 效能瓶頸常源於硬體資源不足、作業系統組態不當或伺服器引數設定錯誤。本旨在提供全面性的效能調校策略，從硬體選型到系統引數調整，協助建構高效能 MySQL 伺服器。文章首先分析 CPU 核心數、記憶體容量及磁碟 I/O 效能對資料函式庫運作的影響，並介紹 iostat、vmstat 等監控工具，搭配實際案例說明效能指標的解讀與應用。接著，探討 MySQL 設定檔的結構、語法及各項引數的設定技巧，涵蓋全域、會話和物件層級的作用域與動態修改方式。最後，文章提供一個最小化組態範例，並提醒讀者避免常見的錯誤做法，例如過度依賴基準測試或盲目追求高快取命中率。

最佳化 MySQL 效能：硬體選擇與作業系統組態

硬體資源的關鍵角色

MySQL 的效能深受硬體資源的影響，主要涉及 CPU、記憶體、磁碟和網路資源。網路資源通常不是主要瓶頸，但 CPU、記憶體和磁碟的表現卻至關重要。最佳化這些資源需要根據工作負載特點，在速度和數量之間取得平衡。

CPU 與平行處理

在高平行環境下，更多的 CPU 核心能夠有效提升 MySQL 的處理能力。選擇適當的 CPU 組態，對於提升整體效能至關重要。

監控工具的使用

有效的監控工具能夠幫助我們深入瞭解系統的執行狀態。常用的工具有 vmstat 和 iostat，但它們存在一些侷限性，例如單位混淆、取樣頻率不匹配等問題。

iostat 的解讀

iostat 提供了關鍵的磁碟效能指標，例如 r/s（每秒讀取次數）、w/s（每秒寫入次數）和 svctm（平均每次 I/O 請求的服務時間）。這些資料可以幫助我們計算磁碟的平行度。

計算平行度公式
concurrency = (r/s + w/s) * (svctm/1000)

例如，若某磁碟的 r/s 為 10，w/s 為 5，svctm 為 50，則其平行度約為：

concurrency = (10 + 5) * (50/1000) = 0.75

這表示在取樣期間，平均每次只有不到一個 I/O 請求在處理。

其他監控工具介紹

  • mpstat：提供更細緻的 CPU 使用狀況，有助於診斷單個 CPU 的負載問題。
  • dstat 和 collectl：彌補了 vmstat 和 iostat 的不足，能夠提供更全面的系統效能資料。
  • blktrace：用於深入分析磁碟 I/O 行為。
  • pt-diskstats：Percona 開發的工具，改進了 iostat 的輸出格式，並支援互動式操作，能夠動態調整顯示內容。
  • perf：Linux 系統級效能分析工具，能夠檢查核心使用情況和特定程式的行為。

儲存裝置的選擇

固態硬碟硬碟（SSD）已成為資料函式庫應用的首選，特別是線上上交易處理（OLTP）場景中，能夠顯著提升效能。傳統硬碟（HDD）僅在極度預算受限或需要大容量儲存（如資料倉儲）時才被採用。

作業系統的最佳化組態

在 GNU/Linux 系統上執行 MySQL 時，以下組態至關重要：

  1. 檔案系統選擇：XFS 檔案系統因其高效能而被廣泛推薦。
  2. 虛擬記憶體管理：調整 swappiness 引數，以減少不必要的交換行為。
  3. 磁碟佇列排程器：選擇適合伺服器工作負載的排程器。

MySQL 伺服器最佳化設定

瞭解 MySQL 的內部運作與行為

在最佳化 MySQL 伺服器設定之前，瞭解其內部運作與行為是至關重要的。這不僅能幫助你正確組態 MySQL，還能避免因錯誤組態而導致的效能問題或系統當機。

組態設定的基本原則

MySQL 有許多可調整的設定，但並非所有設定都需要調整。通常，正確設定基本的引數（如 InnoDB buffer pool 和 logfile size）就足夠了。過度調整組態可能導致不可預期的問題，因此，除非必要，否則應保持預設值。

MySQL 組態的工作原理

MySQL 的組態資訊來自命令列引數和設定檔。在 Unix-like 系統中，設定檔通常位於 /etc/my.cnf 或 /etc/mysql/my.cnf。伺服器會讀取設定檔內容，移除註解和換行符號，然後處理命令列選項。

設定檔的位置與格式

  • 設定檔通常採用 INI 格式，分為多個區段，每個區段以方括號內的區段名稱開始。
  • MySQL 程式會讀取與其名稱相同的區段。
  • 客戶端程式通常也會讀取 client 區段，以便設定通用引數。

要查詢 MySQL 伺服器的設定檔位置，可以使用以下命令：

$ which mysqld
/usr/sbin/mysqld
$ /usr/sbin/mysqld --verbose --help | grep -A 1 'Default options'
Default options are read from the following files in the given order:
/etc/mysql/my.cnf ~/.my.cnf /usr/etc/my.cnf

設定語法、範圍與動態性

  • 組態設定以全小寫形式書寫，單詞之間使用底線或破折號分隔。
  • 命令列選項與伺服器變數通常具有相同的名稱，但也有例外。

內容解密：

此段落說明瞭 MySQL 設定檔的語法規則和命名慣例。保持一致的命名風格有助於在檔案中快速搜尋設定。

伺服器設定的最佳實踐

  1. 瞭解 MySQL 內部機制：在調整組態之前，先了解 MySQL 的運作原理。
  2. 調整基本設定：如 InnoDB buffer pool 和 logfile size。
  3. 謹慎調整其他設定：避免無謂的變更，使用預設值除非必要。
  4. 測試變更：在手動調整組態後，務必閱讀相關手冊條目並進行測試。

使用 Plantuml 圖表呈現組態流程

@startuml
skinparam backgroundColor #FEFEFE
skinparam componentStyle rectangle

title MySQL 效能最佳化

package "資料庫架構" {
    package "應用層" {
        component [連線池] as pool
        component [ORM 框架] as orm
    }

    package "資料庫引擎" {
        component [查詢解析器] as parser
        component [優化器] as optimizer
        component [執行引擎] as executor
    }

    package "儲存層" {
        database [主資料庫] as master
        database [讀取副本] as replica
        database [快取層] as cache
    }
}

pool --> orm : 管理連線
orm --> parser : SQL 查詢
parser --> optimizer : 解析樹
optimizer --> executor : 執行計畫
executor --> master : 寫入操作
executor --> replica : 讀取操作
cache --> executor : 快取命中

master --> replica : 資料同步

note right of cache
  Redis/Memcached
  減少資料庫負載
end note

@enduml

此圖示呈現了最佳化 MySQL 伺服器設定的基本步驟。

MySQL 組態變數的管理與最佳化

MySQL 的組態變數具有多種作用域，包括全域（global）、會話（session）和物件層級。瞭解這些變數的作用域和動態修改方式對於最佳化 MySQL 伺服器的效能至關重要。

組態變數的作用域

MySQL 的組態變數可以分為以下幾種作用域：

  • 全域作用域：某些變數是全域的，例如 max_connections。
  • 會話作用域：某些變數具有全域預設值，但可以針對每個會話進行設定，例如 sort_buffer_size 和 join_buffer_size。
  • 物件層級：某些變數的作用域是針對特定物件的。

動態修改組態變數

MySQL 允許在伺服器執行期間動態修改某些組態變數。可以使用以下陳述式修改會話和全域變數的值：

SET sort_buffer_size = <value>;
SET GLOBAL sort_buffer_size = <value>;
SET @@sort_buffer_size := <value>;
SET @@session.sort_buffer_size := <value>;
SET @@global.sort_buffer_size := <value>;

需要注意的是，動態修改組態變數的值在 MySQL 重新啟動後將會遺失，因此需要更新組態檔以保留變更。

使用 DEFAULT 關鍵字

可以使用 DEFAULT 關鍵字將會話作用域的變數重設為全域變數的值。這對於重設會話作用域的變數非常有用。

持久化系統變數

MySQL 8.0 引入了持久化系統變數的功能，允許使用 SET PERSIST 語法將組態變數的值寫入磁碟，以便在下次重新啟動時使用。

修改組態變數的副作用

動態修改組態變數可能會產生意外的副作用，例如重新整理緩衝區中的髒資料。因此，需要謹慎選擇要修改的組態變數。

常見組態變數的修改效果

  • table_open_cache：修改此變數的值不會立即生效，而是在下次執行緒開啟表格時生效。
  • thread_cache_size：修改此變數的值不會立即生效，而是在下次連線關閉時生效。
程式碼範例
-- 設定 sort_buffer_size 的會話值
SET @@session.sort_buffer_size := 1048576;

-- 設定 sort_buffer_size 的全域值
SET @@global.sort_buffer_size := 1048576;

-- 使用 DEFAULT 關鍵字重設會話作用域的變數
SET @@session.sort_buffer_size := DEFAULT;

內容解密：

  1. 設定會話值：使用 SET @@session.sort_buffer_size := 1048576; 將 sort_buffer_size 的會話值設定為 1048576。這將影響當前會話的排序緩衝區大小。
  2. 設定全域值：使用 SET @@global.sort_buffer_size := 1048576; 將 sort_buffer_size 的全域值設定為 1048576。這將影響新建立的會話的排序緩衝區大小。
  3. 使用 DEFAULT 關鍵字：使用 SET @@session.sort_buffer_size := DEFAULT; 將 sort_buffer_size 的會話值重設為全域值。這對於重設會話作用域的變數非常有用。

MySQL 伺服器設定最佳化

在 MySQL 伺服器設定中，瞭解各種變數的行為至關重要。有些變數會立即分配記憶體，而有些則在需要時才分配。

瞭解 MySQL 變數的行為

  1. thread_cache_size：此變數控制 MySQL 用於快取執行緒的數量。當連線關閉時，MySQL 將執行緒新增至快取，而當新的連線建立時，則從快取中移除執行緒。如果快取已滿，新連線將建立新的執行緒，而不是重複使用快取中的執行緒。

  2. read_buffer_size：MySQL 在需要時才會為此緩衝區分配記憶體，但一旦需要，便會立即分配指定的全部記憶體。

  3. read_rnd_buffer_size：與 read_buffer_size 不同，MySQL 只會在需要時分配所需的記憶體量。

設定變數的注意事項

  • 除非確定提高全域設定的值是正確的，否則不應隨意更改每個連線的設定。有些緩衝區即使不需要，也會一次性分配全部記憶體，因此過大的全域設定可能造成巨大的浪費。
  • 監控服務水平目標（SLOs）以確保變更不會影響客戶體驗。基準測試並不足夠，因為它們不是真實的負載。
  • 理想情況下，使用版本控制系統來追蹤組態檔的變更，這樣可以有效地將效能變化或 SLO 違規與特定的組態變更關聯起來。

規劃變數變更

  • 在更改組態之前，應先最佳化查詢和綱要（schema），至少解決明顯的問題，如新增索引。
  • 除非硬體、工作負載和資料完全靜態，否則很可能需要稍後重新檢視組態。
  • 重點最佳化峰值工作負載，然後停在「足夠好」的狀態，除非有理由相信可以獲得顯著的效能改進。

不建議的做法

  1. 避免透過基準測試套件進行“調校”：這種方法通常不建議大多數人嘗試，因為它需要大量的工作和研究，但潛在回報卻很小。

  2. 避免“按比例調校”：例如，認為 InnoDB 緩衝池命中率應高於某個百分比，如果命中率太低就增加快取大小。這種建議是錯誤的，因為快取命中率與快取大小是否合適無關。

為何不應按比例調校

  • 快取命中率取決於工作負載，有些工作負載無論快取大小，都無法實作高命中率。
  • 快取命中本身是無意義的，因為它不直接表明效能或快取大小是否合適。

MySQL伺服器最佳化：避免常見錯誤與建立合適的組態檔

在最佳化MySQL伺服器的過程中，許多人會掉入一些常見的陷阱中。這些錯誤不僅浪費時間，還可能導致效能下降或系統不穩定。本文將探討這些常見錯誤，並提供如何建立一個合適的MySQL組態檔的建議。

避免「調校」伺服器的錯誤做法

許多人認為透過調整伺服器的引數可以提升效能，但事實上，這種做法往往是無效甚至有害的。Cary Millsap和Jeff Holt在其著作《Optimizing Oracle Performance》中強調了「按比例調校」的無效性，甚至提供了一個工具，可以人為地生成任何快取命中率，無論系統實際效能如何。這說明瞭依賴某些指標進行調校可能是錯誤的。

同樣地，在MySQL社群中，也存在著許多危險的做法。例如，一些流行的「調校指令碼」可能會教導人們錯誤的方法，從而誤導成千上萬的使用者。這些指令碼往往根據過時或錯誤的假設，對系統的實際幫助有限。

正確的最佳化方法

與其使用「調校」一詞，我們更傾向於使用「組態」或「最佳化」，因為這些詞更準確地描述了最佳化伺服器的過程。最佳化伺服器需要深入瞭解伺服器的內部工作原理，而不是簡單地調整引數並觀察結果。

在尋找組態建議時，網際網路上的資訊可能良莠不齊。雖然許多專家線上上分享他們的知識，但很難判斷誰是真正的專家。因此，選擇評價良好的MySQL服務提供者通常是較安全的選擇，因為他們的建議通常根據實際經驗和客戶回饋。

建立MySQL組態檔

沒有一個通用的「最佳組態檔」適用於所有情況。伺服器的最佳組態取決於其具體使用方式和硬體組態。因此，建立自己的組態檔是必要的。

最小化組態範例

以下是一個根據MySQL 8.0的最小化組態檔範例：

[mysqld]
# GENERAL
datadir = /var/lib/mysql
socket = /var/lib/mysql/mysql.sock
pid_file = /var/lib/mysql/mysql.pid
user = mysql
port = 3306

# INNODB
innodb_buffer_pool_size = <value>
innodb_log_file_size = <value>
innodb_file_per_table = 1
innodb_flush_method = O_DIRECT

# LOGGING
log_error = /var/lib/mysql/mysql-error.log
log_slow_queries = /var/lib/mysql/mysql-slow.log

# OTHER
tmp_table_size = 32M
max_heap_table_size = 32M
max_connections = <value>
thread_cache_size = <value>
table_open_cache = <value>
open_files_limit = 65535

[client]
socket = /var/lib/mysql/mysql.sock
port = 3306

組態解密：

  1. datadir、socket、pid_file：指定MySQL資料、socket檔案和pid檔案的存放位置。這些設定確保了MySQL伺服器的基本運作環境。
  2. innodb_buffer_pool_size和innodb_log_file_size：這兩個引數對於InnoDB儲存引擎的效能至關重要。需要根據實際的硬體組態和工作負載進行調整。
  3. innodb_file_per_table：啟用此選項可以讓每個InnoDB表儲存在單獨的檔案中，便於管理和維護。
  4. log_error和log_slow_queries：設定錯誤日誌和慢查詢日誌的存放位置，有助於問題排查和效能最佳化。
  5. tmp_table_size和max_heap_table_size：控制臨時表的最大大小，避免過多的記憶體使用。
  6. max_connections、thread_cache_size和table_open_cache：這些引數影響伺服器的並發處理能力和資源使用，需要根據實際的工作負載進行調整。

組態 MySQL 伺服器設定

在組態 MySQL 伺服器時，瞭解其內部運作和設定選項至關重要。首先，我們需要檢查 MySQL 的設定檔，通常是 my.cnf。這個檔案控制著 MySQL 伺服器的多個方面，包括其記憶體使用、連線設定和安全選項。

設定檔結構與重要引數

一個典型的 my.cnf 設定檔包含多個部分，每個部分都針對特定的 MySQL 元件或功能。以下是一個範例組態：

[mysqld]
datadir = /var/lib/mysql
socket = /var/lib/mysql/mysql.sock
user = mysql
port = 3306
innodb_dedicated_server = 1
open_files_limit = 65535

[client]
socket = /var/lib/mysql/mysql.sock
port = 3306

內容解密：

  • datadir 指定了 MySQL 資料函式庫檔案的存放位置。
  • socket 指定了 MySQL 使用的通訊端檔案位置，用於本地連線。
  • user 指定了 MySQL 伺服器執行的作業系統使用者帳號。
  • port 指定了 MySQL 伺服器監聽的網路埠號，預設為 3306。
  • innodb_dedicated_server 是 MySQL 8.0 引入的新引數，根據伺服器的記憶體自動組態 InnoDB 相關引數，適合專用資料函式庫伺服器。
  • open_files_limit 設定了 MySQL 可以開啟的最大檔案數量，應設為足夠大以避免「too many open files」錯誤。

檢查 MySQL 伺服器狀態變數

使用 SHOW GLOBAL STATUS 命令可以檢視 MySQL 伺服器的狀態變數，這些資訊對於最佳化設定非常有幫助。為了觀察狀態變數的變化，可以使用 mysqladmin extended-status 命令：

$ mysqladmin extended-status -ri60

內容解密：

此命令每 60 秒輸出一次狀態變數的增量變化，有助於瞭解伺服器在不同時間的負載情況。

組態記憶體使用

啟用 innodb_dedicated_server 後，MySQL 將根據伺服器的記憶體大小自動組態 InnoDB 緩衝池大小、重做日誌檔案大小等引數，通常會使用 50%–75% 的 RAM。這樣可以確保有足夠的記憶體用於其他用途，如連線記憶體分配和作業系統開銷。

每連線記憶體需求

MySQL 為每個連線分配一定量的記憶體，用於執行查詢和儲存臨時結果。在峰值負載時，確保有足夠的記憶體可用於所有活躍連線是非常重要的。否則，查詢可能會因為記憶體不足而執行緩慢或失敗。

為作業系統保留記憶體

除了為 MySQL 組態足夠的記憶體外，還需要為作業系統和其他軟體保留足夠的記憶體。這包括執行本地監控軟體、組態管理工具和排程任務等。確保作業系統不會頻繁進行虛擬記憶體交換（paging）至磁碟是至關重要的。