RDS MySQL parameter tuning best practices

Abstract:

preface

RDS users often ask how to tune the parameters of RDS MySQL. To answer this question, write a blog post explaining:

1. Which parameters cannot be modified, which parameters can be modified;
2. Whether the parameters provided for modification are already the best Settings and how to make good use of these parameters;

What parameters can be changed

When purchasing RDS, careful users will see that the maximum number of connections and memory provided by different specifications are different. Therefore, the limitation parameters of product specifications: connection number and memory can not be modified by users. If there is a bottleneck in memory or connection number:

1. Memory bottleneck: The instance will appear OOM, and the active/standby switchover will occur
2. Connection bottleneck: The application cannot establish new connections to the database

Application optimization, slow SQL optimization, or elastic upgrade instance specifications are required to solve the problem.

There are also parameters related to primary and secondary data security such asinnodb_flush_log_at_trx_commit,sync_binlog,gtid_mode,semi_sync,binlog_formatTo ensure the data security of the active and standby nodes, do not provide the data for users to modify.

In addition to these parameters, most of the parameters have been optimized by the DBA team and the source team, and users do not need to adjust the parameters online to run the database well. However, these parameters are only suitable for most application scenarios. Some special scenarios need to be treated individually. For example, if the Tokudb engine is used, the ratio of memory that the Tokudb engine can use needs to be adjusted.tokudb_buffer_pool_ratio); For example, if my application features itself require a large lock timeout, then it needs to be adjustedinnodb_lock_wait_timeoutSize the parameters to fit the application and so on.

How to adjust parameters

Here I’ll take a look at some of the more important parameters that can be changed in the console, which can cause performance problems or application errors if not set properly.

open_files_limit

Purpose: This parameter controls the number of file handles that the MySQL instance can open at the same time.

