欧美97色伦欧美一区二区日韩,国产福利片在线观看,freexxx性欧美vide0高清,西西亚洲,日本欧美国产精品第一页久久,成人18免费软件

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

簡(jiǎn)單SQL也很慢？數(shù)據(jù)庫(kù)端到端性能問題的解決思路探討

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

《簡(jiǎn)單SQL也很慢？數(shù)據(jù)庫(kù)端到端性能問題的解決思路探討》要點(diǎn)：
本文介紹了簡(jiǎn)單SQL也很慢？數(shù)據(jù)庫(kù)端到端性能問題的解決思路探討，希望對(duì)您有用。如果有疑問，可以聯(lián)系我們。

作者介紹

田冬雪,美團(tuán)點(diǎn)評(píng)數(shù)據(jù)庫(kù)架構(gòu)師,7年數(shù)據(jù)庫(kù)自動(dòng)化運(yùn)維經(jīng)驗(yàn).目前負(fù)責(zé)美團(tuán)點(diǎn)評(píng)基礎(chǔ)技術(shù)研究、數(shù)據(jù)庫(kù)高可用架構(gòu)優(yōu)化、數(shù)據(jù)庫(kù)運(yùn)維自動(dòng)化推進(jìn),美團(tuán)點(diǎn)評(píng)工具平臺(tái)融合等.

作為綜合性多業(yè)務(wù)的“互聯(lián)網(wǎng)+生活服務(wù)”平臺(tái),美團(tuán)點(diǎn)評(píng)對(duì)數(shù)據(jù)庫(kù)的穩(wěn)定運(yùn)行有較高的要求,小概率的性能抖動(dòng)(包括慢SQL)都會(huì)造成一定的可用性損失.本文將從過去幾年遇到的一些性能問題中,挑選了一個(gè)較為棘手的案例,探究端到端數(shù)據(jù)庫(kù)性能問題的解決思路,為DBA同學(xué)在解決類似問題時(shí)提供一種參考.

問題描述

在一段時(shí)間內(nèi)不斷有開發(fā)同學(xué)反饋,線上應(yīng)用程序獲取數(shù)據(jù)超時(shí),通過CAT監(jiān)控系統(tǒng)發(fā)現(xiàn)這些應(yīng)用的SQL 99line都比較高,這在一定程度上影響了對(duì)應(yīng)業(yè)務(wù)的QoS,比如達(dá)不到99.99%的業(yè)務(wù)可用性(超時(shí)被定義為不可用).這些問題出現(xiàn)在很多業(yè)務(wù)場(chǎng)景中,是一個(gè)普遍性問題.通過CAT監(jiān)控系統(tǒng)、SQL樣本、慢查詢系統(tǒng)等進(jìn)一步了解,發(fā)現(xiàn)這類SQL有如下特征:

  • 基本上都是以主鍵或唯一鍵為條件的簡(jiǎn)單查詢,查詢后的結(jié)果集及掃描的行數(shù)都比較小；
  • 查詢的表的數(shù)據(jù)總量也很小,最小的表甚至只有幾千行；
  • 時(shí)間達(dá)到了幾百ms,甚至1s；
  • 數(shù)據(jù)庫(kù)的slow log里沒有記錄這類SQL.

下圖為CAT相關(guān)監(jiān)控?cái)?shù)據(jù)的樣本,以xxx-service這個(gè)service為例：

99line的監(jiān)控?cái)?shù)據(jù),有很多SQL的返回時(shí)間超過100ms以上.

監(jiān)控?cái)?shù)據(jù)

SQL的絕對(duì)數(shù)量在2016年9月6日當(dāng)天為 ：3788.

SQL

具體到某個(gè)SQL,甚至達(dá)到了929ms.

簡(jiǎn)單SQL也很慢？數(shù)據(jù)庫(kù)端到端性能問題的解決思路探討

FB_Coach的表結(jié)構(gòu)如下：

可看到最多641條記錄,還有聯(lián)合索引.

索引

概要分析

