14.17 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.

InnoDB Command Options

In MySQL 5.1, this option caused the server to behave as if
the built-in InnoDB were not present, which
enabled the InnoDB Plugin to be used
instead. In MySQL 5.5, InnoDB
is the default storage engine and InnoDB
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.

Whether the InnoDBadaptive hash
index is enabled or disabled. It may be desirable,
depending on your workload, to dynamically enable or disable
adaptive 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. See
Section 14.7.3, “Adaptive Hash Index” 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 the SUPER 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.

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
8MB.

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.

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, setting
innodb_buffer_pool_instances
to a value greater than 1 can improve the scalability on a
busy server.

A larger buffer pool requires less disk I/O to access the same
table data more than once. On a dedicated database server, you
might set the buffer pool size to 80% of the machine's
physical memory size. Be aware of the following potential
issues when configuring buffer pool size, and be prepared to
scale back the size of the buffer pool if necessary.

Competition for physical memory can 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 buffer pool size.

Address space for the buffer pool 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 instances with large buffer
pools, initialization time might be significant.

Whether InnoDB performs
change buffering,
an optimization that delays write operations to secondary
indexes so that the I/O operations can be performed
sequentially. Permitted values are described in the following
table.

Table 14.7 Permitted Values for innodb_change_buffering

Value

Description

none

Do not buffer any operations.

inserts

Buffer insert operations.

deletes

Buffer delete marking operations; strictly speaking, the writes that
mark index records for later deletion during a purge
operation.

changes

Buffer inserts and delete-marking operations.

purges

Buffer the physical deletion operations that happen in the background.

Sets a debug flag for InnoDB change
buffering. A value of 1 forces all changes to the change
buffer. A value of 2 causes a crash at merge. A default value
of 0 indicates that the change buffering debug flag is not
set. This option is only available when debugging support is
compiled in using the WITH_DEBUGCMake option.

InnoDB can use checksum validation on all
pages read from disk to ensure extra fault tolerance against
broken hardware or data files. This validation is enabled by
default. Under specialized circumstances (such as when running
benchmarks) this safety feature can be disabled with
--skip-innodb-checksums.

Determines the number of
threads that can enter
InnoDB concurrently. 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 permitted to enter
InnoDB, it is given a number of “
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. The default value is 500.

With a small innodb_concurrency_tickets
value, small transactions that only need to process a few rows
compete fairly with larger transactions that process many
rows. The disadvantage of a small
innodb_concurrency_tickets value is that
large transactions must loop through the queue many times
before they can complete, which extends the amount of time
required to complete their task.

With a large innodb_concurrency_tickets
value, large transactions spend less time waiting for a
position at the end of the queue (controlled by
innodb_thread_concurrency)
and more time retrieving rows. Large transactions also require
fewer trips through the queue to complete their task. The
disadvantage of a large
innodb_concurrency_tickets value is that
too many large transactions running at the same time can
starve smaller transactions by making them wait a longer time
before executing.

With a non-zero
innodb_thread_concurrency
value, you may need to adjust the
innodb_concurrency_tickets value up or down
to find the optimal balance between larger and smaller
transactions. The SHOW ENGINE INNODB STATUS
report shows the number of tickets remaining for an executing
transaction in its current pass through the queue. This data
may also be obtained from the
TRX_CONCURRENCY_TICKETS column of the
INFORMATION_SCHEMA.INNODB_TRX
table.

Defines the path and file size for individual
InnoDBsystem
tablespacedata
files. The full directory path for system tablespace
data files is formed by concatenating path defined by
innodb_data_home_dir and
innodb_data_file_path. File sizes are
specified KB, MB or GB (1024MB) by appending
K, M or
G to the size value. If specifying the data
file size in kilobytes (KB), do so in multiples of 1024.
Otherwise, KB values are rounded 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 specify
innodb_data_file_path, the
default behavior is to create a single auto-extending data
file, slightly larger than 10MB, 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 operating systems that support
large files. You can also
use raw disk partitions as
data files. For more information about configuring
system tablespace data files, see
Section 14.9.1, “InnoDB Startup Configuration”.

When enabled (the default), InnoDB stores
all data twice, first to the
doublewrite
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.

