In many cases, RDS users often ask how to tune RDS MySQL parameters, in order to answer this question, write a blog to explain:
Which some parameters can not be modified, and that some of the parameters can be modified;
These parameters provide the modification is not already the best setting, how to make good use of these parameters;
Which parameters can be changed
Careful users at the time of purchase RDS will see the maximum number of connections that can provide different specifications and memory are different, so here are some limiting parameters Product specifications: number of connections, users are not able to modify memory, if memory or connection number of bottlenecks are:
Memory bottlenecks: examples occur OOM, then the master and backup switchover occurs
Connections bottleneck: the application can not establish a connection to a new database
The need for application-optimized, slow SQL optimization or upgrade instances of elastic specifications to resolve.
There are some parameters related to the primary and secondary data security such as innodb_flush_log_at_trx_commit, sync_binlog, gtid_mode, semi_sync, binlog_format and other data in order to ensure the safety of primary and secondary, is still temporarily available to the user to be modified.
In addition to these parameters mentioned above, the vast majority of parameters have been optimized by the DBA team and the team source, users do not need too many parameters to adjust the line can put the database up and running better. However, these parameters are only suitable for most application scenarios, individual scenes or special needs individual treatment, such as using a tokudb engine, this time on the need to adjust the memory ratio (tokudb_buffer_pool_ratio) tokudb engine can be used; and application features, such as my own needs a lot of lock timeout, then you need to adjust the size of the innodb_lock_wait_timeout parameters to suit the application, and so on.
How to adjust parameters
Here I will console some of the more important parameters that can be modified to tell you about, if these parameters are not set properly, you may experience performance problems or application error.
Role: This parameter is used to control the number of instances of MySQL can simultaneously open file handles in use.
Cause: When the database tables (MyISAM table when the engine is accessed consumes file descriptors, InnoDB engine will have its own management table opened -table_open_cache) to open more and more, it will consume assigned to each instance of the file the number of handles, open_files_limit RDS in the first instance initialization time set for 8192, when the number of open tables exceeds this parameter will cause all database requests error.
Description: If this parameter is set too small can cause the application error
[ERROR] / mysqld: Can not open file: './mysql/user.frm' (errno: 24 -Too many open files);
Recommendation: Increase the value open_files_limit, RDS can now support up to 65535 ,, also proposed to replace the MyISAM storage engine InnoDB engine.
Role: MySQL will handle each corresponding to a connection request is created when a corresponding new thread, then create a new thread during the main thread, if there are a large number of front-end applications short connection request arrives database, MySQL will limit now entering a new connection the request queue control parameter back_log, if the number of connections exceeds back_log wait, it will not accept new connection requests, so if you need MySQL can handle a large number of short connections, need to increase the size of this parameter.
Phenomenon: If the argument is too small may cause an application error
SQLSTATE [HY000]  Connection timed out;
Recommendation: Increase the size of the value of this parameter, note the need to restart the instance, RDS default values in the beginning of the initialization value is 50, now has cranked up the initialization value 3.000.
Role: After MySQL5.1.22, InnoDB in order to solve the self-energizing the primary key table lock problem, the introduction of parameters innodb_autoinc_lock_mode, for controlling auto-increment primary key lock mechanism, this parameter can be set to a value of 0/1/2, RDS default a value of 1 indicates InnoDB uses lightweight else mutex lock to get the self-energizing the lock, replace the original table-level locking, but the load data (including: INSERT ... SELECT, rEPLACE ... SELECT) will use the table scene increment lock, this will cause the application may deadlock in concurrent import data.
Description: If you apply concurrent use load data (including: INSERT ... SELECT, REPLACE ... SELECT) when the import data of deadlock:
. 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 to set the parameter to 2, then all other cases insert uses lightweight mutex lock (only for row mode), so you can avoid auto_inc deadlock, while in INSERT ... SELECT scene will increase very big performance (note that this parameter is set to 2, binlog format needs to be set for the row).
Role: This parameter is used to control the MySQL query cache memory size; if the MySQL query cache turned on, then perform a query each time will first lock the query cache, query cache then determines whether there is, if there is a direct return results, if not exists, then the engine queries and other operations; at the same time insert, update and delete such an operation would fail the query cahce out, this also includes any failure or changes in the structure of the index, the higher the cost of maintaining cache failure, MySQL will bring to greater pressure, so when we are not so frequent database updates when, query cache is a good thing, but if, in turn, is written very frequently, and focus on a few tables when you query cache lock the locking mechanism can cause very frequent lock conflicts for a table for writing and reading it will wait for another query cache lock unlock, resulting in decreased efficiency of the select query.
Description: The database has a large number of connection status checking query cache for query, Waiting for query cache lock, storing result in query cache;
Recommendation: RDS is disabled by default query cache feature, if your instance open the query cache, when the above situation can turn off the query cache; of course, some cases can also open the query cache, such as: Using query cache resolve database performance issues.
Role: Wait a block sent to the client timeout.
Description: The parameter setting is too small may result in the client being given the last packet successfully received from the server was milliseconds ago, the last packet sent successfully to the server was milliseconds ago.
Recommendation: This parameter is set to default in the RDS 60S, usually when network conditions are poor, or if the client processes each block takes a long time, since net_write_timeout set too small result in connectivity easily, it is recommended to increase the size of the parameter;
Role: This parameter is used to determine the maximum internal memory temporary tables, each thread must be assigned (actual limiting effect is the minimum tmp_table_size and max_heap_table_size), if the in-memory temporary table exceeds the limit, MySQL will automatically put it converted to a disk-based MyISAM table, query optimization, when you want to avoid using a temporary table, if it can not be avoided, to ensure that these temporary tables exist in memory.
Phenomenon: If a complex SQL statement contains a group by / distinct and so can not use the index to optimize temporary table, then SQL execution time lead to lengthened.
Recommendation: If the application has a lot of group by / distinct and other statements, and the database has enough memory, you can increase the tmp_table_size (max_heap_table_size) value, in order to improve query performance.
RDS MySQL to add parameters
Here are a few of the more useful new RDS MySQL parameters.
Role: MySQL can be used for controlling the size of the temporary file, RDS initial default value is 10G, if the temporary file exceeds this size, it will cause application error.
Phenomenon: The table '/ home / mysql / dataxxx / tmp / # sql_2db3_1' is full.
Recommendation: if you need to analyze the cause temporary file to add SQL statements can be optimized by indexing or other means, and secondly to determine if enough space instance, you can enhance the value of this parameter, in order to ensure that SQL can be executed properly. Note that this parameter need to restart the instance;
Role: to control the buffer memory size TokuDB engine can be used, such as innodb_buffer_pool_size set to 1000M, tokudb_buffer_pool_ratio set to 50 (representing 50%), buffer memory size so tokudb engine tables can be used compared to 500M;
Recommendation: The default parameter is set to 0 in the RDS. If RDS is used tokudb engine, it is recommended to turn up the parameters, in order to improve access performance TokuDB engine table. This parameter adjustments need to restart the database instance.
Role: Query for controlling maximum execution time in MySQL if the parameter exceeds the set time, the query will automatically fail, the default is no limit.
Recommendation: If you want to control the execution time of SQL database, you can turn this parameter, in milliseconds.
Phenomenon: ERROR 3006 (HY000): Query execution was interrupted, max_statement_time exceeded
Role: The number of concurrent queries MySQL for control, such as the rds_threads_running_high_watermark the value is set to 100, allows simultaneous concurrent MySQL query is 100, more than the level of the query will be rejected out of the parameter used in conjunction with rds_threads_running_ctl_mode (default value select).
Recommendation: This parameter is often at big spike or concurrent use scenario, the database has a good protective effect.