不一樣的SQL監控，使用perfomance schema填補slow log的空白

作者：VEPHP 時間 2017-09-18

《不一樣的SQL監控，使用perfomance schema填補slow log的空白》要點：
本文介紹了不一樣的SQL監控，使用perfomance schema填補slow log的空白，希望對您有用。如果有疑問，可以聯系我們。

作者介紹

劉世勇,曾就職于華為、網易,2015年初加入鏈家網,負責鏈家網數據庫的運維、數據庫架構設計、DB自動化運維平臺的構建等工作.

1.引言

在MySQL中,通過slow log,我們可以發現線上的慢查詢,并且結合監控和pt-toolkit,我們能夠比較快速地發現慢查詢,并且根據總響應時間、執行次數、平均響應時間等多個維度去分析和統計慢查詢.可是如果我們的DB是經過了大量的SQL和業務優化,在當前的配置下,slow log中幾乎已經沒有慢查詢了,可是整個庫的負載還是非常高,或者是某些項目剛上線,經常會因為緩存策略不合理導致大量的請求穿透到DB,可是這些請求又不是慢查,很難排查這種抖動的問題.這個時候怎么辦呢?我們能夠以什么為切入點更深一步地做一些調優呢?

一種方法就是將slow log的閾值盡可能的減小,或者打開general log,可是這樣對性能的影響是非常大的,因為會新增很多的IO操作.當然可以間歇性地調整,可是這樣運維成本和復雜度就高了,一兩個集群還勉強,集群多了之后,問題就會凸顯出來了.而且從另一方面來講,這種計劃性的調整很難應對線上一些偶發性的問題.

還有沒有別的辦法呢?此時我忍不住懷念起Oracle的AWR.使用過Oracle的同學都知道,Oracle是一個功能強大的性能分析工具,看懂AWR報告也是Oracle?DBA的基本功之一.在AWR里面,有個SQL stat的功能,實際上就是對某段時間內,整個DB執行過的SQL統計和分析報告.區別于MySQL slow log的是,這種統計報告是全量的,而slow log僅僅只是慢查,遇到一些高頻的、快速的查詢,slow log就沒有了參考價值.有前輩高人參考Oracle的AWR寫過一個myawr(https://github.com/noodba/myawr),但是這個工具也僅僅支持了slow log的統計結果.那有沒有辦法能夠像Oracle AWR一樣,能夠統計和分析全量的SQL執行情況呢?有,那就是perfomance schema.當然僅僅是perfomance schema還不夠,還需要將其中的SQL執行的統計數據拿出來作分析和展示.分析數據和展示數據都是借助myawr來做的.

本文內容主要分析如何開啟PS來獲得SQL執行的統計信息、怎么將全量的SQL統計信息錄入myawr、怎么分析和展示SQL執行的統計數據.

2.開啟PS

在使用perfomance schema之前,必須先開啟它.開始PS其實比較簡單,只要在配置文件中添加下面一行配置就可以：

performance_schema=ON

但是,僅僅打開PS還不夠,因為默認PS開啟的功能比較少.因為我們需要做全量的SQL統計,所以需要依賴PS中events_statements_summary_by_digest這個表的數據.這就需要在PS的setup_consumers中,將name和statements相關的值設置為YES：

update?performance_schema.setup_consumers

set ENABLED = ‘YES’

where NAME like ‘events_statements%’;

同時,還需要將setup_instruments表中,name和statements相關的值設置為YES：

update?performance_schema.setup_instruments

set?ENABLED = ‘YES’,?TIMED = ‘YES’

where?NAME like ‘statement%’;

3.PS數據接入myawr

打開之后,接下來就需要將SQL統計數據接入myawr中.由于myawr最初沒有對PS的支持,所以我們需要從頭開始改造myawr.

第一步是創建表,用于存儲統計數據.設計表的時候,為了和myawr原有的表的表結構保持一致,前三個字段還是分別為id、snap_id和host_id,snap_id是快照ID,和一個具體的snapshot對應,這個在生成最后的myawr報告時,非常有用,host_id是MySQL實例的ID.剩下的字段,都是從performance_schema.events_statements_summary_by_digest這個表中,根據實際的需求摘取出來的.

我們先看看這個表的結構：

SCHEMA_NAMESQL執行時的default database
DIGEST一個hash值,代表結構相同的一類SQL
DIGEST_TEXT通過正則過濾后的SQL文本,沒有具體參數值,代表結構相同的一類SQL
COUNT_STAR代表這一類SQL一共執行了多少次,這是一個累積值,只有實例重啟才會重置
SUM_TIMER_WAIT ~?SUM_NO_GOOD_INDEX_USED這一系列的字段,都是從不同維度對SQL進行的統計,可以根據自己關注的側重點,按需查詢
FIRST_SEENSQL第一次執行的時間
LAST_SEENSQL最近一次執行的時間,這個字段在收集SQL統計數據和最終生成myawr報告時都會用到

為了設計上的簡單,我把performance_schema.events_statements_summary_by_digest所有的字段都包含進去,命名為myawr_snapshot_events_statements_summary_by_digest,最終的myawr中的表結構如下：

myawr

創建好表之后,接下來就是將PS的數據錄入這個表.錄入時,需要做一些簡單的篩選,因為可能有一些SQL很長時間都沒有被執行過了,這樣的SQL的統計數據就不用重復的接入到myawr的數據庫中.這時候過濾就需要用上上面說的LAST_SEEN這個字段,我們目前的設計是只錄入最近24小時被執行過的SQL的統計信息,具體數據查詢的SQL為select * from performance_schema.events_statements_summary_by_digest where LAST_SEEN > date_sub(now(), interval 24 hour).確定數據采集方式之后,只需要在myawr的數據采集腳本中,將這部分功能邏輯添加進去即可.

一個需要注意的地方是數據量,因為SQL執行的全量統計信息是非常大的.一方面需要考慮表的設計,在建表時就將表按照時間分區,避免后面數據寫入成為瓶頸.另外,可以在部署數據采集任務時,適當地調整采樣周期,這直接決定了數據寫入的頻率.

4.數據展示

數據錄入到myawr的數據庫之后,接下來的工作就是分析和展示了.實際上就是從各個不同的維度去出分析報告,最終在報告里面展示的數據是一樣的,只是不同的分析維度的排序規則不一樣.下面,從總執行時間、總執行次數、總掃描記錄數、總返回記錄數、總排序記錄數5個維度去分析如何生成myawr報告.分析維度的選取,是根據日常運維的需求而定的,大家可以根據自己的實際需求,從myawr_snapshot_events_statements_summary_by_digest中選取其他的一些維度.

總執行時間

總執行時間是在整個DB性能分析時非常有用的信息,可以據此分析出當前整個DB的資源消耗的分布情況.總執行時間對應myawr_snapshot_events_statements_summary_by_digest這個表的SUM_TIMER_WAIT字段,生成報告的查詢SQL如下：

SQL

$tidhost_id,即對哪個MySQL實例生成myawr報告
$start_snap_id為myawr報告的起始快照ID
$start_snap_time為$start_snap_id對應的時間點
$end_snap_id為myawr報告的終止快照ID
$end_snap_time為$end_snap_id對應的時間點

最終在myawr報告中展示為：

myawr

總執行次數

總執行次數在分析某一固定時間段的故障時比較有參考價值.總執行次數對應myawr_snapshot_events_statements_summary_by_digest這個表的COUNT_STAR字段,生成報告的查詢SQL如下：