找出MySQL瓶頸的基準測試和剖析

      有些時候，我們需要最佳化MySQL。那我們要對MySQL進行哪些改進呢？一條特殊的query?資料庫模式？伺服器硬體？唯一的辦法是測量你的系統在做什麼，在各種條件下測量它的效能。這就是我們下面要學習的。

      最好的策略就是找出最弱的環節，並加強你的應用程式鏈的組成。這非常有用，如果你不知道什麼阻止最優效能，或者以後什麼將要阻止最優效能的發揮。

      基準測試和剖析是兩條基本的找出瓶頸的方法。它們是有關聯的，但是它們又不完全相同。基準測試你的系統的效能。這將有助於確定系統的承受能力，向你展示哪些改變有用哪些沒用，或者顯示在不同的資料下你的應用程式的效能。

       相反，剖析協助你找出你的應用程式在哪裡花費了大量的時間，或者消耗了大量的資源。換句話說，基準可以回答“這種執行表現怎麼樣？”，而剖析可以回答“為什麼它會這個樣子執行?”

       我們準備在這個章節中講述兩部分內容，基準測試和剖析。我們開始討論基準測試的原因和對策，然後引入特定的基準測試的標杆（或者尺子）。我們先向你展示如何計劃和設計基準測試，為精確的結果做設計，執行基準測試和分析結果。最後，來看看基準測試載入器和如何使用它們的例子。

       剩下的章節講述如何最佳化應用程式和MySQL。我們將會詳細地展示我們已經應用於生產協助分析應用程式的效能，真實的最佳化代碼。我們也會展示怎樣記錄MySQL的query語句，分析日誌，使用MySQL的狀態計數器，以及用來查看MySQL和你的query語句怎樣做的其它工具。

1. 為什麼需要基準？

      很多大中型的MySQL部署有專門的標杆用在基準測試裡。然而，每個開發人員和DBA也應該熟悉基礎的基準測試和操作，因為它們非常有用。下面是基準測試可以協助你的一些事情：

•  測量你的應用程式當前是怎樣執行的。如果你不知道你的應用程式當前執行多快，你不能確定哪些改變有用。你還可以用曆史基準結果，來診斷比不能預期的問題。
• 證實你系統的可擴充性。你可以用基準測試來模仿比你的生產環境能處理的多得多的負載，比如成千上百倍的增加使用者。
• 計劃增長。基準測試能協助你評估將來你的預計負荷需要多少硬體，網路容量和其它資源。這能在升級或者大量應用程式改變的時候，協助減少風險。
• 測試你的應用程式在一個變化的環境裡的承受能力。例如，你可以找出你的應用程式，在並發下不定時的峰值或者不同的伺服器的配置的情況下，是怎樣執行的，或者你可以看到在不同的資料分布下它是怎樣處理的。
• 測試不同的硬體、軟體和作業系統配置。對於你的系統來說，是RAID5還是RAID10更好?當你從ATA磁碟切換到SAN儲存的時候，隨機寫的效能是怎樣變化的？2.4的Linux核心比2.6的更好嗎？MySQL的升級有助於提高效能嗎？對於你的資料，不同的儲存引擎有影響嗎？你可以用不同的基準來回答這些問題。

      對於其他目的，你也可以用基準測試，例如，為你的應用程式建立一個單元測試套件，但是在這裡我們僅僅關注效能相關方面。

    2.基準策略

     有兩條基本的基準測試策略：你可以對應用程式作為一個整體，或者隔離MySQL，用基準問題測試。這兩種策略分別以全棧和單組件基準測試聞名。有以下幾點測試整個應用程式而不僅僅是MySQL：

• 你測試整個應用程式，包括web服務，應用程式代碼和資料庫。這非常有用，因為你不僅僅關注MySQL的效能，更關心整個應用程式。
• MySQL並不總是應用的瓶頸，全站基準測試可以證明這一點。
• 只有測試整個應用，你才能知道每個部分的緩衝行為。
• 基準測試在某種程度上是好的，因為它反映了你的應用的真正行為，當你單獨測試某個模組的時候，很難發現的行為。

   另一方面，應用程式基底准測試很難建立，甚至很難正確地安裝。如果你的基準測試設計的很糟糕，你就會得出錯誤的結論，因為結果不能反映真實情況。

   然而，有時你不想瞭解整個應用。在最初階段，可能你只想瞭解MySQL基準測試。下面的基準測試是有用的：

