MySQL 5.5 server variable details two

Add by Zhj: In the MySQL5.6 of some parameters have been increased, see http://dev.mysql.com/doc/refman/5.6/en/server-system-variables.html

Original: http://mageedu.blog.51cto.com/4265610/1062628

Marco Education Linux operation and maintenance training

innodb_adaptive_flushing={on| OFF}

Sets whether the MySQL server is allowed to dynamically adjust the rate at which the dirty pages in the InnoDB buffer pool are brushed based on the workload. The purpose of dynamically adjusting the brush write rate is to avoid spikes in IO activity. The default value is on. The scope is the global level, which can be used for the option file, which is a dynamic variable. Innodb_adaptive_hash_index={on| OFF} Sets whether to enable InnoDB's Adaptive hash index. Benchmark results show that adaptive hash indexes are not beneficial for all workloads, so you need to choose a more appropriate value based on the test results of your actual scenario. This attribute is enabled by default and can be disabled by the command-line option--skip-innodb_adaptive_hash_index. The scope is global and can be used for options files, which belong to dynamic variables. innodb_additional_mem_pool_size={2097152.4294967295} sets the InnoDB storage engine in order to store the intrinsic pool size of the data dictionary and other internal data structures, in bytes. The greater the number of tables, the greater the value of this parameter should be set, and when InnoDB runs out of space for this memory pool, it will request memory space from the operating system and will record warning messages to the error log. The default size is 8MB. The scope is global and can be used for option files, which are non-dynamic variables. innodb_autoextend_increment={1.1000} When there is no extra storage space for a shared tablespace, this variable can be used to set the amount of space it has to grow once, in megabytes, and the default value is 8, if it allows autogrow. The value of the variable innodb_file_per_table is set to 1 o'clock InnoDB will use a separate tablespace file for each table, and the innodb_autoextend_increment variable will not have an effect on this tablespace. The scope is the global level, which can be used for the option file, which is a dynamic variable. Innodb_autoinc_lock_mode={0|1|2} Sets the lock model used to generate the auto-grow (auto_increment field) value. Its acceptable values are 0, 1, and 2, respectively, for the "traditional", "consecutive", and "interleaved" lock models. The default value is 1. The scope is global and can be used for option files, which are non-dynamic variables. innodb_buffer_pool_instances= How many areas are separated by the #设定将InnoDB的buffer pool. For buffer pool with several gigabytes of space, separating it into multiple zones can reduce the resource contention factor when different threads read and write to the cached page, and enhance their concurrency capability. In buffer pool, the area selected for reading or depositing pages is randomly based on the hash algorithm. Each buffer pool manages its own free lists, List brushes, LRU, and other data structures related to buffer pool, and is protected by their respective mutexes. This variable functions only when the value of the variable innodb_buffer_pool_size is greater than 1G, and the overall buffer pool space is partitioned by the buffer pools instance. For best utility purposes, it is recommended that you use the innodb_buffer_pool_instances and innodb_buffer_pool_size variables to make each buffer pool instance have at least 1G of space. The scope is global and can be used for option files, which are non-dynamic variables. innodb_buffer_pool_size= #设定InnoDB缓存表数据和索引的内存缓冲区大小, units are bytes. The default value is 128MB, and the maximum value depends on the CPU architecture. On a busy server, when the buffer pool is larger than 1G, setting the value of innodb_buffer_pool_instances greater than 1 can lift its expansion capacity. The larger the value of the innodb_buffer_pool_size variable, the less IO the MySQL server needs to complete the data access, so you can set this value to 80% of physical memory on a server that is large enough in memory and dedicated to the MySQL service. However, it is recommended to reduce the value of this variable if: (1) physical memory resource tension causes the memory page to be swapped out; (2) InnoDB reserves additional memory for the buffer and control structure (buffers and controls structures). As a result, it may take up to 10% more memory space than the specified value, which is unlikely to exceed the estimated memory resource allocation, and (3) the memory address space must be contiguous, which may be unexpected on Windows systems that use a special address space based on the DLL library; (4) The buffer pool is initialized for a length of time that is proportional to the amount of space it specifies, for example, on a Linux system with a 10G buffer pool of x86_64, which takes approximately 6 seconds to initialize. The scope is global and can be used for option files, which are non-dynamic variables. innodb_change_buffering= When #当在表上执行INSERT, update, or delete operations, the data in the index, especially the second index, is not necessarily stored sequentially, which may trigger random IO to complete the update operation of the second index. This variable is used to set whether InnoDB enables modify buffering (change buffering) and what type of modify buffer to use. Modifying a buffer is an optimization method that converts IO operations to sequential mode through a deferred write operation to a second index. The values it accepts are inserts (buffered insert operations), deletes (buffered delete-marking operations), changes (buffered insert and delete-marking operations), purges (buffered purge operations), all (Buffered Insert, delete-marking, and purge operations) and none (no buffering of any operations). The default value is all. MySQL 5.5.4 Previous versions only accept inserts and none of the two values. The scope is global and can be used in the option file, which is a dynamic variable. innodb_checksums={on| Off}innodb can use checksums (checksum) to verify the integrity of all page data being read from disk, thereby improving fault tolerance for hardware or data file corruption. The default is enabled, however, in rare cases it may be necessary to disable this feature, which can be achieved by using the--skip-innodb-checksums command-line option. The scope is global and can be used for option files, which are non-dynamic variables. innodb_commit_concurrency={0:1000} Sets the number of "commit" action threads that InnoDB can run concurrently. 0 means no limit. This variable cannot be modified from "0 value" to "non-0 value" at run time, but can be modified from a "non-0 value" to a different value. The scope is global and can be used for option files, which are non-dynamic variables. innodb_concurrency_tickets= #在一个线程进入 (Enter) InnoDB, it will obtain a certain number of "free tickets" and with these scrolls free access to InnoDB (i.e. exempt), until its reel is exhausted, and 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 threads that can enter InnoDB concurrently, that is, the thread's "Free reel" quantity. The default value is 500. The scope is global and can be used in the option file, which is a dynamic variable. Innodb_data_file_path=IBDATA_FILE Specifies the individual data files of the InnoDB and their size, separated by semicolons with more than one file. The data file path can be a relative path, relative to the directory that the Innodb_data_home_dir variable points to, and the file size representation can be in K (KB), M (MB), G (GB), but the size of these files must be at least 10MB. Without explicitly setting the Innodb_data_file_path variable, the MySQL server automatically creates a data file named Ibdata1 that automatically grows and has an initial size of 10MB in the data directory. The maximum size of a single data file depends on the operating system, which means that the maximum single file size supported by the operating system can be used to limit the volume of its data files. InnoDB also supports the use of bare devices as data files. The scope is global and can be used for option files, which are non-dynamic variables. Innodb_data_home_dir=/path/to/dirinnodb the directory path for all shared tablespace data files. The default value is MySQL's data directory. 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 data files for each table space in the variable innodb_file_per_table enabled state. The scope is global and can be used for option files, which are non-dynamic variables. Innodb_doublewirte={on| OFF} Sets whether InnoDB uses double write buffering. The default is enabled. InnoDB uses a double write buffer to prevent data corruption when it is partially written to the page. A double write buffer is a special reserved area in a table space that is large enough to hold 100 pages in a contiguous interval. When innodb the page from the buffer pool to the disk, it will first brush the pages into a double write buffer, and then save to the actual target location. Therefore, the double write buffer is essentially a backup of the most recently written page, which ensures the atomicity and sustainability of each write. In some cases, double-write buffering is unnecessary, such as disabling it from the server, and some file systems (such as ZFS) do so themselves, so InnoDB does not have to do repetitive work. The scope is global and can be used for option files, which are non-dynamic variables. Innodb_fast_shutdown={0|1|2} set InnoDB off mode. In its acceptable value, "0" indicates a slow shutdown, which means that the full purge is complete and the buffer merge (insert buffers merge) operation is completed before InnoDB is closed, and "1" is the default value. It means that InnoDB will skip these operations in mode 0 when it shuts down, which is why it is called "Fast Shutdown", and "2" means that InnoDB only writes log information and performs cold shutdown, and no transaction is lost at this time. Just the next time you start the MySQL service, it takes a long while to fail back (crash recovery). The process can take several minutes to slow down, even in extreme cases, such as a scenario with a large amount of data buffering, which is measured in hours. In general, a slow shutdown is required only when the major version of MySQL is upgraded to allow the data file to be ready for full adaptation to the new version. The value of this variable must be set to 2 to allow for the fastest shutdown of data at risk of possible corruption, usually only in emergency situations or for debugging purposes. The scope is global and can be used in the option file, which is a dynamic variable. Innodb_file_format={antelope| Barracuda} Sets the file format of the new InnoDB table. The acceptable parameters are antelope and barracuda, but this only affects each table space file based on the variable innodb_file_per_file. Some InnoDB features such as table compression are supported only by the Barracuda file format. The scope is global and can be used in the option file, which is a dynamic variable. Innodb_file_format_check={on| OFF} is used to set whether InnoDB checks the file format label of the shared tablespace when the MySQL server starts. If the label is checked higher than the current InnoDB version supports, INNODB will generate an error and refuse to start, otherwise InnoDB will set the variable for MySQL 5.5.5 and later versions Innodb_file_format_ The value of max is the file format label for the shared Tablespace, and for versions prior to MySQL 5.5.5, InnoDB sets the file format of the shared tablespace to the value of the variable Innodb_file_format_check. The scope is global and can be used for option files, which are non-dynamic variables. Innodb_file_format_max={antelope| Barracuda} When the MySQL service starts, InnoDB sets the value of the variable Innodb_file_format_max to the file format label for the shared tablespace (for example, antelope or Barracuda). If the MySQL server creates or opens a table with a higher-level format, the value of this variable is set to the more advanced format. The scope is global and can be used in the option file, which is a dynamic variable. innodb_file_per_table={on| OFF} Sets whether the InnoDB table uses each table spatial data file (ending with. ibd) to store data and indexes for each table separately. If you use a table space data file per table, it will no longer use system tablespace (that is, shared tablespace). Some features of the InnoDB table, such as compressed tables, are only valid for each table space. The scope is global and can be used in the option file, which is a dynamic variable. Innodb_flush_log_at_trx_commit={0|1|2} sets the way in which the InnoDB synchronizes the log buffer (log buffer) data to the log file, and how the log file is brushed to disk. In its acceptable value, "0" means that the log buffer is written to the log file once per second and the log file is written to disk at the same time, but no action is taken when the transaction commits; "1" is the default value, indicating that the log buffer is written to the log file when there is a transaction commit, and that the log file is written to disk Indicates that each transaction commits or writes the log buffer to the log file once per second, but does not perform a brush write operation on the log file at the same time. Of course, because of the operating system process scheduling, the log write or write operation once per second can not be guaranteed 100%. A fully acid-compatible scenario requires that this variable value be set to 1, which prevents the I/O call until the write operation is done by performing a log brush write operation for each transaction, which significantly reduces the number of transactions that InnoDB can commit per second. Set to "2" to achieve better performance than "1", and only lose the last second of data when the operating system crashes, so data security also has a good performance. setting to "0" can result in data loss for the last second of the transaction, so data security for the entire transaction is not guaranteed, but it usually has the best performance. In order to guarantee the INNODB transactional persistence and consistency of replication on the maximum program, you should set the variable innodb_flush_log_at_trx_commit=1 and set the variable sync_binlog=1. It should be noted, however, that some disks themselves also have caches, which can pose additional potential risks to transactional operations. You can use the Hdparm tool or the vendor's own tool to disable caching on the disk itself. Of course, the best configuration for a high-performance transaction is to set the value of this variable to 1 and place the log file on a raid with a backup battery on the write cache. The scope is global and can be used in the option file, which is a dynamic variable. Innodb_flush_method={o_dsync| O_direct} Sets the way InnoDB actually interacts with the file system. In addition to write operations, it can also affect how InnoDB reads data. When you set the value of the Innodb_flush_method variable to O_dsync, InnoDB uses the O_SYNC flag to open and brush the log file, while using Fsync () to brush the data file. O_sync causes all writes to be synchronous, that is, the data is not returned until it is written to disk, but does not suppress caching at the operating system level, so it does not avoid double buffering and does not write directly to the disk. Fsync () also brushes data and metadata (while Fdatasync () only writes data), which produces more IO operations than Fdatasync (), and the operating system caches some data in its own cache (which results in double buffering). If the file system can intelligently handle I/O requirements, double buffering may not be a bad thing, but if MySQL sets the value of the innodb_file_per_table variable to 1, it causes the first tablespace file to use the Fsync () function alone, and its write operations cannot be merged. When you set the value of the Innodb_flush_method variable to O_direct, InnoDB uses the O_DIRECT flag to open the data file and use Fsync () to brush the data and log files. The O_DIRECT flag causes the operating system to neither cache nor pre-read data, it completely disables the caching of the operating system and allows all read and write operations to be directly to the storage device, avoiding double buffering. However, it does not prohibit caching and pre-reading capabilities for hardware-level (such as RAID cards), and enabling hardware-level caching and pre-reading is the only way to ensure that InnoDB can still maintain good performance when using the O_direct flag. The scope is global and can be used for option files, which are non-dynamic variables. innodb_force_load_corrupted={on| OFF} Sets whether the InnoDB is loaded at startup with a table marked as "Corrupted (corrupted)". This feature should only be enabled in troubleshooting scenarios to repair inaccessible tables and should be disabled after the troubleshooting task is completed and the MySQL service should be restarted. The scope is global and can be used for option files, which are non-dynamic variables. Innodb_force_recovery={0|1|2|3|4|5|6} Sets the recovery mode for InnoDB. InnoDB "page corruption" appears, most of the data is still intact, so you can use SELECT ... The into outfile command backs up data to reduce the amount of damage. However, some failures of the "corrupted" class can cause the select * from Tbl_name command to fail or innodb the background operation to crash, even leading to InnoDB roll-forward operations. 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. Innodb_force_recovery the acceptable value, "0" is the default value, which means that the "forced repair" mode is not enabled by performing a normal startup. Instead of a value of 0, a number contains a precaution against all the values that are smaller than it, but it is also more likely to cause more damage to the B-tree index and other data structures. Therefore, when the value of this variable is a value other than 0, it prevents the user from using an INSERT, update, or delete operation, but allows the operation of the Select, CREATE table, or drop table class to be executed. The following is a description of other non-0 value features: 1 (srv_force_ignore_corrupt): Runs the MySQL service as usual, even if a page corruption occurs, and tries to skip the corrupted index records and pages when the SELECT * from Tbl_name statement executes. 2 (Srv_force_no_background): Disables the start of the main thread, which prevents crashes (crash) when performing a write-down (purge) operation. 3 (Srv_force_no_trx_undo): The rollback operation of a transaction is not performed after a recovery (crash recovery). 4 (Srv_force_no_ibuf_merge): Disables the Modify buffer (insert buffer) merge operation. 5 (Srv_force_no_undo_log_scan): The Undo log is not checked when starting the database service (undo logs), which causes InnoDB to treat outstanding transactions as committed. 6 (Srv_force_no_log_redo): Do not perform a roll-forward operation of the redo log (REDO log). At this point, only the select * from Tbl_name operation without a where, ORDER by, or other clause can be executed, because the complex query aborts and exits when it encounters a corrupted data structure.   innodb_io_capacity= The upper limit of I/O operations that can be performed #设定InnoDB后台任务, such as swiping a page from a buffer pool or merging data in a modified buffer. The minimum value is 100, the default value is 200, and the maximum value depends on the CPU architecture. For servers with large I/O loads, you should specify a larger value for them to perform background maintenance tasks better and faster. In practice, however, the value of this variable should be as close as possible to the number of I/O operations that the MySQL server performs per second (that is, IOPS), or even lower to a minimum that does not affect background task execution. Because if this value is too high, the data is frequently moved from the buffer into the move out, which reduces the effectiveness of the cache pool in terms of system performance gains. A single 5400RPM or 7200RPM disk can only accomplish approximately 100 IOPS, so this variable value should be reduced to 100, and for scenarios with multiple disks or more performance-based storage devices such as solid-state disks, you can increase the value of this variable as needed. The scope is global and can be used in the option file, which is a dynamic variable. Innodb_large_prefix={on| OFF} sets the index prefix that is greater than 767 bytes in length for InnoDB tables that use the dynamic or compressed row format. However, creating this type of table also requires setting the value of Innodb_file_format to Barracuda, and the value of innodb_file_per_table to ture. Also, this setting has no effect on the index length qualification of tables that use the redundant and compact row formats. The scope is global and can be used in the option file, which is a dynamic variable. Innodb_lock_wait_timeout={1.1073741824} Sets the maximum wait time for a transaction in InnoDB that attempts to access a row that is locked by another InnoDB transaction, in seconds, and the default value is 50. When the timeout condition occurs, INNODB returns a 1205 type of error message and performs a rollback operation on the current statement (not the entire transaction), and if the entire transaction needs to be rolled back in this case, the MySQL service will need to be started with--innodb_rollback_on_ Timeout option. For OLTP systems or programs with more interactive applications, this variable value should be lowered to allow the user to get feedback more quickly, or to make the system commit the update operation to the queue more frequently for deferred processing. For scenarios where batch processing is more applied, such as a data warehouse, you should increase the value of this variable to wait for other large insert or update operations to complete. This variable only acts on the InnoDB row lock, and the MySQL table lock is not implemented in InnoDB, so this timeout length has no effect on the table lock. Furthermore, since InnoDB will immediately detect the occurrence of a deadlock and perform a rollback operation on one of the trimming operations, this timeout length should not be used for deadlocks. The scope is global or session level and can be used for option files, which are dynamic variables. Innodb_locks_unsafe_for_binlog={on| OFF} sets Innnodb whether to use gap lock (gap locking) in search and index scanning. InnoDB uses row-level locks (row-level locking), typically InnoDB use the next key lock (Next-key locking) algorithm to lock an index record and its front gap (GAP) in the row lock mechanism of a search or scan index. To block other users from inserting other index records immediately before the index record. In this sense, a row-level lock is also called an index record lock (Index-record lock). By default, the value of this variable is off, which means that the use of non-secure locks is disabled, and the Gap Lock feature is enabled. Setting it to on means blocking the gap before the index record is blocked, or disabling the gap lock, InnoDB uses only index record locks (Index-record lock) for index searches or scans, although this does not prevent InnoDB from using gap locks when performing foreign key constraint checks or duplicate key checks. The effect of enabling Innodb_locks_unsafe_for_binlog is similar to setting the transaction isolation level of MySQL to read-committed, but they are not exactly the same: Innodb_locks_unsafe_for_ Binlog is a global-level setting and can only be set at service startup, while the transaction isolation level can be set globally and inherited by the session level, but the session level is also tuned at run time as needed. Similar to the read-committed transaction isolation level, enabling Innodb_locks_unsafe_for_binlog also brings up a "phantom problem (Phantom problem)", but in addition, it can provide the following features: (1) For an update or DELETE statement, InnoDB only locks rows that need to be updated or deleted, and locks imposed on rows that cannot be matched by the where condition are freed after a condition check. This can effectively reduce the probability of deadlock occurrence; (2) When an UPDATE statement is executed, if a row has been locked by another statement, InnoDB initiates a "semi-uniform (semi-consistent)" read operation from the last MySQL commit version, And to determine whether it can be matched with the where condition of the current update. If it matches, MySQL will lock it again, and if there are still other locks, then you need to wait for them to exit. innodb_log_buffer_size={262144.4294967295} Sets the size of the log buffer that InnoDB is used to assist in the completion of log file writes, in bytes, by default to 8MB. Larger transactions can use a larger log buffer to avoid writing log buffers ' data to the log file before the transaction completes, reducing I/O operations and thereby improving system performance. Therefore, in a scenario with a larger transaction, it is recommended that you set a larger value for this variable. The scope is the global level, which can be used for option files, which are non-dynamic variables. innodb_log_file_size={108576:4294967295} Sets the size of each log file in the log group, in bytes, and the default value is 5MB. A more sensible range of values is 1/n from 1MB to the cache pool volume, where n represents the number of log files in the log group. The larger the log file, the fewer checkpoint brush writes you need to perform in the cache pool, which means fewer I/O operations are required, but this also results in slower recovery times. The scope is the global level, which can be used for option files, which are non-dynamic variables. Innodb_log_files_in_grouP={2:100} Sets the number of log files in the log group. InnoDB use these log files in a circular manner. The default value is 2. The scope is the global level, which can be used for option files, which are non-dynamic variables.  innodb_log_group_home_dir=/path/to/dir set the storage directory for the InnoDB redo log file. When all variables related to the InnoDB log are used by default, it creates two log files in the data directory, named Ib_logfile0 and Ib_logfile1, with a size of 5MB. The scope is the global level, which can be used for option files, which are non-dynamic variables.  INNODB_MAX_DIRTY_PAGES_PCT={0:99} Sets the upper limit of the dirty page scale in the InnoDB buffer pool, which defaults to 75. When the percentage of dirty pages in the cache pool approaches or reaches the ratio defined by this variable, the main thread of INNODB will brush the data in the part of the dirty page into the corresponding file. The scope is the global level, which can be used for the option file, which is a dynamic variable.  innodb_max_purge_lag={0: The 4294967295}INNODB transaction system maintains a list of transactions with index records that have been added with a delete tag (delete-marked), which is the length of the Write delay (purge _lag). This variable is used to set the delay of an INSERT, update, or delete operation when the write delay occurs when the queue length reaches a large amount. When Puge_lag exceeds Innodb_max_purge_lag, these operations ((Purge_lag/innodb_max_purge_lag) *10) are deferred-5 milliseconds. The default value is 0, which means that these operations are never deferred. The action delay required is calculated at the beginning of the purge operation and is recalculated every 10 seconds. For historical reasons, the purge operation cannot start without any delay in operation. The scope is the global level, which can be used for the option file, which is a dynamic variable.  innodb_mirrored_log_groups= #设定日志组镜像的个数. Its value should be 1.  innodb_old_blocks_pct={5.95}innodb manages the cache pool in the list structure and performs maintenance operations on it using the modified version of the LRU algorithm. When space is needed to save the new block, InnoDB cleans up the least recently used block and joins the new block to the list. The Midpoint insertion strategy (midpoint insertion policy) considers the entire list to be two sub-lists: The list header is the most recentThe new block (New/young block) sub-list that has been visited, with the tail being the old block (Lod block) sub-list that has been less recently accessed. The LRU algorithm and the midpoint insertion policy are used to ensure that the most recently accessed block is placed in a new block sub-list, InnoDB the newly read block is placed at the top of the old block sub-list, and the blocks in the old block sub-list are removed as needed. An old block that is accessed again is moved to the header of the new block sub-list. A table scan operation may read a large number of blocks of data to the cache pool at one time and may result in the removal of an old chunk. This variable is used to set the percentage of the entire list length that is considered the length of the old block sub-list, and the default value is 37, which is three-eighths of the cache pool. The scope is the global level, which can be used for the option file, which is a dynamic variable.  innodb_old_blocks_time= #用于设定缓冲池中旧块子列表中的某旧块在其第一次又被访问到时, which requires at least the length of time (in milliseconds) to remain in the old block sub-list before being transferred to the new block sub-list. The default value is 0, which means immediate transfer to the new block sub-list, even if it has just been moved to the old block sub-list. A value other than 0 clearly defines the length of time that a block in the old block list needs to wait for a transfer at least in the old block sub-list when it is first accessed. This variable is usually used in conjunction with innodb_old_blocks_pct. The scope is the global level, which can be used for the option file, which is a dynamic variable.  innodb_open_files= #设定MySQL可同时打开的 The maximum number of IBD tablespace files. This variable takes effect only when using a multi-tablespace file, with a minimum value of 10 and a default value of 300. The qualification of this variable applies only to the. ibd file of the InnoDB table, which is not related to the MySQL server Option--open-files-limit, and does not affect the operation of the table cache. The scope is the global level, which can be used for option files, which are non-dynamic variables.  innodb_purge_batch_size={1.5000} write-down (purge) refers to the operation that synchronizes dirty pages in the cache pool to the persistent storage device, in the record of the redo log. This variable is used to define the granularity of the write operation, that is, how many redo log records can be combined to trigger a write-down operation, with a default value of 20. This variable is typically used for performance tuning with Innodb_purge_threads=1, but it is not required in a general scenario. The scope is the global level, which can be used for option files, which are non-dynamic variables.  INNODB_PURGE_THREADS={0|1} Sets the number of threads that InnoDB perform a write-down operation. The default value is 0, which means that the clear write operation is done by InnoDB's main thread, which reduces the probability of internal resource competition occurring., which enhances the ability to scale MySQL services. However, with the growing variety of competition within INNODB, the performance benefits of this set-up are almost negligible. The scope is the global level, which can be used for the option file, which is a dynamic variable.  INNODB_READ_AHEAD_THRESHOLD={0:64} Sets the linear read-ahead sensitivity for InnoDB pre-read pages to the buffer pool, that is, InnoDB reads from at least one disk area (extent, contains 64 pages) An asynchronous read operation is initialized for reading subsequent pages in the entire extents when multiple pages are read in the The default value is 56. The scope is the global level, which can be used for the option file, which is a dynamic variable.  innodb_read_io_threads={1.64} Sets the number of I/O threads that InnoDB start for a read operation, with the default of 4. The scope is the global level, which can be used for option files, which are non-dynamic variables.  innodb_replication_delay={0:4294967295} Set the number of threads running on the slave server (slave) to Innodb_thread_ The concurrency variable defines the length of time a delay is required for the replication thread to be defined concurrently. The default is 0, which means no delay. The scope is the global level, which can be used for the option file, which is a dynamic variable.  innodb_rollback_on_timeout={on| OFF} sets the way in which transactions are rolled back over time during a transaction execution. In MySQL 5.5, the default is off, which means that only the last statement in the transaction is rolled back. If set to ON, the transaction execution is aborted and the entire transaction is rolled back. The scope is the global level, which can be used for option files, which are non-dynamic variables.  innodb_rollback_segments={1.128} Sets InnoDB the number of rollback segments (rollback segment) for each transaction in the system tablespace, with 128 default. If fewer rollback segments can improve system performance, you should lower the value of this variable. The scope is the global level, which can be used for the option file, which is a dynamic variable.  innodb_spin_wait_delay={0.4294967295} spin (spin) is a wait operation that checks to see if a resource becomes available by uninterrupted testing for a scenario that only needs to wait for a short time to wait for the required resources. It is much more efficient to use spin, the idle Loop (Busy-loop), to complete the resource wait in a way that makes the thread go to sleep through context switching. However, if it is still unable to acquire the resources after spinning a short time, it will still be transferred to the second resource waiting party described above.Expression This variable is the number of idle cycles used to define the InnoDB spin operation, which defaults to 6 rpm. The scope is the global level, which can be used for the option file, which is a dynamic variable.  innodb_stats_method={nulls_equal|nulls_unequal|null_ignored} Sets the data for the null type to be handled when MySQL collects statistics for the distributed index values for the InnoDB table. There are three acceptable values, null_equals means that all null values are treated as the same, and a value group is created to hold the number of NULL class values; nulls_unequal means that all null values are considered different. A value group of size 1 is created separately for each null, and nulls_ignored indicates that all null values are ignored. These methods for generating table statistics affect how the optimizer chooses to select an index for executing queries. The scope is the global level, which can be used for the option file, which is a dynamic variable.  innodb_stats_on_metadata={off| On when you use the Show table status or the show index two metadata statement, or when you access the tables or statistics table in Information_schema, the statistics are updated InnoDB. The default is update. Disabling this feature speeds up access to a database with a large number of tables or indexes, and may also improve the stability of the query Operation execution Plan (execution plan) on the InnoDB table. The scope is the global level, which can be used for the option file, which is a dynamic variable.  innodb_strict_mod={on| OFF} InnoDB provides a so-called strict pattern to prevent ignoring errors in the writing or syntax of SQL statements, or ignoring the unintentional actions in the various combinations of operation modes and SQL statements. In strict mode, the problem mentioned above will cause InnoDB to produce an error, not a warning and a series of specific processing operations. This parameter is the strict mode used to define whether InnoDB is enabled, which is off by default.  innodb_support_xa={true| Flase} Storage Engine transactions are given an acid attribute inside the storage engine, and distributed (XA) transactions are high-level transactions that extend the ACID properties outside the storage engine in a "prepare" and then "commit" (Prepare-then-commit) two-segment manner. Even outside the database. However, the "prepare" phase causes additional disk brush write operations. XA requires the transaction Coordinator, which notifies all participants that they are ready to commit the transaction (phase 1). When the coordinator is from all the participantsWhen "Ready" information is received, it instructs all participants to make a true "commit" operation. This variable is the distributed transaction that defines whether InnoDB supports two-segment commits, which is enabled by default. In fact, all MySQL servers that have binary logs enabled and that support multiple threads writing data to binary logs need to enable distributed transactions, otherwise, multiple threads may write to the binary log in a different way than the original order. This will result in a binary log-based recovery operation or a different raw data creation from the server. Therefore, this feature should not be disabled except in scenarios where only one thread can change the data. In applications where only one thread can modify the data, disabling this feature is safe and can improve the performance of the InnoDB table. The scope is global and session level and can be used for option files, which are dynamic variables.  innodb_sync_spin_loops={0:4294967295} Sets the number of spins that a thread spins before waiting for InnoDB to release a mutex (mutex), which is suspended when the spin operation reaches the number of spins but the mutex is still not released. The default value is 30. The scope is the global level, which can be used for the option file, which 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 the use of MySQL table-level locks within its internal support. The default value is 1 or on, which means that if the value of the autocommit variable is 0 (that is, auto-commit is disabled), explicitly using the lock tables statement on the InnoDB table will cause InnoDB to lock the table inside the storage engine. Using a value of 0 or off means explicitly using the locks TABLE ... The write statement does not have an impact at the storage engine level, but for other explicitly used lock TABLES ... Write or lock TABLES ... The read statement will still have an impact. The scope is global and session level and can be used for option files, which are dynamic variables.  innodb_thread_concurrency={0...1000} Sets the maximum number of operating system threads that INNODB can run concurrently within. The extra threads are placed in the FIFO queue for waiting, and are not counted into the number of concurrently running threads. For unused scenarios, the ideal value depends on the hardware environment and workload, generally recommended for twice times the number of CPUs plus the number of disks. The default value is 0, which means no upper limit (no concurrency check), meaning that InnoDB can use any number of concurrent threads as needed and disables show ENGINE InnoThe queries inside InnoDB and queries in queue counters two counters in DB status. The scope is the global level, which can be used for the option file, which is a dynamic variable.  innodb_thread_sleep_delay= #设定InnoDB线程在加入InnoDB队列之前的睡眠时长, in milliseconds, the default value is 10000. A value of 0 indicates that sleep is not allowed to join the queue directly. The scope is the global level, which can be used for the option file, which is a dynamic variable.  innodb_use_native_aio={on| OFF} Sets whether InnoDB uses the asynchronous I/O subsystem of Linux, so it applies only to Linux system platforms and cannot change its value after MySQL starts. InnoDB This feature is enabled by default, and InnoDB cannot start properly due to problems with the asynchronous I/O subsystem of Linux, you can set this variable to off and restart it in the options file. In fact, even if the value of the variable is on, the MySQL service startup detects potential problems such as the federated Temporary directory path, the Tmpfs file system, and the Linux kernel does not support the use of the AIO mechanism on TMPFS, which automatically turns off this variable. The scope is the global level, which can be used for option files, which are non-dynamic variables.  innodb_use_sys_malloc={on| OFF} Sets whether the InnoDB uses the operating system's (on) or its own (off) memory allocator. The default value is on.  innodb_version=stringinnodb The version number of the storage engine, read-only variables.  innodb_write_io_threads={1.64} Sets the number of I/O threads that InnoDB use to complete a write operation, by default 4.   : InnoDB's data dictionary refers to metadata information that tracks InnoDB related objects, such as tables, indexes, or fields in a table, stored in the system table space of InnoDB (Systems tablespace). Historical reason, it overlaps some data in the. frm file.    Reference Links  http://dev.mysql.com/doc/refman/5.5/en/innodb-parameters.html

MySQL 5.5 server variable Details II (EXT)