Suggestions on optimizing the parameters of an ApsaraDB RDS for MySQL instance - ApsaraDB RDS

You can modify the parameter values of an ApsaraDB RDS for MySQL instance in the ApsaraDB RDS console. Improper values of key parameters may affect the performance of your RDS instance or cause errors in your application. This topic provides optimization suggestions for key parameters.

Note

You can view the default parameter values in the ApsaraDB RDS console.

back_log

Supported MySQL versions: 8.0, 5.7, 5.6, and 5.5.
Whether to restart the instance after parameter modification: yes.
Functionality: The primary MySQL thread creates a thread for each connection request that it processes. If a frontend application initiates a large number of short-lived connections when the primary thread creates a thread, ApsaraDB RDS for MySQL controls the short-lived connection requests to enter the queue based on the back_log parameter. If the number of waiting connection requests in the queue exceeds the value of the back_log parameter, ApsaraDB RDS for MySQL denies new connection requests. If you want ApsaraDB RDS for MySQL to process a large number of short-lived connections, increase the value of this parameter.
Symptom: If the value of this parameter is small, the application may encounter the following error:
```
SQLSTATE[HY000] [2002] Connection timed out;
```
Suggestion: Set this parameter to 3000.

rpl_semi_sync_master_timeout

Supported MySQL versions: 8.0, 5.7, and 5.6.
Whether to restart the instance after parameter modification: no.
Functionality: An RDS instance uses the semi-synchronous replication mode. Before a transaction is committed from the primary RDS instance, you must wait until the secondary RDS instance receives all binary logs of the transaction. If the waiting period exceeds the value of this parameter, a timeout error is reported, and the replication mode of the RDS instance is degraded to asynchronous replication. In this case, if the secondary RDS instance receives all binary logs of the primary RDS instance, the replication mode of the RDS instance is automatically changed to semi-asynchronous replication.
Suggestion: Set this parameter to 1000. Unit: milliseconds. The value 1000 equals 1 second. If your RDS instance requires high data reliability, you can increase the value of this parameter to prevent the degradation from semi-synchronous replication. However, if you set this parameter to a large value, the RDS instance may fail to process write requests for a long period of time during the execution of large transactions. As a result, the high availability (HA) component of the system detects an instance failure, and an instance switchover is triggered.

innodb_autoinc_lock_mode

Supported MySQL versions: 8.0, 5.7, 5.6, and 5.5.
Whether to restart the instance after parameter modification: yes.
Functionality: In MySQL 5.1.22 and later, the innodb_autoinc_lock_mode parameter is used in InnoDB to manage auto-increment locks for auto-increment primary keys. Valid values: 0, 1, and 2.
- The value 0 indicates the traditional mode. In this mode, the SQL statement holds an auto-increment lock before the auto-increment value is obtained, and the SQL statement releases the auto-increment lock at the end of statement execution. This mode significantly affects the data insertion concurrency.
- The value 1 indicates the consecutive mode. In this mode, the SQL statement holds an auto-increment lock before the auto-increment value is obtained. If an SQL statement is executed to insert a fixed number of data rows, the SQL statement releases the auto-increment lock immediately after the auto-increment value is obtained. If an SQL statement is executed to insert an uncertain number of data rows, the SQL statement releases the auto-increment lock at the end of statement execution.
- The value 2 indicates the interleaved mode. In this mode, the SQL statement holds an auto-increment lock before the auto-increment value is obtained. After the auto-increment value is obtained, the SQL statement immediately releases the auto-increment lock regardless of whether a fixed number of data rows are inserted.
Suggestion: Set this parameter to 2. The value 2 indicates that all SQL statements that are used to insert data use lightweight locks. This prevents the AUTO-INC deadlock and improves data insertion performance. In ApsaraDB RDS for MySQL, the format of binary logs is row. If you set this parameter to 2, no data inconsistency errors occur.
Note
If you set the parameter to 2, you must set the format of binary logs to row.

query_cache_size

