2018-09-03 10:40:11 IT大咖說

內容來源：2018 年 5 月 20 日，愛可生技術服務總監洪斌在“PHPCon China 2018 技術峰會”進行《MySQL性能診斷方法與實踐》演講分享。IT 大咖說作為獨家視頻合作方，經主辦方和講者審閱授權發佈。

閱讀字數：2852 | 8分鐘閱讀

嘉賓演講視頻回放及PPT，請複製：http://t.cn/RF8uMQm，粘貼至瀏覽器接口。

摘要

本次演講將介紹性能診斷方法論，以及觀測工具在MySQL性能分析過程中的運用，並通過實際案例展示面對未知環境的性能問題，該如何診斷。

性能診斷的方法

幾個定律

Little’s law是一個證明排隊論的定律，我們可以將計算系統宏觀的建模成排隊系統，系統中有這樣一個公式——請求量等於到達率乘響應時間。業界一般討論的性能指標有KPS、吞吐量、響應時間等，其中關鍵的是響應時間（延時）的指標和變化以及對吞吐量的影響。

Amdahl’s Law是為了證明並行計算對性能擴展所能帶來的影響。上圖中的綠線就是Amdahl所計算的併發和吞吐量之間的關係，從圖中可以看出整個曲線最終會趨近於一個常數，這表示後續無論系統資源和併發如何增長吞吐量都是恆定。不過真實的情況並不會這樣，由此引出第三個定律——Universal Scalability Law，它指出隨著資源和併發的增加性能會不升而降，圖中通過紅色線條表示，當到達某一時候後性能會急劇下降。因此我們在實際工作中會設法找到最優點，而不是通過不斷的增加資源和併發來提升性能。

這些基礎理論幫我們界定出了性能的邊界，對如何提升性能有更深入的認識。

通用方法

USE方法包含三部分使用率、飽和率、錯誤。任何資源都可以理解為一個隊列系統，這個系統中也會有使用率、飽和率，當隊列飽和無法處理請求的時候會進入錯誤階段，分為邏輯錯誤和壓力過大造成的錯誤。

基於USE方法在進行性能分析時首先會查看系統所處階段，比如資源沒有飽和的情況下，就不用再去新增資源，而是應該找尋其他影響性能的因素；出現錯誤的時候，可能是因為壓力過大。通過這樣的方法我們在資源層面分析性能問題時就有了清晰的脈絡。

火焰圖方法是用來分析應用程序在CPU和非CPU兩個方面的消耗時間，通過on-cpu和off-cpu的繪圖方式能夠直觀的看出每個調用棧在不同階段耗費的時間。

正常情況下性能無法通過單一指標來衡量，需要用到基線，也就是歷史數據，這樣才能判斷出性能變化的好壞。

MySQL體系結構

MySQL整體上分為兩大部分Connectors和Server。下方的Server端又被分為計算層和存儲層，計算層負責所有連接的處理，包括SQL解析、SQL執行計算以及SQL優化等。MySQL的Server層的優點在於擁有抽象接口能夠對接各種存儲引擎，只要該引擎符合接口規範。

解決MySQL問題時要分析故障點具體在哪一層，針對不同層面選擇不同的優化方式。

快速診斷

當系統出現問題但還不能定位具體原因的時候，需要進行系統級的快速判斷，這裡列出一些常規的執行流程。

首先使用top命令判斷主機負載以及cpu消耗情況。之後通過USE方法查看是否存在錯誤，比如oom-killer或tcp drop等。接下來是虛擬內存的情況，主要檢查r、free、si、so、us、sy、id、wa、st列。然後檢查進程的cpu使用率，多核利用情況；內存使用情況；網絡吞吐量；tcp連接情況等。

MySQL診斷工具

對於數據庫來說不僅要從系統層面找問題，還要從自身內部來分析。首先當然就是查看日誌，不同的日誌能夠提供不同的信息，錯誤日誌中有服務掛了或重啟後的詳細的信息和記錄，slow日誌中記錄了超過一定閾值的查詢和SQL請求，general日誌一般不會開啟，只有在故障重現的時候才會用到。

除了日誌外還可以通過命令查看MySQL內部的狀態信息，使用show procersslist就能看到當前MySQL的任務分佈情況和線程處理狀態。存儲引擎的狀態也要檢測，通過show engine InnoDB status命令獲取，主要關注點在事務狀態和事務隊列上。還可以使用Explain查看執行計算，對SQL進一步優化。最後是performance schema，它提供了更詳細的內部狀態，並且能通過SQL的方式出查詢出來。

在出現實際問題後，診斷步驟大致如下。首先是結合快速診斷檢查系統全局資源負載，然後檢查MySQL錯誤日誌和當前MySQL在做什麼，接著查看InnoDB的事務情況，最後要檢查下MySQL的複製狀態。

InnoDB

InnoDB是MySQL中很重要的一個部分，開發者在使用的時候有幾點需要注意。InnoDB表必須要有主鍵或唯一索引，組件應使用較小數據類型且有序，其次是要避免大事務（運行時間長或變更記錄多）。

上圖列出的是一些比較重要的參數。在併發有一定量的情況下，開發者一般都會將max_connection設置的比較大，不過這個值過大是會產生負面影響的。首先我們要理解併發和並行是完全不同的兩個概念，就拿銀行網點來說，併發可以理解為當前有多少顧客在存取款，並行則是有多少櫃檯在處理業務。由於並行量固定，所以當併發過多之後就要排隊。正因為有這種情況，max_connection才不會設置的過大，通常來說不會超過1千。

Innodb_buffer_pool_size的配置同樣也不是越多越好，這取決於數據量，一般建議設置在50-60之間，後續如果不夠會再增加。Innodb_flush_neighbors是關於IO刷盤的參數，主要用在有機械硬盤的場景下，如果用的是SSD可以將它設置為0。Innodb_log_file_size的大小取決於當前是否在頻繁刷新log日誌，設置過大會影響啟動效率，因此建議設為1-2G。

數據庫的優化最重要的還是在於SQL優化，實現更好的物理設計包括表設計、索引設計、數據分佈等等。

Note

優化的核心實際上是如何“少做事”，做的越多越複雜就意味著效率的降低，在優化之前要設法簡化流程。另外切勿盲目追求最優配置模板，存在這樣一個原則——在不知道參數含義的情況下不要隨意改動它，只有在明確知道該參數能夠解決問題的時候才去調整。還有就是避免過早優化，在遇到問題的時候在做優化。