效能優化導論#

定義效能：Response Time 為王#

本書對 效能（performance） 的定義非常明確：完成任務所需的時間，也就是 回應時間（response time）。對資料庫伺服器而言，任務就是查詢（query），效能的衡量單位是「每個查詢花費的時間」。

效能 = 回應時間，不是 CPU 使用率，也不是 queries per second。 Throughput（吞吐量） 是效能優化的副作用，不是目標本身。

常見的思維陷阱：

• 降低 CPU 使用率不等於優化：資源本來就是要被消耗的。升級 MySQL/InnoDB 版本後 CPU 使用率上升，通常表示新版本花更多時間做有用的工作，而非內部爭搶。
• 提高 QPS 不等於效能改善：QPS 是回應時間的倒數，加速每個查詢自然會提高 QPS，但直接追求 QPS 可能導致方向錯誤。

優化的核心方法論#

第一原則：無法衡量就無法優化。

優化的目標是找出時間花在哪裡，然後消除不必要的工作。作者建議把 90% 的時間花在「測量」上，而非「嘗試修改」。

正確的測量範圍（Proper Scoping）：

• 測量的起點和終點必須與要優化的任務一致
• 應該針對具體的活動進行測量，而非觀察整個伺服器的聚合行為
• 如果某個查詢很慢，就測量該查詢本身，而不是整個伺服器

測量值永遠不等於真實值。保持對測量不確定性的意識，但不要因此放棄測量。不完美的測量仍然遠勝於憑直覺猜測。

時間消耗的兩種類型#

• 執行時間（Execution Time）：系統正在做工
• 等待時間（Wait Time）：系統被阻塞或等待資源

不同類型需要不同的分析工具。CPU 繁忙但查詢緩慢時看 execution-time profiling；CPU 空閒但查詢仍慢時做 wait analysis。

透過 Profiling 進行優化#

Profiling 的原理#

Profiling 包含兩個步驟：

1. 測量：記錄每個任務的開始時間和結束時間
2. 聚合排序：將相似任務分組，按總耗時排序，讓最重要的任務浮到頂部

Profile 報告的基本欄位：

Amdahl’s Law 與優化決策#

如果某個查詢只占總回應時間的 5%，無論你把它優化得多快，整體效能最多只能改善 5%。優化應在「改善的成本超過收益」時停止。

Profile 中看不到的資訊#

Profile 報告是摘要，會隱藏和遺漏重要細節：

• 值得優化的查詢：不是排名最高的就一定值得優化，要考量成本效益
• 離群值（Outliers）：偶爾極慢的查詢可能不會排到頂部，但對使用者體驗影響巨大
• 未知的未知（Unknown Unknowns）：Profile 無法呈現的「遺失時間（lost time）」，可能暗示你漏量了某些東西
• 被隱藏的細節：平均值會掩蓋分佈資訊。一筆看似 144ms 平均的查詢，可能是大量微秒級查詢和少數秒級查詢的混合

好的工具（如 pt-query-digest）會顯示 histogram、percentile、標準差和離散指數（index of dispersion），幫助你看穿平均值的假象。

剖析應用程式#

從頂層開始（Top-Down Profiling）#

效能問題不一定出在資料庫。常見的非資料庫瓶頸包括：

• 外部服務呼叫（如 web services、搜尋引擎）
• 應用層處理大量資料（如解析巨大 XML）
• 迴圈中的昂貴操作（如濫用正規表達式）
• 低效演算法

使用應用層 profiling 工具（如 New Relic、xhprof）在生產環境中持續監控。即使只對 1% 的請求取樣，也能發現最嚴重的問題。Tom Kyte（Oracle 效能大師）指出，持續 instrumentation 帶來的效能改善至少 10%。

在應用中加入 Instrumentation#

<?php
// 只對 1% 的 session 啟用 profiling，開銷極小
$profiling_enabled = rand(0, 100) > 99;
?>

工具建議：

剖析 MySQL 查詢#

伺服器層級：Slow Query Log + pt-query-digest#

擷取查詢日誌#

Slow Query Log 是目前開銷最低、精確度最高的查詢測量方式。

-- MySQL 5.1+ 支援微秒精度，設為 0 可記錄所有查詢
SET GLOBAL long_query_time = 0;

Slow query log 的 I/O 開銷在 I/O bound 工作負載下幾乎可忽略。記得設定 log rotation 以免塞滿磁碟。

替代擷取方式（無法存取伺服器時）：

• pt-query-digest --processlist：反覆觀察 SHOW FULL PROCESSLIST（可能遺漏短查詢）
• tcpdump + pt-query-digest --type=tcpdump：攔截 TCP 流量，精確度高，能捕獲所有查詢

分析查詢日誌#

使用 pt-query-digest 產生 profile 報告：

pt-query-digest /path/to/slow-query.log

