性能分析之 MySQL Report 分析（建議收藏）

基本信息

索引報表

操作報表

查詢和排序報表

查詢緩存報表

表鎖報表

表信息報表

連接報表

臨時表報表

線程報表

InnoDB緩存池報表

innodb 鎖報表

InnoDB 數據、頁、行報表

基本信息

索引報表

操作報表

查詢和排序報表

查詢緩存報表

表鎖報表

表信息報表

連接報表

臨時表報表

線程報表

InnoDB緩存池報表

innodb 鎖報表

InnoDB 數據、頁、行報表

聲明：近期在工作時需要用到 Mysqlreport 時，做的一些整理。

基本信息

Mysql 當前的版本，運行的時間，以及當前系統時間。 MySQL 服務器版本信息表明 MySQL 服務器包含和不包含哪些特點。

MySQL 服務器運行時間表明報告價值的代表性。服務器運行時間對于評估報告是很重要的，因為如果服務器不運行幾個小時的話，輸出報告有可能存在曲解和誤導性。

有時甚至運行幾個小時時間都是不夠的，比如，MySQL 服務器運行了午夜的 6 個小時幾乎沒有業務訪問過。

最理想的情況是，MySQL 服務器運行一天之后再運行 mysqlreport 來輸出報告，這樣報告的代表價值要比系統剛運行時要好的多。

在性能場景的運行周期前啟動 MySQL ，在性能場景結束后生成 mysqlreport 會比較有用。

比如此例中，場景運行了1小時后執行了 mysqlreport。

MySQL 5.7.18-log uptime 0 1:0:51 Sat Sep 22 21:43:01 2018

索引報表

說明 mysql 當前索引緩沖區的使用率，如果過高，則需要調整 keybuffersize 的大小了。write hit 及 read hit 分別說明了寫索引和讀索引的效率。 keybuffersize 對 MyISAM 引擎的性能有很大的影響。 不能表明是否 MySQL 服務器索引是否良好，但是能夠指示 shared key buffer（共享索引緩存）是否被充分利用。本部分僅能顯示用于 MyISAM 表的共享索引緩存，其他的緩存并沒有顯示。

MySQL 服務器的首要問題：

索引緩存用了多少？如果它并沒有被用很多，說明狀況比較好，MySQL 僅僅是在需要時分配了用于索引緩存的系統 RAM。也就是說，如果索引緩存設置為 512M，那么 MySQL 不是在系統開始時就分配的 512 M內存，MySQL 僅僅會當需要時才會分配 512 M那么多！

Buffer used，顯示的就是 MySQL 曾經一次分配的最大的數量。實際上，MySQL 實際的使用量會少，也有可能多于這個數。MySQL 稱之為“高水位（high water mark）”。

這一指標通常說明 MySQL 的配置指標中 keybuffersize 是否足夠大。 如果報告的此行指示，MySQL 已經占用了索引緩存的 80 %到 90 %，那么 keybuffersize 的參數就應該增大。

注意，這一行的指標是不可能超過 95 %的。考慮到 mysqlreport 無法計算一些內部數據結構對索引緩存的占用情況，所以，95%通常也就是表示為 100%。 在本例子中，MySQL 利用了 8M 緩存的 3k 空間，所以索引緩存是基本都沒有用到。

在本例中，keybuffersize=8388608，即 8M。

__ Key _________________________________________________________________ Buffer used 3.00k of 8.00M %Used: 0.04

請注意，這里所指的 Key Buffer 是指 MyISAM Storage Engine 所使用的 Shared Key Buffer，InnoDB 所使用的 Key Buffer 并不包含在內。 MySQL Server 的 Buffer 大略可分為二種：

Global Buffer：由所有 Client 所共享的 Buffer、keybuffer、innodbbufferpool、innodblogbuffer、innodbadditionalmempool net_buffer …等等

Thread Buffer：個別的 Connection 所需占用的 Buffer 例如： sortbuffer、myisamsortbuffer、readbuffer、joinbuffer readrnd_buffer …等等。

計算 Server 至少需使用的總內存數量的方式為：

m

i

n

m

e

m

o

r

y

n

e

e

d

e

d

=

g

l

o

b

a

l

b

u

f

f

e

r

+

(

t

h

r

e

a

d

b

u

f

f

e

r

s

?

m

a

x

c

o

n

n

e

c

t

i

o

n

)

