ApsaraDB RDS：參數調優建議

back_log

• 適用版本：8.0、5.7、5.6、5.5

• 修改完後是否需要重啟：是

• 作用：MySQL每處理一個串連請求時都會建立一個新線程與之對應。在主線程建立新線程期間，如果前端應用有大量的短串連請求到達資料庫，MySQL會限制這些新的串連進入請求隊列，由參數back_log控制。如果等待的串連數量超過back_log的值，則不會接受新的串連請求，所以如果需要MySQL能夠處理大量的短串連，需要提高此參數的大小。

• 現象：如果參數過小，應用可能出現如下錯誤：

  SQLSTATE[HY000] [2002] Connection timed out;

• 修改建議：3000

rpl_semi_sync_master_timeout

• 適用版本：8.0、5.7、5.6

• 修改完後是否需要重啟：否

• 作用：在使用半同步複製的執行個體中，事務在主庫提交前，需要等待備庫收到本事務所有的 binlog；當這個等待超過本參數配置的值之後，會觸發逾時，將整個執行個體退化到非同步複製。發生逾時後，如果備庫追上了主庫所有的 binlog 日誌，執行個體會自動回到半同步複製模式。

• 修改建議：此參數的單位為毫秒，建議將此參數設定為 1000（1秒）。對於資料可靠性要求高的執行個體，可以調高此參數來防止半同步複製退化，但需注意，如果此參數設定過高，在執行大事務時可能出現長時間的執行個體不可寫，導致 HA 探活失敗進而引發切換。

innodb_autoinc_lock_mode

• 適用版本：8.0、5.7、5.6、5.5

• 修改完後是否需要重啟：是

• 作用：在MySQL 5.1.22後，InnoDB為瞭解決自增主鍵鎖表的問題，引入了參數innodb_autoinc_lock_mode，用於控制自增主鍵的鎖機制。該參數可以設定的值為0、1、2，其含義解釋如下：

  • 0 表示傳統模式，SQL語句取自增值前會持有自增鎖，SQL語句結束時釋放自增鎖，這種模式會嚴重影響插入的並發度。

  • 1 表示連續模式，SQL語句取自增值前會持有自增鎖，對於插入行數固定的SQL語句，取完自增值之後立刻釋放自增鎖；對於插入行數不確定的SQL語句，在語句結束時釋放自增鎖。

  • 2 表示交叉模式，SQL語句取自增值前會持有自增鎖，取完自增值後，不論SQL語句的插入行數是否確定，都立即釋放自增鎖。

• 修改建議：建議將該參數值設定為2，表示所有情況插入都使用輕量的鎖，這樣可以提升插入效能，並且避免auto_inc的死結。RDS MySQL的 binlog 使用row模式，配置此參數為2不會引起資料不一致。

  說明

  當該參數值為2時，binlog的格式需要被設定為row。

query_cache_size

• 適用版本：5.7、5.6、5.5

• 修改完後是否需要重啟：否

• 作用：該參數用於控制MySQL query cache的記憶體大小。如果MySQL開啟query cache，在執行每一個query的時候會先鎖住query cache，然後判斷是否存在於query cache中，如果存在則直接返回結果，如果不存在，則再進行引擎查詢等操作。同時，INSERT、UPDATE和DELETE等操作都會導致query cache失效，這種失效還包括結構或者索引的任何變化。但是cache失效的維護代價較高，會給MySQL帶來較大的壓力。所以，當資料庫不會頻繁更新時，query cache是很有用的，但如果寫入操作非常頻繁並集中在某幾張表上，那麼query cache lock的鎖機制就會造成很頻繁的鎖衝突，對於這一張表的寫和讀會互相等待query cache lock解鎖，從而導致SELECT的查詢效率下降。

• 現象：資料庫中有大量的串連狀態為checking query cache for query、Waiting for query cache lock、storing result in query cache。

• 修改建議：RDS預設是關閉query cache功能的，如果您的執行個體開啟了query cache，當出現上述情況後可以關閉query cache。

net_write_timeout

• 適用版本：8.0、5.7、5.6、5.5

• 修改完後是否需要重啟：否

• 作用：等待將一個block發送給用戶端的逾時時間。

• 現象：若參數設定過小，可能會導致用戶端出現如下錯誤：

  the last packet successfully received from the server was milliseconds ago或the last packet sent successfully to the server was milliseconds ago.

• 修改建議：該參數在RDS中預設設定為60秒，一般在網路條件比較差時或者用戶端處理每個block耗時較長時，由於net_write_timeout設定過小導致的串連中斷很容易發生，建議增加該參數的大小。

tmp_table_size

• 適用版本：8.0、5.7、5.6、5.5