要想定位到原因,必須通過排除法找到該SQL到底慢在哪個(gè)階段,這樣才能縮小范圍.接下來我們來分析慢SQL的花費(fèi)時(shí)間組成.從下圖可看出,時(shí)間主要由3部分組成：

簡(jiǎn)單SQL也很慢？數(shù)據(jù)庫(kù)端到端性能問題的解決思路探討

  • App Server：發(fā)出SQL請(qǐng)求的時(shí)間,接收返回結(jié)果的時(shí)間
  • 網(wǎng)絡(luò)：SQL請(qǐng)求包及查詢結(jié)果在網(wǎng)絡(luò)上花費(fèi)的時(shí)間
  • MySQL Server：發(fā)出SQL到查詢結(jié)果整個(gè)過程花費(fèi)的時(shí)間

我們可以通過抓包工具獲取每個(gè)階段花費(fèi)的時(shí)間,從而定位到底慢在哪個(gè)階段.

問題解決思路迭代

思路1：確認(rèn)哪個(gè)過程花費(fèi)的時(shí)間最多
方法：分別在APP Server與MySQL Server部署TcpDump抓包工具,得到數(shù)據(jù)包在4個(gè)監(jiān)測(cè)點(diǎn)的“到達(dá)時(shí)間”.為了方便,把如下4個(gè)Wireshark分析結(jié)果(對(duì)TcpDump抓取日志分析)按4個(gè)方位標(biāo)注：
  • APP Server 發(fā)出SQL(左上)
  • MySQL Server 收到SQL(右上)
  • MySQL Server 將查詢發(fā)出(右下)
  • APP Server 收到查詢結(jié)果(左下)

從數(shù)據(jù)可以準(zhǔn)確的看出時(shí)間主要花費(fèi)在MySQL內(nèi)部,具體時(shí)間為22.569285000-21.962634000=0.6066509999999994(秒),約為606ms.

抓包結(jié)果：慢在MySQL Server端.

MySQL

簡(jiǎn)單SQL也很慢？數(shù)據(jù)庫(kù)端到端性能問題的解決思路探討

思路2：一條SQL進(jìn)入MySQL Server到查詢結(jié)果輸出分哪些階段?

方法：將MySQL內(nèi)部對(duì)SQL查詢的流程進(jìn)行梳理,采用排除法定位問題.要把經(jīng)典圖拿出來說事了,以下基礎(chǔ)知識(shí)主要來自于《高性能MySQL》,“拿來主義”一下.

MySQL

首先可以看到,MySQL主要有三個(gè)組件：連接/線程處理、MySQL Server層、存儲(chǔ)引擎層.

  • 最上層主要進(jìn)行連接處理、授權(quán)認(rèn)證、安全等；
  • 第二層包括查詢解析、分析、優(yōu)化(這三個(gè)是解決問題最關(guān)心的)、緩存管理、所有內(nèi)置函數(shù)、存儲(chǔ)過程、觸發(fā)器、視圖,似乎扯得有點(diǎn)遠(yuǎn)；
  • 第三層包含了主要的存儲(chǔ)引擎層,MySQL Server層(第二層)通過“存儲(chǔ)引擎API”向存儲(chǔ)引擎層存儲(chǔ)和提取數(shù)據(jù),此層主要是數(shù)據(jù)存儲(chǔ)相關(guān).

接下來通過一個(gè)客戶端請(qǐng)求查詢數(shù)據(jù),看看MySQL主要做哪些工作吧.

每個(gè)客戶端(可能理解為App負(fù)責(zé)連接數(shù)據(jù)庫(kù)的組件,我們叫DAL)連接到MySQL服務(wù)器進(jìn)程后會(huì)擁有一個(gè)線程,這個(gè)連接的所有查詢都會(huì)在該線程中去執(zhí)行,同時(shí)服務(wù)器會(huì)緩存線程,以減少創(chuàng)建或銷毀線程的開銷和頻繁的上下文切換.

