MySQL parameter tuning best practices

Objective
Many times, RDS users often ask how to tune RDS MySQL parameters, in order to answer this question, write a blog to explain:


Which parameters can not be modified, then some parameters may be modified;
These parameters that provide modification are not already the best settings, how can they be used well;
Which parameters can be changed
Careful users in the purchase of RDS will see that different specifications can provide the maximum number of connections and memory is different, so this some product specifications limit parameters: The number of connections, memory users can not be modified, if the memory or the number of connections bottlenecks:


Memory bottleneck: the instance will appear oom and then cause the primary standby to switch
Number of connections bottleneck: App cannot create new connection to database
Application optimization, slow SQL optimization, or elastic upgrade instance specifications are required to resolve.


There are also some parameters such as Innodb_flush_log_at_trx_commit, Sync_binlog, Gtid_mode, Semi_sync, Binlog_format, etc. which are related to the security of the primary and standby data to ensure the security of the data. It is currently not available to users for modification.


In addition to these parameters, most of the parameters have been optimized by the DBA team and the source team, the user does not need to adjust the parameters on the line too much to run the database better. However, these parameters are only suitable for most applications, and individual special scenarios need to be treated individually, such as the use of the TOKUDB engine, which will need to adjust the ratio of memory used by the TOKUDB engine (tokudb_buffer_pool_ratio) Or, for example, my application feature itself requires a large lock timeout, then you need to adjust the size of the Innodb_lock_wait_timeout parameter to suit the application and so on.


How to tune Parameters
Below I will be able to modify some of the more important parameters in the console to introduce you, these parameters if not set up, you may have performance problems or application error.


Open_files_limit
Function: This parameter controls the number of file handles that the MySQL instance can open at the same time.
Cause: When the tables in the database (MyISAM engine tables need to consume file descriptors when they are accessed, the InnoDB engine manages the tables already open-table_open_cache), the number of file handles allocated to each instance is consumed, and more and more open. RDS sets the Open_files_limit to 8192 when initially initializing the instance, which causes all database requests to be reported incorrectly when the number of open tables exceeds this parameter.
Symptom: If the parameter is too set, the application error is caused
[ERROR]/mysqld:can ' t open file: './mysql/user.frm ' (Errno:24-too many open files);
Recommendation: To increase the value of Open_files_limit, RDS can currently support a maximum of 65535, while it is recommended to replace the MyISAM storage engine with the InnoDB engine.


Back_log
Role: MySQL Each processing a connection request will be corresponding to create a new thread corresponding to it, then, during the main thread to create a new thread, if the front-end application has a large number of short connection requests to reach the database, MySQL will limit the moment a new connection into the request queue, by the parameter Back_log control, If the number of pending connections exceeds Back_log, the new connection request will not be accepted, so the size of this parameter needs to be increased if MySQL is able to handle a large number of short connections.
Phenomenon: If the parameter is too small, it may result in an application error
SQLSTATE[HY000] [2002] Connection timed out;
Recommendation: To increase the size of this parameter value, note the need to restart the instance, the default value of RDS at first initialized value is 50, and now the initialization value has been increased by 3000.


Innodb_autoinc_lock_mode
function: After MySQL5.1.22, InnoDB in order to solve the problem of self-increment primary key lock table, introduced the parameter innodb_autoinc_lock_mode, used to control the lock mechanism of the self-increment primary key, the parameter can be set to the value of 0/1/2,rds default parameter value is 1, Indicates that InnoDB uses a lightweight mutex lock to obtain the self-lock, instead of the most primitive table-level lock, but at load data (including: INSERT ... SELECT, REPLACE ... SELECT) scenario, the self-increment table lock is used, which may cause the app to deadlock in concurrent import data.
Behavior: If the app uses load data concurrently (including: INSERT ... SELECT, REPLACE ... SELECT) There is a deadlock when importing data:
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;
Recommendation: It is recommended that you change the parameter setting to 2, which means that all cases are inserted using a lightweight mutex lock (only for row mode), which avoids the deadlock of the auto_inc, while in the insert ... The scene of the SELECT can improve performance (note that the format of the parameter set to 2,binlog needs to be set to row).