• 修改完後是否需要重啟：否

• 作用：該參數用於決定內部記憶體暫存資料表的最大值，每個線程都要分配，實際起限制作用的是tmp_table_size和max_heap_table_size的最小值。如果記憶體暫存資料表超出了限制，MySQL就會自動地把它轉化為基於磁碟的MyISAM表。最佳化查詢語句的時候，要避免使用暫存資料表，如果實在避免不了的話，要保證這些暫存資料表是存在記憶體中的。

• 現象：如果複雜的SQL語句中包含了GROUP BY、DISTINCT等不能通過索引進行最佳化而使用了暫存資料表，則會導致SQL執行時間加長。

• 修改建議：如果應用中有很多GROUP BY、DISTINCT等語句，同時資料庫有足夠的記憶體，可以增大tmp_table_size（max_heap_table_size）的值提升查詢效能。

loose_rds_max_tmp_disk_space

• 適用版本：5.6、5.5

• 修改完後是否需要重啟：否

• 作用：用於控制MySQL能夠使用的臨時檔案的大小。

• 現象：如果臨時檔案超出loose_rds_max_tmp_disk_space的取值，則會導致應用出現如下錯誤：

  The table ‘/home/mysql/dataxxx/tmp/#sql_2db3_1’ is full

• 修改建議：首先需要分析一下導致臨時檔案增加的SQL語句是否能夠通過索引或者其它方式進行最佳化。其次，如果確定執行個體的空間足夠，則可以提升此參數的值，以保證SQL能夠正常執行。

loose_tokudb_buffer_pool_ratio

• 適用版本：5.6

• 修改完後是否需要重啟：是

• 作用：用於控制TokuDB引擎能夠使用的buffer記憶體大小，比如innodb_buffer_pool_size設定為1000MB，tokudb_buffer_pool_ratio設定為50（代表50%），那麼TokuDB引擎的表能夠使用的buffer記憶體大小則為500MB。

• 修改建議：如果RDS中使用TokuDB引擎，建議調大該參數，以此來提升TokuDB引擎表的訪問效能。

loose_max_statement_time

• 適用版本：5.6

• 修改完後是否需要重啟：否

• 作用：用於控制查詢（QUERY）在資料庫中的最長執行時間。如果超過該參數設定的時間，查詢將會失敗，預設是不限制。

• 現象：若查詢時間超過了該參數的值，則會出現如下錯誤：

  ERROR 3006 (HY000): Query execution was interrupted, max_statement_time exceeded

  說明

  參數修改後僅對新串連生效，保持中的串連需要斷開重連才會生效。

• 修改建議：如果您想要控制資料庫中SQL的執行時間，則可以開啟該參數，單位是毫秒。

loose_rds_threads_running_high_watermark

• 適用版本：5.6、5.5

• 修改完後是否需要重啟：否

• 作用：用於控制MySQL並發的查詢數目，比如將rds_threads_running_high_watermark的值設定為100，則允許MySQL同時進行的並發查詢為100個，超過限制數量的查詢將會被拒絕掉。

• 修改建議：該參數通常在秒殺或者大並發的情境下使用，對資料庫具有較好的保護作用。

innodb_buffer_pool_instances

• 適用版本：8.0、5.7、5.6

• 修改完後是否需要重啟：是

• 作用：將innodb_buffer_pool_size大於1 GB的記憶體緩衝池拆分成多個執行個體進行維護，每個執行個體都有自己的鎖、訊號量、物理塊（Buffer chunks）以及邏輯鏈表，各執行個體之間沒有競爭關係，可以並發讀取與寫入。對於緩衝池較大的執行個體，將緩衝池劃分為單獨的執行個體可以減少不同線程讀取和寫入時的爭用，進而提高並發效能。

• 修改建議：{LEAST(DBInstanceClassMemory/1073741824, 8)}

table_open_cache_instances

• 適用版本：8.0、5.7、5.6

• 修改完後是否需要重啟：是

• 作用：將開啟的表緩衝劃分為幾個大小為table_open_cache / table_open_cache_instances的較小緩衝執行個體，減少會話（Session）間表緩衝的爭用。

• 修改建議：16

table_open_cache

• 適用版本：8.0、5.7、5.6

• 修改完後是否需要重啟：否

• 作用：表緩衝的數量，表緩衝用於將表載入到緩衝中，實現快速存取該表。如果該值偏小，則有可能在高並發時引起SQL效能問題。如果該值過大，可能消耗大量記憶體，調高此參數時請關注執行個體的記憶體水位。

• 修改建議：{LEAST(DBInstanceClassMemory/1073741824*512, 8192)}

