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

This article mainly explains "what are the MySQL system variables that affect database performance". The explanation in this article is simple and clear and easy to learn and understand. Please follow the editor's train of thought to study and learn "what are the MySQL system variables that affect database performance".

Bulk_insert_buffer_size

Bulk insert cache size, this parameter is for the MyISAM storage engine. It is suitable for improving efficiency when inserting 100-1000 + records at one time. The default value is 8m. It can be doubled according to the size of the amount of data.

Concurrent_insert

Concurrent insert, when there is no hole in the table (deleted records), when one process acquires the read lock, other processes can insert at the end of the table.

The value can be set to 0 to disallow concurrent insertion, 1 to perform concurrent insertion when there is no hole in the table, 2. Concurrent insertion is performed regardless of whether there are holes or not.

The default is 1. Set for the frequency at which the table is deleted.

Delay_key_write

Defer updating the index for the MyISAM storage engine. It means that when update records, first up the data to disk, but not up the index, store the index in memory, and write the memory index to disk when the table is closed. A value of 0 does not turn on, 1 turns on. It is on by default.

Delayed_insert_limit,delayed_insert_timeout,delayed_queue_size

Delay insertion, give the data to the memory queue first, and then insert it slowly. However, these configurations are not supported by all storage engines. At present, the commonly used InnoDB does not support it, but MyISAM supports it. According to the actual situation, it is generally enough by default.

Expire_logs_days

Automatically delete logs that exceed the specified number of days. The recommended value is 0, which means "do not delete automatically".

Flush,flush_time

Whether it is enabled or not, synchronize table data to disk. And the interval between automatic synchronization.

For flush_time, it is officially recommended that you use this option only on Windows9x or Me, or on systems with minimal resources. Therefore, it is recommended that it be closed.

Ft_boolean_syntax,ft_max_word_len,ft_min_word_len,ft_query_expansion_limit,ft_stopword_file

Full-text search feature for parameters set by MyISAM. If you don't use FULLTEXT indexes, you don't need to optimize them. See the mysql reference manual for details.

Join_buffer_size

The cache size used for inter-table associations (join). It is recommended to set it to 131072. (128K)

What are the MySQL system variables that affect database performance

Key_buffer_size

Index block cache size, for MyISAM storage engine, the higher the value, the better the performance. But exceeding the maximum value that the operating system can withstand will make mysql unstable.

If it is not a MyISAM storage engine, it is generally set to a size of 4-32m.

Key_cache_age_threshold,key_cache_block_size,key_cache_division_limit

Settings related to the key value cache. It needs to be tuned to the actual situation. Only for the MyISAM storage engine.

Large_pages

Whether to start large page support. It means that some caches can allocate more space. This feature has been supported by common storage engines such as InnoDB,MyISAM.

Low_priority_updates

Low priority update. This means that all write operations (table write locks), including update,delete,insert, etc., need to wait for the read operation to complete (table read locks are unlocked).

Because it is a table lock, only MyISAM is supported here.

Max_write_lock_count

Maximum number of write locks. The meaning of this variable is that when a certain number of write locks are reached, read locks are not restricted and some read locks are allowed to enter. (you can read the data, otherwise you will have to wait for the write lock to be released before you can read it)

Because it is a table lock, only MyISAM is supported here.

Preload_buffer_size

The size of the buffer allocated when the index is overloaded, which only supports MyISAM.

Read_buffer_size,read_rnd_buffer_size

The size (in bytes) of the buffer allocated for each table scanned when each thread scans continuously. If you perform multiple consecutive scans, you may need to increase this value, which defaults to 131072.

Sort_buffer_size

The size of the buffer allocated by each sort thread. Increase this value to speed up ORDERBY or GROUPBY operations. The default value is 2097144 (2m), which is recommended to increase to 16777208 (16m).

Timed_mutexes

Displays the statistics of mutexes, and turns off OFF by default

Tmp_table_size

The size of the temporary table is performed in memory before the size is exceeded, and when it is exceeded, the mysql is automatically converted to the hard disk.

Thank you for your reading, the above is the content of "what are the MySQL system variables that affect the performance of the database?" after the study of this article, I believe you have a deeper understanding of what the MySQL system variables that affect the performance of the database are, and the specific use needs to be verified in practice. Here is, the editor will push for you more related knowledge points of the article, welcome to follow!

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.