當(dāng)前位置：維易PHP培訓(xùn)學(xué)院 > 服務(wù)器運(yùn)維 > 內(nèi)容正文

不一樣的SQL監(jiān)控，使用perfomance schema填補(bǔ)slow log的空白

作者：VEPHP 時(shí)間 2017-09-18

《不一樣的SQL監(jiān)控，使用perfomance schema填補(bǔ)slow log的空白》要點(diǎn)：
本文介紹了不一樣的SQL監(jiān)控，使用perfomance schema填補(bǔ)slow log的空白，希望對(duì)您有用。如果有疑問，可以聯(lián)系我們。

作者介紹

劉世勇,曾就職于華為、網(wǎng)易,2015年初加入鏈家網(wǎng),負(fù)責(zé)鏈家網(wǎng)數(shù)據(jù)庫的運(yùn)維、數(shù)據(jù)庫架構(gòu)設(shè)計(jì)、DB自動(dòng)化運(yùn)維平臺(tái)的構(gòu)建等工作.

1.引言

在MySQL中,通過slow log,我們可以發(fā)現(xiàn)線上的慢查詢,并且結(jié)合監(jiān)控和pt-toolkit,我們能夠比較快速地發(fā)現(xiàn)慢查詢,并且根據(jù)總響應(yīng)時(shí)間、執(zhí)行次數(shù)、平均響應(yīng)時(shí)間等多個(gè)維度去分析和統(tǒng)計(jì)慢查詢.可是如果我們的DB是經(jīng)過了大量的SQL和業(yè)務(wù)優(yōu)化,在當(dāng)前的配置下,slow log中幾乎已經(jīng)沒有慢查詢了,可是整個(gè)庫的負(fù)載還是非常高,或者是某些項(xiàng)目剛上線,經(jīng)常會(huì)因?yàn)榫彺娌呗圆缓侠韺?dǎo)致大量的請(qǐng)求穿透到DB,可是這些請(qǐng)求又不是慢查,很難排查這種抖動(dòng)的問題.這個(gè)時(shí)候怎么辦呢?我們能夠以什么為切入點(diǎn)更深一步地做一些調(diào)優(yōu)呢?

一種方法就是將slow log的閾值盡可能的減小,或者打開general log,可是這樣對(duì)性能的影響是非常大的,因?yàn)闀?huì)新增很多的IO操作.當(dāng)然可以間歇性地調(diào)整,可是這樣運(yùn)維成本和復(fù)雜度就高了,一兩個(gè)集群還勉強(qiáng),集群多了之后,問題就會(huì)凸顯出來了.而且從另一方面來講,這種計(jì)劃性的調(diào)整很難應(yīng)對(duì)線上一些偶發(fā)性的問題.

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

本文內(nèi)容主要分析如何開啟PS來獲得SQL執(zhí)行的統(tǒng)計(jì)信息、怎么將全量的SQL統(tǒng)計(jì)信息錄入myawr、怎么分析和展示SQL執(zhí)行的統(tǒng)計(jì)數(shù)據(jù).

2.開啟PS

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

performance_schema=ON

但是,僅僅打開PS還不夠,因?yàn)槟J(rèn)PS開啟的功能比較少.因?yàn)槲覀冃枰鋈康腟QL統(tǒng)計(jì),所以需要依賴PS中events_statements_summary_by_digest這個(gè)表的數(shù)據(jù).這就需要在PS的setup_consumers中,將name和statements相關(guān)的值設(shè)置為YES：

update?performance_schema.setup_consumers

set ENABLED = ‘YES’

where NAME like ‘events_statements%’;

同時(shí),還需要將setup_instruments表中,name和statements相關(guān)的值設(shè)置為YES：

update?performance_schema.setup_instruments

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

where?NAME like ‘statement%’;

3.PS數(shù)據(jù)接入myawr

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