minmemoryneeded = globalbuffer + (threadbuffers * max_connection)

minmemoryneeded=globalbuffer+(threadbuffers?maxc onnection)

關于 MySQL 的 Cache 機制有一點需要特別注意，MyISAM Storage Engine 將每個 table 分成三個檔案儲存在硬盤之中，例如若有一個數據表的名稱為 example，那么就會在硬盤上發現 example.FRM, example.MYD,example.MYI 等三個檔案。

這三個檔案所儲存的數據如下：

FRM： 儲存這個數據表的結構

MYD： Row Data，也就是你存在 example 數據表里的數據

MYI： 此數據表的索引

接下來是重點： 當 MySQL 要 Cache 某個資料表時，MySQL 只會 Cache 索引，也就是 MYI 檔案，而 Row Data( MYD )則是交由操作系統來負責 Cache。

到底 Key Buffer 要設定多少才夠呢？如前所述，MySQL 只會 Cache 索引(MYI)，因此只要將數據庫中所有的 MYI 檔案加總起來，你就會知道大概要設為多少。

這一行指示 MySQL 在生成報告時占用索引緩存的情況（4.1.2和以后的版本有效，因為 Keyblocksunused 是在 4.1.2 以后才增加的。）。如果前一行表示是一個高水位指示，那么這一行的占用量應該是小于或等于上一行的高水位。有時也會高于高水位指示，這是不是 MySQL 的 Bug 目前還不得而知。不管怎樣，這一行和前一行的結果能夠很好地指示 keybuffersize 的參數設置的是不是足夠大。

在本例子中，MySQL服務器狀態就不太好，占用了8M，是索引緩存的 100 %，已經是全部的空間了。

Current 8.00M %Usage: 100.00

從以上兩個數據就可以看到的是，mysql 每一次分配的 key buffer都不大，最大的也只有 3 K，但整個 key buffer 占用卻已經達到100 %了，所以還是要增加 key buffer size 才行。

索引（keys）是使用固有 RAM 分區的。這一行，Write hit，顯示了索引寫入的效率（這是個百分比比率值，分子是索引寫入硬盤的量，分母是索引寫入緩存的量）。索引寫入命中率沒有一個標準值。索引寫入命中率依賴于 MySQL 服務器基本執行的是哪種語句。

如果MySQL主要執行的是 updates 或者 inserts，那么索引寫入命中率可能接近 0%是可接受的；

如果MySQL主要執行的是selects，那么索引寫入命中率可能 90%或更多是可接受的。

一個負數百分比表示 MySQL 寫入索引到硬盤多于寫入緩存，這樣通常會很慢、不合理的、不可接受的。 最好描述索引寫入命中率的方法，就是需要你知道 MySQL 服務器主要執行哪些語句，DMS報告（Lines 17-22）能夠幫助解決這一問題。結合 Questions 部分的 DMS 里 insert 的數據量，可知，在這個場景執行的過程中，主要是 insert 語句，所以這里的索引寫入命中率接近0%的。

Write hit 0.00%

計算公式：Write hit = MySQL 將索引寫入硬盤的次數 / MySQL 將索引寫入 RAM 的次數

比索引寫入命中率更重要的是索引讀取命中率（key read hit）。這一行描述了索引讀取的效率（這是個百分比比率值，分子是索引讀取硬盤的量，分母是索引讀取緩存的量）。索引讀取命中率應該低于99%。 過低的百分比表明這會是一個問題。一個低索引命中百分比通常可能是索引空間太小了。索引空間是為了避免MySQL裝載過多的索引到內存中，當這種情況發生時，MySQL會恢復從硬盤讀取索引，這就會使得硬盤相當慢而且會使索引無效。 通常來講，開始運行MySQL的前1-2個小時，這一行的值會低于99%，經過 1-2 個小時以后，取值就會接近99%。

Read hit 0.00%

R

e

a

d

h

i

t

=

M

y

S

Q

L

從硬盤讀取索引的次數

/

M

y

S

Q

L

從

R

A

M

讀取索引的次數

Read hit = MySQL從硬盤讀取索引的次數 / MySQL從RAM讀取索引的次數

Readhit=MySQL從硬盤讀取索引的次數/MySQL從RAM讀取索引的次數

操作報表

操作報表的第一行表示了 MySQL 回應了所有問題的總數和更新時間內的平均回應率。

