Because MySQL uses data file, log file, and page size settings to initialize InnoDB, it is recommended that you define these settings in an option file that MySQL reads at startup, prior to initializing InnoDB. Normally, InnoDB is initialized when the MySQL server is started for the first time.

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 6.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_option_file

To view InnoDB initialization information during startup, start mysqld from a command prompt, which prints initialization information to the console.

For example, on Windows, if mysqld is located in C:\Program Files\MySQL\MySQL Server 8.4\bin, start the MySQL server like this:

C:\> "C:\Program Files\MySQL\MySQL Server 8.4\bin\mysqld" --console

On Unix-like systems, mysqld is located in the bin directory of your MySQL installation:

$> 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”.

Review the following storage-related considerations before proceeding with your startup configuration.

The innodb_data_file_path 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).

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, 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.

The InnoDB doublewrite buffer storage area resides in doublewrite files, which provides flexibility with respect to the storage location of doublewrite pages. In previous releases, the doublewrite 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 17.6.4, “Doublewrite Buffer”.

The amount of disk space occupied by redo log files is controlled by the innodb_redo_log_capacity variable, which can be set at startup or runtime; for example, to set the variable to 8GiB in an option file, add the following entry:

[mysqld]
innodb_redo_log_capacity = 8589934592

For information about configuring redo log capacity at runtime, see Configuring Redo Log Capacity.

The innodb_redo_log_capacity variable supersedes the innodb_log_file_size and innodb_log_files_in_group variables, which are deprecated. When the innodb_redo_log_capacity setting is defined, the innodb_log_file_size and innodb_log_files_in_group settings are ignored; otherwise, if one or both of these deprecated settings are defined then they are used to compute Innodb_redo_log_capacity_resized as (innodb_log_files_in_group * innodb_log_file_size). If none of those variables are set, then the default innodb_redo_log_capacity value is used.

InnoDB attempts to maintain 32 redo log files, with each file equal to 1/32 * innodb_redo_log_capacity. The redo log files reside in the #innodb_redo directory in the data directory unless a different directory was specified by the innodb_log_group_home_dir variable. If innodb_log_group_home_dir was defined, the redo log files reside in the #innodb_redo directory in that directory. For more information, see Section 17.6.5, “Redo Log”.

You can define a different number of redo log files and different redo log file size when initializing the MySQL Server instance by configuring the innodb_log_files_in_group and innodb_log_file_size variables.

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 log file size (innodb_log_file_size * innodb_log_files_in_group) cannot exceed the maximum value, which 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. A larger log file size means less checkpoint flush activity in the buffer pool, which reduces disk I/O. For additional information, see Section 10.5.4, “Optimizing InnoDB Redo Logging”.

The innodb_log_group_home_dir defines the directory path to the InnoDB log files. You might use this option to place InnoDB redo 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

Undo logs, by default, reside in two undo tablespaces created when the MySQL instance is initialized.

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.

The I/O patterns for undo logs make undo tablespaces good candidates for SSD storage.

For information about configuring additional undo tablespaces, see Section 17.6.3.4, “Undo Tablespaces”.

The global temporary tablespace stores rollback segments for changes made to user-created temporary tables.

A single auto-extending global temporary tablespace data file named ibtmp1 in the innodb_data_home_dir directory by default. The initial file size is slightly larger than 12MB.

The innodb_temp_data_file_path option 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 file size or combined file size must be slightly larger than 12MB.

To specify an alternate location for global temporary tablespace data files, configure the innodb_temp_data_file_path option at startup.

In MySQL 8.4, InnoDB is always used as the on-disk storage engine for internal temporary tables.

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 16KB page size 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 can also be more efficient for 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 10.12.3.1, “How MySQL Uses Memory”.

Buffers specific to InnoDB are configured using the following parameters:

innodb_buffer_pool_size
+ key_buffer_size
+ max_connections*(sort_buffer_size+read_buffer_size+binlog_cache_size)
+ max_connections*2MB

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 10.12.3.3, “Enabling Large Page Support”.