Throughout the course of development, InnoDB 1.1 and its predecessor the InnoDB Plugin introduced new configuration parameters. The following table summarizes those parameters:
Table 13.10. InnoDB 1.1 New Parameter Summary
| Name | Cmd-Line | Option File | System Var | Scope | Dynamic | Default |
|---|---|---|---|---|---|---|
innodb_adaptive_flushing | YES | YES | YES | GLOBAL | YES | TRUE |
innodb_buffer_pool_instances | YES | YES | YES | GLOBAL | YES | TRUE |
innodb_change_buffering | YES | YES | YES | GLOBAL | YES | inserts |
innodb_file_format | YES | YES | YES | GLOBAL | YES | Antelope |
innodb_file_format_check | YES | YES | YES | GLOBAL | NO | 1 |
innodb_file_format_max | YES | YES | YES | GLOBAL | YES | Antelope for a new database;
Barracuda if any tables using that file
format exist in the database |
innodb_io_capacity | YES | YES | YES | GLOBAL | YES | 200 |
innodb_old_blocks_pct | YES | YES | YES | GLOBAL | YES | 37 |
innodb_old_blocks_time | YES | YES | YES | GLOBAL | YES | 0 |
innodb_purge_batch_size | YES | YES | YES | GLOBAL | YES | 0 |
innodb_purge_threads | YES | YES | YES | GLOBAL | YES | 0 |
innodb_read_ahead_threshold | YES | YES | YES | GLOBAL | YES | 56 |
innodb_read_io_threads | YES | YES | YES | GLOBAL | NO | 4 |
innodb_spin_wait_delay | YES | YES | YES | GLOBAL | YES | 6 |
innodb_stats_sample_pages | YES | YES | YES | GLOBAL | YES | 8 |
innodb_strict_mode | YES | YES | YES | GLOBAL|SESSION | YES | FALSE |
innodb_use_native_aio | YES | YES | YES | GLOBAL | NO | TRUE |
innodb_use_sys_malloc | YES | YES | YES | GLOBAL | NO | TRUE |
innodb_write_io_threads | YES | YES | YES | GLOBAL | NO | 4 |
Whether InnoDB uses a new algorithm to estimate the required rate of flushing. The default value is
TRUE. This parameter was added in InnoDB storage engine 1.0.4. See Section 13.7.7.12, “Controlling the Flushing Rate of Dirty Pages” for more information.Whether InnoDB performs insert buffering. The default value is
"inserts"(buffer insert operations). This parameter was added in InnoDB storage engine 1.0.3. See Section 13.7.7.4, “Controlling InnoDB Change Buffering” for more information.The default file format for new InnoDB tables. The default is “Antelope”. To enable support for table compression, change it to “Barracuda”. This parameter was added in InnoDB storage engine 1.0.1. See Section 13.7.4.1, “Enabling File Formats” for more information.
innodb_file_format_checkandinnodb_file_format_maxControls whether InnoDB performs file format compatibility checking when opening a database. The default value is
innodb-file-format-check=1, withinnodb_file_format_maxset to the highest format that is used in the database (either “Barracuda” or “Antelope”). See Section 13.7.4.2.1, “Compatibility Check When InnoDB Is Started” for more information.The number of
I/Ooperations that can be performed per second. The allowable value range is any number 100 or greater, and the default value is200. This parameter was added in InnoDB storage engine 1.0.4. To reproduce the earlier behavior, use a value of 100. See Section 13.7.7.11, “Controlling the Master Thread I/O Rate” for more information.Controls the desired percentage of “old” blocks in the LRU list of the buffer pool. The default value is
37and the allowable value range is5 to 95. This parameter was added in InnoDB storage engine 1.0.5. See Section 13.7.7.15, “Making Buffer Cache Scan Resistant” for more information.The time in milliseconds since the first access to a block during which it can be accessed again without being made “young”. The default value is
0which means that blocks are moved to the “young” end of the LRU list at the first access. This parameter was added in InnoDB storage engine 1.0.5. See Section 13.7.7.15, “Making Buffer Cache Scan Resistant” for more information.Control the sensitivity of the linear read ahead. The allowable value range is
0to64and the default value is56. This parameter was added in InnoDB storage engine 1.0.4. See Section 13.7.7.7, “Changes in the Read-Ahead Algorithm” for more information.The number of background
I/Othreads used for reads. The allowable value range is1to64and the default value is4. This parameter was added in InnoDB storage engine 1.0.4. See Section 13.7.7.8, “Multiple Background I/O Threads” for more information.Maximum delay between polling for a spin lock. The allowable value range is
0(meaning unlimited) or positive integers and the default value is6. This parameter was added in InnoDB storage engine 1.0.4. See Section 13.7.7.14, “Control of Spin Lock Polling” for more information.The number of index pages to sample when calculating statistics. The allowable value range is
1-unlimitedand the default value is8. This parameter was added in InnoDB storage engine 1.0.2. See Section 13.7.8.5, “Controlling Optimizer Statistics Estimation” for more information.Whether InnoDB raises error conditions in certain cases, rather than issuing a warning. This parameter was added in InnoDB storage engine 1.0.2. See Section 13.7.8.4, “InnoDB Strict Mode” for more information.
Whether InnoDB uses its own memory allocator or an allocator of the operating system. The default value is
ON(use an allocator of the underlying system). This parameter was added in InnoDB storage engine 1.0.3. See Section 13.7.7.3, “Using Operating System Memory Allocators” for more information.The number of background
I/Othreads used for writes. The allowable value range is1to64and the default value is4. This parameter was added in InnoDB storage engine 1.0.4. See Section 13.7.7.8, “Multiple Background I/O Threads” for more information.