從下面的數據可以看到，每秒 mysql 的操作是 3.2 k次（QPS），但是有多少完成了，就要看下面的幾行數據了。 （這里需要說明的是，“Questions（操作）”是被響應的，而“（Queries查詢）”是被執行的。mysqlreport 可以分辨出“操作”和查詢，特別是在“操作數報告”中。“操作”是每個和各種對MySQL服務器的請求，這包含了SQL查詢和MySQL特定的命令和協議通信，查詢是僅包含SQL查詢：SELECT, UPDATE 等）

__ Questions ___________________________________________________________ Total 32.20k 3.2k/s

所有的“操作”可分為5類：

數據操作語句（DMS）

詢緩存命中率（QC Hits）

COMQUIT

其他通信命令

未知。

這 5 個分類是動態顯示的。mysqlreport 按照他們的總次數降序排列。這個子報告能夠明顯的表示出 MySQL 在忙著干什么。

理想情況下，MySQL 應該忙于 DMS 或者 QC Hits，因為這些行為時真正完成某些事情的。COMQUIT，Com 和 Unknown 類別是必要的，但是處于次要地位。 在進一步解釋每一類之前，需要說明的是這部分子報告第三列表明該列值占總“操作”請求數的百分比，“操作”部分的其他子報告也是如此。

在例子中，DMS 數占總操作數的 82.84 %是正常示數。 Data manipulation statements（DMS）包括SELECT，INSERT，REPLACE，UPDATE和DELETE。基本上，DMS 是 MySQL 數據庫干的最有用的，因此，DMS 應該是 MySQL 做的最多的。DMS 子報告會詳細顯示。 QC Hits（Query Cache Hits） 是MySQL查詢執行過程中，通過查詢緩存補償，而不是實際執行的操作數。具有一個較高的QC Hits數是令人期待的，因為QC的返回是非常快的。但是，完成有效地QC緩存是非常困難的。這在后面的Query Cache Report中的Insrt:Prune和Hit:Insert部分將深入解釋。

在例子中，QC Hits 是沒有顯示的，說明在這個 report 期間沒有 select 語句。

COMQUIT 是個可以忽略的無關緊要的參數，它包含到報告中為了保證完整性。

Com_ 表示 MySQL 處理的各種命令，通常都是協議相關的。理想情況下，在這個指標應當比較低，因為當比較高時，說明 MySQL 忙而無用。該分類參數過高，則表示一些怪異的問題，后面在 Com_ 將詳細討論。

Unknown 是一個推測的目錄。理想情況下，前四部分的總和應該是等于全部“操作”數量，但通常不相等。這是因為存在一些MySQL的操作，增加了操作計數器，但是并沒有表現在單獨的指標上。 這一行會動態顯示為 “+Unknown” 或者 “-Unknown”。"+Unknown"表示存在更多的操作數，比 mysqlreport 計算的多；"-Unknown" 表示 mysqlreport 計算的數比所有的統計數少。

DMS 32.20k 3.2k/s %Total: 99.99 COM_QUIT 3 0.3/s 0.01 -Unknown 2 0.2/s 0.01 Com_ 1 0.1/s 0.00

指示了 MySQL 執行的慢查詢數目有多少。影響“Slow”指標的系統參數longquerytime，這一參數默認值為10s。很多人認為10s是在數據庫時間中時一個恒定值，longquerytime最好設置為1或者毫秒級單位（毫秒設置在MySQL的新版本中支持） longquerytime，這一參數值只有慢查詢之后才會顯現出來。在mysqlreport v3.5以后，該參數支持：秒、毫秒、微妙。在某些情況下，該參數由于顯示寬度8個字母的限制。

例如，longquerytime的參數值’999.999 ms’ 截斷成’999.999 '，‘10.000100 s’ 截斷成 '10.0001 '。 理想情況下，慢查詢的統計應該為0，但是通常也會有一些慢查詢的存在。一般來說，慢查詢的比率（第三列）占整個操作數的0.05或更低。當有很多慢查詢（第一列），這是的比率值就會顯示出問題。這一行還增加了一列：DMS 操作數百分比。對于慢查詢，0 是最好的，這一列在DMS子報告中更加有用。 最后一列，Log，表示慢查詢日志功能開啟還是關閉（通過設置logslowqueries參數）。慢查詢日志通常是打開的。

Slow 10 s 0 0/s 0.00 %DMS: 0.00 Log: ON

