InnoDB Storage Engine¶
The innodb plugin provides the InnoDB storage engine, a fully transactional MVCC storage engine developed by Innobase Oy. InnoDB is the default storage engine for Drizzle.
Compatibility with MySQL¶
The innodb plugin is maintained in Drizzle as a downstream project of the innodb_plugin for MySQL. The two are nearly identical, and the Drizzle plugin is usually kept up-to-date with the MySQL plugin, but there are some notable differences:
The on disk formats are slightly incompatible (to allow for the same index length for the four byte UTF-8 that Drizzle supports)
The table definitions (.frm for MySQL, .dfe for Drizzle) are completely different. This means that you cannot directly share InnoDB tablespaces between MySQL and Drizzle. Use the drizzledump tool to migrate data from MySQL to Drizzle.
There are also some notable compatibilies:
AUTO_INCREMENT behaves the standard way (as in MyISAM)
Supports four byte UTF-8 with the same index size
Native AIO Support¶
InnoDB supports Linux native AIO when compiled on platforms that have the libaio development files installed (typically a package called libaio-dev or libaio-devel). For more information on the advantages of this please see http://blogs.innodb.com/wp/2010/04/innodb-performance-aio-linux/
To confirm that Linux native AIO is enabled, execute:
SHOW GLOBAL VARIABLES LIKE 'innodb_use_native_aio';
InnoDB Replicaiton Log¶
The innodb plugin provides a mechanism to store replication
events in an InnoDB table. When enabled, this transaction log can be accessed
through the SYS_REPLICATION_LOG and INNODB_REPLICATION_LOG tables in the
DATA_DICTIONARY schema.
To enable this transaction log, start the server with the
drizzled --innodb.replication-log.
Loading¶
This plugin is loaded by default, but it may need to be configured. See the plugin’s Configuration and Variables.
To stop the plugin from loading by default, start drizzled with:
--plugin-remove=innodb
See also
Plugin Options for more information about adding and removing plugins.
Configuration¶
These command line options configure the plugin when drizzled is started. See Command Line Options for more information about specifying command line options.
- --innodb.adaptive-flushing-method ARG¶
- Default:
estimate
- Variable:
Adaptive flushing method. Possible values are:
native
estimate
keep_average
- --innodb.additional-mem-pool-size ARG¶
- Default:
8388608 (8M)
- Variable:
Size of a memory pool InnoDB uses to store data dictionary information and other internal data structures.
- --innodb.auto-lru-dump¶
- Default:
0
- Variable:
Time in seconds between automatic buffer pool dumps.
- --innodb.autoextend-increment ARG¶
- Default:
64
- Variable:
Data file autoextend increment in megabytes.
- --innodb.buffer-pool-instances ARG¶
- Default:
1
- Variable:
Number of buffer pool instances.
- --innodb.buffer-pool-size ARG¶
- Default:
134217728 (128M)
- Variable:
The size of the memory buffer InnoDB uses to cache data and indexes of its tables.
- --innodb.change-buffering¶
- Default:
all
- Variable:
Buffer changes to reduce random access. Possible values:
none
inserts
deletes
changes
purges
all
- --innodb.checkpoint-age-target¶
- Default:
0
- Variable:
Control soft limit of checkpoint age. (0 = no control)
- --innodb.commit-concurrency¶
- Default:
0
- Variable:
Helps in performance tuning in heavily concurrent environments.
- --innodb.concurrency-tickets ARG¶
- Default:
500
- Variable:
Number of times a thread is allowed to enter InnoDB within the same SQL query after it has once got the ticket.
- --innodb.data-file-path¶
- Default:
ibdata1:10M:autoextend
- Variable:
Path to individual files and their sizes.
- --innodb.data-home-dir¶
- Default:
- Variable:
Directory for InnoDB data.
- --innodb.disable-adaptive-flushing¶
- Default:
- Variable:
Do not attempt flushing dirty pages to avoid IO bursts at checkpoints.
- --innodb.disable-adaptive-hash-index¶
- Default:
- Variable:
Enable InnoDB adaptive hash index (enabled by default)
- --innodb.disable-checksums¶
- Default:
false
- Variable:
Disable InnoDB checksums validation.
- --innodb.disable-doublewrite¶
- Default:
- Variable:
Disable InnoDB doublewrite buffer.
- --innodb.disable-native-aio¶
- Default:
- Variable:
Do not use Native AIO library for IO, even if available. See Native AIO Support.
- --innodb.disable-stats-on-metadata¶
- Default:
- Variable:
Disable statistics gathering for metadata commands such as SHOW TABLE STATUS (on by default).
- --innodb.disable-table-locks¶
- Default:
- Variable:
Disable InnoDB locking in LOCK TABLES.
- --innodb.disable-xa¶
- Default:
- Variable:
Disable InnoDB support for the XA two-phase commit.
- --innodb.fast-shutdown ARG¶
- Default:
1
- Variable:
Speeds up the shutdown process of the InnoDB storage engine. Possible values are:
0 (off)
1 (faster)
2 (fastest, crash-like)
- --innodb.file-format ARG¶
- Default:
Antelope
- Variable:
File format to use for new tables in .ibd files.
- --innodb.file-format-check¶
- Default:
true
- Variable:
Whether to perform system file format check.
- --innodb.file-format-max ARG¶
- Default:
Antelope
- Variable:
The highest file format in the tablespace.
- --innodb.file-per-table¶
- Default:
false
- Variable:
Stores each InnoDB table to an .ibd file in the database dir.
- --innodb.flush-log-at-trx-commit ARG¶
- Default:
1
- Variable:
Flush lot at transaction commit. Possible values are:
0 (write and flush once per second)
1 (write and flush at each commit)
2 (write at commit, flush once per second)
- --innodb.flush-method¶
- Default:
- Variable:
Data flush method.
- --innodb.flush-neighbor-pages ARG¶
- Default:
1
- Variable:
Enable/Disable flushing also neighbor pages. 0:disable 1:enable
- --innodb.force-recovery¶
- Default:
0
- Variable:
Helps to save your data in case the disk image of the database becomes corrupt.
- --innodb.ibuf-accel-rate ARG¶
- Default:
100
- Variable:
Tunes amount of insert buffer processing of background, in addition to innodb_io_capacity. (in percentage)
- --innodb.ibuf-active-contract ARG¶
- Default:
1
- Variable:
Enable/Disable active_contract of insert buffer. 0:disable 1:enable
- --innodb.ibuf-max-size ARG¶
- Default:
UINT64_MAX
- Variable:
The maximum size of the insert buffer (in bytes).
- --innodb.io-capacity ARG¶
- Default:
200
- Variable:
Number of IOPs the server can do. Tunes the background IO rate.
- --innodb.lock-wait-timeout ARG¶
- Default:
50
- Variable:
Timeout in seconds an InnoDB transaction may wait for a lock before being rolled back. Values above 100000000 disable the timeout.
- --innodb.log-buffer-size ARG¶
- Default:
8,388,608 (8M)
- Variable:
The size of the buffer which InnoDB uses to write log to the log files on disk.
- --innodb.log-file-size ARG¶
- Default:
20971520 (20M)
- Variable:
The size of the buffer which InnoDB uses to write log to the log files on disk.
- --innodb.log-files-in-group ARG¶
- Default:
2
- Variable:
Number of log files in the log group. InnoDB writes to the files in a circular fashion.
- --innodb.log-group-home-dir¶
- Default:
- Variable:
Path to InnoDB log files.
- --innodb.max-dirty-pages-pct ARG¶
- Default:
75
- Variable:
Percentage of dirty pages allowed in bufferpool.
- --innodb.max-purge-lag¶
- Default:
0
- Variable:
Desired maximum length of the purge queue (0 = no limit)
- --innodb.mirrored-log-groups ARG¶
- Default:
1
- Variable:
Number of identical copies of log groups we keep for the database. Currently this should be set to 1.
- --innodb.old-blocks-pct ARG¶
- Default:
37
- Variable:
Percentage of the buffer pool to reserve for ‘old’ blocks.
- --innodb.old-blocks-time¶
- Default:
0
- Variable:
Move blocks to the ‘new’ end of the buffer pool if the first access was at least this many milliseconds ago.
- --innodb.open-files ARG¶
- Default:
300
- Variable:
How many files at the maximum InnoDB keeps open at the same time.
- --innodb.purge-batch-size ARG¶
- Default:
20
- Variable:
Number of UNDO logs to purge in one batch from the history list.
- --innodb.purge-threads ARG¶
- Default:
1
- Variable:
Purge threads can be either 0 or 1.
- --innodb.read-ahead ARG¶
- Default:
linear
- Variable:
Control read ahead activity. Possible values are:
none
random
linear
both
- --innodb.read-ahead-threshold ARG¶
- Default:
56
- Variable:
Number of pages that must be accessed sequentially for InnoDB to trigger a readahead.
- --innodb.read-io-threads ARG¶
- Default:
4
- Variable:
Number of background read I/O threads in InnoDB.
- --innodb.replication-delay¶
- Default:
0
- Variable:
Replication thread delay (ms) on the slave server if innodb_thread_concurrency is reached (0 by default)
- --innodb.replication-log¶
- Default:
false
- Variable:
Enable InnoDB Replicaiton Log.
- --innodb.spin-wait-delay ARG¶
- Default:
6
- Variable:
Maximum delay between polling for a spin lock.
- --innodb.stats-sample-pages ARG¶
- Default:
8
- Variable:
The number of index pages to sample when calculating statistics.
- --innodb.status-file¶
- Default:
false
- Variable:
Enable SHOW INNODB STATUS output in the innodb_status.<pid> file.
- --innodb.strict-mode¶
- Default:
false
- Variable:
Use strict mode when evaluating create options.
- --innodb.sync-spin-loops ARG¶
- Default:
30
- Variable:
Count of spin-loop rounds in InnoDB mutexes.
- --innodb.thread-concurrency¶
- Default:
0
- Variable:
Helps in performance tuning in heavily concurrent environments. Sets the maximum number of threads allowed inside InnoDB. Value 0 will disable the thread throttling.
- --innodb.thread-sleep-delay ARG¶
- Default:
10000
- Variable:
Time of innodb thread sleeping before joining InnoDB queue (usec). Value 0 disable a sleep.
- --innodb.timed-mutexes¶
- Default:
false
- Variable:
innodb_timed_mutexes
Specify whether to time mutexes (only InnoDB mutexes are currently supported). This variable is only present if InnoDB is compiled with UNIV_DEBUG.
- --innodb.use-internal-malloc¶
- Default:
false
- Variable:
innodb_use_sys_malloc <innodb_use_sys_malloc>
Use InnoDB’s internal memory allocator instead of the system’s malloc.
- --innodb.use-replicator¶
- Default:
default
- Variable:
innodb_use_replicator <innodb_use_sys_malloc>
Use this replicator for the InnoDB Replicaiton Log.
- --innodb.version ARG¶
- Default:
- Variable:
InnoDB version.
- --innodb.write-io-threads ARG¶
- Default:
4
- Variable:
Number of background write I/O threads in InnoDB.
Variables¶
These variables show the running configuration of the plugin. See variables for more information about querying and setting variables.
innodb_adaptive_flushing- Scope:
Global
- Dynamic:
No
- Option:
If adaptive flushing is enabled or not.
innodb_adaptive_flushing_method- Scope:
Global
- Dynamic:
No
- Option:
Adaptive flushing method. Possible values are:
native
estimate
keep_average
innodb_adaptive_hash_index- Scope:
Global
- Dynamic:
No
- Option:
If the adapative hash index is enabled or not.
innodb_additional_mem_pool_size- Scope:
Global
- Dynamic:
No
- Option:
Size of a memory pool InnoDB uses to store data dictionary information and other internal data structures.
innodb_auto_lru_dump- Scope:
Global
- Dynamic:
No
- Option:
Time in seconds between automatic buffer pool dumps.
innodb_autoextend_increment- Scope:
Global
- Dynamic:
No
- Option:
Data file autoextend increment in megabytes
innodb_buffer_pool_size- Scope:
Global
- Dynamic:
No
- Option:
The size of the memory buffer InnoDB uses to cache data and indexes of its tables.
innodb_change_buffering- Scope:
Global
- Dynamic:
No
- Option:
Buffer changes to reduce random access: OFF
innodb_checkpoint_age_target- Scope:
Global
- Dynamic:
No
- Option:
Control soft limit of checkpoint age. (0 : not control)
innodb_checksums- Scope:
Global
- Dynamic:
No
- Option:
If checksums are enabled or not.
innodb_commit_concurrency- Scope:
Global
- Dynamic:
No
- Option:
Helps in performance tuning in heavily concurrent environments.
innodb_concurrency_tickets- Scope:
Global
- Dynamic:
No
- Option:
Number of times a thread is allowed to enter InnoDB within the same SQL query after it has once got the ticket
innodb_data_file_path- Scope:
Global
- Dynamic:
No
- Option:
Path to individual files and their sizes.
innodb_data_home_dir- Scope:
Global
- Dynamic:
No
- Option:
Directory for InnoDB data.
innodb_doublewrite- Scope:
Global
- Dynamic:
No
- Option:
If doublewrite buffer is enabled or not.
innodb_fast_shutdown- Scope:
Global
- Dynamic:
No
- Option:
Fast shutdown method.
innodb_file_format_check- Scope:
Global
- Dynamic:
No
- Option:
Whether to perform system file format check.
innodb_file_per_table- Scope:
Global
- Dynamic:
No
- Option:
Stores each InnoDB table to an .ibd file in the database dir.
innodb_file_format- Scope:
Global
- Dynamic:
No
- Option:
File format to use for new tables in .ibd files.
innodb_file_format_max- Scope:
Global
- Dynamic:
No
- Option:
The highest file format in the tablespace.
innodb_flush_method- Scope:
Global
- Dynamic:
No
- Option:
Data flush method.
innodb_flush_log_at_trx_commit- Scope:
Global
- Dynamic:
No
- Option:
Set to 0 (write and flush once per second)
innodb_flush_neighbor_pages- Scope:
Global
- Dynamic:
No
- Option:
Enable/Disable flushing also neighbor pages. 0:disable 1:enable
innodb_force_recovery- Scope:
Global
- Dynamic:
No
- Option:
Helps to save your data in case the disk image of the database becomes corrupt.
innodb_ibuf_accel_rate- Scope:
Global
- Dynamic:
No
- Option:
Tunes amount of insert buffer processing of background
innodb_ibuf_active_contract- Scope:
Global
- Dynamic:
No
- Option:
Enable/Disable active_contract of insert buffer. 0:disable 1:enable
innodb_ibuf_max_size- Scope:
Global
- Dynamic:
No
- Option:
The maximum size of the insert buffer (in bytes).
innodb_io_capacity- Scope:
Global
- Dynamic:
No
- Option:
Number of IOPs the server can do. Tunes the background IO rate
innodb_lock_wait_timeout- Scope:
Global
- Dynamic:
No
- Option:
Timeout in seconds an InnoDB transaction may wait for a lock before being rolled back. Values above 100000000 disable the timeout.
innodb_log_group_home_dir- Scope:
Global
- Dynamic:
No
- Option:
Path to InnoDB log files.
innodb_log_buffer_size- Scope:
Global
- Dynamic:
No
- Option:
The size of the buffer which InnoDB uses to write log to the log files on disk.
innodb_log_file_size- Scope:
Global
- Dynamic:
No
- Option:
The size of the buffer which InnoDB uses to write log to the log files on disk.
innodb_log_files_in_group- Scope:
Global
- Dynamic:
No
- Option:
Number of log files in the log group. InnoDB writes to the files in a circular fashion.
innodb_max_dirty_pages_pct- Scope:
Global
- Dynamic:
No
- Option:
Percentage of dirty pages allowed in bufferpool.
innodb_max_purge_lag- Scope:
Global
- Dynamic:
No
- Option:
Desired maximum length of the purge queue (0 = no limit)
innodb_mirrored_log_groups- Scope:
Global
- Dynamic:
No
- Option:
Number of identical copies of log groups we keep for the database. Currently this should be set to 1.
innodb_old_blocks_pct- Scope:
Global
- Dynamic:
No
- Option:
Percentage of the buffer pool to reserve for ‘old’ blocks.
innodb_old_blocks_time- Scope:
Global
- Dynamic:
No
- Option:
ove blocks to the ‘new’ end of the buffer pool if the first access
innodb_open_files- Scope:
Global
- Dynamic:
No
- Option:
How many files at the maximum InnoDB keeps open at the same time.
innodb_purge_batch_size- Scope:
Global
- Dynamic:
No
- Option:
Number of UNDO logs to purge in one batch from the history list.
innodb_purge_threads- Scope:
Global
- Dynamic:
No
- Option:
Purge threads can be either 0 or 1. Default is 1.
innodb_read_ahead- Scope:
Global
- Dynamic:
No
- Option:
Readahead method.
innodb_read_ahead_threshold- Scope:
Global
- Dynamic:
No
- Option:
Number of pages that must be accessed sequentially for InnoDB to trigger a readahead.
innodb_read_io_threads- Scope:
Global
- Dynamic:
No
- Option:
Number of background read I/O threads in InnoDB.
innodb_replication_delay- Scope:
Global
- Dynamic:
No
- Option:
Replication thread delay (ms) on the slave server if innodb_thread_concurrency is reached (0 by default)
innodb_replication_log- Scope:
Global
- Dynamic:
No
- Option:
If the InnoDB Replicaiton Log is enabled or not.
innodb_spin_wait_delay- Scope:
Global
- Dynamic:
No
- Option:
Maximum delay between polling for a spin lock (6 by default)
innodb_stats_sample_pages- Scope:
Global
- Dynamic:
No
- Option:
The number of index pages to sample when calculating statistics (default 8)
innodb_status_file- Scope:
Global
- Dynamic:
No
- Option:
Enable SHOW INNODB STATUS output in the innodb_status.<pid> file
innodb_strict_mode- Scope:
Global
- Dynamic:
No
- Option:
Use strict mode when evaluating create options.
innodb_support_xa- Scope:
Global
- Dynamic:
No
- Option:
If two-phase XA commit it enabled or not.
innodb_sync_spin_loops- Scope:
Global
- Dynamic:
No
- Option:
Count of spin-loop rounds in InnoDB mutexes (30 by default)
innodb_thread_concurrency- Scope:
Global
- Dynamic:
No
- Option:
Helps in performance tuning in heavily concurrent environments. Sets the maximum number of threads allowed inside InnoDB. Value 0 will disable the thread throttling.
innodb_thread_sleep_delay- Scope:
Global
- Dynamic:
No
- Option:
Time of innodb thread sleeping before joining InnoDB queue (usec). Value 0 disable a sleep
innodb_use_native_aio- Scope:
Global
- Dynamic:
No
- Option:
If Native AIO Support enabled or not.
innodb_use_sys_malloc- Scope:
Global
- Dynamic:
No
- Option:
If system or internal malloc() is being used.
innodb_use_replicator- Scope:
Global
- Dynamic:
No
- Option:
Replicator to which the InnoDB Replicaiton Log is paired.
innodb_version- Scope:
Global
- Dynamic:
No
- Option:
InnoDB version
innodb_write_io_threads- Scope:
Global
- Dynamic:
No
- Option:
Number of background write I/O threads in InnoDB.
Version¶
This documentation applies to innodb 1.1.4.
To see which version of the plugin a Drizzle server is running, execute:
SELECT MODULE_VERSION FROM DATA_DICTIONARY.MODULES WHERE MODULE_NAME='innodb'
Changelog¶
v1.1.4¶
First Drizzle version.
