更新时间:2022-10-10 GMT+08:00

RDS for MySQL参数调优建议

数据库参数是数据库系统运行的关键配置信息,设置不合适的参数值可能会影响业务。本文列举了一些重要参数说明,更多参数详细说明,请参见MySQL官网

通过控制台界面修改MySQL参数值,请参见修改RDS for MySQL实例参数

修改敏感参数

若干参数相关说明如下:

  • “lower_case_table_names”

    云数据库默认值“1”

    作用:该参数表示创建数据库及表时,表存储是否大小写敏感。默认值“1”,表示创建数据库及表时,默认小写,不区分大小写。

    RDS for MySQL 8.0版本不支持该参数。

    影响:修改该参数可能会导致主从复制异常,请谨慎修改。如果必须要修改,请根据以下场景设置数据库参数:

    • 参数值从1变为0的设置顺序:先修改和重启只读库,后修改和重启主库。
    • 参数值从0变为1的设置顺序:先修改和重启主库,在主库执行SELECT @@GLOBAL.GTID_EXECUTED。然后在只读库执行SELECT @@GLOBAL.GTID_EXECUTED,直到结果集合大于或者等于主库的SELECT @@GLOBAL.GTID_EXECUTED的结果集合,再修改和重启只读库。
  • “innodb_flush_log_at_trx_commit”

    云数据库默认值:“1”

    作用:该参数控制提交操作在严格遵守ACID合规性和高性能之间的平衡。设置为默认值“1”,是为了保证完整的ACID,每次提交事务时,把事务日志从缓存区写到日志文件中,并刷新日志文件的数据到磁盘上;当设为“0”时,每秒把事务日志缓存区的数据写入日志文件,并刷新到磁盘;如果设为“2”,每次提交事务都会把事务日志从缓存区写入日志文件,每隔一秒左右会刷新到磁盘。

    影响:参数设置为非默认值“1”时,降低了数据安全性,在系统崩溃的情况下,可能导致数据丢失。

    POC建议值“2”

  • “sync_binlog”

    云数据库默认值“1”

    作用:该参数控制MySQL服务器将二进制日志同步到磁盘的频率。设置为默认值“1”,表示MySQL每次事务提交,binlog同步写入磁盘,是最安全的设置;设置为“0”时,表示MySQL不控制binlog的刷新,由文件系统自己控制其缓存的刷新。此时的性能最好,但风险最大,因为一旦断电或操作系统崩溃,在“binlog_cache”中的所有binlog信息都会被丢失。

    影响:参数设置为非默认值“1”时,降低了数据安全性,在系统崩溃的情况下,可能导致binlog丢失。

    POC建议值“1000”

  • “innodb_large_prefix”

    云数据库默认值“OFF”

    作用:InnoDB表允许单列索引的最大长度。

    仅RDS for MySQL 5.6版本支持该参数。

    影响:在DDL执行时修改该参数,有可能会导致主从复制异常,请谨慎修改。如果必须要修改,请根据以下场景设置数据库参数:

    • 参数值从OFF变为ON的设置顺序:先修改只读库,后修改主库。
    • 参数值从ON变为OFF的设置顺序:先修改主库,后修改只读库。
  • “innodb_buffer_pool_size”

    云数据库默认值“规格参数,不同实例规格默认值也不同”

    作用:该参数为Innodb缓冲池大小,用来缓存表和索引数据的内存区域,增加该值可减少磁盘I/O。

    影响:过大的buffer pool可能导致系统崩溃,请谨慎修改。

    POC建议值:32G及以上规格可将其调整至内存的70%~75%。

修改性能参数

若干参数相关说明如下:

  • “innodb_spin_wait_delay”“query_alloc_block_size”依赖于实例的规格,设置过大时,可能会影响数据库的使用。
  • “max_connections”参数值设置较小,将影响数据库访问。
  • “innodb_buffer_pool_size”“max_connections”“back_log”参数依赖于实例的规格,实例规格不同对应其默认值也不同。因此,这些参数在用户未设置前显示为“default”
  • “innodb_io_capacity_max”“innodb_io_capacity”参数依赖于磁盘类型,用户未设置前显示为“default”

联动参数

  • “character_set_server”:修改该参数的值, 系统会联动调整“collation_server”“character_set_database”,和“collation_database”的取值。

    其中,字符序“character_set_server”跟字符集“collation_server”存在对应关系,比如针对MySQL 5.7而言,“character_set_server”“latin1”时,对应的“collation_server”默认值为“latin1_swedish_ci”,此时“collation_server”的取值区间为以“latin1”开头的字符序。

  • “innodb_io_capacity”:该参数的取值必须小于等于“innodb_io_capacity_max”的取值。 比如“innodb_io_capacity_max”“2000”, 则“innodb_io_capacity”最大设置为“2000”
  • “innodb_buffer_pool_size”: 该参数受“innodb_buffer_pool_chunk_size ”* “innodb_buffer_pool_instances”的影响,为两参数乘积的整数倍向上取值。 比如“innodb_buffer_pool_chunk_size”“134217728”“ innodb_buffer_pool_instances”“1”,那“innodb_buffer_pool_size”必须大于等于“134217728”

参数修改限制

  • “innodb_adaptive_hash_index”“innodb_buffer_pool_size”参数同时修改时,“innodb_adaptive_hash_index”的值由“OFF”改为“ON”会失败。
  • “innodb_buffer_pool_size”参数值必须是“innodb_buffer_pool_instances”“innodb_buffer_pool_chunk_size”参数值乘积的整数倍。
  • “innodb_buffer_pool_instances”参数值设置为“2”时,“innodb_buffer_pool_size”值必须大于等于1GB。
  • “max_prepared_stmt_count”:对于MySQL 8.0版本,如果内核版本低于8.0.18,参数取值上限为1048576,超过会修改失败。

其他参数

  • “max_prepared_stmt_count”:准备大量的语句会消耗服务器的内存资源,参数设置较小,会带来潜在的“拒绝服务”的风险,建议您根据业务情况,调整该参数的值。
  • 如下参数的输入会根据内核规则对取值进行对应的调整。调整的规则如下所示:
    • “key_cache_age_threshold”会自动调整为100的倍数。
    • “join_buffer_size”“key_cache_block_size”会自动调整为128的倍数。
    • “query_cache_size”“query_prealloc_size”“innodb_log_buffer_size”“max_allowed_packet ”“thread_stack ”会自动调整为1024的倍数。
    • “read_buffer_size”“read_rnd_buffer_size”“binlog_cache_size ”“binlog_stmt_cache_size ”会自动调整为4096的倍数。
    • “data_buffer_size”“log_buffer_size”“shared_pool_size”“temp_buffer_size ”会自动调整为1048576的倍数。
  • “binlog_format”默认设置为row,表示将binlog记录成每一行数据被修改的形式,包括修改前和修改后的数据。建议不要对该参数进行修改,否则可能影响您的正常使用。
  • “log_timestamps”控制错误日志消息的时间戳时区,以及写入文件的一般查询日志消息和慢日志消息的时间戳时区。默认设置为系统时区,且无法修改。
  • “skip_name_resolve”:默认值为“ON”,表示跳过域名解析从白名单的IP中判断是否可以建立连接。
  • “innodb_strict_mode”:用于限制InnoDB的检查策略,默认值为“OFF”。
  • “binlog_rows_query_log_events”:该参数控制原始SQL是否记录到Binlog。开启时在特定大量数据更新等场景可能存在一定的性能劣化,修改该值应该考虑Otter等工具的兼容性。