資料函式庫監控連線成長與效能指標

資料函式庫連線數量管理對於系統穩定性至關重要，應用層開啟過多閒置連線或過度使用連線都會影響資料函式庫效能。監控 threads_connected 和 threads_running 的值，並結合 max_connections 百分比，可以有效識別連線問題。此外，複製延遲也是重要指標，它反映了主從資料函式庫間的同步狀態，過高的延遲可能導致資料不一致。磁碟 I/O 利用率也是效能瓶頸的關鍵因素，應密切監控 I/O 等待時間，避免查詢因 I/O 阻塞而影響效能。

資料函式庫監控：連線成長與效能指標的重要性

在管理資料函式庫系統時，監控連線成長是確保資源不被耗盡並維持資料函式庫可用性的關鍵。這種風險主要體現在兩個方面：

應用層開啟大量未使用的連線

當應用層開啟大量連線但未實際使用時，可能會導致連線數量達到上限，從而影響資料函式庫的正常運作。判斷這種情況的一個明顯跡象是，threads_connected 的值很高，但 threads_running 的值仍然很低。

應用層積極使用大量連線

當應用層積極使用大量連線時，可能會導致資料函式庫負載過高。這個情況可以透過觀察 threads_connected 和 threads_running 的值是否同時很高（數百或數千）並且持續增加來識別。

在設定連線數量監控時，使用百分比而非絕對數值是一個有用的方法。threads_connected/max_connections 的百分比可以顯示應用層節點數量增長對資料函式庫連線池最大容量的影響程度。這有助於監控第一種連線成長問題。

連線數量的監控與警示

除了監控連線數量外，還應該追蹤和警示資料函式庫主機的負載情況，如前所述，這可以透過 threads_running 的值來觀察。通常，當這個值超過一百個執行緒時，CPU 使用率會升高，記憶體使用量也會增加，這是資料函式庫主機負載過高的跡象。這種情況對資料函式庫的可用性構成立即威脅，因為它可能導致 MySQL 程式被作業系統終止。

一個常見的快速解決方案是使用 kill process 命令或自動化工具（如 pt-kill）來緩解負載，然後透過查詢分析來找出資料函式庫進入這種狀態的原因。

連線風暴（Connection Storms）

連線風暴是指生產系統中，應用層感知到查詢延遲增加，從而開啟更多到資料函式庫層的連線。這可能導致資料函式庫負載顯著增加，因為處理大量新連線會佔用資源，進而影響查詢請求的處理。連線風暴可能導致 max_connections 中可用連線數量突然減少，並增加資料函式庫可用性的風險。

複製延遲（Replication Lag）

MySQL 具有原生複製功能，可以將資料從一個伺服器（源）傳送到一個或多個其他伺服器（副本）。源伺服器上的資料寫入與副本伺服器上資料可用的時間差異稱為複製延遲。如果應用程式從副本讀取資料，延遲可能導致資料不一致的現象。例如，在社交媒體應用中，使用者評論某人的帖子後，如果應用程式將讀取請求傳送到延遲的副本，可能會看不到最新的評論。

複製延遲的監控與警示

複製延遲是一個重要的指標，可以觸發事件。它不僅是一個即時的服務水平指標（SLI），也是一個長期趨勢，表明需要進行更多的架構變更。即使複製延遲沒有立即影響客戶體驗，它仍然是一個跡象，表明至少在某些時候，源節點的寫入量超過了當前組態下副本的寫入能力。它可以作為未來寫入容量問題的前兆。

需要注意的是，對複製延遲進行警示時，需要考慮到立即可行的補救措施是否總是可用的。如果應用程式不從副本讀取資料，則需要考慮監控系統對此類別狀況發出警示的積極程度。特別是在非工作時間收到的警示應該總是具有可操作性。

I/O 利用率

對於資料函式庫工程師來說，一個永恆的努力目標是“盡可能在記憶體中完成工作，因為它更快”。雖然這是正確的，但我們也知道，由於擴充套件性的需求，我們不可能 100% 地實作這一點。

隨著資料函式庫基礎設施的擴充套件和資料不再適合記憶體，我們意識到，下一步最好的做法是減少從磁碟讀取的資料量，以避免查詢因為等待 I/O 週期而被阻塞。即使在大多數系統都執行在固態硬碟硬碟的時代，這仍然是正確的。