DMS 子報告，和 DTQ 子報告一樣，第一列是按照降序排列的,第二列是按每秒計算的結果，第三列表明該列值占總數的百分比。表示了前文所提到數據操作數（SELECT、INSERT等）。 第一行顯示的和DTQ報告中的顯示一樣的。 這一子報告顯示MySQL數據庫是哪一種類的數據庫：是查詢負荷高、還是插入負荷高、還是其他的。MySQL 服務器都是傾向于查詢負荷高（SELECT heavy）。了解是哪種類型的 MySQL 數據庫有利于理解其他的報告值。

例如，一個插入負荷高的服務器，其寫入率會接近為1.0，這種類型的數據庫鎖表報告值也會偏高，這類數據庫適合采用 InnoDB 類型表；一個查詢負荷高的數據庫，就會表現出讀取率為1和一個較低的表鎖值，這種類型的數據庫需要采用查詢緩存，適合于采用 MyISAM 表。 在這個例子中，服務器是一個插入高的數據庫。很明顯，這個數據庫面向插入事務。知道數據庫類型就有利于數據庫參數的優化。

DMS 32.20k 3.2k/s 99.99 INSERT 16.10k 1.6k/s 50.00 50.00 SELECT 16.10k 1.6k/s 50.00 50.00 REPLACE 0 0/s 0.00 0.00 DELETE 0 0/s 0.00 0.00 UPDATE 0 0/s 0.00 0.00

Com 子報告和其他子報告一樣分類排序。這部分子報告的內容不同于服務器到服務器命令，因為每一行指示的Com指標都是表現的 MySQL 協議的命令，你可以參考 MySQL 的幫助文檔理解這部分概念。 大部分的條目名稱都是很直觀的，比如Comchangedb。 這部分指標當 DTQ 子報告中的 Com 最高時才起作用，此時表明 MySQL 正忙于“程序事務”而不是 SQL 查詢事務。舉個例子，一臺服務器的 Comrollback 指標很高。

rollback 發生在事務處理失敗的時候。服務器的每一次事務處理都失敗，很顯然，服務器是有問題的。在沒有 mysqlreport 的情況下，很明顯是不可能分辨出服務器的這些問題的。 大部分服務器，Com_子報告顯示沒有異常，但是時常地檢查該部分報告是很有必要的。

Com_ 1 0.1/s 0.00 show_status 1 0.1/s 0.00

查詢和排序報表

Scan 指的是有多少 SELECT statements 造成 MySQL 需要進行 Full Table Scan。

Full Join 的意思與 Scan 差不多，但它是適用在多個 Tables 相互Join 在一起的情況。這二種情況的執行效能都非常的差，因此原則上你會希望這兩個數值越低越好。但這也不是絕對的，仍然要考慮實際的情況，例如雖然Server 有很高比例的 Scan，但若這些 Scan 都是針對一些只有幾十筆數據的 table，那么相對而言它依然是十分有效率的；但反之，若這些 Scan 是針對具有上百萬筆數據的 table，那么就會嚴重影響系統效能。

__ SELECT and Sort _____________________________________________________ Scan 2 0.2/s %SELECT: 0.01 Range 0 0/s 0.00 Full join 0 0/s 0.00 Range check 0 0/s 0.00 Full rng join 0 0/s 0.00 Sort scan 0 0/s Sort range 0 0/s Sort mrg pass 0 0/s

查詢緩存報表

查詢緩存的目的很簡單，將 select 查詢的結果緩存在內存中，以供下次直接獲取。在這個場景中緩存查詢是沒有開啟的。

query_cache_size = 1048576 query_cache_type = OFF query_cache_limit = 1048576

在查詢為主的數據庫的由要開啟緩存查詢，并且要配置合理的查詢緩存大小。

但是，查詢緩存有一個需要注意的問題，那就是緩存過期策略，MySQL 采用的機制是，當一個數據表有更新操作（比如 update或者 insert ）后，那么涉及這個表的所有查詢緩存都會失效。這的確令人比較沮喪，但是 MySQL 這樣做是不希望引入新的開銷而自找麻煩，所以“寧可錯殺一千，不可放過一個”。這樣一來，對于 select 和 update 混合的應用來說，查詢緩存反而可能會添亂

比如說如下 mysqlreport 中查詢緩存的報告：

