17.14 InnoDB Startup Options and System Variables (original) (raw)

SET GLOBAL innodb_buffer_pool_filename='file_name';

You can also specify a file name at startup, in a startup string or MySQL configuration file. When specifying a file name at startup, the file must exist orInnoDB returns a startup error indicating that there is no such file or directory.
For more information, seeSection 17.8.3.6, “Saving and Restoring the Buffer Pool State”.

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

File sizes are specified in kilobytes, megabytes, or gigabytes by appending K, M orG to the size value. If specifying the data file size in kilobytes, do so in multiples of 1024. Otherwise, KB values are rounded to nearest megabyte (MB) boundary. The sum of file sizes must be, at a minimum, slightly larger than 12MB.
For additional configuration information, seeSystem Tablespace Data File Configuration. For resizing instructions, seeResizing the System Tablespace.

[mysqld]  
innodb_data_home_dir = /path/to/myibdata/

This setting does not affect the location offile-per-table tablespaces.
For related information, seeSection 17.8.1, “InnoDB Startup Configuration”.

mysqld --innodb-directories="directory_path_1;directory_path_2"

MySQL option file: 

[mysqld]  
innodb_directories="directory_path_1;directory_path_2"

Wildcard expressions cannot be used to specify directories.
The innodb_directories scan also traverses the subdirectories of specified directories. Duplicate directories and subdirectories are discarded from the list of directories to be scanned.
For more information, seeSection 17.6.3.6, “Moving Tablespace Files While the Server is Offline”.

SET GLOBAL innodb_ft_aux_table = 'test/t1';

After you set this variable to a name in the format_`dbname`_/_`tablename`_, the INFORMATION_SCHEMA tablesINNODB_FT_INDEX_TABLE,INNODB_FT_INDEX_CACHE,INNODB_FT_CONFIG,INNODB_FT_DELETED, andINNODB_FT_BEING_DELETED show information about the search index for the specified table.
For more information, seeSection 17.15.4, “InnoDB INFORMATION_SCHEMA FULLTEXT Index Tables”.

ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

When a lock wait timeout occurs, the current statement isrolled back (not the entire transaction). To have the entire transaction roll back, start the server with the--innodb-rollback-on-timeout option. See also Section 17.20.5, “InnoDB Error Handling”.
You might decrease this value for highly interactive applications or OLTP systems, to display user feedback quickly or put the update into a queue for processing later. You might increase this value for long-running back-end operations, such as a transform step in a data warehouse that waits for other large insert or update operations to finish.
innodb_lock_wait_timeout applies to InnoDB row locks. A MySQLtable lock does not happen inside InnoDB and this timeout does not apply to waits for table locks.
The lock wait timeout value does not apply todeadlocks wheninnodb_deadlock_detect is enabled (the default) because InnoDB detects deadlocks immediately and rolls back one of the deadlocked transactions. Wheninnodb_deadlock_detect is disabled, InnoDB relies oninnodb_lock_wait_timeout for transaction rollback when a deadlock occurs. SeeSection 17.7.5.2, “Deadlock Detection”.
innodb_lock_wait_timeout can be set at runtime with the SET GLOBAL orSET SESSION statement. Changing theGLOBAL setting requires privileges sufficient to set global system variables (seeSection 7.1.9.1, “System Variable Privileges”) and affects the operation of all clients that subsequently connect. Any client can change the SESSION setting forinnodb_lock_wait_timeout, which affects only that client.

mysql> SELECT innodb_set_open_files_limit(1000);

The statement executes a stored procedure that sets the new limit. If the procedure is successful, it returns the value of the newly set limit; otherwise, a failure message is returned.
It is not permitted to setinnodb_open_files using aSET statement. To set innodb_open_files at runtime, use the SELECT innodb_set_open_files_limit(_`N`_) statement described above.
Settinginnodb_open_files=default is not supported. Only integer values are permitted.
To prevent non-LRU managed files from consuming the entireinnodb_open_files limit, non-LRU managed files are limited to 90 percent of this limit, which reserves 10 percent of it for LRU managed files.

[Note] InnoDB: If the mysqld execution user is authorized, page cleaner  
thread priority can be changed. See the man page of setpriority().

For systems where server startup and shutdown is not managed by systemd, mysqld execution user authorization can be configured in/etc/security/limits.conf. For example, if mysqld is run under themysql user, you can authorize themysql user by adding these lines to/etc/security/limits.conf: 

mysql              hard    nice       -20  
mysql              soft    nice       -20

For systemd managed systems, the same can be achieved by specifying LimitNICE=-20 in a localized systemd configuration file. For example, create a file namedoverride.conf in/etc/systemd/system/mysqld.service.d/override.conf and add this entry: 

[Service]  
LimitNICE=-20

After creating or changing override.conf, reload the systemd configuration, then tell systemd to restart the MySQL service: 

systemctl daemon-reload  
systemctl restart mysqld  # RPM platforms  
systemctl restart mysql   # Debian platforms

For more information about using a localized systemd configuration file, seeConfiguring systemd for MySQL.
After authorizing the mysqld execution user, use the cat command to verify the configured Nice limits for themysqld process: 

$> cat /proc/mysqld_pid/limits | grep nice  
Max nice priority         18446744073709551596 18446744073709551596  
innodb_redo_log_archive_dirs='label1:/backups1;label2:/backups2'

A label can be any string of characters, with the exception of colons (:), which are not permitted. An empty label is also permitted, but the colon (:) is still required in this case.
A path must be specified, and the directory must exist. The path can contain colons (':'), but semicolons (;) are not permitted.

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

The global temporary tablespace data file cannot have the same name as another InnoDB data file. Any inability or error creating the global temporary tablespace data file is treated as fatal and server startup is refused.
File sizes are specified in KB, MB, or GB by appendingK, M orG to the size value. The sum of file sizes must be slightly larger than 12MB.
The size limit of individual files is determined by the operating system. File size can be more than 4GB on operating systems that support large files. Use of raw disk partitions for global temporary tablespace data files is not supported.
The autoextend and max attributes can be used only for the data file specified last in theinnodb_temp_data_file_path setting. For example: 

[mysqld]  
innodb_temp_data_file_path=ibtmp1:50M;ibtmp2:12M:autoextend:max:500M

The autoextend option causes the data file to automatically increase in size when it runs out of free space. The autoextend increment is 64MB by default. To modify the increment, change theinnodb_autoextend_increment variable setting.
The directory path for global temporary tablespace data files is formed by concatenating the paths defined byinnodb_data_home_dir andinnodb_temp_data_file_path.
Before running InnoDB in read-only mode, setinnodb_temp_data_file_path to a location outside of the data directory. The path must be relative to the data directory. For example: 

--innodb-temp-data-file-path=../../../tmp/ibtmp1:12M:autoextend

For more information, seeGlobal Temporary Tablespace.

EAGAIN: The specified maxevents exceeds the user's limit of available events.

You can typically address this error by writing a higher limit to /proc/sys/fs/aio-max-nr.
However, if a problem with the asynchronous I/O subsystem in the OS prevents InnoDB from starting, you can start the server withinnodb_use_native_aio=0. This option may also be disabled automatically during startup ifInnoDB detects a potential problem such as a combination of tmpdir location,tmpfs file system, and Linux kernel that does not support AIO on tmpfs.
For more information, seeSection 17.8.6, “Using Asynchronous I/O on Linux”.