基于clickhouse分析和優化mysql的業務運行

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

2017年一年里,脈脈發展出了一套mysql統計方法。借用此方法,業務方對數據庫的使用效率得到長足提高,同時也解決了一些之前困擾很久的問題。

在此基礎上,我們又在基礎架構方面和數據解析方面做了進一步的優化,并且依靠更豐富和實時的數據做了更多更深入的實踐,本文將介紹最新的基礎架構以及部分實踐經驗。

一. 觀察業務的問題

這部分列舉一些日常工作中dba可能會好奇或者真正會遇到但又處理起來比較麻煩的問題

1. 先看一個監控圖

可以看到,增刪改查的數量以及變化的趨勢一目了然,根據對業務的基本了解,也大體可以推斷出qps的起伏是由于用戶在不同時間段的活躍程度不一樣導致的。

但是如果再進一步思考這些問題:這一望無際的綠色(select)具體都是些什么sql?他們的數量都是多少?貢獻qps最多的sql是什么?他們的數量隨著時間變化的趨勢又是什么樣子?一張簡單的監控圖所能展現的信息恐怕就不夠了,而如果再繼續追問:在11點前后qps有較大波動,是為什么?是某個sql的qps突然飆高,還是業務整體性抽風?如果是單條sql突然飆高,他來自哪臺服務器,之前的運行規律是什么?上面的這些問題或許通過咨詢研發可能可以得到一些線索,但是想掌握詳細的情況可能不太容易。

2. 表和字段

在日常的工作中,哪些表查詢量大,哪些表查詢量小,我們多少會心里有數,但是哪些表已經沒有查詢了,恐怕不是一件很好確定的事情。

由于公司的快速發展和在業務上的不斷試錯,導致數據庫里有很多“可能”沒用了的表,之所以說“可能”,是因為真的沒有誰說得清楚這個表到底有沒有用。即使不考慮相關研發離職這種極端情況,也可能因為時間太久或者業務的快速迭代導致相關研發也不敢保證這個表到底還有沒有用了,如果這是一張占磁盤空間比較大的表,讓他這樣不明不白的留在磁盤上顯然不是一件讓人愉快的事情。還有更糟糕的——對于一張明顯比較大的表,dba終究會想起來關注一下;而對于更多的單表不那么大但是加在一起所占空間也不容小視的表來講,他們則是徹底待在被遺忘的角落了。對于前者,我們或許可以有限的通過查詢全日志或者審計日志來判斷一下是否還有讀寫,而對于后者,要想一一搞清楚成本就比較高了。

更進一步,由于類似的原因,一張表(尤其是寬表)里的字段也會有同樣的問題——哪些字段訪問量大,哪些字段訪問量小,哪些字段在業務上關聯度更大,哪些字段已經不用了。如果能得到這些信息,將對業務上的優化有很大的幫助。

3. 用戶權限

這個相對簡單些,如果在做權限控制的時候是以表為單位授權的話,一切都是一目了然的。而如果是對庫授權甚至圖省事對*.*做授權的話,事情就要麻煩多了。再設想一個場景(我相信很多dba都遇到過),如果研發新加了一臺服務器,然后對dba說“我新加了一個服務器,幫我按照其他服務器的權限授權”。如果庫表的業務所屬劃分的比較好,這自然不是一個問題,但是如果不是這樣,可能會讓dba和研發都比較頭疼——授權工作會在dba的反復操作和研發的反復報錯之間曲折前行。

二. 數據的收集、解析和存儲

在上一篇文章里,這一方案最終確定為:原始數據(審計日志) -> flume interceptor解析日志及sqltext ->hdfs,通過hive進行查詢。后來在此基礎上又添加了從hdfs轉存到clickhouse的環節,最終通過clickhouse查詢。