Supported MySQL versions: 5.7, 5.6, and 5.5.
Whether to restart the instance after parameter modification: no.
Functionality: This parameter manages the memory capacity of the MySQL query cache. If you enable the MySQL query cache, the system locks the query cache before it performs a query and then checks for the query result in the cache. If the query result exists in the query cache, the system directly returns the result. Otherwise, the system performs the query to obtain the result. Operations, such as INSERT, UPDATE, and DELETE, can cause the query cache to become invalid. Changes in the syntax or indexes can also cause the query cache to become invalid. Frequent invalidation of the query cache causes pressure on the RDS instance. If the data on the RDS instance is not frequently updated, the query cache can greatly improve query efficiency. However, if the RDS instance processes a large number of write operations on a few tables, the lock mechanism of the query cache may cause frequent lock conflicts. The write and read requests on the locked table wait for the query cache to be unlocked. This reduces the query efficiency of SELECT statements.
Symptom: A large number of database connections are in the following states: checking query cache for query, waiting for query cache lock, and storing result in query cache.
Suggestion: By default, ApsaraDB RDS disables the query cache. If you enabled the query cache and encountered the preceding symptom, disable the query cache.

net_write_timeout

Supported MySQL versions: 8.0, 5.7, 5.6, and 5.5.
Whether to restart the instance after parameter modification: no.
Functionality: This parameter sets the timeout period that ApsaraDB RDS waits before it sends a block to a client.

Symptom: If the parameter value is small, the client may encounter the following error:

"the last packet successfully received from the server was milliseconds ago" or "the last packet sent successfully to the server was milliseconds ago"

Suggestion: The default value of this parameter is 60. If the value is small, the client may be frequently disconnected from the RDS instance when the network is not stable or a long period of time is required for the client to process each block. We recommend that you increase the value of this parameter.

tmp_table_size

Supported MySQL versions: 8.0, 5.7, 5.6, and 5.5.
Whether to restart the instance after parameter modification: no.
Functionality: This parameter specifies the maximum size of an internal in-memory temporary table. The memory specified by this parameter is assigned to each thread. The actual maximum size of an internal in-memory temporary table is specified by the value of tmp_table_size or max_heap_table_size, whichever is smaller. If the size of the in-memory temporary table exceeds the maximum size, ApsaraDB RDS for MySQL automatically converts the table to an on-disk MyISAM table. When you optimize query statements, do not use temporary tables. If you have to use a temporary table, make sure that the temporary table is stored in the memory.
Symptom: If you use a temporary table for complicated SQL statements that contain GROUP BY or DISTINCT clauses and cannot be optimized by using indexing, a longer period of time is required to execute SQL statements.
Suggestion: If the SQL statements contain a large number of GROUP BY or DISTINCT clauses and your RDS instance has sufficient memory, increase the values of the tmp_table_size and max_heap_table_size parameters to improve query performance.

loose_rds_max_tmp_disk_space

Supported MySQL versions: 5.6 and 5.5.
Whether to restart the instance after parameter modification: no.
Functionality: This parameter manages the size of temporary files on the RDS instance.
Symptom: If the size of temporary files exceeds the value of the loose_rds_max_tmp_disk_space parameter, the application may encounter the following error:
```
The table '/home/mysql/dataxxx/tmp/#sql_2db3_1' is full
```
Suggestion: Check whether you can optimize the SQL statements that cause an increase in the size of temporary files by using indexing or other methods. If your instance has sufficient space, increase the value of this parameter to ensure the normal execution of SQL statements.

loose_tokudb_buffer_pool_ratio

Supported MySQL version: 5.6.
Whether to restart the instance after parameter modification: yes.
Functionality: This parameter specifies the size of buffer memory that can be used by TokuDB tables. For example, if the innodb_buffer_pool_size parameter is set to 1000 MB and the tokudb_buffer_pool_ratio parameter to 50, which indicates 50%, the size of buffer memory that can be used by TokuDB tables is 500 MB.
Suggestion: If the TokuDB engine is used on the RDS instance, increase the value of this parameter to improve the access performance of TokuDB tables.

loose_max_statement_time

Supported MySQL version: 5.6.
Whether to restart the instance after parameter modification: no.
Functionality: This parameter manages the maximum query duration on the RDS instance. By default, the query duration is not limited. If this parameter is configured and the query duration exceeds the specified limit, the query fails.
Symptom: If the query duration exceeds the value of this parameter, the following error occurs:
```
ERROR 3006 (HY000): Query execution was interrupted, max_statement_time exceeded
```
Note
After the parameter is modified, the modification takes effect only for new connections. Existing connections must be reconnected for the modification to take effect.
Suggestion: If you want to manage the execution duration of SQL statements, specify a value for this parameter. Unit: milliseconds.

loose_rds_threads_running_high_watermark