當(dāng)客戶連接到MySQL服務(wù)器時(shí),服務(wù)器會(huì)分配一個(gè)線程,之后進(jìn)行權(quán)限認(rèn)證,認(rèn)證通過后,MySQL就開始解析該SQL查詢,并創(chuàng)建內(nèi)部數(shù)據(jù)數(shù)據(jù)結(jié)構(gòu)(解析樹),然后對(duì)其各種優(yōu)化,最后調(diào)用存儲(chǔ)引擎API獲取或存儲(chǔ)需要的數(shù)據(jù),最后將查詢結(jié)果返回給客戶端.

通過以上“背書”,我們大概了解了一個(gè)SQL請(qǐng)求的執(zhí)行過程,那到底慢在哪個(gè)階段呢?

通過“慢SQL特點(diǎn)”的第4條知道,“數(shù)據(jù)庫(kù)的slow log里沒有記錄這類SQL”,那慢SQL發(fā)生的階段就可以排除了.

MySQL slow log是記錄SQL執(zhí)行過程花費(fèi)的時(shí)間,記錄的時(shí)間從“SQL解析”到“存儲(chǔ)引擎”返回?cái)?shù)據(jù)整個(gè)過程,所以可以排除該SQL是慢在第二層和第三層,那么只能是把時(shí)間花費(fèi)在第一層了?和線程相關(guān)?

結(jié)果：很可能慢在MySQL線程管理上.

思路3：是創(chuàng)建線程慢?thread cache不夠用,需要頻繁的創(chuàng)建線程?

方法：查看當(dāng)時(shí)數(shù)據(jù)庫(kù)的狀態(tài)值

數(shù)據(jù)庫(kù)

可以看到,當(dāng)時(shí)空閑的thread很多,監(jiān)控圖也沒有抖動(dòng),所以并沒有頻繁地創(chuàng)建線程.慢SQL產(chǎn)生的時(shí)間點(diǎn),空閑的thread很多,并沒有進(jìn)行大量的線程創(chuàng)建.

那問題到底出現(xiàn)在和線程相關(guān)的哪個(gè)環(huán)節(jié)呢? 先把所有和thread相關(guān)的參數(shù)列出來.

thread_cache_size
thread_concurrency
thread_handling
thread_pool_high_prio_mode
thread_pool_high_prio_tickets
thread_pool_idle_timeout
thread_pool_max_threads
thread_pool_oversubscribe
thread_pool_size
thread_pool_stall_limit
thread_stack
thread_statistics

一眼看過去,大部分是和Thread-Pool相關(guān).同時(shí)意識(shí)到這些問題是隨著升級(jí)到MySQL 5.6產(chǎn)生的,5.6引入了Thread-Pool功能.

結(jié)果：看來MySQL5.6的Thread-Pool有很大嫌疑了.

思路4：關(guān)閉MySQL 5.6的Thread-Pool,確認(rèn)一下問題

方法：調(diào)整MySQL參數(shù) thread_handling =?pool-of-threads—- →?thread_handling =?One-Connection-Per-Thread.

結(jié)論：關(guān)閉Thread-Pool功能后,減少78%的慢SQL,側(cè)面證明是Thread-Pool的問題.

以下是具體的證據(jù),以xxx-service這個(gè)service為例：打開Thread-Pool功能(2016年9月6日當(dāng)天數(shù)據(jù)).

99line占比：有好多超過100ms的SQL.

簡(jiǎn)單SQL也很慢？數(shù)據(jù)庫(kù)端到端性能問題的解決思路探討

慢SQL數(shù)量：3788

簡(jiǎn)單SQL也很慢？數(shù)據(jù)庫(kù)端到端性能問題的解決思路探討

關(guān)閉Thread-Pool功能后(2016年9月13日當(dāng)天數(shù)據(jù)).

99line占比：已經(jīng)看不到超過100ms的sql了,都在10ms以內(nèi).

簡(jiǎn)單SQL也很慢？數(shù)據(jù)庫(kù)端到端性能問題的解決思路探討

慢SQL數(shù)量：818

