MySQL 架構與 InnoDB 儲存引擎

– 15 分鐘閱讀

MySQL 採用分層架構，儲存引擎扮演關鍵角色。理解儲存引擎 API 如何執行查詢和傳遞資料列是掌握 MySQL 架構的基礎。MySQL 8.0 引入的原子性 DDL，根據 InnoDB 儲存引擎的擴充套件，提升了資料定義管理的可靠性。SRE 理念的應用，則透過 SLO 等指標，將客戶體驗融入資料函式倉管理流程。此外，選擇合適的監控工具和策略，例如監控 Threads_running 和 Connection_errors_% 等關鍵指標，並結合 Performance Schema 分析查詢延遲，對於保障資料函式庫效能至關重要。磁碟增長和連線數的監控，則是主動式預防問題的關鍵，有助於在潛在風險影響客戶體驗前及時採取應對措施。

MySQL 架構與 InnoDB 儲存引擎的重要性

MySQL 是一種採用分層架構的資料函式庫系統，其上層負責處理伺服器範圍內的服務和查詢執行，而下層則是儲存引擎。雖然 MySQL 提供了多種不同的外掛式 API，但儲存引擎 API 卻是最為重要的。如果能夠理解 MySQL 是如何透過儲存引擎 API 來執行查詢並傳遞資料列，那麼就掌握了伺服器架構的基本原理。

資料定義管理的重大變革

在過去的幾次主要版本更新中，MySQL 對資料定義的管理進行了重大改進。特別是在 MySQL 8.0 中，引入了原子性資料定義變更（Atomic DDL），使得資料定義陳述式能夠完全成功或完全回復。這一改進得益於 InnoDB 儲存引擎的設計擴充套件，使其能夠支援 DDL 特定的復原和重做日誌，從而實作了資料定義變更的原子性。

程式碼範例：使用 InnoDB 儲存引擎的優點

CREATE TABLE customers (
    id INT AUTO_INCREMENT,
    name VARCHAR(255),
    email VARCHAR(255),
    PRIMARY KEY (id)
) ENGINE=InnoDB;

內容解密：

  1. CREATE TABLE 陳述式用於建立一個名為 customers 的新資料表。
  2. ENGINE=InnoDB 指定了該資料表使用 InnoDB 儲存引擎，這是 MySQL 的預設儲存引擎，能夠提供更好的效能和可靠性。
  3. InnoDB 支援事務處理、外部索引鍵約束等功能，能夠滿足大多數應用場景的需求。

Site Reliability Engineering（SRE）對資料函式倉管理的重要性

近年來，隨著 Site Reliability Engineering（SRE）理念的興起，資料函式倉管理的方式也發生了變化。SRE 強調以客戶體驗為中心，透過定義服務水準目標（SLO）來衡量系統的效能和可靠性。

SRE 理念下的資料函式倉管理流程

此圖示展示了 SRE 理念下的資料函式倉管理流程，從定義服務水準目標到監控系統效能、分析效能指標、最佳化系統組態，最終提升客戶體驗。

定義服務水準目標：讓客戶滿意的關鍵

在討論客戶滿意度時，有些情況的答案很明顯（例如，源資料函式庫宕機，我們無法進行任何寫入，因此業務停滯）。有些則不太明顯，例如週期性任務有時會佔用所有資料函式庫磁碟I/O，突然間所有其他操作都變慢了。在整個組織中，對我們正在測量什麼以及為什麼測量達成共識，可以幫助指導優先順序的討論。

瞭解SLI、SLO和SLA

在SRE（Site Reliability Engineering）實踐中，關於客戶滿意度的討論將使團隊對什麼是健康的業務達成共識，具體體現在服務水準指標（SLIs）、服務水準目標（SLOs）和服務水準協定（SLAs）。讓我們首先定義這些術語的含義：

服務水準指標（SLI）

簡單來說，SLI回答了「我如何衡量客戶是否滿意？」的問題。答案代表了從使用者的角度來看一個健康的系統。SLI可以是業務層面的指標，例如「導向客戶的API回應時間」，或者是更基礎的「服務是否啟動」。根據資料的上下文及其與產品的相關性，您可能會發現需要不同的指標或度量標準。

服務水準目標（SLO）

