15.8.1 InnoDB Startup Configuration

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

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

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

To specify more than one data file, separate them by semicolon (;) characters:

innodb_data_file_path=datafile_spec1[;datafile_spec2]...

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, InnoDB creates it in the MySQL data directory:

[mysqld]
innodb_data_file_path=ibdata1:12M:autoextend

File size is specified using K, M, or 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 12MB.

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 named ibdata2 can be configured like this:

[mysqld]
innodb_data_file_path=ibdata1:50M;ibdata2:50M:autoextend

The full syntax for a data file specification includes the file name, file size, and optional autoextend and max attributes:

file_name:file_size[:autoextend[:max:max_file_size]]

The autoextend and max attributes can be used only for the data file that is specified last in the innodb_data_file_path setting.

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 innodb_autoextend_increment system variable.

If the disk becomes full, you might want to add another data file on another disk. For instructions, see Resizing the 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:

[mysqld]
innodb_data_file_path=ibdata1:12M:autoextend:max:500M

InnoDB creates system tablespace files in the MySQL data directory by default (datadir). To specify a location explicitly, use the innodb_data_home_dir option. For example, to create two files named ibdata1 and ibdata2 in a directory named myibdata, configure InnoDB like this:

[mysqld]
innodb_data_home_dir = /path/to/myibdata/
innodb_data_file_path=ibdata1:50M;ibdata2:50M:autoextend

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” directory ./, 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 innodb_data_file_path value. 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

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:

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.

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

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