(mysql.info.gz) InnoDB start
Info Catalog
(mysql.info.gz) InnoDB configuration
(mysql.info.gz) InnoDB
(mysql.info.gz) InnoDB init
15.5 `InnoDB' Startup Options
=============================
This section describes the `InnoDB'-related server options. In MySQL
4.0 and up, all of them can be specified in `--OPT_NAME=VALUE' form on
the command line or in option files. Before MySQL 4.0, numeric options
should be specified using `--set-variable=OPT_NAME=VALUE' or `-O
OPT_NAME=VALUE' syntax.
`innodb_additional_mem_pool_size'
The size 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 will need to
allocate here. If `InnoDB' runs out of memory in this pool, it
will start to allocate memory from the operating system, and write
warning messages to the MySQL error log. The default value is 1MB.
`innodb_autoextend_increment'
The increment size (in megabytes) for extending the size of an
autoextending tablespace when it becomes full. The default value
is 8. This option 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.
`innodb_buffer_pool_awe_mem_mb'
The size of the buffer pool (in MB), if it is placed in the AWE
memory of 32-bit Windows. Available from MySQL 4.1.0 and relevant
only in 32-bit Windows. If your 32-bit Windows operating system
supports more than 4GB memory, so-called "Address Windowing
Extensions," you can allocate the `InnoDB' buffer pool into the
AWE physical memory using this parameter. The maximum possible
value for this is 64000. If this parameter is specified,
`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.
`innodb_buffer_pool_size'
The size 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 the physical memory might cause paging in the
operating system.
`innodb_checksums'
`InnoDB' uses checksum validation on all pages read from the disk
to ensure extra fault tolerance against broken hardware or data
files. However, under some rare circumstances (such as when
running benchmarks) this "extra safety" feature is unneeded. In
such cases, this option (which is enabled by default) can be
turned off with `--skip-innodb-checksums'. This option 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 acquired by concatenating
`innodb_data_home_dir' to each path specified here. The file sizes
are specified in megabytes or gigabytes (1024MB) by appending `M'
or `G' to the size value. The sum of the sizes of the files must
be at least 10MB. 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 bigger than 4GB on those operating
systems that support big files. You can also use raw disk
partitions as data files. 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 this also 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
option can be used to disable this functionality. Like
`innodb_checksums', this option 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 option was added in MySQL
5.0.3.
`innodb_fast_shutdown'
By default, `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 parameter to 1, `InnoDB'
skips these operations at shutdown. This option is available
starting from MySQL 3.23.44 and 4.0.1. Its default value is 1
starting from 3.23.50.
`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. This option is
available as of MySQL 3.23.37.
`innodb_file_per_table'
* CRITICAL BUG in 4.1 if you specify `innodb_file_per_table'
in `my.cnf'! If you shut down `mysqld', then 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' to your `my.cnf' or `my.ini' to
get `mysqld' to work.
This option causes `InnoDB' to create each new table using its own
`.ibd' file for storing data and indexes, rather than in the
shared tablespace. Multiple tablespaces. This option is
available as of MySQL 4.1.1.
`innodb_flush_log_at_trx_commit'
When `innodb_flush_log_at_trx_commit' is set to 0, once per second
the log buffer is written out to the log file, 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), at
each transaction commit the log buffer is written out to the log
file, and the flush to disk operation is performed on the log
file. When set to 2, at each commit the log buffer is written out
to the file, but the flush to disk operation is not performed on
it. However, the flushing on the log file takes place once per
second also in the case of 2.
We must note that the once-per-second flushing is not 100%
guaranteed to happen every second, due to process scheduling
issues.
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.
Note that many operating systems and some disk hardware fool in the
flush-to-disk operation. They may tell to `mysqld' that the flush
has taken place, 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.
The default value of this option is 1 (prior to MySQL 4.0.13, the
default is 0).
`innodb_flush_method'
This option is relevant only on Unix systems. If set to
`fdatasync', `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
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 `InnoDB' does not use `fdatasync' or `O_DSYNC' by
default because there have been problems with them on many Unix
flavors. This option is available as of MySQL 3.23.40.
`innodb_force_recovery'
Warning: This option should be defined 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 a user from modifying data when this
option is greater than 0. This option is available starting from
MySQL 3.23.44.
`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. Beginning with MySQL 4.0.20 and 4.1.2, InnoDB notices
locks set using the `LOCK TABLES' statement. Before that, if you
use the `LOCK TABLES' statement, or other transaction-safe storage
engines than `InnoDB' in the same transaction, a deadlock may arise
that `InnoDB' cannot notice. In cases like this, the timeout is
useful to resolve the situation. The default is 50 seconds.
`innodb_locks_unsafe_for_binlog'
This option turns off next-key locking in `InnoDB' searches and
index scans. Default value for this option is false.
Normally `InnoDB' uses an algorithm called "next-key locking."
`InnoDB' does the row-level locking in such a way that when it
searches or scans an index of a table, it sets shared or exclusive
locks on the index records it encounters. Thus the row-level locks
are actually index record locks. The locks `InnoDB' sets on index
records also affect the "gap" before 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
index order. This option 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 using this option 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 intent 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 will
scan that index starting from the first record where id is bigger
than 100. If the locks set on the index records do not lock out
inserts made in the gaps, a new row will meanwhile be inserted to
the table. If you execute the same `SELECT' within the same
transaction, you will 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 instead
conflict serializability is still guaranteed. Therefore, if this
option is used InnoDB guarantees at most isolation level `READ
COMMITTED'. This option is available as of MySQL 4.1.4.
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 this option still does not allow e.g.
`UPDATE' to overtake other `UPDATE' even the case when both
updates different rows. Consider following example:
CREATE TABLE A(A INT NOT NULL, B INT);
INSERT INTO A VALUES (1,2),(2,3),(3,2),(4,3),(5,2);
COMMIT;
If one connection executes a query:
SET AUTOCOMMIT = 0;
UPDATE A SET B = 5 WHERE B = 3;
and the other connection executes after the first one a query:
SET AUTOCOMMIT = 0;
UPDATE A SET B = 4 WHERE B = 2;
Then query two has to wait for a commit or rollback of query one,
because query one has an exclusive lock to a row (2,3), and query
two while scanning rows also tries to take an exclusive lock to
the row (2,3) which it cannot have. This is because query two first
takes an exclusive lock to a row and then checks does this row
belong to the result set and if not then releases the unnecessary
lock when option `innodb_locks_unsafe_for_binlog' is used.
Therefore, query one is executed 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)
and then query two is executed 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. The value of this parameter should
currently be set the same as `innodb_log_group_home_dir'. Starting
from MySQL 4.0.6, you may omit this option.
`innodb_log_archive'
This value should currently be set to 0. Because recovery from a
backup is done by MySQL using its own log files, there is
currently no need to archive `InnoDB' log files. The default for
this option is 0.
`innodb_log_buffer_size'
The size 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
will save disk I/O.
`innodb_log_file_size'
The size 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, below, 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 will be 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 is 2 (recommended).
`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 parameters, 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 flush pages from the buffer
pool so that at most this many percent of pages may not yet
flushed been flushed at any particular time. Available starting
from 4.0.13 and 4.1.1. If you have the `SUPER' privilege, this
percentage can be changed while the server is running:
SET GLOBAL innodb_max_dirty_pages_pct = VALUE;
`innodb_max_purge_lag'
This option controls how to delay `INSERT', `UPDATE' and `DELETE'
operations when the purge operations are lagging. The default
value of this parameter is zero, meaning that there will not be
any delays. When the value is greater than zero, `InnoDB' may
delay new row operations, as described in InnoDB
Multi-Versioning. This option can be changed at runtime as a
global system variable. `innodb_max_purge_lag' is available as of
MySQL 4.0.22 and 4.1.6.
`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 option 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. This option 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_table_locks'
Starting from MySQL 4.0.20, and 4.1.2, `InnoDB' honors `LOCK
TABLES'; MySQL will not return from `LOCK TABLE .. WRITE' until
all other threads have released all their locks to the table. In
MySQL 4.0.19 and before, InnoDB ignored table locks, which allowed
one to more easily simulate transactions with a combination of
MyISAM and InnoDB tables. The default value is 1, which means that
`LOCK TABLES' causes also InnoDB internally to take a table lock.
In applications using `AUTOCOMMIT=1', InnoDB's internal table
locks can cause deadlocks. You can set `innodb_table_locks=0' in
`my.cnf' 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 parameter. 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 parameter 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 you computer. A
recommended value is the sum of the number of processors and disks
your system has. A value of 500 or greater disables the
concurrency checking. This option is available starting from
MySQL 3.23.44 and 4.0.1.
`innodb_status_file'
This option causes `InnoDB' to create a file
`<DATADIR>/innodb_status.<PID>' for periodical `SHOW INNODB
STATUS' output. This option is available as of MySQL 4.0.21.
Info Catalog
(mysql.info.gz) InnoDB configuration
(mysql.info.gz) InnoDB
(mysql.info.gz) InnoDB init
automatically generated byinfo2html