The InnoDBshutdown mode. If the
value is 0, InnoDB does a
slow shutdown, a
full purge and a change
buffer merge before shutting down. If the value is 1 (the
default), InnoDB skips these operations at
shutdown, a process known as a
fast 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 the
crash 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.

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 the
file format tag in the
system
tablespace (for example, Antelope or
Barracuda). If the tag is checked and is
higher than that supported by the current version of
InnoDB, an error occurs and
InnoDB does not start. If the tag is not
higher, InnoDB sets the value of
innodb_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 of
InnoDB, an error occurs and
InnoDB does not start. If the tag is not
higher, InnoDB sets the value of
innodb_file_format_check to
the file format tag, which is the value seen at runtime.

Note

Despite the default value sometimes being displayed as
ON or OFF, always use
the numeric values 1 or 0 to turn this option on or off in
your configuration file or command line string.

At server startup, InnoDB sets the value of
this variable to the file
format tag in the
system
tablespace (for example, Antelope or
Barracuda). If the server creates or opens
a table with a “higher” file format, it sets the
value of
innodb_file_format_max to
that format.

When innodb_file_per_table is disabled,
InnoDB stores the data for tables and
indexes in the ibdata
files that make up the
system
tablespace. This setting reduces the performance
overhead of file system operations for operations such as
DROP TABLE or
TRUNCATE 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 an instance, avoid
loading huge amounts of temporary data on a space-constrained
system when innodb_file_per_table is
disabled. 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 instead of the system tablespace. The storage for
these tables is reclaimed when the tables are dropped or
truncated. This setting enables
InnoDBfeatures such as table
compression. See
Section 14.10.4, “InnoDB File-Per-Table Tablespaces” for more
information.

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 a second of transactions in a crash.

The default value of 1 is required for full ACID
compliance. With this value, the contents of the
InnoDBlog buffer are
written out to the log
file at each transaction commit and the log file is
flushed to disk.

With a value of 0, the contents of the
InnoDB log buffer are written to the
log file approximately once per second and the log file is
flushed to disk. No writes from the log buffer to the log
file are performed at transaction commit. Once-per-second
flushing is not guaranteed to happen every second due to
process scheduling issues. Because the flush to disk
operation only occurs approximately once per second, you
can lose up to a second of transactions with any
mysqld process crash.

With a value of 2, the contents of the
InnoDB log buffer are written to the
log file after each transaction commit and the log file is
flushed to disk approximately once per second.
Once-per-second flushing is not 100% guaranteed to happen
every second, due to process scheduling issues. Because
the flush to disk operation only occurs approximately once
per second, you can lose up to a second of transactions in
an operating system crash or a power outage.

InnoDBcrash 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, use
innodb_flush_log_at_trx_commit=1 and
sync_binlog=1 in your master server
my.cnf file.

Caution

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. In this case, the durability of
transactions is not guaranteed even with the setting 1, and
in the worst case, a power outage can 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 to disable the caching of disk writes in
hardware caches.

If innodb_flush_method is set to
NULL on a Unix-like system, the
fsync option is used by default. If
innodb_flush_method is set to
NULL on Windows, the
async_unbuffered option is used by default.

The innodb_flush_method options for
Unix-like systems include:

fsync: InnoDB uses
the fsync() system call to flush both
the data and log files. fsync is the
default setting.

O_DSYNC: InnoDB uses
O_SYNC to open and flush the log files,
and fsync() to flush the data files.
InnoDB does not use
O_DSYNC directly because there have
been problems with it on many varieties of Unix.

littlesync: This option is used for
internal performance testing and is currently unsupported.
Use at your own risk.

nosync: This option is used for
internal performance testing and is currently unsupported.
Use at your own risk.

O_DIRECT: InnoDB
uses O_DIRECT (or
directio() on Solaris) to open the data
files, and uses fsync() to flush both
the data and log files. This option is available on some
GNU/Linux versions, FreeBSD, and Solaris.

normal: InnoDB uses
simulated asynchronous I/O and buffered I/O. This option
is used for internal performance testing and is currently
unsupported. Use at your own risk.

unbuffered: InnoDB
uses simulated asynchronous I/O and non-buffered I/O. This
option is used for internal performance testing and is
currently unsupported. Use at your own risk.

