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 (.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

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:innodb_adaptive_flushing_method

Adaptive flushing method. Possible values are:

  • native
  • estimate
  • keep_average
--innodb.additional-mem-pool-size ARG
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.

--innodb.auto-lru-dump
Default:0
Variable:innodb_auto_lru_dump

Time in seconds between automatic buffer pool dumps.

--innodb.autoextend-increment ARG
Default:64
Variable:innodb_autoextend_increment

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:innodb_buffer_pool_size

The size of the memory buffer InnoDB uses to cache data and indexes of its tables.

--innodb.change-buffering
Default:all
Variable:innodb_change_buffering

Buffer changes to reduce random access. Possible values:

  • none
  • inserts
  • deletes
  • changes
  • purges
  • all
--innodb.checkpoint-age-target
Default:0
Variable:innodb_checkpoint_age_target

Control soft limit of checkpoint age. (0 = no control)

--innodb.commit-concurrency
Default:0
Variable:innodb_commit_concurrency

Helps in performance tuning in heavily concurrent environments.

--innodb.concurrency-tickets ARG
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.

--innodb.data-file-path
Default:ibdata1:10M:autoextend
Variable:innodb_data_file_path

Path to individual files and their sizes.

--innodb.data-home-dir
Default:
Variable:innodb_data_home_dir

Directory for InnoDB data.

--innodb.disable-adaptive-flushing
Default:
Variable:innodb_adaptive_flushing

Do not attempt flushing dirty pages to avoid IO bursts at checkpoints.

--innodb.disable-adaptive-hash-index
Default:
Variable:innodb_adaptive_hash_index

Enable InnoDB adaptive hash index (enabled by default)

--innodb.disable-checksums
Default:false
Variable:innodb_checksums

Disable InnoDB checksums validation.

--innodb.disable-doublewrite
Default:
Variable:innodb_doublewrite

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:innodb_support_xa

Disable InnoDB support for the XA two-phase commit.

--innodb.fast-shutdown ARG
Default:1
Variable:innodb_fast_shutdown

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:innodb_file_format

File format to use for new tables in .ibd files.

--innodb.file-format-check
Default:true
Variable:innodb_file_format_check

Whether to perform system file format check.

--innodb.file-format-max ARG
Default:Antelope
Variable:innodb_file_format_max

The highest file format in the tablespace.

--innodb.file-per-table
Default:false
Variable:innodb_file_per_table

Stores each InnoDB table to an .ibd file in the database dir.

--innodb.flush-log-at-trx-commit ARG
Default:1
Variable:innodb_flush_log_at_trx_commit

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:innodb_flush_method

Data flush method.

--innodb.flush-neighbor-pages ARG
Default:1
Variable:innodb_flush_neighbor_pages

Enable/Disable flushing also neighbor pages. 0:disable 1:enable

--innodb.force-recovery
Default:0
Variable:innodb_force_recovery

Helps to save your data in case the disk image of the database becomes corrupt.

--innodb.ibuf-accel-rate ARG
Default:100
Variable:innodb_ibuf_accel_rate

Tunes amount of insert buffer processing of background, in addition to innodb_io_capacity. (in percentage)

--innodb.ibuf-active-contract ARG
Default:1
Variable:innodb_ibuf_active_contract

Enable/Disable active_contract of insert buffer. 0:disable 1:enable

--innodb.ibuf-max-size ARG
Default:UINT64_MAX
Variable:innodb_ibuf_max_size

The maximum size of the insert buffer (in bytes).

--innodb.io-capacity ARG
Default:200
Variable:innodb_io_capacity

Number of IOPs the server can do. Tunes the background IO rate.

--innodb.lock-wait-timeout ARG
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.

--innodb.log-buffer-size ARG
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.

--innodb.log-file-size ARG
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.

--innodb.log-files-in-group ARG
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.

--innodb.log-group-home-dir
Default:
Variable:innodb_log_group_home_dir

Path to InnoDB log files.

--innodb.max-dirty-pages-pct ARG
Default:75
Variable:innodb_max_dirty_pages_pct

Percentage of dirty pages allowed in bufferpool.

--innodb.max-purge-lag
Default:0
Variable:innodb_max_purge_lag

Desired maximum length of the purge queue (0 = no limit)

--innodb.mirrored-log-groups ARG
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.

--innodb.old-blocks-pct ARG
Default:37
Variable:innodb_old_blocks_pct

Percentage of the buffer pool to reserve for ‘old’ blocks.

--innodb.old-blocks-time
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.

--innodb.open-files ARG
Default:300
Variable:innodb_open_files

How many files at the maximum InnoDB keeps open at the same time.

--innodb.purge-batch-size ARG
Default:20
Variable:innodb_purge_batch_size

Number of UNDO logs to purge in one batch from the history list.

--innodb.purge-threads ARG
Default:1
Variable:innodb_purge_threads

Purge threads can be either 0 or 1.

--innodb.read-ahead ARG
Default:linear
Variable:innodb_read_ahead

Control read ahead activity. Possible values are:

  • none
  • random
  • linear
  • both
--innodb.read-ahead-threshold ARG
Default:56
Variable:innodb_read_ahead_threshold

Number of pages that must be accessed sequentially for InnoDB to trigger a readahead.

--innodb.read-io-threads ARG
Default:4
Variable:innodb_read_io_threads

Number of background read I/O threads in InnoDB.

--innodb.replication-delay
Default:0
Variable:innodb_replication_delay

Replication thread delay (ms) on the slave server if innodb_thread_concurrency is reached (0 by default)

--innodb.replication-log
Default:false
Variable:innodb_replication_log

Enable InnoDB Replicaiton Log.

--innodb.spin-wait-delay ARG
Default:6
Variable:innodb_spin_wait_delay

Maximum delay between polling for a spin lock.

--innodb.stats-sample-pages ARG
Default:8
Variable:innodb_stats_sample_pages

The number of index pages to sample when calculating statistics.

--innodb.status-file
Default:false
Variable:innodb_status_file

Enable SHOW INNODB STATUS output in the innodb_status.<pid> file.

--innodb.strict-mode
Default:false
Variable:innodb_strict_mode

Use strict mode when evaluating create options.

--innodb.sync-spin-loops ARG
Default:30
Variable:innodb_sync_spin_loops

Count of spin-loop rounds in InnoDB mutexes.

--innodb.thread-concurrency
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.

--innodb.thread-sleep-delay ARG
Default:10000
Variable:innodb_thread_sleep_delay

Time of innodb thread sleeping before joining InnoDB queue (usec). Value 0 disable a sleep.

--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_var

InnoDB version.

--innodb.write-io-threads ARG
Default:4
Variable:innodb_write_io_threads

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_method

    Scope:Global
    Dynamic:No
    Option:--innodb.adaptive-flushing-method

    Adaptive flushing method. Possible values are:

    • native
    • estimate
    • keep_average
  • 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_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_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_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_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_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_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_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_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_write_io_threads

    Scope:Global
    Dynamic:No
    Option:--innodb.write-io-threads

    Number of background write I/O threads in InnoDB.

Authors

Innobase Oy

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.

Table Of Contents

Previous topic

Information Schema Dictionary

Next topic

JS

This Page