MySQL innodb parameters
来源:互联网 发布:淘宝卖家免费工具 编辑:程序博客网 时间:2024/04/30 15:49
本文转载自:http://dev.mysql.com/doc/refman/5.5/en/innodb-parameters.html
InnoDB
Startup Options and System Variables
[+/-]
- 14.15.1 Changes to
InnoDB
Startup Options and System Variables
This section describes the InnoDB
-related command options and system variables.
System variables that are true or false can be enabled at server startup by naming them, or disabled by using a
--skip-
prefix. For example, to enable or disableInnoDB
checksums, you can use--innodb_checksums
or--skip-innodb_checksums
on the command line, orinnodb_checksums
orskip-innodb_checksums
in an option file.System variables that take a numeric value can be specified as
--
on the command line or asvar_name
=value
in option files.var_name
=value
Many system variables can be changed at runtime (see Section 5.1.5.2, “Dynamic System Variables”).
For information about
GLOBAL
andSESSION
variable scope modifiers, refer to theSET
statement documentation.Certain options control the locations and layout of the
InnoDB
data files. Section 14.6, “InnoDB
Configuration” explains how to use these options.Some options, which you might not use initially, help tune
InnoDB
performance characteristics based on machine capacity and your databaseworkload.For more information on specifying options and system variables, see Section 4.2.3, “Specifying Program Options”.
Table 14.4 InnoDB
Option/Variable Reference
InnoDB
Command Options
--ignore-builtin-innodb
Deprecated5.5.22Command-Line Format--ignore-builtin-innodb
Option-File Formatignore-builtin-innodb
System Variable Nameignore_builtin_innodb
Variable ScopeGlobalDynamic VariableNo Permitted ValuesTypeboolean
In MySQL 5.1, this option caused the server to behave as if the built-in
InnoDB
were not present, which enabledInnoDB Plugin
to be used instead. In MySQL 5.5,InnoDB
is the default storage engine andInnoDB Plugin
is not used, so this option has no effect. As of MySQL 5.5.22, it is deprecated and its use results in a warning.--innodb[=
value
]Controls loading of the
InnoDB
storage engine, if the server was compiled withInnoDB
support. This option has a tristate format, with possible values ofOFF
,ON
, orFORCE
. See Section 5.1.8.1, “Installing and Uninstalling Plugins”.To disable
InnoDB
, use--innodb=OFF
or--skip-innodb
. In this case, because the default storage engine isInnoDB
, the server will not start unless you also use--default-storage-engine
to set the default to some other engine.--innodb-status-file
Command-Line Format--innodb-status-file
Option-File Formatinnodb-status-file
Permitted ValuesTypeboolean
DefaultOFF
Controls whether
InnoDB
creates a file namedinnodb_status.
in the MySQL data directory. If enabled,pid
InnoDB
periodically writes the output ofSHOW ENGINE INNODB STATUS
to this file.By default, the file is not created. To create it, start mysqld with the
--innodb-status-file=1
option. The file is deleted during normal shutdown.--skip-innodb
Disable the
InnoDB
storage engine. See the description of--innodb
.
InnoDB
System Variables
ignore_builtin_innodb
Deprecated5.5.22Command-Line Format--ignore-builtin-innodb
Option-File Formatignore-builtin-innodb
System Variable Nameignore_builtin_innodb
Variable ScopeGlobalDynamic VariableNo Permitted ValuesTypeboolean
See the description of
--ignore-builtin-innodb
under “InnoDB
Command Options” earlier in this section.innodb_adaptive_flushing
Command-Line Format--innodb_adaptive_flushing=#
Option-File Formatinnodb_adaptive_flushing
System Variable Nameinnodb_adaptive_flushing
Variable ScopeGlobalDynamic VariableYes Permitted ValuesTypeboolean
DefaultON
Specifies whether to dynamically adjust the rate of flushing dirty pages in the
InnoDB
buffer pool based on the workload. Adjusting the flush rate dynamically is intended to avoid bursts of I/O activity. This setting is enabled by default. SeeSection 14.16.2.2, “Configuring the Rate ofInnoDB
Buffer Pool Flushing” for more information. For general I/O tuning advice, seeSection 8.5.7, “OptimizingInnoDB
Disk I/O”.innodb_adaptive_hash_index
Command-Line Format--innodb_adaptive_hash_index=#
Option-File Formatinnodb_adaptive_hash_index
System Variable Nameinnodb_adaptive_hash_index
Variable ScopeGlobalDynamic VariableYes Permitted ValuesTypeboolean
DefaultON
Whether the
InnoDB
adaptive hash index is enabled or disabled. It may be desirable, depending on your workload, to dynamically enable or disableadaptive hash indexing to improve query performance. Because the adaptive hash index may not be useful for all workloads, conduct benchmarks with it both enabled and disabled, using realistic workloads. SeeSection 14.5.13.5, “Adaptive Hash Indexes” for details.This variable is enabled by default. As of MySQL 5.5, You can modify this parameter using the
SET GLOBAL
statement, without restarting the server. Changing the setting requires theSUPER
privilege. You can also use--skip-innodb_adaptive_hash_index
at server startup to disable it.Disabling the adaptive hash index empties the hash table immediately. Normal operations can continue while the hash table is emptied, and executing queries that were using the hash table access the index B-trees directly instead. When the adaptive hash index is re-enabled, the hash table is populated again during normal operation.
innodb_additional_mem_pool_size
Command-Line Format--innodb_additional_mem_pool_size=#
Option-File Formatinnodb_additional_mem_pool_size
System Variable Nameinnodb_additional_mem_pool_size
Variable ScopeGlobalDynamic VariableNo Permitted ValuesTypenumeric
Default8388608
Range2097152 .. 4294967295
The size in bytes of a memory pool
InnoDB
uses to storedata dictionary information and other internal data structures. The more tables you have in your application, the more memory you need to allocate here. IfInnoDB
runs out of memory in this pool, it starts to allocate memory from the operating system and writes warning messages to the MySQL error log. The default value is 8MB.This variable relates to the
InnoDB
internal memory allocator, which is unused ifinnodb_use_sys_malloc
is enabled.innodb_autoextend_increment
Command-Line Format--innodb_autoextend_increment=#
Option-File Formatinnodb_autoextend_increment
System Variable Nameinnodb_autoextend_increment
Variable ScopeGlobalDynamic VariableYes Permitted ValuesTypenumeric
Default64
Range1 .. 1000
The increment size (in MB) for extending the size of an auto-extending system tablespace file when it becomes full. The default value is 8. This variable does not affect the per-table tablespace files that are created if you use
innodb_file_per_table=1
. Those files are auto-extending regardless of the value ofinnodb_autoextend_increment
. The initial extensions are by small amounts, after which extensions occur in increments of 4MB.innodb_autoinc_lock_mode
Command-Line Format--innodb_autoinc_lock_mode=#
Option-File Formatinnodb_autoinc_lock_mode
System Variable Nameinnodb_autoinc_lock_mode
Variable ScopeGlobalDynamic VariableNo Permitted ValuesTypenumeric
Default1
Valid Values0
1
2
The lock mode to use for generating auto-increment values. The permissible values are 0, 1, or 2, for “traditional”, “consecutive”, or“interleaved” lock mode, respectively.Section 14.9.5, “
AUTO_INCREMENT
Handling inInnoDB
”, describes the characteristics of these modes.This variable has a default of 1 (“consecutive” lock mode).
innodb_buffer_pool_instances
Introduced5.5.4Command-Line Format--innodb_buffer_pool_instances=#
Option-File Formatinnodb_buffer_pool_instances
System Variable Nameinnodb_buffer_pool_instances
Variable ScopeGlobalDynamic VariableNo Permitted ValuesTypenumeric
Default1
Range1 .. 64
The number of regions that the
InnoDB
buffer pool is divided into. For systems with buffer pools in the multi-gigabyte range, dividing the buffer pool into separate instances can improve concurrency, by reducing contention as different threads read and write to cached pages. Each page that is stored in or read from the buffer pool is assigned to one of the buffer pool instances randomly, using a hashing function. Each buffer pool manages its own free lists, flush lists, LRUs, and all other data structures connected to a buffer pool, and is protected by its own buffer pool mutex.This option takes effect only when you set the
innodb_buffer_pool_size
to a size of 1 gigabyte or more. The total size you specify is divided among all the buffer pools. For best efficiency, specify a combination ofinnodb_buffer_pool_instances
andinnodb_buffer_pool_size
so that each buffer pool instance is at least 1 gigabyte.innodb_buffer_pool_size
Command-Line Format--innodb_buffer_pool_size=#
Option-File Formatinnodb_buffer_pool_size
System Variable Nameinnodb_buffer_pool_size
Variable ScopeGlobalDynamic VariableNo Permitted ValuesPlatform Bit Size32
Typenumeric
Default134217728
Range5242880 .. 2**32-1
Permitted ValuesPlatform Bit Size64
Typenumeric
Default134217728
Range5242880 .. 2**64-1
The size in bytes of the buffer pool, the memory area where
InnoDB
caches table and index data. The default value is 128MB. The maximum value depends on the CPU architecture; the maximum is 4294967295 (232-1) on 32-bit systems and 18446744073709551615 (264-1) on 64-bit systems. On 32-bit systems, the CPU architecture and operating system may impose a lower practical maximum size than the stated maximum. When the size of the buffer pool is greater than 1GB, settinginnodb_buffer_pool_instances
to a value greater than 1 can improve the scalability on a busy server.The larger you set this value, the less disk I/O is needed to access the same data in tables more than once. On a dedicated database server, you might set this to up to 80% of the machine physical memory size. Be prepared to scale back this value if these other issues occur:
Competition for physical memory might cause paging in the operating system.
InnoDB
reserves additional memory for buffers and control structures, so that the total allocated space is approximately 10% greater than the specified size.The address space must be contiguous, which can be an issue on Windows systems with DLLs that load at specific addresses.
The time to initialize the buffer pool is roughly proportional to its size. On large installations, this initialization time might be significant. For example, on a modern Linux x86_64 server, initialization of a 10GB buffer pool takes approximately 6 seconds. See Section 8.9.1, “The
InnoDB
Buffer Pool”.
innodb_change_buffering
Command-Line Format--innodb_change_buffering=#
Option-File Formatinnodb_change_buffering
System Variable Nameinnodb_change_buffering
Variable ScopeGlobalDynamic VariableYes Permitted Values (<= 5.5.3)Typeenumeration
Defaultinserts
Valid Valuesinserts
none
Permitted Values (>= 5.5.4)Typeenumeration
Defaultall
Valid Valuesinserts
deletes
purges
changes
all
none
Whether
InnoDB
performs change buffering, an optimization that delays write operations to secondary indexes so that the I/O operations can be performed sequentially. The permitted values areinserts
(buffer insert operations),deletes
(buffer delete operations; strictly speaking, the writes that mark index records for later deletion during a purge operation),changes
(buffer insert and delete-marking operations),purges
(buffer purge operations, the writes when deleted index entries are finally garbage-collected),all
(buffer insert, delete-marking, and purge operations) andnone
(do not buffer any operations). The default isall
. For details, see Section 14.16.5, “ConfiguringInnoDB
Change Buffering”.innodb_checksums
Command-Line Format--innodb_checksums
Option-File Formatinnodb_checksums
System Variable Nameinnodb_checksums
Variable ScopeGlobalDynamic VariableNo Permitted ValuesTypeboolean
DefaultON
InnoDB
can use checksum validation on all pages read from the disk to ensure extra fault tolerance against broken hardware or data files. This validation is enabled by default. However, under some rare circumstances (such as when running benchmarks) this extra safety feature is unneeded and can be disabled with--skip-innodb-checksums
.innodb_commit_concurrency
Command-Line Format--innodb_commit_concurrency=#
Option-File Formatinnodb_commit_concurrency
System Variable Nameinnodb_commit_concurrency
Variable ScopeGlobalDynamic VariableYes Permitted ValuesTypenumeric
Default0
Range0 .. 1000
The number of threads that can commit at the same time. A value of 0 (the default) permits any number of transactions to commit simultaneously.
The value of
innodb_commit_concurrency
cannot be changed at runtime from zero to nonzero or vice versa. The value can be changed from one nonzero value to another.innodb_concurrency_tickets
Command-Line Format--innodb_concurrency_tickets=#
Option-File Formatinnodb_concurrency_tickets
System Variable Nameinnodb_concurrency_tickets
Variable ScopeGlobalDynamic VariableYes Permitted ValuesTypenumeric
Default500
Range1 .. 4294967295
Determines the number of threads that can enter
InnoDB
concurrently. A thread is placed in a queue when it tries to enterInnoDB
if the number of threads has already reached the concurrency limit. When a thread is permitted to enterInnoDB
, it is given a number of “free tickets” equal to the value ofinnodb_concurrency_tickets
, and the thread can enter and leaveInnoDB
freely until it has used up its tickets. After that point, the thread again becomes subject to the concurrency check (and possible queuing) the next time it tries to enterInnoDB
. The default value is 500.innodb_data_file_path
Command-Line Format--innodb_data_file_path=name
Option-File Formatinnodb_data_file_path
System Variable Nameinnodb_data_file_path
Variable ScopeGlobalDynamic VariableNo Permitted ValuesTypestring
Defaultibdata1:10M:autoextend
The paths to individual
InnoDB
data files and their sizes. The full directory path to each data file is formed by concatenatinginnodb_data_home_dir
to each path specified here. The file sizes are specified KB, MB or GB (1024MB) by appendingK
,M
orG
to the size value. If specifying data file size in kilobytes (KB), do so in multiples of 1024. Otherwise, KB values are rounded off to nearest megabyte (MB) boundary. The sum of the sizes of the files must be at least slightly larger than 10MB. If you do not specifyinnodb_data_file_path
, the default behavior is to create a single auto-extending data file, slightly larger than 10MB, namedibdata1
. The size limit of individual files is determined by your operating system. You can set the file size to more than 4GB on those operating systems that support big files. You can alsouse raw disk partitions as data files. For detailed information on configuringInnoDB
tablespace files, see Section 14.6, “InnoDB
Configuration”.innodb_data_home_dir
Command-Line Format--innodb_data_home_dir=path
Option-File Formatinnodb_data_home_dir
System Variable Nameinnodb_data_home_dir
Variable ScopeGlobalDynamic VariableNo Permitted ValuesTypedirectory name
The common part of the directory path for all
InnoDB
data files in thesystem tablespace. This setting does not affect the location of file-per-table tablespaces wheninnodb_file_per_table
is enabled. The default value is the MySQL data directory. If you specify the value as an empty string, you can use absolute file paths ininnodb_data_file_path
.innodb_doublewrite
Command-Line Format--innodb-doublewrite
Option-File Formatinnodb_doublewrite
System Variable Nameinnodb_doublewrite
Variable ScopeGlobalDynamic VariableNo Permitted ValuesTypeboolean
If this variable is enabled (the default),
InnoDB
stores all data twice, first to thedoublewrite buffer, and then to the actual data files. This variable can be turned off with--skip-innodb_doublewrite
for benchmarks or cases when top performance is needed rather than concern for data integrity or possible failures.innodb_fast_shutdown
Command-Line Format--innodb_fast_shutdown[=#]
Option-File Formatinnodb_fast_shutdown
System Variable Nameinnodb_fast_shutdown
Variable ScopeGlobalDynamic VariableYes Permitted ValuesTypenumeric
Default1
Valid Values0
1
2
The
InnoDB
shutdown mode. If the value is 0,InnoDB
does a slow shutdown, a full purge and an insert buffer merge before shutting down. If the value is 1 (the default),InnoDB
skips these operations at shutdown, a process known as afast shutdown. If the value is 2,InnoDB
flushes its logs and shuts down cold, as if MySQL had crashed; no committed transactions are lost, but thecrash recovery operation makes the next startup take longer.The slow shutdown can take minutes, or even hours in extreme cases where substantial amounts of data are still buffered. Use the slow shutdown technique before upgrading or downgrading between MySQL major releases, so that all data files are fully prepared in case the upgrade process updates the file format.
Use
innodb_fast_shutdown=2
in emergency or troubleshooting situations, to get the absolute fastest shutdown if data is at risk of corruption.innodb_file_format
Command-Line Format--innodb_file_format=#
Option-File Formatinnodb_file_format
System Variable Nameinnodb_file_format
Variable ScopeGlobalDynamic VariableYes Permitted Values (<= 5.5.6)Typestring
DefaultBarracuda
Valid ValuesAntelope
Barracuda
Permitted Values (>= 5.5.7)Typestring
DefaultAntelope
Valid ValuesAntelope
Barracuda
The file format to use for new
InnoDB
tables. Currently,Antelope
andBarracuda
are supported. This applies only for tables that have their owntablespace, so for it to have an effect,innodb_file_per_table
must be enabled. The Barracuda file format is required for certain InnoDB features such as table compression.Be aware that
ALTER TABLE
operations that recreateInnoDB
tables (ALTER OFFLINE
) will use the currentinnodb_file_format
setting (the conditions outlined above still apply).innodb_file_format_check
Command-Line Format--innodb_file_format_check=#
Option-File Formatinnodb_file_format_check
System Variable Nameinnodb_file_format_check
Variable ScopeGlobalDynamic VariableNo Permitted ValuesTypestring
DefaultAntelope
Permitted Values (>= 5.5.1)Typestring
DefaultBarracuda
Permitted Values (>= 5.5.5)Typeboolean
DefaultON
As of MySQL 5.5.5, this variable can be set to 1 or 0 at server startup to enable or disable whether
InnoDB
checks thefile format tag in the system tablespace (for example,Antelope
orBarracuda
). If the tag is checked and is higher than that supported by the current version ofInnoDB
, an error occurs andInnoDB
does not start. If the tag is not higher,InnoDB
sets the value ofinnodb_file_format_max
to the file format tag.Before MySQL 5.5.5, this variable can be set to 1 or 0 at server startup to enable or disable whether
InnoDB
checks the file format tag in the shared tablespace. If the tag is checked and is higher than that supported by the current version ofInnoDB
, an error occurs andInnoDB
does not start. If the tag is not higher,InnoDB
sets the value ofinnodb_file_format_check
to the file format tag, which is the value seen at runtime.NoteDespite the default value sometimes being displayed as
ON
orOFF
, always use the numeric values 1 or 0 to turn this option on or off in your configuration file or command line.innodb_file_format_max
Introduced5.5.5Command-Line Format--innodb_file_format_max=#
Option-File Formatinnodb_file_format_max
System Variable Nameinnodb_file_format_max
Variable ScopeGlobalDynamic VariableYes Permitted ValuesTypestring
DefaultAntelope
Valid ValuesAntelope
Barracuda
At server startup,
InnoDB
sets the value of this variable to thefile format tag in the system tablespace (for example,Antelope
orBarracuda
). If the server creates or opens a table with a “higher” file format, it sets the value ofinnodb_file_format_max
to that format.This variable was added in MySQL 5.5.5.
innodb_file_per_table
Command-Line Format--innodb_file_per_table
Option-File Formatinnodb_file_per_table
System Variable Nameinnodb_file_per_table
Variable ScopeGlobalDynamic VariableYes Permitted Values (<= 5.5.6)Typeboolean
DefaultON
Permitted Values (>= 5.5.7)Typeboolean
DefaultOFF
When
innodb_file_per_table
is disabled,InnoDB
stores the data for all tables and indexes in the ibdata files that make up the system tablespace. This setting reduces the performance overhead of filesystem operations for operations such asDROP TABLE
orTRUNCATE TABLE
. It is most appropriate for a server environment where entire storage devices are devoted to MySQL data. Because the system tablespace never shrinks, and is shared across all databases in aninstance, avoid loading huge amounts of temporary data on a space-constrained system wheninnodb_file_per_table=OFF
. Set up a separate instance in such cases, so that you can drop the entire instance to reclaim the space.When
innodb_file_per_table
is enabled,InnoDB
stores data and indexes for each newly created table in a separate.ibd
file, rather than in the system tablespace. The storage for theseInnoDB
tables is reclaimed when the tables are dropped or truncated. This setting enables several otherInnoDB
features, such as table compression. See Section 14.8.2, “InnoDB File-Per-Table Mode” for details about such features as well as advantages and disadvantages of using file-per-table tablespaces.Be aware that enabling
innodb_file_per_table
also means that anALTER TABLE
operation will moveInnoDB
table from the system tablespace to an individual.ibd
file in cases whereALTER TABLE
recreates the table (ALTER OFFLINE
).In MySQL 5.5 and higher, the configuration parameter
innodb_file_per_table
is dynamic, and can be setON
orOFF
usingSET GLOBAL
. Previously, the only way to set this parameter was in the MySQLconfiguration file (my.cnf
ormy.ini
), and changing it required shutting down and restarting the server.Dynamically changing the value of this parameter requires the
SUPER
privilege and immediately affects the operation of all connections.innodb_flush_log_at_trx_commit
Command-Line Format--innodb_flush_log_at_trx_commit[=#]
Option-File Formatinnodb_flush_log_at_trx_commit
System Variable Nameinnodb_flush_log_at_trx_commit
Variable ScopeGlobalDynamic VariableYes Permitted ValuesTypeenumeration
Default1
Valid Values0
1
2
Controls the balance between strict ACID compliance for commit operations, and higher performance that is possible when commit-related I/O operations are rearranged and done in batches. You can achieve better performance by changing the default value, but then you can lose up to one second worth oftransactions in a crash.
The default value of 1 is required for full ACID compliance. With this value, thelog buffer is written out to the log file at each transaction commit and the flush to disk operation is performed on the log file.
With a value of 0, any mysqld process crash can erase the last second of transactions. The log buffer is written out to the log file once per second and the flush to disk operation is performed on the log file, but nothing is done at a transaction commit.
With a value of 2, an operating system crash or a power outage can erase the last second of commit records. The log buffer is written out to the file at each commit, but the flush to disk operation is not performed on it. However, the flushing on the log file takes place once per second also when the value is 2. Note that the once-per-second flushing is not 100% guaranteed to happen every second, due to process scheduling issues.
InnoDB
's crash recovery works regardless of the value. Transactions are either applied entirely or erased entirely.
For the greatest possible durability and consistency in a replication setup using
InnoDB
with transactions, useinnodb_flush_log_at_trx_commit=1
andsync_binlog=1
in your master servermy.cnf
file.CautionMany operating systems and some disk hardware fool the flush-to-disk operation. They may tellmysqld that the flush has taken place, even though it has not. Then the durability of transactions is not guaranteed even with the setting 1, and in the worst case a power outage can even corrupt
InnoDB
data. Using a battery-backed disk cache in the SCSI disk controller or in the disk itself speeds up file flushes, and makes the operation safer. You can also try using the Unix commandhdparm to disable the caching of disk writes in hardware caches, or use some other command specific to the hardware vendor.innodb_flush_method
Command-Line Format--innodb_flush_method=name
Option-File Formatinnodb_flush_method
System Variable Nameinnodb_flush_method
Variable ScopeGlobalDynamic VariableNo Permitted ValuesType (Linux)string
Defaultfdatasync
Valid Valuesfdatasync
O_DSYNC
O_DIRECT
Permitted ValuesType (HP-UX)string
Defaultfdatasync
Valid Valuesfdatasync
O_DSYNC
O_DIRECT
Permitted ValuesType (Solaris)string
Defaultfdatasync
Valid Valuesfdatasync
O_DSYNC
O_DIRECT
Controls the system calls used to flush data to the
InnoDB
data files and log files, which can influence I/O throughput. This variable is relevant only for Unix and Linux systems. On Windows systems, the flush method is alwaysasync_unbuffered
and cannot be changed.By default,
InnoDB
uses thefsync()
system call to flush both the data and log files. Ifinnodb_flush_method
option is set toO_DSYNC
,InnoDB
usesO_SYNC
to open and flush the log files, andfsync()
to flush the data files. IfO_DIRECT
is specified (available on some GNU/Linux versions, FreeBSD, and Solaris),InnoDB
usesO_DIRECT
(ordirectio()
on Solaris) to open the data files, and usesfsync()
to flush both the data and log files. Note thatInnoDB
usesfsync()
instead offdatasync()
, and it does not useO_DSYNC
by default because there have been problems with it on many varieties of Unix.Depending on hardware configuration, setting
innodb_flush_method
toO_DIRECT
can either have either a positive or negative effect on performance. Benchmark your particular configuration to decide which setting to use. The mix of read and write operations in your workload can also affect which setting performs better for you. For example, on a system with a hardware RAID controller and battery-backed write cache,O_DIRECT
can help to avoid double buffering between theInnoDB
buffer pool and the operating system's filesystem cache. On some systems whereInnoDB
data and log files are located on a SAN, the default value orO_DSYNC
might be faster for a read-heavy workload with mostlySELECT
statements. Always test this parameter with the same type of hardware and workload that reflects your production environment. For general I/O tuning advice, seeSection 8.5.7, “OptimizingInnoDB
Disk I/O”.Formerly, a value of
fdatasync
also specified the default behavior. This value was removed, due to confusion that a value offdatasync
causedfsync()
system calls rather thanfdatasync()
for flushing. To obtain the default value now, do not set any value forinnodb_flush_method
at startup.innodb_force_load_corrupted
Introduced5.5.18Command-Line Format--innodb_force_load_corrupted
Option-File Formatinnodb_force_load_corrupted
System Variable Nameinnodb_force_load_corrupted
Variable ScopeGlobalDynamic VariableNo Permitted ValuesTypeboolean
DefaultOFF
Lets InnoDB load tables at startup that are marked as corrupted. Use only during troubleshooting, to recover data that is otherwise inaccessible. When troubleshooting is complete, turn this setting back off and restart the server.
innodb_force_recovery
Command-Line Format--innodb_force_recovery=#
Option-File Formatinnodb_force_recovery
System Variable Nameinnodb_force_recovery
Variable ScopeGlobalDynamic VariableNo Permitted ValuesTypenumeric
Default0
Range0 .. 6
The crash recovery mode, typically only changed in serious troubleshooting situations. Possible values are from 0 to 6. The meanings of these values are described inSection 14.21.2, “Starting
InnoDB
on a Corrupted Database”.WarningOnly set this variable greater than 0 in an emergency situation, to dump your tables from a corrupt database. As a safety measure,
InnoDB
prevents any changes to its data when this variable is greater than 0. This restriction also prohibits some queries that useWHERE
orORDER BY
clauses, because high values can prevent queries from using indexes, to guard against possible corrupt index data.innodb_io_capacity
Command-Line Format--innodb_io_capacity=#
Option-File Formatinnodb_io_capacity
System Variable Nameinnodb_io_capacity
Variable ScopeGlobalDynamic VariableYes Permitted ValuesPlatform Bit Size32
Typenumeric
Default200
Range100 .. 2**32-1
Permitted ValuesPlatform Bit Size64
Typenumeric
Default200
Range100 .. 2**64-1
The
innodb_io_capacity
parameter sets an upper limit on the I/O activity performed by theInnoDB
background tasks, such asflushing pages from the buffer pool and merging data from the insert buffer. The default value is 200. For busy systems capable of higher I/O rates, you can set a higher value at server startup, to help the server handle the background maintenance work associated with a high rate of row changes. For systems with individual 5400 RPM or 7200 RPM drives, you might lower the value to the former default of100
.The
innodb_io_capacity
limit is a total limit for all buffer pool instances. When dirty pages are flushed, theinnodb_io_capacity
limit is divided equally among buffer pool instances.This parameter should be set to approximately the number of I/O operations that the system can perform per second. Ideally, keep this setting as low as practical, but not so low that these background activities fall behind. If the value is too high, data is removed from the buffer pool and insert buffer too quickly to provide significant benefit from the caching.
The value represents an estimated proportion of the I/O operations per second (IOPS) available to older-generation disk drives that could perform about 100 IOPS. The current default of 200 reflects that modern storage devices are capable of much higher I/O rates.
In general, you can increase the value as a function of the number of drives used for
InnoDB
I/O, particularly fast drives capable of high numbers of IOPS. For example, systems that use multiple disks or solid-state disks forInnoDB
are likely to benefit from the ability to control this parameter.Although you can specify a very high number, in practice such large values have little if any benefit; for example, a value of one million would be considered very high.
You can set the
innodb_io_capacity
value to any number 100 or greater, and the default value is200
. You can set the value of this parameter in the MySQL option file (my.cnf
ormy.ini
) or change it dynamically with theSET GLOBAL
command, which requires theSUPER
privilege.See Section 14.16.9, “Configuring the
InnoDB
Master Thread I/O Rate” for more guidelines about this option. For general information about InnoDB I/O performance, seeSection 8.5.7, “OptimizingInnoDB
Disk I/O”.innodb_large_prefix
Introduced5.5.14Command-Line Format--innodb_large_prefix
Option-File Formatinnodb_large_prefix
System Variable Nameinnodb_large_prefix
Variable ScopeGlobalDynamic VariableYes Permitted ValuesTypeboolean
DefaultOFF
Enable this option to allow index key prefixes longer than 767 bytes (up to 3072 bytes), for
InnoDB
tables that use theDYNAMIC
andCOMPRESSED
row formats. (Creating such tables also requires the option valuesinnodb_file_format=barracuda
andinnodb_file_per_table=true
.) See Section 14.9.7, “Limits onInnoDB
Tables” for the relevant maximums associated with index key prefixes under various settings.For tables using the
REDUNDANT
andCOMPACT
row formats, this option does not affect the allowed key prefix length. It does introduce a new error possibility. When this setting is enabled, attempting to create an index prefix with a key length greater than 3072 for aREDUNDANT
orCOMPACT
table causes anER_INDEX_COLUMN_TOO_LONG
error.innodb_lock_wait_timeout
Command-Line Format--innodb_lock_wait_timeout=#
Option-File Formatinnodb_lock_wait_timeout
System Variable Nameinnodb_lock_wait_timeout
Variable ScopeGlobal, SessionDynamic VariableYes Permitted ValuesTypenumeric
Default50
Range1 .. 1073741824
The length of time in seconds an
InnoDB
transaction waits for a row lock before giving up. The default value is 50 seconds. A transaction that tries to access a row that is locked by anotherInnoDB
transaction waits at most this many seconds for write access to the row before issuing the following error:ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
When a lock wait timeout occurs, the current statement is rolled back (not the entire transaction). To have the entire transaction roll back, start the server with the
--innodb_rollback_on_timeout
option. See also Section 14.21.4, “InnoDB
Error Handling”.You might decrease this value for highly interactive applications or OLTP systems, to display user feedback quickly or put the update into a queue for processing later. You might increase this value for long-running back-end operations, such as a transform step in a data warehouse that waits for other large insert or update operations to finish.
innodb_lock_wait_timeout
applies toInnoDB
row locks only. A MySQL table lock does not happen insideInnoDB
and this timeout does not apply to waits for table locks.The lock wait timeout value does not apply to deadlocks, because
InnoDB
detects them immediately and rolls back one of the deadlocked transactions.As of MySQL 5.5,
innodb_lock_wait_timeout
can be set at runtime with theSET GLOBAL
orSET SESSION
statement. Changing theGLOBAL
setting requires theSUPER
privilege and affects the operation of all clients that subsequently connect. Any client can change theSESSION
setting forinnodb_lock_wait_timeout
, which affects only that client.innodb_locks_unsafe_for_binlog
Command-Line Format--innodb_locks_unsafe_for_binlog
Option-File Formatinnodb_locks_unsafe_for_binlog
System Variable Nameinnodb_locks_unsafe_for_binlog
Variable ScopeGlobalDynamic VariableNo Permitted ValuesTypeboolean
DefaultOFF
This variable affects how
InnoDB
uses gap locking for searches and index scans. Normally,InnoDB
uses an algorithm callednext-key locking that combines index-row locking with gap locking.InnoDB
performs row-level locking in such a way that when it searches or scans a table index, it sets shared or exclusive locks on the index records it encounters. Thus, the row-level locks are actually index-record locks. In addition, a next-key lock on an index record also affects the “gap” before that index record. That is, a next-key lock is an index-record lock plus a gap lock on the gap preceding the index record. If one session has a shared or exclusive lock on recordR
in an index, another session cannot insert a new index record in the gap immediately beforeR
in the index order. SeeSection 14.5.6, “InnoDB
Record, Gap, and Next-Key Locks”.By default, the value of
innodb_locks_unsafe_for_binlog
is 0 (disabled), which means that gap locking is enabled:InnoDB
uses next-key locks for searches and index scans. To enable the variable, set it to 1. This causes gap locking to be disabled:InnoDB
uses only index-record locks for searches and index scans.Enabling
innodb_locks_unsafe_for_binlog
does not disable the use of gap locking for foreign-key constraint checking or duplicate-key checking.The effect of enabling
innodb_locks_unsafe_for_binlog
is similar to but not identical to setting the transaction isolation level toREAD COMMITTED
:Enabling
innodb_locks_unsafe_for_binlog
is a global setting and affects all sessions, whereas the isolation level can be set globally for all sessions, or individually per session.innodb_locks_unsafe_for_binlog
can be set only at server startup, whereas the isolation level can be set at startup or changed at runtime.
READ COMMITTED
therefore offers finer and more flexible control thaninnodb_locks_unsafe_for_binlog
. For additional details about the effect of isolation level on gap locking, seeSection 13.3.6, “SET TRANSACTION
Syntax”.Enabling
innodb_locks_unsafe_for_binlog
may cause phantom problems because other sessions can insert new rows into the gaps when gap locking is disabled. Suppose that there is an index on theid
column of thechild
table and that you want to read and lock all rows from the table having an identifier value larger than 100, with the intention of updating some column in the selected rows later:SELECT * FROM child WHERE id > 100 FOR UPDATE;
The query scans the index starting from the first record where
id
is greater than 100. If the locks set on the index records in that range do not lock out inserts made in the gaps, another session can insert a new row into the table. Consequently, if you were to execute the sameSELECT
again within the same transaction, you would see a new row in the result set returned by the query. This also means that if new items are added to the database,InnoDB
does not guarantee serializability. Therefore, ifinnodb_locks_unsafe_for_binlog
is enabled,InnoDB
guarantees at most an isolation level ofREAD COMMITTED
. (Conflict serializability is still guaranteed.) For additional information about phantoms, seeSection 14.5.7, “Avoiding the Phantom Problem Using Next-Key Locking”.Enabling
innodb_locks_unsafe_for_binlog
has additional effects:For
UPDATE
orDELETE
statements,InnoDB
holds locks only for rows that it updates or deletes. Record locks for nonmatching rows are released after MySQL has evaluated theWHERE
condition. This greatly reduces the probability of deadlocks, but they can still happen.For
UPDATE
statements, if a row is already locked,InnoDB
performs a “semi-consistent” read, returning the latest committed version to MySQL so that MySQL can determine whether the row matches theWHERE
condition of theUPDATE
. If the row matches (must be updated), MySQL reads the row again and this timeInnoDB
either locks it or waits for a lock on it.
Consider the following example, beginning with this table:
CREATE TABLE t (a INT NOT NULL, b INT) ENGINE = InnoDB;INSERT INTO t VALUES (1,2),(2,3),(3,2),(4,3),(5,2);COMMIT;
In this case, table has no indexes, so searches and index scans use the hidden clustered index for record locking (seeSection 14.5.13.2, “Clustered and Secondary Indexes”).
Suppose that one client performs an
UPDATE
using these statements:SET autocommit = 0;UPDATE t SET b = 5 WHERE b = 3;
Suppose also that a second client performs an
UPDATE
by executing these statements following those of the first client:SET autocommit = 0;UPDATE t SET b = 4 WHERE b = 2;
As
InnoDB
executes eachUPDATE
, it first acquires an exclusive lock for each row, and then determines whether to modify it. IfInnoDB
does not modify the row andinnodb_locks_unsafe_for_binlog
is enabled, it releases the lock. Otherwise,InnoDB
retains the lock until the end of the transaction. This affects transaction processing as follows.If
innodb_locks_unsafe_for_binlog
is disabled, the firstUPDATE
acquires x-locks and does not release any of them:x-lock(1,2); retain x-lockx-lock(2,3); update(2,3) to (2,5); retain x-lockx-lock(3,2); retain x-lockx-lock(4,3); update(4,3) to (4,5); retain x-lockx-lock(5,2); retain x-lock
The second
UPDATE
blocks as soon as it tries to acquire any locks (because first update has retained locks on all rows), and does not proceed until the firstUPDATE
commits or rolls back:x-lock(1,2); block and wait for first UPDATE to commit or roll back
If
innodb_locks_unsafe_for_binlog
is enabled, the firstUPDATE
acquires x-locks and releases those for rows that it does not modify:x-lock(1,2); unlock(1,2)x-lock(2,3); update(2,3) to (2,5); retain x-lockx-lock(3,2); unlock(3,2)x-lock(4,3); update(4,3) to (4,5); retain x-lockx-lock(5,2); unlock(5,2)
For the second
UPDATE
,InnoDB
does a“semi-consistent” read, returning the latest committed version of each row to MySQL so that MySQL can determine whether the row matches theWHERE
condition of theUPDATE
:x-lock(1,2); update(1,2) to (1,4); retain x-lockx-lock(2,3); unlock(2,3)x-lock(3,2); update(3,2) to (3,4); retain x-lockx-lock(4,3); unlock(4,3)x-lock(5,2); update(5,2) to (5,4); retain x-lock
innodb_log_buffer_size
Command-Line Format--innodb_log_buffer_size=#
Option-File Formatinnodb_log_buffer_size
System Variable Nameinnodb_log_buffer_size
Variable ScopeGlobalDynamic VariableNo Permitted ValuesTypenumeric
Default8388608
Range262144 .. 4294967295
The size in bytes of the buffer that
InnoDB
uses to write to thelog files on disk. The default value is 8MB. A large log buffer enables large transactions to run without a need to write the log to disk before the transactionscommit. Thus, if you have transactions that update, insert, or delete many rows, making the log buffer larger saves disk I/O. For general I/O tuning advice, seeSection 8.5.7, “OptimizingInnoDB
Disk I/O”.innodb_log_file_size
Command-Line Format--innodb_log_file_size=#
Option-File Formatinnodb_log_file_size
System Variable Nameinnodb_log_file_size
Variable ScopeGlobalDynamic VariableNo Permitted ValuesTypenumeric
Default5242880
Range1048576 .. 4GB / innodb_log_files_in_group
The size in bytes of each log file in a log group. The combined size of log files (
innodb_log_file_size
*innodb_log_files_in_group
) cannot exceed a maximum value that is slightly less than 4GB. A pair of 2047 MB log files, for example, would allow you to approach the range limit but not exceed it. The default value is 5MB. Sensible values range from 1MB to 1/N
-th of the size of the buffer pool, whereN
is the number of log files in the group. The larger the value, the less checkpoint flush activity is needed in the buffer pool, saving disk I/O. Larger log files also makecrash recovery slower, although improvements to recovery performance in MySQL 5.5 and higher make the log file size less of a consideration. For general I/O tuning advice, seeSection 8.5.7, “OptimizingInnoDB
Disk I/O”.innodb_log_files_in_group
Command-Line Format--innodb_log_files_in_group=#
Option-File Formatinnodb_log_files_in_group
System Variable Nameinnodb_log_files_in_group
Variable ScopeGlobalDynamic VariableNo Permitted ValuesTypenumeric
Default2
Range2 .. 100
The number of log files in the log group.
InnoDB
writes to the files in a circular fashion. The default (and recommended) value is 2. The location of these files is specified byinnodb_log_group_home_dir
.innodb_log_group_home_dir
Command-Line Format--innodb_log_group_home_dir=path
Option-File Formatinnodb_log_group_home_dir
System Variable Nameinnodb_log_group_home_dir
Variable ScopeGlobalDynamic VariableNo Permitted ValuesTypedirectory name
The directory path to the
InnoDB
redo log files, whose number is specified byinnodb_log_files_in_group
. If you do not specify anyInnoDB
log variables, the default is to create two files namedib_logfile0
andib_logfile1
in the MySQL data directory. Their size is given by the size of theinnodb_log_file_size
system variable.innodb_max_dirty_pages_pct
Command-Line Format--innodb_max_dirty_pages_pct=#
Option-File Formatinnodb_max_dirty_pages_pct
System Variable Nameinnodb_max_dirty_pages_pct
Variable ScopeGlobalDynamic VariableYes Permitted ValuesTypenumeric
Default75
Range0 .. 99
InnoDB
tries to flush data from the buffer pool so that the percentage of dirty pages does not exceed this value. Specify an integer in the range from 0 to 99. The default value is 75.For additional information about this variable, see Section 14.16.2.2, “Configuring the Rate of
InnoDB
Buffer Pool Flushing”. For general I/O tuning advice, seeSection 8.5.7, “OptimizingInnoDB
Disk I/O”.innodb_max_purge_lag
Command-Line Format--innodb_max_purge_lag=#
Option-File Formatinnodb_max_purge_lag
System Variable Nameinnodb_max_purge_lag
Variable ScopeGlobalDynamic VariableYes Permitted ValuesTypenumeric
Default0
Range0 .. 4294967295
This variable controls how to delay
INSERT
,UPDATE
, andDELETE
operations when purge operations are lagging (see Section 14.5.12, “InnoDB
Multi-Versioning”). The default value is 0 (no delays).The
InnoDB
transaction system maintains a list of transactions that have index records delete-marked byUPDATE
orDELETE
operations. The length of this list represents thepurge_lag
value. Whenpurge_lag
exceedsinnodb_max_purge_lag
, eachINSERT
,UPDATE
, andDELETE
operation is delayed by ((purge_lag
/innodb_max_purge_lag
)×10)–5 milliseconds. The delay is computed in the beginning of a purge batch, every ten seconds. The operations are not delayed if purge cannot run because of an oldconsistent read view that could see the rows to be purged.A typical setting for a problematic workload might be 1 million, assuming that transactions are small, only 100 bytes in size, and it is permissible to have 100MB of unpurged
InnoDB
table rows.The lag value is displayed as the history list length in the
TRANSACTIONS
section of InnoDB Monitor output. For example, if the output includes the following lines, the lag value is 20:------------TRANSACTIONS------------Trx id counter 0 290328385Purge done for trx's n:o < 0 290315608 undo n:o < 0 17History list length 20
For general I/O tuning advice, see Section 8.5.7, “Optimizing
InnoDB
Disk I/O”.innodb_mirrored_log_groups
Has no effect.
innodb_old_blocks_pct
Command-Line Format--innodb_old_blocks_pct=#
Option-File Formatinnodb_old_blocks_pct
System Variable Nameinnodb_old_blocks_pct
Variable ScopeGlobalDynamic VariableYes Permitted ValuesTypenumeric
Default37
Range5 .. 95
Specifies the approximate percentage of the
InnoDB
buffer pool used for the old block sublist. The range of values is 5 to 95. The default value is 37 (that is, 3/8 of the pool). SeeSection 14.16.2.3, “Making the Buffer Pool Scan Resistant” for more information. SeeSection 8.9.1, “TheInnoDB
Buffer Pool” for information about buffer pool management, such as theLRU algorithm and eviction policies.innodb_old_blocks_time
Command-Line Format--innodb_old_blocks_time=#
Option-File Formatinnodb_old_blocks_time
System Variable Nameinnodb_old_blocks_time
Variable ScopeGlobalDynamic VariableYes Permitted ValuesTypenumeric
Default0
Range0 .. 2**32-1
Non-zero values protect against the buffer pool being filled up by data that is referenced only for a brief period, such as during afull table scan. Increasing this value offers more protection against full table scans interfering with data cached in the buffer pool.
Specifies how long in milliseconds (ms) a block inserted into the old sublist must stay there after its first access before it can be moved to the new sublist. If the value is 0, a block inserted into the old sublist moves immediately to the new sublist the first time it is accessed, no matter how soon after insertion the access occurs. If the value is greater than 0, blocks remain in the old sublist until an access occurs at least that many ms after the first access. For example, a value of 1000 causes blocks to stay in the old sublist for 1 second after the first access before they become eligible to move to the new sublist.
This variable is often used in combination with
innodb_old_blocks_pct
. See Section 14.16.2.3, “Making the Buffer Pool Scan Resistant” for more information. SeeSection 8.9.1, “TheInnoDB
Buffer Pool” for information about buffer pool management, such as theLRU algorithm and eviction policies.innodb_open_files
Command-Line Format--innodb_open_files=#
Option-File Formatinnodb_open_files
System Variable Nameinnodb_open_files
Variable ScopeGlobalDynamic VariableNo Permitted ValuesTypenumeric
Default300
Range10 .. 4294967295
This variable is relevant only if you use multiple
InnoDB
tablespaces. It specifies the maximum number of.ibd
files that MySQL can keep open at one time. The minimum value is 10. The default value is 300.The file descriptors used for
.ibd
files are forInnoDB
tables only. They are independent of those specified by the--open-files-limit
server option, and do not affect the operation of the table cache.innodb_print_all_deadlocks
Introduced5.5.30Command-Line Format--innodb_print_all_deadlocks=#
Option-File Formatinnodb_print_all_deadlocks
System Variable Nameinnodb_print_all_deadlocks
Variable ScopeGlobalDynamic VariableYes Permitted ValuesTypeboolean
DefaultOFF
When this option is enabled, information about all deadlocks in
InnoDB
user transactions is recorded in themysqld
error log. Otherwise, you see information about only the last deadlock, using theSHOW ENGINE INNODB STATUS
command. An occasionalInnoDB
deadlock is not necessarily an issue, becauseInnoDB
detects the condition immediately, and rolls back one of the transactions automatically. You might use this option to troubleshoot why deadlocks are happening if an application does not have appropriate error-handling logic to detect the rollback and retry its operation. A large number of deadlocks might indicate the need to restructure transactions that issueDML orSELECT ... FOR UPDATE
statements for multiple tables, so that each transaction accesses the tables in the same order, thus avoiding the deadlock condition.innodb_purge_batch_size
Introduced5.5.4Command-Line Format--innodb_purge_batch_size=#
Option-File Formatinnodb_purge_batch_size
System Variable Nameinnodb_purge_batch_size
Variable ScopeGlobalDynamic VariableYes Permitted Values (>= 5.5.4)Typenumeric
Default20
Range1 .. 5000
The granularity of changes, expressed in units of redo log records, that trigger a purge operation, flushing the changed buffer pool blocks to disk. This option is intended for tuning performance in combination with the setting
innodb_purge_threads=
, and typical users do not need to modify it.n
innodb_purge_threads
Introduced5.5.4Command-Line Format--innodb_purge_threads=#
Option-File Formatinnodb_purge_threads
System Variable Nameinnodb_purge_threads
Variable ScopeGlobalDynamic VariableNo Permitted Values (>= 5.5.4)Typenumeric
Default0
Range0 .. 1
The number of background threads devoted to the InnoDB purge operation. Currently, can only be 0 (the default) or 1. The default value of 0 signifies that the purge operation is performed as part of themaster thread. Running the purge operation in its own thread can reduce internal contention within InnoDB, improving scalability. Currently, the performance gain might be minimal because the background thread might encounter different kinds of contention than before. This feature primarily lays the groundwork for future performance work.
innodb_random_read_ahead
Introduced5.5.16Command-Line Format--innodb_random_read_ahead=#
Option-File Formatinnodb_random_read_ahead
System Variable Nameinnodb_random_read_ahead
Variable ScopeGlobalDynamic VariableYes Permitted ValuesTypeboolean
DefaultOFF
Enables the random read-ahead technique for optimizing
InnoDB
I/O. This is a setting that was originally on by default, then was removed in MySQL 5.5, and now is available but turned off by default. SeeSection 14.16.2.1, “ConfiguringInnoDB
Buffer Pool Prefetching (Read-Ahead)” for details about the performance considerations for the different types of read-ahead requests. For general I/O tuning advice, seeSection 8.5.7, “OptimizingInnoDB
Disk I/O”.innodb_read_ahead_threshold
Command-Line Format--innodb_read_ahead_threshold=#
Option-File Formatinnodb_read_ahead_threshold
System Variable Nameinnodb_read_ahead_threshold
Variable ScopeGlobalDynamic VariableYes Permitted ValuesTypenumeric
Default56
Range0 .. 64
Controls the sensitivity of linear read-ahead that
InnoDB
uses to prefetch pages into thebuffer pool. IfInnoDB
reads at leastinnodb_read_ahead_threshold
pages sequentially from anextent (64 pages), it initiates an asynchronous read for the entire following extent. The permissible range of values is 0 to 64. The default is 56:InnoDB
must read at least 56 pages sequentially from an extent to initiate an asynchronous read for the following extent.Knowing how many pages are read through this read-ahead mechanism, and how many of them are evicted from the buffer pool without ever being accessed, can be useful to help fine-tune the
innodb_read_ahead_threshold
parameter. As of MySQL 5.5,SHOW ENGINE INNODB STATUS
output displays counter information from theInnodb_buffer_pool_read_ahead
andInnodb_buffer_pool_read_ahead_evicted
global status variables. These variables indicate the number of pages brought into thebuffer pool by read-ahead requests, and the number of such pages evicted from the buffer pool without ever being accessed respectively. These counters provide global values since the last server restart.SHOW ENGINE INNODB STATUS
also shows the rate at which the read-ahead pages are read in and the rate at which such pages are evicted without being accessed. The per-second averages are based on the statistics collected since the last invocation ofSHOW ENGINE INNODB STATUS
and are displayed in theBUFFER POOL AND MEMORY
section of the output.See Section 14.16.2.1, “Configuring
InnoDB
Buffer Pool Prefetching (Read-Ahead)” for more information. For general I/O tuning advice, seeSection 8.5.7, “OptimizingInnoDB
Disk I/O”.innodb_read_io_threads
Command-Line Format--innodb_read_io_threads=#
Option-File Formatinnodb_read_io_threads
System Variable Nameinnodb_read_io_threads
Variable ScopeGlobalDynamic VariableNo Permitted ValuesTypenumeric
Default4
Range1 .. 64
The number of I/O threads for read operations in
InnoDB
. The default value is 4. Its counterpart for write threads isinnodb_write_io_threads
. See Section 14.16.7, “Configuring the Number of BackgroundInnoDB
I/O Threads” for more information. For general I/O tuning advice, seeSection 8.5.7, “OptimizingInnoDB
Disk I/O”.NoteOn Linux systems, running multiple MySQL servers (typically more than 12) with default settings for
innodb_read_io_threads
,innodb_write_io_threads
, and the Linuxaio-max-nr
setting can exceed system limits. Ideally, increase theaio-max-nr
setting; as a workaround, you might reduce the settings for one or both of the MySQL configuration options.innodb_replication_delay
Command-Line Format--innodb_replication_delay=#
Option-File Formatinnodb_replication_delay
System Variable Nameinnodb_replication_delay
Variable ScopeGlobalDynamic VariableYes Permitted ValuesTypenumeric
Default0
Range0 .. 4294967295
The replication thread delay (in ms) on a slave server if
innodb_thread_concurrency
is reached.innodb_rollback_on_timeout
Command-Line Format--innodb_rollback_on_timeout
Option-File Formatinnodb_rollback_on_timeout
System Variable Nameinnodb_rollback_on_timeout
Variable ScopeGlobalDynamic VariableNo Permitted ValuesTypeboolean
DefaultOFF
In MySQL 5.5,
InnoDB
rolls back only the last statement on a transaction timeout by default. If--innodb_rollback_on_timeout
is specified, a transaction timeout causesInnoDB
to abort and roll back the entire transaction (the same behavior as in MySQL 4.1).innodb_rollback_segments
Introduced5.5.11Command-Line Format--innodb_rollback_segments=#
Option-File Formatinnodb_rollback_segments
System Variable Nameinnodb_rollback_segments
Variable ScopeGlobalDynamic VariableYes Permitted ValuesTypenumeric
Default128
Range1 .. 128
Defines how many of the rollback segments in the system tablespace that InnoDB uses within a transaction. You might reduce this value from its default of 128 if a smaller number of rollback segments performs better for your workload.
innodb_spin_wait_delay
Command-Line Format--innodb_spin_wait_delay=#
Option-File Formatinnodb_spin_wait_delay
System Variable Nameinnodb_spin_wait_delay
Variable ScopeGlobalDynamic VariableYes Permitted ValuesTypenumeric
Default6
Range0 .. 4294967295
The maximum delay between polls for a spin lock. The low-level implementation of this mechanism varies depending on the combination of hardware and operating system, so the delay does not correspond to a fixed time interval. The default value is 6. SeeSection 14.16.11, “Configuring Spin Lock Polling” for more information.
innodb_stats_method
Introduced5.5.10Command-Line Format--innodb_stats_method=name
Option-File Formatinnodb_stats_method
System Variable Nameinnodb_stats_method
Variable ScopeGlobalDynamic VariableYes Permitted ValuesTypeenumeration
Defaultnulls_equal
Valid Valuesnulls_equal
nulls_unequal
nulls_ignored
How the server treats
NULL
values when collectingstatistics about the distribution of index values forInnoDB
tables. This variable has three possible values,nulls_equal
,nulls_unequal
, andnulls_ignored
. Fornulls_equal
, allNULL
index values are considered equal and form a single value group that has a size equal to the number ofNULL
values. Fornulls_unequal
,NULL
values are considered unequal, and eachNULL
forms a distinct value group of size 1. Fornulls_ignored
,NULL
values are ignored.The method that is used for generating table statistics influences how the optimizer chooses indexes for query execution, as described inSection 8.3.7, “
InnoDB
andMyISAM
Index Statistics Collection”.innodb_stats_on_metadata
Introduced5.5.4Command-Line Format--innodb_stats_on_metadata
Option-File Formatinnodb_stats_on_metadata
System Variable Nameinnodb_stats_on_metadata
Variable ScopeGlobalDynamic VariableYes Permitted ValuesTypeboolean
DefaultON
When this variable is enabled (which is the default, as before the variable was created),
InnoDB
updatesstatistics when metadata statements such asSHOW TABLE STATUS
orSHOW INDEX
are run, or when accessing theINFORMATION_SCHEMA
tablesTABLES
orSTATISTICS
. (These updates are similar to what happens forANALYZE TABLE
.) When disabled,InnoDB
does not update statistics during these operations. Disabling this variable can improve access speed for schemas that have a large number of tables or indexes. It can also improve the stability ofexecution plans for queries that involveInnoDB
tables.To change the setting, issue the statement
SET GLOBAL innodb_stats_on_metadata=
, wheremode
mode
is eitherON
orOFF
(or1
or0
). Changing this setting requires theSUPER
privilege and immediately affects the operation of all connections.innodb_stats_sample_pages
Command-Line Format--innodb_stats_sample_pages=#
Option-File Formatinnodb_stats_sample_pages
System Variable Nameinnodb_stats_sample_pages
Variable ScopeGlobalDynamic VariableYes Permitted ValuesTypenumeric
Default8
Range1 .. 2**64-1
The number of index pages to sample for index distribution statistics such as are calculated by
ANALYZE TABLE
. The default value is 8. For additional information, seeSection 14.16.15, “Configuring Optimizer Statistics Parameters”.Setting a high value for
innodb_stats_sample_pages
could result in lengthyANALYZE TABLE
execution time. To estimate the number of database pages accessed byANALYZE TABLE
, see Section 14.16.16, “EstimatingANALYZE TABLE
Complexity forInnoDB
Tables”.innodb_strict_mode
Command-Line Format--innodb_strict_mode=#
Option-File Formatinnodb_strict_mode
System Variable Nameinnodb_strict_mode
Variable ScopeGlobal, SessionDynamic VariableYes Permitted ValuesTypeboolean
DefaultOFF
When
innodb_strict_mode
isON
,InnoDB
returns errors rather than warnings for certain conditions. The default value isOFF
.Strict mode helps guard against ignored typos and syntax errors in SQL, or other unintended consequences of various combinations of operational modes and SQL statements. When
innodb_strict_mode
isON
,InnoDB
raises error conditions in certain cases, rather than issuing a warning and processing the specified statement (perhaps with unintended behavior). This is analogous tosql_mode
in MySQL, which controls what SQL syntax MySQL accepts, and determines whether it silently ignores errors, or validates input syntax and data values.The
innodb_strict_mode
setting affects the handling of syntax errors forCREATE TABLE
,ALTER TABLE
andCREATE INDEX
statements.innodb_strict_mode
also enables a record size check, so that anINSERT
orUPDATE
never fails due to the record being too large for the selected page size.Oracle recommends enabling
innodb_strict_mode
when usingROW_FORMAT
andKEY_BLOCK_SIZE
clauses onCREATE TABLE
,ALTER TABLE
, andCREATE INDEX
statements. Wheninnodb_strict_mode
isOFF
,InnoDB
ignores conflicting clauses and creates the table or index, with only a warning in the message log. The resulting table might have different behavior than you intended, such as having no compression when you tried to create a compressed table. Wheninnodb_strict_mode
isON
, such problems generate an immediate error and the table or index is not created, avoiding a troubleshooting session later.You can turn
innodb_strict_mode
ON
orOFF
on the command line when you startmysqld
, or in the configuration filemy.cnf
ormy.ini
. You can also enable or disableinnodb_strict_mode
at runtime with the statementSET [GLOBAL|SESSION] innodb_strict_mode=
, wheremode
mode
is eitherON
orOFF
. Changing theGLOBAL
setting requires theSUPER
privilege and affects the operation of all clients that subsequently connect. Any client can change theSESSION
setting forinnodb_strict_mode
, and the setting affects only that client.innodb_support_xa
Command-Line Format--innodb_support_xa
Option-File Formatinnodb_support_xa
System Variable Nameinnodb_support_xa
Variable ScopeGlobal, SessionDynamic VariableYes Permitted ValuesTypeboolean
DefaultTRUE
Enables
InnoDB
support for two-phase commit in XA transactions, causing an extra disk flush for transaction preparation. This setting is the default. The XA mechanism is used internally and is essential for any server that has its binary log turned on and is accepting changes to its data from more than one thread. If you turn it off, transactions can be written to the binary log in a different order from the one in which the live database is committing them. This can produce different data when the binary log is replayed in disaster recovery or on a replication slave. Do not turn it off on a replication master server unless you have an unusual setup where only one thread is able to change data.For a server that is accepting data changes from only one thread, it is safe and recommended to turn off this option to improve performance for
InnoDB
tables. For example, you can turn it off on replication slaves where only the replication SQL thread is changing data.You can also turn off this option if you do not need it for safe binary logging or replication, and you also do not use an external XA transaction manager.
innodb_sync_spin_loops
Command-Line Format--innodb_sync_spin_loops=#
Option-File Formatinnodb_sync_spin_loops
System Variable Nameinnodb_sync_spin_loops
Variable ScopeGlobalDynamic VariableYes Permitted ValuesTypenumeric
Default30
Range0 .. 4294967295
The number of times a thread waits for an
InnoDB
mutex to be freed before the thread is suspended. The default value is 30.innodb_table_locks
Command-Line Format--innodb_table_locks
Option-File Formatinnodb_table_locks
System Variable Nameinnodb_table_locks
Variable ScopeGlobal, SessionDynamic VariableYes Permitted ValuesTypeboolean
DefaultTRUE
If
autocommit = 0
,InnoDB
honorsLOCK TABLES
; MySQL does not return fromLOCK TABLES ... WRITE
until all other threads have released all their locks to the table. The default value ofinnodb_table_locks
is 1, which means thatLOCK TABLES
causes InnoDB to lock a table internally ifautocommit = 0
.As of MySQL 5.5.3,
innodb_table_locks = 0
has no effect for tables locked explicitly withLOCK TABLES ... WRITE
. It still has an effect for tables locked for read or write byLOCK TABLES ... WRITE
implicitly (for example, through triggers) or byLOCK TABLES ... READ
.innodb_thread_concurrency
Command-Line Format--innodb_thread_concurrency=#
Option-File Formatinnodb_thread_concurrency
System Variable Nameinnodb_thread_concurrency
Variable ScopeGlobalDynamic VariableYes Permitted ValuesTypenumeric
Default0
Range0 .. 1000
InnoDB
tries to keep the number of operating system threads concurrently insideInnoDB
less than or equal to the limit given by this variable. Once the number of threads reaches this limit, additional threads are placed into a wait state within a FIFO queue for execution. Threads waiting for locks are not counted in the number of concurrently executing threads.The correct value for this variable is dependent on environment and workload. Try a range of different values to determine what value works for your applications. A recommended value is 2 times the number of CPUs plus the number of disks.
The range of this variable is 0 to 1000. A value of 0 (the default) is interpreted as infinite concurrency (no concurrency checking). Disabling thread concurrency checking enables InnoDB to create as many threads as it needs. A value of 0 also disables the
queries inside InnoDB
andqueries in queue counters
in theROW OPERATIONS
section ofSHOW ENGINE INNODB STATUS
output.innodb_thread_sleep_delay
Command-Line Format--innodb_thread_sleep_delay=#
Option-File Formatinnodb_thread_sleep_delay
System Variable Nameinnodb_thread_sleep_delay
Variable ScopeGlobalDynamic VariableYes Permitted Values (<= 5.5.36)Platform Bit Size32
Typenumeric
Default10000
Range0 .. 4294967295
Permitted Values (<= 5.5.36)Platform Bit Size64
Typenumeric
Default10000
Range0 .. 18446744073709551615
Permitted Values (>= 5.5.37)Typenumeric
Default10000
Range0 .. 1000000
How long
InnoDB
threads sleep before joining theInnoDB
queue, in microseconds. The default value is 10,000. A value of 0 disables sleep.innodb_use_native_aio
Introduced5.5.4Command-Line Format--innodb_use_native_aio=#
Option-File Formatinnodb_use_native_aio
System Variable Nameinnodb_use_native_aio
Variable ScopeGlobalDynamic VariableNo Permitted ValuesTypeboolean
DefaultON
Specifies whether to use the Linux asynchronous I/O subsystem. This variable applies to Linux systems only, and cannot be changed while the server is running. Normally, you do not need to touch this option, because it is enabled by default.
As of MySQL 5.5, the asynchronous I/O capability that
InnoDB
has on Windows systems is available on Linux systems. (Other Unix-like systems continue to use synchronous I/O calls.) This feature improves the scalability of heavily I/O-bound systems, which typically show many pending reads/writes in the output of the commandSHOW ENGINE INNODB STATUS\G
.Running with a large number of
InnoDB
I/O threads, and especially running multiple such instances on the same server machine, can exceed capacity limits on Linux systems. In this case, you may receive the following error:EAGAIN: The specified maxevents exceeds the user's limit of available events.
You can typically address this error by writing a higher limit to
/proc/sys/fs/aio-max-nr
.However, if a problem with the asynchronous I/O subsystem in the OS prevents
InnoDB
from starting, you can start the server withinnodb_use_native_aio=0
disabled (useinnodb_use_native_aio=0
in the option file). This option may also be turned off automatically during startup ifInnoDB
detects a potential problem such as a combination oftmpdir
location,tmpfs
filesystem, and Linux kernel that does not support AIO ontmpfs
.This variable was added in MySQL 5.5.4.
innodb_use_sys_malloc
Command-Line Format--innodb_use_sys_malloc=#
Option-File Formatinnodb_use_sys_malloc
System Variable Nameinnodb_use_sys_malloc
Variable ScopeGlobalDynamic VariableNo Permitted ValuesTypeboolean
DefaultON
Whether
InnoDB
uses the operating system memory allocator (ON
) or its own (OFF
). The default value isON
. SeeSection 14.16.4, “Configuring the Memory Allocator forInnoDB
” for more information.innodb_version
The
InnoDB
version number. Starting in 5.5.30, the separate numbering forInnoDB
is discontinued and this value is the same as for theversion
variable.innodb_write_io_threads
Command-Line Format--innodb_write_io_threads=#
Option-File Formatinnodb_write_io_threads
System Variable Nameinnodb_write_io_threads
Variable ScopeGlobalDynamic VariableNo Permitted ValuesTypenumeric
Default4
Range1 .. 64
The number of I/O threads for write operations in
InnoDB
. The default value is 4. Its counterpart for read threads isinnodb_read_io_threads
. See Section 14.16.7, “Configuring the Number of BackgroundInnoDB
I/O Threads” for more information. For general I/O tuning advice, seeSection 8.5.7, “OptimizingInnoDB
Disk I/O”.NoteOn Linux systems, running multiple MySQL servers (typically more than 12) with default settings for
innodb_read_io_threads
,innodb_write_io_threads
, and the Linuxaio-max-nr
setting can exceed system limits. Ideally, increase theaio-max-nr
setting; as a workaround, you might reduce the settings for one or both of the MySQL configuration options.
You should also take into consideration the value of sync_binlog
, which controls synchronization of the binary log to disk.
For general I/O tuning advice, see Section 8.5.7, “Optimizing InnoDB
Disk I/O”.
- MySQL innodb parameters
- mysql innodb
- mysql-Innodb
- MySQL-InnoDB
- Tuning Mysql Server Parameters
- MySQL Stored Procedure Parameters
- Mysql InnoDB介绍
- Mysql InnoDB介绍
- Mysql InnoDb简介
- mysql InnoDB的count(*)
- mysql innodb 性能优化
- mysql innodb 性能优化
- Mysql Innodb的恢复
- Mysql中的InnoDB
- mysql ENGINE=InnoDB
- MYSQL中InnoDB是什么?
- mysql ENGINE=InnoDB
- mysql ENGINE=InnoDB
- 让linux CentOS6在同一个窗口打开文件夹(转载)
- Training--保存数据(3)-- 在SQL数据库中保存数据
- ios强制竖屏启动,状态栏位置设置
- 推荐点书,说点学习路线...
- ORACLE数据库名、实例名、ORACLE_SID的区别
- MySQL innodb parameters
- WINDOW PHONE sdK离线包下载
- Bayesian Face Revisited: A Joint Formulation 算法流程图
- glew在linux环境下的编译
- poj1502 MPI Maelstrom 最短路径,dijkstra
- .exe文件解压不出来/解压不出来.exe文件
- PHP面向对象(OOP)编程完全教程:8.构造方法__construct()与析构方法__destruct()
- ubuntu修改主机名后无法解析主机
- Choosing innodb_buffer_pool_size
User Comments
Posted by Morgan Tocker on December 28 2006 1:21am[Delete] [Edit]Be careful when being too aggressive with settings like innodb_buffer_pool_size. Although your system might have a lot of RAM installed, a 32-bit Linux operating can't allocate more than 2.2-2.7G* per process.
Posted by Sunny Walia on April 4 2007 1:55am[Delete] [Edit]* This limit varies in different kernels.
I am using innodb_file_per_table to separate the files out so when i delete database, we can get our disk usage back. I go into details in my blog which I hope helps somebody.
Posted by Brian Aker on August 16 2007 3:08pm[Delete] [Edit]http://crazytoon.com/2007/04/03/mysql-ibdata-files-do-not-shrink-on-database-deletion-innodb/
Commentary on Innodb parameters for an 8way machine:
Posted by Willem de Groot on September 20 2007 3:39pm[Delete] [Edit]http://krow.livejournal.com/542306.html
Changing innodb_log_file_size can yield strange errors, such as: Incorrect information in file: './db010840/notifications.frm'
Posted by Simon Mudd on October 13 2009 3:25pm[Delete] [Edit]This is particularly of importance when performing a file based sync to setup replication. If you have a different (or no) innodb_log_file_size setting at the slave, you will be puzzled for hours (I was).
NOTE: The time to Initialise the innodb buffer pool is roughly proportional to the size of the pool created. On large installations[*] this initialisation time may be significant.
Posted by K Thomas Kuruvilla on July 16 2012 4:32pm[Delete] [Edit][*] 2009/10 Initialising a 10 GB buffer pool takes 6 seconds, larger configurations may take proportionally longer.
It would have been better if the parameters given in here were with concrete examples. I hope it would be done in the future releases.
Posted by Morgan Tocker on November 15 2013 11:34pm[Delete] [Edit]I wrote a guide for what to tune in MySQL 5.6 after installation here:
http://www.tocker.ca/2013/09/17/what-to-tune-in-mysql-56-after-installation.html