SLO回答了「我可以允許我的SLI的最低限度是多少，以確保客戶滿意？」的問題。SLO是我們希望在給定的SLI中達到的目標範圍，以被視為一個健康的服務。如果您認為執行時間是SLI，那麼您希望在給定的時間跨度內達到的「九個九」（例如99.99%）就是SLO。SLO必須定義為在給定的時間框架內的值，以確保每個人都對SLO的含義達成共識。SLI加上SLO構成了瞭解客戶是否滿意的基本公式。

服務水準協定（SLA）

SLA提供了對「我願意同意什麼SLO，並且會產生什麼後果？」問題的答案。SLA是一個已經被納入與一個或多個業務客戶（付費客戶，而非內部利益相關者）達成的協定中的SLO，如果未能滿足該SLA，將會產生財務或其他懲罰。值得注意的是，SLA是可選的。

為什麼定義SLI、SLO和SLA很重要

定義這些SLI、SLO和SLA不僅指導業務的健康狀況，也指導工程團隊的規劃。如果一個團隊沒有達到其約定的SLO，那麼就不應該繼續進行新功能的工作。對於資料函式庫工程團隊來說也是如此。如果本章討論的潛在SLO之一沒有被滿足，那就應該引發關於為什麼沒有被滿足的討論。當您掌握了資料，可以解釋為什麼客戶體驗不理想時，您就可以就團隊優先事項進行更有意義的討論。

如何讓客戶滿意

選擇了一組指標作為您的SLI之後，您可能會想將目標設定為100%。但是，您必須抵制這種衝動。請記住，選擇指標和目標的目的是在任何時候，以客觀的指標評估您的團隊是否可以創新新功能，或者穩定性是否有風險降至客戶無法接受的水平，因此需要更多的關注和資源。目標是定義讓客戶滿意所需的絕對最低限度。如果客戶對您的頁面在兩秒內載入感到滿意，那麼就沒有必要將目標設定為頁面在750毫秒內載入。這可能會為工程團隊帶來不合理的負擔。

可用性範例

以可用性作為指標和目標值的例子，我們可以宣稱「我們不會有任何宕機時間」，但是在實施和跟蹤是否達到目標時，這意味著什麼？達到三個九（99.9%）的可用性並非易事。三個九的可用性在整整一年中意味著總共八個多小時的宕機時間，相當於在一週內只有10分鐘的宕機時間。您承諾的「九」越多，實作它就越困難，團隊為實作這一承諾而花費的工程時間也就越昂貴。下表是一個來自Amazon Web Services的有用的圖表，顯示了原始資料中的挑戰。

可用性每年宕機時間每月宕機時間每週宕機時間每天宕機時間
99.999%5分鐘15.36秒26.28秒6.06秒0.14秒
99.995%26分鐘16.8秒2分鐘11.4秒30.3秒4.32秒
99.990%52分鐘33.6秒4分鐘22.8秒1分鐘0.66秒8.64秒
99.950%4小時22分鐘48秒31分鐘54秒5分鐘3秒43秒
99.900%8小時45分鐘36秒43分鐘53秒10分鐘6秒1分鐘26秒
99.500%43小時48分鐘36秒3小時39分鐘50分鐘32秒7分鐘12秒
99.250%65小時42分鐘5小時34分鐘30秒1小時15分鐘48秒10分鐘48秒
99.000%3天15小時54分鐘7小時18分鐘1小時41分鐘5秒14分鐘24秒

由於工程時間是一種有限的資源，因此在選擇SLO時必須小心，不要追求完美。並非產品中的所有功能都需要所有這些「九」來讓客戶滿意，因此您會發現，隨著產品功能集的擴充套件，不同的功能可能需要不同的SLO。

監控與可靠性工程的世界

在現代的線上業務中，資料函式庫的效能直接影響客戶體驗和公司的營收。隨著業務的增長，不同的功能和服務對資料函式庫的需求也會有所不同，這使得監控資料函式庫效能變得越來越重要。本章將討論如何在可靠性工程的世界中監控MySQL資料函式庫，包括定義服務水平指標（SLIs）和服務水平目標（SLOs），以及如何選擇合適的監控工具。

定義SLIs和SLOs

定義好的SLIs和SLOs是為了簡潔地描述如何為客戶提供令人滿意的使用者經驗。在MySQL的上下文中，這需要定義三個主要的方面：可用性、延遲和關鍵錯誤的缺乏。對於一個線上商店來說，這意味著頁面需要在幾百毫秒內載入完成，至少99.5%的時間內沒有錯誤。

客戶體驗的重要性

