[InnoDB系列] -- SHOW INNODB STATUS 探秘

原文作者： Peter Zaitsev

原文來源： http://www.mysqlperformanceblog.com/2006/07/17/show-innodb-status-walk-through/

譯者：葉金榮（Email:

），轉載請注明譯者和出處，并且不能用于商業用途，違者必究。

很多人讓我來闡述一下 <b>SHOW INNODB STATUS </b>的輸出資訊, 了解 SHOW INNODB STATUS 都輸出了些什麼資訊，并且我們能從這些資訊中擷取什麼資訊，得以提高 MySQL 性能。

首先，讓我們來了解一下 SHOW INNODB STATUS 輸出的基礎，它列印了很多關于 InnoDB 内部性能相關的計數器、統計、事務處理資訊等。在 MySQL 5 中，InnoDB 的性能統計結果也在 <b>SHOW STATUS</b> 結果中顯示了。大部分和 SHOW INNODB STATUS 的其他資訊相同，在舊版本中還沒有這個功能。

SHOW INNODB STATUS 中的很多統計值都是每秒更新一次的，如果你打算利用這些統計值的話，那麼最好統計一段時間内的結果。InnoDB 首先輸出以下資訊：

首先要确認這是至少統計了 20-30 秒的樣本資料。如果平均統計間隔是0或1秒，那麼結果就沒什麼意義了。

說實在的我不喜歡InnoDB提供的平均值，因為很難取得合理的平均間隔統計值，如果你是寫腳本來取得 SHOW INNODB STATUS 結果的話，那麼最好取得全局的統計結果，然後取得平均值。當然了，直接檢視輸出的結果資訊也是很有用的。

下一部分顯示了信号(Semaphores)相關資訊：

這段可以分成2個部分。一部分是目前的等待，這部分隻是包含了在高并發環境下的全部記錄，是以 InnoDB 會頻繁回退到系統等待。如果等待是通過自旋鎖來解決的話，那麼這些資訊就就不會顯示了。

通過這部分資訊，你就會知道系統負載的熱點在哪了。不過這需要了解一下源碼相關的知識 - 從上面的資訊中就可以看出來是哪個源碼檔案中的哪行(不同的版本結果可能不同)，隻是從這裡卻看不出來任何資訊。盡管如此，還是可以從檔案名中猜到一些東西 - 比如本例中，檔案名 "buf0buf.ic" 預示着和一些緩沖池争奪有關系。如果想了解更多，就去看源碼吧。

還有一些關于等待的更多細節。"lock var" 表示目前的 mutex 對象的值(被鎖住 = 1 / 釋放 = 0) 值，"waiters flag" 表示目前的等待個數。另外，本例中還可以看到等待狀态資訊 "wait is ending"，這表示 mutex 已經釋放，但是系統排程線程還正在處理。

第二塊是事件統計 - "reservation count" 和 "signal count" 顯示了 innodb 使用内部同步陣列的活躍程度 - 時間片(slot)配置設定以及線程信号使用同步陣列的頻繁程度。這些統計資訊可以用于表示 innodb 回退到系統等待的頻率。還有關于系統等待的直接相關資訊，可以看到"OS Waits"的互斥信号燈(mutexes)，以及讀寫鎖。這些資訊中顯示了互斥鎖和共享鎖。系統等待和 "保留(reservation)" 不完全一樣，在回退到用 sync_array 的複雜等待模式前，innodb 會嘗試 "輸出(yield)" 到系統，希望下一次排程時間對象裡命名線程已經釋放了。系統等待相對較慢，如果每秒發生了上萬次系統等待，則可能會有問題。另一個觀察方法是檢視系統狀态中的上下文(context)交換頻率。

另一塊重要的資訊是 "spin waits" 和 "spin rounds" 的數量。相較于系統等待，自旋鎖是低成本的等待；不過它是一個活躍的等待，會浪費一些cpu資源。是以如果看到大量的自旋等待和自旋輪轉，則很顯然它浪費了很多cpu資源。浪費cpu時間和無謂的上下文切換之間可以用 innodb_sync_spin_loops 來平衡。

接下來的這段顯示死鎖狀況:

這裡顯示了 Innodb 最後檢測到事務引發的死鎖，包括發生死鎖時的狀态，加了什麼鎖，在等待什麼鎖釋放，以及 Innodb 決定哪個事務會被復原。注意，innodb隻顯示了事務持有鎖的相關簡單資訊。并且隻顯示了每個事務最後執行的語句，發生死鎖的記錄就是由于這些語句引起的。檢視複雜的死鎖資訊還需要檢視日志檔案，才能找到真正引發沖突的語句。大部分情況下，SHOW INNODB STATUS 顯示的資訊基本足夠了。

下面是關于外鍵限制引發的死鎖資訊：

Innodb會顯示引發錯誤的語句。外鍵限制定義失敗，以及定義關系最密切的父表。有很多嵌接資訊都是用16進制表示，不過對于問題診斷并不是太重要，它們主要用于給 Innodb 的開發者來檢視或者用于調試目的。

