Mastering RDS MySQL: Which Parameters You Can Tune and How

RDS MySQL users often ask how to tune instance parameters; this article clarifies which parameters can be changed, which are locked by the service, and how to adjust the configurable ones for better stability and performance.

Parameters That Cannot Be Modified

Instance‑level limits such as maximum connections and memory are determined by the chosen RDS instance class; these values cannot be altered. Hitting these limits leads to memory OOM (causing primary‑standby failover) or connection‑refused errors, which must be addressed by application optimization, slow‑SQL tuning, or scaling the instance.

Parameters that protect data consistency between primary and standby, such as innodb_flush_log_at_trx_commit, sync_binlog, gtid_mode, semi_sync, and binlog_format, are also locked to ensure reliable replication.

Configurable Parameters and Recommendations

open_files_limit

Controls the maximum number of file descriptors MySQL can open simultaneously. The default is 8192; exceeding this limit produces errors like

[ERROR] /mysqld: Can't open file: './mysql/user.frm' (errno: 24 -Too many open files)

. Increase the value up to the RDS maximum of 65535 and consider switching MyISAM tables to InnoDB.

back_log

Defines the size of the connection request queue for short‑lived connections. A small value can cause SQLSTATE[HY000] [2002] Connection timed out. Raise the setting (default 50, now up to 3000) and restart the instance.

innodb_autoinc_lock_mode

Determines the lock strategy for auto‑increment columns. Values: 0 (table lock), 1 (lightweight mutex, default), 2 (row‑level mutex). Setting to 2 avoids deadlocks during bulk loads (e.g., INSERT … SELECT) but requires the binlog format to be ROW.

query_cache_size

Specifies the memory allocated for MySQL's query cache. When enabled, each query acquires a lock; heavy write workloads cause frequent lock contention, degrading performance. RDS disables the cache by default; enable it only for read‑heavy workloads, otherwise keep it off.

net_write_timeout

Timeout for sending a block of data to the client. A low value can trigger errors like "the last packet successfully received from the server was ... milliseconds ago". The default is 60 seconds; increase it for high‑latency networks or large result sets.

tmp_table_size (and max_heap_table_size)

Sets the maximum size of in‑memory temporary tables per thread. When a temporary table exceeds this size, MySQL converts it to an on‑disk MyISAM table, slowing queries that use GROUP BY or DISTINCT. Increase both values if sufficient RAM is available.

rds_max_tmp_disk_space

Limits the total size of temporary files MySQL may create (default 10 GB). Exceeding this limit yields errors such as The table '/home/mysql/dataxxx/tmp/#sql_2db3_1' is full. Optimize offending SQL or raise the limit (requires instance restart).

tokudb_buffer_pool_ratio

For instances using the TokuDB engine, this ratio determines the portion of the InnoDB buffer pool size allocated to TokuDB. The default is 0 (disabled). Set a non‑zero percentage (e.g., 50) to improve TokuDB performance; a restart is required.

max_statement_time

Specifies the maximum execution time for a query in milliseconds. When exceeded, MySQL aborts the query with

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

. Enable and set an appropriate limit to protect against runaway queries.

rds_threads_running_high_watermark

Controls the maximum number of concurrent queries. Setting a value (e.g., 100) works with rds_threads_running_ctl_mode to reject excess queries, providing protection in flash‑sale or high‑concurrency scenarios.

For most workloads, the default DBA‑optimized settings are sufficient; only adjust parameters when specific engine features (e.g., TokuDB) or workload characteristics demand it.