ApsaraDB RDS:Tune instance parameters

back_log

Controls the length of the connection queue when the instance handles a large number of short-lived connections. When the queue is full, the instance rejects new connection requests.

Symptom: A too-small value causes the following application error:

SQLSTATE[HY000] [2002] Connection timed out

Recommended value: 3000

net_write_timeout

Sets how long the instance waits before sending a block to a client.

Symptom: A too-small value causes the following client error:

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

Recommended value: 60 (seconds, the default). If the network is unstable or the client takes a long time to process each block, increase this value to prevent unexpected disconnections.

innodb_lock_wait_timeout

Sets the maximum time (in seconds) an InnoDB transaction waits for a row lock before the transaction is aborted.

Recommended value: 50

innodb_thread_concurrency

Sets the maximum number of concurrent threads inside InnoDB. A value of 0 removes the limit.

Recommended value: 0 (unlimited) unless you are troubleshooting high-concurrency performance issues.

loose_rds_threads_running_high_watermark

Caps the total number of concurrent queries. For example, setting this to 100 allows up to 100 concurrent MySQL queries; additional queries are rejected.

Use this parameter to protect the instance against burst traffic and peak-hour overload.

thread_pool_enabled

Enables or disables the thread pool feature.

Recommended value: ON

thread_pool_size

Sets the number of thread groups in the thread pool.

Recommended value: {LEAST(DBInstanceClassCPU × 2, 64)}

thread_pool_oversubscribe

Sets the maximum number of active threads per thread group.

Recommended value: 32

thread_stack

Sets the stack depth for each user thread. Too small a value causes deeply recursive SQL queries to fail.

Recommended value: 1048576

innodb_buffer_pool_size

Sets the size of the InnoDB buffer pool — the primary memory area for caching table and index data. A properly sized buffer pool keeps your working set (the data and indexes your queries access most often) in memory, dramatically reducing disk I/O.

Recommended value: {DBInstanceClassMemory × 3/4}

innodb_buffer_pool_instances

Splits the buffer pool into multiple independently managed instances to support concurrent reads and writes. Applies only when innodb_buffer_pool_size exceeds 1 GB.

Recommended value: {LEAST(DBInstanceClassMemory/1073741824, 8)}

innodb_max_dirty_pages_pct_lwm

Sets the dirty page percentage threshold that triggers pre-flushing. The value must be less than innodb_max_dirty_pages_pct. Setting it to 0 disables pre-flushing.

Recommended value: 10

innodb_lru_scan_depth

Sets how deep page-cleaner threads scan the Least Recently Used (LRU) list in each buffer pool instance when flushing dirty pages.

Recommended value: {LEAST(DBInstanceClassMemory/1048576/8, 8192)}

innodb_page_cleaners

Sets the number of page-cleaner threads used to flush dirty pages from buffer pool instances. If this value exceeds the number of buffer pool instances, the system automatically caps it at the innodb_buffer_pool_instances value. The default is 4.

Recommended value: 8

tmp_table_size

Sets the maximum size of an internal in-memory temporary table per thread. The effective limit is whichever is smaller: tmp_table_size or max_heap_table_size. When a temporary table exceeds this limit, MySQL converts it to an on-disk table (InnoDB in MySQL 8.0; MyISAM in earlier versions).

Symptom: Complex queries with GROUP BY or DISTINCT that cannot be served by an index run slower when temporary tables spill to disk.

Default value: 2097152. Increase on instances with ample memory if queries contain many GROUP BY or DISTINCT clauses.

innodb_io_capacity

Sets the maximum number of I/O operations per second that InnoDB background tasks can consume.

Recommended value: 20000

innodb_io_capacity_max

Sets the maximum IOPS InnoDB can use when flushing falls behind the configured rate.

Recommended value: 40000

innodb_flush_neighbors

Controls whether InnoDB flushes neighboring dirty pages when flushing a dirty page. Valid values:

• 0: Flush only the target dirty page.

• 1: Flush the target page and its neighbors in the same extent.

• 2: Flush all dirty pages in the same extent.

Recommended value: 0

innodb_change_buffering

Delays write operations on secondary indexes to convert random I/O to sequential I/O. This helps on HDDs but provides limited benefit on modern storage hardware.

