Shulou(Shulou.com)06/01 Report--

Availability back_log

If the number of threads connected at the same time exceeds the max_connections, the excess will not immediately reject the connection, but will be placed on a stack waiting for the main thread to be processed, and connections exceeding the number of back_log will be rejected directly.

Max_connections

Sometimes instances are blocked by a large number of connections, and new ones cannot get in. At this time, you generally don't have to turn up this parameter, but consider looking at the causes of storm connections, and then see if there are long-term sleep connections, killing these connections.

Wait_timeout

This corresponds to the maximum duration of the sleep connection above. Generally, shortening this will better solve the above problems, but it also requires the actual business situation. You can't modify it hastily.

Max_user_connection

The maximum number of threads that a single user can connect at the same time

Innodb_file_per_table

Each table is stored as a separate file, which is convenient for backup or recovery. Be sure to open it.

Secure skip_name_resolve

When a pure IP connection is used in a production environment, enabling this parameter may reduce the overhead or latency of domain name resolution. And in this case, users created with domain names or hostnames may not be able to connect

Performance no-auto-rehash

When the mysql client connects, it does not take the initiative to obtain meta-information such as the database table of the instance. It is a slow operation with overhead and blocking risk.

Innodb_io_capacity

Tell the MySQL instance the performance of the local hard disk, and the instance will adjust some strategies for flushing the disk according to this parameter.

Innodb_max_dirty_pages_pct

Dirty page ratio, which is used to control the proportion of undropped data in IBP. If DML statements are frequent, the appropriate improvement here can improve performance to some extent. After all, brushing disk is a very slow operation.

Innodb_lock_wait_timeout

The lock wait timeout can be reduced appropriately to avoid holding data locks for long periods of time in junk transactions and blocking other request threads

Innodb_rollback_on_timeout

Whether to roll back the entire transaction after waiting for a timeout is recommended here. By default, only the last statement in the transaction is rolled back. I think this can reduce lock contention.

Internal_tmp_disk_storage_engine

The type of internal temporary table is recommended here as InnoDB, which overrides the coloring of the temporary table by the default engine.

Innodb_stats_on_metadata

It is recommended that you turn it off and do not collect the table information of the internal metadata table after turning it off. Of course, the implied condition is that the internal metadata information table is not out of stock.

Innodb_log_file_size

This refers to the size of the internal redo log file. If there is a requirement for the restart time of the instance, the file should be set smaller.

IBP correlation

This is a clich é, that is, IBP size, IBP instances, UPDATE_BUFFER,INSERT_BUFFER and so on. Just make it bigger appropriately.

Innodb_autoinc_lock_mode

Self-locking mode, for 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. It is recommended to change the parameter setting to 2, which means that all inserts use lightweight mutex locks (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).

Read RDS parameter tuning for reference

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.