關聯與下鉆:快速定位MySQL性能瓶頸的制勝手段

為促進社區發展,運維派尋求戰略合作、贊助、投資,請聯系微信:helloywp

本文根據DBAplus社群〖2018年1月6日北京開源與架構技術沙龍〗現場演講內容整理而成。

講師介紹

李季鵬

新炬網絡數據庫專家

專注于MySQL數據庫性能管理及相關解決方案,目前主要從事MySQL性能分析工具的設計與研發工作。

目前我從事的是MySQL的技術研究并讓其實現產品化的工作,所以給大家今天分享的是MySQL性能分析的一些思路。

分享大綱:

1.MySQL性能管理需求與現狀

2.MySQL性能分析建設

MySQL性能管理需求與現狀

1、數據庫管理的現實需求

MySQL性能管理的現實需求很直觀,我們現在大部分的客戶都是在傳統企業,而傳統企業這幾年向MySQL轉型也是非常迅猛的。

所以用傳統的商用數據庫建設MySQL時就會發生一些很頭疼的問題,比如:如何讓少量的DBA管理大量的數據庫?如何通過簡單的方式改善和優化數據庫性能問題?如何持續保障數據庫的連續性和穩定性?…

數據庫

一個極大的風險點在于MySQL的性能數據難鋪展開回溯。MySQL性能數據的特點是比較依賴實時分析或執行分析,事后能得到的信息較少,也很難下鉆下去。當少量DBA管理大量數據庫時,出現問題只能通過高可用手段快速處理保障可用。但缺乏充足信息的回溯手段,往往只能在執行時間,鎖定時間等少數幾個維度分析問題SQL的影響程度,其覆蓋面難以包括大部分SQL語句的影響層面,這在很多運維環境上看來,缺乏事后分析手段,都是存在極大風險的。

在這個方面我們需要基于MySQL目前已有的分析手段去做改進,讓MySQL具備它原本不具備的性能信息增量記錄的能力,并在這個基礎上實現關聯與下鉆分析,便于快速定位到數據庫性能問題。

2.傳統MySQL性能管理手段的限制

從傳統上來說,MySQL性能分析的特點是實時執行、分析手段多樣,事后分析主要依賴Slow日志。

人工做問題分析,一般需要借助PT工具(Percona Toolkit)生成時段內慢語句報告,它能得到一些時段內的慢語句情況。但這并不能把PT工具生成報告跟原本在某一時段內的數據庫性能的整體狀態做一個關聯。

假設出現了一個性能問題引起的數據庫宕機故障,因為存儲在數據庫內的狀態信息P_S信息在重啟后都丟失了,那我們可以借助的手段只有日志分析和監控平臺的狀態指標分析。事實上這是一個比較頭痛的過程,因為這些分析難以關聯、精確定位到造成問題的原因。

數據庫

另一方面,如果要將這些多樣化的分析手段都產品化,那對我們來說一定是一個穩定性、可用性非常差的選擇。

因此,從簡單即運維這個觀點出發,站在產品化的角度面對不同的用戶時,要盡量減少現場為了適應產品接入而進行的變更,同時還要能夠提供最關鍵的關聯與下鉆分析功能。上述提到的這些點都是我們做設計的過程中一致考慮的問題。

3、優化MySQL性能管理的思考

誰都希望實現的工具是輕量化的,不需要做太多改造就可以接入。性能管理的需求分析起來可歸結為三點:

第一是輕量化采集,即接入數據庫并拿到數據庫性能信息。常規的分析手段都是用一種形式,但其實用非侵入手段會更好。什么叫非侵入手段?就是直接給工具提供一個賬號和相應的權限,然后工具就能通過接入數據庫采集響應的信息來避免Agent,這樣就能盡量避免自動化采集過程中對目標端的性能影響和風險。

MySQL

第二是增量化記錄:狀態增標、語句信息實現增量化記錄,使性能管理具備豐富的時段展示手段和歷史回溯手段。

