MySQL記錄檔與分析

標籤：查看   簡單   掃描   查詢   app   眼睛   執行器   sorted   沒有   

1.查詢日誌、慢查詢日誌、二進位日誌對比

查詢日誌	general_log	會記錄使用者的所有操作，其中包含增刪查改等	可以指定輸出為表
慢查詢日誌	slow_log	只要超過定義時間的所有動作陳述式都記錄	可以指定輸出為表
二進位日誌	log_bin	記錄可能執行更改的所有操作	mysqlbinlog查看

 

 

  2.日誌的分析2.1日誌的儲存

資料操作過程中，Mysqld是將接收到的語句按照接收的順序(注意不是執行順序)寫到查詢記錄檔中。一條一條就類似這樣：

    # Time: 070927 8:08:52
    # [email protected]: root[root] @ [192.168.0.20]
    # Query_time: 372 Lock_time: 136 Rows_sent: 152 Rows_examined: 263630 
    select id, name from manager where id in (66,10135); 

 這樣的話，當我們去查看日誌內容時就會灰常費時費神費眼睛。那麼應該怎麼破？

自5.1.6版本起，就有一波新功能出爐，比如查詢日誌可以寫到資料庫系統中的專用表。

MySQL的命令列在啟動時可以載入很多參數，其中就提供了一個日誌專用的參數--log-output，用來指定記錄檔的輸出方式.

--log-output參數可選值有三個：

• TABLE：記錄到資料庫中的日誌表；
• FILE：記錄到記錄檔，預設值即為FILE (在5.1.6到5.1.20版本時，預設值為TABLE)；
• NONE：不記錄。

（1）可以是TABLE、FILE、NONE，也可以是TABLE及FILE的組合(用逗號隔開)，預設為TABLE。

（2）如果組合中出現了NONE，那麼其它設定都將失效，同時，無論是否啟用日誌功能，也不會記錄任何相關的日誌資訊。

（3）作用範圍為全域層級，可用於設定檔，屬動態變數。

（4）啟用mysqld進程時附加--log-output指定日誌輸出類型。

MySQL支援將慢查詢日誌儲存到mysql.slow_log這張表中:

2.2慢查詢分析工具

如果慢查詢日誌輸出類型已經指定了FILE，在日誌量大的情況下，我們可以藉助一些分析工具。

（1） mysqldumpslow         mysql 內建慢查詢日誌分析工具 mysqldumpslow，使用很簡單，可以跟-help來查看具體的用法。        主要功能：統計不同慢sql的出現次數(Count) 執行最長時間(Time), 累計總耗費時間(Time), 等待鎖的時間(Lock), 發送給用戶端的行總數(Rows), 掃描的行總數(Rows), 使用者以及sql語句本身(抽象了一下格式, 比如 limit 1, 20 用 limit N,N 表示).

（2）mysqlsla

       hackmysql.com推出的一款日誌分析工具

功能非常強大. 資料報表,非常有利於分析慢查詢的原因, 包括執行頻率, 資料量, 查詢消耗等.

 格式說明如下：

總查詢次數 (queries total), 去重後的sql數量 (unique)輸出報表的內容排序(sorted by)最重大的慢sql統計資訊, 包括平均執行時間, 等待鎖時間, 結果行的總數, 掃描的行總數。

Count：                    sql的執行次數及佔總的slow log數量的百分比。

Time：                     執行時間, 包括總時間, 平均時間, 最小, 最大時間, 時間佔到總慢sql時間的百分比。

95% of Time：       去除最快和最慢的sql, 覆蓋率佔95%的sql的執行時間。

Lock Time：           等待鎖的時間。

95% of Lock：        95%的慢sql等待鎖時間。

Rows sent：            結果行統計數量, 包括平均, 最小, 最大數量。

Rows examined：  掃描的行數量。

Database：            屬於哪個資料庫。

Users：                   哪個使用者IP佔到所有使用者執行的sql百分比。

Query abstract：    抽象後的sql語句。

Query sample：     sql語句。

 除了以上的輸出, 官方還提供了很多定製化參數, 是一款不可多得的好工具。

(3) pt-upgrade

這個工具用來檢查在新版本中啟動並執行SQL是否與老版本一樣，返回相同的結果，最好的應用情境就是資料移轉的時候。

pt-upgrade h=host1 h=host2 slow.log

(4) pt-query-digest

可以從普通MySQL日誌，慢查詢日誌以及二進位日誌中分析查詢，甚至可以從SHOW PROCESSLIST和MySQL協議的tcpdump中進行分析，如果沒有指定檔案，它從標準輸入資料流（STDIN）中讀取資料。

最簡單的用法如下：

pt-query-digest slow.logs

整個輸出分為三大部分：

1、整體概要（Overall）

這個部分是一個大致的概要資訊(類似loadrunner給出的概要資訊)，通過它可以對當前MySQL的查詢效能做一個初步的評估，比如各個指標的最大 值(max)，平均值(min)，95%分布值，中位元(median)，標準差(stddev)。這些指標有查詢的執行時間（Exec time），鎖佔用的時間（Lock time），MySQL執行器需要檢查的行數（Rows examine），最後返回給用戶端的行數（Rows sent），查詢的大小。

2、查詢的匯總資訊（Profile）

這個部分對所有”重要”的查詢(通常是比較慢的查詢)做了個一覽表:

每個查詢都有一個Query ID，這個ID通過Hash計算出來的。pt-query-digest是根據這個所謂的Fingerprint來group by的。舉例下面兩個查詢的Fingerprint是一樣的都是select * from table1 where column1 = ?，工具箱中也有一個與之相關的工具pt-fingerprint。

select * from table1 where column1 = 2select * from table1 where column1 = 3

• Rank整個分析中該“語句”的排名，一般也就是效能最常的。
• Response time  “語句”的回應時間以及整體佔比情況。
• Calls 該“語句”的執行次數。
• R/Call 每次執行的平均回應時間。
• V/M 回應時間的差異平均對比率。

在尾部有一行輸出，顯示了其他2個佔比較低而不值得單獨顯示的查詢的統計資料。

3、詳細資料

這個部分會列出Profile表中每個查詢的詳細資料：

包括Overall中有的資訊、查詢回應時間的分布情況以及該查詢”入榜”的理由。

 

2.3BOX Anemometer

BOX Anemometer是 基於pt-query-digest將MySQL慢查詢可視化

 

MySQL記錄檔與分析