如何快速定位MySQL性能問題

中國MySQL用戶組社區組織了一場深圳●珠海兩天兩地(04月14~15日)技術分享活動，這次活動以"深入MySQL技術，探究雲資料庫，分享實戰心得"為主題, 活動邀請了來自MySQL原廠、阿里巴巴集團資料庫事業部、極數雲舟、PingCap、新浪、魅族、阿里雲、寶存科技等資料庫專家演講，我們系統管理中心的同學徐春陽也參加了這個以資料庫技術為專題的分享活動，並受邀作為講師，以「如何快速定位MySQL性能問題」這個極具實踐性的題目進行分享:通過把資料庫類比作數據加工廠，把SQL執行過程比作生產流水線，然後充分挖掘「各工序」的任務計數指標，量化各環節的處理能力與壓力，來精準定位資料庫的性能問題。

以下是徐春陽同學本次分享的部分內容：

大綱：

.................................................................

流水線的生產能力大多取決於串列環節，資料庫的處理能力也類似。資料庫內部處理SQL時，也存在不少串列環節。

大範圍掃描或者全表掃描，類比流水線中處理步驟很簡單的環節，但是需要反覆多次執行時，會導致耗時較長。

資料庫中的cache或者buffer,可類比加工過程中的零部件或者工具，當它們缺少時，則需要臨時構建或者等待，從而影響效率。

當我們獲取流水線各個環節的工作量計數之後，就可以從整體分析導致流水線生產效率降低的源頭。資料庫內部各個環節，也都有相應的計數，依據這些指標，可以幫助我們非常快捷精準地定位資料庫性能問題。

下面我們來深入分析部分「工序」指標的含義。

對於thread相關的計數， 我們知道，MySQL是單進程多線程的資料庫，每一個用戶連接，是由一個對應的線程來服務的。當一個新的用戶會話連接進來的時候，如果需要創建新的線程，則thread_created這個指標就會增加1。 我們下面來看一下MySQL的源代碼，來最直觀的解釋在什麼情況下這個指標會增加。

這個函數的名稱為add_connection， 從名稱上來猜測這個函數的含義也是正確的，該函數的作用：每當有新的用戶連接進來的時候，都要調用這個函數來處理。該函數首先去檢查thread cache裡面是否有idle線程，如果有，則將選擇1個idle線程給這個新的連接(會話)使用，函數然後返回。如果沒有，則需要調用mysql_thread_create函數，通過操作系統調用來fork線程，然後調用inc_thread_created函數，將thread_created指標增加1。通過操作系統調用來fork新的線程用於服務新連接，是比較消耗系統資源的，如果有大量新的連接且都需要創建新線程，則需要消耗大量系統資源，導致資料庫響應變慢、與資料庫建立連接慢或者失敗等問題。因此如果存在風暴連接的情況，則需要增加資料庫參數thread_cache_size的值，讓thread cache有更多地idle線程來供新的會話(連接)使用。

下面我們通過解析MySQL源碼，來解析table_open_cache_misses跟table_open_cache_hits是如何增長的。

函數的名稱為open_table，在訪問表之前，首先需要打開表，在代碼實現上，就是獲得這個表的TABLE對象。 函數開頭的部分，定義了一些變數，我們重點來解析TABLE *table變數跟TABLE_SHARE *share變數。剛才就提到，打開表就是獲取table對象，而 TABLE_SHARE *share 是一個共享對象，它在線程之間是共享的，它包含的是表的結構信息，一個物理定義的表只需要一個TABLE_SHARE對象，而TABLE對象，是不共享的。不同會話之間，如果它們同時訪問同一個表，使用的是不同的TABLE對象，因此並發線程越多，被使用的TABLE對象也就越多。

我們來看上面函數的邏輯：首先根據會話線程來分配對應的table cache, 也就是TABLE對象的緩存池，MySQL 5.7默認有16個table cache 實例 (表緩存池實例)，然後調用緩存池的get_table函數來嘗試獲取TABLE對象，將返回結果保存在table指針中。

接下來看後續的處理邏輯，見下圖：

如上所示：如果table指針不為空，則在table cache中找到了對應的TABLE對象，緩存命中，將table_open_cache_hits指標增加1，然後程序跳轉到table_found代碼段。 如果緩存沒命中，則檢查share對象是否存在，如果存在，則跳轉到share_found代碼段。否則程序繼續往執行。

我們來看看各代碼段的處理邏輯。

上一頁ppt就講到，如果前面的get_table函數的返回結果是table found,則直接跳轉到table_found代碼段，如果結果是share found ， 則跳轉到share_found代碼段。如果什麼都沒有發現，則從最上面開始執行，首先調用get_table_share_with_discover函數獲取TABLE_SHARE對象，保存在指針變數share中。 然後通過my_malloc函數分配TABLE對象所使用的內存，這是一個硬內存分配，需要通過調用操作系統的內存分配函數，將消耗系統資源。當內存分配之後，然後通過share對象，也就是根據這個表的結構信息（包含欄位定義等信息），來具體化TABLE對象，完成TABLE對象的構建。因為這是一個完全新建的TABLE對象，所以table_open_cache_misses這個指標就會增加1。

以上就是對table_open_cache_hits 與table_open_cache_misses 指標值增加原因的源碼層解釋。當需要訪問某個物理表的時候，首先需要打開這個表。在打開過程中，如果在table cache中，不存在跟這個物理表相關的、沒有被使用的內存結構table對象，則需要重新構建這個內存結構table對象。重新構建則涉及物理內存硬分配，消耗系統內核資源的同時，也降低處理速度。也就是我們前面類比的、在流水線加工過程中，出現零部件/工具短缺的情況。

............................................................................

通過解析「各工序」的工作機制以及其對應指標實際含義之後，可以便於我們快速定位性能問題。但我們仍需要對一些工序的天花板指標進行摸底，以便於提前預防性能問題發生。有些環節基於現行的工作機制以及伺服器硬體條件，會容易成為性能瓶頸。 舉例如下：

以上是徐春陽同學在本次分享中的部分內容，感謝閱讀！

喜歡這篇文章嗎？立刻分享出去讓更多人知道吧！