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_buffer_pool_size

• Supported versions: 8.0, 5.7, and 5.6
• Function: It specifies the size of the InnoDB buffer pool. The InnoDB buffer pool is used to cache table and index data. Increasing the value of this parameter reduces disk I/O.
• Reboot required: No
• Default value: Its value is determined by the DB instance specifications.
• Configuration suggestions: Setting this parameter to a large value may cause system breakdown. Exercise caution when changing this parameter value. Its value must be an integer multiple of the product of the parameters innodb_buffer_pool_chunk_size and innodb_buffer_pool_instances. For example, if innodb_buffer_pool_chunk_size is 134217728 and innodb_buffer_pool_instances is 1, the value of innodb_buffer_pool_size must be a multiple of 134217728.
  • If innodb_adaptive_hash_index and innodb_buffer_pool_size are modified at the same time, changing the value of innodb_adaptive_hash_index from OFF to ON will fail.
  • If innodb_buffer_pool_instances is set to 2, the value of innodb_buffer_pool_size must be greater than or equal to 1 (unit: GB).

innodb_buffer_pool_instances

• Supported versions: 8.0, 5.7, and 5.6
• Function:
  • In InnoDB, data is accessed by page. The default size of each page is 16 KB. The buffer pool is used to manage and cache these pages. InnoDB allocates a continuous memory block to the buffer pool and divides it into multiple buffer pool instances to better manage the memory. Each instance has the same size. An algorithm is used to ensure that a page is in only one specific instance. Using buffer pool instances improves the concurrent performance of the buffer pool.
  • Each buffer pool instance maintains its own buffer pool module. InnoDB reads data from files into the buffer pool in 16 KB pages and caches these pages in a Least Recently Used (LRU) list. Pages that are frequently accessed are placed at the front of the LRU list, and pages that are not frequently accessed are placed at the end. When accessing a page, InnoDB first obtains the page from the buffer pool. If the page is not obtained, InnoDB accesses the data file, reads the page, and puts it to the LRU list. When there are no available idle pages in the buffer pool of an instance, pages in the LRU list are evicted.
  • Each buffer pool instance has its own locks, including mutex, buffer chunks, and page linked lists. Each instance is independent and supports multi-thread concurrent access. A page can be stored only in a specific instance.

    Each buffer pool instance contains a page_hash (hash table). The page_hash can be used to quickly find pages in the LRU list, avoiding scanning the entire LRU list and greatly improving the page access efficiency.

• Reboot required: Yes
• Default value: The default value of innodb_buffer_pool_instances is related to the memory specifications of an instance. Changing this parameter value will affect the concurrency performance of the buffer pool.

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.

max_connections

• Supported versions: 8.0, 5.7, and 5.6
• Function: It specifies the maximum number of concurrent connections to a DB instance.
• Reboot required: No
• Default value: Its value is determined by the DB instance specifications.
• Configuration suggestions: Part of this limit is reserved for system built-in connections. To prevent concurrent connection conflicts, do not set this parameter to a value less than 30. This parameter cannot be set to a value smaller than the number of current connections.

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.

innodb_io_capacity

• Supported versions: 8.0, 5.7, and 5.6
• Function: It specifies the maximum number of I/O operations InnoDB can perform per second.
• Reboot required: No
• Default value: It depends on the disk type.
• Configuration suggestions: Its value must be less than or equal to the value of innodb_io_capacity_max. For example, if innodb_io_capacity_max is set to 2000, the maximum value of innodb_io_capacity is 2000.

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.

back_log

• Supported versions: 8.0, 5.7, and 5.6
• Function: The back_log value indicates how many requests can be stacked during the interval before MySQL temporarily stops responding to new requests.
• Reboot required: Yes
• Default value: Its value is determined by the DB instance specifications.
• Configuration suggestions: None

Parameters Related to Kernel Rules