Suggestions on RDS for MySQL Parameter Tuning

lower_case_table_names

• Supported versions: 5.7 and 5.6
• Function: It controls whether table names are case-sensitive when databases and tables are created.
• Reboot required: Yes
• Default value: 1
• Configuration suggestions: Changing its value may cause primary/standby replication exceptions. If you do need to change the value, set the parameter as follows:
  • The default value 1 indicates that table names are case-insensitive and are lowercase by default.
  • If you want to change this parameter value from 1 to 0, change it on read replicas and reboot them first, and then repeat the operations on the primary DB instance.
  • If you want to change this parameter value from 0 to 1, change it on the primary DB instance, reboot and run SELECT @@GLOBAL.GTID_EXECUTED on the primary instance first. Then run SELECT @@GLOBAL.GTID_EXECUTED on read replicas. Wait until the result set is at least the same as the primary DB instance and then change this parameter value on read replicas and reboot them.

innodb_flush_log_at_trx_commit

• Supported versions: 8.0, 5.7, and 5.6
• Function: It controls the balance between strict ACID compliance for commit operations and higher performance.
• Reboot required: No
• Default value: 1
• Configuration suggestions: If this parameter value is not 1, data security is not guaranteed. Once the system fails, data may be lost.
  • If it is set to the default value 1, InnoDB writes transaction logs from the buffer to the log file and flushes the log file data to the disk at each transaction commit. This setting helps to keep the database ACID-compliant.
  • If it is set to 0, InnoDB writes transaction logs in the buffer to the log file and flushes the log file data to the disk once per second.
  • If it is set to 2, InnoDB writes transaction logs from the buffer to the log file at each transaction commit and flushes the log file data to the disk once per second.

sync_binlog

• Supported versions: 8.0, 5.7, and 5.6
• Function: It controls how often the MySQL server flushes binary logs to the disk.
• Reboot required: No
• Default value: 1
• Configuration suggestions: The default value 1 indicates that the MySQL server synchronizes binlogs to the disk before transactions are committed. This is the safest setting.

  If this parameter is set 0, the MySQL server does not flush binlogs. Instead, the file system flushes binlog data from the cache to the disk. This setting provides the best performance, but in the event of a power failure or OS crash, all binlog data in the binlog_cache will be lost.

innodb_large_prefix

• Supported version: 5.6
• Function: It specifies the maximum length of a single-column index in an InnoDB table.
• Reboot required: No
• Default value: OFF
• Configuration suggestions: Changing this parameter value during DDL execution may cause primary/standby replication exceptions. If you do need to change the value, set the parameter as follows:
  • If you want to change this parameter value from OFF to ON, change it on read replicas first and then on the primary DB instance.
  • If you want to change it from ON to OFF, change it on the primary DB instance first and then on read replicas.

innodb_spin_wait_delay

• Supported versions: 8.0, 5.7, and 5.6
• Function: It specifies the maximum delay between polls for a spin lock.
• Reboot required: No
• There is no default value.
• Configuration suggestions: Its value is determined by the instance specifications. Setting it to a very large value may impact database performance.

query_alloc_block_size

• Supported versions: 8.0, 5.7, and 5.6
• Function: It specifies the size of the blocks allocated during query parsing and execution.
• Reboot required: No
• There is no default value.
• Configuration suggestions: Its value is determined by the instance specifications. Setting it to a very large value may impact database performance.

character_set_server

• Supported versions: 8.0, 5.7, and 5.6
• Function: It specifies the server character set.
• Reboot required: No
• There is no default value.
• Configuration suggestions: If you change the value of this parameter, the system changes the values of collation_server, character_set_database, and collation_database accordingly.

  The parameters character_set_server and collation_server are correlated with each other. The value of collation_server starts with the value of character_set_server. For example, if character_set_server is set to latin1, the value of collation_server starts with latin1.

max_prepared_stmt_count

• Supported versions: 8.0, 5.7, and 5.6
• Function: It limits the total number of prepared statements. Too many prepared statements consume server memory resources. If this parameter is set to a small value, your DB instance may be vulnerable to denial of service (DoS) attacks. Adjust this parameter value as necessary.
• Reboot required: No
• There is no default value.
• Configuration suggestions: In MySQL 8.0, if the kernel version is earlier than 8.0.18, the value of this parameter cannot exceed 1048576.

innodb_strict_mode

• Supported versions: 8.0, 5.7, and 5.6
• Function: It restricts the InnoDB check policy.
• Reboot required: No
• Default value: OFF
• Configuration suggestions: Set this parameter to ON when page data compression is used.

binlog_rows_query_log_events

• Supported versions: 8.0, 5.7, and 5.6
• Function: It controls whether to write original SQL statements into binlogs.
• Reboot required: No
• Default value: OFF
• Configuration suggestions: If this parameter is set to ON, database performance may deteriorate when a large amount of data is updated. Before changing the parameter value, consider its compatibility with tools such as Otter.

innodb_print_all_deadlocks

• Supported versions: 8.0, 5.7, and 5.6
• Function: When this parameter is enabled, information about all deadlocks in InnoDB user transactions is recorded in the mysqld error log.
• Reboot required: No
• Default value: OFF
• Configuration suggestions: Setting this parameter to its default value can avoid the performance overhead caused by frequent log writes. If it is set to ON and there are a large number of deadlocks in the system, frequent log writes will increase I/O overhead. If information about all deadlocks is recorded, there will be more and more error logs. For example, if deadlocks frequently occur in high-concurrency scenarios, the storage space is quickly consumed. You can set this parameter to ON temporarily when you want to troubleshoot and analyze deadlocks.

Parameters Related to Kernel Rules

The values of the following parameters will be adjusted based on kernel rules:

• key_cache_age_threshold: changed to a multiple of 100
• join_buffer_size and key_cache_block_size: changed to multiples of 128
• query_prealloc_size, max_allowed_packet, and thread_stack: changed to multiples of 1024
• read_buffer_size, read_rnd_buffer_size, binlog_cache_size, and binlog_stmt_cache_size: changed to multiples of 4096