(mysql.info) innodb-parameters
Info Catalog
(mysql.info) innodb-configuration
(mysql.info) innodb
(mysql.info) innodb-init
14.2.4 `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 disable `InnoDB' checksums, you can use
-innodb_checksums or -skip-innodb_checksums on the command line, or
`innodb_checksums' or `skip-innodb_checksums' in an option file. System
variables that take a numeric value can be specified as -VAR_NAME=VALUE
on the command line or as `VAR_NAME=VALUE' in option files. For more
information on specifying options and system variables, see
program-options. Many of the system variables can be changed at
runtime (see dynamic-system-variables).
`InnoDB' command options:
* -innodb
Enables the `InnoDB' storage engine, if the server was compiled
with `InnoDB' support. Use -skip-innodb to disable `InnoDB'.
* -innodb_status_file
Causes `InnoDB' to create a file named
`<DATADIR>/innodb_status.<PID>' in the MySQL data directory.
`InnoDB' periodically writes the output of `SHOW ENGINE INNODB
STATUS' to this file.
`InnoDB' system variables:
* `innodb_additional_mem_pool_size'
The size in bytes of a memory pool `InnoDB' uses to store data
dictionary information and other internal data structures. The
more tables you have in your application, the more memory you need
to allocate here. If `InnoDB' 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 1MB.
* `innodb_autoextend_increment'
The increment size (in MB) for extending the size of an
auto-extending tablespace when it becomes full. The default value
is 8.
* `innodb_buffer_pool_awe_mem_mb'
The size of the buffer pool (in MB), if it is placed in the AWE
memory. This is relevant only in 32-bit Windows. If your 32-bit
Windows operating system supports more than 4GB memory, using
so-called `Address Windowing Extensions,' you can allocate the
`InnoDB' buffer pool into the AWE physical memory using this
variable. The maximum possible value for this variable is 63000.
If it is greater than 0, `innodb_buffer_pool_size' is the window
in the 32-bit address space of `mysqld' where `InnoDB' maps that
AWE memory. A good value for `innodb_buffer_pool_size' is 500MB.
To take advantage of AWE memory, you will need to recompile MySQL
yourself. The current project settings needed for doing this can
be found in the `innobase/os/os0proj.c' source file.
* `innodb_buffer_pool_size'
The size in bytes of the memory buffer `InnoDB' uses to cache data
and indexes of its tables. The larger you set this value, the less
disk I/O is needed to access data in tables. On a dedicated
database server, you may set this to up to 80% of the machine
physical memory size. However, do not set it too large because
competition for physical memory might cause paging in the
operating system.
* `innodb_checksums'
`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. This variable was added in MySQL 5.0.3.
* `innodb_commit_concurrency'
The number of threads that can commit at the same time. A value of
0 disables concurrency control. This variable was added in MySQL
5.0.12.
* `innodb_concurrency_tickets'
The number of threads that can enter `InnoDB' concurrently is
determined by the `innodb_thread_concurrency' variable. A thread
is placed in a queue when it tries to enter `InnoDB' if the number
of threads has already reached the concurrency limit. When a
thread is allowed to enter `InnoDB', it is given a number of `free
tickets' equal to the value of `innodb_concurrency_tickets', and
the thread can enter and leave `InnoDB' 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 enter `InnoDB'. This variable was added in MySQL 5.0.3.
* `innodb_data_file_path'
The paths to individual data files and their sizes. The full
directory path to each data file is formed by concatenating
`innodb_data_home_dir' to each path specified here. The file sizes
are specified in MB or GB (1024MB) by appending `M' or `G' to the
size value. The sum of the sizes of the files must be at least
10MB. If you do not specify `innodb_data_file_path', the default
behavior is to create a single 10MB auto-extending data file named
`ibdata1'. 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 also
use raw disk partitions as data files. See
innodb-raw-devices.
* `innodb_data_home_dir'
The common part of the directory path for all `InnoDB' data files.
If you do not set this value, the default is the MySQL data
directory. You can specify the value as an empty string, in which
case you can use absolute file paths in `innodb_data_file_path'.
* `innodb_doublewrite'
By default, `InnoDB' stores all data twice, first to the
doublewrite buffer, and then to the actual data files. This
variable is enabled by default. It 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. This variable was added in MySQL 5.0.3.
* `innodb_fast_shutdown'
If you set this variable to 0, `InnoDB' does a full purge and an
insert buffer merge before a shutdown. These operations can take
minutes, or even hours in extreme cases. If you set this variable
to 1, `InnoDB' skips these operations at shutdown. The default
value is 1. If you set it to 2, `InnoDB' will just flush its logs
and then shut down cold, as if MySQL had crashed; no committed
transaction will be lost, but crash recovery will be done at the
next startup. The value of 2 can be used as of MySQL 5.0.5, except
that it cannot be used on NetWare.
* `innodb_file_io_threads'
The number of file I/O threads in `InnoDB'. Normally, this should
be left at the default value of 4, but disk I/O on Windows may
benefit from a larger number. On Unix, increasing the number has
no effect; `InnoDB' always uses the default value.
* `innodb_file_per_table'
If this variable is enabled, `InnoDB' creates each new table using
its own `.ibd' file for storing data and indexes, rather than in
the shared tablespace. The default is to create tables in the
shared tablespace. See multiple-tablespaces.
* `innodb_flush_log_at_trx_commit'
When `innodb_flush_log_at_trx_commit' is set to 0, 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. When this value is 1 (the default), the
log buffer is written out to the log file at each transaction
commit and the flush to disk operation is performed on the log
file. When set to 2, 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.
The default value of this variable is 1, which is the value that
is required for ACID compliance. You can achieve better
performance by setting the value different from 1, but then you
can lose at most one second worth of transactions in a crash. If
you set the value to 0, then any `mysqld' process crash can erase
the last second of transactions. If you set the value to 2, then
only an operating system crash or a power outage can erase the last
second of transactions. However, `InnoDB''s crash recovery is not
affected and thus crash recovery does work regardless of the
value. Note that many operating systems and some disk hardware
fool the flush-to-disk operation. They may tell `mysqld' 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 the `InnoDB'
database. 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 command
`hdparm' to disable the caching of disk writes in hardware caches,
or use some other command specific to the hardware vendor.
* `innodb_flush_method'
If set to `fdatasync' (the default), `InnoDB' uses `fsync()' to
flush both the data and log files. If set to `O_DSYNC', `InnoDB'
uses `O_SYNC' to open and flush the log files, but uses `fsync()'
to flush the data files. If `O_DIRECT' is specified (available on
some GNU/Linux versions), `InnoDB' uses `O_DIRECT' to open the
data files, and uses `fsync()' to flush both the data and log
files. Note that `InnoDB' uses `fsync()' instead of `fdatasync()',
and it does not use `O_DSYNC' by default because there have been
problems with it on many varieties of Unix. This variable is
relevant only for Unix. On Windows, the flush method is always
`async_unbuffered' and cannot be changed.
* `innodb_force_recovery'
The crash recovery mode. Warning: This variable should be set
greater than 0 only in an emergency situation when you want to
dump your tables from a corrupt database! Possible values are from
1 to 6. The meanings of these values are described in
forcing-recovery. As a safety measure, `InnoDB' prevents any
changes to its data when this variable is greater than 0.
* `innodb_lock_wait_timeout'
The timeout in seconds an `InnoDB' transaction may wait for a lock
before being rolled back. `InnoDB' automatically detects
transaction deadlocks in its own lock table and rolls back the
transaction. `InnoDB' notices locks set using the `LOCK TABLES'
statement. The default is 50 seconds.
Note: For the greatest possible durability and consistency in a
replication setup using `InnoDB' with transactions, you should use
`innodb_flush_log_at_trx_commit=1', `sync_binlog=1', and, before
MySQL 5.0.3, `innodb_safe_binlog' in your master server `my.cnf'
file. (`innodb_safe_binlog' is not needed from 5.0.3 on.)
* `innodb_locks_unsafe_for_binlog'
This variable controls next-key locking in `InnoDB' searches and
index scans. By default, this variable is 0 (disabled), which
means that next-key locking is enabled.
Normally, `InnoDB' uses an algorithm called next-key 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 any index records it encounters. Thus, the row-level locks are
actually index record locks. The locks that `InnoDB' sets on index
records also affect the `gap' preceding that index record. If a
user has a shared or exclusive lock on record _R_ in an index,
another user cannot insert a new index record immediately before
_R_ in the order of the index. Enabling this variable causes
`InnoDB' not to use next-key locking in searches or index scans.
Next-key locking is still used to ensure foreign key constraints
and duplicate key checking. Note that enabling this variable may
cause phantom problems: Suppose that you want to read and lock all
children from the `child' table with 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;
Suppose that there is an index on the `id' column. The query scans
that index starting from the first record where `id' is greater
than 100. If the locks set on the index records do not lock out
inserts made in the gaps, another client can insert a new row into
the table. If you execute the same `SELECT' within the same
transaction, you 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,
if this variable is enabled `InnoDB' guarantees at most isolation
level `READ COMMITTED'. (Conflict serializability is still
guaranteed.)
Starting from MySQL 5.0.2, this option is even more unsafe.
`InnoDB' in an `UPDATE' or a `DELETE' only locks rows that it
updates or deletes. This greatly reduces the probability of
deadlocks, but they can happen. Note that enabling this variable
still does not allow operations such as `UPDATE' to overtake other
similar operations (such as another `UPDATE') even in the case
when they affect different rows. Consider the following example,
beginning with this table:
CREATE TABLE A(A INT NOT NULL, B INT) ENGINE = InnoDB;
INSERT INTO A VALUES (1,2),(2,3),(3,2),(4,3),(5,2);
COMMIT;
Suppose that one client executes these statements:
SET AUTOCOMMIT = 0;
UPDATE A SET B = 5 WHERE B = 3;
Then suppose that another client executes these statements
following those of the first client:
SET AUTOCOMMIT = 0;
UPDATE A SET B = 4 WHERE B = 2;
In this case, the second `UPDATE' must wait for a commit or
rollback of the first `UPDATE'. The first `UPDATE' has an
exclusive lock on row (2,3), and the second `UPDATE' while
scanning rows also tries to acquire an exclusive lock for the same
row, which it cannot have. This is because `UPDATE' two first
acquires an exclusive lock on a row and then determines whether
the row belongs to the result set. If not, it releases the
unnecessary lock, when the `innodb_locks_unsafe_for_binlog'
variable is enabled.
Therefore, `InnoDB' executes `UPDATE' one as follows:
x-lock(1,2)
unlock(1,2)
x-lock(2,3)
update(2,3) to (2,5)
x-lock(3,2)
unlock(3,2)
x-lock(4,3)
update(4,3) to (4,5)
x-lock(5,2)
unlock(5,2)
`InnoDB' executes `UPDATE' two as follows:
x-lock(1,2)
update(1,2) to (1,4)
x-lock(2,3) - wait for query one to commit or rollback
* `innodb_log_arch_dir'
The directory where fully written log files would be archived if
we used log archiving. If used, the value of this variable should
be set the same as `innodb_log_group_home_dir'. However, it is not
required.
* `innodb_log_archive'
Whether to log `InnoDB' archive files. This variable is present
for historical reasons, but is unused. Recovery from a backup is
done by MySQL using its own log files, so there is no need to
archive `InnoDB' log files. The default for this variable is 0.
* `innodb_log_buffer_size'
The size in bytes of the buffer that `InnoDB' uses to write to the
log files on disk. Sensible values range from 1MB to 8MB. The
default is 1MB. A large log buffer allows large transactions to
run without a need to write the log to disk before the
transactions commit. Thus, if you have big transactions, making
the log buffer larger saves disk I/O.
* `innodb_log_file_size'
The size in bytes of each log file in a log group. The combined
size of log files must be less than 4GB on 32-bit computers. The
default is 5MB. Sensible values range from 1MB to 1/N-th of the
size of the buffer pool, where N 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. But larger log files
also mean that recovery is slower in case of a crash.
* `innodb_log_files_in_group'
The number of log files in the log group. `InnoDB' writes to the
files in a circular fashion. The default (and recommended) is 2.
* `innodb_log_group_home_dir'
The directory path to the `InnoDB' log files. It must have the
same value as `innodb_log_arch_dir'. If you do not specify any
`InnoDB' log variables, the default is to create two 5MB files
names `ib_logfile0' and `ib_logfile1' in the MySQL data directory.
* `innodb_max_dirty_pages_pct'
This is an integer in the range from 0 to 100. The default is 90.
The main thread in `InnoDB' tries to write pages from the buffer
pool so that the percentage of dirty (not yet written) pages will
not exceed this value.
* `innodb_max_purge_lag'
This variable controls how to delay `INSERT', `UPDATE' and `DELETE'
operations when the purge operations are lagging (see
innodb-multi-versioning). The default value of this variable is
0, meaning that there are no delays.
The `InnoDB' transaction system maintains a list of transactions
that have delete-marked index records by `UPDATE' or `DELETE'
operations. Let the length of this list be PURGE_LAG. When
PURGE_LAG exceeds `innodb_max_purge_lag', each `INSERT', `UPDATE'
and `DELETE' 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
old consistent read view that could see the rows to be purged.
A typical setting for a problematic workload might be 1 million,
assuming that our transactions are small, only 100 bytes in size,
and we can allow 100MB of unpurged rows in our tables.
* `innodb_mirrored_log_groups'
The number of identical copies of log groups we keep for the
database. Currently, this should be set to 1.
* `innodb_open_files'
This variable is relevant only if you use multiple tablespaces in
`InnoDB'. It specifies the maximum number of `.ibd' files that
`InnoDB' can keep open at one time. The minimum value is 10. The
default is 300.
The file descriptors used for `.ibd' files are for `InnoDB' 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_safe_binlog'
Adds consistency guarantees between the content of `InnoDB' tables
and the binary log. See binary-log. This variable was
removed in MySQL 5.0.3, having been made obsolete by the
introduction of XA transaction support.
* `innodb_support_xa'
When set to `ON' or 1 (the default), this variable enables
`InnoDB' support for two-phase commit in XA transactions. Enabling
`innodb_support_xa' causes an extra disk flush for transaction
preparation. If you don't care about using XA, you can disable
this variable by setting it to `OFF' or 0 to reduce the number of
disk flushes and get better `InnoDB' performance. This variable
was added in MySQL 5.0.3.
* `innodb_sync_spin_loops'
The number of times a thread waits for an `InnoDB' mutex to be
freed before the thread is suspended. This variable was added in
MySQL 5.0.3.
* `innodb_table_locks'
`InnoDB' honors `LOCK TABLES'; MySQL does not return from `LOCK
TABLE .. WRITE' until all other threads have released all their
locks to the table. The default value is 1, which means that `LOCK
TABLES' causes `InnoDB' to lock a table internally. In
applications using `AUTOCOMMIT=1', `InnoDB''s internal table locks
can cause deadlocks. You can set `innodb_table_locks=0' in the
server option file to remove that problem.
* `innodb_thread_concurrency'
`InnoDB' tries to keep the number of operating system threads
concurrently inside `InnoDB' less than or equal to the limit given
by this variable. Before MySQL 5.0.8, the default value is 8. If
you have performance issues, and `SHOW ENGINE INNODB STATUS'
reveals many threads waiting for semaphores, you may have thread
`thrashing' and should try setting this variable lower or higher.
If you have a computer with many processors and disks, you can try
setting the value higher to make better use of your computer's
resources. A recommended value is the sum of the number of
processors and disks your system has. A value of 500 or greater
disables concurrency checking. Starting with MySQL 5.0.8, the
default value is 20, and concurrency checking will be disabled if
the setting is greater than or equal to 20.
* `innodb_thread_sleep_delay'
How long `InnoDB' threads sleep before joining the `InnoDB' queue,
in microseconds. The default value is 10,000. A value of 0
disables sleep. This variable was added in MySQL 5.0.3.
* `sync_binlog'
If the value of this variable is positive, the MySQL server
synchronizes its binary log to disk (`fdatasync()') after every
`sync_binlog''th write to this binary log. Note that there is one
write to the binary log per statement if in autocommit mode, and
otherwise one write per transaction. The default value is 0 which
does no synchronizing to disk. A value of 1 is the safest choice,
because in the event of a crash you lose at most one
statement/transaction from the binary log; however, it is also the
slowest choice (unless the disk has a battery-backed cache, which
makes synchronization very fast).
Info Catalog
(mysql.info) innodb-configuration
(mysql.info) innodb
(mysql.info) innodb-init
automatically generated byinfo2html