How each setting affects performance depends on hardware
configuration and workload. Benchmark your particular
configuration to decide which setting to use, or whether to
keep the default setting. Examine the
Innodb_data_fsyncs status
variable to see the overall number of
fsync() calls for each setting. The mix of
read and write operations in your workload can affect how a
setting performs. For example, on a system with a hardware
RAID controller and battery-backed write cache,
O_DIRECT can help to avoid double buffering
between the InnoDB buffer pool and the
operating system file system cache. On some systems where
InnoDB data and log files are located on a
SAN, the default value or O_DSYNC might be
faster for a read-heavy workload with mostly
SELECT statements. Always test this
parameter with hardware and workload that reflect your
production environment. For general I/O tuning advice, see
Section 8.5.7, “Optimizing InnoDB Disk I/O”.

Prior to MySQL 5.1.24, the default
innodb_flush_method option was named
fdatasync. When
fdatasync was specified,
InnoDB used the fsync()
system call to flush both the data and log files. To avoid
confusing the fdatasync option name with
the fdatasync() system call, the option
name was changed to fsync in MySQL 5.1.24.

Permits InnoDB to load tables at startup
that are marked as corrupted. Use only during troubleshooting,
to recover data that is otherwise inaccessible. When
troubleshooting is complete, disable this setting and restart
the server.

Only set this variable to a value greater than 0 in an
emergency situation so that you can start
InnoDB and dump your tables. As a safety
measure, InnoDB prevents
INSERT,
UPDATE, or
DELETE operations when
innodb_force_recovery is greater than 0.

The innodb_io_capacity limit
is a total limit for all buffer pool instances. When dirty
pages are flushed, the limit is divided equally among buffer
pool instances.

innodb_io_capacity should be
set to approximately the number of I/O operations that the
system can perform per second. Ideally, keep the setting as
low as practical, but not so low that background activities
fall behind. If the value is too high, data is removed from
the buffer pool and insert buffer too quickly for caching to
provide a significant benefit.

The default value is 200. For busy systems capable of higher
I/O rates, you can set a higher value to help the server
handle the background maintenance work associated with a high
rate of row changes.

In general, you can increase the value as a function of the
number of drives used for InnoDB
I/O. For example, you can increase the value on systems that
use multiple disks or solid-state disks (SSD).

The default setting of 200 is generally sufficient for a
lower-end SSD. For a higher-end, bus-attached SSD, consider a
higher setting such as 1000, for example. For systems with
individual 5400 RPM or 7200 RPM drives, you might lower the
value to the former default of 100, which
represents an estimated proportion of the I/O operations per
second (IOPS) available to older-generation disk drives that
can perform about 100 IOPS.

Although you can specify a very high value such as one
million, in practice such large values have little if any
benefit. Generally, a value of 20000 or higher is not
recommended unless you have proven that lower values are
insufficient for your workload.

Consider write workload when tuning
innodb_io_capacity. Systems
with large write workloads are likely to benefit from a higher
setting. A lower setting may be sufficient for systems with a
small write workload.

You can set innodb_io_capacity in the MySQL
option file (my.cnf or
my.ini) or change it dynamically using a
SET GLOBAL statement, which requires the
SUPER privilege.

For tables that use
REDUNDANT
or
COMPACT
row format, this option does not affect the permitted index
key prefix length. When this setting is enabled, attempting to
create an index prefix with a key length greater than 3072 for
a REDUNDANT or COMPACT
table causes an
ER_INDEX_COLUMN_TOO_LONG
error.

The length of time in seconds an InnoDBtransaction 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 another
InnoDB transaction waits at most this many
seconds for write access to the row before issuing the
following error:

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 to
InnoDB row locks only. A MySQL
table lock does not
happen inside InnoDB and this timeout does
not apply to waits for table locks.

innodb_lock_wait_timeout can
be set at runtime with the SET GLOBAL or
SET SESSION statement. Changing the
GLOBAL setting requires the
SUPER privilege and affects the operation
of all clients that subsequently connect. Any client can
change the SESSION setting for
innodb_lock_wait_timeout,
which affects only that client.