接下來是顯示 Innodb 目前活躍的事務：

如果目前連接配接不是很多，則會顯示全部事務清單；如果有大量連接配接，則 Innodb 隻會顯示他們的數量，減少輸出的清單資訊，使得輸出結果不會太多。

事務ID是目前事務的辨別，事務的id每次都會增加。Purge done for trx's n:o 是指淨化(purge)線程已經完成的事務數。Innodb僅清除那些被目前事務認為不再需要的舊版本資料。那些未送出的舊事務可能會阻塞淨化線程并且消耗資源。通過檢視2次清除事務數之差，就可以知道是否發生了這種情況。少數情況下，淨化線程可能難以跟上更新的速度，2次檢視值之差可能會越來越大；那麼，innodb_max_purge_lag 就派得上用場了。 "undo n:o" 顯示了淨化線程目前正在處理的復原日志号，如果目前不處于活躍狀态，則它的值是 0。

History list length 6 是指在復原空間中的未清除事務數。随着事務的送出，它的值會增加；随着清除線程的運作，它的值會減小。

Total number of lock structs in row lock hash table 是指事務配置設定過的行鎖結構總數。它和曾經被鎖住過的行總數不一定相等，通常是一個鎖結構對應多行記錄。

MySQL中，每個連接配接如果沒有活動的事務，則它的狀态是 not started，如果有活動的事務，則是 ACTIVE。注意，盡管事務是活動的，但是其連接配接的狀态卻可能是 "睡眠(sleep)" - 如果是在一個有多條語句的事務裡的話。Innodb 會同時顯示系統的線程号以及程序号，這有助于利用gdb來調試或者其他類似用途。另外，事務的狀态也會根據目前實際狀态來顯示，例如 "讀取記錄(fetching rows)"，em&gt;"更新(updating)"等等。"Thread declared inside InnoDB 400" 的意思是 Innodb 核心正在運作該線程，并且還需要400個票。Innodb 會根據 innodb_thread_concurrency 的值來限制同時并發的線程數不超過它。如果線程目前不在 Innodb 的核心中運作，則它的狀态可能是 "waiting in InnoDB queue" 或 "sleeping before joining InnoDB queue"。後面這個狀态有點意思 - Innodb 為了避免有太多的線程同時搶着要進入運作隊列，那麼就會嘗試讓這些線程進入等待狀态(如果沒有足夠的空閑插槽(slot)的話)。這就可能會導緻 Innodb 核心中目前活躍的線程數可能比 innodb_thread_concurrency 的值還小。某種負載環境下，這可能有助于減小線程進入隊列的時間。可以通過調整 innodb_thread_sleep_delay 來實作，它的機關是微妙。

mysql tables in use 1, locked 0 是指事務中已經用過的資料表個數(已經通路過了的)，以及被鎖的個數。Innodb 一般情況不會鎖表，是以鎖表數一般是0，除非是 ALTER TABLE 或者其他類似 LOCK TABLES 的語句。

除了Innodb相關的特定資訊外，一些基本資訊可以通過 來檢視，例如正在執行什麼語句，查詢ID号，查詢狀态等。

下面這部分顯示的是跟IO相關的具體資訊：

本部分顯示了IO助手線程狀态 - 插入緩沖線程，日志線程，讀、寫線程。它們分别對應插入緩沖合并，異步日志重新整理，預讀以及重新整理髒資料。源自查詢的正常讀取是由正在運作的查詢執行的。在Unix/Linux平台下，總能看見4個線程，在Windows上可以通過 innodb_file_io_threads 來調整。每個線程準備好之後都能看到其狀态：waiting for i/o request 或者正在執行特定的操作。

每個線程都會顯示正在進行的操作數量 - 同時正要執行或者正在執行的操作數量。另外，正在執行的 fsync 操作數量也會顯示出來。有寫資料時，Innodb需要確定資料最終被寫到磁盤上，隻是把它們放在系統緩存裡是不夠的。通常是調用 fsync() 來完成的。如果它的值一直很高，那意味這Innodb可能是處于IO負載較高狀态。注意，由線程執行請求引發的IO請求是不計算在内的，是以盡管系統的IO負載較高，但是它們的值卻可能為 0。

接下來顯示的是IO操作的平均統計值，它們對于圖形顯示或者監控很有用。

"16384 avg bytes/read" 是讀請求的平均值。随機IO的話，每個頁的大小是16K，全表掃描或索引掃描時的預讀會導緻這個值明顯的增加。是以，它展現了預讀的效率。

本部分顯示了插入緩沖以及自适應哈希索引的狀态。第一行顯示了插入緩沖的狀态 - 段的大小以及空閑清單，以及緩沖中有多少記錄。接下來顯示了緩沖中已經完成了多少次插入，有多少記錄已經合并，有多少次合并已經完成。合并次數除以插入次數得到的比率可以反映出插入緩沖的效率如何。