__ Query Cache ______________________________________________________ Memory usage 38.05M of 256.00M %Used: 14.86 Block Fragmnt 4.29% Hits 12.74k 33.3/s Inserts 58.21k 152.4/s Insrt:Prune 58.21k:1 152.4/s Hit:Insert 0.22:1

如果你的應用中對于密集 select 的數據表很少更新，很適合于使用查詢緩存。 Block Fragmnt這個數值越高表示 Query Cache的碎片狀況越嚴重，通常它會界于 10%~20% 之間。在此范例中 Block Fragmnt為 100%，這是不可接受的情況。可以調整 querycacheminresunit 的值來降低 Block Fragmnt。

__ Query Cache _________________________________________________________ Memory usage 16.35k of 1.00M %Used: 1.60 Block Fragmnt 100.00% Hits 0 0/s Inserts 1 0.1/s Insrt:Prune 1:1 0/s Hit:Insert 0.00:1

再來分析另一個例子中的 QC 情況：

__ Query Cache_________________________________________________________ Memory usage 17.81M of 32.00M %Used: 55.66 Block Fragmnt 13.05% Hits 16.58k 8.02/s Inserts 48.50k 23.48/s Prunes 33.46k 16.20/s Insrt:Prune 1.45:1 7.28/s Hit:Insert 0.34:1

Hits 是這三個數值中最重要的一項，因為它指出有多少 SELECT statements 是可直接從 Query Cache 里面取得所需的信息，此數值 越高就越好。

Inserts 和 Prunes 最好是從比值來觀察比較容易理解。雖然 Prunes 的值偏高可能代表著Query Cache 設得不夠大，但并不一定是如此。在上例中只有 55% 的 Query Cache 被使用，有著相對而言算低的fragmentation 值。但 Prunes 值偏高，Prunes 的值(16/s)是 QC Hits 的兩倍。可以想象這臺 Server 的 Query Cache 是一顆蘋果樹，它的樹枝被剪去的速度比你采收蘋果的速度還快。 Insert 與 Prune 的比值可顯示 Query Cache 的揮發性。在一個高度穩定的 Query Cache 中，Insrt 的值應該要高于 Prune 的值；反之，在一個揮發性較高(較不穩定)的 Query Cache 中，這個比值將會是 1:1 或是偏重在 Prune 那方，這表示 Query Cache 中的數據有可能在使用到之前就已經被清了。我們會希望擁有一個穩定的 Query Cache，因為穩定的 Query Cache表示那些被 Cache 在 Query Cache 中的資料會常被用到。高揮發性(較不穩定)的Query Cache 代表兩件事情：

第一，Query Cache 設得太小，需要加大。

第二，MySQL 正試圖要 cache 所有的東西，甚至是那些其實并不需要 cache 的數據。

若是第一種狀況，只要單純的加大 Query Cache 即可。

若是第二種情況，可能是 MySQL 試圖要去 cache 所有可以 cache 的數據，你可以使用 SQLNOCACHE 來明確的告訴 MySQL 什么資料是你不想要 cache 的。

Hit 與 Insert 的比值代表著 Query Cache 的有效性，理想的情況是我們新增了一些 Qeury 到 Query Cache中，然后希望得到許多 Hits。因此若是這個 Query Cache 是有效率的，那么該比值應該要偏重在左方。若比值是偏重在 Insert 那方，那么這個 Query Cache 的揮發性就太高了。考慮以下這個比值，若 Hit:Insert 為 1:1，那就表示Query Cache 中的數據只使用了一次就被清除掉了，換句話說，我們放進去的數據比我們從里面拿出來的數據還多，這樣一來就失去了使用 Query Cache 的意義。

回想我們前面所提過的，雖然在本范例中 QC Hit 在全部的 Questions 中占了很高的比例，但實際上我們可以發現 QC 的有效性其實是很低的(Hit:Insert 的比值偏重在 Insert 那方)。若造成這個現象的原因是 MySQL 正試圖cache 所有的東西，那么將 Cache 模式改為 DEMAND 或許可以解決此問題。

表鎖報表

Waited 表示有多少次查詢需要等待表鎖定；Immediate 表示有多少次查詢可以立即獲得表鎖定，同時后面還有一個比例，表示等待表鎖定的查詢次數占所有查詢次數的百分比，這里是0.00%，非常好，但為什么這么低呢？