Supported MySQL versions: 5.6 and 5.5.
Whether to restart the instance after parameter modification: no.
Functionality: This parameter manages the number of concurrent queries. For example, if you set the rds_threads_running_high_watermark parameter to 100, a total of 100 MySQL queries can be concurrently executed. Additional queries are denied.
Suggestion: You can use this parameter to handle burst requests and requests during peak hours to protect the RDS instance.

innodb_buffer_pool_instances

Supported MySQL versions: 8.0, 5.7, and 5.6.
Whether to restart the instance after parameter modification: yes.
Functionality: This parameter divides the InnoDB buffer pool into a specific number of instances when the size of the InnoDB buffer pool specified by the innodb_buffer_pool_size parameter is greater than 1 GB. Each instance has its own locks, semaphores, buffer chunks, and logically linked lists. As a result, no contention exists among instances, and concurrent reads and writes are supported. If the size of the buffer pool for an RDS instance is large, this parameter divides the buffer pool into a specific number of instances. This reduces contention for resources that are used by different threads to read and write data and improves concurrency performance.
Suggestion: Set this parameter to {LEAST(DBInstanceClassMemory/1073741824, 8)}.

table_open_cache_instances

Supported MySQL versions: 8.0, 5.7, and 5.6.
Whether to restart the instance after parameter modification: yes.
Functionality: This parameter partitions the open table cache into several cache instances of a specific size that is calculated by using the following formula: table_open_cache/table_open_cache_instances to reduce table cache contention among sessions.
Suggestion: Set this parameter to 16.

table_open_cache

Supported MySQL versions: 8.0, 5.7, and 5.6.
Whether to restart the instance after parameter modification: no.
Functionality: This parameter specifies the maximum number of tables that the server can keep open in a table cache instance. If the value of this parameter is too small, SQL query performance may be affected in high-concurrency scenarios. A large value may cause high memory consumption. When you increase the value of this parameter, you must take note of the memory usage of your RDS instance.
Suggestion: Set this parameter to {LEAST(DBInstanceClassMemory/1073741824*512, 8192)}.

innodb_adaptive_hash_index

Supported MySQL versions: 8.0, 5.7, and 5.6.
Whether to restart the instance after parameter modification: no.
Functionality: This parameter determines whether to disable or enable the adaptive hash index feature. Adaptive hash indexes help you quickly locate the leaf node in a database based on your query conditions and reduce the number of I/O operations.
Symptom: If this parameter is set to ON, the impact on query performance varies based on SQL statements that you execute. Some statements may cause the maintenance of adaptive hash indexes. For example, adaptive hash indexes are cleared from the memory when DDL statements are executed on tables. As a result, the execution of SQL statements is blocked or the query performance is degraded.
Suggestion: Set this parameter to OFF. For more information about how to modify this parameter, see Best practices on adaptive hash indexes of ApsaraDB RDS for MySQL.

open_files_limit

Supported MySQL versions: 8.0, 5.7, and 5.6.
Whether to restart the instance after parameter modification: yes.
Functionality: This parameter specifies the number of file handles that can be simultaneously opened by the RDS instance. It also affects the configuration of the innodb_open_files parameter.
Suggestion: Set this parameter to 655350. If the RDS instance has large specifications that are greater than or equal to 32 CPU cores and has a large number of active sessions or tables, you can increase the value of this parameter based on the actual situation. If this parameter is set to a value that is greater than the number of instance files, the running of the instance is not affected.

loose_innodb_rds_faster_ddl

Supported MySQL versions: 8.0, 5.7, and 5.6. Supported minor engine versions: 20200630 and later.
Whether to restart the instance after parameter modification: no.
Functionality: This parameter is used to accelerate some DDL operations and reduces the impact of DDL operations on the performance of your RDS instance.
Suggestion: Enable this parameter to reduce the impact of DDL operations on your workloads. After you enable this parameter, the buffer pool management mechanism that is developed by the ApsaraDB RDS team is used. The mechanism accelerates some DDL operations and reduces the impact of DDL operations on your workloads.

innodb_thread_concurrency

Supported MySQL versions: 8.0, 5.7, and 5.6.
Whether to restart the instance after parameter modification: no.
Functionality: This parameter specifies the maximum number of threads allowed in InnoDB. A value of 0 indicates that no limits are imposed on the number of concurrent threads. If performance issues are caused by high concurrency on your RDS instance, you can modify this parameter to control the concurrency in InnoDB.
This parameter is used for performance tuning in high-concurrency systems.
Suggestion: If you do not encounter high-concurrency performance issues, we recommend that you set this value to 0. The value zero indicates that the concurrency is unlimited.