第三是經驗沉淀:沉淀MySQL性能分析經驗的重點在于沉淀指標關聯,下鉆到問題語句分析過程。整個分析過程很簡單,重點在于提供給用戶在使用層面中哪些指標是需要關聯的、哪些指標是可以幫助我們進一步下鉆的,這樣就可以把分析的邏輯清晰化,也可以解決很多問題。

MySQL性能分析建設

下圖是在MySQL做性能分析里要去關聯下鉆的總體思路。從圖中可以看出,展示層就是性能入口。我們應該選取少量小而簡單的性能指標來作為它的問題點上浮。

MySQL

后面是通過性能上浮的指標來提供一系列對比的手段進行分解,同時還能做到指標間的對比、指標和語句的關聯。最后根據突出的性能指標下鉆到語句,并確認語句的執行形態是否造成了性能的風險。

能做到這層指標關聯與下鉆,主要在于數據庫中對語句執行性能信息的豐富程度遠遠大于日志記錄的信息,再將語句執行記錄的信息與數據庫相關的狀態信息進行關聯,這樣就更容易分析到語句執行對性能的影響了。

1、性能入口

(1)選取適當的上浮核心指標

如何選取上浮的核心指標,其實質是怎樣去評估一套數據庫的總體性能,這里主要包括兩個內容:一是數據庫負載如何、效率怎么樣,二則是這里面有性能問題的程度到底如何。

傳統上大部分DBA喜歡選擇QPS作為這樣的指標,在我們分析其意義,可以發現QPS并不能完全代表數據庫的性能狀態。QPS是純粹的每秒查詢數、執行數,能直觀看到每秒鐘有多少操作量,簡單計算便能知道哪個數據庫會更繁忙一些。

但如果把它放在衡量性能狀態的層面,每一個操作會有操作的響應時間,需要同時關注操作數與響應時間才能反映真實的性能壓力,而操作系統的對應負載就應該是操作數與操作時間的總數。

這里不難得到一個時段負載的公式,為“Load=QPS * avg_Latency(平均響應時間) * time(時間)”。但計算后會發現它的值與操作系統的Load對應不上,往往響應時間越大,系統壓力會越大,負載值就越高。

在這種情況下,我們可以確認它其實有很多慢響應的時間,但在我們每一分鐘采集一次的過程中,在采集點的前后有一些語句并沒有被執行完成,而等到執行完以后,這些語句才會被計算QPS。所以如果在你采集的過程中有大量的慢語句,那這部分沒有執行完成的語句就被遺漏了,若仍然使用上面的負載公式計算,那么慢語句越多,時段負載與OS統計的負載值就相差越大。

這時我們可以引入一個概念:AS/PS,它表示平均每秒執行語句的總負載。它引入了執行中語句時間增長統計,修正了單純計算短查詢負載的誤差,通過驗證,明確了通過在數據庫層面的AS/PS總繁忙度計算,能夠與主機層面統計的系統負載構成關聯。有了數據庫與系統的這一層的聯系,再進行主機資源與數據庫指標的關聯分析,就變得有意思起來了。

當系統負載升高時,我們能很清晰地分解出到底是哪個數據庫實例推高了負載,可以進一步得知是短查詢暴增還是出現慢語句而影響了負載,再進一步得知到底是被誰觸發了或者到底是哪個語句造成的,這些我們都能夠根據這個思路一一查出。引入這個指標,還可以在這個維度上解決數據庫上浮的問題。

另外一個思路是直觀地根據響應時間評分上浮來明確最應該關注哪個節點。MySQL官方推出的Enterprise Monitor工具提出的QRTi,其實就是面向這一思路。

它實際是用一種評分的標準,我們可以設置一個可接受和不可接受的慢查詢時限,其中默認設置為100-400毫秒之間,小于100毫秒查詢記為1分,100-400毫秒之間的查詢在可接受范圍之內的為0.5分,大于400毫秒不在接受的范圍之內為0分,根據執行次數計算平均值,再乘以百分比,得到了QRTi的結果值。那么顯而易見,百分比越高就說明這里面的響應層級越好,而百分比下降就說明這里面有更多慢響應。

