MySQL server variable details (2)

Innodb_adaptive_flushing {ON | OFF} sets whether to allow the MySQL server to dynamically adjust the speed of flushing dirty pages in InnoDBbufferpool based ON the workload. The purpose of dynamically adjusting the write speed is to avoid IO activity.

Innodb_adaptive_flushing = {ON | OFF} sets whether to allow the MySQL server to dynamically adjust the rate of dirty pages in the InnoDBbufferpool based ON the workload. The purpose of dynamically adjusting the write speed is to avoid IO activity.

Innodb_adaptive_flushing = {ON | OFF}

Set whether to allow the MySQL server to dynamically adjust the dirty pages in the InnoDB buffer pool based on the workload. The purpose of dynamically adjusting the write speed is to avoid IO activity spikes. The default value is ON. The range is global. It can be used in option files and is a dynamic variable.

Innodb_adaptive_hash_index = {ON | OFF}

Set whether to enable the InnoDB adaptive hash index. The benchmark test results show that the adaptive hash index is not beneficial to all workloads. Therefore, you need to select a more appropriate value based on the test results in actual application scenarios. This feature is enabled by default and can be disabled by the command line option -- skip-innodb_adaptive_hash_index. The function is global and can be used in option files. It is a dynamic variable.

Innodb_additional_mem_pool_size = {2097152 .. 4294967295}

Set the internal pool size of the innodb Storage engine to store data dictionaries and other internal data structures, in bytes. The more tables there are, the larger the value of this parameter should be set. When InnoDB runs out of the memory pool space, it will apply for memory space from the operating system, warning information will be recorded in the error log. The default size is 8 MB. The function is global and can be used in option files. It is a non-dynamic variable.

Innodb_autoextend_increment = {1 .. 1000}

If the shared tablespace does not have any excess storage space, if it permits automatic growth, this variable can be used to set the size of the space for a single increase. The unit is MB and the default value is 8. When innodb_file_per_table is set to 1, InnoDB uses a separate tablespace file for each table, while the innodb_autoextend_increment variable does not affect the tablespace. The range is global. It can be used in option files and is a dynamic variable.

Innodb_autoinc_lock_mode = {0 | 1 | 2}

Set the lock model used to generate the "auto-increment (auto_increment field)" value. The acceptable values include 0, 1, and 2, which are used to represent the "traditional", "consecutive", and "interleaved" lock models respectively. The default value is 1. The function is global and can be used in option files. It is a non-dynamic variable.

Innodb_buffer_pool_instances = #

Sets the number of regions to separate the buffer pool of InnoDB. For a buffer pool with several GB of space, separating it into multiple regions can reduce the resource contention coefficient when different threads perform read/write operations on the cache page and enhance its concurrency. In the buffer pool, the selected region for reading or storing the page is random based on the hash algorithm. Each buffer pool manages its own idle list, list flushing, LRU, and other data structures related to the buffer pool, and uses their respective mutex locks for protection.

This variable can be used only when the value of innodb_buffer_pool_size is greater than 1 GB. The overall space of the buffer pool will be used separately by the buffer pool instances. For the purpose of optimal utility, we recommend that you use the innodb_buffer_pool_instances and innodb_buffer_pool_size variables to make each buffer pool instance have at least 1 GB of space. The function is global and can be used in option files. It is a non-dynamic variable.

Innodb_buffer_pool_size = #

Sets the memory buffer size for InnoDB cached table data and indexes, in bytes. The default value is 128 MB, and the maximum value depends on the CPU architecture. On a busy server, when the buffer pool (buffer pool) is greater than 1 GB, set innodb_buffer_pool_instances to be greater than 1 to improve the scalability. The greater the value of the innodb_buffer_pool_size variable, the less I/O required for the MySQL server to complete data access. Therefore, on a server with a large enough memory and dedicated for MySQL services, this value can be set to 80% of the physical memory. However, if the following conditions occur, we recommend that you reduce the value of this variable: (1) Memory Page switching due to insufficient physical memory resources; (2) InnoDB will buffer and control the structure (buffers and control structures) additional memory is reserved, so in fact the memory space occupied may be about 10% larger than the specified value, which cannot exceed the estimated memory resource allocation; (3) the memory address space must be consecutive, which may cause exceptions on Windows systems that use special address space based on DLL libraries; (4) the duration required for the buffer pool Initialization is proportional to the size of the specified space. For example, for a Linux system with a 10g buffer pool x86_64, the initialization time is about 6 seconds. The function is global and can be used in option files. It is a non-dynamic variable.

Innodb_change_buffering = #

When an INSERT, UPDATE, or DELETE operation is performed on a table, data in the index, especially in the second index, may not be stored in order, which may lead to random IO to complete the second Index UPDATE operation. This variable is used to set whether InnoDB enables change buffering and the type of modification buffer used. Modifying the buffer is an optimization method. It can convert IO operations to sequential modes by delaying write operations to the second index. The accepted values include inserts (buffer insert operation), deletes (buffer delete-marking operation), changes (buffer insert and delete-marking operation), and purges (buffer purge operation), all (insert, delete-marking, and purge operations are buffered), and none (no operation is buffered ). The default value is all. MySQL versions earlier than 5.5.4 only accept inserts and none. The range is global. It can be used in option files and is a dynamic variable.

Innodb_checksums = {ON | OFF}

InnoDB can use checksum to verify the integrity of all page data that is being read from the disk, thus improving fault tolerance for hardware or data file corruption. This feature is enabled by default. However, you may need to disable this feature in rare cases by using the -- skip-innodb-checksums command line option. The function is global and can be used in option files. It is a non-dynamic variable.

Innodb_commit_concurrency = {0 .. 1000}

Sets the number of "Submit" Operation threads that InnoDB can run simultaneously. 0 indicates no limit. This variable cannot be changed from "zero value" to "non-zero value" at runtime, but can be changed from a "non-zero value" to another value. The function is global and can be used in option files. It is a non-dynamic variable.

Innodb_concurrency_tickets = #

When a thread enters (enter) InnoDB, it will obtain a certain number of "free tickets" and use these scrolls to freely access InnoDB (that is, free of check ), until the scroll is exhausted; then the thread will be placed in the waiting queue and may need to accept the concurrency limit check again. This variable is used to set the number of concurrent threads that can enter InnoDB at the same time, that is, the "free scroll" Number of threads. The default value is 500. The range is global. It can be used in option files and is a dynamic variable.

Innodb_data_file_path = IBDATA_FILE

Specify the data files and their sizes of InnoDB. When there are more than one file, separate them with semicolons. The data file path can be a relative path, which is relative to the directory pointed to by the innodb_data_home_dir variable. The file size can be expressed as K (KB), M (MB), G (GB) but the total size of these files must be at least 10 MB. Without explicitly setting the innodb_data_file_path variable, the MySQL server will automatically create a data file named ibdata1 with an auto-increment initial size of 10 MB in the data directory. The maximum size of a single data file depends on the operating system, which means that the maximum size of a single file supported by the operating system can be used as the maximum size of the data file. InnoDB also supports using bare devices as data files. The function is global and can be used in option files. It is a non-dynamic variable.

Innodb_data_home_dir =/PATH/TO/DIR