14.15 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 14.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 14.8.1, “InnoDB Startup Configuration”.

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 14.16.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 14.22.4, “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 only. 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 14.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 5.1.8.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.

SELECT * FROM child WHERE id > 100 FOR UPDATE;

The query scans the index starting from the first record where the id is greater than 100. If the locks set on the index records in that range do not lock out inserts made in the gaps, another session can insert a new row into the table. Consequently, if you were to execute the sameSELECT again within the same transaction, you would see a new row in the result set returned by the query. This also means that if new items are added to the database, InnoDB does not guarantee serializability. Therefore, ifinnodb_locks_unsafe_for_binlog is enabled, InnoDB guarantees at most an isolation level of READ COMMITTED. (Conflict serializability is still guaranteed.) For more information about phantoms, seeSection 14.7.4, “Phantom Rows”.
Enablinginnodb_locks_unsafe_for_binlog has additional effects: 

CREATE TABLE t (a INT NOT NULL, b INT) ENGINE = InnoDB;  
INSERT INTO t VALUES (1,2),(2,3),(3,2),(4,3),(5,2);  
COMMIT;

In this case, table has no indexes, so searches and index scans use the hidden clustered index for record locking (seeSection 14.6.2.1, “Clustered and Secondary Indexes”).
Suppose that one client performs anUPDATE using these statements: 

SET autocommit = 0;  
UPDATE t SET b = 5 WHERE b = 3;

Suppose also that a second client performs anUPDATE by executing these statements following those of the first client: 

SET autocommit = 0;  
UPDATE t SET b = 4 WHERE b = 2;

As InnoDB executes eachUPDATE, it first acquires an exclusive lock for each row, and then determines whether to modify it. If InnoDB does not modify the row andinnodb_locks_unsafe_for_binlog is enabled, it releases the lock. Otherwise,InnoDB retains the lock until the end of the transaction. This affects transaction processing as follows.
Ifinnodb_locks_unsafe_for_binlog is disabled, the first UPDATE acquires x-locks and does not release any of them: 

x-lock(1,2); retain x-lock  
x-lock(2,3); update(2,3) to (2,5); retain x-lock  
x-lock(3,2); retain x-lock  
x-lock(4,3); update(4,3) to (4,5); retain x-lock  
x-lock(5,2); retain x-lock

The second UPDATE blocks as soon as it tries to acquire any locks (because the first update has retained locks on all rows), and does not proceed until the first UPDATE commits or rolls back: 

x-lock(1,2); block and wait for first UPDATE to commit or roll back

Ifinnodb_locks_unsafe_for_binlog is enabled, the first UPDATE acquires x-locks and releases those for rows that it does not modify: 

x-lock(1,2); unlock(1,2)  
x-lock(2,3); update(2,3) to (2,5); retain x-lock  
x-lock(3,2); unlock(3,2)  
x-lock(4,3); update(4,3) to (4,5); retain x-lock  
x-lock(5,2); unlock(5,2)

For the second UPDATE,InnoDB does a“semi-consistent” read, returning the latest committed version of each row to MySQL so that MySQL can determine whether the row matches the WHERE condition of the UPDATE: 

x-lock(1,2); update(1,2) to (1,4); retain x-lock  
x-lock(2,3); unlock(2,3)  
x-lock(3,2); update(3,2) to (3,4); retain x-lock  
x-lock(4,3); unlock(4,3)  
x-lock(5,2); update(5,2) to (5,4); retain x-lock  
[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  
file_name:file_size[:autoextend[:max:max_file_size]]

The temporary tablespace data file cannot have the same name as another InnoDB data file. Any inability or error creating a temporary tablespace data file is treated as fatal and server startup is refused. The temporary tablespace has a dynamically generated space ID, which can change on each server restart.
File sizes are specified KB, MB or GB (1024MB) by appendingK, M orG to the size value. The sum of the sizes of the files must be slightly larger than 12MB.
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. Use of raw disk partitions for temporary tablespace data files is not supported.
The autoextend and max attributes can be used only for the data file that is specified last in theinnodb_temp_data_file_path setting. For example: 

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

If you specify the autoextend option,InnoDB extends the data file if it runs out of free space. The autoextend increment is 64MB by default. To modify the increment, change theinnodb_autoextend_increment system variable.
The full directory path for temporary tablespace data files is formed by concatenating the paths defined byinnodb_data_home_dir andinnodb_temp_data_file_path.
The temporary tablespace is shared by all non-compressedInnoDB temporary tables. Compressed temporary tables reside in file-per-table tablespace files created in the temporary file directory, which is defined by the tmpdir configuration option.
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

Metadata about active InnoDB temporary tables is located in the Information SchemaINNODB_TEMP_TABLE_INFO table.
For related information, seeSection 14.6.3.5, “The 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 14.8.7, “Using Asynchronous I/O on Linux”.