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.
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 (.from 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
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';
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.
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.
These command line options configure the plugin when drizzled is started. See Command Line Options for more information about specifying command line options.
Default: | estimate |
---|---|
Variable: | innodb_adaptive_flushing_method |
Adaptive flushing method. Possible values are:
Default: | 8388608 (8M) |
---|---|
Variable: | innodb_additional_mem_pool_size |
Size of a memory pool InnoDB uses to store data dictionary information and other internal data structures.
Default: | 0 |
---|---|
Variable: | innodb_auto_lru_dump |
Time in seconds between automatic buffer pool dumps.
Default: | 64 |
---|---|
Variable: | innodb_autoextend_increment |
Data file autoextend increment in megabytes.
Default: | 1 |
---|---|
Variable: |
Number of buffer pool instances.
Default: | 134217728 (128M) |
---|---|
Variable: | innodb_buffer_pool_size |
The size of the memory buffer InnoDB uses to cache data and indexes of its tables.
Default: | all |
---|---|
Variable: | innodb_change_buffering |
Buffer changes to reduce random access. Possible values:
Default: | 0 |
---|---|
Variable: | innodb_checkpoint_age_target |
Control soft limit of checkpoint age. (0 = no control)
Default: | 0 |
---|---|
Variable: | innodb_commit_concurrency |
Helps in performance tuning in heavily concurrent environments.
Default: | 500 |
---|---|
Variable: | innodb_concurrency_tickets |
Number of times a thread is allowed to enter InnoDB within the same SQL query after it has once got the ticket.
Default: | ibdata1:10M:autoextend |
---|---|
Variable: | innodb_data_file_path |
Path to individual files and their sizes.
Default: | |
---|---|
Variable: | innodb_data_home_dir |
Directory for InnoDB data.
Default: | |
---|---|
Variable: | innodb_adaptive_flushing |
Do not attempt flushing dirty pages to avoid IO bursts at checkpoints.
Default: | |
---|---|
Variable: | innodb_adaptive_hash_index |
Enable InnoDB adaptive hash index (enabled by default)
Default: | false |
---|---|
Variable: | innodb_checksums |
Disable InnoDB checksums validation.
Default: | |
---|---|
Variable: | innodb_doublewrite |
Disable InnoDB doublewrite buffer.
Default: | |
---|---|
Variable: |
Do not use Native AIO library for IO, even if available. See Native AIO Support.
Default: | |
---|---|
Variable: |
Disable statistics gathering for metadata commands such as SHOW TABLE STATUS (on by default).
Default: | |
---|---|
Variable: |
Disable InnoDB locking in LOCK TABLES.
Default: | |
---|---|
Variable: | innodb_support_xa |
Disable InnoDB support for the XA two-phase commit.
Default: | 1 |
---|---|
Variable: | innodb_fast_shutdown |
Speeds up the shutdown process of the InnoDB storage engine. Possible values are:
Default: | Antelope |
---|---|
Variable: | innodb_file_format |
File format to use for new tables in .ibd files.
Default: | true |
---|---|
Variable: | innodb_file_format_check |
Whether to perform system file format check.
Default: | Antelope |
---|---|
Variable: | innodb_file_format_max |
The highest file format in the tablespace.
Default: | false |
---|---|
Variable: | innodb_file_per_table |
Stores each InnoDB table to an .ibd file in the database dir.
Default: | 1 |
---|---|
Variable: | innodb_flush_log_at_trx_commit |
Flush lot at transaction commit. Possible values are:
Default: | |
---|---|
Variable: | innodb_flush_method |
Data flush method.
Default: | 1 |
---|---|
Variable: | innodb_flush_neighbor_pages |
Enable/Disable flushing also neighbor pages. 0:disable 1:enable
Default: | 0 |
---|---|
Variable: | innodb_force_recovery |
Helps to save your data in case the disk image of the database becomes corrupt.
Default: | 100 |
---|---|
Variable: | innodb_ibuf_accel_rate |
Tunes amount of insert buffer processing of background, in addition to innodb_io_capacity. (in percentage)
Default: | 1 |
---|---|
Variable: | innodb_ibuf_active_contract |
Enable/Disable active_contract of insert buffer. 0:disable 1:enable
Default: | UINT64_MAX |
---|---|
Variable: | innodb_ibuf_max_size |
The maximum size of the insert buffer (in bytes).
Default: | 200 |
---|---|
Variable: | innodb_io_capacity |
Number of IOPs the server can do. Tunes the background IO rate.
Default: | 50 |
---|---|
Variable: | innodb_lock_wait_timeout |
Timeout in seconds an InnoDB transaction may wait for a lock before being rolled back. Values above 100000000 disable the timeout.
Default: | 8,388,608 (8M) |
---|---|
Variable: | innodb_log_buffer_size |
The size of the buffer which InnoDB uses to write log to the log files on disk.
Default: | 20971520 (20M) |
---|---|
Variable: | innodb_log_file_size |
The size of the buffer which InnoDB uses to write log to the log files on disk.
Default: | 2 |
---|---|
Variable: | innodb_log_files_in_group |
Number of log files in the log group. InnoDB writes to the files in a circular fashion.
Default: | |
---|---|
Variable: | innodb_log_group_home_dir |
Path to InnoDB log files.
Default: | 75 |
---|---|
Variable: | innodb_max_dirty_pages_pct |
Percentage of dirty pages allowed in bufferpool.
Default: | 0 |
---|---|
Variable: | innodb_max_purge_lag |
Desired maximum length of the purge queue (0 = no limit)
Default: | 1 |
---|---|
Variable: | innodb_mirrored_log_groups |
Number of identical copies of log groups we keep for the database. Currently this should be set to 1.
Default: | 37 |
---|---|
Variable: | innodb_old_blocks_pct |
Percentage of the buffer pool to reserve for ‘old’ blocks.
Default: | 0 |
---|---|
Variable: | innodb_old_blocks_time |
Move blocks to the ‘new’ end of the buffer pool if the first access was at least this many milliseconds ago.
Default: | 300 |
---|---|
Variable: | innodb_open_files |
How many files at the maximum InnoDB keeps open at the same time.
Default: | 20 |
---|---|
Variable: | innodb_purge_batch_size |
Number of UNDO logs to purge in one batch from the history list.
Default: | 1 |
---|---|
Variable: | innodb_purge_threads |
Purge threads can be either 0 or 1.
Default: | linear |
---|---|
Variable: | innodb_read_ahead |
Control read ahead activity. Possible values are:
Default: | 56 |
---|---|
Variable: | innodb_read_ahead_threshold |
Number of pages that must be accessed sequentially for InnoDB to trigger a readahead.
Default: | 4 |
---|---|
Variable: | innodb_read_io_threads |
Number of background read I/O threads in InnoDB.
Default: | 0 |
---|---|
Variable: | innodb_replication_delay |
Replication thread delay (ms) on the slave server if innodb_thread_concurrency is reached (0 by default)
Default: | false |
---|---|
Variable: | innodb_replication_log |
Enable InnoDB Replicaiton Log.
Default: | 6 |
---|---|
Variable: | innodb_spin_wait_delay |
Maximum delay between polling for a spin lock.
Default: | 8 |
---|---|
Variable: | innodb_stats_sample_pages |
The number of index pages to sample when calculating statistics.
Default: | false |
---|---|
Variable: | innodb_status_file |
Enable SHOW INNODB STATUS output in the innodb_status.<pid> file.
Default: | false |
---|---|
Variable: | innodb_strict_mode |
Use strict mode when evaluating create options.
Default: | 30 |
---|---|
Variable: | innodb_sync_spin_loops |
Count of spin-loop rounds in InnoDB mutexes.
Default: | 0 |
---|---|
Variable: | innodb_thread_concurrency |
Helps in performance tuning in heavily concurrent environments. Sets the maximum number of threads allowed inside InnoDB. Value 0 will disable the thread throttling.
Default: | 10000 |
---|---|
Variable: | innodb_thread_sleep_delay |
Time of innodb thread sleeping before joining InnoDB queue (usec). Value 0 disable a sleep.
Default: | false |
---|---|
Variable: | innodb_use_sys_malloc <innodb_use_sys_malloc> |
Use InnoDB’s internal memory allocator instead of the system’s malloc.
Default: | default |
---|---|
Variable: | innodb_use_replicator <innodb_use_sys_malloc> |
Use this replicator for the InnoDB Replicaiton Log.
Default: | |
---|---|
Variable: | innodb_version_var |
InnoDB version.
Default: | 4 |
---|---|
Variable: | innodb_write_io_threads |
Number of background write I/O threads in InnoDB.
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: --innodb.disable-adaptive-flushing If adaptive flushing is enabled or not.
innodb_adaptive_flushing_method
Scope: Global Dynamic: No Option: --innodb.adaptive-flushing-method Adaptive flushing method. Possible values are:
- native
- estimate
- keep_average
innodb_adaptive_hash_index
Scope: Global Dynamic: No Option: --innodb.disable-adaptive-hash-index If the adapative hash index is enabled or not.
innodb_additional_mem_pool_size
Scope: Global Dynamic: No Option: --innodb.additional-mem-pool-size 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: --innodb.auto-lru-dump Time in seconds between automatic buffer pool dumps.
innodb_autoextend_increment
Scope: Global Dynamic: No Option: --innodb.autoextend-increment Data file autoextend increment in megabytes
innodb_buffer_pool_size
Scope: Global Dynamic: No Option: --innodb.buffer-pool-size The size of the memory buffer InnoDB uses to cache data and indexes of its tables.
innodb_change_buffering
Scope: Global Dynamic: No Option: --innodb.change-buffering Buffer changes to reduce random access: OFF
innodb_checkpoint_age_target
Scope: Global Dynamic: No Option: --innodb.checkpoint-age-target Control soft limit of checkpoint age. (0 : not control)
innodb_checksums
Scope: Global Dynamic: No Option: --innodb.disable-checksums If checksums are enabled or not.
innodb_commit_concurrency
Scope: Global Dynamic: No Option: --innodb.commit-concurrency Helps in performance tuning in heavily concurrent environments.
innodb_concurrency_tickets
Scope: Global Dynamic: No Option: --innodb.concurrency-tickets 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: --innodb.data-file-path Path to individual files and their sizes.
innodb_data_home_dir
Scope: Global Dynamic: No Option: --innodb.data-home-dir Directory for InnoDB data.
innodb_doublewrite
Scope: Global Dynamic: No Option: --innodb.disable-doublewrite If doublewrite buffer is enabled or not.
innodb_fast_shutdown
Scope: Global Dynamic: No Option: --innodb.fast-shutdown Fast shutdown method.
innodb_file_format_check
Scope: Global Dynamic: No Option: --innodb.file-format-check Whether to perform system file format check.
innodb_file_per_table
Scope: Global Dynamic: No Option: --innodb.file-per-table Stores each InnoDB table to an .ibd file in the database dir.
innodb_file_format
Scope: Global Dynamic: No Option: --innodb.file-format File format to use for new tables in .ibd files.
innodb_file_format_max
Scope: Global Dynamic: No Option: --innodb.file-format-max The highest file format in the tablespace.
innodb_flush_method
Scope: Global Dynamic: No Option: --innodb.flush-method Data flush method.
innodb_flush_log_at_trx_commit
Scope: Global Dynamic: No Option: --innodb.flush-log-at-trx-commit Set to 0 (write and flush once per second)
innodb_flush_neighbor_pages
Scope: Global Dynamic: No Option: --innodb.flush-neighbor-pages Enable/Disable flushing also neighbor pages. 0:disable 1:enable
innodb_force_recovery
Scope: Global Dynamic: No Option: --innodb.force-recovery Helps to save your data in case the disk image of the database becomes corrupt.
innodb_ibuf_accel_rate
Scope: Global Dynamic: No Option: --innodb.ibuf-accel-rate Tunes amount of insert buffer processing of background
innodb_ibuf_active_contract
Scope: Global Dynamic: No Option: --innodb.ibuf-active-contract Enable/Disable active_contract of insert buffer. 0:disable 1:enable
innodb_ibuf_max_size
Scope: Global Dynamic: No Option: --innodb.ibuf-max-size The maximum size of the insert buffer (in bytes).
innodb_io_capacity
Scope: Global Dynamic: No Option: --innodb.io-capacity Number of IOPs the server can do. Tunes the background IO rate
innodb_lock_wait_timeout
Scope: Global Dynamic: No Option: --innodb.lock-wait-timeout 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: --innodb.log-group-home-dir Path to InnoDB log files.
innodb_log_buffer_size
Scope: Global Dynamic: No Option: --innodb.log-buffer-size 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: --innodb.log-file-size 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: --innodb.log-files-in-group 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: --innodb.max-dirty-pages-pct Percentage of dirty pages allowed in bufferpool.
innodb_max_purge_lag
Scope: Global Dynamic: No Option: --innodb.max-purge-lag Desired maximum length of the purge queue (0 = no limit)
innodb_mirrored_log_groups
Scope: Global Dynamic: No Option: --innodb.mirrored-log-groups 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: --innodb.old-blocks-pct Percentage of the buffer pool to reserve for ‘old’ blocks.
innodb_old_blocks_time
Scope: Global Dynamic: No Option: --innodb.old-blocks-time ove blocks to the ‘new’ end of the buffer pool if the first access
innodb_open_files
Scope: Global Dynamic: No Option: --innodb.open-files How many files at the maximum InnoDB keeps open at the same time.
innodb_purge_batch_size
Scope: Global Dynamic: No Option: --innodb.purge-batch-size Number of UNDO logs to purge in one batch from the history list.
innodb_purge_threads
Scope: Global Dynamic: No Option: --innodb.purge-threads Purge threads can be either 0 or 1. Default is 1.
innodb_read_ahead
Scope: Global Dynamic: No Option: --innodb.read-ahead Readahead method.
innodb_read_ahead_threshold
Scope: Global Dynamic: No Option: --innodb.read-ahead-threshold Number of pages that must be accessed sequentially for InnoDB to trigger a readahead.
innodb_read_io_threads
Scope: Global Dynamic: No Option: --innodb.read-io-threads Number of background read I/O threads in InnoDB.
innodb_replication_delay
Scope: Global Dynamic: No Option: --innodb.replication-delay 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: --innodb.replication-log If the InnoDB Replicaiton Log is enabled or not.
innodb_spin_wait_delay
Scope: Global Dynamic: No Option: --innodb.spin-wait-delay Maximum delay between polling for a spin lock (6 by default)
innodb_stats_sample_pages
Scope: Global Dynamic: No Option: --innodb.stats-sample-pages The number of index pages to sample when calculating statistics (default 8)
innodb_status_file
Scope: Global Dynamic: No Option: --innodb.status-file Enable SHOW INNODB STATUS output in the innodb_status.<pid> file
innodb_strict_mode
Scope: Global Dynamic: No Option: --innodb.strict-mode Use strict mode when evaluating create options.
innodb_support_xa
Scope: Global Dynamic: No Option: --innodb.disable-xa If two-phase XA commit it enabled or not.
innodb_sync_spin_loops
Scope: Global Dynamic: No Option: --innodb.sync-spin-loops Count of spin-loop rounds in InnoDB mutexes (30 by default)
innodb_thread_concurrency
Scope: Global Dynamic: No Option: --innodb.thread-concurrency 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: --innodb.thread-sleep-delay Time of innodb thread sleeping before joining InnoDB queue (usec). Value 0 disable a sleep
innodb_use_native_aio
Scope: Global Dynamic: No Option: --innodb.disable-native-aio If Native AIO Support enabled or not.
innodb_use_sys_malloc
Scope: Global Dynamic: No Option: --innodb.use-internal-malloc If system or internal malloc() is being used.
innodb_use_replicator
Scope: Global Dynamic: No Option: --innodb.use-replicator Replicator to which the InnoDB Replicaiton Log is paired.
innodb_version
Scope: Global Dynamic: No Option: --innodb.version InnoDB version
innodb_write_io_threads
Scope: Global Dynamic: No Option: --innodb.write-io-threads Number of background write I/O threads in InnoDB.
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'