詭異的”慢查詢“

       早上接到研發的通知，說數據庫有大量的慢查詢，并把調用的時間發過來，查詢時間基本在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以內，沒有出現忽高忽低的情況了。

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