This section describes the InnoDB
-related
command options and system variables. System variables that take a
numeric value can be specified as
--
on the command line or as
var_name
=value
in option files. Many of the system variables can be changed at
runtime (see Section 5.1.5.2, “Dynamic System Variables”). (Before
MySQL 4.0.2, system variable values should be specified using
var_name
=value
--set-variable
syntax.) For more information on
specifying options and system variables, see
Section 4.2.3, “Specifying Program Options”.
It is not a good idea to configure InnoDB
to
use data files or log files on NFS volumes. Otherwise, the files
might be locked by other processes and become unavailable for
use by MySQL.
MySQL Enterprise. The MySQL Enterprise Monitor provides expert advice on InnoDB start-up options and related system variables. For more information, see http://www.mysql.com/products/enterprise/advisors.html.
InnoDB
command options:
Enables the InnoDB
storage engine, if the
server was compiled with InnoDB
support.
Use
--skip-innodb
to disable InnoDB
.
Controls whether InnoDB
creates a file
named
innodb_status.
in the MySQL data directory. If enabled,
<pid>
InnoDB
periodically writes the output of
SHOW 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.
This option is available as of MySQL 4.0.21.
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.
The increment size (in MB) for extending the size of an auto-extending tablespace file when it becomes full. The default value is 8. This variable is available starting from MySQL 4.0.24 and 4.1.5. As of MySQL 4.0.24 and 4.1.6, it can be changed at runtime as a global system variable.
The size of the buffer pool (in MB), if it is placed in the
AWE memory. 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. The maximum possible value is 63000.
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/os0proc.c
source file.
This variable is available as of MySQL 4.1.0. It 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 size in bytes of the memory buffer
InnoDB
uses to cache data and indexes of
its tables. The default value is 8MB. 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. Also, the time to initialize
the buffer pool is roughly proportional to its size. On large
installations, this initialization time may be significant.
For example, on a modern Linux x86_64 server, initialization
of a 10GB buffer pool takes approximately 6 seconds. See
Section 7.4.6, “The InnoDB
Buffer Pool”
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 KB, MB,
or GB (1024MB) by appending K
,
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 On some operating systems, files
must be less than 2GB. If you do not specify
innodb_data_file_path
, the
default behavior starting from 4.0 is to create a single 10MB
auto-extending data file named ibdata1
.
Starting from 3.23.44, you can set the file size larger than
4GB on those operating systems that support big files. You can
also use raw disk partitions as data files. For detailed
information on configuring InnoDB
tablespace files, see Section 13.2.3, “InnoDB
Configuration”.
The common part of the directory path for all
InnoDB
data files in the shared tablespace.
This setting does not affect the location of per-file
tablespaces when
innodb_file_per_table
is
enabled. The default value is the MySQL data directory. If you
specify the value as an empty string, in which case you can
use absolute file paths in
innodb_data_file_path
.
The InnoDB
shutdown mode. The default value
is 1 as of MySQL 3.23.50, which causes a “fast”
shutdown (the normal type of shutdown). If the value is 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 the value is 1,
InnoDB
skips these operations at shutdown.
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. This variable is available as
of MySQL 3.23.37.
If innodb_file_per_table
is
disabled (the default), InnoDB
creates
tables in the shared tablespace. If
innodb_file_per_table
is
enabled, InnoDB
creates each new table
using its own .ibd
file for storing data
and indexes, rather than in the shared tablespace. See
Section 13.2.3.1, “Using Per-Table Tablespaces”. This variable is
available as of MySQL 4.1.1.
There is a bug in versions <= 4.1.8 if you specify
innodb_file_per_table
in
my.cnf
! If you shut down
mysqld, records may disappear from the
secondary indexes of a table. See Bug#7496 for more
information and workarounds. This is fixed in 4.1.9, but
another bug (Bug#8021) bit the Windows version in 4.1.9, and
in the Windows version of 4.1.9, you must put the line
innodb_flush_method=unbuffered
in your
my.cnf
or my.ini
to get mysqld to work.
innodb_flush_log_at_trx_commit
If the value of
innodb_flush_log_at_trx_commit
is 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 the
value is 1, 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 the value is 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 (prior to MySQL 4.0.13, the default is 0).
A value of 1 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. With a value of 0, any mysqld
process crash can erase the last second of transactions. With
a value of 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.
For the greatest possible durability and consistency in a
replication setup using InnoDB
with
transactions, use
innodb_flush_log_at_trx_commit=1
,
sync_binlog=1
, and
innodb_safe_binlog
in your master server
my.cnf
file.
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.
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, and
fsync()
to flush the data files. If
O_DIRECT
is specified (available on some
GNU/Linux versions starting from MySQL 4.0.14),
InnoDB
uses O_DIRECT
to
open the data files, and uses fsync()
to
flush both the data and log files. Note that starting from
MySQL 3.23.41, 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.
This variable is available as of MySQL 3.23.40.
Different values of this variable can have a marked effect on
InnoDB
performance. For example, on some
systems where InnoDB
data and log files are
located on a SAN, it has been found that setting
innodb_flush_method
to
O_DIRECT
can degrade performance of simple
SELECT
statements by a factor
of three.
The crash recovery mode. Possible values are from 0 to 6. The
meanings of these values are described in
Section 13.2.7.2, “Forcing InnoDB
Recovery”.
This variable should be set greater than 0 only in an
emergency situation when you want 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 variable is
available starting from MySQL 3.23.44.
The timeout in seconds an InnoDB
transaction may wait for a lock before being rolled back. The
default is 50 seconds.
A lock wait for a MySQL table lock does not happen inside
InnoDB
, and this timeout does not apply to
waits for table locks.
InnoDB
does detect transaction deadlocks in
its own lock table immediately and rolls back one transaction.
The lock wait timeout value does not apply to such a wait.
innodb_locks_unsafe_for_binlog
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, 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 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 13.2.9.4, “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 to READ
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 than
innodb_locks_unsafe_for_binlog
.
For additional details about the effect of isolation level on
gap locking, see Section 12.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 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
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 additional information about phantoms, see
Section 13.2.9.5, “Avoiding the Phantom Problem Using Next-Key Locking”.
innodb_locks_unsafe_for_binlog
is available as of MySQL 4.1.4.
The directory where fully written log files would be archived
if we used log archiving. The value of this variable should
currently be set the same as
innodb_log_group_home_dir
.
Starting from MySQL 4.0.6, there is no need to set this
variable.
Whether to log InnoDB
archive files. This
variable 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.
The size in bytes of the buffer that InnoDB
uses to write to the log files on disk. The default value is
1MB. Sensible values range from 1MB to 8MB. 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.
The size in bytes of each log file in a log group. The
combined size of log files must be less than 4GB. The default
value 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.
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 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.
This is an integer in the range from 0 to 100. The default
value 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. Available starting from 4.0.13 and 4.1.1.
This variable controls how to delay
INSERT
,
UPDATE
, and
DELETE
operations when the
purge operations are lagging (see
Section 13.2.10, “InnoDB
Multi-Versioning”). The default value
of this variable is 0, meaning that there are no delays.
innodb_max_purge_lag
is
available as of MySQL 4.0.22 and 4.1.6.
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 transactions are small, only 100 bytes
in size, and it is allowable 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 290328385 Purge done for trx's n:o < 0 290315608 undo n:o < 0 17 History list length 20
The number of identical copies of log groups to keep for the database. This should be set to 1.
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 value is 300. This variable
is available as of MySQL 4.1.1.
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
If this option is given, then after a crash recovery by
InnoDB
, mysqld truncates
the binary log after the last not-rolled-back transaction in
the log. The option also causes InnoDB
to
print an error if the binary log is smaller or shorter than it
should be. See Section 5.3.4, “The Binary Log”.
Starting from MySQL 4.0.20, and 4.1.2,
InnoDB
honors LOCK
TABLES
. If autocommit =
0
, InnoDB
honors
LOCK TABLES
; MySQL does not
return from LOCK TABLES ... WRITE
until all
other threads have released all their locks to the table. The
default value of
innodb_table_locks
is 1,
which means that LOCK TABLES
causes InnoDB to lock a table internally if
autocommit = 0
.
InnoDB
tries to keep the number of
operating system threads concurrently inside
InnoDB
less than or equal to the limit
given by this variable. The default value is 8. If you have
low performance and SHOW 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
better utilize the resources of your computer. A recommended
value is 2 times the number of CPUs plus the number of disks.
A value of 500 or greater disables the concurrency checking.
This variable is available starting from MySQL 3.23.44 and
4.0.1.
If the value of this variable is greater than 0, the MySQL
server synchronizes its binary log to disk (using
fdatasync()
) after every
sync_binlog
writes to the
binary log. There is one write to the binary log per statement
if autocommit is enabled, and one write per transaction
otherwise. The default value of
sync_binlog
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
or 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). This variable was
added in MySQL 4.1.3.
User Comments
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 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.
http://crazytoon.com/2007/04/03/mysql-ibdata-files-do-not-shrink-on-database-deletion-innodb/
Commentary on Innodb parameters for an 8way machine:
http://krow.livejournal.com/542306.html
Changing innodb_log_file_size can yield strange errors, such as: Incorrect information in file: './db010840/notifications.frm'
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.
[*] 2009/10 Initialising a 10 GB buffer pool takes 6 seconds, larger configurations may take proportionally longer.
Add your own comment.