報告包含：

• 指紋（Fingerprint）：將查詢正規化（移除字面值、統一大小寫）後的 hash ID
• V/M 欄位：variance-to-mean ratio（離散指數），數值越高表示執行時間越不穩定，通常是好的優化目標
• MISC 行：不夠重要而被合併的查詢摘要
• 每個查詢的詳細報告：包含統計摘要、response time histogram、EXPLAIN-ready 查詢範例

# Profile
# Rank Query ID           Response time    Calls R/Call V/M  Item
# ==== ================== ================ ===== ====== ==== =======
#    1 0xBFCF8E3F293F6466 11256.3618 68.1% 78069 0.1442 0.21 SELECT InvitesNew?
#    2 0x620B8CAB2B1C76EC 2029.4730 12.3% 14415 0.1408 0.21 SELECT StatusUpdate?
#    3 0xB90978440CC11CC7 1345.3445  8.1%  3520 0.3822 0.00 SHOW STATUS
# MISC 0xMISC               560.7556  3.4% 23930 0.0234 0.0  <17 ITEMS>

利用報告中的 byte offset 快速定位日誌中的問題查詢：

tail -c +3214 /path/to/query.log | head -n100

單一查詢層級#

SHOW PROFILE#

MySQL 5.1+ 內建的查詢 profiling 工具，能顯示查詢執行各階段的耗時：

SET profiling = 1;

SELECT * FROM sakila.nicer_but_slower_film_list;

SHOW PROFILES;  -- 列出所有已 profile 的查詢
SHOW PROFILE FOR QUERY 1;  -- 查看特定查詢的階段耗時

將結果轉為真正的 profile 報告（按耗時排序）：

SET @query_id = 1;
SELECT STATE, SUM(DURATION) AS Total_R,
    ROUND(100 * SUM(DURATION) /
        (SELECT SUM(DURATION) FROM INFORMATION_SCHEMA.PROFILING
         WHERE QUERY_ID = @query_id), 2) AS Pct_R,
    COUNT(*) AS Calls,
    SUM(DURATION) / COUNT(*) AS "R/Call"
FROM INFORMATION_SCHEMA.PROFILING
WHERE QUERY_ID = @query_id
GROUP BY STATE
ORDER BY Total_R DESC;

典型結果範例：

“Sending data” 是一個包羅萬象的狀態，涵蓋多種伺服器活動（包含 join 中的行搜尋等），不只是傳送資料。“Sorting result” 通常占比很小，因此「調整 sort buffer」往往是浪費時間。

SHOW STATUS#

SHOW STATUS 提供的是計數器而非計時器，因此嚴格來說不是 profiling 工具。但能輔助判斷查詢做了哪些昂貴操作：

FLUSH STATUS;
SELECT * FROM sakila.nicer_but_slower_film_list;
SHOW STATUS WHERE Variable_name LIKE 'Handler%'
    OR Variable_name LIKE 'Created%';

重點計數器：

SHOW STATUS 本身會建立暫存表並產生 handler 操作，因此計數器的值會受到自身影響。此外，部分計數器只有 global scope，無法精確反映單一 session 的行為。

Percona Server 的增強型 Slow Query Log#

Percona Server 在 slow query log 中記錄了遠比標準 MySQL 豐富的資訊：

• 查詢執行計畫詳情（Full_scan、Filesort、Tmp_table_on_disk 等）
• InnoDB 層級的 I/O 等待時間、鎖等待時間
• SHOW PROFILE 的完整資料
• 使用的 distinct pages 數量

Performance Schema#

MySQL 5.5 引入的 Performance Schema 提供 wait analysis 能力：

SELECT event_name, count_star, sum_timer_wait
FROM events_waits_summary_global_by_event_name
ORDER BY sum_timer_wait DESC LIMIT 5;

MySQL 5.5 的 Performance Schema 尚未支援查詢級別的 profiling（MySQL 5.6 才加入）。其優勢在於可透過 SQL 查詢存取計量資料，未來有極大潛力。

診斷間歇性問題#

避免 Trial-and-Error#

間歇性問題（偶爾的 stall、pileup、freeze）最常見的陷阱是退回到「隨機嘗試修改設定」的方法。作者見過真實的間歇性問題根因包括：

• 應用程式呼叫外部 API（如 curl 取匯率）而該服務偶爾極慢
• Memcached 中的重要快取同時過期，導致大量重建查詢湧入 MySQL（cache stampede）
• DNS 查詢隨機 timeout
• Query cache 因為 mutex contention 而週期性凍結
• InnoDB 的可擴展性限制導致高併發時查詢計畫最佳化耗時過長

必須從「能觀察到問題的地方」開始，逐層往下追蹤資源使用，盡可能完整測量。在錯誤的地方尋找不存在的問題，是最大的時間浪費。

