RDS MySQL配置参数的调优建议 - 云数据库 RDS

对于RDS MySQL实例，您可以通过控制台修改参数。对于某些重要参数而言，不恰当的参数值会导致实例性能问题或应用报错，所以本文介绍一些重要参数的优化建议以减少您在设置参数时的疑虑。

说明

参数的默认值请在RDS管理控制台查看。

back_log

适用版本：8.0、5.7、5.6、5.5
修改完后是否需要重启：是
作用：MySQL每处理一个连接请求时都会创建一个新线程与之对应。在主线程创建新线程期间，如果前端应用有大量的短连接请求到达数据库，MySQL会限制这些新的连接进入请求队列，由参数back_log控制。如果等待的连接数量超过back_log的值，则不会接受新的连接请求，所以如果需要MySQL能够处理大量的短连接，需要提高此参数的大小。
现象：如果参数过小，应用可能出现如下错误：
```
SQLSTATE[HY000] [2002] Connection timed out;
```
修改建议：提高此参数值的大小。

innodb_autoinc_lock_mode

适用版本：8.0、5.7、5.6、5.5
修改完后是否需要重启：是
作用：在MySQL 5.1.22后，InnoDB为了解决自增主键锁表的问题，引入了参数innodb_autoinc_lock_mode，用于控制自增主键的锁机制。该参数可以设置的值为0、1、2，RDS默认的参数值为1，表示InnoDB使用轻量级别的mutex锁来获取自增锁，替代最原始的表级锁。但是在load data（包括INSERT … SELECT和REPLACE … SELECT）场景下若使用自增表锁，则可能导致应用在并发导入数据时出现死锁。

现象：在load data（包括INSERT … SELECT和REPLACE … SELECT）场景下若使用自增表锁，在并发导入数据时出现如下死锁：

RECORD LOCKS space id xx page no xx n bits xx index PRIMARY of table xx.xx trx id xxx lock_mode X insert intention waiting. TABLE LOCK table xxx.xxx trx id xxxx lock mode AUTO-INC waiting；

修改建议：建议将该参数值改为2，表示所有情况插入都使用轻量级别的mutex锁（只针对row模式），这样就可以避免auto_inc的死锁，同时在INSERT … SELECT的场景下性能会有很大提升。
说明
当该参数值为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参数配置。
修改建议：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操作带来的性能影响。
修改建议：如果用户担心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
修改完后是否需要重启：否
作用：脏页高于该百分比会启动刷脏从而控制脏页百分比。该值应该始终小于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清除操作的后台线程数。增加该值将创建额外的清除线程，可以提高在多个表上执行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