Mysql 之 性能分析（mysqlreport工具）

進行MySQL的配置優化，首先必須找出MySQL的性能瓶頸所在；而SHOW STATUS輸出的報告正是用來計算性能瓶頸的參考資料。mysqlreport不像SHOW STATUS那樣簡單的羅列資料，而是對這些參考資料加以融合計算，整理成一個個優化參考點，然後就可以根據這個優化參考點的值以及該點的衡量标準，進行對應調整。

索引報表：3-7行 索引報表是第一個主要章節，因為索引(key或者說index)對于mysql資料庫，是最最最重要的。雖然報表不可能直接告訴你這個庫的索引好還是不好，但它能告訴你這個索引緩沖區(key buffer)被利用的怎麼樣了。 注意：本報表僅彙總預設的MyISAM表的共享key buffer資訊，而不會管管理者自建的其他空間。 緩沖區使用情況：第4行 對于mysql，我們的第一個問題就是：到底用了多少key buffer？如果不太多，沒問題~因為mysql隻會在有需求的時候才配置設定系統記憶體給key buffer。也就是說，my.cnf中定義了’key_buffer_size=512M’，

不代表mysql啟動時就建立一個512M大小的key buffer。 本行顯示的，是mysql曾經使用過的key buffer峰值大小。而事實上，mysql應該用的更少，或者詭異的更多。這個更多的情況，mysql的術語叫“高水位”這個情況和my.cnf裡的’key_buffer_size’是否足夠大密切相關。當“水位”已經達到80-90%的時候，趕緊加大你的’key_buffer_size’吧。 注意：永遠不用“擔心”這個值超過95%，mysql文檔指出，key buffer中的一部分會被mysql主程式用于内部資料結構，這些是mysqlreport無法統計的内容。是以，所謂的95%，其實已經是100%了…… 目前情況：第5行 這行隻有在mysql版本高于4.1.2時出現，因為之前mysql的’show status’中沒有’key_blocks_unused’。這行資料顯示的是mysql目前使用的key buffer大小。如果上行的used%太大的話，那麼這行必然不會超過used，除非碰上那個傳說中的bug了。綜合這兩行，相信對’key_buffer_size’的設定是否合理就有譜了~ 本例中，mysql使用了60M的key buffer(12%)，這就很不錯，離滿負荷運作還早着呢。 寫命中：第6行 從本質上說，索引是基于記憶體的。因為通路記憶體的速度比硬碟快太多了。不過，mysql從磁盤裡進行一點點讀寫操作總是不可避免的。 這行資料顯示了寫索引的效率(具體意思是：寫入磁盤的key與寫入記憶體的key的比值)。這個值沒有什麼參考答案，而是取決于業務類型。如果mysql主要執行的是update/insert之類的操作，那麼正常比值接近0%；如果執行的select居多，那比值超過90%也是正常的。不過如果你看到的是一個負數，那說明mysql總是在往那個慢的要死的磁盤裡寫索引，這就很不妙了。 要想知道到底比值正常與否，請參考之後的DMS報表内容。 讀命中：第7行 比寫命中重要多了的就是讀命中。同樣，這個值就是讀自磁盤的key與讀自記憶體的比值。這個比值最好别低于99%！！再低就有問題了——很可能就是key buffer太小。mysql沒法從記憶體裡讀到，隻好找硬碟了…… 當然，如果你剛重新開機過一次mysql，那在一兩個小時内，命中率低一點也是正常的。

請求報表：9-26行 第二個主要章節。它展示了很多關于mysql在做什麼以及做的怎麼樣的内容。請求(question)包括SQL查詢(query)，也包括mysql協定通訊。大家經常關注的一個性能是mysql的qps(每秒執行查詢數)。不過從一個更廣泛的眼光看來，這個衡量标準其實是很随意的……mysql需要處理很多其他的請求。本報表試圖展現的，就是這麼一個更完整的内容。 總值：第10行 本行第一列，回答自運作起mysql一共處理多少請求，第二列，得出自運作起平均每秒鐘處理多少請求。大家可能以為第二列這個值就是我們想要的qps了。但mysql真的做了這麼多事情麼？繼續往下看。 （再次提醒大家注意questions和queries的差別） 查詢的總體分布報表(DTQ)：第11-15行 所有的請求都可以被粗略的分入五類：資料操作語句(DMS)，查詢緩存命中(QC Hits)，COM_QUIT，其他的COM_指令以及其他未知的東東。接下來的五行分别顯示這些，從大到小排列。這樣你可以一眼看出mysql最重要的任務是什麼了。一般的說，DMS和QCHits是主角，COM_是必須的，額，路人甲…… 再詳細解釋每行之前，提示一下，第三列的比值分母是上面那行的總值。比如本例中DMS占到了82.84%就挺不錯的。

