14.6.6 配置InnoDB 的線程

來自專欄 mysql 官方文檔翻譯

參考官方文檔：

https://dev.mysql.com/doc/refman/5.7/en/innodb-performance-thread_concurrency.html

InnoDB使用操作系統線程來處理來自用戶事務的請求。 （事務在提交或回滾之前可能會向InnoDB發出很多請求。）在具有多核處理器的現代操作系統和伺服器上，在上下文切換有效的情況下，大多數工作負載運行良好，對並發線程數沒有任何限制。 MySQL 5.5及以上版本的可伸縮性改進降低了對InnoDB內部並發執行線程數量的限制。

在減少線程上下文切換的場景中，InnoDB可以使用許多技術來限制並發執行的操作系統線程的數量（以及任何時候處理的請求的數量）。 當InnoDB收到來自用戶會話的新請求時，如果並發執行的線程數量處於預定義的限制，則新請求會在短時間內休眠，然後再次嘗試。 睡眠之後無法重新安排的請求被放入先入先出隊列中並最終被處理。 等待鎖的線程不計入並發執行線程的數量。

您可以通過設置配置參數innodb_thread_concurrency來限制並發線程的數量。 一旦執行線程的數量達到此限制，其他線程在放入隊列之前會休眠幾微秒，由配置參數innodb_thread_sleep_delay設置。

之前，它需要進行實驗才能找到innodb_thread_sleep_delay的最佳值，並且最佳值可能會根據工作負載而改變。 在MySQL 5.6.3及更高版本中，您可以將配置選項innodb_adaptive_max_sleep_delay設置為innodb_thread_sleep_delay允許的最大值，並且InnoDB根據當前的線程調度活動向上或向下自動調整innodb_thread_sleep_delay。 這種動態調整有助於線程調度機制在系統輕載時以及接近滿負載運行時順利運行。

innodb_thread_concurrency的默認值和並發線程數默認的默認限制在MySQL和InnoDB的不同版本中已經改變。 innodb_thread_concurrency的默認值為0，所以默認情況下並發執行線程的數量沒有限制。

只有當並發線程數量有限時，InnoDB才會使線程進入睡眠狀態。 在線程數沒有限制的情況下，所有的競爭都是預定的。 也就是說，如果innodb_thread_concurrency為0，則innodb_thread_sleep_delay的值將被忽略。

當線程數有限制時（innodb_thread_concurrency大於0時），InnoDB通過允許執行單個SQL語句期間發出的多個請求進入InnoDB，而不遵守innodb_thread_concurrency設置的限制來減少上下文切換開銷。 由於SQL語句（例如連接）可能包含InnoDB中的多個行操作，因此InnoDB會分配指定數量的「tickets」，以便以最小的開銷重複調度線程。

當一個新的SQL語句啟動時，一個線程沒有tickets，並且它必須遵守innodb_thread_concurrency。 一旦線程被授權進入InnoDB，它將被分配一些tickets，它可以用來隨後輸入InnoDB來執行行操作。 如果tickets用完，線程被驅逐，並且再次觀察到innodb_thread_concurrency，這可能會將線程放回到等待線程的先入先出隊列中。 當線程再次被授權進入InnoDB時，tickets再次分配。 分配的tickets數量由全局選項innodb_concurrency_tickets指定，預設值為5000。 一旦鎖定可用，正在等待鎖定的線程將被授予一ticket。

這些變數的正確值取決於您的環境和工作負載。 嘗試一系列不同的值來確定適用於您的應用程序的值。 在限制並發執行線程的數量之前，請查看可以提高多核和多處理器計算機上的InnoDB性能的配置選項，例如innodb_adaptive_hash_index。

14.6.7 配置 後台InnoDB I/O線程的數量

InnoDB使用後台線程來處理各種類型的I / O請求。 您可以使用innodb_read_io_threads和innodb_write_io_threads配置參數來配置服務在數據頁上讀寫I / O的後台線程數。 這些參數分別表示用於讀取和寫入請求的後台線程的數量。 它們在所有支持的平台上都很有效。 您可以在MySQL選項文件（my.cnf或my.ini）中設置這些參數的值; 你不能動態改變數值。 這些參數的默認值是4，允許值範圍是1-64。

這些配置選項的目的是使InnoDB在高端系統上更具可擴展性。 每個後台線程最多可處理256個待處理的I / O請求。 後台I / O的主要來源是預讀請求。 InnoDB試圖通過大多數後台線程平均共享工作的方式來平衡傳入請求的負載。 InnoDB也會嘗試將同一範圍內的讀請求分配給同一個線程，以增加合併請求的機會。 如果您有高端I / O子系統，並且您在SHOW ENGINE INNODB STATUS輸出中看到多於64×innodb_read_io_threads未決讀取請求，則可以通過增加innodb_read_io_threads的值來提高性能。

在Linux系統上，InnoDB默認使用非同步I / O子系統來執行數據文件頁面的預讀和寫請求，這改變了InnoDB後台線程處理這些類型的I / O請求的方式。

14.6.8 在 Linux 上使用非同步 I/O

InnoDB使用Linux上的非同步I / O子系統（本地AIO）來執行數據文件頁的預讀和寫請求。 此行為由innodb_use_native_aio配置選項控制，該選項僅適用於Linux系統，默認情況下處於啟用狀態。 在其他類Unix系統上，InnoDB只使用同步I / O。 歷史上，InnoDB只在Windows系統上使用非同步I / O。 在Linux上使用非同步I / O子系統需要libaio庫。

使用同步I / O，查詢線程對I / O請求進行排隊，InnoDB後台線程一次檢索一個排隊的請求，為每個請求發出同步I / O請求。 當I / O請求完成並且I / O調用返回時，處理請求的InnoDB後台線程調用I / O完成常式並返回以處理下一個請求。 可以並行處理的請求數為n，其中n是InnoDB後台線程的數量。 InnoDB後台線程的數量由innodb_read_io_threads和innodb_write_io_threads控制。