I/O 利用率的監控

監控磁碟 I/O 活動可以幫助您在效能下降成為客戶面臨的問題之前採取行動。工具有很多，例如 iostat 可以幫助您監控 I/O 等待時間。透過監控這些指標，您可以預測和避免效能問題。

-- 檢視目前的執行緒狀態
SHOW GLOBAL STATUS LIKE 'Threads_running';
SHOW GLOBAL STATUS LIKE 'Threads_connected';

內容解密：

• SHOW GLOBAL STATUS LIKE 'Threads_running'; 用於檢視目前正在執行的執行緒數量。
• SHOW GLOBAL STATUS LIKE 'Threads_connected'; 用於檢視目前已連線的執行緒數量。
• 透過觀察這兩個值，可以瞭解資料函式庫當前的負載狀況和連線使用情況。

資料函式庫效能監控與長期規劃

在維護資料函式庫的過程中，監控是確保系統穩定執行的關鍵步驟。除了即時監控外，長期規劃也同樣重要。本篇文章將探討一些需要長期監控的指標，以及如何利用這些指標來最佳化資料函式庫的效能和擴充套件性。

磁碟I/O使用率監控

監控磁碟I/O使用率（IOutil）對於預測和避免效能瓶頸至關重要。當資料函式庫伺服器的執行緒大量處於IOwait狀態時，表示它們正在等待磁碟資源的可用性。透過持續監控IOutil，可以及早發現潛在的問題，例如全表掃描和低效率的查詢。

IOutil是以系統整體磁碟存取能力的百分比來表示的。如果IOutil長期接近100%，尤其是在非備份時間，可能指示著需要最佳化查詢或調整資料函式庫組態。

自動增量主鍵空間監控

MySQL預設將自動增量主鍵建立為帶有符號的整數，這可能會導致主鍵空間耗盡的問題。為了避免這種情況，需要監控使用自動增量主鍵的表格的剩餘整數空間。

可以使用以下查詢來監控自動增量主鍵的剩餘空間：

SELECT
    t.TABLE_SCHEMA AS `schema`,
    t.TABLE_NAME AS `table`,
    t.AUTO_INCREMENT AS `auto_increment`,
    c.DATA_TYPE AS `pk_type`,
    (
        t.AUTO_INCREMENT /
        (CASE DATA_TYPE
            WHEN 'tinyint'
            THEN IF(COLUMN_TYPE LIKE '%unsigned', 255, 127)
            WHEN 'smallint'
            THEN IF(COLUMN_TYPE LIKE '%unsigned', 65535, 32767)
            WHEN 'mediumint'
            THEN IF(COLUMN_TYPE LIKE '%unsigned', 16777215, 8388607)
            WHEN 'int'
            THEN IF(COLUMN_TYPE LIKE '%unsigned', 4294967295, 2147483647)
            WHEN 'bigint'
            THEN IF(COLUMN_TYPE LIKE '%unsigned', 18446744073709551615, 9223372036854775807)
        END / 100)
    ) AS `max_value`
FROM information_schema.TABLES t
INNER JOIN information_schema.COLUMNS c
ON t.TABLE_SCHEMA = c.TABLE_SCHEMA
AND t.TABLE_NAME = c.TABLE_NAME
WHERE
    t.AUTO_INCREMENT IS NOT NULL
    AND c.COLUMN_KEY = 'PRI'
    AND c.DATA_TYPE LIKE '%int';

內容解密：

1. 查詢目的：此查詢旨在檢查使用自動增量主鍵的表格，並計算其主鍵空間的使用率。
2. 主要欄位：
   • schema 和 table：表示表格所屬的資料函式庫和表格名稱。
   • auto_increment：表示目前自動增量的值。
   • pk_type：表示主鍵的資料型別。
   • max_value：表示主鍵空間的使用百分比。
3. CASE敘述：根據不同的整數資料型別（tinyint、smallint、mediumint、int、bigint），計算其最大可能值，並根據是否為無符號型別進行調整。
4. JOIN操作：透過information_schema.TABLES和information_schema.COLUMNS兩個系統表格的JOIN，取得表格和欄位的後設資料。