這需要了解 MyISAM 的表鎖定機制。 MyISAM 的表鎖定可以允許多個線程同時讀取數據，比如 select 查詢，它們之間是不需要鎖等待的。但是對于更新操作（如 update 操作），它會排斥對當前表的所有其他查詢，包括 select 查詢。除此之外，更新操作有著默認的高優先級，這意味著當表鎖釋放后，更新操作將先獲得鎖定，然后才輪到讀取操作。也就是說，如果有很多 update 操作排著長隊，那么對當前表的 select 查詢必須等到所有的更新都完成之后才能開始。

舉例如下：

__ Table Locks______________________________________________________ Waited 1.01k 0.49/s %Total: 1.24 Immediate 80.04k 38.74/s

這個部份包含了兩項信息：

第一項是 Waited，代表 MySQL 需要等待以取得 table lock 的次數。

第二項是 Immediate，表示MySQL 不需要等待即可立刻取得 table lock 的次數。

對數據庫來說『等待』幾乎可以肯定是一件很不好的事情，因此 Waited 的值應該要越小越好。最具有代表性的是第三個字段 (Waited 占所有 table lock 的百分比)，這個數值應該要小于 10%，大于這個值就表示table/query 的索引設計不良或是有過多的 Slow Query。

從下面的數據來看，在場景執行期間就沒有發生過鎖表的情況。

__ Table Locks _________________________________________________________ Waited 0 0/s %Total: 0.00 Immediate 1 0.1/s

表信息報表

第一是 Open，顯示目前正開啟的 table 數量、總共可開啟的最大數量，以及 Table Cache 的使用狀況。

第二是 Opend，表示截至目前為止 MySQL 總共開啟過的 Table 數量，以及除上 Uptime 后的比值。

這里有兩件事值得注意：

首先是 Table Cache 的使用狀況，100% 的 Table Cache 使用率并不是一件壞事但你可以試著調大 Table Cache 以增進效能

第二是 MySQL 開啟 Table 的平均速率，若這個值很高則表示的 table_cache 設得太小了，需要調大一些。

一般來說， MySQL 開啟 Table 的平均速率最好是小于 1/s。但大于這個數值也不一定就是壞事，有些調校良好且運作的十分有效率的 MySQL Server 其值為 7/s 并使用了 100% 的 Table Cache。

__ Tables ______________________________________________________________ Open 816 of 2000 %Cache: 40.80 Opened 1 0.1/s

連接報表

Connections Report 所代表的意義與 Tables Report 相似，請各位以此類推。

比較需要注意的是：若你發現 Connections 的使用率接近 100%，也許你會想調大 maxconnections 的值以允許MySQL 的 Client 建立更多連接。然而，這通常是一種錯誤。常常可以發現很多網絡上的數據會教我們要調大 maxconnections，但卻從來沒有給一個明確的理由。事實上，maxconnections 的默認值(100)，就算是對于負載十分沉重但有良好調校過的 Server 都已十分足夠。

MySQL 對于單一連接的數據處理通常只需要零點幾秒的時間即可完成，就算是最大只能使用 100 個連接也夠讓你用上很長一段時間。若是的 Server 有著非常高的最大連接數(max connections)或是單一連接需要很長時間才可完成，那么問題八成不是 maxconnections 的值不夠大而是在別的地方，例如 slow queries、索引設計不良、甚至是過于緩慢的 DNS 解析。

在將 max_connections 的值調到 100 以上之前，應該要先確定真的是因為Server 過于忙碌而需要調高此數值，而不是其它地方出了問題。每秒平均連接數有可能會很高，事實上，若這個值很高而且 Server 的運作十分順暢，那么這通常會是一個好現象，無需擔心。大部份 Server 的每秒平均連接數應該都會低于 5/s。

__ Connections _________________________________________________________ Max used 604 of 2000 %Max: 30.20 Total 5 0.5/s

臨時表報表

或許看到一些 explain 查詢在分析時出現 Using temporary 的狀態，這意味著查詢過程中需要創建臨時表來存儲中間數據，我們需要通過合理的索引來避免它。

另一方面，當臨時表在所難免時，我們也要盡量減少臨時表本身的開銷，通過 mysqlreport 報告中的 Created Temp 部分，我們可以看到：

_ Created Temp _____________________________________________________ Disk table 864.89k 2.0/s Table 7.06M 16.1/s Size: 32.0M File 9.22k 0.0/s

MySQL 可以將臨時表創建在磁盤（Disk table）、內存（Table）以及臨時文件（File）中，顯然，在磁盤上創建臨時表的開銷最大，所以我們希望 MySQL 盡量不要在磁盤上創建臨時表。