• 你想比較不同的模式或者query語句
• 你想測試應用中一個特殊的問題
• 相比長篇大論的基準測試來說，你更傾向短的基準測試，能向你展示標記和測量改變的快的“迴圈時間”。

    當你在真實資料集的環境中，一次又一次的重複你的應用query語句時，基準測試MySQL是非常有用的。資料集本身和資料集的大小都必須是真實的。如果可能的話，做一個生產環境中的資料快照。

    不幸的是，建立一個真實的基準，是非常複雜和耗時的；如果你能得到生產環境中的資料集的複製品，算你走運。當然，這有可能是不可行的。比如,你可能開發了一個新的應用程式，只有少數的使用者和資料。如果你想知道，如果它變得龐大時，將會有什麼問題發生，除了類比更大應用資料和負載，你沒得選擇。

    測試什嗎？

    在你開始基準測試之前，甚至是在你設計測試之前，你需要確定你的目標。你的目標將會決定你的工具和技術，以便得到精確地有意義的結果。用問題來設計你的目標，比如“CPU是多的好嗎？”或者“新的索引是不是比現在的索引執行的更快？”

    它不可能是顯而易見的，這就需要你用不同的方法來測試不同的事情，例如：延遲和輸送量需要不同的基準測試。

    考慮以下幾個量度和它們如何完善你的效能目標：

   單位時間的交易量

    這是一個經典的曆史為基準的資料庫應用程式。標準化測試，如TPC-C標準(見http://www.tpc.org)被廣泛引用，很多資料庫供應商，工作非常努力以使它們工作的好。這些基準測試線上處理（OLTP）效能，這些基準最適合多使用者交易應用程式。通常的測量單位是每秒交易量。

    輸送量這個詞通常的意思是等同於單位時間內的交易量（或者工作的其它單元）。

    回應時間或延遲 

    這測量了一個任務需要的總時間。依賴你的應用，你可能需要測量毫秒，秒或者分鐘。從這裡你可以得出平均回應時間，最小回應時間和最大回應時間。

    最大回應時間是很少有用的度量，因為基準測試回合時間越長，可能最大回應時間越大。它並不總是能重複的，這就可能會在啟動並執行過程中拉大差距。正是因為這個原因，很多人使用百分比的回應時間。例如，如果95%的回應時間是5毫秒，你就可以知道任務可以在總時間的95%內少於5毫秒完成。

    畫出基準測試的結果，為圖形或者線性圖（例如，平均值和95%百分比）或者是散列圖，將是非常有協助的，因為這樣你就可以看到結果的分布情況。通過這些圖形，可以看出在長時間運行過程中，基準測試是怎樣執行的。

   假設你的系統每小時做一分鐘檢測。在檢測期間，系統“拋錨”，沒有交易完成。95%的回應時間不會顯示峰值，所以結果會掩蓋這個問題。然而，一個圖形會顯示回應時間內的周期性峰值。圖2-1會闡述這點。

   圖2-1顯示了每分鐘的交易量。線條顯示了象徵性的超過平均值的峰值。第一個峰值是因為伺服器的緩衝被冷凍了，另一個峰值顯示了伺服器重新整理髒頁穩定性到磁碟花費的時間。如果沒有圖形我們很難看到這些差異。

   穩定性

   對於系統來說，穩定性測試非常重要，因為系統需要在變化的工作負載下保持效能。

   “在一個變化的工作負載下保持效能”是一個很抽象的概念。效能是可以被度量的，例如，輸送量和回應時間；工作負載隨著資料庫大小，當前串連數，或者硬體不同，可能會存在差異。

    穩定性測試，對於評估系統承載能力來說是好的，因為它能展示出你的應用中的薄弱環節，而在其它基準測試中不會展示。

圖2-1 30分鐘啟動並執行結果

     例如，在單連結（不好的測試策略）的情況下做回應時間測試，你設計的系統效能良好；但是在任何等級的並發下，你應用可能會表現糟糕。一個測試關注的是在不斷增加的串連下的持續回應時間，這樣才可以看到設計的瑕疵。

     有一些活動，例如搜集顆粒資料建立總結性資料表的周期性批量作業，僅僅需要快速回應時間。單純地測試回應時間是好的，但是也要關心他們和其它活動是怎麼互動（相互影響）的。批量作業可能會導致互動的query語句表現較差，反之亦然。

      並發

      並發是很重要的，但是很多時候都被濫用和被錯誤地衡量。例如，有一種很流行的說法，有多少使用者在同時瀏覽網站。然而，HTTP是無狀態的，大多數使用者只是簡單地閱讀瀏覽器展示的內容，所以這並不能轉化為web伺服器的並發。同樣地，在web伺服器上的並發並不一定轉化到資料庫伺服器上。有直接關聯的就是你的會話儲存機制能處理多少資料。一個更精確的測試web伺服器的並發的方法是在峰值的時候，使用者每秒請求的次數。

       你也可以在應用程式的不同地方測試並發。在web伺服器上的並發越高，可能引起更高的資料庫並發等級。但是語言和工具套件可能影響它。例如，Java的串連池可能會比持續串連的PHP，會降低MySQL伺服器的並發串連。

      更重要的是在一個給定時間內運行query語句的並發數量。一個很好的設計應用程式可能會開啟MySQL伺服器的數以百計的並發，但是其中的一少部分應該會同時執行query語句。這樣，一個“50,000使用者同時線上”的web網站，可能在MySQL伺服器上只需要10~15個同時執行query語句。

      換句話說，你要真正關心的基準測試就是工作並發，或者線程數量，或者同時工作串連。測試當並發增加的時候，效能掉下來多少。如果是這樣的話，你的應用程式可能就無法處理高負載下的峰值。

      你也需要確保效能不會很快地降下來，或者設計應用程式，這樣就不會在應用程式的各個部分產生不能處理的高並發了。在通常情況下，你要設計限制MySQL伺服器的並發，如應用隊列。

      並發不能完全等同於回應時間和穩定性：它並不是一個結果，而是你怎樣建立基準測試的一個屬性。你應該在不同的並發水平下測試應用程式的效能，而不是測試你的應用程式的能達到的並發。

      總之，你應該測試對使用者來說重要的東西。測試衡量效能，但是“效能”對不同的人意味著不同的東西。收集一些關於系統應當怎樣測量的需求（正式或非正式的），能接受的回應時間，期望的並發類型，等等。然後，嘗試設計你的測試來解釋所有的需求，而不是“井底之蛙”排除其他東西關注某項東西。

       3.測試標杆

      在有個大致瞭解的情況下，讓我們轉向怎樣設計和執行基準測試上來。在我們討論如何把基準測試做好之前，先讓我們看下一些常見的錯誤，這些錯誤能導致不能用或者不精確地結果：

• 使用真實資料大小的子集，例如，當應用程式不得不處理好幾百G的資料時，我們只使用其中的1G資料；或者當你準備擴大你的應用程式時，使用現在的資料集
• 使用錯誤的資料分布，例如當真實系統資料中的“熱點”規則的資料分布（隨機產生的資料通常是不切實際的分布）。
• 使用不切實際的分布參數，例如，假設所有使用者的設定檔同樣地被瀏覽。
• 在多使用者應用中，使用單使用者情境。
• 在單台伺服器上測試分布式應用。
• 和真實使用者的行為錯誤地比較，例如web頁面上的“考慮時間”。真實使用者請求並閱讀它；他們不會一個接一個沒有停頓地點選連結。
• 在一個迴圈裡執行相同的query語句。真實的query語句是不同的，所以它們會引起緩衝未命中的情況。相同的query語句將會在某種層級全部或者部分被緩衝。
• 未能檢查錯誤。如果一個基準測試的結果沒有意義-例如，如果一個慢操作突然非常快地完成，那麼就該檢查錯誤。你就能測試出在一個SQL查詢時，MySQL能多快地探測到語法錯誤！原則性來說，每次測試完後都應該檢查錯誤記錄檔。
• 當系統還沒有變熱的時候，忽略系統是怎樣執行的，例如，系統剛剛重啟後。有時你需要知道你的伺服器重啟後，需要多長時間達到承載能力，所以你需要在暖開機期間注意觀察。相反地，如果你想研究它的正常效能，你需要關心，如果你的測試正好在重啟後，許多緩衝將被冷凍，那麼測試結果將不會反映，在緩衝在變熱後，在負載下得到的結果。
• 使用預設的服務設定。

     僅在避免這些錯誤上就會花費你很長時間來改進你的結果品質。

     對於別的所有事情都是同樣的，你應該在儘可能真實的環境中做測試。儘管有時，使用一個稍微不真實的測試也是明智的。例如，假如說你的應用程式在不同的主機上。使用相同的配置執行測試，將會更接近真實情況，但是這樣做就會增加更多變數，例如，網路負載多少，多快。在單節點上測試往往很簡單，然而在某些情況下，將會更精確。什麼時候使用最合適，完全取決於你的判斷。

       設計和規劃測試

   規劃測試的第一步就是確定問題和目標。然後，決定是否使用標準的測試還是你自己設計。

      如果你使用標準測試，要保證你選用的測試符合你的需求。例如，不要使用TCP測試你的電子商務系統。用TCP自己的話說，TCP“”。所以對於OLTP系統來說，不是一個合適的測試。

      設計你自己的測試是一個複雜的反覆的進程。開始，使用你生產環境中的資料集的快照。確保你能為後來的運行恢複這些資料集。

      然後，你需要在資料裡運行query語句。你可以在基本測試裡添加單元測試套件，多次執行，但是這和你怎樣真實地使用資料庫，不大可能匹配。一個比較好的方法是在一個典型的時間架構內，記錄你生產環境中的所有的query語句，例如，在峰值負載內的一個小時，或者一整天。如果你在很短的時間架構內，記錄了query語句，你可能需要選擇幾個時間架構。這將會使你覆蓋所有系統活動，例如，每周報告query語句，或者在低峰值的時期，執行計畫任務。

      你可以在不同等級下記錄query語句。例如，如果你需要全棧測試的話，你就可以記錄WEB伺服器上的HTTP請求。你也可以啟用MySQL的查詢日誌，但是如果你重放查詢日誌，要確保重新建立單獨的線程，代替線性地重放每條query語句。在日誌裡為每個串連建立一個單獨的線程也是非常重要的，避免線程間的query阻塞。查詢日誌顯示了哪個串連執行了哪條query語句。

       即使你還沒有構建自己的測試，你可以寫下你的測試計劃。你可以使測試跑很多遍，你需要重新精確地構建你的測試。也為將來打算。你可能不是下次執行這個測試的人，即使你是，你可能也不太記得你第一次是怎麼執行它的。你的計劃應該包括測試資料，安裝系統的步驟和暖開機計劃。

     設計一些規範參數和結果的方法，並詳細地記錄每次執行。你的文檔方法可能如試算表或者筆記那麼簡單，也可能如定做的資料庫那麼複雜（但是要記住，你要寫一些指令碼來協助分析測試結果，所以沒有比開啟試算表和文字檔更容易的方法了）。

     你可能會發現建立一個測試目錄，包含每次執行的結果的子目錄，會很有用。在相應地的子目錄，你可以放結果，設定檔，和每次執行的筆記。如果你的測試比你預想的多，而且你也高度興趣，無論如何記錄額外的資料。錯過記錄重要的資料總比不需要的資料要好，可能以後你會發現額外資料非常有用。在測試期間儘可能多地記錄附加資訊，如CPU的使用方式，磁碟I/O,和網路流量統計;SHOW GLOBAL STATUS的計數器，等等。

     獲得精確結果

     獲得精確結果的最好方法是，設計你的測試來回答你想要的問題。你有選擇正確的測試嗎？你捕捉到你需要的答案的資料了嗎？你的測試有錯誤的標準嗎？例如，你有運行一個計算密集型的測試來預測I/O密集型的應用程式的效能嗎？

     接著，確保你的測試結果可以重複的。盡量確保你的系統在每次開始執行的時候，是處在相同狀態的。如果測試很重要，你應當在每次執行後重啟系統。如果你需要一個預熱過的伺服器，正常來講，你也應當確保你的系統已經有足夠長的預熱。例如，如果預熱過程包含了隨機query查詢，那麼你的測試結果將會不可重複。

     如果測試改變了測試資料或資料庫模式，在每次執行的時候，用快照重新設定它。向一個表中插入一千行記錄和向一個表中插入一百萬行記錄，不會給出相同的的結果。在磁碟上的資料存放區和分布也會使結果不可重複。一個方法是確保物理布局相近，做一個快速的格式和檔案拷貝分區。

     當心額外負載，最佳化和監控系統，詳細記錄日誌，計劃任務，以及其它因素能使你的結果發生位移。