MySQL Innodb 並發涉及參數

標籤：delay   show   span   com   線程阻塞   dev   應用   範圍   max   

1 參數作用

    MySQL的各個外掛程式式引擎中，都會對事務及線程做一定的處理和最佳化。在Innodb引擎中，總是嘗試保持 innodb內 作業系統的線程數（暫命名為innodb_thread） 應該小於或等於 系統可提供給innodb處理事務的線程數(暫命名為system_innodb_thread)。在大多數情況下，innodb_thread都不會指定一個限制值，而是讓它想要多少直接申請多少。

    當 innodb_thread 大於system_innodb_thread 時，期間較長時，會導致伺服器的線程資源被資料庫使用，CPU可能居高不下，甚至引發宕機。

    這個時候，Innodb內部可以提供一個參數來限制 並發線程（同一時刻可處理的請求數），當並發數達到 並發線程限制數時，再接收到一個新的請求，那麼這個請求需要在下次請求前先sleep一段時間，如果sleep後再請求還是沒有多餘線程提供其執行，那麼，它就會進入到先進先出的隊列中等待執行。這裡注意下，等待線程，不計入 innodb_thread 。innodb_thread_concurrency 參數因此而來。

   可以通過innodb_thread_concurrency 來調節  並發線程數的限制值，使用innodb_thread_sleep_delay來調整當 並發 thread 到達 innodb_thread_concurrency時需要sleep的時間。當請求被innodb接受的時候，會獲得一個 消費憑證 innodb_concurrency_tickets (預設5000次)，當這個請求中有多個SQL被執行的時候，每執行一次，消費一次tickets，在次數用完之前，該線程重新請求時無須再進行前面 thread 是否達到 並發限制值的檢查。

   同時 innodb_commit_concurrency也控制了多線程並發提交的數量。如果 innodb_thread_concurrency  設定的有點大innodb_commit_concurrency應該做出相應的調整，否則會造成大量線程阻塞。

   所以，跟並發相關的有這幾個參數設定：innodb_thread_concurrency、innodb_thread_sleep_delay、innodb_concurrency_tickets、innodb_commit_concurrency。

跟innodb_thread_concurrency相似的參數有 thread_concurrency ，但是它在5.6版本的官方文檔中已被標識為過時，在5.7.2版本廢除了該參數，所以我們這裡不涉及對該參數的測試及描述。

2 參數設定2.1 innodb_thread_concurrency2.1.1 預設值

   innodb_thread_concurrency預設是0，則表示沒有並發線程數限制，所有請求都會直接請求線程執行。注意：當 innodb_thread_concurrency 設定為0時，則innodb_thread_sleep_delay的設定將會被忽略，不起作用。如果資料庫沒出現效能問題時，使用預設值即可。

2.1.2 大於0

   當innodb_thread_concurrency>0，則表示有 並發數限制，當一個新的請求發起時，會檢查當前並發線程數是否達到了 innodb_thread_concurrency的限制值，如果有，則需要sleep一段時間（sleep的設定詳見下一部分），然後再再次請求，如果再次請求時，當前並發數還是達到限制值，那麼就會進入FIFO隊列等待執行。當進入到核心執行時，會得到一個 消費憑證 ticket，則這個線程，在後面的多次進入innodb執行操作是都不需要重複上面的檢查步驟，當把次數消費完，那麼這個線程就會被驅逐，等待下次再次進入Innodb，再重新分配ticket。

2.1.3 建議配置（來自官網）

• 當並發使用者線程數量小於64，建議設定innodb_thread_concurrency=0；
• 如果負載不穩定，時而低，時而高到峰值，建議先設定innodb_thread_concurrency=128，並通過不斷的降低這個參數，96, 80, 64等等，直到發現能夠提供最佳效能的線程數，例如，假設系統通常有40到50個使用者，但週期性數量增加至60，70，甚至200。你會發現，效能在80個並發使用者佈建時表現穩定，如果高於這個數，效能反而下降。在這種情況下，建議設定innodb_thread_concurrency參數為80，以避免影響效能；
• 如果DB伺服器上還允許其他應用，需要限制mysql的線程使用方式，則可以設定可分配給DB的線程數，但是不建議DB上跑其他應用，也不建議這麼設定，因為這樣可能導致資料庫沒有對硬體最優使用；
• 設定過高值，可能會因為系統資源內部爭奪導致效能下降；
• 在大多數情況下，最佳的值是小於並接近虛擬CPU的個數；
• 定期監控和分析DB，因為隨著資料庫負載的變化，業務的增加，innodb_thread_concurrency也需要動態調整。

2.2 innodb_thread_sleep_delay

   5.6.3版本前，需要反覆測試才能確定innodb_thread_sleep_delay值，並且固定為一個值，在5.6.3版本後，因為 Innodb 自動調整innodb_thread_sleep_delay參數：

• Innodb_adaptive_max_sleep_delay：最大sleep的時間，微秒為單位

可以通過設定參數 innodb_adaptive_max_sleep_delay 來限制 innodb_thread_sleep_delay的最大值，不設定 innodb_thread_sleep_delay的取值情況，讓Innodb自動跟進負載來調整，當系統負荷較高時，Innodb動態調整slee時間可使得資料庫穩定運行。

2.3 innodb_commit_concurrency

   該值只能為預設值0，mysql不限制並發提交。大於0表示允許N個事務在同一時間點提交，N的範圍是0-1000。 

    注意事項：mysqld運行時，不許把innodb_commit_concurrency 的值從0改為非0，或非0的值改為0；但允許從N改為M（N及M均大於0）

2.4 innodb_concurrency_tickets

   預設是5000（基於5.6，5.7）。

   如果innodb_concurrency_tickets設定小些，適用於小事物操作較多的系統，可以快速使用完線程後退出來，提供給其他請求使用；而對於大事務來說，可能會迴圈進入等待隊列中等待執行完成，這會耗費更多時間及資源；如果innodb_concurrency_tickets設定大些，適用於大事務頻繁操作的系統，這樣大事務則不需要頻繁進入queue等待隊列，可以通過較少的請求來處理；但是對於小事務來說，則意味著他們要等待更長的時候，才能排隊進入到核心執行。所以，當innodb_thread_concurrency>0時，需要上下調整 innodb_concurrency_tickets ，使其達到最佳效能。可以通過show engine innodb status 的queue查看，也可以通過INFORMATION_SCHEMA.INNODB_TRX 的TRX_CONCURRENCY_TICKETS查看消費次數情況。

MySQL Innodb 並發涉及參數