通過這兩個維度,一是它對系統造成的負載壓力大小,二是它實際上有多少個慢響應,就可以很快評估出這個數據庫里哪些是值得你關注的。根據經驗所能得到的很多情況是,最值得關注的不是QPS最高的數據庫,反而是繁忙度升高、響應度變差的數據庫,而且一般繁忙度升高往往伴隨響應度降低,QPS也會隨之降低。

(2)指標特點對比

下圖列出了QPS、AS/PS、和QRTi的指標特點和優劣勢的對比。

一般而言,當選擇做整個性能評估或性能入口的點時,DBA會比較認可傳統段的QPS,并通過其來接觸數據庫或者評估它的操作總量。從劣勢來看,QPS不能直觀橫向比較數據庫性能問題。

而AS/PS的劣勢在于并不直接與數據庫狀態指標等同,而優勢是與數據庫的運行性能關聯度高,能作為關聯與下鉆過程的關鍵指標。而QRTi僅能衡量慢查詢程度,并不直觀反映性能情況,但根據它能直接下鉆問題SQL。

2.?關聯分析:提供多種場景的分析通路

有了入口指標后,它就可以為我們提供多種場景的分析通路,如指標分解、指標語句關聯、指標對比和時段執行回溯。

(1)指標分解

  • 分解獲取性能指標的構成詳情
  • 場景舉例:分解AS/PS,發現opening table占比較多

上圖是我對AS/PS做的分解,其中紅色部分是大量的短查詢語句,根據絕對值來說這是一個繁忙度能達到400%的相當繁忙的數據庫,很多短查詢語句都是在時段內完成的,但仍然有一些語句屬于慢等待。這就證明這里面其實有很多語句的響應時限在我們的采集范圍間隔之外了,也就是說,如果我現在采集的間隔是1分鐘一次,那很多語句的響應時限間隔是大于5秒、10秒甚至1分鐘的,那我們很自然地就會關注這一部分還有沒有執行耗時很高的情況。

(2)指標語句關聯

  • 提供性能指標關聯到語句的分析通路
  • 場景舉例:
  • 通過臨時表創建率高定位到創建臨時表的語句
  • 發現時段內bytes較高,關聯到rows_sent高的語句

上圖中展示的是Bytes_sents系統狀態指標,這里可以與采集到的語句Rows Snet相關聯,能夠直觀地關聯到具體哪些語句的返回量過高,推高了整個數據庫網絡發送。

(3)指標對比

  • 選取不同性能指標按維度進行對比
  • 對比方式:
  • 同庫不同指標間對比:對比Com_delete與Innodb_rows_deletedu
  • 異同庫相關指標對比:對比不同庫的innodb buffer pool命中率
  • 同庫不同時段指標對比:對于語句優化后臨時表創建改善情況

在分析過程中也會普遍存在指標對比的需求。

第一個場景發生在同一個數據庫內。假如我希望知道這套數據庫里有沒有一些大批量的刪除操作和以及這些操作的比例,那只需要拿Com_delete與Innodb_row_delete關聯在一起,確認一個刪除操作到底對應多少行的刪除。如果這個數非常大,那其實也會存在一些風險。

第二個場景發生在不同庫之間。比如你知道很多庫都有性能問題,但到底哪些庫的受性能指標影響更深呢?那就可以把對應的指標拿出來作對比。

第三個場景是時段的對比。我們會在很多時候發起一些專門優化的周期,而優化周期前后是性能對比,所以可能調整過的語句、前面語句和后面的語句不能一一對應,這時我們也可以做一些對比,很明確的是,此時這個數據庫的性能已經得到了優化。

需要指出,指標和語句的關聯其實是這里面整個下鉆過程中的核心,傳統的分析手段里只能分別得到指標、語句,沒有辦法把它們關聯起來。在傳統情況下,如果一個數據庫出了問題,我們可以根據慢日志看到其中很多查詢時間長的語句是什么、最早產生的語句是什么,認為這部分就是對它性能影響最大的語句,這其實是靠經驗推測的一個處理方法。

但如果做到狀態指標跟語句的關聯,我們通過問題指標的上浮,可以馬上下鉆到相關的風險語句,就可以節省分析時間,還可以通過造成的系統負載來判斷不同語句對系統造成的問題的影響度。