在 MySQL 的配置中，我們可以通過 tmptablesize 選項來設置用于存儲臨時表的內存空間大小，一旦這個空間不夠用，MySQL 將會啟用磁盤來保存臨時表，你可以根據 mysqlreport 的統計盡量給臨時表設置較大的內存空間。

在本示例中，臨時表的情況如下，只用到了一個臨時表。

__ Created Temp ________________________________________________________ Disk table 0 0/s Table 1 0.1/s Size: 16.0M File 0 0/s

線程報表

MySQL 采用多線程來處理并發的連接，通過 mysqlreport 中的 Threads 部分，可以看到線程創建的統計結果：

Threads _____________________________________________________ Running 2 of 5 Cached 0 of 0 %Hit: 0 Created 6.15M 43.6/s Slow 0 0/s

也許你會覺得創建線程的消耗不值一提，但是所謂優化都是在你系統繁忙下的救命稻草。 一個比較好的辦法是在應用中盡量使用持久連接，這將在一定程度上減少線程的重復創建。

另一方面，從上面的 Cached=0 可以看出，這些線程并沒有被復用。 在本例中，threadcachesize = 64，只用到了 5 個線程，并且沒有復用（Cached)的。

__ Threads _____________________________________________________________ Running 5 of 64 Cached 0 of 64 %Hit: 100 Created 0 0/s Slow 0 0/s __ Aborted _____________________________________________________________ Clients 0 0/s Connects 4 0.4/s __ Bytes _______________________________________________________________ Sent 27.43M 2.7M/s Received 18.84M 1.9M/s

上面的 %HIT 需要關注下，每一個連接到 MySQL 的聯機都是由不同的 Thread 來處理，當 MySQL 啟動時會預先建立一些 Threads 并保留在 Thread Cache 中，如此一來 MySQL 就不用一直忙著建立與刪除 Threads。

但當每秒最大聯機數大于 MySQL 的 Thread Cache 時，MySQL 就會進入 Thread Thrash 的狀態：它不斷地建立新的 Threads 以滿足不斷增加的聯機的需求。當 Thread Thrash 發生時，%Hit 的數值就會降低。在本范例中 %Hit 的值為 100%，這是非常好的，因為它表示幾乎每一個新進來的聯機都不會造成 MySQL 建立新的Thread。

而在本例中，threadcachesize 是 64，當前 Running 的 threads 也很少，可見并沒有太大的壓力。

InnoDB緩存池報表

nnodbbufferpoolsize 定義了 InnoDB 存儲引擎的表數據和索引數據的最大內存緩沖區大小。和 MyISAM 存儲引擎不同， MyISAM 的 keybuffersize只能緩存索引鍵，而 innodbbufferpoolsize 卻可以緩存數據塊和索引鍵。適當的增加這個參數的大小，可以有效的減少 InnoDB 類型的表的磁盤 I/O。

如果你在 MySQ L中大量使用 Innodb 類型表，則可以將緩沖池大小設置為物理內存的 60%-80%（最好配置成自增長的緩沖池），并持續關注它的使用率。 如下 Usage 表示, 總的緩存 16 G中, 當前已占用 2.27G, 使用率 14.20 %，這種情況下完全不用考慮增加 innodb 緩存池。 Read hit 表示緩存命中率 100%, 這個數值是比較理想的, 一般情況下, 都應該大于 99.98%.

__ InnoDB Buffer Pool __________________________________________________ Usage 2.27G of 16.00G %Used: 14.20 Read hit 100.00%

這部分數據和 innodbflushlogattrx_commit 參數關系非常大。

innodbflushlogattrx_commit = 1 表示事務提交時立即將事務日志寫入磁盤，同時數據和索引也立即更新。這符合事務的持久性原則。

innodbflushlogattrx_commit = 0 表示事務提交時不立即將事務日志寫入磁盤，而是每隔 1 秒寫入磁盤文件一次，并且刷新到磁盤，同時更新數據和索引。這樣一來，如果 mysqld 崩潰，那么在內存中事務日志緩沖區最近 1 秒的數據將會丟失，這些更新將永遠無法恢復。