判斷是全域問題還是單一查詢問題#

方法一：SHOW GLOBAL STATUS#

每秒擷取一次，觀察 Threads_running、Threads_connected、Queries 的異常波動：

mysqladmin ext -i1 | awk '
    /Queries/{q=$4-qp;qp=$4}
    /Threads_connected/{tc=$4}
    /Threads_running/{printf "%5d %5d %5d\n", q, tc, $4}'

典型問題特徵：QPS 突然下降，同時 Threads_running 飆升。

方法二：SHOW PROCESSLIST#

觀察大量 thread 停在異常狀態：

mysql -e 'SHOW PROCESSLIST\G' | grep State: | sort | uniq -c | sort -rn

常見的異常訊號：

• 大量 thread 在 freeing items 狀態 → 可能是 InnoDB 內部爭搶或 flushing 問題
• 大量 thread 在 Locked 狀態 → MyISAM 表級鎖問題
• 大量 thread 在 statistics 狀態 → 查詢最佳化階段異常

方法三：Query Log 分析#

開啟 slow query log（long_query_time = 0），觀察 throughput 的突然下降：

awk '/^# Time:/{print $3, $4, c;c=0}/^# User/{c++}' slow-query.log

flowchart TD
    Start["發現間歇性效能問題"] --> M1 & M2 & M3

    M1["方法 1：SHOW GLOBAL STATUS"]
    M1 --> M1a["觀察 Threads_running 等指標波動"]
    M1a --> M1b["判斷：尖峰 vs 穩態異常"]

    M2["方法 2：SHOW PROCESSLIST"]
    M2 --> M2a["觀察異常狀態的 thread"]
    M2a --> M2b["找出阻塞源頭"]

    M3["方法 3：Query Log"]
    M3 --> M3a["分析 throughput 變化"]
    M3a --> M3b["定位問題查詢"]

    M1b & M2b & M3b --> Result["定位根因"]

用 gnuplot 或 R 將資料視覺化，比肉眼掃描上千行輸出有效得多。

擷取診斷資料#

設定觸發條件（Trigger）#

好的觸發條件必須避免：

• 假陽性（False Positive）：正常時觸發，浪費時間分析正常資料
• 假陰性（False Negative）：問題發生時沒觸發，錯過收集機會

實務建議：

• 以 Threads_running 為觸發變數
• 設定閾值：正常值的 2-3 倍（例如正常 < 10，閾值設 20）
• 加入時間條件：超過閾值持續 5 秒才觸發，避免短暫尖峰

使用 pt-stalk 自動監控並觸發資料收集。

收集什麼資料#

使用 pt-collect（由 pt-stalk 呼叫）自動收集：

• 作業系統統計：CPU、磁碟使用率、ps、vmstat、iostat、記憶體
• MySQL 資料：SHOW STATUS、SHOW PROCESSLIST、SHOW INNODB STATUS
• Execution-time profile：使用 oprofile 分析 CPU 時間花在哪些函式
• Wait analysis：使用 GDB stack traces 分析 thread 在哪裡等待
• 查詢流量：使用 tcpdump 擷取

使用 GDB 會短暫凍結伺服器（特別是連線數多時），甚至可能造成 crash。但如果伺服器在 stall 期間已經無法使用，風險是可接受的。

分析 Stack Traces：pt-pmp（Poor Man’s Profiler）#

pt-pmp -l 5 stacktraces.txt

將相似的 stack trace 聚合，顯示最常見的等待模式：

398 pthread_cond_wait,os_event_wait_low,srv_conc_enter_innodb,...
 83 pthread_cond_wait,os_event_wait_low,sync_array_wait_event,mutex_spin_wait,...

上例顯示大量 thread 等待進入 InnoDB kernel（受 innodb_thread_concurrency 限制）。

flowchart TD
    A["pt-stalk 持續監控"] --> B{"Threads_running > 閾值？"}
    B -->|否| A
    B -->|是| C["觸發 pt-collect"]
    C --> D1["OS 統計"] & D2["MySQL 狀態"] & D3["oprofile CPU 分析"] & D4["GDB stack traces"] & D5["tcpdump 網路流量"]
    D4 --> E["pt-pmp 聚合分析"]
    D1 & D2 & D3 & D5 --> F["人工判讀"]
    E --> F

oprofile 範例#

samples    %       image name    symbol name
325733     11.34   mysqld        Query_cache::free_memory_block()
 46516      1.62   mysqld        Query_cache::invalidate_query_block_list()
 42153      1.47   mysqld        Query_cache::write_result_data()

當 Query Cache 相關函式占據大量 CPU 取樣時 → Query Cache 是瓶頸。關閉 query cache 即可恢復正常效能。

解讀資料的原則#