Recommended value: none

innodb_log_file_size

Sets the size of each log file in the redo log group. The total redo log size is innodb_log_file_size × innodb_log_files_in_group and cannot exceed 512 GB. The default is 48 MB.

A larger log file reduces checkpoint frequency and disk I/O, but increases crash recovery time. Set this value large enough that the instance can handle write traffic for more than one hour, smoothing out the difference between peak and off-peak I/O.

Recommended value: Configure based on your instance specifications.

open_files_limit

Sets the number of file handles the instance can open simultaneously. Also affects innodb_open_files.

Recommended values:

• MySQL 5.6: 65535

• MySQL 5.7 and 8.0: 655350

For instances with 32 or more CPU cores and a large number of active sessions or tables, set this to a value slightly above the actual number of instance files.

innodb_open_files

Sets the maximum number of file handles InnoDB can have open at the same time.

Symptom: A too-small value causes the following warning that degrades performance:

[Warning] [MY-012152] [InnoDB] Open files * exceeds the limit *

Recommended value: 20000

table_open_cache

Sets the maximum number of tables the server keeps open across all table cache instances. A too-small value degrades query performance under high concurrency; a too-large value increases memory consumption. Monitor memory usage when increasing this value.

Recommended value: {LEAST(DBInstanceClassMemory/1073741824 × 1024, 16384)}

table_open_cache_instances

Partitions the table cache into separate instances, each of size table_open_cache / table_open_cache_instances. This reduces contention on the table cache across sessions.

Recommended value: 16

innodb_autoinc_lock_mode

Controls how InnoDB manages auto-increment locks for auto-increment primary keys. Valid values:

• 0 (traditional): Holds the AUTO-INC lock for the full duration of the statement. Significantly limits INSERT concurrency.

• 1 (consecutive, default): Holds the AUTO-INC lock briefly. Releases it immediately for fixed-row inserts; holds it for the full statement duration for variable-row inserts.

• 2 (interleaved): Releases the AUTO-INC lock as soon as the value is assigned, regardless of whether the number of rows is fixed.

Recommended value: 2. This eliminates AUTO-INC deadlocks and improves INSERT … SELECT performance.

innodb_adaptive_hash_index

Enables or disables the adaptive hash index, which helps InnoDB locate buffer pool pages quickly based on query patterns.

Symptom: When enabled (ON), certain statements — particularly DDL — can trigger adaptive hash index maintenance, blocking other queries or degrading performance.

Recommended value: OFF. For background information, see Best practices on adaptive hash indexes of ApsaraDB RDS for MySQL.

rpl_semi_sync_master_timeout

Sets how long the primary RDS instance waits for the secondary RDS instance to acknowledge receipt of binary logs before a transaction commits. If the timeout is exceeded, replication degrades from semi-synchronous to asynchronous. Once the secondary catches up, replication automatically returns to semi-synchronous mode.

Recommended value: 1000 (milliseconds, equal to 1 second). For workloads that require high data reliability, increase this value to prevent degradation to asynchronous replication. Be aware that a large value can cause write requests to stall during long-running transactions, which may trigger the high availability (HA) component to detect an instance failure and initiate an instance switchover.

slave_parallel_workers

Sets the number of parallel replay threads on the secondary RDS instance. Too few threads limit parallel replay throughput and increase replication latency.

Recommended value: {GREATEST(DBInstanceClassCPU, 8)}

binlog_transaction_dependency_tracking

Specifies how MySQL tracks transaction dependencies for parallel replication. Setting this to WRITESET enables row-level conflict detection, allowing faster parallel replay on the secondary RDS instance.

Recommended value: WRITESET. Set transaction_write_set_extraction before changing this parameter. For details, see Modify WRITESET-related parameters.

binlog_transaction_dependency_history_size

Sets the maximum number of hashed transaction entries kept in memory for dependency tracking. When the limit is reached, all entries are cleared.

Symptom: Too small a value reduces parallel replay concurrency on the secondary RDS instance, increasing replication latency.

Recommended value: 500000. For details, see Modify WRITESET-related parameters.

transaction_write_set_extraction

Sets the hash algorithm used by WRITESET.