DMS包括：select/insert/replace/update/delete(其他的比較偏門，mysqlreport幹脆就排除他們了)。正常的說，mysql最應該做的事情，就是這些DMS了。詳細内容見17-22行。 QC Hits是mysql從查詢緩存裡直接擷取結果的數量。我們夢寐以求的就是讓這個命中率變高，因為這意味着mysql響應變的相當快。但這也意味着你必須接受一定的資料差異性。詳細理由見QC報表中的insert/prune和hit/insert比值部分。 在本例中QC Hits達到了16.91%，看來很不錯的樣子。可别被這麼一條資料迷惑了，38-45行的QC報表會給你一個完全不一樣的結論。 COMQUIT，嗯，湊數的。 COM，如果這個值比較高的話，或許會有些問題，詳見23-26行。 Unknown，理想情況下，有上面四個類别就夠了。因為有時候，mysql處理了幾個請求，卻沒有記錄相應的操作數。是以Unknown有+-兩種。+說明mysqlreport統計的多了，-就是少了。這個類别浮動性很大，某些特定情況下，可能會排名很靠前，不過最好還是在最底下吧。

慢查詢：第16行 第16行非常重要，展示的是mysql執行的慢查詢數。預設情況下，配置的’long_query_time’是10秒鐘。事實上，大家都覺得這太長了，一般都改成1秒，甚至更短，mysql在版本5以後，支援到微妙us級别的。 配置的’long_query_time’會在’Slow’後面顯示，預設8個字元，是以如果配置的是’9.999999 ms’，隻會顯示成’999.999 ‘，不過應該不會這麼無聊吧~ 理想情況下最好這裡永遠都是0，不過不太可能，多少還是有點。隻要第三列的比例低于0.05%就行。 第四列，DMS中的slow比值；第五列，慢查詢日志是否記錄。強烈建議選擇ON。

DMS：17-22行 和DTQ一樣，第一行也是總值，其餘内容也是動态排名。這部分内容可以解釋mysql伺服器偏重于什麼類型：select還是insert，或者其他。一般來說會是select吧。明确這個問題，對我們了解其他數值有很大的幫助。比如說：一個insert型的mysql，他的寫比率接近1.0，同時帶來比較高的表鎖。然後很可能用innodb表；一個select型的mysql則相反，讀比率高，表鎖少，而且很可能用的是MyISAM表。 本例就是一個select型的。65.72%的總請求是select(在DMS的比例提高到79.33%)，顯然我們可以朝着select的方向進行優化了。

COM_：23-26行 這部分内容都很直覺，在mysql協定裡都有，想com_change_db，一眼就知道是幹嘛的。 如果在DTQ排名裡COM_比較高，那說明mysql忙着幹自己的事情而不是響應SQL查詢。比如說，如果一台mysql的com_rollback高，可能糟糕了，你的事物復原失敗了。結合之前的DTQ報表分析這個東西吧~ 一般這些東西不能出什麼問題，不過時不時看一眼還是有必要的。

select/sort報表：28-36行 select和sort都是select_的内容。其中最主要的是29和31行：scan和full join。scan展示的是對全表進行掃描的select語句個數。full join和scan很像，除了它還出現于多表查詢。程式聯合多表進行全表掃描，聽起來就慢的可怕……總之，對于這兩個數值，隻有更低，沒有最低！

QC報表：38-45行 隻有mysql版本支援QC并且my.cnf開啟了QC的時候該報表才會出現。 記憶體使用：39行 如果記憶體使用的接近最大設定值，在更下面的Prune資料上也會有反應，因為QC裡的查詢會被踢出來。 記憶體碎片：40行 記憶體塊碎片(block fragmnt)的詳細解釋參見《MYSQL手冊》的5.14.3章節所述： ‘query_cache_min_res_unit’的預設值是4KB，大多數情況下足夠用了。如果你有一個傳回超小結果的海量查詢，預設的塊大小(即4KB)可能會導緻大量的記憶體碎片，同樣也浪費了很多空閑記憶體塊。因為記憶體不足，碎片會強制删除(prune，我也不知道為啥不叫delete或者purge)QC裡的部分内容。這時候你就得降低這個’query_cache_min_res_unit’設定。至于空閑塊和QC的删除閥值，分别由’Qcache_free_blocks’和’Qcache_lowmem_prunes’定義。 記憶體碎片的計算方法是空閑記憶體塊除以總記憶體塊數。比值越大，碎片越多，10-20%就已經超出平均水準了。 本例的13.05%還是可以接受的，不過最好還是檢查一下’query_cache_min_res_unit’是不是能調調~

Hits/Inserts/Prunes：41-43行 Hits是最重要的，它反映了select有多少是從QC裡獲得的應答，當然越多越好。至于insert和prune，或許從44行的比值中更好了解一下。之前有提到prune多說明qc太小，當然這隻是一種可能而已。 本例中，隻有55%的QC被利用，而碎片又不是太高。prune達到每秒16次，比QCHits高了一倍！打個不太恰當的（這話我加的，感覺比直接了解技術更難懂的比喻）比方：這台mysql的QC就像蘋果樹一樣，蘋果還沒摘呢，樹枝已經被砍掉了……