Cause: While tables in the database (MyISAM engine tables consume file descriptors when accessed, InnoDB engine manages open tables itself —table_open_cacheOpening more and more consumes the number of file handles allocated to each instance, which RDS sets when initializing the instance in the first placeopen_files_limitThe value is 8192. If the number of open tables exceeds this parameter, all database requests will fail.

Symptom: If the parameter is too small, an application error occurs

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

Suggestion: Improveopen_files_limitRDS can currently support up to 65535, and it is recommended to replace MyISAM storage engine with InnoDB engine.

back_log

If a large number of short connection requests arrive in the database during the main thread creation process, MySQL will limit the number of new connections to the queueback_logControl if the number of connections waiting exceedsback_log, will not accept new connection requests, so if you want MySQL to be able to handle a large number of short connections, you need to increase the size of this parameter.

Symptom: An application error may occur if the parameter is too small

SQLSTATE[HY000] [2002] Connection timed out;

Suggestion: Increase the value of this parameter. Note that you need to restart the instance. The initial value of RDS is 50 by default, but the initial value has been increased by 3000.

innodb_autoinc_lock_mode

Purpose: InnoDB introduced parameters after MySQL5.1.22 to solve the problem of auto-increment primary key lock tableinnodb_autoinc_lock_modeThe default value of RDS is 1, which means InnoDB uses lightweight mutex locks instead of the original table-level locks. However, in load data (including: INSERT… SELECT the REPLACE… In the SELECT scenario, auto-add table locks are used, which may cause deadlock in concurrent data import.

If an application concurrently uses load data(including INSERT… SELECT the REPLACE… SELECT * from ‘SELECT’ where ‘deadlock’ occurs

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 parameter to 2 to use a lightweight mutex lock (only for ROW mode) for all inserts. This will avoid auto_inc deadlocks, and at the same time in INSERT… In the SELECT scenario, performance is greatly improved (note that this parameter is set to 2 and the binlog format must be set to ROW).

query_cache_size

Function: This parameter controls the size of the MySQL Query cache. If query Cache is enabled, the MySQL database locks the query cache before executing each query, and then checks whether the query cache exists. If the query cache does not exist, the MySQL database queries the query engine. Insert, update, and DELETE invalidates query CAhCE, including any structural or index changes. Cache invalidations are costly to maintain and cause a lot of pressure on MySQL. Query Cache is a good thing, but if the reverse is true, the query Cache lock mechanism causes frequent lock collisions when writes are concentrated on a few tables, and writes and reads to the table wait for the Query Cache lock to unlock each other. The select query efficiency decreases.

A large number of connections in the database are checking Query cache for query, Waiting for query cache lock, and storing result in query cache.

Suggestion: RDS disables Query Cache by default. If query Cache is enabled in your instance, you can disable Query Cache when the above situation occurs. Of course, there are situations where you can turn query Cache on, such as using Query Cache to solve database performance problems.

net_write_timeout

Action: Timeout to wait for a block to be sent to a client.

Phenomenon: The last packet successfully received from the server was milliseconds ago, The last packet sent successfully to the server was milliseconds ago.

Suggestion: The default value of this parameter is 60 seconds in RDS. This parameter is generally set when the network condition is poor or the client takes a long time to process each blocknet_write_timeoutIf the value is too small, connection interruption may occur easily. You are advised to increase the value of this parameter.

tmp_table_size

Effect: This parameter determines the maximum number of internal memory temporary tables that are allocated per threadtmp_table_sizeandmax_heap_table_sizeMySQL automatically converts a temporary table into a disk-based MyISAM table if the temporary table exceeds the limit. Avoid using temporary tables when optimizing a query statement, or if it is unavoidable, ensure that the temporary table is in memory.

Symptom: If a complex SQL statement contains groups by/ DISTINCT that cannot be optimized through indexes and uses temporary tables, the SQL execution time will be prolonged.

Suggestion: If there are many group BY/DISTINCT statements in the application and the database has sufficient memory, increase the numbertmp_table_size(max_heap_table_size) to improve query performance.

RDS MySQL new parameter

Here are some useful new RDS MySQL parameters.

rds_max_tmp_disk_space

RDS controls the size of temporary files that MySQL can use. The initial RDS default value is 10GB. If temporary files exceed this size, an application error will be reported.

The table ‘/home/mysql/dataxxx/ TMP /# sql_2DB3_1’ is full.

Suggestion: Check whether the SQL statement that causes the increase of temporary files can be optimized by indexing or other means. If the space of the instance is sufficient, increase the value of this parameter to ensure the normal execution of THE SQL. Note that this parameter requires a restart of the instance.

tokudb_buffer_pool_ratio

What it does: Controls how much buffer the TokuDB engine can use, for exampleinnodb_buffer_pool_sizeSet to 1000M,tokudb_buffer_pool_ratioIf set to 50 (representing 50%), the size of buffer used by the tables in the Tokudb engine is 500 MB.

Suggestion: The default value of this parameter is 0 in RDS. If the TOkudb engine is used in RDS, you are advised to increase this parameter to improve the table access performance of the Tokudb engine. This parameter adjustment requires a restart of the database instance.

max_statement_time

Function: Controls the maximum execution time of a query in MySQL. If this parameter is set, the query will automatically fail. The default value is unlimited.

Suggestion: If you want to control the execution time of SQL in the database, enable this parameter, in milliseconds.

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

rds_threads_running_high_watermark

Use to control the number of concurrent queries in MySQLrds_threads_running_high_watermarkIf this parameter is set to 100, MySQL is allowed to perform 100 concurrent queries at the same time. Queries that exceed the watermark are rejectedrds_threads_running_ctl_modeUsed together (the default value is select).

Suggestion: This parameter is usually used in seckilling or concurrent scenarios to protect the database.

Copyright Notice: The content of this article is contributed by Internet users, the community does not have the ownership, also does not assume the relevant legal responsibility. If you find any content suspected of plagiarism in our community, you are welcome to send an email to [email protected] to report and provide relevant evidence. Once verified, our community will immediately delete the content suspected of infringement.