區分因果（Cause vs Effect）：症狀不等於原因。InnoDB 的 “flushing buffer pool pages” 可能只是磁碟被佔滿的結果，而非問題根因。

• 收集「正常狀態」的 baseline 資料，才能判斷異常
• 遇到不合理的現象（如 900 MB 資料庫每秒寫入 150 MB），檢查是否有隱含假設
• 跟隨每條推理線索到結論，做筆記避免遺漏

實際案例：Cache Stampede 診斷#

問題描述#

客戶每隔一兩天出現 max_connections 錯誤，持續數秒到數分鐘，高度隨機。伺服器配置：16 核 CPU、12 GB RAM、900 MB InnoDB 資料（全部在 SSD 上）。

診斷過程#

觀察到的現象：

• 300-2,000 SELECT/s，僅 ~5 UPDATE/s
• SHOW PROCESSLIST：大量 thread 在 Copying to tmp table 和 Sorting result
• SHOW INNODB STATUS：主線程在 “flushing buffer pool pages”，但 dirty pages 很少；495 個查詢在 InnoDB queue 中排隊
• iostat：幾乎沒有讀取，但寫入每秒高達 150 MB（對 900 MB 的資料庫來說不合理）
• oprofile：沒有明顯的 CPU 瓶頸
• gdb stack traces：大量 thread 等待進入 InnoDB kernel

推理排除：

1. 不是 InnoDB checkpoint stall：Innodb_buffer_pool_pages_flushed 幾乎沒有增加，buffer pool 中 dirty data 很少
2. InnoDB flushing 延遲是果不是因：磁碟被其他 I/O 佔滿導致 InnoDB 無法完成正常 I/O

找到根因：

# df -h 顯示磁碟空間在數秒內劇烈變化
/dev/sda3  58G  20G  36G  36%  /
/dev/sda3  58G  18G  37G  33%  /  # 幾秒後少了 2GB

# lsof 顯示 mysqld 在 /tmp 開啟了 1.6 GB 的暫存檔案
18:34:38 1655.21 MB
18:34:43    1.88 MB  # 暫存表消失

根因：Memcached 中的快取項目同時過期（cache stampede），導致大量應用程式實例同時向 MySQL 發送重建快取的查詢，這些查詢需要建立磁碟暫存表，瞬間產生 1.5 GB 的磁碟寫入，佔滿 I/O 頻寬。

解決方案：

1. 開發者修復應用層的 cache stampede 問題
2. 優化查詢使其不再使用磁碟暫存表
3. 兩者都做，互為保險

flowchart TD
    A["症狀：伺服器間歇性停頓"] --> B["SHOW PROCESSLIST"]
    B --> C["發現大量 Copying to tmp table"]
    C --> D["檢查 InnoDB STATUS"]
    D --> E["495 個查詢排隊等待"]
    E --> F["檢查 iostat"]
    F --> G["150 MB/s 磁碟寫入"]
    G --> H{"InnoDB checkpoint stall？"}
    H -->|排除| I["檢查 /tmp 目錄"]
    I --> J["發現 1.6 GB 暫存檔"]
    J --> K["根因：Memcached cache stampede"]

案例中的教訓#

• 不要因為查詢看起來「不完美」就急著優化，先確認是不是真正的問題根因
• 「資料庫以前從來不是問題」是一種偏見，要意識到它的存在
• 通用的 “best practices” 審查有其價值，但很少能解決高度特定的問題
• 如果有應用層 profiling 工具（如 New Relic），這個問題可能根本不需要外部顧問

其他 Profiling 工具#

USER_STATISTICS 表#

Percona Server 和 MariaDB 提供額外的 INFORMATION_SCHEMA 統計表（源自 Google）：

利用 INDEX_STATISTICS 找出未被使用的索引，作為移除候選。

strace#

strace -cfp $(pidof mysqld)

產生系統呼叫的 profile。與 oprofile 的區別：

• strace：測量 wall-clock time（包含 I/O 等待），能顯示 I/O 瓶頸
• oprofile：測量 CPU cycle（不包含等待時間），適合找 CPU 瓶頸

strace 會嚴重拖慢 mysqld，不適合直接用於生產環境。Percona Toolkit 的 pt-ioprofile 基於 strace 產生 I/O 活動的 profile。

本章總結#

• 效能 = 回應時間，不是資源消耗或吞吐量
• 無法衡量就無法改善：高品質、正確範圍的回應時間測量是一切的基礎
• 從應用層開始測量（top-down），資料庫層的問題會在好的測量中自動浮現
• Profile 是最有效的工具：將海量測量資料聚合排序，讓重要的東西浮到頂部
• 兩種時間消耗：工作（execution）與等待（waiting），需要不同的分析工具
• 優化不等於改善：當進一步改善的成本超過收益時應停止
• 用數據而非直覺做決策：直覺用來引導分析方向，但最終決策要基於數據