innodb_adaptive_hash_index

• 適用版本：8.0、5.7、5.6

• 修改完後是否需要重啟：否

• 作用：是否開啟自適應雜湊索引。自適應雜湊索引可以根據您提供的查詢條件加速定位到葉子節點，減少IO次數。

• 現象：開啟本參數能否帶來效能提升和業務SQL有關係，但部分操作可能引發自適應雜湊索引更新（例如對錶執行DDL操作時自適應雜湊索引會被清理），從而導致SQL被阻塞或效能下降。

• 修改建議：OFF。調整該參數可參照文檔RDS MySQL Adaptive Hash Index (AHI)最佳實務。

open_files_limit

• 適用版本：8.0、5.7、5.6

• 修改完後是否需要重啟：是

• 作用：該參數用於控制MySQL執行個體能夠同時開啟使用的檔案控制代碼數，同時會影響innodb_open_files參數配置。

• 修改建議：建議將此參數設定為655350。如果執行個體的規格較大（大於或等於32核），同時使用者的活躍會話數、表數量較多，還可根據實際情況再調大open_files_limit參數的值， 該參數配置超過執行個體檔案個數不會對執行個體的運行造成影響。

loose_innodb_rds_faster_ddl

• 適用版本：8.0、5.7、5.6（核心小版本均為20200630或以上）

• 修改完後是否需要重啟：否

• 作用：該參數開啟後，能夠對部分DDL操作進行加速，降低DDL操作帶來的效能影響。

• 修改建議：如果使用者擔心DDL操作對業務產生影響，建議開啟此參數。開啟該參數後，將使用RDS核心團隊自研的Buffer Pool頁面管理原則，該頁面管理原則能夠對部分DDL操作進行加速，降低部分DDL執行過程中對業務的影響。

innodb_thread_concurrency

• 適用版本：8.0、5.7、5.6

• 修改完後是否需要重啟：否

• 作用：InnoDB內部允許使用的最大線程數。值為0表示無並發限制。如果執行個體上並發過高引發效能問題，可以通過修改此參數控制InnoDB中的並發度。

• 該變數用於在高並發性系統上進行效能調優。

• 修改建議：如果未遇到高並發效能問題，建議設定為0（無限制）。

binlog_transaction_dependency_history_size

• 適用版本：8.0（核心小版本為20210930以上），5.7（核心小版本為20211231以上）

• 修改完後是否需要重啟：否

• 作用：啟動writeset功能後，會在記憶體中儲存最後修改某一行的事務資訊，事務資訊是以Hash的方式儲存的，該變數控制記憶體中可以儲存的這些Hash條目的上限，達到限制後會清空所有資訊。

• 現象：該值設定過小會影響備庫並行回放的並發度，從而產生複寫延遲。

• 修改建議：500000。調整該參數可參照文檔調整執行個體WRITESET相關參數。

binlog_transaction_dependency_tracking

• 適用版本：8.0（核心小版本為20210930以上），5.7（核心小版本為20211231以上）

• 修改完後是否需要重啟：否

• 作用：MySQL的控制並行複製的方法，設定為WRITESET可以檢測事務間行層級的衝突，從而在備庫實現更快的並行回放。

• 修改建議：WRITESET。調整該參數可參照文檔調整執行個體WRITESET相關參數。

innodb_max_dirty_pages_pct_lwm

• 適用版本：8.0、5.7、5.6

• 修改完後是否需要重啟：否

• 作用：髒頁占緩衝池的百分比高於此參數時，執行個體將會啟動預刷髒來控制髒頁佔比。設定此參數為 0 意味著禁止預刷髒，此參數的值應該始終低於innodb_max_dirty_pages_pct的值。

• 修改建議：10

eq_range_index_dive_limit

• 適用版本：8.0、5.7、5.6

• 修改完後是否需要重啟：否

• 作用：如果查詢中等值條件的等值範圍數大於等於該值，最佳化器使用統計資訊來計算執行計畫；否則最佳化器用index dive的方式進行採樣，擷取統計資訊，用該統計資訊計算執行計畫。

  • 例：

    col_name IN(val1, ..., valN)
    col_name = val1 OR ... OR col_name = valN

    即包含N個等值範圍。

  • 該參數更詳細資料見官網文檔https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_eq_range_index_dive_limit。

• 修改建議：5.6版本為10，5.7和8.0版本為100。

innodb_flush_neighbors

• 適用版本：8.0、5.7、5.6

• 修改完後是否需要重啟：否

