This section only describes some important parameters. For more parameter details, visit the MySQL official website. For details on how to modify MySQL parameter values on the console,see Modifying the Parameter Group.
Modifying Sensitive Parameters
Some parameters are described as follows:
• lower_case_table_names RDS default value: 1 Function: Allows MySQL to set whether the case is sensitive or not. The default value is 1, indicating that when creating a database or table, storage on the disk is lowercase, and the comparison is case-insensitive. At present, RDS for MySQL only supports lower_case_table_names=1 (case-insensitive). The case-sensitive function is expected to be launched in the future. Please keep an eye out for product dynamics.
• innodb_flush_log_at_trx_commit RDS default value: 1 Function: Controls the balance between security and high performance in the submission operation. If this parameter is set to the default value of 1, the data of the log buffer is written to the log file, and the log file is written to disk each time the transaction is committed. If this parameter is set to 0, the log buffer is written to the log file every second, and the log file is written to the disk (In this mode, when the transaction is committed, the operation of writing to the disk is not actively triggered). If this parameter is set to 2, the data of the log buffer is written to the log file each time the transaction is committed, and the log file is written to disk approximately every second, which is asynchronous with the log buffer write. Affect: When this parameter is not set to the default of 1, data security is reduced. If the system fails, data may be lost. If this parameter is set to 0, the write speed is fast but the data is not safe. When the MySQLd process crashes, the operation data for all transactions of the previous second will be lost. If this parameter is set to 1, the write speed is the slowest but the data is safe. Even if the MySQLd process crashes or the service host shuts down, the log may lose at most one statement or the operation data of one transaction. If this parameter is set to 2, the write speed is faster and the data is safer than that if this parameter is set to 0. Only when the service host shuts down, the operation data for all transactions of the previous second will be lost. Recommended value for POC: 2
• sync_binlog RDS default value: 1 Function: Controls how often the binary log of the MySQL is synchronized to disk. The value range is 0 to N. If this parameter is set to the default value 1, MySQL performs a disk synchronization command such as fsync to force to write the data in binlog_cache to the disk. This is the safest setting. If this parameter is set to 0, MySQL does not perform a disk synchronization command such as fsync to refresh the information in binlog_cache to the disk after the transaction is committed. Instead, Filesystem decides when to synchronize, or the data is synchronized to the disk after the cache is full. This setting provides the best performance, but the risk is also the greatest, because all binlog information in binlog_cache will be lost in the event of a power fault or operating system crash. Affect: When this parameter is not set to the default of 1, data security is reduced. If the system fails, binlog may be lost. Recommended value for POC: 1000.
• innodb_buffer_pool_size RDS default value: Varies depending on the specifications of the enabled instance. Function: Specifies the maximum memory buffer size for the table data and index data in the InnoDB database engine. The read/write speed of data in the memory is multiple times that of the read/write speed in the disk. Increasing the value of this parameter reduces disk I/O. Affect: Too large a buffer pool may cause a system OOM (Out-of-Memory) crash. Exercise caution when changing this value. Recommended value for POC: 70% to 75% of the memory for your instance with 32 GB or above.
Modifying Performance Parameters
Some parameters are described as follows:
• The parameter max_connections indicates the maximum number of connections allowed for the database. If this parameter value is set to a small value, the database connection access may be affected.
• The parameters innodb_buffer_pool_size, max_connections, and back_log depend on the instance specification. Their default values vary depending on the instance specifications.
Associated Parameters
• character_set_server: If you modify the value of this parameter, the system automatically changes the value of collation_server, character_set_database, and collation_database accordingly. Generally, if character_set_server is utf8, collation_server is utf8_*_ci. They are correlated with each other.
• innodb_buffer_pool_size: This parameter is affected by innodb_buffer_pool_chunk_size * innodb_buffer_pool_instances. The final valid value is an integer multiple of the product of the two parameters. For example, if the innodb_buffer_pool_chunk_size is 128 MB and the innodb_buffer_pool_instances is 8, when the innodb_buffer_pool_size is 4.5 GB, the system automatically changes the size to 5 GB (must be greater than or equal to an integer multiple of 128*8MB (1 GB)).
Constraints on Parameter Modification
• The value of innodb_buffer_pool_size must be an integer multiple of the product of the values of innodb_buffer_pool_instances and innodb_buffer_pool_chunk_size. The non-integer multiple is automatically adjusted upward to an integer multiple.
• When the value of innodb_buffer_pool_instances is set to greater than 1, the value of innodb_buffer_pool_size must be greater than or equal to 1 GB.
• max_prepared_stmt_count: For MySQL V5.7, the upper limit value is 1048576. For MySQL V8.0, if the kernel version is greater than or equal to 8.0.18, the upper limit value of the parameter is 4194304. If the changed value exceeds this limit, it is automatically truncated to the maximum value.
Other Parameters
• max_prepared_stmt_count: Preparing too many statements consumes the memory resources of the server. If this parameter is set to a small value, a large number of PREPARE statements will be generated in the service, which may exceed the limit of the max_prepared_stmt_count parameter. In this case, an error will be reported. You are advised to modify the value of this parameter based on service requirements.
• The values of the following parameters will be adjusted based on the MySQL kernel rules. The adjustment rules are as follows:
• (a) key_cache_age_threshold: automatically adjusted to a multiple of 100. When the value of this parameter is set to an integer multiple other than 100, it is automatically adjusted down to an integer multiple of 100. (b) join_buffer_size and key_cache_block_size: automatically adjusted to multiples of 128. (c) query_cache_size, query_prealloc_size, innodb_log_buffer_size, max_allowed_packet, and thread_stack: automatically adjusted to multiples of 1024. (d) read_buffer_size, read_rnd_buffer_size, binlog_cache_size, and binlog_stmt_cache_size: automatically adjusted to multiples of 4096. (e) data_buffer_size, log_buffer_size, shared_pool_size, and temp_buffer_size: automatically adjusted to multiples of 1048576.
• binlog_format: The default value is set to row. The binlog is recorded for each column that was modified, including the data before and after the modification. This parameter restricts modifications. Otherwise, your use may be affected.
• binlog_rows_query_log_events: The default value is set to ON. When binlog records events, it also records the original SQL statements, which is convenient for future queries and audits while ensuring the compatibility of some synchronization tools such as ETL. However, when there is a large number of updates, it may have a certain impact on performance.
• binlog_row_image: The default value is set to FULL. The binlog records all front and rear mirrors with or without primary key constraints or unique constraints.
• log_timestamps: Controls the timestamp time zone for error log messages as well as the timestamp time zone for general query log messages and slow log messages written to files. The default value is set to the system time zone and cannot be modified.
• default_time_zone: The default value is set to +8:00, which can be customized according to your service rules.
• skip_name_resolve: The default value is ON, which indicates skipping domain name resolution to determine whether a connection can be established from the trustlist IP.
• innodb_strict_mode: Restricts the checking policy for InnoDB. The default value is ON. It is not recommended to change this value to OFF and skip restricting checks.
• transaction_isolation: The default value is set to READ-COMMITTED, which can be changed according to your service scenarios.
• innodb_adaptive_hash_index: The default value is set to OFF, which can be modified according to your service requirements.
• The default values of table_definition_cache and table_open_cache are 2000, respectively, which can be changed based on the expected data tables on the table and the maximum number of concurrent connections.
• gtid_mode: The default value is set to ON, which is duplicated in GTID mode. Modification is not recommended as it will affect high-availability components.
• tmp_table_size: The default value is set to 16 MB. The complexity scenarios can be queried based on service SQL. For example, when complex order by and group by parameters are used for query, you may need to use many disk temporary tables, resulting in query performance degradation. Appropriately increasing this value can reduce the created disk temporary tables and reduce IO.
• max_heap_table_size: The default value is set to 64 MB. This parameter sets the maximum size of the memory table that the user can create. The value of the variable calculates the value of the memory table MAX_ROWS.