簡(jiǎn)單SQL也很慢？數(shù)據(jù)庫(kù)端到端性能問題的解決思路探討

那么關(guān)閉Thread-Pool ?答案很顯然,不能!Thread-Pool是MySQL5.6重要的功能,能夠保證MySQL數(shù)據(jù)庫(kù)高并發(fā)下的性能穩(wěn)定.

思路5：調(diào)優(yōu)Thread-Pool相關(guān)參數(shù)

方法：深入了解Thread-Pool的工作原理,查找可能產(chǎn)生慢SQL的參數(shù).

結(jié)果：找到了相關(guān)參數(shù)(thread_pool_stall_limit),并且效果明顯,慢SQL數(shù)量從最初的3788減少到63,幾乎全部消滅掉.

以xxx-service這個(gè)service為例,調(diào)整后的效果,2016年9月20日當(dāng)天的數(shù)據(jù)：

99line占比：

簡(jiǎn)單SQL也很慢？數(shù)據(jù)庫(kù)端到端性能問題的解決思路探討

慢SQL數(shù)量：63

簡(jiǎn)單SQL也很慢？數(shù)據(jù)庫(kù)端到端性能問題的解決思路探討

?

ok,效果有了,總結(jié)一下

問題分析

1、基本原理沒有引入Thread-Pool前,MySQL使用的是one thread per connection,一旦connection增加到一定程度,MySQL的性能將急劇下降甚至被壓跨.引入Thread-Pool后將會(huì)解決上述問題,同時(shí)會(huì)減少M(fèi)ySQL內(nèi)部的線程數(shù)(節(jié)省內(nèi)存)及頻繁創(chuàng)建線程的開銷(節(jié)省CPU).2、Thread-Pool是如何工作的?在MySQL內(nèi)部有一個(gè)專用的thread用來監(jiān)聽數(shù)據(jù)庫(kù)連接請(qǐng)求,當(dāng)一個(gè)新的請(qǐng)求過來,如果采用以前的模型(one-thread-per-connection),main listener(這是主線程中的listener,為了避免與thread group 中的listener混淆,我們稱之為“Main listener”)將從thread cache中取出1個(gè)thread或創(chuàng)建1個(gè)新的thead立即處理該連接請(qǐng)求,由該thread完成該連接的整個(gè)生命周期；而如果采用Thread-Pool模型,這個(gè)連接請(qǐng)求將會(huì)被隨機(jī)放到一個(gè)thread group(thread pool由多個(gè)thread group 組成)的隊(duì)列中,之后該thread group中worker thread從隊(duì)列中取出并建立連接,一旦連接建立,該連接對(duì)應(yīng)的socket句柄將與該thread group中的listener關(guān)聯(lián)起來,之后該連接將在該thread group中完成它的生命周期.接下來我們來說說Thread Group .Thread Group是Thread-Pool的核心組件,所有的操作都是發(fā)生在thread group.Thread-Pool由多個(gè)(數(shù)量由參數(shù)thread_pool_size來決定,默認(rèn)等于cpu個(gè)數(shù))thrad group組成.一個(gè)連接請(qǐng)求被隨機(jī)地綁定到一個(gè)thread group,每個(gè)thread group獨(dú)立工作,并且占用一個(gè)核的CPU.所以thread group都會(huì)最大限度地保持一個(gè)thread處于ACTIVE狀態(tài),并且最好只有一個(gè),因?yàn)樘嗑陀锌赡軌嚎鐢?shù)據(jù)庫(kù).Thread Group中的thread一般有4個(gè)狀態(tài)：

  • TP_STATE_LISTENER
  • TP_STATE_IDLE
  • TP_STATE_ACTIVE
  • TP_STATE_WAITING

