不一樣的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報告也是OracleDBA的基本功之一。在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_NAME

SQL執行時的default database

DIGEST

一個hash值，代表結構相同的一類SQL

DIGEST_TEXT

通過正則過濾後的SQL文本，沒有具體參數值，代表結構相同的一類SQL

COUNT_STAR

代表這一類SQL一共執行了多少次，這是一個累積值，只有實例重啟才會重置

SUM_TIMER_WAIT ~ SUM_NO_GOOD_INDEX_USED

這一系列的欄位，都是從不同維度對SQL進行的統計，可以根據自己關注的側重點，按需查詢

FIRST_SEEN

SQL第一次執行的時間

LAST_SEEN

SQL最近一次執行的時間，這個欄位在收集SQL統計數據和最終生成myawr報告時都會用到

為了設計上的簡單，我把performance_schema.events_statements_summary_by_digest所有的欄位都包含進去，命名為myawr_snapshot_events_statements_summary_by_digest，最終的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如下：

$tid

host_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_snapshot_events_statements_summary_by_digest這個表的COUNT_STAR欄位，生成報告的查詢SQL如下：

最終在myawr報告中展示為：

總掃描記錄數

總掃描記錄數，以及後面的總返回記錄數、總排序次數，都是在分析某一固定時間段的某一類問題故障時比較有參考價值。總掃描記錄數對應myawr_snapshot_events_statements_summary_by_digest這個表的SUM_ROWS_EXAMINED欄位，生成報告的查詢SQL如下：

最終在myawr報告中展示為：

總返回記錄數

總返回記錄數，實際上是指返回給客戶端的記錄數，也就是最終結果集的大小。如果你發現網卡流量突增，可以從這個角度去分析一下。總返回記錄數對應myawr_snapshot_events_statements_summary_by_digest這個表的SUM_ROWS_SENT欄位，生成報告的查詢SQL如下：

最終在myawr報告中展示為：

總排序記錄數

總排序記錄數，對應myawr_snapshot_events_statements_summary_by_digest這個表的SUM_ROWS_SENT欄位，生成報告的查詢SQL如下：

最終在myawr報告中展示為：

5

結語

通過將performance schema中的SQL執行統計數據，錄入到myawr，擴展了myawr的功能，實現了全量SQL執行情況統計分析，填補了slow log功能上的空白，為解決和分析線上問題提供了更多的參考依據。

除了SQL統計信息，其實在perfomance schema中還有很多有用的信息，比如類似Oracle AWR的等待事件、文件IO統計、連接統計等，這些能為DBA的日常故障排查、性能調優提供非常多的幫助，所以perfomance schema是非常值得嘗試的一個特性。這些信息我們實際上也已經添加到myawr中，成為日常運維工作的一個很重要的工具。當然有得必有失，開啟perfomance schema會對性能有一些影響，也會消耗額外的內存。不過，只要前期經過嚴謹的測試，這些影響都是可以控制的。

喜歡這篇文章嗎？立刻分享出去讓更多人知道吧！