MySQL 5.5 server variables (2)

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} determines 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} sets the lock model used to generate the "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= # Set 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= # Set 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, setting innodb_buffer_pool_instances to be greater than 1 can 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 sequence, this may trigger random IO to update the second index. 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 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 specifies the data files and their sizes of InnoDB. When there are more than one file, they are separated by 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/DIRInnoDB directory PATH of all data files in the shared tablespace. The default value is the data directory of MySQL. You can set the value of this variable to null, and then use the absolute path for each data file in innodb_data_file_path. This variable does not affect the data files in each table space when innodb_file_per_table is enabled. The function is global and can be used in option files. It is a non-dynamic variable. Innodb_doublewirte= {ON | OFF} sets whether InnoDB uses dual-write buffering. Enabled by default. InnoDB uses dual-write buffer for partial page writing to prevent data corruption. A dual-write buffer is a special reserved area in a tablespace, and its size is enough to accommodate 100 pages in a continuous interval. When InnoDB writes pages from the buffer pool to the disk, it first refreshes these pages to the dual-write buffer, and then saves them to the real target location. Therefore, the dual-write buffer is essentially a backup of the recently written pages, which ensures the atomicity and sustainability of each write. In some cases, dual-write buffering is unnecessary, for example, it can be disabled on the slave server. In addition, some file systems (such as ZFS) also implement this function, therefore, InnoDB does not need to do repetitive work. The function is global and can be used in option files. It is a non-dynamic variable. Innodb_fast_shutdown= {0 | 1 | 2} sets the InnoDB close mode. In the acceptable values, "0" indicates slow shutdown, which means that the full purge and insert buffer merge operations will be completed before InnoDB is disabled; "1" is the default value. It indicates that InnoDB will skip these operations in Mode 0 when it is disabled. This is also why it is called "Quick Close; "2" indicates that InnoDB only writes log information and closes the cold (cold) statement. At this time, no transaction is lost, it only takes a long time to start the MySQL service for fault recovery (crash recovery ). When a slow shutdown is executed, the process may last for several minutes. In some extreme cases, for example, in scenarios with a large amount of data buffering, the duration of this process may be measured in hours. In general, you only need to disable it slowly when upgrading the MySQL main version so that the data file can be properly adapted to the new version. Generally, you can set the value of this variable to 2 only in case of an emergency or for debugging purposes, so that data in the risk of potential damage can be disabled as quickly as possible. The range is global. It can be used in option files and is a dynamic variable. Innodb_file_format= {Antelope | Barracuda} sets the file format for creating an InnoDB table. The acceptable parameters include Antelope and Barracuda, but this only affects each table space File Based on the innodb_file_per_file variable. Some InnoDB features, such as table compression, are only supported by the Barracuda file format. The range is global. It can be used in option files and is a dynamic variable. Innodb_file_format_check= {ON | OFF} is used to set whether InnoDB checks the file format tags of the shared tablespace when the MySQL server starts. When the tag is checked, if it is higher than the capabilities supported by the current InnoDB version, InnoDB will generate an error and refuse to start. Otherwise, for MySQL 5.5.5 and later versions, InnoDB will set the value of the variable innodb_file_format_max to the file format label of the shared tablespace. For versions earlier than MySQL 5.5.5, innoDB sets the file format of the shared tablespace to the value of innodb_file_format_check. The function is global and can be used in option files. It is a non-dynamic variable. Innodb_file_format_max= {Antelope | Barracuda} when the MySQL service is started, InnoDB sets the value of the innodb_file_format_max variable to the file format label of the shared tablespace (for example, Antelope or Barracuda ). If the MySQL server creates or opens a table with a more advanced format, the value of this variable is set to the more advanced format. The range is global. It can be used in option files and is a dynamic variable. Innodb_file_per_table= {ON | OFF} specifies whether the InnoDB table uses the data files (ending with. ibd) in each table space to store the data and indexes of each table. If data files in each table space are used, the system tablespace (that is, the shared tablespace) is no longer used ). Some features of InnoDB tables, such as compressed tables, only take effect for each table space. The range is global. It can be used in option files and is a dynamic variable. Innodb_flush_log_at_trx_commit= {0 | 1 | 2} set the method in which InnoDB synchronizes log buffer data to log files and writes log files to disks. In the acceptable value, "0" indicates that the log buffer is written to the log file once per second, and the log file is flushed to the disk at the same time, but no action is taken when the transaction is committed; "1" is the default value, indicating that the log buffer is written to the log file when a transaction is committed, and the log file is flushed to the disk; "2" indicates that each transaction commits or writes the log buffer to the log file once per second, but does not perform the fl operation on the log file at the same time. Of course, due to the scheduling of the operating system process, the number of log writes or writes per second cannot be 100% guaranteed. In ACID-compatible scenarios, the variable value must be set to 1. Because the log write operation for each transaction is to be executed, it will block I/O calls until the write operation is completed, therefore, InnoDB significantly reduces the number of transactions that can be committed per second. Setting "2" provides better performance than "1", and data in the last second is lost only when the operating system crashes. Therefore, data security is also good. If it is set to "0", data may be lost in the last second of the transaction. Therefore, the data security of the entire transaction cannot be guaranteed, but it usually has the best performance. To ensure the transaction persistence and consistency of InnoDB replication in the Maximum program, set innodb_flush_log_at_trx_commit = 1 and sync_binlog = 1. However, it should be noted that some disks also have caches, which may bring additional potential risks to transaction operations. You can use hdparm or a vendor's own tool to disable the disk's own cache. Of course, the best configuration for high-performance transactions is to set the value of this variable to 1, and put the log file on the RAID with the backup battery write cache. The range is global. It can be used in option files and is a dynamic variable. Innodb_flush_method= {O_DSYNC | O_DIRECT} sets the actual interaction mode between InnoDB and the file system. In addition to write operations, it also affects how InnoDB reads data. When the value of the innodb_flush_method variable is set to O_DSYNC, InnoDB uses the O_SYNC flag to open and write log files, and uses fsync () to write data files. O_SYNC will synchronize all write operations, that is, the data will be returned only after the data is written to the disk, but will not prohibit caching at the operating system level. Therefore, it will not avoid double buffering, and does not directly write data to the disk. Fsync () simultaneously refreshes data and metadata (while fdatasync () only writes data), which generates more IO operations than fdatasync, in addition, the operating system caches some data in its own cache (this will lead to dual buffering ). For example, if the file system can intelligently handle I/O requirements, double buffering may not be a bad thing, but if MySQL sets the innodb_file_per_table variable to 1, this will cause the first tablespace file to use the fsync () function separately, and its write operations will not be merged. When the value of the innodb_flush_method variable is set to O_DIRECT, InnoDB uses the O_DIRECT flag to open the data file and fsync () to write data and log files. The O_DIRECT flag will cause the operating system to neither cache data nor pre-read data. It completely disables the cache of the operating system and causes all read/write operations to go directly to the storage device, avoiding double buffering. However, it cannot prohibit the caching and pre-reading functions of hardware (such as raid cards, in addition, enabling the hardware-level cache and pre-read functions is the only way to ensure good performance when InnoDB uses the O_DIRECT flag. The function is global and can be used in option files. It is a non-dynamic variable. Innodb_force_load_initupted= {ON | OFF} sets whether InnoDB will load the table marked as "corrupted upted" at startup. This feature should only be enabled in the scenario of troubleshooting to fix inaccessible tables. After the troubleshooting task is complete, disable this feature and restart the MySQL service. The function is global and can be used in option files. It is a non-dynamic variable. Innodb_force_recovery= {0 | 1 | 2 | 3 | 4 | 5 | 6} sets the InnoDB fault recovery mode. When "page corruption uption" occurs in InnoDB, most of the data is still intact, so you can use the SELECT... into outfile command to back up the data to reduce the loss. However, some "corrupt" faults may cause the SELECT * FROM tbl_name command to fail to be executed, the InnoDB background operation to crash, or even the InnoDB rollback operation. In this case, you can use the innodb_force_recovery variable to force the InnoDB Storage engine to not perform background operations at startup, so that data can be backed up. In the acceptable value of innodb_force_recovery, "0" is the default value, indicating that the "forced Repair" mode is not enabled after the normal start. A non-zero value contains all its smaller values. However, it may cause more damage to the B-tree index and other data structures. Therefore, when the value of this variable is non-zero, it will prevent users from using the INSERT, UPDATE, or DELETE operation, but will allow the execution of SELECT, create table, or drop table operations. The following describes other non-zero value functions: 1 (srv_force_ignore_0000upt): MySQL service is run as usual even if the page is damaged, when the SELECT * FROM tbl_name statement is executed, it will try to skip corrupt index records and pages. 2 (SRV_FORCE_NO_BACKGROUND): Do not start the master thread. It will prevent crash when executing the purge operation ). 3 (SRV_FORCE_NO_TRX_UNDO): After the fault recovery (crash recovery), no transaction rollback is performed. 4 (SRV_FORCE_NO_IBUF_MERGE): The insert buffer merge operation is prohibited. 5 (SRV_FORCE_NO_UNDO_LOG_SCAN): when starting the Database Service, InnoDB does not check the undo logs. As a result, InnoDB considers unfinished transactions as committed. 6 (SRV_FORCE_NO_LOG_REDO): do not perform the redo log rollback operation. In this case, only SELECT * FROM tbl_name operations without WHERE, order by, or other clauses can be executed, because the complex query will stop and exit when the damaged data structure is encountered. Innodb_io_capacity= # Set the maximum number of I/O operations that can be performed by InnoDB background tasks (such as flushing pages from the buffer pool or merging and modifying data in the buffer pool. The minimum value is 100, and the default value is 200. The maximum value depends on the CPU architecture. For a server with a large I/O load, you should specify a larger value for it to perform background maintenance tasks better and faster. However, in practice, the value of this variable should be as close as possible to the number of I/O operations (IOPS) performed by the MySQL server per second ), it is even as low as the minimum target to avoid affecting the execution of background tasks. If this value is too high, data is frequently moved from the buffer, which reduces the effectiveness of the cache pool in improving system performance. A single 5400RPM or 100 rpm disk can only complete about 100 IOPS, so this variable value should be reduced to in this case; for scenarios with multiple disks or better performance storage devices (such as solid state disks), you can increase the value of this variable as needed. The range is global. It can be used in option files and is a dynamic variable. Innodb_large_prefix= {ON | OFF} indicates whether an index prefix greater than 767 bytes can be used for InnoDB tables in DYNAMIC or COMPRESSED row format. However, to create a table of this type, you also need to set the innodb_file_format value to barracuda and the innodb_file_per_table value to true. At the same time, this setting does not work for the index length limitation of tables in the REDUNDANT and COMPACT row formats. The range is global. It can be used in option files and is a dynamic variable. Innodb_lock_wait_timeout= {1 .. 1073741824} sets the maximum wait time for a transaction in InnoDB to attempt to access a row locked by another InnoDB Transaction, in seconds. The default value is 50. When a timeout occurs, InnoDB returns an error message of the 1205 type and performs a rollback operation on the current statement (not the entire transaction; if you need to roll back the entire transaction in this case, you need to use the -- innodb_rollback_on_timeout option when starting the MySQL service. For OLTP systems or programs with many interactive applications, this variable value should be lowered so that users can quickly obtain feedback information, or, the system submits the update operation to the queue for further processing. For scenarios with many batch processing applications, such as data warehouses, the value of this variable should be added to wait for other large insert or update operations to complete. This variable only applies to InnoDB row locks. MySQL table locks are not implemented in InnoDB, so this timeout period does not affect table locks. Moreover, InnoDB can immediately detect the occurrence of a deadlock and perform a rollback operation on one of the TRIM tasks. Therefore, the timeout duration should not be used for deadlocks. The range is global or session level. It can be used in option files and is a dynamic variable. Innodb_locks_unsafe_for_binlog= {ON | OFF} determines whether the InnnoDB uses gap locking in search and index scanning ). InnoDB uses row-level locking (row-level locking). Generally, InnoDB uses "next-key locking" in the row lock mechanism for searching or scanning indexes) to lock the gap between an index record and its front, so as to block other users from inserting other index records before the index record. From this perspective, row-level locks are also called index-record locks ). By default, the value of this variable is OFF, meaning that non-security locks are prohibited, that is, the gap lock function is enabled. Setting it to ON indicates that the gap before the index record is disabled, that is, the gap lock is disabled. InnoDB only uses the index-record lock for index search or scanning. However, this does not prevent InnoDB from using a gap lock when performing a foreign key constraint check or duplicate key check. Enabling innodb_locks_unsafe_for_binlog is similar to setting the MySQL transaction isolation level to READ-COMMITTED, but they are not exactly the same: innodb_locks_unsafe_for_binlog is set globally and can only be set at service startup, the transaction isolation level can be set globally and inherited by the session level. However, the session level is also adjusted as needed at runtime. Similar to the READ-COMMITTED transaction isolation level, enabling innodb_locks_unsafe_for_binlog also brings about "phantom problem", but it also brings the following features: (1) for an UPDATE or DELETE statement, InnoDB only locks the row to be updated or deleted. The lock applied to the row that cannot be matched by the WHERE condition will be released after the condition check. This effectively reduces the likelihood of deadlocks. (2) When an UPDATE statement is executed, if a row is locked by another statement, InnoDB starts a "semi-consistency (semi-consistent) "read operations get this row from the latest MySQL version submitted and determine whether it can match the WHERE condition of the current UPDATE. If it can match, MySQL will lock it again, and if there are other locks, you need to wait for them to exit. Innodb_log_buffer_size= {262144 .. 4294967295} set the size of the log buffer that InnoDB uses to assist in log file write operations. The unit is byte. The default value is 8 MB. Large transactions can use a larger log buffer to avoid writing data in the log buffer before the transaction is completed, so as to reduce I/O operations and improve system performance. Therefore, we recommend that you set a larger value for this variable in scenarios with large transactions. The range is global. It can be used in option files and is a non-dynamic variable. Innodb_log_file_size= {108576 .. 4294967295} set the size of each log file in the log group. The unit is byte and the default value is 5 MB. The wise value range is from 1 MB to 1/n of the cache pool size, where n indicates the number of log files in the log group. The larger the log file, the fewer checkpoint write operations that need to be performed in the cache pool, which means that fewer I/O operations are required. However, this will lead to slow fault recovery. The range is global. It can be used in option files and is a non-dynamic variable. Innodb_log_files_in_grouP = {2 .. 100} sets the number of log files in the log group. InnoDB uses these log files cyclically. The default value is 2. The range is global. It can be used in option files and is a non-dynamic variable. Innodb_log_group_home_dir =/PATH/TO/DIR sets the directory for storing InnoDB redo log files. When all variables related to InnoDB logs are used by default, two log files, ib_logfile0 and ib_logfile1, are created in the data directory by default. The range is global. It can be used in option files and is a non-dynamic variable. Innodb_max_dirty_pages_pct = {0 .. 99} sets the upper limit of the dirty page ratio in the InnoDB buffer pool. The default value is 75. When the ratio of dirty pages in the cache pool is close to or reaches the ratio defined by this variable, the InnoDB main thread will fl some dirty pages to the corresponding file. The range is global. It can be used in option files and is a dynamic variable. Innodb_max_purge_lag = {0 .. 4294967295} the InnoDB Transaction System maintains a list of transactions with an index record added with the delete-marked mark (delete-marked). The length of this list is purge_lag ). This variable is used to set the delay in INSERT, UPDATE, or DELETE operations when the queue length is large. When puge_lag exceeds innodb_max_purge_lag, these operations will be delayed (purge_lag/innodb_max_purge_lag) * 10)-5 milliseconds. The default value is 0, indicating that these operations are never delayed. The operation latency is calculated at the beginning of the purge operation and is re-calculated every 10 seconds. For historical reasons, there is no operation delay when the purge operation cannot be started. The range is global. It can be used in option files and is a dynamic variable. Innodb_mirrored_log_groups = # set the number of log group images. The value should be 1. Innodb_old_blocks_pct = {5 .. 95} InnoDB manages the cache pool in a "list" structure and performs maintenance using the modified LRU algorithm. When you need space to save the new block, InnoDB cleans up the blocks that are least recently used and adds the new blocks to the list. The "midpoint insertion policy" lists the entire list as two sublists: The first part of the list is the list of new/young blocks that have been recently accessed, the backend is the list of old blocks that are rarely accessed recently. The LRU algorithm and the Central Point Insertion policy are used to ensure that recently accessed blocks are placed in the new block sublist. Newly read InnoDB blocks are placed before the old block sublist, remove the blocks from the old blocks sub-list as needed. An old block that is accessed again will be moved to the header of the new block sublist. The table scan operation may read a large number of data blocks into the cache pool at a time and may cause a large number of old blocks to be removed. This variable is used to set the proportion of the length of the entire list that is regarded as the length of the old sub-list. The default value is 37, that is, 3/8 of the cache pool. The range is global. It can be used in option files and is a dynamic variable. Innodb_old_blocks_time = # used to set the time (in milliseconds) for an old block in the old block sublist in the buffer pool when it is accessed for the first time) to be transferred to the new sublist. The default value is 0, which means to transfer to the new sublist immediately, even if it is just transferred to the old sublist. The non-zero value clearly defines the length of time when the block in the old block list needs to be transferred at least in the old block sublist when it is accessed for the first time. This variable is usually used in combination with innodb_old_blocks_pct. The range is global. It can be used in option files and is a dynamic variable. Innodb_open_files = # sets the maximum number of. ibd tablespace files that can be opened simultaneously by MySQL. This variable takes effect only when multiple tablespace files are used. The minimum value is 10 and the default value is 300. The limitation of this variable is only applied to the. ibd file of the InnoDB table. It has nothing to do with the MySQL server option -- open-files-limit, and does not affect table cache operations. The range is global. It can be used in option files and is a non-dynamic variable. Innodb_purge_batch_size = {1 .. 5000} purge refers to the operation to synchronize dirty pages in the cache pool to persistent storage devices, in units of redo log records. This variable is used to define the granularity of the write-clear operation, that is, how many redo log records can be combined to trigger a write-clear operation. The default value is 20. This variable is usually used for performance optimization with innodb_purge_threads = 1, but does not need to be modified in general scenarios. The range is global. It can be used in option files and is a non-dynamic variable. Innodb_purge_threads = {0 | 1} sets the number of threads for InnoDB to perform the clean write operation. The default value is 0, indicating that the clear write operation is completed by the InnoDB main thread, which can reduce the probability of internal resource competition and enhance the MySQL service scaling capability. However, as various kinds of competition inside InnoDB grows, the performance advantage brought by such settings is hardly worth mentioning. The range is global. It can be used in option files and is a dynamic variable. Innodb_read_ahead_threshold = {0 .. 64} sets the linear pre-read sensitivity between the InnoDB pre-read page and the buffer pool, that is, InnoDB read operations should at least be performed from one disk partition (extent, including 64 pages) when reading multiple pages, an asynchronous read operation is initiated for subsequent pages in the entire disk. The default value is 56. The range is global. It can be used in option files and is a dynamic variable. Innodb_read_io_threads = {1 .. 64} sets InnoDB as the number of I/O threads started by the read operation. The default value is 4. The range is global. It can be used in option files and is a non-dynamic variable. Innodb_replication_delay = {0 .. 4294967295} sets the duration of the replication thread delay when the number of threads running on the slave server (slave) reaches the concurrency defined by the innodb_thread_concurrency variable. The default value is 0, indicating no delay. The range is global. It can be used in option files and is a dynamic variable. Innodb_rollback_on_timeout = {ON | OFF} is used to set the timeout mode for current affairs rollback during transaction execution. In MySQL 5.5, the default value is OFF, indicating that only the last statement in the transaction is rolled back. If it is set to ON, the transaction is aborted and the entire transaction is rolled back. The range is global. It can be used in option files and is a non-dynamic variable. Innodb_rollback_segments = {1 .. 128} sets how many rollback segments (rollback segment) InnoDB uses for each transaction in the system tablespace. The default value is 128. If a small number of rollback segments can improve system performance, the value of this variable should be reduced. The range is global. It can be used in option files and is a dynamic variable. Innodb_spin_wait_delay = {0 .. 4294967295} spin (spin) is a waiting operation that allows you to check whether a resource changes to a usable state through continuous testing. It is used in scenarios where you only need to wait for a short period of time to wait for the required resources. Using a spin-free loop to wait for resources is much more efficient than using context switching to transfer threads to sleep. However, if it still cannot obtain resources after a short time, it will still be transferred to the second resource waiting method. This variable is used to define the number of idle cycle revolutions for InnoDB spin operations. The default value is 6. The range is global. It can be used in option files and is a dynamic variable. Innodb_stats_method = {nulls_equal | nulls_unequal | null_ignored} sets MySQL as the InnoDB table to process NULL data when collecting statistics on distributed index values. There are three acceptable values. null_equals means to consider all NULL values as the same, and create a value group for it to save the number of NULL class values; nulls_unequal means to treat all NULL values as different, and create a value group with a size of 1 for each NULL; nulls_ignored indicates that all NULL values are ignored. These methods used to generate table statistics will affect how the optimizer selects indexes for executing queries. The range is global. It can be used in option files and is a dynamic variable. Innodb_stats_on_metadata = {OFF | ON} sets whether InnoDB updates STATISTICS when the show table status or show index metadata statements are used, or when the TABLES or statistics table in INFORMATION_SCHEMA is accessed. The default value is update. Disabling this function can accelerate access to a database with a large number of tables or indexes, or improve the stability of the execution plan for queries on InnoDB tables. The range is global. It can be used in option files and is a dynamic variable. Innodb_strict_mod = {ON | OFF} InnoDB provides the so-called strict mode to avoid ignoring errors in SQL statement writing or syntax or ignoring unintentional combinations of operation modes and SQL statements. In strict mode, once the preceding problem occurs, InnoDB will generate an error instead of warning and a series of specific processing operations. This parameter is used to define whether to enable the strict mode of InnoDB. The default value is OFF. Innodb_support_xa = {TRUE | FLASE} the transaction of the storage engine is assigned the ACID attribute within the storage engine. A distributed (XA) transaction is a high-level transaction, it uses the "prepare" and "Submit" (prepare-then-commit) two-step method to extend the ACID attribute to the external storage engine, or even the external database. However, the "prepare" phase will result in additional disk write operations. XA requires the Transaction Coordinator, which notifies all participants to prepare for committing the transaction (Phase 1 ). When the Coordinator receives a "ready" Message from all participants, it instructs all participants to perform real "Submit" operations. This variable is used to define whether InnoDB supports two-segment commit distributed transactions. It is enabled by default. In fact, all MySQL servers that enable binary logs and support multiple threads to write data to binary logs at the same time must enable distributed transactions. Otherwise, multiple Threads may write binary logs in a different order from the original one, this will create different raw data results in binary log-based recovery operations or from the server. Therefore, this function should not be disabled in other application scenarios except that only one thread can change data. In applications where only one thread can modify data, disabling this function is safe and can improve the performance of InnoDB tables. The range is global and session level. It can be used in option files and is a dynamic variable. Innodb_sync_spin_loops = {0 .. 4294967295} set a thread to spin before InnoDB releases a mutex. When the spin operation reaches the mutex but the mutex is not released, the thread will be suspended. The default value is 30. The range is global. It can be used in option files and is a dynamic variable. Innodb_table_locks = {ON | OFF} InnoDB supports row-level locks at the storage engine level, while MySQL supports table-level locks at the server level. This variable is used to define whether InnoDB supports MySQL table-level locks within it. The default value is 1 or ON, indicating that if the autocommit variable value is 0 (that is, automatic submission is prohibited ), the explicit use of the lock tables Statement on the InnoDB table causes InnoDB to LOCK the table within the storage engine. Using 0 or OFF means explicit use of locks table... WRITE statements do not affect the storage engine level, but lock tables... WRITE or lock tables... the READ Statement is still affected. The range is global and session level. It can be used in option files and is a dynamic variable. Innodb_thread_concurrency = {0... 1000} sets the maximum number of operating system threads that InnoDB can run concurrently within it. The extra threads will be placed in the FIFO queue for waiting, and will not be counted into the number of concurrent running threads. For scenarios that do not need to be used, the ideal value depends on the hardware environment and workload. It is generally recommended that the number of CPUs be twice the number of disks. The default value is 0, indicating that there is no upper limit (no check for the number of concurrent threads), which means InnoDB can use any number of concurrent threads as needed, the queries inside INNODB and queries in queue counters in show engine InnoDB STATUS are disabled. The range is global. It can be used in option files and is a dynamic variable. Innodb_thread_sleep_delay = # sets the InnoDB thread's sleep duration before it is added to the InnoDB queue. The unit is milliseconds. The default value is 10000. The value 0 indicates that sleep is disabled and the queue is directly added. The range is global. It can be used in option files and is a dynamic variable. Innodb_use_native_aio = {ON | OFF} sets whether InnoDB uses the Linux asynchronous I/O subsystem. Therefore, InnoDB is only applicable to Linux systems and cannot be changed after MySQL is started. InnoDB enables this function by default. If InnoDB cannot be started properly due to Linux's asynchronous I/O subsystem, you can set this variable to OFF and restart it in the option file. In fact, even if the variable value is ON, if the MySQL service detects potential problems such as the joint temporary directory path, tmpfs file system, and Linux kernel does not support using the AIO mechanism on tmpfs, the variable is automatically disabled. The range is global. It can be used in option files and is a non-dynamic variable. Innodb_use_sys_malloc = {ON | OFF} sets whether InnoDB uses the operating system's (ON) or its own (OFF) memory distributor. The default value is ON. Innodb_version = STRINGInnoDB storage engine version, read-only variable. Innodb_write_io_threads = {1 .. 64} sets the number of I/O threads used by InnoDB to complete write operations. The default value is 4. Appendix: InnoDB data dictionary refers to tracking metadata information of InnoDB-related objects, such as tables, indexes, or fields in tables, the metadata is stored in the system tablespace of InnoDB ). Historical reasons: it overlaps with some data in the. frm file. Reference link http://dev.mysql.com/doc/refman/5.5/en/innodb-parameters.html author original, open source documentation. If you understand or have any mistakes, please discuss them, GROUP: 279599283. Reprint please be sure to keep this link, http://mageedu.blog.51cto.com.

This article is from the "Marco Education" blog, please be sure to keep this source http://mageedu.blog.51cto.com/4265610/1062628