Shulou(Shulou.com)05/31 Report--

In this issue, the editor will bring you what the best practices of MySQL parameter tuning are. The article is rich in content and analyzed and described from a professional point of view. I hope you can get something after reading this article.

Preface

In many cases, RDS users often ask how to tune the parameters of RDS MySQL. To answer this question, write a blog to explain:

Which parameters cannot be modified and which parameters can be modified?

Are these modified parameters already the best setting, and how to make good use of these parameters?

Which parameters can be changed?

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

Memory bottleneck: OOM occurs in the instance, which causes the master / slave to switch.

Number of connections bottleneck: applications cannot establish new connections to the database

It needs to be solved by application optimization, slow SQL optimization or elastic upgrade of instance specifications.

There are also some parameters related to the security of master and slave data, such as innodb_flush_log_at_trx_commit, sync_binlog, gtid_mode, semi_sync, binlog_format and so on. In order to ensure the security of master and slave data, they are not yet available to users for modification.

In addition to the above parameters, most of the parameters have been optimized by the DBA team and the source team, and users do not need to adjust the online parameters to run the database well. However, these parameters are only suitable for most application scenarios, and individual special scenarios still need to be treated individually, for example, when using the tokudb engine, you need to adjust the memory ratio (tokudb_buffer_pool_ratio) that the tokudb engine can use; or, for example, the characteristics of my application require a large lock timeout, so you need to adjust the size of the innodb_lock_wait_timeout parameters to adapt to the application, and so on.

How to adjust parameters

Next, I will introduce some of the more important parameters that can be modified in the console. If these parameters are not set properly, there may be performance problems or application errors.

Open_files_limit

Function: this parameter is used to control the number of file handles that a MySQL instance can open and use at the same time.

Reason: when the tables in the database (MyISAM engine tables need to consume file descriptors when being accessed, InnoDB engine will manage the open tables by itself-table_open_cache) when more and more tables are opened, the number of file handles assigned to each instance will be consumed. When RDS initializes the instance, the open_files_limit is set to 8192. When the number of tables opened exceeds this parameter, it will cause all database requests to report errors.

Phenomenon: if the parameter setting is too small, it can cause the application to report an error.

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

Suggestion: increase the value of open_files_limit. RDS can currently support a maximum of 65535. It is also recommended to replace the MyISAM storage engine with the InnoDB engine.

Back_log

Function: every time MySQL processes a connection request, it creates a new thread corresponding to it. During the creation of a new thread by the main thread, if the front-end application has a large number of short connection requests arriving at the database, MySQL will restrict new connections from entering the request queue, which is controlled by the parameter back_log. If the number of waiting connections exceeds back_log, new connection requests will not be accepted. So if you need MySQL to be able to handle a large number of short connections, you need to increase the size of this parameter.

Phenomenon: if the parameter is too small, it may cause the application to report an error

SQLSTATE [HY000] [2002] Connection timed out

Suggestion: increase the size of this parameter value. Note that the instance needs to be restarted. The default value of the initial initialization value of RDS is 50, but now the initialization value has been increased by 3000.

Innodb_autoinc_lock_mode

Function: after MySQL5.1.22, in order to solve the problem of self-increasing primary key locking table, InnoDB introduced the parameter innodb_autoinc_lock_mode, which is used to control the locking mechanism of self-increasing primary key. This parameter can be set to 0, 1, 2, and the default parameter value of RDS is 1, indicating that InnoDB uses lightweight mutex locks to acquire self-incrementing locks, replacing the original table-level locks, but in load data (including: INSERT … SELECT, REPLACE... Self-increment table locks are used in SELECT) scenarios, which may result in deadlocks for applications when importing data concurrently.

Phenomenon: if the application concurrently uses load data (including: INSERT … SELECT, REPLACE... SELECT) deadlock occurs 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 to change the parameter setting to 2, which means that lightweight mutex locks are used for all inserts (for row mode only), so that deadlocks in auto_inc can be avoided, and at the same time in INSERT. The performance will be greatly improved in the SELECT scenario (note that the format of the parameter set to 2jol binlog needs to be set to row).

Query_cache_size