insert/prune和hit/insert比：44-45行 insert/prune是一個波動性的QC名額。一個穩定運作中的QC，insert進QC的查詢數量應該大于prune掉的查詢數量。而一個不穩定的QC，比值或許是1:1，甚至偏向prune。這說明兩個問題：1、QC大小不夠；2、mysql試圖緩存一切，結果幫了倒忙~ 如果是第一種情況，簡單的加大QC大小就夠了。然後再觀察碎片和記憶體使用率的情況。 但更多的時候是第二種情況。因為QC設定裡開啟的預設type1就是要求mysql盡可能的緩存一切東西。

mysql官方說明裡這麼解釋這個’type 1’的：“緩存一切查詢結果，除非查詢時使用’select sql_no_cache’方式”。可惜這個’sql_no_cache’基本沒人用。另一個稍微好一些的方式是’type 2 demand’，解釋如下：“隻有在查詢使用’select sql_cache’時才緩存查詢結果”。這個type對開發人員要求比較多，因為他們得明确指出哪些要緩存，哪些不要。不過也沒人比他們更清楚到底哪些是該緩存的啦~ hit/insert用來反映QC的有效性。理想情況是：mysql插入一批穩定的查詢到QC裡，然後源源不斷的命中這批結果……是以，如果QC的有效性足夠，這個比值應該是偏向hit的。如果不幸的偏向了insert，那說明QC其實沒起到太大的作用。比如說1:1，一次insert用了一次hit，然後就被替換了，這完全違背了使用QC的初衷。不過還有更糟的，比如0.34:1,一次都沒用上，就被prune掉了…… 本例的QCHits不低，hit/insert卻不高。再考慮到記憶體使用和碎片情況也還可以。或許真的有必要換成type2的DEMAND了~~

表鎖報表：47-49行 表鎖報表包括兩行，一行是總數，一行是目前數。鎖等待對于資料庫來說永遠是糟糕的事情。第三列的總比值反應了一個綜述的情況，無論如何不能高過10%，否則肯定就帶來一大堆的索引和慢查詢問題！

表報表：51-53行 也是兩行，一行是目前mysql打開表的個數、表緩存的使用率，一行是mysql運作以來的平均值。 這裡有兩個值比較重要。一個是表緩存使用率，哪怕高到100%都行。不過要是真高到100%了，可能你的’table_cache’設定已經不夠了，趕緊加大吧。第二個是目前打開表的比率，這個也能協助判斷’table_cache’設定是否合理。一般這個值應該小于每秒1次。不過一個負載比較高而又運作的還不錯的mysql，可能能達到每秒打開7次表，依然保持100%的表緩存~

連接配接數報表：55-57行 如果最大連接配接數曾經接近過100%，請加大’max_connection’設定。不過事實上，預設的100已經足夠絕大多數哪怕相當繁忙的mysql使用了，盲目加大這個設定其實不對。一個mysql連結持續1秒鐘，100個就是足足100秒。是以如果連接配接數太高，或者說一直在慢慢漲，問題很可能在别的地方，比如慢查詢、糟糕的索引、甚至DNS解析太慢。在修改這個數的事情，還是先去研究一下為什麼100個還不夠呢？ 至于每秒連接配接數，隻要mysql運作正常，高低無所謂。不過大多數mysql這個值在每秒5次以下

臨時表報表：59-62行 mysql可以在記憶體、磁盤甚至臨時檔案上建立臨時表。這三種情況分别對應下面的三條報告。這些資料沒有标準值，不過必須要知道的是磁盤上的臨時表示最慢的。mysql一般也避免在磁盤上建立臨時表，除非達到了’tmp_table_size’的閥值。這個閥值會顯示在記憶體(Table)那行的Size列後面。至于記憶體和臨時檔案的數值到底該多少，取決于mysql資料庫的硬體配置。

線程、中斷、流量報表：64-76行 這三個報表是最重要的，系統級别的問題不用多說了都…… 這裡面有一個需要注意的地方：66行的線程命中率。每個mysql的連接配接都是一個單獨的線程。mysql啟動時，隻建立不多的幾個線程和一個線程緩存，以節省不斷建立和銷毀線程的開銷，哪怕這個開銷不怎麼明顯。當mysql的連接配接數超過了線程緩存數(由’thread_cache_size’定義)時，MySQL開始出現線程抖動(‘thread thrash’)。為了接納新的連接配接，mysql瘋狂的建立新線程，結果自然是線程命中率大幅下滑。 線程抖動是問題麼？Yahoo的Jeremy Zawondy在部落格中寫了如下一段話： 是以上面這個故事的教訓就是：如果你的伺服器老是接受一些快速連接配接，想辦法加大你的線程緩存吧，直到你的show status指令裡’threads_created’不再飙升為止！你的CPU絕對會感謝你的。線程緩存不是啥大問題。可是你解決完真的大問題後，它就是最大的問題了……(汗) 是以，如果真出現線程抖動的話，加大’thread_cache_size’吧。 比如本例，線程緩存命中率隻有可憐的0.05%！基本意味着每個連接配接都是新建立了線程。不過看看報表其他的内容，這也是必然的：緩存裡一個線程都沒有(66行)，201個線程被建立(67行),202個連接配接(57行)……

https://www.cnblogs.com/cnsanshao/archive/2013/02/28/2936903.html