Pre-General Availability Draft: 2018-02-22
The first decisions to make about
configuration involve the configuration of data files, log files,
page size, and memory buffers. It is recommended that you define
data file, log file, and page size configuration before creating
InnoDB instance. Modifying data file or log
file configuration after the
InnoDB instance is
created may involve a non-trivial procedure, and page size can
only be defined when the
InnoDB instance is
In addition to these topics, this section provides information
InnoDB options in a
configuration file, viewing
initialization information, and important storage considerations.
Because MySQL uses data file, log file, and page size
configuration settings to initialize the
InnoDB instance, it is recommended that you
define these settings in a configuration file that MySQL reads
at startup, prior to initializing
the first time.
InnoDB is initialized when
the MySQL server is started, and the first initialization of
InnoDB normally occurs the first time you
start the MySQL server.
You can place
InnoDB options in the
[mysqld] group of any option file that your
server reads when it starts. The locations of MySQL option files
are described in Section 4.2.6, “Using Option Files”.
For example, on Windows, if mysqld is located
C:\Program Files\MySQL\MySQL Server
8.0\bin, start the MySQL server like
C:\> "C:\Program Files\MySQL\MySQL Server 8.0\bin\mysqld" --console
On Unix-like systems, mysqld is located in
bin directory of your MySQL
shell> bin/mysqld --user=mysql &
If you do not send server output to the console, check the error
log after startup to see the initialization information
InnoDB printed during the startup process.
For information about starting MySQL using other methods, see Section 2.9.5, “Starting and Stopping MySQL Automatically”.
InnoDB does not open all user tables and
associated data files at startup. However,
InnoDB does check for the existence of
tablespace files (
*.ibd files) that are
referenced in the data dictionary. If a tablespace file is not
InnoDB logs an error and continues
the startup sequence. Tablespace files that are referenced in
the redo log may be opened during crash recovery for redo
Review the following storage-related considerations before proceeding with your startup configuration.
In some cases, database performance improves if the data is not all placed on the same physical disk. Putting log files on a different disk from data is very often beneficial for performance. For example, you can place system tablespace data files and log files on different disks. You can also use raw disk partitions (raw devices) for
InnoDBdata files, which may speed up I/O. See Section 15.7.3, “Using Raw Disk Partitions for the System Tablespace”.
InnoDBis a transaction-safe (ACID compliant) storage engine for MySQL that has commit, rollback, and crash-recovery capabilities to protect user data. However, it cannot do so if the underlying operating system or hardware does not work as advertised. Many operating systems or disk subsystems may delay or reorder write operations to improve performance. On some operating systems, the very
fsync()system call that should wait until all unwritten data for a file has been flushed might actually return before the data has been flushed to stable storage. Because of this, an operating system crash or a power outage may destroy recently committed data, or in the worst case, even corrupt the database because of write operations having been reordered. If data integrity is important to you, perform some “pull-the-plug” tests before using anything in production. On OS X 10.3 and higher,
InnoDBuses a special
fcntl()file flush method. Under Linux, it is advisable to disable the write-back cache.
On ATA/SATA disk drives, a command such
hdparm -W0 /dev/hdamay work to disable the write-back cache. Beware that some drives or disk controllers may be unable to disable the write-back cache.
With regard to
InnoDBrecovery capabilities that protect user data,
InnoDBuses a file flush technique involving a structure called the doublewrite buffer, which is enabled by default (
innodb_doublewrite=ON). The doublewrite buffer adds safety to recovery following a crash or power outage, and improves performance on most varieties of Unix by reducing the need for
fsync()operations. It is recommended that the
innodb_doublewriteoption remains enabled if you are concerned with data integrity or possible failures. For additional information about the doublewrite buffer, see Section 15.11.1, “InnoDB Disk I/O”.
Before using NFS with
InnoDB, review potential issues outlined in Using NFS with MySQL.
configuration option defines the name, size, and attributes of
InnoDB system tablespace data files. If you
do not specify a value for
innodb_data_file_path, the default behavior
is to create a single auto-extending data file, slightly larger
than 12MB, named
To specify more than one data file, separate them by semicolon
The following setting configures a single 12MB data file named
ibdata1 that is auto-extending. No location
for the file is given, so by default,
creates it in the MySQL data directory:
File size is specified using
G suffix letters to
indicate units of KB, MB, or GB. 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
A minimum file size is enforced for the first system tablespace data file to ensure that there is enough space for doublewrite buffer pages:
A system tablespace with a fixed-size 50MB data file named
ibdata1 and a 50MB auto-extending file
ibdata2 can be configured like this:
The full syntax for a data file specification includes the file
name, file size, and optional
attributes can be used only for the data file that is specified
last in the
If you specify the
autoextend option for the
last data file,
InnoDB extends the data file
if it runs out of free space in the tablespace. The
autoextend increment is 64MB at a time by
default. To modify the increment, change the
If the disk becomes full, you might want to add another data file on another disk. For instructions, see Section 15.7.1, “Resizing the InnoDB System Tablespace”.
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.
InnoDB is not aware of the file system
maximum file size, so be cautious on file systems where the
maximum file size is a small value such as 2GB. To specify a
maximum size for an auto-extending data file, use the
max attribute following the
autoextend attribute. Use the
max attribute only in cases where
constraining disk usage is of critical importance, because
exceeding the maximum size causes a fatal error, possibly
causing the server to exit. The following configuration permits
ibdata1 to grow to a limit of 500MB:
InnoDB creates system tablespace files in the
MySQL data directory by default
datadir). To specify a
location explicitly, use the
For example, to create two files named
a directory named
InnoDB like this:
[mysqld] innodb_data_home_dir = /path/to/myibdata/ innodb_data_file_path=ibdata1:50M;ibdata2:50M:autoextend
A trailing slash is required when specifying a value for
InnoDB does not create directories, so make
sure that the
myibdata directory exists
before you start the server. Use the Unix or DOS
mkdir command to create directories.
Make sure that the MySQL server has the proper access rights to create files in the data directory. More generally, the server must have access rights in any directory where it needs to create data files.
InnoDB forms the directory path for each data
file by textually concatenating the value of
innodb_data_home_dir to the
data file name. If the
innodb_data_home_dir option is
not specified, the default value is the “dot”
./, which means the MySQL data
directory. (The MySQL server changes its current working
directory to its data directory when it begins executing.)
If you specify
innodb_data_home_dir as an
empty string, you can specify absolute paths for data files
listed in the
The following example is equivalent to the preceding one:
[mysqld] innodb_data_home_dir = innodb_data_file_path=/path/to/myibdata/ibdata1:50M;/path/to/myibdata/ibdata2:50M:autoextend
InnoDB creates two 48MB log files
in the MySQL data directory
The following options can be used to modify the default configuration:
You might use this option to place
InnoDBlog files in a different physical storage location than
InnoDBdata files to avoid potential I/O resource conflicts. For example:
[mysqld] innodb_log_group_home_dir = /dr3/iblogsNote
InnoDBdoes not create directories, so make sure that the log directory exists before you start the server. Use the Unix or DOS
mkdircommand to create any necessary directories.
Make sure that the MySQL server has the proper access rights to create files in the log directory. More generally, the server must have access rights in any directory where it needs to create log files.
innodb_log_files_in_groupdefines the number of log files in the log group. The default and recommended value is 2.
innodb_log_file_sizedefines the size in bytes of each log file in the log group. The combined size of log files (
innodb_log_files_in_group) cannot exceed a maximum value that is slightly less than 512GB. A pair of 255 GB log files, for example, approaches the limit but does not exceed it. The default log file size is 48MB. 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 needed in the buffer pool, saving disk I/O. For additional information, see Section 8.5.4, “Optimizing InnoDB Redo Logging”.
By default, undo logs reside in two undo tablespaces. The I/O patterns for undo logs make undo tablespaces good candidates for SSD storage. Because undo logs can become large during long-running transactions, having undo logs in multiple undo tablespaces reduces the maximum size of any one undo tablespace.
configuration option defines the path where
InnoDB creates tablespaces for the undo logs.
If a path is not specified for
tablespaces are created in the MySQL data directory, as defined
innodb_undo_directory option is
non-dynamic. Configuring it requires restarting the server.
deprecated and will be removed in a future release.
For more information, see Section 15.7.8, “Configuring Undo Tablespaces”.
InnoDB creates a single
auto-extending temporary tablespace data file named
ibtmp1 that is slightly larger than 12MB in
directory. The default temporary tablespace data file
configuration can be modified at startup using the
option specifies the path, file name, and file size for
InnoDB temporary tablespace data files. The
full directory path for a file is formed by concatenating
innodb_data_home_dir to the
path specified by
File size is 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 slightly larger than 12MB.
specifies the page size for all
tablespaces in a MySQL instance. This value is set when the
instance is created and remains constant afterward. Valid values
are 64k, 32k, 16k (the default), 8k, and 4k. Alternatively, you
can specify page size in bytes (65536, 32768, 16384, 8192,
The default page size of 16k is appropriate for a wide range of
workloads, particularly for queries involving table scans and
DML operations involving bulk updates. Smaller page sizes might
be more efficient for OLTP workloads involving many small
writes, where contention can be an issue when a single page
contains many rows. Smaller pages might also be efficient with
SSD storage devices, which typically use small block sizes.
InnoDB page size close to the
storage device block size minimizes the amount of unchanged data
that is rewritten to disk.
MySQL allocates memory to various caches and buffers to improve
performance of database operations. When allocating memory for
InnoDB, always consider memory required by
the operating system, memory allocated to other applications,
and memory allocated for other MySQL buffers and caches. For
example, if you use
MyISAM tables, consider
the amount of memory allocated for the key buffer
key_buffer_size). For an
overview of MySQL buffers and caches, see
Section 126.96.36.199, “How MySQL Uses Memory”.
Buffers specific to
InnoDB are configured
using the following parameters:
innodb_buffer_pool_sizedefines size of the buffer pool, which is the memory area that holds cached data for
InnoDBtables, indexes, and other auxiliary buffers. The size of the buffer pool is important for system performance, and it is typically recommended that
innodb_buffer_pool_sizeis configured to 50 to 75 percent of system memory. The default buffer pool size is 128MB. For additional guidance, see Section 188.8.131.52, “How MySQL Uses Memory”. For information about how to configure
InnoDBbuffer pool size, see Section 184.108.40.206, “Configuring InnoDB Buffer Pool Size”. Buffer pool size can be configured at startup or dynamically.
On systems with a large amount of memory, you can improve concurrency by dividing the buffer pool into multiple buffer pool instances. The number of buffer pool instances is controlled by the by
innodb_buffer_pool_instancesoption. By default,
InnoDBcreates one buffer pool instance. The number of buffer pool instances can be configured at startup. For more information, see Section 220.127.116.11, “Configuring Multiple Buffer Pool Instances”.
innodb_log_buffer_sizedefines the size in bytes of the buffer that
InnoDBuses to write to the log files on disk. The default size is 16MB. A large log buffer enables large transactions to run without a need to write the log to disk before the transactions commit. If you have transactions that update, insert, or delete many rows, you might consider increasing the size of the log buffer to save disk I/O.
innodb_log_buffer_sizecan be configured at startup. For related information, see Section 8.5.4, “Optimizing InnoDB Redo Logging”.
On 32-bit GNU/Linux x86, be careful not to set memory usage
glibc may permit the process heap
to grow over thread stacks, which crashes your server. It is a
risk if the memory allocated to the mysqld
process for global and per-thread buffers and caches is close
to or exceeds 2GB.
A formula similar to the following that calculates global and per-thread memory allocation for MySQL can be used to estimate MySQL memory usage. You may need to modify the formula to account for buffers and caches in your MySQL version and configuration. For an overview of MySQL buffers and caches, see Section 18.104.22.168, “How MySQL Uses Memory”.
innodb_buffer_pool_size + key_buffer_size + max_connections*(sort_buffer_size+read_buffer_size+binlog_cache_size) + max_connections*2MB
Each thread uses a stack (often 2MB, but only 256KB in MySQL
binaries provided by Oracle Corporation.) and in the worst
case also uses
read_buffer_size additional memory.
On Linux, if the kernel is enabled for large page support,
InnoDB can use large pages to allocate memory
for its buffer pool. See Section 22.214.171.124, “Enabling Large Page Support”.