ApsaraDB RDS：參數調優建議

參數調優介紹

儘管RDS MySQL執行個體採用了參數模板，為特定情境預設了參數值（即預設參數值），但在實際應用中，仍需根據具體業務情境進行相應的調整。特別是一些關鍵參數，不當的參數值可能會導致執行個體效能問題或應用錯誤。

RDS MySQL支援參數診斷功能，該功能基於執行個體狀態資訊、參數模板、附加參數相關基準概念、計算公式及專家經驗，對執行個體的參數設定進行深入分析，並提供相應的參數最佳化建議。您可以利用參數診斷功能，結合本文中重要參數的調優建議，進行參數的最佳化調整。

注意事項

部分動態參數（MySQL 8.0和MySQL 5.7中Scope為Global,Session的參數）修改後，僅對新串連生效。如需對現有串連生效，請重新串連或重啟執行個體（重啟執行個體會閃斷，請在業務低峰期重啟）。

back_log

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

• 修改完後是否需要重啟：是，重啟執行個體才會生效。

• 是否會閃斷：是，建議在業務低峰期修改。

• 作用：控制MySQL在處理大量短串連請求時的串連隊列長度。如果等待串連數超過back_log值，新串連請求將被拒絕。需要處理大量短串連時，應提高此參數值。

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

  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（傳統模式）：SQL語句取自增值前會持有自增鎖，直至語句執行結束釋放自增鎖，這種模式會嚴重影響插入的並發度。

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

  • 2 （交叉模式）：SQL語句取自增值前會持有自增鎖，但無論插入行數是否確定，取完自增值後立即釋放鎖。

• 修改建議：建議將該參數值改為2，這樣可以避免auto_inc的死結，並提升INSERT … SELECT的效能。

  說明

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

query_cache_size

• 適用版本：5.7、5.6、5.5

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

• 是否會閃斷：否

• 作用：該參數用於控制MySQL query cache的記憶體大小。如果開啟query cache，查詢時會先檢查緩衝，命中則返回緩衝結果，未命中則正常執行查詢並將結果存入緩衝。任何對錶的寫操作（如INSERT、UPDATE、DELETE等）或結構變化都會使與該表相關的query cache失效，增加系統負擔。因此，query cache適用於更新不頻繁的資料庫，但在頻繁寫入的情況下可能導致鎖衝突，降低查詢效率。

• 現象：資料庫中有大量的串連狀態為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.

• 修改建議：預設值為60秒。建議在網路條件較差或用戶端處理每個block耗時較長時，增加該參數的大小，以避免串連中斷。

tmp_table_size

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

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

• 是否會閃斷：否

• 作用：該參數用於設定每個線程分配的內部記憶體暫存資料表的最大值，實際限制由tmp_table_size和max_heap_table_size中的較小值決定。超出限制時，MySQL會自動將其轉化為基於磁碟的表（在MySQL 8.0中為InnoDB表）。最佳化查詢時應盡量避免使用暫存資料表，若無法避免，應保證暫存資料表在記憶體中。

• 現象：複雜SQL語句中包含GROUP BY、DISTINCT等無法通過索引最佳化的暫存資料表，會導致SQL執行時間加長。

• 修改建議：預設值為2097152。若應用中有很多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設定為1000 MB，tokudb_buffer_pool_ratio設定為50（即50%），則TokuDB引擎的表可使用的buffer記憶體大小為500 MB。

• 修改建議：在RDS使用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並發的查詢數目。例如，將loose_rds_threads_running_high_watermark的值設定為100，則允許100個並發查詢，超過的查詢將被拒絕。

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

innodb_buffer_pool_size

• 適用版本：8.0、5.7、5.6

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

  • 8.0，5.7版本：否

  • 5.6版本：是，重啟執行個體才會生效。

• 是否會閃斷：

  • 8.0，5.7版本：否

  • 5.6版本：是，建議在業務低峰期修改。

• 作用：設定Innodb Buffer Pool的大小。

• 修改建議：{DBInstanceClassMemory*3/4}。

innodb_buffer_pool_instances

• 適用版本：8.0、5.7、5.6

• 修改完後是否需要重啟：是，重啟執行個體才會生效。

• 是否會閃斷：是，建議在業務低峰期修改。

• 作用：將innodb_buffer_pool_size大於1 GB的緩衝池拆分成多個執行個體，每個執行個體獨立管理，支援並發讀寫。

• 修改建議：{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*1024, 16384)}

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參數配置。

• 修改建議：5.6版本為65535，5.7和8.0版本為655350。如果執行個體的規格較大（大於或等於32核），同時使用者的活躍會話數、表數量較多，可根據實際情況將 open_files_limit 設定為一個合理且略高於實際需求的值。

loose_innodb_rds_faster_ddl

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

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

• 是否會閃斷：否

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

• 修改建議：如果擔心DDL操作對業務產生影響，建議開啟此參數。開啟後，將使用RDS自研的Buffer Pool頁面管理原則，能夠對部分DDL操作（如表結構變更）進行加速，降低對業務的影響。

innodb_thread_concurrency

• 適用版本：8.0、5.7、5.6

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

