MySQL System variables that affect database performance

Which of the following MySQL System variables will affect the database performance? The following lists some MySQL System variables that affect the database performance. We hope you can have a better understanding of MySQL system variables.

Bulk_insert_buffer_size

Batch insert cache size. This parameter is for the MyISAM storage engine. this method improves the efficiency when more than 100-1000 records are inserted at a time. the default value is 8 Mb. double the data size.

Concurrent_insert
Concurrent insert. When the table has no holes (records deleted), other processes can insert the read locks at the end of the table when a process obtains the read locks.
The value can be set to 0 and cannot be inserted concurrently. 1. When the table has no holes, concurrent insertion is performed. 2. Concurrent Insertion is performed regardless of whether there are holes.
The default value is 1. Set the table deletion frequency.

Delay_key_write

For the MyISAM storage engine, Index Update is delayed. this means that when the update record is updated, the data is first up to the disk, but the index is not up, and the index is stored in the memory. When the table is closed, the memory index is written to the disk. if the value is 0, it is disabled. If the value is 1, it is enabled. enabled by default.

Delayed_insert_limit, delayed_insert_timeout, delayed_queue_size
Delayed insertion: The data is first handed over to the memory queue and then inserted slowly. however, not all storage engines support these configurations. Currently, common InnoDB does not support these configurations, and MyISAM does. increase the value according to the actual situation. Generally, it is enough by default.

Expire_logs_days

Automatically delete logs that have exceeded the specified number of days. It is recommended that the value be 0, indicating "not automatically deleted ".

Flush, flush_time

Whether to enable, synchronize table data to disk, and the automatic synchronization interval.
For flush_time, we recommend that you use this option only in Windows 9x or Me, or in systems with minimal resources. Therefore, we recommend that you disable this option.

Ft_boolean_syntax, ft_max_word_len, ft_min_word_len, ft_query_expansion_limit, ft_stopword_file

Full-text search features for parameters set for MyISAM. If you do not use FULLTEXT indexes, you do not need to optimize them. For details, see the mysql reference manual.

Join_buffer_size

Cache size used for table join. It is recommended to set it to 131072. (128 K)

Key_buffer_size

The size of the index block cache area. For the MyISAM storage engine, the larger the value, the better the performance. However, exceeding the maximum value that the operating system can afford will make mysql unstable.
If it is not the MyISAM storage engine, it is generally set to 4-32 m.

Key_cache_age_threshold, key_cache_block_size, key_cache_division_limit

Key-value cache settings. You need to optimize the settings based on the actual situation. It is only for the MyISAM storage engine.
 
Large_pages

Whether to enable support for large pages. This means that you can allocate more space to some caches. This feature has been supported by common storage engines such as InnoDB and MyISAM.

Low_priority_updates

Low-priority update means that all write operations (table write locks), including update, delete, and insert, must be executed after the read operation is completed (the 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. this variable means that when a certain number of write locks are reached, the read locks are not restricted and some read locks are allowed to enter. (You can read the data. Otherwise, you must wait for the write lock to be released before reading the data)

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

Preload_buffer_size

The size of the buffer allocated when the index is reloaded. This variable only supports MyISAM.

Read_buffer_size, read_rnd_buffer_size

The size (in bytes) of the buffer allocated to each table scanned during continuous scanning by each thread ). If you perform multiple consecutive scans, you may need to add this value. The default value is 131072.

Sort_buffer_size

The size of the buffer allocated by each sort thread. Adding this value can speed up the order by or group by operation. The default value is 2097144 (2 M). We recommend that you increase it to 16777208 (16 M ).

Timed_mutexes

Displays mutexes statistics. OFF is disabled by default.

Tmp_table_size

The temporary table size is in the memory before it is exceeded. When it is exceeded, mysql automatically converts it to the hard disk.

Difference between NULL and MySQL NULL strings

In-depth study of MySQL result strings

MySQL string truncation Function Method

MySQL string segmentation implementation

In-depth understanding of MySQL string comparison functions