binlog_transaction_dependency_history_size

Supported MySQL versions: MySQL 8.0 for RDS instances that run a minor engine version of 20210930 or later, and MySQL 5.7 for RDS instances that run a minor engine version of 20211231 or later.
Whether to restart the instance after parameter modification: no.
Functionality: After you enable the WRITESET feature, the transaction information of the last modified row is saved in the memory after hashing. This parameter specifies the maximum number of hashed entries that can be saved in the memory. After the upper limit is reached, all information is cleared.
Symptom: If you set this parameter to an excessively small value, the concurrency of parallel playback in the secondary RDS instance is affected. As a result, the replication latency occurs between the primary and secondary RDS instances.
Suggestion: Set this parameter to 500000. For more information about how to modify this parameter, see Modify WRITESET-related parameters.

binlog_transaction_dependency_tracking

Supported MySQL versions: MySQL 8.0 for RDS instances that run a minor engine version of 20210930 or later, and MySQL 5.7 for RDS instances that run a minor engine version of 20211231 or later.
Whether to restart the instance after parameter modification: no.
Functionality: This parameter specifies how MySQL manages parallel replication. If you set this parameter to WRITESET, row-level conflicts between transactions can be detected. This way, faster parallel playback can be achieved in the secondary RDS instance.
Suggestion: Set this parameter to WRITESET. For more information about how to modify this parameter, see Modify WRITESET-related parameters.

innodb_max_dirty_pages_pct_lwm

Supported MySQL versions: 8.0, 5.7, and 5.6.
Whether to restart the instance after parameter modification: no.
Functionality: If the proportion of dirty pages in a buffer pool is greater than the value of this parameter, preflushing is enabled to control the proportion of dirty pages. The value 0 indicates that preflushing is disabled. The value of this parameter must be less than the value of the innodb_max_dirty_pages_pct parameter.
Suggestion: Set this parameter to 10.

eq_range_index_dive_limit

Supported MySQL versions: 8.0, 5.7, and 5.6.
Whether to restart the instance after parameter modification: no.
Functionality: This parameter specifies the maximum number of equivalent ranges. If the number of equivalent ranges of equivalent conditions in a query is greater than or equal to the value of this parameter, the optimizer uses the statistics to calculate the execution plan. If the number of equivalent ranges of equivalent conditions in the query is less than the value of this parameter, the optimizer uses the index dive method to sample data, obtain statistics, and then use the statistics to calculate the execution plan.
- Example:
```
col_name IN(val1, ..., valN)
col_name = val1 OR ... OR col_name = valN
```
  The number of N equivalent ranges are contained.
- For more information about this parameter, see https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_eq_range_index_dive_limit.
Suggestion: Set this parameter to 10 for RDS instances that run MySQL 5.6 and 100 for RDS instances that run MySQL 5.7 and MySQL 8.0.

innodb_flush_neighbors

Supported MySQL versions: 8.0, 5.7, and 5.6.
Whether to restart the instance after parameter modification: no.
Functionality: This parameter specifies whether to flush the dirty pages in the same extent as the dirty page that is flushed from a specific InnoDB buffer pool. Valid values:
- 0: The dirty pages in the same extent as the specified dirty page are not flushed.
- 1: The dirty pages in the same extent as the specified dirty page or the neighbor dirty pages are flushed.
- 2: All dirty pages in the same extent as the specified dirty page are flushed.

Suggestion: Set this parameter to 0.

innodb_lock_wait_timeout

Supported MySQL versions: 8.0, 5.7, and 5.6.
Whether to restart the instance after parameter modification: no.
Functionality: This parameter specifies the time that an InnoDB transaction waits for a row lock before the transaction is aborted. Unit: seconds.
Suggestion: Set this parameter to 50.

innodb_lru_scan_depth

Supported MySQL versions: 8.0, 5.7, and 5.6.
Whether to restart the instance after parameter modification: no.
Functionality: This parameter specifies the scan depth of page-cleaner threads that are used to scan the Least Recently Used (LRU) page lists in a buffer pool. This parameter affects the flushing operation of the buffer pool.
Suggestion: Set this parameter to {LEAST(DBInstanceClassMemory/1048576/8, 8192)}.

innodb_purge_threads