Recommended value: XXHASH64. For details, see Modify WRITESET-related parameters.

query_cache_size

Controls the memory allocated to the MySQL query cache. When enabled, MySQL checks the cache before executing a query. On read-heavy workloads with infrequent writes, this can improve performance. On write-heavy workloads, the lock mechanism of the query cache causes frequent conflicts that degrade SELECT performance.

Symptom: Many connections stuck in these states:

checking query cache for query
waiting for query cache lock
storing result in query cache

Recommended value: Leave disabled (the ApsaraDB RDS default). If you previously enabled the query cache and see the above symptoms, disable it.

eq_range_index_dive_limit

Controls when the optimizer switches from index dives to statistics-based estimation for equality range conditions. For a query such as:

col_name IN (val1, ..., valN)
col_name = val1 OR ... OR col_name = valN

If N is less than this limit, the optimizer samples data using index dives. If N is greater than or equal to this limit, the optimizer uses table statistics instead.

For more information, see MySQL documentation on eq_range_index_dive_limit.

Recommended values:

• MySQL 5.6: 10

• MySQL 5.7 and 8.0: 100

loose_rds_max_tmp_disk_space

Limits the total size of temporary files on the instance.

Symptom: Exceeding this limit causes the following error:

The table '/home/mysql/dataxxx/tmp/#sql_2db3_1' is full

First, check whether the SQL statements generating temporary files can be optimized. If the instance has enough disk space and the queries cannot be avoided, increase this value.

loose_innodb_rds_faster_ddl

Accelerates certain DDL operations (such as schema changes) using a buffer pool management mechanism developed by the ApsaraDB RDS team, reducing the impact of DDL on running workloads.

Recommended value: Enabled.

innodb_ft_cache_size

Sets the memory (in bytes) allocated for full-text index data per table.

Recommended value: 8000000

innodb_ft_total_cache_size

Sets the total memory (in bytes) allocated for full-text index data across all tables on the instance.

Recommended value: 640000000

innodb_ft_result_cache_limit

Sets the memory limit (in bytes) for the result set of each full-text index query.

Recommended value: 2000000000

innodb_purge_threads

Sets the number of background threads InnoDB uses to purge undo records. More threads improve purge throughput, preventing undo file accumulation and improving DML and query performance in some workloads.

Recommended value: LEAST(DBInstanceClassMemory/1073741824, 8)

innodb_sync_array_size

Sets the size of the mutex/lock wait array. A larger value improves concurrency for workloads that generate many waiting threads. This parameter is read at startup and cannot be changed at runtime. If the number of waiting threads frequently exceeds 768, increase this value.

Recommended value: 128

innodb_parallel_read_threads

Sets the number of concurrent threads for primary key scans. This parameter has known bugs.

Recommended value: 0 (disabled)

performance_schema

Enables or disables Performance Schema, which instruments MySQL's internal execution. Enabling it adds memory overhead and can affect performance.

Recommended value: OFF

general_log

Enables or disables the general query log, which records every statement received by the server.

Symptom: Enabling this log can exhaust instance storage. For more information, see FAQ about the general query log feature.

Recommended value: OFF

default_time_zone

Sets the default server time zone.

Symptom: An unset or mismatched time zone causes incorrect time conversions, inconsistent timestamps, and higher CPU usage from frequent timezone adjustments — particularly in applications serving multiple regions.

Recommended value: Set based on your business requirements. For details, see Best practices of the time_zone parameter in ApsaraDB RDS for MySQL.

loose_tokudb_buffer_pool_ratio

Sets the percentage of buffer memory that TokuDB tables can use, relative to innodb_buffer_pool_size. For example, if innodb_buffer_pool_size is 1000 MB and this parameter is set to 50, TokuDB tables can use up to 500 MB.

Increase this value if TokuDB tables are the primary workload on the instance.

loose_max_statement_time

Sets the maximum execution time for queries, in milliseconds. Queries that exceed this limit are interrupted. By default, query duration is unlimited.

Symptom: Exceeding the limit causes the following error:

ERROR 3006 (HY000): Query execution was interrupted, max_statement_time exceeded

Set this parameter when you need to enforce query time limits on the instance.