備份與還原時間監控

除了日常維運外，備份與還原的時間也是需要長期監控的重要指標。隨著資料量的增長，備份與還原的時間可能會變得不可接受。因此，需要定期檢查備份與還原的時間，並據此調整災難還原計畫。

功能性分片與水平分片

當資料函式庫規模擴大到單一叢集無法有效處理時，可以考慮使用分片技術。功能性分片是指將特定業務功能的表格分離到專屬的叢集中，以獨立管理其可用性、效能或存取控制。水平分片則是將龐大的資料集分割到多個叢集中，並透過查詢機制來定位所需的資料子集。

此圖示說明功能性分片與水平分片的不同之處

圖示內容解密：

1. 功能性分片：將不同的業務功能分配到不同的叢集中（如叢集1和叢集2），以實作獨立管理。
2. 水平分片：將龐大的資料集分割到多個節點中（如節點1、節點2和節點3），並透過查詢機制來定位所需的資料子集。

衡量長期效能

選擇用於日常運作的服務水平指標（SLIs）和服務水平目標（SLOs）只是第一步。您需要確保不會只見樹木不見森林，過度關注特定的主機指標，而忽略了整體系統效能和客戶體驗結果的檢查。在本文中，我們將介紹一些策略，幫助您思考系統的整體長期健康狀況。

瞭解您的業務節奏

瞭解您業務的流量節奏非常重要，因為這將是您的所有SLOs受到最嚴格測試和最重要客戶審視的時候。業務節奏可能意味著尖峰流量時間比「平均」流量大幾個數量級，如果您的資料函式庫基礎設施沒有做好準備，將會有許多後果。在資料函式庫基礎設施的背景下，這可能轉化為每秒要處理更多的請求、來自應用程式伺服器的更多連線負載，或者如果寫入操作間歇性失敗，將對收入產生更大的影響。以下是一些業務節奏的例子，可以幫助您瞭解您的公司運作在什麼樣的業務週期中：

• 電子商務網站：許多國家的晚十一月到年底是最忙碌的時期，線上商店的銷售額可能會增加幾個數量級。這意味著更多的購物車、更多的並發銷售，以及對相同故障的收入影響比一年中的其他時間更大。
• 人力資源軟體：在美國，十一月通常是許多員工進行福利選擇的時期，被稱為「開放註冊」，這將產生更多的流量。
• 線上鮮花供應商：情人節將是一年中最忙碌的時期，將會有更多的人訂購花束。

正如您所見，這些業務週期可能因客戶需求而有很大差異。對於您來說，瞭解您業務的週期及其對業務收入、聲譽的影響，以及您應該為滿足需求而不影響系統穩定性做好多少準備至關重要。

在衡量支撐業務的資料函式庫基礎設施效能時，重要的是不要將效能衡量與工程組織正在追蹤的其他重要指標分開。資料函式庫效能應該是技術堆積疊效能更大對話的一部分，而不是特殊情況。首先，盡可能使用與工程組織其他部分相同的工具。您希望用來確定資料函式庫層效能的指標和儀錶板與應用層指標一樣容易取得，甚至在相同的儀錶板上。

有效追蹤您的指標

在為業務進行長期規劃時，有許多事情需要關注，包括但不限於：

• 規劃未來的容量
• 預測何時需要重大改進，何時增量變更就足夠
• 規劃執行基礎設施的成本增加

您需要能夠不僅衡量資料儲存基礎設施在某一時間點的健康狀況，還要長期趨勢地觀察效能改進或惡化。這意味著不僅要識別SLIs和SLOs，還要找出哪些SLIs和SLOs對於長期趨勢仍然是有價值的高訊號指標。您可能會發現，並非所有可用於短期待命決策的指標都適合用於長期業務規劃。

使用監控工具檢查效能

衡量效能對於立即「我們目前是否處於事件狀態」以及長期追蹤和趨勢分析都很重要。儲存您關心的指標的工具與指標本身一樣重要。如果您選擇了一個好的SLI，但無法以一種與組織其他指標相關的方式正確地檢視其趨勢，那麼這個SLI就毫無用處。

