All Products
Search
Document Center

ApsaraDB RDS:Optimize instance parameters

Last Updated:Sep 26, 2023

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: Increase the value of this parameter.

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. Valid values: 0, 1, and 2. Default value: 1. The default value specifies that InnoDB uses a lightweight mutex to obtain auto-increment locks, instead of table-level locks. If you use auto-increment locks when you execute statements to load data, including INSERT … SELECT and REPLACE … SELECT, deadlocks may occur in your application when data is concurrently imported.

  • Symptom: You use auto-increment locks when you execute statements to load data, including INSERT … SELECT and REPLACE … SELECT. The following deadlock occurs in your application when data is concurrently imported:

    RECORD LOCKS space id xx page no xx n bits xx index PRIMARY of table xx.xx trx id xxx lock_mode X insert intention waiting. TABLE LOCK table xxx.xxx trx id xxxx lock mode AUTO-INC waiting;
  • Suggestion: Change the value of this parameter to 2. The value 2 indicates that all SQL statements that are used to load data in row mode use a lightweight mutex. This prevents the AUTO-INC deadlock and greatly improves the performance of the INSERT … SELECT statement.

    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, 5.6, and 5.5.

  • 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 larger 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.

  • Suggestion: For instances of earlier versions, the value of the innodb_buffer_pool_size parameter may be greater than 1 GB, but the innodb_buffer_pool_instances parameter is set to 1. We recommend that you reset the value based on the default value of the current parameter template.

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: For instances of earlier versions, the value of the innodb_buffer_pool_size parameter may be greater than 1 GB, but the table_open_cache_instances parameter is set to 1. We recommend that you reset the value based on the default value of the current parameter template.

table_open_cache

  • Supported MySQL versions: 8.0, 5.7, and 5.6.

  • Whether to restart the instance after parameter modification: no.

  • Function: This parameter defines 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.

  • Suggestion: Execute the SHOW GLOBAL STATUS LIKE 'Open_tables' statement. If the return value is close to or equal to the value of the table_open_cache parameter, we recommend that you increase the value of this parameter based on your business requirements.

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.

  • 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. Before you set this parameter to ON, check whether query performance can be improved after you execute SQL statements. If query performance may be degraded after you execute your SQL statements, we recommend that you retain the default value OFF.

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 its default value 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. The running of the instance is not affected even if the value of this parameter is greater than the number of instance files.