14.15 InnoDB Startup Options and System Variables

System variables that are true or false can be enabled at server startup by naming them, or disabled by using a --skip- prefix. For example, to enable or disable the InnoDB adaptive hash index, you can use --innodb-adaptive-hash-index or --skip-innodb-adaptive-hash-index on the command line, or innodb_adaptive_hash_index or skip_innodb_adaptive_hash_index in an option file.

System variables that take a numeric value can be specified as --var_name=value on the command line or as var_name=value in option files.

Many system variables can be changed at runtime (see Section 5.1.8.2, “Dynamic System Variables”).

For information about GLOBAL and SESSION variable scope modifiers, refer to the SET statement documentation.

Certain options control the locations and layout of the InnoDB data files. Section 14.8.1, “InnoDB Startup Configuration” explains how to use these options.

Some options, which you might not use initially, help tune InnoDB performance characteristics based on machine capacity and your database workload.

For more information on specifying options and system variables, see Section 4.2.2, “Specifying Program Options”.

--innodb[=value]

Controls loading of the InnoDB storage engine, if the server was compiled with InnoDB support. This option has a tristate format, with possible values of OFF, ON, or FORCE. See Section 5.5.1, “Installing and Uninstalling Plugins”.

To disable InnoDB, use --innodb=OFF or --skip-innodb. In this case, because the default storage engine is InnoDB, the server does not start unless you also use --default-storage-engine and --default-tmp-storage-engine to set the default to some other engine for both permanent and TEMPORARY tables.

The InnoDB storage engine can no longer be disabled, and the --innodb=OFF and --skip-innodb options are deprecated and have no effect. Their use results in a warning. These options will be removed in a future MySQL release.

--innodb-status-file

The --innodb-status-file startup option controls whether InnoDB creates a file named innodb_status.pid in the data directory and writes SHOW ENGINE INNODB STATUS output to it every 15 seconds, approximately.

The innodb_status.pid file is not created by default. To create it, start mysqld with the --innodb-status-file option. InnoDB removes the file when the server is shut down normally. If an abnormal shutdown occurs, the status file may have to be removed manually.

The --innodb-status-file option is intended for temporary use, as SHOW ENGINE INNODB STATUS output generation can affect performance, and the innodb_status.pid file can become quite large over time.

For related information, see Section 14.18.2, “Enabling InnoDB Monitors”.

--skip-innodb

Disable the InnoDB storage engine. See the description of --innodb.

daemon_memcached_enable_binlog

Enable this option on the source server to use the InnoDB memcached plugin (daemon_memcached) with the MySQL binary log. This option can only be set at server startup. You must also enable the MySQL binary log on the source server using the --log-bin option.

For more information, see Section 14.21.6, “The InnoDB memcached Plugin and Replication”.

daemon_memcached_engine_lib_name

Specifies the shared library that implements the InnoDB memcached plugin.

For more information, see Section 14.21.3, “Setting Up the InnoDB memcached Plugin”.

daemon_memcached_engine_lib_path

The path of the directory containing the shared library that implements the InnoDB memcached plugin. The default value is NULL, representing the MySQL plugin directory. You should not need to modify this parameter unless specifying a memcached plugin for a different storage engine that is located outside of the MySQL plugin directory.

For more information, see Section 14.21.3, “Setting Up the InnoDB memcached Plugin”.

daemon_memcached_option

Used to pass space-separated memcached options to the underlying memcached memory object caching daemon on startup. For example, you might change the port that memcached listens on, reduce the maximum number of simultaneous connections, change the maximum memory size for a key-value pair, or enable debugging messages for the error log.

See Section 14.21.3, “Setting Up the InnoDB memcached Plugin” for usage details. For information about memcached options, refer to the memcached man page.

daemon_memcached_r_batch_size

Specifies how many memcached read operations (get operations) to perform before doing a COMMIT to start a new transaction. Counterpart of daemon_memcached_w_batch_size.

This value is set to 1 by default, so that any changes made to the table through SQL statements are immediately visible to memcached operations. You might increase it to reduce the overhead from frequent commits on a system where the underlying table is only being accessed through the memcached interface. If you set the value too large, the amount of undo or redo data could impose some storage overhead, as with any long-running transaction.

For more information, see Section 14.21.3, “Setting Up the InnoDB memcached Plugin”.

daemon_memcached_w_batch_size

Specifies how many memcached write operations, such as add, set, and incr, to perform before doing a COMMIT to start a new transaction. Counterpart of daemon_memcached_r_batch_size.

This value is set to 1 by default, on the assumption that data being stored is important to preserve in case of an outage and should immediately be committed. When storing non-critical data, you might increase this value to reduce the overhead from frequent commits; but then the last N-1 uncommitted write operations could be lost if a crash occurs.

For more information, see Section 14.21.3, “Setting Up the InnoDB memcached Plugin”.

ignore_builtin_innodb

In earlier versions of MySQL, enabling this variable caused the server to behave as if the built-in InnoDB were not present, which enabled the InnoDB Plugin to be used instead. In MySQL 5.7, InnoDB is the default storage engine and InnoDB Plugin is not used, so this variable is ignored.

innodb_adaptive_flushing

Specifies whether to dynamically adjust the rate of flushing dirty pages in the InnoDB buffer pool based on the workload. Adjusting the flush rate dynamically is intended to avoid bursts of I/O activity. This setting is enabled by default. See Section 14.8.3.5, “Configuring Buffer Pool Flushing” for more information. For general I/O tuning advice, see Section 8.5.8, “Optimizing InnoDB Disk I/O”.

innodb_adaptive_flushing_lwm