Function: this parameter is used to control the memory size of MySQL query cache; if MySQL turns on query cache, each query execution will lock the query cache first, and then determine whether it exists in the query cache. If there is a direct return result, if it does not exist, then perform operations such as engine query. At the same time, operations such as insert, update and delete will invalidate query cahce, which also includes any changes in structure or index. The maintenance cost of cache failure is high, which will bring greater pressure on MySQL, so when our database is not updated so frequently, query cache is a good thing, but if vice versa, writes are very frequent and concentrated on several tables. Then the locking mechanism of query cache lock will cause frequent lock conflicts, and the writing and reading of this table will wait for query cache lock to unlock each other, resulting in a decline in the query efficiency of select.

Phenomenon: a large number of connection states in the database are checking query cache for query, Waiting for query cache lock, storing result in query cache

Recommendation: RDS disables query cache by default. If query cache is enabled in your instance, you can disable query cache; when the above occurs. Of course, in some cases, you can also enable query cache, such as using query cache to solve database performance problems.

Net_write_timeout

Purpose: the timeout for waiting for a block to be sent to the client.

Phenomenon: too small parameter setting may cause the client to report an error 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 60s by default in RDS. Generally, when the network condition is poor, or it takes a long time for the client to process each block, it is easy to break the connection because the net_write_timeout setting is too small. It is recommended to increase the size of this parameter.

Tmp_table_size

Function: this parameter is used to determine the maximum value of the internal memory temporary table, which is allocated by each thread (the minimum values of tmp_table_size and max_heap_table_size actually play a limiting role). If the memory temporary table exceeds the limit, MySQL will automatically convert it into a disk-based MyISAM table. When optimizing query statements, you should avoid using temporary tables, if it really cannot be avoided. Make sure that these temporary tables are stored in memory.

Symptom: if complex SQL statements include group by/distinct and so on that cannot be optimized by index and temporary tables are used, it will result in longer SQL execution time.

Suggestion: if there are many statements such as group by/distinct in the application, and the database has enough memory, you can increase the value of tmp_table_size (max_heap_table_size) to improve query performance.

New parameters for RDS MySQL

Here are some useful new parameters for RDS MySQL.

Rds_max_tmp_disk_space

Function: used to control the size of temporary files that can be used by MySQL. The initial default value of RDS is 10G. If the temporary file exceeds this size, it will cause the application to report an error.

Phenomenon: The table'/ home/mysql/dataxxx/tmp/#sql_2db3_1' is full.

Suggestion: first, you need to analyze whether the SQL statement that leads to the increase of temporary files can be optimized by index or other means. Secondly, if you determine that there is enough space for the instance, you can increase the value of this parameter to ensure that SQL can be executed normally. Note that this parameter requires a restart of the instance

Tokudb_buffer_pool_ratio

Function: it is used to control the amount of buffer memory that can be used by the TokuDB engine. For example, if the innodb_buffer_pool_size is set to 1000m and the buffer memory ratio is set to 50 (representing 50%), then the buffer memory that can be used by the table of the tokudb engine is 500m.

Recommendation: this parameter is set to 0 by default in RDS. If tokudb engine is used in RDS, it is recommended to increase this parameter to improve the access performance of TokuDB engine table. This parameter adjustment requires a restart of the database instance.

Max_statement_time

Function: it is used to control the maximum execution time of the query in MySQL. If the setting time of this parameter is exceeded, the query will fail automatically. The default is unlimited.

Suggestion: if you want to control the execution time of SQL in the database, you can enable this parameter (in milliseconds).

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

Rds_threads_running_high_watermark

Function: used to control the number of concurrent queries in MySQL. For example, setting the value of rds_threads_running_high_watermark to 100will allow MySQL to conduct 100concurrent queries at the same time. Queries that exceed the water level will be rejected. This parameter is used in conjunction with rds_threads_running_ctl_mode (the default value is select).

Suggestion: this parameter is often used in scenarios of second kill or large concurrency, which has a good protection effect on the database.

The above is the best practice of MySQL parameter tuning shared by the editor. If you happen to have similar doubts, please refer to the above analysis to understand. If you want to know more about it, you are welcome to follow the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.