edit-icon download-icon

Parameter optimization for MySQL instances

Last Updated: Mar 30, 2018

For MySQL instances, you can modify some parameters using the console. Setting inappropriate values for key parameters may reduce instance performance and cause application errors. This article details recommended optimal values for key parameters to help reduce risks when setting parameters.

back_log

  • Default value: 3000

  • Restart required after modification: Yes

  • Function: MySQL creates a corresponding new thread for each connection request it handles. If a large number of short connection requests are sent to the database when the main thread is creating new threads, MySQL uses the back_log parameter to restrict these new connections from joining the request queue. If the number of waiting connections exceeds the back_log value, new connection requests are not accepted. If MySQL is expected to process a large number of short connections, we recommend that you increase the value of this parameter.

  • Symptom:: A parameter value that is too small may cause the following error.

    1. SQLSTATE[HY000] [2002] Connection timed out;
  • Recommended modification: Increase the parameter value.

innodb_autoinc_lock_mode

  • Default value: 1

  • Restart required after modification: Yes

  • Function: For MySQL 5.1.22 and later versions, InnoDB introduces the innodb_autoinc_lock_mode parameter to resolve table-level lock issues regarding auto-incrementing primary keys. The values of this parameter include 0, 1, and 2. The default value for RDS is 1, which indicates that InnoDB uses the lightweight mutex lock to get auto-increment locks and replace original table-level locks. However, if auto-increment table locks are used in a load data scenario (including INSERT … SELECT and REPLACE … SELECT), a deadlock may occur when the application imports data concurrently.

  • Symptom: If auto-increment table locks are used in the load data scenario (including INSERT … SELECT and REPLACE … SELECT), a deadlock may occur when data is concurrently imported.

    1. 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;
  • Recommended modification: Set the parameter value to 2 to use the lightweight mutex locks in all insertion scenarios. This not only avoids a deadlock when using auto_inc, it also improves performance during an INSERT … SELECT scenario.

    Note: If the value of this parameter is 2, we recommend that you set the binlog format to row.

query_cache_size

  • Default value: 3145728

  • Restart required after modification: No

  • Function: This parameter is used to control the memory size of MySQL query cache. If query cache is enabled in MySQL, MySQL first locks the query cache before executing a query, and then determines whether the query already exists in the query cache. If the query exists, results are returned immediately. If the query does not exist, the engine query and other operations are executed. At the same time, operations such as INSERT, UPDATE, and DELETE, cause the query cache to fail. Any changes to the structure or index can also cause failure. Recovering from a cache failure can be costly and causes heavy stress on MySQL. Query cache is a useful function if the database is not frequently updated. However, if data writes are frequent, and performed mainly on several tables, query cache lock frequently causes lock conflicts. When lock conflicts occur, reading from, and writing to, a table is paused until the query cache lock is lifted, reducing the efficiency of the select query.

  • Symptom: The database goes through a number of different statuses, including checking query cache for query, Waiting for query cache lock, and storing result in query cache.

  • Recommended modification: The query cache function is disabled in RDS by default. If query cache is enabled in your instance, you can choose to disable it if you encounter any of the preceding statuses.

net_write_timeout

  • Default value: 60

  • Restart required after modification: No

  • Function: This parameter controls the time that a block needs to wait before it is sent to the client.

  • Symptom: A parameter value that is too small may cause the errors in the client: the last packet successfully received from the server was milliseconds ago or the last packet sent successfully to the server was milliseconds ago.

  • Recommended modification: The value of this parameter is set to 60(s) in RDS by default. With a slow network connection, it may take longer for the client to process each block. In this case, a net_write_timeout value that is too small may cause frequent disconnections. In such cases, we recommend that you increase the parameter value.

tmp_table_size

  • Default value: 2097152

  • Restart required after modification: No

  • Function: This parameter determines the maximum size of internal temporary memory tables, which are then assigned to each thread. The actual limit is the smaller value of the tmp_table_size and max_heap_table_size parameters. If temporary memory tables exceed the limit, MySQL automatically converts them to disk-based MyISAM tables. When optimizing the query statement, avoid using temporary tables. If temporary tables are required, make sure that these temporary tables are in memory.

  • Symptom: If temporary tables are used when a complex SQL statement contains statements such as group by and distinct, which cannot be optimized through an index, the SQL execution duration is extended.

  • Recommended modification: If the application involves a lot of group by/distinct statements, and the database has enough memory, you can increase the value of tmp_table_size (max_heap_table_size) to improve query performance.

loose_rds_max_tmp_disk_space

  • Default value: 10737418240

  • Restart required after modification: No

  • Function: This parameter determines the size of temporary files that MySQL can use.

  • Symptom: If temporary files exceed the value of loose_rds_max_tmp_disk_space, the following error occurs.

    1. The table ‘/home/mysql/dataxxx/tmp/#sql_2db3_1 is full
  • Recommended modification: Analyze whether the SQL statement that increases temporary files can be optimized by indexing or using other techniques. Then, if there is sufficient instance space, increase the value of this parameter to make sure that the SQL statement is executed properly.

loose_tokudb_buffer_pool_ratio

  • Default value: 0

  • Restart required after modification: Yes

  • Function: This parameter controls the size of the buffer memory that the TokuDB engine can use. For example, if the innodb_buffer_pool_size is set to 1000 MB and the value of tokudb_buffer_pool_ratio is set to 50 (that is, 50%), then the buffer memory size available to the TokuDB engine is 500 MB.

  • Recommended modification: If the TokuDB engine is used in RDS, we recommend that you increase this parameter’s value to improve the access performance of the TokuDB engine.

loose_max_statement_time

  • Default value: 0

  • Restart required after modification: No

  • Function: This parameter determines the maximum execution time of queries in MySQL. If the configured time for this parameter is exceeded, then queries fail automatically. By default, there is no limit.

  • Symptom: If the query time exceeds the configured value for this parameter, the following error occurs.

    1. ERROR 3006 (HY000): Query execution was interrupted, max_statement_time exceeded
  • Recommended modification: Set a value for this parameter (unit: seconds) to control the execution time of SQL statements for the database.

loose_rds_threads_running_high_watermark

  • Default value: 50000

  • Restart required after modification: No

  • Function: This parameter controls the number of concurrent queries in MySQL. For example, if the rds_threads_running_high_watermark value is set to 100, the number of simultaneous concurrent queries allowed in MySQL is 100. Any queries exceeding this limit are refused. This parameter is used with the rds_threads_running_ctl_mode parameter (the default is select).

  • Recommended modification: Set this parameter in scenarios that involve a large number of concurrent requests, for example, a “spike in the last minute” situation. This helps to protect the database.

Thank you! We've received your feedback.