Query_cache_size
Function: This parameter is used to control the memory size of MySQL query cache, and if MySQL opens query cache and then executes each query, it locks the query cache and then determines if there is a query cache, if there is a direct return result , if it does not exist, then the engine query and other operations, while the INSERT, update and DELETE operations will invalidate query CAHCE, this failure also includes any changes in structure or index, cache failure maintenance cost is higher, Will give MySQL a lot of pressure, so when our database is not so frequently updated, the query cache is a good thing, but if in turn, write very frequently, and focus on a few tables, then the query cache Lock locking mechanism causes very frequent lock collisions, and for this table the write and read will wait for the query cache lock to unlock each other, resulting in the search efficiency of select reduced.
Phenomenon: There are a large number of connection states in the database checking query cache for query, waiting for query cache lock, storing result in query cache;
Recommendation: RDS turns off the query cache by default, and if your instance opens query cache, you can close the query cache when the above occurs, but there are cases where you can open query cache, for example, by using query cache resolves database performance issues.


Net_write_timeout
Function: Waits for the time-out to send a block to the client.
Symptom: The parameter setting is too small may cause the client to error the last packet successfully received from the server is milliseconds ago,the last packet sent SUCCESSF Ully to the server was milliseconds ago.
Recommendation: This parameter is set to 60S by default in RDS, generally when the network condition is poor, or the client processing each block time is relatively long, because the net_write_timeout set too small caused by the connection interruption is easy to occur, it is recommended to increase the size of the parameter;


Tmp_table_size
Function: This parameter is used to determine the maximum value of the internal memory temporary table, each thread is allocated (the actual limit is the minimum value of tmp_table_size and max_heap_table_size), if the memory temp table exceeds the limit, MySQL will automatically convert it into a disk-based MyISAM table, when optimizing query statements, to avoid the use of temporary tables, if it can not be avoided, to ensure that these temporary tables are in memory.
Phenomenon: If a complex SQL statement contains a group by/distinct and so on that cannot be optimized by index, temporary tables are used, resulting in longer SQL execution time.
Recommendation: If there are many statements such as group By/distinct in your application, and the database has enough memory, you can increase the value of tmp_table_size (max_heap_table_size) to improve query performance.


RDS MySQL New Parameter
Here are a few of the more useful RDS MySQL new parameters.


Rds_max_tmp_disk_space
Function: Used to control the size of the temporary file that MySQL can use, the initial default value of RDS is 10G, if the temporary file exceeds this size, it will cause the application error.
Phenomenon: The table '/home/mysql/dataxxx/tmp/#sql_2db3_1 ' is full.
Recommendation: It is necessary to analyze whether the SQL statement that caused the temporary file increase can be optimized by index or otherwise, and if the instance is determined to have sufficient space, the value of this parameter can be promoted to ensure that SQL executes properly. Note that this parameter requires a restart of the instance;


Tokudb_buffer_pool_ratio
Function: Used to control the buffer memory size that the TOKUDB engine can use, such as Innodb_buffer_pool_size set to 1000m,tokudb_buffer_pool_ratio set to 50 (for 50%), Then the TOKUDB engine table can use the buffer memory size is 500M;
Recommendation: This parameter is set to 0 by default in RDS, and if you use the TOKUDB engine in RDS, it is recommended to increase the access performance of the TOKUDB engine table by increasing the parameter. This parameter adjustment requires a restart of the DB instance.


Max_statement_time
Function: Used to control the maximum execution time of the query in MySQL, if more than the parameter set time, the query will automatically fail, the default is unlimited.
Recommendation: If a user wants to control the execution time of SQL in the database, the parameter can be turned on in milliseconds.
Symptom: ERROR 3006 (HY000): Query execution was interrupted, Max_statement_time exceeded


Rds_threads_running_high_watermark
Function: The number of queries used to control MySQL concurrency, such as setting the Rds_threads_running_high_watermark value to 100, allows MySQL concurrent queries to be 100, and queries that exceed the water level will be rejected, and the RDS _threads_running_ctl_mode with (the default is select).
Recommendation: This parameter is often used in the scenario of second-kill or large concurrency, which has a good protective effect on the database.


Transferred from: http://mysql.taobao.org/monthly/2015/12/04/?utm_source=tool.lu

MySQL parameter tuning best practices