監控工具領域正在迅速發展，對於如何進行監控有很多強烈的意見。這裡的目標是提高透明度，專注於跟蹤結果而非產出。在使基礎設施堆積疊成功的領域中，追蹤成功是一項團隊運動。

@startuml
skinparam backgroundColor #FEFEFE
skinparam componentStyle rectangle

title 資料函式庫監控連線成長與效能指標

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

此圖示展示了監控工具在即時事件回應和長期趨勢分析中的作用。

內容解密：

1. 圖表展示監控工具的主要功能，包括即時事件回應和長期趨勢分析。
2. 長期趨勢分析有助於容量規劃和效能最佳化。
3. 即時事件回應能夠實作快速故障檢測和有效的事件管理。

重點整理

1. 業務節奏對系統效能有重大影響，需要充分準備。
2. 資料函式庫效能應與整體技術堆積疊效能一併考量。
3. 有效的指標追蹤需要合適的監控工具支援。
4. 長期趨勢分析對於容量規劃和效能最佳化至關重要。

長期趨勢監控工具的關鍵特性

在評估長期趨勢監控工具時，應關注以下重要特性和方面，而非特定工具本身。

拒絕平均值

在自行管理指標解決方案或使用軟體即服務（SaaS）時，應謹慎處理資料的長期儲存方式。許多解決方案預設將長期資料聚合成平均值，這是一個大問題。如果需要檢視某個指標在數週以上的趨勢，平均值會抹平峰值，從而可能導致錯誤的安全感。檢視數月的資料趨勢時，應始終關注峰值，以保持對偶發性峰值的敏感度。

百分位數是你的好朋友

百分位數透過對給定時間範圍內的資料點進行排序，並根據目標百分位數去除最高值（例如，95百分位數會去除前5%）。這種方法能使所檢視的資料在視覺上更接近於SLI和SLO的計算方式。如果能將查詢回應時間圖表顯示為95百分位數，就能更容易地與應用請求完成的SLO相匹配，使資料函式庫指標對客戶支援團隊和工程師（而非僅僅資料函式庫工程團隊）有意義。

長期保留與效能

監控工具在顯示長時間範圍資料時的效能至關重要。在評估業務指標趨勢解決方案時，必須測試在請求越來越長的時間範圍資料時，使用者經驗的變化。一個好的指標解決方案不僅要具備快速的資料攝入速度和長期的資料儲存能力，還要能快速提供資料。

使用SLO指導整體架構

在業務成長過程中，保持一致且良好的客戶體驗是一項艱鉅的任務。隨著業務規模的擴大，即使保持相同的SLO，也變得越來越困難，更不用說設定更具野心的目標。以可用性為例，每個人都希望資料函式庫讀寫操作的正常執行時間盡可能多。但是，SLO越嚴格，所需的工作就越昂貴，因為資料函式庫事務峰值或其大小也會以數量級增長。

利用SLI和SLO，可以找出適合開始將資料拆分為功能性分片或資料分割槽的成長點。第11章將詳細討論使用分片擴充套件MySQL，但這裡要強調的是，同樣的SLI和SLO不僅能告訴你目前系統的表現，還能指導你何時投資於擴充套件MySQL，以便在SLO的範圍內保持可管理的單個叢集，從而保護客戶體驗。

效能結構概述

由Sveta Smirnova貢獻

調整高負載下資料函式庫的效能是一個迭代過程。每次對資料函式庫效能進行調整時，都需要了解變更是否有效。查詢是否比以前執行得更快？鎖是否仍在拖慢應用程式，還是已經完全消失？記憶體使用情況是否有所改變？等待磁碟的時間是否有所改變？一旦瞭解如何回答這些問題，就能夠更快、更自信地評估和應對日常情況。

效能結構是一個儲存所需資料的資料函式庫，用於回答這些問題。本章將幫助您瞭解效能結構的工作原理、其侷限性，以及如何與其配套的sys結構一起使用，以揭露有關MySQL內部正在發生的事情的常見資訊。

效能結構簡介

效能結構提供了MySQL伺服器內部執行操作的低階指標。為了說明效能結構的工作原理，需要提前介紹兩個概念。

第一個是儀器（instrument）。儀器指的是MySQL程式碼中任何我們想要捕捉資訊的部分。例如，如果想要收集有關後設資料鎖的資訊，就需要啟用wait/lock/metadata/sql/mdl儀器。