使用AIO，查詢線程直接將I/O請求分派給操作系統，從而消除後台線程數量所帶來的限制。 InnoDB後台線程等待I/O事件發出已完成請求的信號。 當請求完成時，後台線程調用I/O完成常式並繼續等待I/O事件。

AIO的優勢在於I/O受限的系統的可擴展性，這些系統通常在SHOW ENGINE INNODB STATUS G輸出中顯示許多未決的讀/寫操作。 使用AIO時並行處理的增加意味著I/O調度程序的類型或磁碟陣列控制器的屬性對I/O性能有更大的影響。

AIO對於I/O密集型系統的潛在缺點是缺乏對同時分派給操作系統的I/O寫入請求數量的控制。 根據I/O活動的數量和系統性能的不同，調度到操作系統進行並行處理的太多I/O寫請求可能會導致I/O讀取不足。

如果操作系統中非同步I/O子系統的問題阻止了InnoDB的啟動，則可以使用innodb_use_native_aio = 0啟動伺服器。 如果InnoDB檢測到tmpdir位置，tmpfs文件系統和不支持tmpfs上的非同步I/O的Linux內核的組合，則此選項可能會在啟動期間自動禁用。

14.6.9 配置InnoDB Master 線程I/O 比率

InnoDB中的主線程是一個在後台執行各種任務的線程。 這些任務中的大多數都與I/O相關，例如從緩衝池中清除臟頁或將插入緩衝區中的更改寫入適當的二級索引。 主線程嘗試以不會對伺服器的正常工作產生不利影響的方式執行這些任務。 它試圖估計可用的空閑I/O帶寬，並調整其活動以利用此空閑容量。 歷史上，InnoDB使用了100 IOP（每秒輸入/輸出操作）的硬編碼值作為伺服器的總I / O容量。

參數innodb_io_capacity表示InnoDB可用的整體I / O容量。 此參數設置為大約系統每秒可執行的I / O操作的數量。 該值取決於您的系統配置。 當設置innodb_io_capacity時，主線程根據設置值估計可用於後台任務的I / O帶寬。 將該值設置為100會恢復到舊的行為。

您可以將innodb_io_capacity的值設置為任何數字100或更大。 默認值是200，反映了典型的現代I / O設備的性能比MySQL早期的高。 通常，以前的默認值100左右的值適用於消費級存儲設備，例如高達7200 RPM的硬碟驅動器。 更快的硬碟驅動器，RAID配置和固態硬碟從更高的價值中受益。

--innodb_io_capacity 可以配置到一個經過測試的實際的值

innodb_io_capacity設置是所有緩衝池實例的總限制。 當刷新臟頁時，innodb_io_capacity限制會在緩衝池實例中平均分配。

您可以在MySQL選項文件（my.cnf或my.ini）中設置此參數的值，或者使用需要SUPER特權的SET GLOBAL命令動態更改它。

innodb_flush_sync配置選項會導致innodb_io_capacity設置在檢查點發生的I / O活動爆發期間被忽略。 innodb_flush_sync默認啟用。

以前，InnoDB主線程還執行任何所需的清除操作。 在MySQL 5.6.5及更高版本中，這些I / O操作被移動到其他後台線程，後台線程的數量由innodb_purge_threads配置選項控制。

14.6.10 配置 Spin 鎖輪詢

很多InnoDB mutexes和rw-locks都是短時間保留的。 在多核系統上，線程可以更有效地檢查睡眠前是否可以獲取互斥鎖或rw鎖。 如果在此輪詢期間mutexes 或rw-locks可用，則線程可以在同一時間片內立即繼續。 然而，由多個共享對象線程輪詢太頻繁會導致「緩存乒乓」，不同的處理器會使每個其他緩存的部分無效。 InnoDB通過在隨後的輪詢之間等待隨機時間來最小化這個問題。 延遲被實現為繁忙循環。

您可以使用參數innodb_spin_wait_delay來控制測試mutexes 或rw-lock之間的最大延遲時間。 延遲循環的持續時間取決於C編譯器和目標處理器。 （在100MHz奔騰時代，延遲單位為1微秒）。在所有處理器內核共享高速緩存內存的系統上，可以通過設置innodb_spin_wait_delay = 0來減少最大延遲或完全禁用繁忙的loop。 在具有多個處理器晶元的系統上，高速緩存失效的影響可能更為顯著，您可能會增加最大延遲。

nnodb_spin_wait_delay的默認值為6.spin 等待延遲是一個動態全局參數，您可以在MySQL選項文件（my.cnf或my.ini）中指定該參數，或者在運行時使用SET GLOBAL innodb_spin_wait_delay = delay命令進行更改，其中 delay是所需的最大延遲。 更改設置需要SUPER許可權。

14.6.11 配置InnoDB 清理調度

InnoDB自動執行的清除操作（一種垃圾收集）可以由一個或多個單獨的線程執行，而不是作為主線程的一部分。 通過允許主資料庫操作獨立於後台發生的維護工作來運行，使用單獨的線程可以提高可伸縮性。

要控制此功能，請增加配置選項innodb_purge_threads的值。 如果DML操作集中在單個表或幾個表上，請將設置保持為低，以便線程不會彼此爭用以訪問繁忙表。 如果DML操作分散在多個表中，請增加設置。 它的最大值是32. innodb_purge_threads是一個非動態配置選項，這意味著它不能在運行時配置。

還有另一個相關配置選項，innodb_purge_batch_size，默認值為300，最大值為5000.此選項主要用於試驗和調整清除操作，對典型用戶應該不感興趣