Innodb采用哈希索引建立記憶體頁索引形成自适應哈希索引而不是采 B-tree 索引，得以加速行記錄到記憶體頁的檢索。這裡顯示了哈希表的大小，以及自适應哈希索引使用了多少單元和緩沖。可以通過計算利用哈希索引檢索的次數以及沒利用它檢索的次數來了解哈希索引的效率。

目前對自适應哈希索引基本沒有什麼辦法可以調整它，主要還是用于檢視。

接下來顯示的是Innodb的日志子系統相關資訊。可以看到目前的日志序列号 - 相當于Innodb自從表空間開始建立直到現在已經寫入日志檔案的總位元組數。還可以看到日志已經重新整理到哪個點，同樣也可以根據最後檢查點計算出還有多少日志沒有重新整理到檔案中去。Innodb采用模糊檢查點，是以這行顯示的是已經從緩沖池中重新整理到檔案的日志序列号。由于更高的日志序列号可能不會被立刻重新整理到日志檔案中去，是以日志序列号不能被覆寫掉。通過監控重新整理到哪個日志的日志序列，可以判定 innodb_log_buffer_size 的設定是否合理，如果看到超過 30% 的日志還沒有重新整理到日志檔案中，則需要考慮增加它的值了。

另外，還能看到日志寫入以及檢查點的數目。根據日志 I/O 操作的數目可以區分開表空間相關的IO請求和日志IO請求數量，進而可以确定到底需要幾個日志檔案。注意，innodb_flush_log_at_trx_commit 的值可以影響到日志寫操作的代價高或低。如果 innodb_flush_logs_at_trx_commit=2，則日志是寫到系統緩存，然後再順序寫到日志檔案中，是以相對會快很多。

這部分顯示了緩沖池和記憶體的使用率相關資訊。可以看到Innodb配置設定的所有記憶體(有些時候可能比你設定的還要多點)，以及額外的記憶體池配置設定情況(可以檢查它的大小是否正好)，緩沖池總共有多少個記憶體頁，有多少空閑記憶體頁，資料庫配置設定了多少個記憶體頁以及有多少個髒記憶體頁。從這些資訊中，就可以判斷記憶體緩沖池是否設定合理，如果總是有大量空閑記憶體頁，則不需要設定那麼多記憶體，可以适當減小一點。如果空閑記憶體頁為 0，這種情況下資料庫記憶體頁就不一定會和緩沖池的總數一緻，因為緩沖池還需要儲存鎖資訊，自适應哈希索引以及其他系統結構等資訊。

等待中的讀寫是指記憶體緩沖池級别的請求。Innodb可能會把多個檔案級别的請求合并到一個上，是以各不相同。我們還可以看到Innodb送出的各種不同類型的IO，LRU記憶體頁中需要重新整理的頁 - 髒記憶體頁，它們不會被長時間存取；重新整理清單 -

檢查點程序處理完之後需要重新整理的舊記憶體頁；獨立記憶體頁 - 獨立的寫記憶體頁。

我們還可以看到記憶體頁總共讀寫了多少次。已經建立的記憶體頁是目前一個記憶體頁中的内容沒有讀取到記憶體緩沖池中時，專門為新資料建立的空記憶體頁。

最後我們可以看到緩沖池的命中率，它預示着緩沖池的效率。1000/1000 相當于 100% 的命中率。不過這樣也很難說明緩沖池的命中率就足夠高了，這要需要根據不同的負載環境而定。通常情況下，950/1000 就夠了，有些時候在IO負載較高的環境下，命中率可能為 995/1000。

最後一部分，顯示了資料行操作以及一些系統資訊相關情況。

一開始顯示了Innodb線程隊列狀态 - 有多少線程處于等待或活躍的。Innodb内部打開了多少讀視圖 -

這是在事務開始後，但是目前還沒有活躍語句的情況，Innodb主線程的狀态控制了系統操作排程的數量 - 重新整理髒記憶體頁、檢查點、淨化線程、重新整理日志、合并插入緩沖等。 "state" 的值則表示了主線程目前的狀态。

接下來可以看到自從系統啟動以來，所有的資料行操作數量及其平均值。它們可以很友善地用于監控以及畫出系統狀态圖，資料行操作次數可以很好的衡量Innodb的負載。不是所有的資料行操作帶來的負載都是一樣的，存取10位元組的行比10Mb的行相比會小了很多，不過相對于查詢的總次數來說這個資訊可是有用的多了，差别也很大。

還有一點需要注意的是，SHOW INNODB STATUS 不是一成不變的，有些時間點上可能會不相符。SHOW INNODB STATUS結果中，不同時間可能會顯示不同結果，是以有些時候可能會看到沖突的資訊。這是由于設計時需要由全局鎖提供一緻性資訊，導緻了大量的開銷。

本文出自 “MySQL中文網”部落格 http://www.imysql.cn/