第二個概念是消費者（consumer），它只是一個儲存有關被檢測程式碼資訊的表格。如果我們檢測查詢，消費者將記錄諸如總執行次數、未使用索引的次數等資訊。

內容解密：

1. 效能結構的目的：用於監控和分析MySQL伺服器的內部操作，提供低階別的效能指標。
2. 儀器的概念：代表MySQL程式碼中需要被監控的部分，例如後設資料鎖。
3. 消費者的概念：用於儲存被監控程式碼的執行資訊，如查詢總數、未使用索引的查詢次數等。
4. 啟用儀器：需要手動啟用特定的儀器來收集相關資訊，例如wait/lock/metadata/sql/mdl。
5. 消費者記錄資訊：根據所檢測的內容不同，消費者會記錄不同的資訊，有助於後續分析和調優。

深入理解 Performance Schema

Performance Schema 是 MySQL 中一個非常重要的工具，用於監控和分析資料函式庫的效能。它提供了詳細的效能資料，幫助 DBA 和開發人員瞭解資料函式庫的執行狀況，找出效能瓶頸。

Performance Schema 的基本功能

當應用程式使用者連線到 MySQL 並執行一條已檢測的指令時，Performance Schema 會將每個被檢測的呼叫封裝到兩個巨集中，然後將結果記錄到相應的消費者表中。需要注意的是，啟用檢測工具會呼叫額外的程式碼，這意味著檢測工具會消耗 CPU 資源。

檢測工具元素

在 Performance Schema 中，setup_instruments 表包含所有支援的檢測工具的清單。所有檢測工具的名稱都由以斜線分隔的部分組成。例如：

• statement/sql/select
• wait/synch/mutex/innodb/autoinc_mutex

檢測工具名稱的最左邊部分表示檢測工具的型別，例如 statement 表示這是一個陳述式檢測工具，wait 表示這是一個等待檢測工具。

名稱欄位中的其他元素，從左到右，表示從一般到具體的子系統。例如，select 是 sql 子系統的一部分，而 autoinc_mutex 屬於 innodb，它是 mutex 這個更通用的檢測工具類別的一部分，而 mutex 又是 sync 這個更通用的檢測工具型別的一部分。

mysql> SELECT * FROM performance_schema.setup_instruments
    -> WHERE DOCUMENTATION IS NOT NULL LIMIT 5, 5\G
*************************** 1. row ***************************
NAME: statement/sql/error
ENABLED: YES
TIMED: YES
PROPERTIES:
VOLATILITY: 0
DOCUMENTATION: Invalid SQL queries (syntax error).
*************************** 2. row ***************************
NAME: statement/abstract/Query
ENABLED: YES
TIMED: YES
PROPERTIES: mutable
VOLATILITY: 0
DOCUMENTATION: SQL query just received from the network. At this point, the real statement type is unknown, the type will be refined after SQL parsing.

程式碼解密：

上述 SQL 陳述式用於查詢 setup_instruments 表中 DOCUMENTATION 欄位不為空的記錄，並限制輸出 5 行。

• SELECT * FROM performance_schema.setup_instruments：查詢 setup_instruments 表中的所有欄位。
• WHERE DOCUMENTATION IS NOT NULL：篩選出 DOCUMENTATION 欄位不為空的記錄。
• LIMIT 5, 5：從第 6 行開始，輸出 5 行記錄。
• \G：以垂直格式顯示結果，便於閱讀。

消費者組織

消費者是檢測工具將資訊傳送到的目的地。Performance Schema 將檢測工具的結果儲存在多個表中。為了理解這些表的用途，可以將它們分成幾組。

目前和歷史資料

事件被放入名稱以下列結尾的表中：

• _current：目前在伺服器上發生的事件。
• _history：每個執行緒最近完成的 10 個事件。
• _history_long：全域最近完成的 10,000 個事件。

這些表的大小是可以組態的。目前和歷史資料可用於以下事件：

• events_waits：低階伺服器等待事件，例如取得互斥鎖。
• events_statements：SQL 陳述式。
• events_stages：概要資訊，例如建立暫存表或傳送資料。
• events_transactions：交易。