(4)時段執行回溯

  • 提供歷史時段內的指標和語句回溯
  • 場景舉例:回顧過去故障時段的語句執行情況

狀態指標差值計算比較簡單,比如按采集周期在30秒或1分鐘間取值再計算差值,這樣的采集方式很多工具都支持。比較麻煩的是,語句的差值計算可能會存在一些特別的情況,很多語句是周期性執行的,可能這一分鐘有查詢,下一分鐘沒有,再下一分鐘又有,這時你如果只是簡單地做兩個時間周期的差值是不合適的,需要特別處理采集間隔過大的情況。

還有一種情況就是數據庫做了一些重啟的操作,其中部分存儲的信息可能遺失了,你在第一次獲取時就會得到一個負的差值,這些值也需要特別處理。

我們設計時做了一個語句索引表,因為里面的ID是唯一的,即便是同一條語句在不同的庫里也無妨,只要記錄了它曾經出現、最近出現的兩次數據,這樣的設計能較快定位到語句,也比較省存儲空間。

3、 問題點定位:提供更豐富的語句信息

最后講講我們到底可以做多少針對語句的性能下鉆分析場景。

語句執行性能的采集來源是基于Performance_schema,下面的列表中體現了這一系列的語句執行信息包括的維度,這些維度都能關聯數據庫狀態指標進行分析。

但比較不好的一點在于它是以語句的模板形式進行存儲的,無論where條件變量是等于1、2、還是3,它仍然是一個模板,需要去專門獲取語句的樣例。有了語句的樣子,特別是特定時段的樣例,就能很好地針對性分析語句執行計劃等執行信息。

4、圍繞性能管理的其它拓展

這樣的性能管理其實還有很多擴展,尤其是我剛提到的SQL樣例。針對語句的樣例,我們可以對語句做一些語法上的分析,把對SQL規范語法審核引入,又能在上線環節很好地做好應用變更的管控,即審核應該調整那些不合規的應用新增語句,才允許上線。

數據庫性能

往往我們還會發現,很多造成數據庫性能問題的點不僅僅是由于慢語句本身造成的,而是比如由配置不合理等產生,也有可能涉及到表對象本身的容量、碎片過多或者是由于對象存在外鍵、無索引、甚至無主鍵等情況,這些因素的監控也可以一并考慮。

這種基于性能本身的管理可以圍繞起來做很多事情,希望能從性能管理的點最終擴展到數據庫管理、數據庫運維的整個面上來。

結語

問題分析的過程,包括本文所關注的性能分析過程,往往是一個通過工具,結合經驗的沉淀過程。分析問題的手段多種多樣,而沉淀的經驗是可以復制,可以被固化的。在這里,分享我們沉淀在產品中的分析經驗與方法論,同樣是希望與大家探討一個把MySQL中的原理與使用實踐相結合的思路。

將問題分析的過程自動化甚至智能化,在數據庫運維中這是極具挑戰的一環。而在數據庫運維經驗沉淀的產品化道路上,我們一直在不斷探索。我們在努力鍛造的正是這樣一款支持主流Oracle、MySQL、SQL Server和DB2各版本數據庫,具備數據庫的專業監控、運維、性能管理,SQL審核等場景的專業數據庫運維管理平臺。

最后,容許我打個小廣告,簡單展示一下:

原文來自微信公眾號:DBAplus社群

網友評論comments

發表評論

電子郵件地址不會被公開。 必填項已用*標注

  1. 說道:

    數據庫的性能檢查,有工具能檢查,為什么還要手動檢查是不是有點多余了

Copyright ? 2012-2019 YUNWEIPAI.COM - 運維派 - 粵ICP備14090526號-3
掃二維碼
掃二維碼
返回頂部
街机电玩捕鱼抢红包 白小姐一肖特马开 青海快3图片欣赏 安徽快3遗漏数据二牛 极速飞艇开奖公正吗 重时时彩五星综合走势 燕赵福彩排列5开奖结果 北京时时正规吗 快三下期可能出什么 极速时时彩计划稳赢版免费 中盛重新时时