詭異的”慢查詢“

zlingyi發表於2018-08-10

       早上接到研發的通知,說資料庫有大量的慢查詢,並把呼叫的時間發過來,查詢時間基本在0.5s以上。通知他們把sql發過來,開啟資料庫連線,執行sql,發現sql執行很快,執行時間不到0.1s,讓他們排查下網路問題。

       過了段時間接到通知,網路無任何問題,懷疑是不是dns問題導致,使用本地連線和遠端連線方式執行sql。


time mysql -S mysql.sock -uxxx-p'xxx' -e "select 1"

time mysql -h xx.xx.xx.xx -P xxxx-u xxx-p'xxx' -e "select 1"

       發現查詢差不多,大部分的查詢時間都需要0.5s左右,少部分在0.1s不到。排除dns的問題。同事確認資料庫的連線確實有問題。

       透過對比發現,其他的例項均沒有這種情況,在這臺物理機上還有一個其他例項,訪問基本在0.1s不到,只有極少部分達到0.5s,懷疑是配置引數問題,將兩個例項的引數檔案show global status;show global variables;做對比,沒有找到明顯異常。暫時陷入僵局。

        由於是資料庫的連線問題,領導要求必須解決,我們再次思考,因為開啟資料庫的連線執行sql很快,但是像上面那樣執行sql卻很慢,懷疑是資料庫的執行緒池的問題。查詢相關執行緒池的資料。

客戶端連線伺服器執行緒,對兩者進行對映,且對應關係不固定 ,伺服器執行緒負責執行從客戶端傳來的sql

基本單位為執行緒組,每組包含一個監聽執行緒和執行執行緒(有可能多個),前者負責接受客戶端傳來的sql,後者負責執行;

當接受到新sql時,如果此時沒有其他sql則由監聽執行緒立即執行,此期間該組暫時無監聽,如果sql執行時間過長,則執行緒池為其分配一個新的監聽執行緒;否則排隊等待;

        mysql的執行緒池(thread pool)預設有三種模式:one-thread-per-connection,pool-of-threads,和no-thread

Thread_pool_size為執行緒組的數量,預設為cpu核數,Threads_running為正常執行的執行緒數量,檢視當前庫的thread情況:

        Threads_running明顯超出Thread_pool_size值,由於thread pool最初設計的目標是保持一定數量的執行緒處於“ACTIVE”狀態,具體的實現方式就是控制thread group的數量和thread group內部處於"ACTIVE"狀態的thread的數量。控制thread group內部的ACTIVE狀態的數量,方法就是最大限度地保證處於ACTIVE狀態的執行緒個數是1。很顯然當前thread group中有一個處於WAITING狀態的thread了,如果再啟用一個新的執行緒並且處於ACTIVE狀態,剛才的執行緒由WAITING變為ACTIVE狀態時,此時將會有2個“ACTIVE”狀態的執行緒,和最初的目標似乎相背,但顯然也不能讓後續就緒的socket一直等待下去,那應該怎麼處理?

 那麼此時需要一個權衡了,提供了這樣的一個方法:對正在ACTIVE或WAITING狀態的執行緒啟用一個計數器,超過計數器後將該thread標記為stalled,然後thread group建立新的thread或喚醒sleep的thread處理新的sokcet,這樣將是一個很好的權衡。超時時間該引數thread_pool_stall_limit來決定,預設是500ms。

       終於找到為什麼查詢時間經常在0.5s的原因了!!振奮人心的訊息。找到原因就好辦了,mysql5.7該引數預設為6ms,生產是5.6,預設為500ms,我們可以將其改為10ms,這樣就強迫被老的sql更快被標記為stalled,然後建立新的執行緒來處理新連線。

        網上也檢視了其他的資料,據稱阿里的資料庫的thread_pool_stall_limit引數值也10ms,我們再次測試,連線查詢都在0.1s以內,沒有出現忽高忽低的情況了。

        第二天研發反饋,以前半小時一次的慢查詢告警消失了。詭異的慢查詢終於結束了。





來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/28686045/viewspace-2199616/,如需轉載,請註明出處,否則將追究法律責任。

相關文章