當(dāng)一個(gè)線程作為listener運(yùn)行時(shí)就處于“TP_STATE_LISTENER”,它通過epoll的方式監(jiān)聽聯(lián)接到該Thread Group的所有連接,當(dāng)一個(gè)socket就緒后,listener將決定是否喚醒一個(gè)thread或自己處理該socket.此時(shí)如果Thread Group的隊(duì)列為空,它將自己處理該socket并將狀態(tài)更改為“ACTIVE”,之后該thread 在MySQL Server內(nèi)部處理“工作”,當(dāng)該線程遇到鎖或異步IO(比如將數(shù)據(jù)頁讀入到buffer pool)這些wait時(shí),該thread將通過回調(diào)函數(shù)的方式告訴thread pool,讓其把自己標(biāo)記為“WAITING”狀態(tài).

此時(shí),假設(shè)隊(duì)列中有了新的socket準(zhǔn)備就緒,是立即創(chuàng)建新的線程還是等待剛才的線程執(zhí)行結(jié)束呢?

由于Thread-Pool最初設(shè)計(jì)的目標(biāo)是保持一定數(shù)量的線程處于“ACTIVE”狀態(tài),具體的實(shí)現(xiàn)方式就是控制thread group的數(shù)量和thread group內(nèi)部處于”ACTIVE”狀態(tài)的thread的數(shù)量.控制thread group內(nèi)部的ACTIVE狀態(tài)的數(shù)量,方法就是最大限度地保證處于ACTIVE狀態(tài)的線程個(gè)數(shù)是1.很顯然,當(dāng)前thread group中有一個(gè)處于WAITING狀態(tài)的thread了,如果再啟用一個(gè)新的線程并且處于ACTIVE狀態(tài),剛才的線程由WAITING變?yōu)锳CTIVE狀態(tài)時(shí),此時(shí)將會(huì)有2個(gè)“ACTIVE”狀態(tài)的線程,和最初的目標(biāo)似乎相背,但顯然也不能讓后續(xù)就緒的socket一直等待下去,那應(yīng)該怎么處理?

那么此時(shí)需要一個(gè)權(quán)衡了,提供了這樣的一個(gè)方法：對(duì)正在ACTIVE或WAITING狀態(tài)的線程啟用一個(gè)計(jì)數(shù)器,超過計(jì)數(shù)器后將該thread標(biāo)記為stalled,然后thread group創(chuàng)建新的thread或喚醒sleep的thread處理新的sokcet,這樣將是一個(gè)很好的權(quán)衡.超時(shí)時(shí)間該參數(shù)thread_pool_stall_limit來決定,默認(rèn)是500ms.

如果一個(gè)線程無事可做,它將保持空閑狀態(tài)(TP_STATE_WAITING)一定時(shí)間(thread_pool_idle_timeout參數(shù)決定,默認(rèn)是60秒)后“自殺”.

3、和我們遇到的具體問題相關(guān)的點(diǎn)

假設(shè)上文提到的由“ACTIVE”轉(zhuǎn)化為“WAITING”狀態(tài)的線程(標(biāo)記為“線程A”)所執(zhí)行的“SQL”可能是一個(gè)標(biāo)準(zhǔn)的慢SQL(命名為SQLA,執(zhí)行時(shí)間較長(zhǎng)),那么后續(xù)有連接請(qǐng)求分配到了同一個(gè)thread group,那么新連接的SQL(命名SQLB)需要等待線程A結(jié)束；如果SQLA執(zhí)行時(shí)間超過500ms,該thread group創(chuàng)建新的worker線程來處理SQLB.

不管哪種情況,SQLB都會(huì)在線程等待上花費(fèi)很多時(shí)間,此時(shí)SQLB就是CAT監(jiān)控系統(tǒng)上看到的慢SQL.又因?yàn)镾QLA不一定都是慢SQL,所以SQLB也不是每次在線程等待上花費(fèi)較多的時(shí)間,這就吻合我們看到的現(xiàn)象“一定比例的慢SQL”.

簡(jiǎn)單SQL也很慢？數(shù)據(jù)庫(kù)端到端性能問題的解決思路探討

?解決方法

找到問題了,那么解決辦法就簡(jiǎn)單了.調(diào)整thread_pool_stall_limit=10,這樣就強(qiáng)迫被SQLA更快被標(biāo)記為stalled,然后創(chuàng)建新的線程來處理SQLB.