在引入了clickhouse后,查詢的效率大幅度提升,絕大多數統計需求可以做到在10秒以內得到結果——相比起之前通過spark-sql的查詢,速度可以說有了數量級的提升。然而這一方案仍然有很大的改進空間,主要有以下幾點:

1.流程繁瑣,既然數據最終會存到clickhouse,那么現有的先存到hdfs然后轉存至clickhouse的模式就顯得沒有必要,如果可以直接存到clickhouse顯然是最好的;

2.解析sql的功能仍然不夠強大和細致——原先的功能只能把一個sql中涉及到的表名,條件字段,查詢(更新)字段解析出來,但是不會做更多的解析。參考下面三個sql:

????????? select id, name, qqfrom users where id = 1 and status = 1;

????????? select id, name, qqfrom users where id in ( 1000個id ) and status = 1;

????????? select id, name, qqfrom users where id = 1 or status = 1;

在原先的方案里,這三個sql都會被解析成 {tables: ‘users’, items: ‘id,name,qq’, columns: ‘id,status’}。顯而易見的,這三個sql是完全不一樣的,其背后的業務含義也完全不一樣(不要深究sql的合理性,只是例子),但是在統計結果里,則會被展示為相同的sql樣式,這在當時對業務只需要粗粒度分析的背景下是可以接受的,而如果想更加細致的觀察和分析就無能為力了。

3. 對業務的觀察僅限于sql層面,原始數據是mysql的審計日志,因為設計日志解析器時考慮不夠全面,其中包括的信息有很多沒有被解析和存儲,包括執行狀態(成功失敗),操作類型(登錄登出)等。

