MySQL some important configuration parameters of learning and finishing (iii)?

Original address: MySQL Some important configuration parameters of learning and finishing (iii)

Before MySQL some important configuration parameters of learning and finishing (a) and MySQL some important configuration parameters of learning and finishing (ii), for the online MySQL server configuration parameters are studied, but the official doc after the configuration of some of the parameters are not clearly understood, Today and colleagues to exchange and communication, are some important configuration exchange views, this article on some of the questions, to sort out the experience.

Skip-external-locking effect

in the release version of MySQL Linux, there is a default row of Skip-external-locking, which indicates that an external lock is skipped, and the external-locking variable indicates that an external lock is enabled, For multi-threaded conditions to lock the MyISAM data table, by default, MySQL is disabled externally locked, in real-world production, our business environment is a single-server environment, do not require external locking, so it is disabled.

Key_buffer_size is not valid when InnoDB engine

in a real-world production environment, we have upgraded our business libraries, although our business library uses the InnoDB engine, but there are still a few remaining tables that use the MyISAM storage engine, set This parameter is also used to provide access performance for these tables, and the indexes for these tables are better for processing read and multi-write operations. In addition, when MySQL 5.5 was installed on a previous windows, a few my.ini files of different production environments were generated by default, and the configuration of this parameter also included some of these configurations.

table_open_cache=64 settings are too small

The setup of this parameter is best set according to the real production environment, the MySQL command line through show global status like ' open%_tables% ', you can find two important parameters, as follows:

+---------------+-------+

| variable_name | Value |

+---------------+-------+

| Open_tables | 81 |

| Opened_tables | 88 |

+---------------+-------+

For most server settings, it is recommended to refer to the formula: Open_tables/opened_tables >= 0.85;open_tables/table_open_cache <= 0.95

But not set table_open_cache the bigger the better, because the table_cache increased, so that MySQL response to SQL faster, will inevitably generate more deadlock (dead Lock), which makes the database a whole set of operations slow down, Severely affect performance. So in peacetime maintenance or in accordance with the actual situation of the library to make judgments, to find the most suitable for your maintenance of the library's Table_open_cache value.

For details, see: Parameter Table_open_cache.

The Myisam_sort_buffer_size parameter is valid only for the MyISAM engine

In a realistic production environment, although our business library uses the InnoDB engine, there are several business tables using the MyISAM engine, which requires this parameter, the size of the buffer used for table recovery, and a reference to the configuration of MySQL 5.5.

Net_buffer_length default is 16K, set to 8K?

This parameter is based on the length of the client connection statement, the actual business is not complicated, the statement is relatively simple, refer to the configuration of MySQL 5.5, set net_buffer_length=8k, However, after discussing with colleagues, we decided to use the default value of 16K.

Thread_cache_size set to 20 basis

The setting of this parameter is related to Max_connections, max_connections represents the maximum number of concurrent client connections allowed, affects the number of threads running on the server, the default value is 151, and the Thread_cache_size setting , the official DOC suggested formula: 8+ (MAX_CONNECTIONS/100), which is also related to the reality of the business, when the server concurrency is large, you need to modify the value of Max_connections to meet business needs, in our real business, thread_cache_ The size is set to 20 more appropriate.

Innodb_flush_log_at_trx_commit Official Doc Construction do not modify default values

Although MySQL official Doc recommends setting Innodb_flush_log_at_trx_commit to 1, in real-world business, customers are very fast for business performance, and by default, 1 means that each time a transaction commits, The contents of the InnoDB log buffer are written to the log file, and the log files are flushed to disk. Setting to 2 reduces the flush disk operation, although transaction data may be lost in the case of a sudden breakpoint or system crash, but in the business scope, modifying this parameter is a significant improvement in business speed.

read_rnd_buffer_size increase ORDER BY query efficiency

    in what exactly is There is a bit of understanding in Read_rnd_buffer_size, which mentions Read_buffer_size, in the Three ways to optimize MySQL database queries probably understand the role of this parameter, when a query constantly scan a table, MySQL allocates a memory buffer for it. The read_buffer_size variable controls the size of this buffer. If you think that the continuous scan is going too slowly, you can improve its performance by increasing the value of the variable and the size of the memory buffer. However, it seems that these two parameters are values for the MyISAM table, in the MySQL installation directory my.ini see such a comment: Size of the buffer used for doing full table scans of MyISAM tables.

Innodb_thread_concurrency

Specifically, you can see here.

A little collation of MySQL thread_concurrency and innodb_thread_concurrency parameters , the specific configuration of this parameter and the actual effect will need to be discussed and tested.

MySQL some important configuration parameters of learning and finishing (iii)?