MySQL 8.0 Reference Manual Including MySQL NDB Cluster 8.0
The first decisions to make about InnoDB
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
the 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
first initialized.
In addition to these topics, this section provides information
about specifying InnoDB
options in a
configuration file, viewing InnoDB
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 InnoDB
for
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.2.2, “Using Option Files”.
To make sure that mysqld reads options only
from a specific file (and mysqld-auto.cnf
),
use the --defaults-file
option
as the first option on the command line when starting the
server:
mysqld --defaults-file=path_to_configuration_file
To view InnoDB
initialization information
during startup, start mysqld from a command
prompt. When mysqld is started from a command
prompt, initialization information is printed to the console.
For example, on Windows, if mysqld is located
in C:\Program Files\MySQL\MySQL Server
8.0\bin
, start the MySQL server like
this:
C:\> "C:\Program Files\MySQL\MySQL Server 8.0\bin\mysqld" --console
On Unix-like systems, mysqld is located in
the bin
directory of your MySQL
installation:
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.10.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
found, 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
application.
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
InnoDB
data files, which may speed up
I/O. See Using Raw Disk Partitions for the System Tablespace.
InnoDB
is 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 macOS,
InnoDB
uses 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/hda
may 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 InnoDB
recovery
capabilities that protect user data,
InnoDB
uses 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 an
unexpected exit or power outage, and improves performance on
most varieties of Unix by reducing the need for
fsync()
operations. It is recommended
that the innodb_doublewrite
option 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.
The innodb_data_file_path
startup option defines the name, size, and attributes of
InnoDB
system tablespace data files. If you
do not configure this option prior to initializing the MySQL
server, the default behavior is to create a single
auto-extending data file, slightly larger than 12MB, named
ibdata1
:
mysql> SHOW VARIABLES LIKE 'innodb_data_file_path';
+-----------------------+------------------------+
| Variable_name | Value |
+-----------------------+------------------------+
| innodb_data_file_path | ibdata1:12M:autoextend |
+-----------------------+------------------------+
The full data file specification syntax includes the file name,
file size, autoextend
attribute, and
max
attribute:
file_name
:file_size
[:autoextend[:max:max_file_size
]]
File sizes are specified in kilobytes, megabytes, or gigabytes
by appending K
, M
or
G
to the size value. If specifying the data
file size in kilobytes, do so in multiples of 1024. Otherwise,
kilobyte values are rounded to nearest megabyte (MB) boundary.
The sum of file sizes must be, at a minimum, slightly larger
than 12MB.
You can specify more than one data file using a semicolon-separated list. For example:
[mysqld] innodb_data_file_path=ibdata1:50M;ibdata2:50M:autoextend
The autoextend
and max
attributes can be used only for the data file that is specified
last.
When the autoextend
attribute is specified,
the data file automatically increases in size by 64MB increments
as space is required. The
innodb_autoextend_increment
variable controls the increment size.
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. The following
configuration permits ibdata1
to grow to a
limit of 500MB:
[mysqld] innodb_data_file_path=ibdata1:12M:autoextend:max:500M
A minimum file size is enforced for the
first system tablespace data file to ensure
that there is enough space for doublewrite buffer pages. The
following table shows minimum file sizes for each
InnoDB
page size. The default
InnoDB
page size is 16384 (16KB).
Page Size (innodb_page_size) | Minimum File Size |
---|---|
16384 (16KB) or less | 3MB |
32768 (32KB) | 6MB |
65536 (64KB) | 12MB |
If your disk becomes full, you can add a data file on another disk. For instructions, see Resizing the System Tablespace.
The size limit for 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. See Using Raw Disk Partitions for the System Tablespace.
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.
System tablespace files are created in the data directory by
default (datadir
). To specify
an alternate location, you can use the
innodb_data_home_dir
option.
For example, to create a system tablespace data file in a
directory named myibdata
, use this
configuration:
[mysqld] innodb_data_home_dir = /myibdata/ innodb_data_file_path=ibdata1:50M:autoextend
A trailing slash is required when specifying a value for
innodb_data_home_dir
.
InnoDB
does not create directories, so ensure
that the specified directory exists before you start the server.
Also, ensure sure that the MySQL server has the proper access
rights to create files in the directory.
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
innodb_data_home_dir
is not
defined, the default value is “./”, which is the
data directory. (The MySQL server changes its current working
directory to the data directory when it begins executing.)
Alternatively, you can specify an absolute path for system tablespace data files. The following configuration is equivalent to the preceding one:
[mysqld] innodb_data_file_path=/myibdata/ibdata1:50M:autoextend
When you specify an absolute path for
innodb_data_file_path
, the
setting is not concatenated with the
innodb_data_home_dir
setting.
System tablespace files are created in the specified absolute
path. The specified directory must exist before you start the
server.
As of MySQL 8.0.20, the doublewrite buffer storage area resides
in doublewrite files, which provides flexibility with respect to
the storage location of doublewrite pages. In previous releases,
the doublwrite buffer storage area resided in the system
tablespace. The
innodb_doublewrite_dir
variable
defines the directory where InnoDB
creates
doublewrite files at startup. If no directory is specified,
doublewrite files are created in the
innodb_data_home_dir
directory,
which defaults to the data directory if unspecified.
To have doublewrite files created in a location other than the
innodb_data_home_dir
directory,
configure
innodb_doublewrite_dir
variable. For example:
innodb_doublewrite_dir=/path/to/doublewrite_directory
Other doublewrite buffer variables permit defining the number of doublewrite files, the number of pages per thread, and the doublewrite batch size. For more information about doublewrite buffer configuration, see Section 15.6.4, “Doublewrite Buffer”.
By default, InnoDB
creates two 5MB redo log
files in the data directory named
ib_logfile0
and
ib_logfile1
.
The following options can be used to modify the default configuration:
innodb_log_group_home_dir
defines directory path to the InnoDB
log
files (the redo logs). If this option is not configured,
InnoDB
log files are created in the MySQL
data directory (datadir
).
You might use this option to place InnoDB
log files in a different physical storage location than
InnoDB
data files to avoid potential I/O
resource conflicts. For example:
[mysqld] innodb_log_group_home_dir = /dr3/iblogs
InnoDB
does not create directories, so
make sure that the log directory exists before you start
the server. Use the Unix or DOS mkdir
command 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_group
defines the number of log files in the log group. The
default and recommended value is 2.
innodb_log_file_size
defines the size in bytes of each log file in the 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
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 that are created when the MySQL instance is initialized. The I/O patterns for undo logs make undo tablespaces good candidates for SSD storage.
The innodb_undo_directory
variable defines the path where InnoDB
creates default undo tablespaces. If that variable is undefined,
default undo tablespaces are created in the data directory. The
innodb_undo_directory
variable
is not dynamic. Configuring it requires restarting the server.
For information about configuring additional undo tablespaces, see Section 15.6.3.4, “Undo Tablespaces”.
The global temporary tablespace stores rollback segments for changes made to user-created temporary tables.
By default, InnoDB
creates a single
auto-extending global temporary tablespace data file named
ibtmp1
in the
innodb_data_home_dir
directory.
The initial file size is slightly larger than 12MB.
The innodb_temp_data_file_path
variable specifies the path, file name, and file size for global
temporary tablespace data files. File size is specified in KB,
MB, or GB by appending K, M, or G to the size value. The sum of
the sizes of the files must be slightly larger than 12MB.
To specify an alternate location for global temporary tablespace
data files, configure the
innodb_temp_data_file_path
variable at startup.
In MySQL 8.0.15 and earlier, session temporary tablespaces store
user-created temporary tables and internal temporary tables
created by the optimizer when InnoDB
is
configured as the on-disk storage engine for internal temporary
tables
(internal_tmp_disk_storage_engine=InnoDB
).
In MySQL 8.0.16 and later, the InnoDB
storage
engine is always used for internal temporary tables on disk.
The innodb_temp_tablespaces_dir
variable defines the location where InnoDB
creates session temporary tablespaces. The default location is
the #innodb_temp
directory in the data
directory.
To specify an alternate location for session temporary
tablespaces, configure the
innodb_temp_tablespaces_dir
variable at startup. A fully qualified path or path relative to
the data directory is permitted.
The innodb_page_size
option
specifies the page size for all InnoDB
tablespaces in a MySQL instance. This value is set when the
instance is created and remains constant afterward. Valid values
are 64KB, 32KB, 16KB (the default), 8KB, and 4KB. Alternatively,
you can specify page size in bytes (65536, 32768, 16384, 8192,
4096).
The default page size of 16KB 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.
Keeping the 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 8.12.3.1, “How MySQL Uses Memory”.
Buffers specific to InnoDB
are configured
using the following parameters:
innodb_buffer_pool_size
defines size of the buffer pool, which is the memory area
that holds cached data for InnoDB
tables,
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_size
is
configured to 50 to 75 percent of system memory. The default
buffer pool size is 128MB. For additional guidance, see
Section 8.12.3.1, “How MySQL Uses Memory”. For information about how to
configure InnoDB
buffer pool size, see
Section 15.8.3.1, “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_instances
option. By default, InnoDB
creates one
buffer pool instance. The number of buffer pool instances
can be configured at startup. For more information, see
Section 15.8.3.2, “Configuring Multiple Buffer Pool Instances”.
innodb_log_buffer_size
defines the size in bytes of the buffer that
InnoDB
uses 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_size
can
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
too high. 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 8.12.3.1, “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 sort_buffer_size +
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 8.12.3.2, “Enabling Large Page Support”.