經過調研,決定放棄原有的flume-> hdfs -> clickhouse的方案,而更新為使用clicktail(詳情請見https://github.com/Altinity/clicktail)直接寫入clickhouse,這樣的好處顯而易見:clicktail比flume輕量化很多,易于配置和部署;直接寫入clickhouse,提升了數據更新的實時性;clicktail自帶對審計日志的解析功能,只需要再人為添加sql解析功能即可,開發工作量小。在原有的sql解析功能上,添加的對sql的歸一化處理并計算checksum,保證了對sql樣式的精細區分,并存儲了sql原文的前300個字符,這可以觀察sql詳情,雖然對于有些比較長的sql(比如查詢條件in了上千個id)不能完全展示,不過也足夠看明白細節了,當然它最重要的功能并不僅僅是要讓dba看到sql細節,在統計工作中sql的詳情頁是非常重要的。

作為補充,又把慢日志以同樣的解析策略上傳至clickhouse,唯一的區別是慢日志是完全保留sql原文而非前300個字符,因為慢日志的量要少的多。

在這一切確定并部署上傳后,存儲到clickhouse里的數據大約是這個樣子:

我們可以看到用戶信息,時間和sql信息

關于sql,我們可以看到sql的原文、被歸一化處理過的sql以及對應的checksum、也可以看到sql的關鍵信息(sql類型,庫表字段信息)。有了這些,一切都是透明的了。

三. 觀察業務的基本方法

這部分展示如何組織和使用統計數據

1. 狀態統計

狀態信息是最基礎也是最常要查看的信息,有助于我們定量的觀察和分析業務在mysql上運行的情況

對于業務狀態的統計,最直觀和最全面的就是面向實例的統計 —— 某個實例在某段時間內的查詢情況 —— 包括sql的樣式,樣例,訪問什么表,(以查詢為例)訪問了哪些字段,條件是什么,總量是多少,來自哪個用戶@ip等內容(如下圖)。

這樣一來,一個時間段里mysql的查詢情況就被完全展現出來了,我們可以看到所有查詢的sql,以及他們元信息。這可以說起到了“開全圖”的效果,在此基礎上,我們可以進行更細粒度的統計。可以把面向實例細化為面向表,可以看到某個表的訪問情況,再進一步細化統計,可以限定它的條件字段,查詢字段,用戶等內容,增刪改查的比例,甚至可以看到某sql的所有訪問原文。

依賴上面列出的各個粗細粒度不一的狀態統計信息,可以進一步的干很多事情

2. 分析業務變化情況

對兩個時間段或者兩個時間序列的狀態統計信息進行比較,即可獲取業務變化信息。上文中提到的“qps”出現較大波動的情況,就可以通過對比這兩個qps高位和低位兩個時間段的狀態進行觀察,并通過比較結果得出結論。而如果發現了比較明顯的周期性變化,則可以把兩個時間段擴展成兩個時間段的序列,這樣比較的效果會更明顯。

3. 變化趨勢

很多個連續的時間段上的狀態的累加就成了趨勢,一般趨勢都是針對訪問量而言的。對于某個sql,比如上圖中checkusm = ‘3789f41…6a9d’的sql,如果我們關注他在一天里每分鐘的qps都是多少,就可以把每分鐘的qps值進行累加,從而形成一個趨勢數據進行觀察。同樣的,除了指定sql,還可以指定某個用戶或者某個表觀察他們的訪問趨勢數據——如果觀察對象是整個實例,則qps的趨勢數據就和監控數據一樣,不過通過統計可以讓數據的精度達到秒,而監控一般以分鐘計。

4. 訪問變量的分布

如果我們把視角轉移到sql本身,對于大多數sql(長度不超過300字符)我們可以根據業務的具體情況,把最重要的變量提取出來,并觀察這些變量的訪問分布情況以及關鍵的分位數據。再進一步可以把這個分布的情況或者某個變量的訪問量和時間趨勢結合起來,來觀察業務訪問的變化情況。

5. 用戶行為與權限觀察

從用戶的角度觀察,可以看到某個用戶在某段時間都做了哪些查詢,訪問了哪些表,來自于哪些ip,訪問量是多少。同樣的,對于某個表,也可以統計有哪些用戶訪問,訪問量是多少。

四. 實踐

1. sql上下線觀察

眾所周知的是,由于溝通機制的問題,研發如果要新上線sql,很難做到上線前或者上線的第一時間就通知dba,久而久之則會出現很多新sql,而這些sql的訪問量也通常會隨著業務量的增加而增加。通過上文提到的【分析業務變化情況】的方法,如果把兩個時間段設置為以天或者周為單位,則可以比較清楚的觀察到新上線的sql,如果發現問題,則可以及時處理。

2. 熱數據/數據傾斜分析

對于某些高qps的sql,我們可以通過提取出sql中的關鍵變量,并統計其分布情況來分析sql的訪問熱度——這樣做最直接的好處是可以判斷這個sql是否適合通過緩存來減少qps并提高效率,除此以外,還可以給產品和數據分析組的工作提供一定的支持。對于慢日志,同樣可以照上面的辦法進行分析,不同的是,通過統計結果可以判斷數據表是否存在數據傾斜,導致涉及到某個變量時sql性能不佳。

3. sql趨勢數據的相關性分析

a. 總趨勢相關性分析:對于一個實例,我們可以通過監控數據知道它的總體qps的變化趨勢,而哪些sql的趨勢和總體趨勢相關性最大,我們可以通過計算所有sql的qps趨勢數據和整體qps趨勢數據的相關系數來獲得。除了可以用sql的趨勢數據,也可以用更粗或者更細粒度(比如以用戶或者表為觀察維度)的趨勢數據做分析,全面觀察數據庫的使用情況。

b.? sql相關度分析:通常來講,sql不會是孤立的。一個sql的執行,在其前后也會有其他sql執行,換句話說,sql是“一群一群”的。所以我們可以找到這些sql群,并由此分析業務的相關特性。或者在發現一個業務上不合理的sql的時候,可以一并觀察和他同一群的其他sql,確保問題的全面解決。

c. 與nginx日志的協同分析:類似的,我們還可以把sql和nginx的接口訪問量進行相關性分析,這在挖掘古老的、負責人不明確的sql來源時非常有用——找到sql對應的接口,也就自然能找到對應的負責人(部門)

4. 權限回收,授權套餐化

對于權限過大的賬號,回收權限是一件風險很大的事情,畢竟要確保某個賬號真的沒有再訪問某個表或者沒有再從某個ip訪問數據庫是比較難的,尤其是對于大周期的定時任務,觀察總是很難全面。而通過存儲的審計日志則可以觀察很長時間段(以年計)的用戶行為,確認用戶要訪問的庫表,從而回收無關表的權限。

既然可以知道所有用戶要訪問的所有表,以及對應的應用服務器ip,那么我們就可以把每一個業務所屬的賬號權限記錄下來,成為一個“套餐”,所有的權限變更操作以套餐為單位。一個典型的套餐由三部分組成,即用戶名、ip列表和庫表列表,當ip列表或者庫表列表發生變化時,只需要對應的增加/回收套餐里的信息并在相應的數據庫里做修改便可以了。這在業務擴容或者新庫表上線時會帶來很大的方便。

5. 表或字段下線判斷

隨著公司的發展和開發人員的流動,很難避免的帶來一些神秘的表或者神秘的字段——沒人確定他們是干什么用的,還有沒有在讀寫,能不能下線。對于這種問題,可以通過觀察一個表在之前一段時間的訪問情況來解決,如果既沒有讀也沒有寫,則很顯然這個表沒有用了。而對于只有讀沒有寫或者只有寫沒有讀的表,則可以以此為線索推動研發確認,做到有的放矢。

對于字段也是同理,不過因為可能會存在“select *”這種寫法,所以我個人比較傾向于只觀察是否有寫,然后找研發做確認

五. 結語和展望未來

借助clickhouse長期存儲解析后的審計日志,可以讓我們保留全面的,長時間段的數據,為統計分析提供足量的數據。而通過統計分析,業務方對數據庫的使用已經沒有秘密——我們可以深入到業務的所有細枝末節,業務方對mysql的使用從此對dba是透明的。結合性能監控,dba可以全面的掌握mysql的使用情況,不錯過任何一個變化。甚至可以更清晰的了解業務狀況,反向的配合和推動研發、產品部門進行業務邏輯層面的優化。再來回過頭來看文章開始時提出的問題,是不是全部迎刃而解了?

接下來考慮要做的,是在現有數據的基礎上,結合已有的實踐經驗,進一步摸索業務運行的邏輯,并對各個業務進行運行規律進行分析,從多個維度和顆粒度建立各個業務運行的模型。做到對業務運行情況的體系化觀察。

作者介紹:
紀維驍,在公司里被同事稱作基維蝦,本科就讀于北京航空航天大學,在香港科技大學獲取碩士學位。曾在人民搜索、百度、京東工作,2016年9月入職脈脈。作為唯一的dba,在公司業務規模迅速發展(日活量增長20倍)期間保證了mysql的穩定和高效運行,在dba日常工作以外會深入了解研發的業務代碼并在業務層面提出優化建議。2017年兼任公司的大數據與統計工程師,自學spark與scala,統計公司各類業務指標(日活,月活,留存等)以及產品指標(拉新效果,活動效果,產品性能與可靠性),在此期間產生了利用spark分析mysql日志的想法并加以實踐。

網友評論comments

發表評論

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

暫無評論

Copyright ? 2012-2019 YUNWEIPAI.COM - 運維派 - 粵ICP備14090526號-3
掃二維碼
掃二維碼
返回頂部
街机电玩捕鱼抢红包 e球彩两场全包中奖率 真人龙虎斗平台 去哪儿司机能赚钱吗 22选5今天晚上开奖结果 大连娱网棋牌游戏大厅 江西多乐彩入口 足球比分网 万赢彩票安卓 安徽快三开奖 福建22选5开奖走势图