客戶體驗是衡量資料函式庫效能的關鍵指標。一個好的SLI和SLO應該能夠反映出客戶體驗的好壞。例如，「我希望99.5%的資料函式庫請求在兩毫秒內完成，沒有錯誤」是一個足夠好的SLI和SLO。

監控解決方案

在監控MySQL資料函式庫時，需要關注客戶體驗。這意味著需要依靠工具來在查詢回應時間超過約定閾值時及時提醒。以下是一些可用的監控解決方案：

商業解決方案

商業解決方案如SolarWinds Database Performance Management，可以自動化和簡化MySQL效能監控的工作，讓更多的工程師能夠輕鬆地進行查詢效能分析。

開源解決方案

開源解決方案如Percona Monitoring and Management（PMM）提供了一個客戶/伺服器架構，可以收集和傳送資料函式庫例項的指標到伺服器端，並提供了豐富的儀錶板來檢視效能相關的圖表。

-- 使用PMM收集MySQL效能指標的示例
-- 安裝PMM客戶端
pmm-admin config --server-insecure-tls --server-url=https://your-pmm-server:8443

-- 新增MySQL例項進行監控
pmm-admin add mysql --username=pmm --password=pmm_password --host=your-mysql-host --port=3306

內容解密：

  1. 安裝PMM客戶端：首先需要在你的MySQL例項上安裝PMM客戶端，並組態它連線到PMM伺服器。
  2. 新增MySQL例項：使用pmm-admin add mysql命令將你的MySQL例項新增到PMM中進行監控。
  3. 收集指標：PMM客戶端會收集MySQL例項的效能指標並傳送到PMM伺服器。
  4. 檢視效能圖表：透過PMM提供的儀錶板，可以檢視與MySQL效能相關的各種圖表。

其他監控方法

除了使用商業或開源的監控工具外，還可以透過將資料函式庫慢查詢日誌和MySQL Performance Schema輸出傳送到集中式位置，然後使用像pt-query-digest這樣的工具來分析日誌，以獲得對資料函式庫例項正在執行的操作的深入瞭解。

# 使用pt-query-digest分析慢查詢日誌的示例
pt-query-digest /path/to/your/slow-query.log

內容解密：

  1. 收集慢查詢日誌：首先需要組態MySQL將慢查詢日誌寫入到檔案中。
  2. 使用pt-query-digest分析：使用pt-query-digest工具分析慢查詢日誌，以找出效能瓶頸。
  3. 最佳化查詢：根據分析結果最佳化慢查詢，以提高資料函式庫效能。

監控與可靠性工程的世界

在現代線上商店的營運中，客戶體驗是至關重要的評估指標。為了確保客戶的滿意度，我們需要重新思考如何評估資料函式庫的效能與可用性。

效能評估的新思維

傳統的效能評估方法往往侷限於系統輸出的層面，例如查詢延遲或事務處理量。然而，這些指標並不能直接反映客戶體驗的實際情況。為了更好地理解客戶體驗，我們需要關注結果導向的指標。

利用 Performance Schema 來分析 MySQL 的效能是一個非常有用的方法。您可以利用它來找出瓶頸，使您的資料函式庫例項在相同的硬體規格下發揮更大的效能，從而節省基礎設施成本，或者回答「為什麼這個操作需要這麼長時間？」這樣的問題。

在生產環境中測試

在生產環境中進行測試可以帶來很多價值。生產環境是您發現變更與系統其他部分互動的實際情況的地方，能夠讓您觀察到對相鄰系統的影響。

透過使用基本的「客戶是否滿意」問題，您可以發現：

  • 當生產環境中的回饋迴路快速且與變更密切相關時，回復變更和重新檢查特定變更的速度會更快。
  • 這種方法促進了功能團隊和資料函式庫工程師之間的更強協作。當所有相關方都對要監控的特定指標和預期值達成一致時，衡量效能就成為了一項團隊工作。
  • 在發生效能迴歸的情況下，花費在生產環境外的時間用於調查「發生了什麼」比嘗試重新建立一個模擬更大規模程式碼路徑的基準測試套件更為具體。工程時間的投入變得更有針對性。

監控可用性

對於線上商店來說，間歇性的離線可能會導致客戶信任度的下降。因此，可用性作為一個獨立的指標，以及作為客戶體驗的一部分，是非常重要的。

可用性是指能夠在沒有錯誤的情況下回應客戶請求。用標準的 HTTP 術語來說，這可能是一個明確的成功回應，如 200 回應碼，或者是一個接受請求並承諾非同步完成相關工作的成功接受，如 202 已接受。

定義可用性