• 是否會閃斷：否

• 作用：InnoDB內部允許使用的最大線程數。值為0表示無並發限制。用於解決執行個體上並發過高引發的效能問題。

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

binlog_transaction_dependency_history_size

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

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

• 是否會閃斷：否

• 作用：控制記憶體中儲存的事務資訊Hash條目的上限，達到限制後會清空所有資訊，不會直接影響進行中的事務。

• 現象：設定過小會影響備庫並行回放的並發度，導致複寫延遲。

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

binlog_transaction_dependency_tracking

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

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

• 是否會閃斷：否

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

• 修改建議：WRITESET。修改此參數前，需先修改transaction_write_set_extraction參數，調整該參數請參見調整執行個體WRITESET相關參數。

transaction_write_set_extraction

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

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

• 是否會閃斷：否

• 作用：指定WRITESET功能使用的雜湊演算法。

• 修改建議：XXHASH64。調整該參數請參見調整執行個體WRITESET相關參數。

slave_parallel_workers

• 適用版本：8.0、5.7、5.6

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

• 是否會閃斷：否

• 作用：設定從庫上回放線程的數量，此參數較低時可能影響從庫並行回放的效率。

• 修改建議：{GREATEST(DBInstanceClassCPU, 8)}

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個等值範圍。

  • 更多資訊請參見官網文檔。

• 修改建議：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）不能超過512 GB。預設值為48 MB。

  • 記錄檔應足夠大，以處理超過一小時的寫活動，平滑工作負載高峰和低穀。較大的記錄檔減少緩衝池中的檢查點重新整理活動，節省磁碟I/O，但會使崩潰恢複變慢。

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

innodb_sync_array_size

• 適用版本：8.0、5.7、5.6

• 修改完後是否需要重啟：是，重啟執行個體才會生效。

• 是否會閃斷：是，建議在業務低峰期修改。

• 作用：定義互斥鎖/鎖等待數組的大小。增加該值可以提高具有大量等待線程的工作負載的並發性。該值必須在MySQL執行個體啟動時配置，之後不能更改。對於經常產生大量等待線程（通常大於768）的工作負載，建議增加該值。

• 修改建議：128

innodb_page_cleaners

• 適用版本：8.0、5.7

• 修改完後是否需要重啟：是，重啟執行個體才會生效。

• 是否會閃斷：是，建議在業務低峰期修改。

• 作用：定義頁面清理線程數量，預設值為4，超過緩衝池執行個體數量時自動調整為與緩衝池執行個體數量相同。

• 修改建議：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背景工作在重新整理活動落後時的最大IOPS。

• 修改建議：40000

innodb_change_buffering

• 適用版本：8.0、5.7、5.6

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

• 是否會閃斷：否

• 作用：將二級索引的寫入操作延遲，以便將其轉化為順序I/O。此功能在機械硬碟效能收益較高，在最新的硬體上收益有限，建議關閉。

• 修改建議：none

innodb_parallel_read_threads

• 適用版本：8.0（核心小版本為20200831以上）

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

• 是否會閃斷：否

• 作用：設定主鍵掃描的並發線程數，此功能存在bug，建議關閉。

• 修改建議：0

innodb_ft_cache_size

• 適用版本：8.0、5.7、5.6

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

• 是否會閃斷：是，建議在業務低峰期修改。

• 作用：設定每個表使用的全文索引的記憶體大小（單位：位元組）。

• 修改建議：8000000

innodb_ft_total_cache_size

• 適用版本：8.0、5.7、5.6

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

• 是否會閃斷：是，建議在業務低峰期修改。

• 作用：設定全執行個體使用的全文索引的記憶體大小（單位：位元組）。

• 修改建議：640000000

innodb_ft_result_cache_limit

• 適用版本：8.0、5.7、5.6

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

• 是否會閃斷：否

• 作用：設定每個全文索引的查詢結果集所使用的記憶體大小（單位：位元組）。

• 修改建議：2000000000

thread_pool_enabled

• 適用版本：8.0、5.7、5.6

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

• 是否會閃斷：否

• 作用：設定是否開啟線程池功能。

• 修改建議：ON

thread_pool_size

• 適用版本：8.0、5.7

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

• 是否會閃斷：否

• 作用：設定線程池中的線程組個數。

• 修改建議：{LEAST(DBInstanceClassCPU*2, 64)}

thread_pool_oversubscribe

• 適用版本：8.0、5.7、5.6

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

• 是否會閃斷：否

• 作用：設定線程池的每個線程組中，最多有多少個活躍線程。

• 修改建議：32

thread_stack

• 適用版本：8.0、5.7、5.6

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

• 是否會閃斷：是，建議在業務低峰期修改。

• 作用：設定每個使用者線程的棧深度，設定過小可能導致部分遞迴較深的SQL執行失敗。

• 修改建議：1048576

performance_schema

• 適用版本：8.0、5.7、5.6

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

• 是否會閃斷：是，建議在業務低峰期修改。

• 作用：設定是否開啟Performance Schema功能，該功能主要用於監控執行個體的運行情況，有記憶體消耗和效能瓶頸問題，建議關閉。

• 修改建議：OFF