第一步是創(chuàng)建表,用于存儲(chǔ)統(tǒng)計(jì)數(shù)據(jù).設(shè)計(jì)表的時(shí)候,為了和myawr原有的表的表結(jié)構(gòu)保持一致,前三個(gè)字段還是分別為id、snap_id和host_id,snap_id是快照ID,和一個(gè)具體的snapshot對(duì)應(yīng),這個(gè)在生成最后的myawr報(bào)告時(shí),非常有用,host_id是MySQL實(shí)例的ID.剩下的字段,都是從performance_schema.events_statements_summary_by_digest這個(gè)表中,根據(jù)實(shí)際的需求摘取出來的.

我們先看看這個(gè)表的結(jié)構(gòu)：

數(shù)據(jù)

SCHEMA_NAMESQL執(zhí)行時(shí)的default database
DIGEST一個(gè)hash值,代表結(jié)構(gòu)相同的一類SQL
DIGEST_TEXT通過正則過濾后的SQL文本,沒有具體參數(shù)值,代表結(jié)構(gòu)相同的一類SQL
COUNT_STAR代表這一類SQL一共執(zhí)行了多少次,這是一個(gè)累積值,只有實(shí)例重啟才會(huì)重置
SUM_TIMER_WAIT ~?SUM_NO_GOOD_INDEX_USED這一系列的字段,都是從不同維度對(duì)SQL進(jìn)行的統(tǒng)計(jì),可以根據(jù)自己關(guān)注的側(cè)重點(diǎn),按需查詢
FIRST_SEENSQL第一次執(zhí)行的時(shí)間
LAST_SEENSQL最近一次執(zhí)行的時(shí)間,這個(gè)字段在收集SQL統(tǒng)計(jì)數(shù)據(jù)和最終生成myawr報(bào)告時(shí)都會(huì)用到

為了設(shè)計(jì)上的簡(jiǎn)單,我把performance_schema.events_statements_summary_by_digest所有的字段都包含進(jìn)去,命名為myawr_snapshot_events_statements_summary_by_digest,最終的myawr中的表結(jié)構(gòu)如下：

myawr

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

一個(gè)需要注意的地方是數(shù)據(jù)量,因?yàn)镾QL執(zhí)行的全量統(tǒng)計(jì)信息是非常大的.一方面需要考慮表的設(shè)計(jì),在建表時(shí)就將表按照時(shí)間分區(qū),避免后面數(shù)據(jù)寫入成為瓶頸.另外,可以在部署數(shù)據(jù)采集任務(wù)時(shí),適當(dāng)?shù)卣{(diào)整采樣周期,這直接決定了數(shù)據(jù)寫入的頻率.

4.數(shù)據(jù)展示

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

總執(zhí)行時(shí)間

總執(zhí)行時(shí)間是在整個(gè)DB性能分析時(shí)非常有用的信息,可以據(jù)此分析出當(dāng)前整個(gè)DB的資源消耗的分布情況.總執(zhí)行時(shí)間對(duì)應(yīng)myawr_snapshot_events_statements_summary_by_digest這個(gè)表的SUM_TIMER_WAIT字段,生成報(bào)告的查詢SQL如下：

SQL

$tidhost_id,即對(duì)哪個(gè)MySQL實(shí)例生成myawr報(bào)告
$start_snap_id為myawr報(bào)告的起始快照ID
$start_snap_time為$start_snap_id對(duì)應(yīng)的時(shí)間點(diǎn)
$end_snap_id為myawr報(bào)告的終止快照ID
$end_snap_time為$end_snap_id對(duì)應(yīng)的時(shí)間點(diǎn)

最終在myawr報(bào)告中展示為：

myawr

總執(zhí)行次數(shù)

總執(zhí)行次數(shù)在分析某一固定時(shí)間段的故障時(shí)比較有參考價(jià)值.總執(zhí)行次數(shù)對(duì)應(yīng)myawr_snapshot_events_statements_summary_by_digest這個(gè)表的COUNT_STAR字段,生成報(bào)告的查詢SQL如下：