云数据库 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