This variable affects how InnoDB uses gap
locking for searches and index scans. Normally,
InnoDB uses an algorithm called
next-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, row-level locks are actually index-record locks. In
addition, a next-key lock on an index record also affects the
gap before the 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
record R in an index, another session
cannot insert a new index record in the gap immediately before
R in the index order. See
Section 14.8.1, “InnoDB Locking”.

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 effects of enabling
innodb_locks_unsafe_for_binlog are the same
as setting the transaction isolation level to
READ COMMITTED, with these
exceptions:

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.

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 the id column of the
child 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
the 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 same
SELECT 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, if
innodb_locks_unsafe_for_binlog is enabled,
InnoDB guarantees at most an isolation
level of READ COMMITTED.
(Conflict serializability is still guaranteed.) For more
information about phantoms, see
Section 14.8.4, “Phantom Rows”.

Enabling innodb_locks_unsafe_for_binlog has
additional effects:

For UPDATE or
DELETE statements,
InnoDB holds locks only for rows that
it updates or deletes. Record locks for nonmatching rows
are released after MySQL has evaluated the
WHERE 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 the
WHERE condition of the
UPDATE. If the row matches
(must be updated), MySQL reads the row again and this time
InnoDB either locks it or waits for a
lock on it.

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 each
UPDATE, it first acquires an
exclusive lock for each row, and then determines whether to
modify it. If InnoDB does not
modify the row and
innodb_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 first UPDATE
acquires x-locks and does not release any of them:

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 the WHERE
condition of the UPDATE:

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, approaches the limit
but does not exceed it. The default value is 5MB.

Generally, the combined size of the log files should be large
enough that the server can smooth out peaks and troughs in
workload activity, which often means that there is enough redo
log space to handle more than an hour of write activity. The
larger the value, the less checkpoint flush activity is
required in the buffer pool, saving disk I/O. Larger log files
also make crash
recovery slower, although improvements to recovery
performance in MySQL 5.5 and higher make the log file size
less of a consideration.

The directory path to the InnoDBredo log files, whose
number is specified by
innodb_log_files_in_group. If
you do not specify any InnoDB log
variables, the default is to create two files named
ib_logfile0 and
ib_logfile1 in the MySQL data directory.
Log file size is given by the
innodb_log_file_size system
variable.

The InnoDB transaction system maintains a
list of transactions that have index records delete-marked by
UPDATE or
DELETE operations. The length
of the list represents the
purge_lag value. When
purge_lag exceeds
innodb_max_purge_lag,
INSERT,
UPDATE, and
DELETE operations are 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 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:

Non-zero values protect against the
buffer pool being
filled by data that is referenced only for a brief period,
such as during a full
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 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
milliseconds 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 configuration option is only relevant if you use multiple
InnoDBtablespaces. 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 for InnoDB 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.

When this option is enabled, information about all
deadlocks in
InnoDB user transactions is recorded in the
mysqlderror
log. Otherwise, you see information about only the last
deadlock, using the SHOW ENGINE INNODB
STATUS command. An occasional
InnoDB deadlock is not necessarily an
issue, because InnoDB detects the condition
immediately and rolls back one of the transactions
automatically. You might use this option to troubleshoot why
deadlocks are occurring 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 issue
DML or SELECT ... FOR
UPDATE statements for multiple tables, so that each
transaction accesses the tables in the same order, thus
avoiding the deadlock condition.

Defines the number of undo log pages that purge parses and
processes in one batch from the
history list. The
innodb_purge_batch_size option also defines
the number of undo log pages that purge frees after every 128
iterations through the undo logs.

The innodb_purge_batch_size option is
intended for advanced performance tuning in combination with
the innodb_purge_threads
setting. Most MySQL users need not change
innodb_purge_batch_size from its default
value.

The number of background threads devoted to the
InnoDBpurge 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 the
master 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.

Enables the random
read-ahead technique
for optimizing InnoDB I/O. Random
read-ahead functionality was removed from the InnoDB
Plugin (version 1.0.4) and was therefore not
included in MySQL 5.5.0 when InnoDB Plugin
became the “built-in” version of
InnoDB. Random read-ahead was reintroduced
in MySQL 5.1.59 and 5.5.16 and higher along with the
innodb_random_read_ahead configuration
option, which is disabled by default.