Supported MySQL versions: 8.0, 5.7, and 5.6.
Whether to restart the instance after parameter modification: yes.
Functionality: This parameter specifies the number of background threads that InnoDB uses to purge undo records. A large value improves the efficiency to purge undo records. This prevents undo files from being accumulated. In some cases, a large value improves the efficiencies of DML operations and queries.
Suggestion: Set this parameter to LEAST(DBInstanceClassMemory/1073741824, 8).

innodb_log_file_size

Supported MySQL versions: 8.0, 5.7, and 5.6.
Whether to restart the instance after parameter modification: yes.
Functionality:
- This parameter specifies the size of each log file in a redo log group. The total size of log files cannot exceed 512 GB. The total size is specified by the innodb_log_file_size * innodb_log_files_in_group parameter. The default value is 48 MB.
- In most cases, the size of the log file must be large enough. This way, the server has more storage for redo logs to handle write requests for more than one hour. This helps achieve a workload balance during peak and off-peak hours. If the value of this parameter is large, the number of checkpoint refreshes required in the buffer pool is small. This consumes less disk I/O resources. However, if the size of a log file is excessively large, the time that is required to recover from a failure becomes long.
Suggestion: Configure this parameter based on your instance specifications.

innodb_sync_array_size

Supported MySQL versions: 8.0, 5.7, and 5.6.
Whether to restart the instance after parameter modification: yes.
Functionality: This parameter defines the size of the mutex or lock wait array. A large value splits the internal data structure that is used to coordinate threads. This improves the concurrency in workloads that have a large number of waiting threads. You must configure this parameter when you start your RDS instance. The setting of this parameter cannot be changed. We recommend that you set this parameter to a large value for workloads that frequently generate a large number of waiting threads. In most cases, if the number of waiting threads is greater than 768, you can increase the value of this parameter.
Suggestion: Set this parameter to 128.

innodb_page_cleaners

Supported MySQL versions: 8.0 and 5.7.
Whether to restart the instance after parameter modification: yes.
Functionality: This parameter specifies the number of page-cleaner threads that are used to flush dirty pages from buffer pool instances. If multiple page cleaner threads exist, the flushing tasks for each buffer pool instance are distributed to idle page cleaner threads. The default value of the innodb_page_cleaners parameter is 4. If the number of page cleaner threads exceeds the number of buffer pool instances, the system sets the innodb_page_cleaners parameter to the value of the innodb_buffer_pool_instances parameter.
Suggestion: Set this parameter to {LEAST(DBInstanceClassMemory/1073741824, 8)}.

innodb_open_files

Supported MySQL versions: 8.0, 5.7, and 5.6.
Whether to restart the instance after parameter modification: yes.
Functionality: This parameter specifies the maximum number of file handles that InnoDB can open at the same time.
Symptom: If the value of this parameter is small, the following error that affects instance performance may occur:
```
[Warning] [MY-012152] [InnoDB] Open files * exceeds the limit *
```
Suggestion: Set this parameter to 20000.

default_time_zone

Supported MySQL versions: 8.0, 5.7, and 5.6.
Whether to restart the instance after parameter modification: yes.
Functionality: This parameter specifies the default time zone.
Symptom: If this parameter is left empty, the time zone of the host is used. In this case, the operating system may enter the lock wait state, and the CPU utilization may significantly increase.
Suggestion: Configure this parameter based on your business requirements. For more information about how to modify this parameter, see Best practices of the time_zone parameter in ApsaraDB RDS for MySQL.

general_log

Supported MySQL versions: 8.0, 5.7, and 5.6.
Whether to restart the instance after parameter modification: no.
Functionality: This parameter specifies whether to enable the general query log feature.
Symptom: If you enable the general query log feature, some errors occur. For more information, see FAQ about the general query log feature of ApsaraDB RDS for MySQL.
Suggestion: Set this parameter to OFF.

innodb_io_capacity

Supported MySQL versions: 8.0, 5.7, and 5.6.
Whether to restart the instance after parameter modification: no.
Functionality: This parameter specifies the maximum number of I/O operations that are allowed by InnoDB per second for each background task.
Suggestion: Set this parameter to 20000.

innodb_io_capacity_max

Supported MySQL versions: 8.0, 5.7, and 5.6.
Whether to restart the instance after parameter modification: no.
Functionality: If a flushing operation falls behind, InnoDB can forcefully perform the operation at a higher rate than the value of the innodb_io_capacity parameter. The innodb_io_capacity_max parameter specifies the maximum IOPS of InnoDB for background tasks.
Suggestion: Set this parameter to 40000.