在將可用性轉化為 SLI 和 SLO 時，需要考慮以下細節：

  • 當發生不可避免的災難性故障時，哪些功能是不可協商的，哪些功能是「可有可無」的？
  • 我們定義哪些型別的故障為「災難性」的？
  • 「功能降級」是什麼樣子的？
  • 給定一系列可能的故障場景，我們能夠承諾的核心功能的最小可能平均還原時間（MTTR）是多少？

驗證可用性

驗證可用性的首選方法是從客戶端或遠端端點進行驗證。這可以透過被動方式完成，如果您有權存取客戶端的資料庫存取日誌。或者，您也可以主動驗證可用性，透過設定遠端程式碼對資料函式庫執行操作，以確保其可用性。

SELECT 1;

詳細解說：

上述 SQL 陳述式 SELECT 1; 是一個簡單的查詢，用於驗證 MySQL 是否正在接收和解析查詢，但不存取儲存層。這是一種基本的可用性檢查方法。透過執行此查詢，您可以快速確認資料函式庫是否能夠回應請求。

圖表說明

@startuml
skinparam backgroundColor #FEFEFE
skinparam componentStyle rectangle

title MySQL 架構與 InnoDB 儲存引擎

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

此圖示展示了客戶端請求到資料函式庫查詢再到回應的流程。客戶端發起 HTTP 請求，應用程式伺服器接收請求後向資料函式庫發起查詢，資料函式庫處理查詢後傳回結果，應用程式伺服器再將結果以 HTTP 回應的形式傳回給客戶端。

監控 MySQL 效能：從指標到錯誤分析

在現代軟體架構中，MySQL 資料函式庫的效能監控至關重要。監控不僅可以幫助我們及時發現問題，還能提供潛在問題的預警。本文將探討 MySQL 效能監控的關鍵指標、查詢延遲的監控方法，以及錯誤處理的策略。

遠端驗證可用性與效能指標

遠端驗證可用性有助於追蹤可用性目標，但無法在問題發生前提供洞察。MySQL 狀態計數器 Threads_running 是一個可用性問題的先行指標，它追蹤目前在特定資料函式庫主機上執行的查詢數量。當 Threads_running 快速增長且沒有下降跡象時，表示查詢執行速度不夠快，導致資源耗盡。通常，這會導致資料函式庫主機 CPU 鎖定或記憶體負載過高，甚至導致 MySQL 程式被作業系統關閉。

程式碼範例：監控 Threads_running

SHOW GLOBAL STATUS LIKE 'Threads_running';

內容解密：

  • SHOW GLOBAL STATUS LIKE 'Threads_running'; 用於查詢目前正在執行的執行緒數量。
  • 監控此指標可以幫助我們瞭解資料函式庫負載狀況。
  • 當 Threads_running 超過 CPU 核心數時，可能預示著伺服器即將進入不穩定狀態。

監控查詢延遲

MySQL 引入了多項增強功能來追蹤查詢執行時間，這些指標對於監控應用程式碼變更後的效能趨勢至關重要。然而，這仍然不能完全反映客戶體驗。除了內部追蹤的延遲外，我們還需要了解應用程式如何感知延遲及其對客戶體驗的影響。

程式碼範例：使用 Performance Schema 監控查詢延遲

SELECT EVENT_ID, TRUNCATE(TIMER_WAIT/1000000000000,6) as Duration, SQL_TEXT
FROM performance_schema.events_statements_history_long;

內容解密：

  • performance_schema.events_statements_history_long 用於儲存歷史查詢事件。
  • TRUNCATE(TIMER_WAIT/1000000000000,6) 將查詢等待時間轉換為秒，並保留六位小數。
  • 監控查詢延遲有助於我們瞭解資料函式庫效能對應用程式的影響。

錯誤監控與處理

並非所有錯誤都需要被追蹤和警示。分散式系統中，客戶端遇到間歇性錯誤是常見的，通常重試即可解決。然而，錯誤率的上升可能是潛在問題的指標。

程式碼範例：監控連線錯誤

SHOW GLOBAL STATUS LIKE 'Connection_errors_%';

內容解密：

  • Connection_errors_% 用於追蹤不同型別的連線錯誤。
  • 突然增加的連線錯誤可能是某些元件故障的訊號。
  • 監控這些錯誤有助於及時發現並解決問題。

主動式監控：提升系統可靠性的關鍵