• 作用：指定從InnoDB緩衝池中重新整理一個髒頁時，是否也會重新整理和該髒頁同簇的其他髒頁。

  • 設定為0表示同簇的其他髒頁不會被重新整理。

  • 設定為1表示會重新整理同簇內和該髒頁相鄰的其他髒頁。

  • 設定為2表示會重新整理同簇內的所有髒頁。

• 修改建議：0

innodb_lock_wait_timeout

• 適用版本：8.0、5.7、5.6

• 修改完後是否需要重啟：否

• 作用：InnoDB事務在放棄擷取行鎖之前需要等待的時間，單位為秒。

• 修改建議：50

innodb_lru_scan_depth

• 適用版本：8.0、5.7、5.6

• 修改完後是否需要重啟：否

• 作用：指定頁面清理線程在掃描緩衝池的LRU頁面鏈表時的掃描深度，該參數會影響緩衝池的刷髒操作。

• 修改建議：{LEAST(DBInstanceClassMemory/1048576/8, 8192)}

innodb_purge_threads

• 適用版本：8.0、5.7、5.6

• 修改完後是否需要重啟：是

• 作用：InnoDB用於清理undo記錄的後台線程數。增加該值可以提升 undo 清理的效率，防止 undo 檔案堆積，在部分情況下可以提升DML和查詢操作執行的效率。

• 修改建議：LEAST(DBInstanceClassMemory/1073741824, 8)

innodb_log_file_size

• 適用版本：8.0、5.7、5.6

• 修改完後是否需要重啟：是

• 作用：

  • REDO日誌組中每個記錄檔的大小。記錄檔大小之和（innodb_log_file_size * innodb_log_files_in_group）不能超過512GB。預設值為48MB。

  • 通常，記錄檔的大小應該足夠大，以便伺服器有足夠的REDO日誌空間來處理超過一個小時的寫活動，從而可以平滑工作負載的高峰和低穀。該值越大，緩衝池中需要的檢查點重新整理活動就越少，從而節省磁碟I/O。但是較大的記錄檔會使崩潰恢複變慢。

• 修改建議：隨規格變化。

innodb_sync_array_size

• 適用版本：8.0、5.7、5.6

• 修改完後是否需要重啟：是

• 作用：定義互斥鎖/鎖等待數組的大小。增加該值會拆分用於協調線程的內部資料結構，從而在具有大量等待線程的工作負載中獲得更高的並發性。這個值必須在MySQL執行個體啟動時配置，之後不能更改。對於經常產生大量等待線程（通常大於768）的工作負載，建議增加該值。

• 修改建議：128

innodb_page_cleaners

• 適用版本：8.0、5.7

• 修改完後是否需要重啟：是

• 作用：從緩衝池執行個體中清除髒頁的頁面清理線程的數量。當有多個頁清理線程時，每個緩衝池執行個體的緩衝池刷髒任務將被指派給閒置頁清理線程。innodb_page_cleaners預設值為4。如果頁面清理線程的數量超過緩衝池執行個體的數量，innodb_page_cleaners會自動化佈建為與innodb_buffer_pool_instances相同的值。

• 修改建議：{LEAST(DBInstanceClassMemory/1073741824, 8)}

innodb_open_files

• 適用版本：8.0、5.7、5.6

• 修改完後是否需要重啟：是

• 作用：該參數指定InnoDB能夠同時開啟的最大檔案控制代碼數。

• 現象：如該值設定較小，可能會出現如下錯誤，影響執行個體效能：

  [Warning] [MY-012152] [InnoDB] Open files * exceeds the limit *

• 修改建議：20000

default_time_zone

• 適用版本：8.0、5.7、5.6

• 修改完後是否需要重啟：是

• 作用：預設時區。

• 現象：如未設定該值，會使用主機時區，這可能導致作業系統層面的鎖等待，從而造成CPU使用率飆升。

• 修改建議：根據實際使用需求設定為相應的時區。調整該參數可參照文檔RDS MySQL參數time_zone最佳實務。

general_log

• 適用版本：8.0、5.7、5.6

• 修改完後是否需要重啟：否

• 作用：指定是否開啟general log。

• 現象：開啟general log會產生一些問題，詳情請參見RDS MySQL General log常見問題。

• 修改建議：OFF

innodb_io_capacity

• 適用版本：8.0、5.7、5.6

• 修改完後是否需要重啟：否

• 作用：指定InnoDB背景工作每秒可用的I/O運算元。

• 修改建議：20000

innodb_io_capacity_max

• 適用版本：8.0、5.7、5.6

• 修改完後是否需要重啟：否

• 作用：如果重新整理活動落後，InnoDB會以高於innodb_io_capacity參數定義的速率來重新整理。innodb_io_capacity_max參數即定義了在這種情況下InnoDB背景工作的最大IOPS。

• 修改建議：40000