This section describes the
command options and system variables. System variables that take a
numeric value can be specified as
on the command line or as
in option files. Many of the system variables can be changed at
runtime (see Section 126.96.36.199, “Dynamic System Variables”). (Before
MySQL 4.0.2, system variable values should be specified using
--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
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.
InnoDB storage engine, if the
server was compiled with
In this case, the server will not start if the default storage
engine is set to
--default-storage-engine to set
the default to some other engine if necessary.
InnoDB creates a file
in the MySQL data directory. If enabled,
InnoDB periodically writes the output of
INNODB STATUS to this file.
This option is available as of MySQL 4.0.21.
InnoDB storage engine. See the
The size in bytes of a memory pool
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 shared 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
Those files are auto-extending regardless of the value of
The initial extensions are by small amounts, after which
extensions occur in increments of 4MB.
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,
the window in the 32-bit address space of
that AWE memory. A good value for
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
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.5.2, “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
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
default behavior starting from 4.0 is to create a single 10MB
auto-extending data file named
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
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
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 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
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;
always uses the default value. This variable is available as
of MySQL 3.23.37.
disabled (the default),
tables in the shared tablespace. If
InnoDB creates each new table
using its own
.ibd file for storing data
and indexes, rather than in the shared tablespace. See
Section 188.8.131.52, “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
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
to get mysqld to work.
If the value of
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
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-safe-binlog in your master server
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.
Defines the method used to
flush data to the
files and log
files, which can affect I/O throughput. This variable
is only configurable on Unix and Linux systems. On Windows
systems, the flush method is always
async_unbuffered and cannot be changed.
This variable is available as of MySQL 3.23.40.
innodb_flush_method options include:
fsync() system call to flush
both the data and log files.
the default setting. Starting from MySQL 3.23.41,
O_SYNC to open and flush the log files,
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.
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 starting from MySQL 4.0.14.
How each settings 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
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
InnoDB buffer pool and the
operating system's 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
The crash recovery mode. Possible values are from 0 to 6. The meanings of these values are described in Section 184.108.40.206, “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
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.
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.
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 220.127.116.11, “InnoDB Record, Gap, and Next-Key Locks”.
By default, the value of
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:
only index-record locks for searches and index scans.
does not disable the use of gap locking for foreign-key
constraint checking or duplicate-key checking.
is a global setting and affects all sessions, whereas the
isolation level can be set globally for all sessions, or
individually per session.
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
For additional details about the effect of isolation level on
gap locking, see Section 12.3.6, “SET TRANSACTION Syntax”.
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
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 guarantees at most an
isolation level of
COMMITTED. (Conflict serializability is still
guaranteed.) For additional information about phantoms, see
Section 18.104.22.168, “Avoiding the Phantom Problem Using Next-Key Locking”.
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
Starting from MySQL 4.0.6, there is no need to set this
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
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
enables 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
N-th of the size of the buffer
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_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
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
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.
available as of MySQL 4.0.22 and 4.1.6.
InnoDB transaction system maintains a
list of transactions that have index records delete-marked by
DELETE operations. Let the
length of this list be
DELETE operation is delayed by
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:
------------ 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
InnoDB. It specifies the maximum number
.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
InnoDB only. They are independent
of those specified by the
option, and do not affect the operation of the table cache.
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
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,
LOCK TABLES; MySQL does not
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
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
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
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.