Controls the sensitivity of linear
read-ahead that
InnoDB uses to prefetch pages into the
buffer pool. If
InnoDB reads at least
innodb_read_ahead_threshold pages
sequentially from an extent
(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.

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 of SHOW ENGINE INNODB STATUS and
are displayed in the BUFFER POOL AND MEMORY
section of the
SHOW ENGINE
INNODB STATUS output.

On Linux systems, running multiple MySQL servers (typically
more than 12) with default settings for
innodb_read_io_threads,
innodb_write_io_threads,
and the Linux aio-max-nr setting can
exceed system limits. Ideally, increase the
aio-max-nr setting; as a workaround, you
might reduce the settings for one or both of the MySQL
configuration options.

InnoDBrolls
back only the last statement on a transaction timeout
by default. If
--innodb_rollback_on_timeout is
specified, a transaction timeout causes
InnoDB to abort and roll back the entire
transaction (the same behavior as in MySQL 4.1).

Note

If the start-transaction statement was
START
TRANSACTION or
BEGIN
statement, rollback does not cancel that statement. Further
SQL statements become part of the transaction until the
occurrence of COMMIT,
ROLLBACK,
or some SQL statement that causes an implicit commit.

Defines the number of
rollback segments
used by InnoDB for data-modifying
transactions that generate undo records. Each rollback segment
can support a maximum of 1023 data-modifying transactions.

This setting is appropriate for tuning performance if you
observe mutex contention related to the undo logs.

Although you can increase or decrease the number of rollback
segments used by InnoDB, the number of
rollback segments physically present in the system never
decreases. Thus, you might start with a low value for this
parameter and gradually increase it, to avoid allocating
rollback segments that are not required. The
innodb_rollback_segments
default value is 128, which is also the maximum value.

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. For more
information, see
Section 14.9.9, “Configuring Spin Lock Polling”.

How the server treats NULL values when
collecting statistics
about the distribution of index values for
InnoDB tables. Permitted values are
nulls_equal,
nulls_unequal, and
nulls_ignored. For
nulls_equal, all NULL
index values are considered equal and form a single value
group with a size equal to the number of
NULL values. For
nulls_unequal, NULL
values are considered unequal, and each
NULL forms a distinct value group of size
1. For nulls_ignored,
NULL values are ignored.

To change the setting, issue the statement SET GLOBAL
innodb_stats_on_metadata=mode,
where mode is
either ON or OFF (or
1 or 0). Changing the
setting requires the SUPER privilege and
immediately affects the operation of all connections.

When innodb_strict_mode is enabled,
InnoDB returns errors rather than warnings
for certain conditions.

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 is enabled,
InnoDB raises error conditions in certain
cases, rather than issuing a warning and processing the
specified statement (perhaps with unintended behavior). This
is analogous to
sql_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 for CREATE
TABLE, ALTER TABLE
and CREATE INDEX statements.
innodb_strict_mode also enables a record
size check, so that an INSERT or
UPDATE never fails due to the record being
too large for the selected page size.

Oracle recommends enabling
innodb_strict_mode when using
ROW_FORMAT and
KEY_BLOCK_SIZE clauses in
CREATE TABLE,
ALTER TABLE, and
CREATE INDEX statements. When
innodb_strict_mode is disabled,
InnoDB ignores conflicting clauses and
creates the table or index with only a warning in the message
log. The resulting table might have different characteristics
than intended, such as lack of compression support when
attempting to create a compressed table. When
innodb_strict_mode is enabled, such
problems generate an immediate error and the table or index is
not created.

You can enable or disable
innodb_strict_mode on the command line when
starting mysqld, or in a MySQL
configuration
file. You can also enable or disable
innodb_strict_mode at runtime with the
statement SET [GLOBAL|SESSION]
innodb_strict_mode=mode,
where mode is
either ON or OFF.
Changing the GLOBAL setting requires the
SUPER privilege and affects the operation
of all clients that subsequently connect. Any client can
change the SESSION setting for
innodb_strict_mode, and the setting affects
only that client.

Enables InnoDB support for two-phase commit
in XA transactions, causing an
extra disk flush for transaction preparation. 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 disable
innodb_support_xa, transactions can be
written to the binary log in a different order than the live
database is committing them, which can produce different data
when the binary log is replayed in disaster recovery or on a
replication slave. Do not disable
innodb_support_xa 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 disable 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 disable 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 tries to keep the number of
operating system threads concurrently inside
InnoDB less than or equal to the limit
given by this variable (InnoDB uses
operating system threads to process user transactions). Once
the number of threads reaches this limit, additional threads
are placed into a wait state within a “First In, First
Out” (FIFO) queue for execution. Threads waiting for
locks are not counted in the number of concurrently executing
threads.

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 and queries in queue
counters in the ROW OPERATIONS
section of SHOW ENGINE INNODB STATUS
output.

Consider setting this variable if your MySQL instance shares
CPU resources with other applications, or if your workload or
number of concurrent users is growing. The correct setting
depends on workload, computing environment, and the version of
MySQL that you are running. You will need to test a range of
values to determine the setting that provides the best
performance. innodb_thread_concurrency is a
dynamic variable, which allows you to experiment with
different settings on a live test system. If a particular
setting performs poorly, you can quickly set
innodb_thread_concurrency back to 0.

Use the following guidelines to help find and maintain an
appropriate setting:

If the number of concurrent user threads for a workload is
less than 64, set
innodb_thread_concurrency=0.

If your workload is consistently heavy or occasionally
spikes, start by setting
innodb_thread_concurrency=128 and then
lowering the value to 96, 80, 64, and so on, until you
find the number of threads that provides the best
performance. For example, suppose your system typically
has 40 to 50 users, but periodically the number increases
to 60, 70, or even 200. You find that performance is
stable at 80 concurrent users but starts to show a
regression above this number. In this case, you would set
innodb_thread_concurrency=80 to avoid
impacting performance.

If you do not want InnoDB to use more
than a certain number of vCPUs for user threads (20 vCPUs,
for example), set
innodb_thread_concurrency to this
number (or possibly lower, depending on performance
results). If your goal is to isolate MySQL from other
applications, you may consider binding the
mysqld process exclusively to the
vCPUs. Be aware, however, that exclusive binding could
result in non-optimal hardware usage if the
mysqld process is not consistently
busy. In this case, you might bind the
mysqld process to the vCPUs but also
allow other applications to use some or all of the vCPUs.

Note

From an operating system perspective, using a resource
management solution to manage how CPU time is shared
among applications may be preferable to binding the
mysqld process. For example, you
could assign 90% of vCPU time to a given application
while other critical process are
not running, and scale that value back to 40%
when other critical processes are
running.

innodb_thread_concurrency values that
are too high can cause performance regression due to
increased contention on system internals and resources.

In some cases, the optimal
innodb_thread_concurrency setting can
be smaller than the number of vCPUs.

Monitor and analyze your system regularly. Changes to
workload, number of users, or computing environment may
require that you adjust the
innodb_thread_concurrency setting.

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 configure 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 SHOW
ENGINE INNODB STATUS\G output.

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 with
innodb_use_native_aio=0. This
option may also be disabled automatically during startup if
InnoDB detects a potential problem such as
a combination of tmpdir location,
tmpfs file system, and Linux kernel that
does not support AIO on tmpfs.

Pauses purging of delete-marked records while allowing the
purge view to be updated. This option artificially creates a
situation in which the purge view is updated but purges have
not yet been performed. This option is only available if
debugging support is compiled in using the
WITH_DEBUGCMake option.

Sets a debug flag that limits
TRX_RSEG_N_SLOTS to a given value for the
trx_rsegf_undo_find_free function that
looks for free slots for undo log segments. This option is
only available if debugging support is compiled in using the
WITH_DEBUGCMake option.

On Linux systems, running multiple MySQL servers (typically
more than 12) with default settings for
innodb_read_io_threads,
innodb_write_io_threads,
and the Linux aio-max-nr setting can
exceed system limits. Ideally, increase the
aio-max-nr setting; as a workaround, you
might reduce the settings for one or both of the MySQL
configuration options.

Also take into consideration the value of
sync_binlog, which controls
synchronization of the binary log to disk.

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.

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).

Posted by
Simon Mudd
on
October 13, 2009

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.