帶來的價(jià)值

  • 以xxx-service為例,減少了98.3%的慢SQL,效果很明顯；
  • 該問題的解決讓百個(gè)產(chǎn)品線從中受益,業(yè)務(wù)可用性超過了99.99%.

簡(jiǎn)單SQL也很慢？數(shù)據(jù)庫(kù)端到端性能問題的解決思路探討

總結(jié)

首先我們分析了慢SQL的特點(diǎn)及該SQL花費(fèi)的時(shí)間組成,通過“時(shí)間花費(fèi)在哪”這一通用方法,不斷把問題范圍縮小,最終通過排除法將問題鎖定在MySQL內(nèi)部線程.對(duì)于MySQL內(nèi)部線程,我們通過對(duì)參數(shù)“全量掃描”,發(fā)現(xiàn)了與MySQL 5.6新開啟的參數(shù)有關(guān),粗略確定了Thread-Pool是導(dǎo)致慢SQL問題的.之后通過關(guān)閉Thread-Pool進(jìn)一步確認(rèn)是開啟該功能引起的.之后我們不斷調(diào)整參數(shù)和閱讀大量相關(guān)的資料,最終將問題解決.通過以上問題的解決,我們可以學(xué)到一些端到端的性能問題解決思路：定位問題

  • 劃分問題的邊界

每個(gè)問題總有它的邊界.當(dāng)我們無法一眼看出來問題的邊界在哪里時(shí),就需要不斷的通過排除法縮小邊界,在特定的邊界內(nèi)就用特定的專業(yè)知識(shí)來定位問題.

  • ?搜集關(guān)鍵數(shù)據(jù)得出靠譜結(jié)論

比如生產(chǎn)環(huán)境中會(huì)有各種數(shù)據(jù),包含監(jiān)控?cái)?shù)據(jù)、臨時(shí)部署工具獲取的數(shù)據(jù),充分利用這些數(shù)據(jù)支撐我們的結(jié)論.

  • 對(duì)問題產(chǎn)生的時(shí)間或影響范圍進(jìn)行上下文聯(lián)想

很多問題是隨著一些改變產(chǎn)生的,就像軟件的生命周期一樣,受到各種環(huán)境的變化影響.通過問題產(chǎn)生的上下去尋找問題的原因,可以發(fā)現(xiàn)大部分問題的產(chǎn)生原因.

解決問題

  • 不斷嘗試

有很多人認(rèn)為,知道問題的原因了,解決問題是比較容易的.其實(shí)我認(rèn)為這個(gè)是反的.因?yàn)橹挥星宄绬栴}解決了,才能證明問題的原因是對(duì)的.在找到問題的原因之前,其實(shí)我們已經(jīng)通過不斷的調(diào)整和測(cè)試把問題解決了.所以解決問題很關(guān)鍵,貌似是廢話.

  • 合理的理論推斷問題產(chǎn)生的原因

問題解決了,原因也找到了,最后一步還要“自圓其說”,這就需要深究技術(shù)原理,找到切入點(diǎn),復(fù)現(xiàn)問題了.

解決問題的方法有千萬種,這里列舉了其中一種,希望能夠幫助到大家.

參考文獻(xiàn)：

  • https://my.oschina.net/andylucc/blog/820624
  • https://yq.aliyun.com/articles/41078
  • http://blog.chinaunix.net/uid-28364803-id-3427833.html
  • http://blog.chinaunix.net/uid-28364803-id-3431242.html
  • https://www.percona.com/doc/percona-server/5.6/performance/threadpool.html
  • https://mariadb.com/kb/en/mariadb/thread-pool-in-mariadb/
  • https://www.safaribooksonline.com/library/view/high-performance-mysql/9781449332471/ch01.html#mysqlas_logical_architecture

文章來自微信公眾號(hào)：DBAplul社群

轉(zhuǎn)載請(qǐng)注明本頁網(wǎng)址：
http://www.snjht.com/jiaocheng/1966.html


    標(biāo)簽：