在探討SLO監控時，我們關注的是客戶是否滿意。這有助於在客戶不滿意時改善他們的體驗，並在他們滿意時執行其他任務，如減少重複性工作。然而，這忽略了一個關鍵領域：主動式監控。

瞭解主動式監控的重要性

想像一下，你經營一家線上商店，雖然沒有發生任何重大的元件故障，但客戶支援票數卻不斷增加，報告“緩慢”或偶爾出現的錯誤，這些錯誤似乎會自行消失。如何追蹤這種行為？如果沒有良好的訊號基線表現，這可能是一個非常困難的任務。

你用來觸發待命警示的儀錶板和指令碼可以被稱為穩定狀態監控。它們讓你知道給定系統是否發生了意外事件，無論是否有變化。它們是提供前置指標的重要工具，在客戶體驗失敗之前。

監控的平衡之道

監控需要達到的平衡是，它必須始終是可行的，同時也是真正的前置指標。對資料函式庫磁碟空間滿100%的警示太晚了，因為服務已經關閉，但如果增長率不是那麼快，80%的警示可能太慢或不太可行。

有用的監控訊號

磁碟增長監控

追蹤磁碟增長是一種你可能直到它成為問題才會想到的指標。當它成為問題時，解決這個問題可能很耗時，並影響你的業務。瞭解如何追蹤它，制定緩解計劃，並知道什麼警示閾值是合適的，肯定會有所裨益。

有多種策略可以用來監控磁碟增長。讓我們從最理想到最低限度進行分析。

  • 追蹤磁碟空間使用率的增長率：如果你的監控工具允許，追蹤磁碟空間使用率的增長率非常有用。總是有一些場景會導致可用磁碟空間迅速減少，將你的可用性置於風險之中。長時間執行的交易與大型取消日誌或更改表格的操作就是例子。有很多事故案例表明，過多的日誌記錄或給定資料集的插入模式變化直到“資料函式庫”用完磁碟空間才被發現。

    -- 示例：檢視目前資料函式庫的磁碟使用情況
SELECT 
    TABLE_SCHEMA AS `Database`, 
    SUM(DATA_LENGTH + INDEX_LENGTH) AS `SizeInBytes`
FROM 
    information_schema.TABLES
GROUP BY 
    TABLE_SCHEMA;

    內容解密：

    • 這段SQL查詢用於計算每個資料函式庫的大小，透過加總每個表格的資料長度和索引長度。
    • information_schema.TABLES是一個包含所有資料函式庫表格資訊的虛擬表格。
    • DATA_LENGTH和INDEX_LENGTH分別代表資料和索引所佔用的位元組數。
    • 這對於監控資料函式庫增長和規劃儲存需求非常有用。

  • 設定多個閾值：如果無法追蹤增長率，可以設定多個閾值，在營業時間內觸發較低的警告，在非營業時間觸發較高的、更關鍵的值作為警示。

  • 單一閾值設定：如果以上兩種方法都不可行，那麼至少需要為磁碟空間使用率確定一個單一閾值，以此來通知待命工程師。這個閾值需要足夠低，以便在評估原因和考慮長期緩解措施時騰出一些時間和釋放磁碟空間。

連線增長監控

隨著業務增長，通常應用層會線性增長。你需要更多的例項來支援登入、購物車、處理請求或其他產品相關功能。所有這些新增的例項都會開啟越來越多的連線到你的資料函式庫主機。你可能會透過新增副本、使用複製作為擴充套件措施，甚至使用像ProxySQL這樣的中介軟體層來減緩這種增長，將前端的增長與直接對資料函式庫的連線負載解耦。

然而，當流量增長時，資料函式庫伺服器只能支援有限的連線池，這是由伺服器設定max_connections組態的。一旦連線到伺服器的總數達到這個最大值，你的資料函式庫將不允許任何新的連線，這是導致事件發生的常見原因，事件中你無法再開啟新的連線到資料函式庫，從而導致使用者錯誤增加。

-- 示例：檢視目前資料函式庫的最大連線數和目前連線數
SHOW VARIABLES LIKE 'max_connections';
SHOW STATUS LIKE 'Threads_connected';

內容解密：

  • SHOW VARIABLES LIKE 'max_connections';用於檢視目前設定的最大連線數。
  • SHOW STATUS LIKE 'Threads_connected';用於檢視目前已建立的連線數。
  • 這兩個查詢對於監控和管理資料函式庫連線非常有用，有助於預測和防止因連線數達到上限而導致的問題。
玄貓 BlackCat

玄貓 BlackCat

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