innodbflushlogattrx_commit = 2 表示事務提交時立即寫入磁盤文件，但是不立即刷新到磁盤，而是每隔 1 秒刷新到磁盤一次，同時更新數據和索引。在這種情況下，即使 mysqld 崩潰后，位于內核緩沖區的事務日志仍然不會丟失，只有當操作系統崩潰的時候才會丟失最后 1 秒的數據。

顯然，將 innodbflushlogattrx_commit 設置為 0 可以獲得最佳性能，同時它的數據丟失可能性也最大。

Free: 空閑頁，是使用率（%Used）的對立方。

Data: 數據頁，列 %Dirty，展示已經被修改過，但還沒有被刷新到磁盤存儲的數據頁的比率。

Misc：用于管理分配行鎖和自適應哈希索引導致的開銷使用的頁。

Latched: 目前正在讀寫、或者因為其他原因無法被刷新的頁。

Pages Free 899.55k %Total: 85.80 Data 148.89k 14.20 %Drty: 0.34 Misc 6 0.00 Latched 0.00

Reads 從內存讀取的數量, 這個數值可以用來衡量 innodb 緩沖池的吞吐量,因為幾乎所有 inondb 需要的東西都是在緩沖池里，所以緩沖池的讀性能是越快越好。 哪怕超過每秒 200000 次也不是不可能的 。

From file: 從磁盤讀的數量，越小越好。

Ahead Rnd: 隨機讀，innodb 啟動的隨機讀取數。只有對表的大部分內容進行隨機掃描的時候才會出現。

Ahead Sql: 順序讀，只有全表掃描才會出現。

Writes：本行顯示寫的數量以及讀寫的比率。如果服務器主要操作是update和insert的話，這個值也會比較高。

Flushes: 緩沖池的頁刷新請求數。

Wait Free：一般情況下，innodb 緩沖池的寫操作是后臺運行的。不過，如果出現必須要讀寫一個頁可偏偏沒有可用的新頁時，（innodb）就只能先等待頁的刷新了。這個變量就是這些等待的總數。只要緩沖池的大小設置得當，等待數應該會很小。

Reads 191.27k 19.1k/s From file 0 0/s 0.00 Ahead Rnd 0 0/s Ahead Sql 0/s Writes 122.74k 12.3k/s Flushes 1.05k 105.5/s Wait Free 0 0/s

innodb 鎖報表

Waits：等待某行解鎖的累積次數，最好為0。

Current：當前正在等待解鎖的行個數，最好為0。

Time acquiring：顯示了毫秒(ms)級行鎖等待數據。分別是總值、平均值和最大值。同樣最好是 0 次。

__ InnoDB Lock _________________________________________________________ Waits 0 0/s Current 0 Time acquiring Total 0 ms Average 0 ms Max 0 ms

InnoDB 數據、頁、行報表

這部分報告，一般廣泛的用于衡量 innodb 引擎的吞吐量指標。

Reads/Writes: 指的是整個innodb引擎完成所有的數據讀/寫次數。

注意：

不是整個數據讀取字節數或者類型，而是 innodb 完成的數據讀/寫次數。

fsync： 刷新，innodb從內存寫入磁盤的次數。這個值應該會比前兩個小。

Pending: 等待，又被分成了三行(108-110)，分別是讀、寫、刷新的等待次數。

__ InnoDB Data, Pages, Rows ____________________________________________ Data Reads 0 0/s Writes 13.12k 1.3k/s fsync 125 12.5/s Pending Reads 0 Writes 0 fsync 0

包括三種自描述類型：創建、讀取、寫入，分別用來表示緩沖池中頁的創建、讀取 和寫入的數量和速率(即每秒操作數)。

Pages Created 753 75.3/s Read 0 0/s Written 1.05k 105.5/s Rows Deleted 0 0/s Inserted 16.10k 1.6k/s Read 0 0/s Updated 0 0/s

MySQL 運維 高性能計算

版權聲明：本文內容由網絡用戶投稿，版權歸原作者所有，本站不擁有其著作權，亦不承擔相應法律責任。如果您發現本站中有涉嫌抄襲或描述失實的內容，請聯系我們jiasou666@gmail.com 處理，核實后本網站將在24小時內刪除侵權內容。

版權聲明：本文內容由網絡用戶投稿，版權歸原作者所有，本站不擁有其著作權，亦不承擔相應法律責任。如果您發現本站中有涉嫌抄襲或描述失實的內容，請聯系我們jiasou666@gmail.com 處理，核實后本網站將在24小時內刪除侵權內容。