Concurrency — some applications have more granular lock requirements (such as row-level locks) than others. Choosing the right locking strategy can reduce overhead and therefore improve overall performance. This area also includes support for capabilities such as multi-version concurrency control or “snapshot” read.

Transaction Support — Not every application needs transactions, but for those that do, there are very well defined requirements such as ACID compliance and more.

Referential Integrity — The need to have the server enforce relational database referential integrity through DDL defined foreign keys.

Physical Storage — This involves everything from the overall page size for tables and indexes as well as the format used for storing data to physical disk.

Index Support — Different application scenarios tend to benefit from different index strategies. Each storage engine generally has its own indexing methods, although some (such as B-tree indexes) are common to nearly all engines.

Memory Caches — Different applications respond better to some memory caching strategies than others, so although some memory caches are common to all storage engines (such as those used for user connections or MySQL's high-speed Query Cache), others are uniquely defined only when a particular storage engine is put in play.

Performance Aids — This includes multiple I/O threads for parallel operations, thread concurrency, database checkpointing, bulk insert handling, and more.

Miscellaneous Target Features — This may include support for geospatial operations, security restrictions for certain data manipulation operations, and other similar features.

They are safer. Even if MySQL crashes or you get hardware problems, you can get your data back, either by automatic recovery or from a backup plus the transaction log.

You can combine many statements and accept them all at the same time with the COMMIT statement (if autocommit is disabled).

You can execute ROLLBACK to ignore your changes (if autocommit is disabled).

If an update fails, all of your changes are reverted. (With non-transaction-safe tables, all changes that have taken place are permanent.)

Transaction-safe storage engines can provide better concurrency for tables that get many updates concurrently with reads.

Much faster

Lower disk space requirements

Less memory required to perform updates

PrimeBase XT (PBXT) — PBXT has been designed for modern, web-based, high concurrency environments.

RitmarkFS — RitmarkFS allows you to access and manipulate the file system using SQL queries. RitmarkFS also supports file system replication and directory change tracking.

Distributed Data Engine — The Distributed Data Engine is an Open Source project that is dedicated to provide a Storage Engine for distributed data according to workload statistics.

mdbtools — A pluggable storage engine that allows read-only access to Microsoft Access .mdb database files.

solidDB for MySQL — solidDB Storage Engine for MySQL is an open source, transactional storage engine for MySQL Server. It is designed for mission-critical implementations that require a robust, transactional database. solidDB Storage Engine for MySQL is a multi-threaded storage engine that supports full ACID compliance with all expected transaction isolation levels, row-level locking, and Multi-Version Concurrency Control (MVCC) with non-blocking reads and writes.

BLOB Streaming Engine (MyBS) — The Scalable BLOB Streaming infrastructure for MySQL will transform MySQL into a scalable media server capable of streaming pictures, films, MP3 files and other binary and text objects (BLOBs) directly in and out of the database.

--myisam-recover=mode

Set the mode for automatic recovery of crashed MyISAM tables.

--delay-key-write=ALL

Don't flush key buffers between writes for any MyISAM table.

bulk_insert_buffer_size

The size of the tree cache used in bulk insert optimization.

myisam_max_sort_file_size

The maximum size of the temporary file that MySQL is allowed to use while re-creating a MyISAM index (during REPAIR TABLE, ALTER TABLE, or LOAD DATA INFILE). If the file size would be larger than this value, the index is created using the key cache instead, which is slower. The value is given in bytes.

myisam_sort_buffer_size

Set the size of the buffer used when recovering tables.

The server checks the table for errors.

If the server finds an error, it tries to do a fast table repair (with sorting and without re-creating the data file).

If the repair fails because of an error in the data file (for example, a duplicate-key error), the server tries again, this time re-creating the data file.

If the repair still fails, the server tries once more with the old repair option method (write row by row without sorting). This method should be able to repair any type of error and has low disk space requirements.

maria enables the Maria plugin. You can disable Maria by using --skip-maria.

Default is for the Maria plugin to be enabled.

maria-block-size

Sets the block size to be used for Maria index pages and data pages for the new PAGE row format. Once the page size has been set the first time mysqld has been run, you cannot change the page size without recreating all your Maria tables.

The configuration setting of this value is recorded within the maria_log_control file the first time MySQL is started with the Maria engine enabled.

The default size is 8192 (8KB).

maria-checkpoint-interval

Sets the interval between automatic checkpoints. Checkpoints in Maria synchronize the in-memory and on-disk data and then collate information about the current status of different transactions, before writing the information about the current status to the Maria log. The use of checkpoints improves the ability and speed of Maria when recovering from a crash, as recovery can continue from the last stable checkpoint in the log, instead of replaying the entire log.

The default checkpoint interval is 30 seconds. You can disable checkpoints by setting the value to 0, but disabling checkpoints will increase the time taken to recover in the event of a failure. You should only set the value to 0 during testing.

maria-force-start-after-recovery-failures

Sets the maximum number of recovery operations to attempt before deleting the Maria log files and forcing mysqld to start. Recovery of the Maria tables using the log files may fail during startup, preventing mysqld from starting.

Setting maria-force-start-after-recovery-failures to a nonzero value forces Maria to check the recovery count within the Maria log control file. This value is incremented each time a recovery is attempted. If the number of attempts matches the value of maria-force-start-after-recovery-failures, then Maria will delete all the current log files before mysqld starts.

Because forcing a start without recovery leaves tables in an inconsistent state, you should only set maria-force-start-after-recovery-failures in conjunction with the maria-recovery option, which forces checking and recovery of tables during startup without requiring the Maria log files.

The default value is 0.

maria_log_dir_path

Sets the path to the directory where the transactional log files and the log control file will be stored. You can set this to another device to improve performance.

The default value is to use the same directory as the datadir option.

maria_log_file_size

Sets the size of each of the Maria log files. When the log reaches this figure, a new log file (with a new sequential log file number) is created, and the maria_log_control file is updated.

The default size is 1GB. The minimum log file size is 8MB and the maximum log file size is (4GB - 8192 bytes)

maria_log_purge_type

Specifies how the transactional log will be purged. Supported types are as follows:

Default is immediate.

maria_max_sort_file_size

Don't use the fast sort index method to create the index if the temporary file would get bigger than this size.

Default is the maximum file size.

maria_page_checksum

Sets the default mode for page checksums. If a table has page checksums, then Maria will use the checksum to ensure that the page information is not corrupted. You can override page checksums on a table by table level by using the PAGE_CHECKSUM or CHECKSUM option during CREATE TABLE.

Default is for maria_page_checksum to be enabled.

maria_pagecache_age_threshold

The number of hits that a hot block in the page cache has to be untouched until it is considered old enough to be downgraded to a warm block. Lower values cause demotion to happen more quickly.

The default is 300.

maria_pagecache_buffer_size

Sets the size of the buffer used for data and index pages to Maria tables. You should increase this value to improve performance on data and index reads and writes, ideally to the maximum figure supported by your environment.

The default value is 8MB.

maria_pagecache_division_limit

The minimum percentage of warm blocks in the page cache.

The default value is 100.

maria_recover

Forces recovery of corrupted Maria tables without using the log files. Unlike the automatic recovery supported by Maria on transactional tables, maria-recover will work for all Maria tables.

maria_repair_threads

Number of threads to be used when repairing Maria tables when using REPAIR TABLE. The default value of 1 disables parallel repair.

maria_sort_buffer_size

Sets the size of the buffer that is allocated when sorting the index when doing a REPAIR or when creating indexes using CREATE INDEX or ALTER TABLE. Increasing this option will improve the speed of the index creation process.

The default value is 8MB.

maria_stats_method

Specifies how index statistics collection treats NULL values. Valid choices are nulls_unequal, nulls_equal, nulls_ignored.

The default setting is nulls_unequal.

maria_sync_log_dir

Controls the synchronization of the directory after a log file has been extended or a new log file has been created. Supported values are never, newfile (only when a new log file is created), and always.

The default setting is newfile.

PAGE_CHECKSUM

The PAGE_CHECKSUM table option specifies whether a page checksum for the table should be enabled. The checksum can either be switched on (value 1) or off (value 0). The default value of this option is implied by the maria_page_checksum variable.

Because the default value of the PAGE_CHECKSUM option is configurable, the checksum setting is always included in the output of SHOW CREATE TABLE if it was enabled when the table was created.

TRANSACTIONAL

Maria tables can be either transactional or non-transactional. For Maria versions less than 2.0, TRANSACTIONAL means crash-safe. Full transaction support will only be available with Maria 2.0 and later.

Changes to transactional tables are recorded in the Maria log and use slightly more space per row than non-transactional tables. By default all tables are transactional; that is, TRANSACTIONAL=1 is implied within the CREATE TABLE definition. This means that the transactional status of the table is not written in the output of the SHOW CREATE TABLE output:

mysql> create table maria_trans (id int, title char(20)) engine=Maria;
Query OK, 0 rows affected (0.05 sec)

mysql> SHOW CREATE TABLE maria_trans;
+-------------+---------------------------------------+
| Table       | Create Table                          |
+-------------+---------------------------------------+
| maria_trans | CREATE TABLE `maria_trans` (
   `id` int(11) DEFAULT NULL,
   `title` char(20) DEFAULT NULL
) ENGINE=MARIA DEFAULT CHARSET=latin1 PAGE_CHECKSUM=1 |
+-------------+---------------------------------------+
1 row in set (0.00 sec)
      

If you create a non-crash safe (non-transactional) table then the option is shown

mysql> CREATE TABLE maria_nontrans (id INT, title CHAR(20)) ENGINE=Maria TRANSACTIONAL=0;
Query OK, 0 rows affected (0.02 sec)

mysql> SHOW CREATE TABLE maria_nontrans;
+----------------+----------------------------------------------------+
| Table          | Create Table                                       |
+----------------+----------------------------------------------------+
| maria_nontrans | CREATE TABLE `maria_nontrans` (
   `id` int(11) DEFAULT NULL,
   `title` char(20) DEFAULT NULL
) ENGINE=MARIA DEFAULT CHARSET=latin1 PAGE_CHECKSUM=1 TRANSACTIONAL=0 |
+----------------+----------------------------------------------------+
1 row in set (0.00 sec)

Currently, transactional tables cannot handle SPATIAL or FULLTEXT indexes. You must use a non-transactional table if you want to use these index key types. If you try to create a table with these options, then an error will occur during CREATE TABLE.

TABLE_CHECKSUM, CHECKSUM

Forces MySQL to maintain a live checksum for all rows in the table. This maintains a rolling checksum (that is, one that changes when the table data changes), and can be used to identify corrupted tables. This is identical to the CHECKSUM on MyISAM tables.

The CHECKSUM TABLE statement, which returns the checksum for a given table, ignores record's columns which have a NULL value. This is different behavior from standard MySQL 5.1.

FIXED — identical to the FIXED row format used by MyISAM. Must be used with non-transactional tables (that is, where TRANSACTIONAL=0).

DYNAMIC — identical to the DYNAMIC row format used by MyISAM. Must be used with non-transactional tables (that is, where TRANSACTIONAL=0).

PAGE — new Maria row format where data and index information is written into pages. The PAGE option can be used with TRANSACTIONAL=0 or TRANSACTIONAL=1. The PAGE format uses a different caching mechanism than MyISAM. For more information, see Section 13.5.1, “Maria Configuration”.

Maria data pages in PAGE format have an overhead of 10 bytes/page and 5 bytes/row. Transaction and multiple concurrent writer support will use an additional overhead of 7 bytes for new rows, 14 bytes for deleted rows and 0 bytes for old compacted rows.

unknown — no checkpoint has taken place, so the current status of the log file is unknown.

in-use — information is still being written to or read from the log, or outstanding statements are still active that have information active within the log file.

free — the log file and any statements related to it have been completed and safely committed to disk, and the log file is no longer active.

Replay the contents of the log and update the data and index information.

Roll back any statements that had not completed, or where the transactions have not been committed.

Last checkpoint will be read from the log and the log will be replayed from the point calculated on the data contained within the checkpoint record. This point may be before the actual checkpoint.

If a checkpoint is not available (for example, if the log file was newly created and no checkpoint was written when the crash occurred), then all events are replayed from the start of the log file.

For each active connection, events are replayed until the last statement that completed before the crash occured, unless the statement was executed after a LOCK TABLES statement, in which case recovery takes place to the last statement executed before the LOCK TABLES statement was issued.

Because multiple connections can be active at the same time, recovery takes place for each connection individually.

During recovery, if a statement fails due to a unique key violation, then the entire statement is not rolled back. Only the data that would have triggered the unique key violation is rolled back. For example, if you had executed the following statements into a table with a unique key:

INSERT VALUES (1);
INSERT VALUES (2),(1)

The second statement would generate a unique key violation, but during recovery the statement would not be rolled back. The table would still contain two rows containing the first two values.

When using Maria tables and transactions, statements operate using the REPEATABLE READ isolation level, except when running REPAIR TABLE, OPTIMIZE TABLE, or ALTER TABLE, when all rows are exposed to all statements regardless of the isolation level.

When Maria is enabled, and if MySQL has been built using the configure option --with-maria-tmp-tables, then all internal on-disk temporary tables, including INFORMATION_SCHEMA, will be created using the Maria storage engine, in place of MyISAM.

You should not delete the maria_log_control, or the associated log files, except within the following circumstances:

Outside of these situations, deleting the maria_log_control may cause loss of data.

If you delete maria_log_control and then want to use any existing Maria tables, you should shutdown mysqld. You can then either manually run maria_check --zerofill to check the structure and format of each Maria table, or you can wait until the first access of the table, when the check will be performed automatically. On very large tables, the check should be performed manually to ensure that there is no delay during usage.

If you want to copy Maria tables from one system to another, use the following sequence:

SELECT

All issued SELECT's are running concurrently. While a SELECT is running, all writers (INSERT, DELETE, UPDATE) are blocked from using any of the used tables (that is, they wait for the table to be free before continuing) . The only exception is that one INSERT CONCURRENT can be run on each table that doesn't have any deleted rows.

UPDATE

Only one UPDATE statement can run at the same time on each table. While the UPDATE is running all other threads are blocked from using this table.

DELETE

Only one DELETE statement can run at the same time on each table. While the DELETE is running all other threads are blocked from using this table.

INSERT

If INSERT CONCURRENT is used, and there are no deleted rows in the table, only one INSERT CONCURRENT statement can run at the same time on each table. While the INSERT CONCURRENT is running all other writer threads are blocked for using this table. Any number of SELECT statements can use this table.

If normal INSERT is used or if there are deleted rows in the table, only one INSERT statement can run at the same time on the table. While the INSERT is running all SELECT, INSERT, DELETE and UPDATE are blocked from using this table.

CREATE or DROP

CREATE's on different tables can be run concurrently. On the same table, first creator wins. DROP waits until all statements using the tables are completed, after which the table is dropped.

SELECT

All issued SELECT's are running concurrently. While a SELECT is running, all writers (INSERT, DELETE, UPDATE) are blocked from using any of the used tables (ie, they wait for the table to be free before continuing).

UPDATE

As part of the single writer, only one UPDATE statement can run at the same time on each table. While the UPDATE is running all other threads using UPDATE or INSERT are blocked from using this table.

INSERT

As part of the single writer, only one INSERT statement can run at the same time on the table. While the INSERT is running all other threads using UPDATE or INSERT are blocked from using this table.

DELETE

Only one DELETE statement can run at the same time on each table. While the DELETE is running all other threads are blocked from using this table.

CREATE or DROP

CREATE operations on different tables can be run concurrently. On the same table, first creator wins. DROP waits until all statements using the tables are completed, after which the table is dropped.

To use multiple writers you should lock tables using the statement:

LOCK TABLES table_name WRITE CONCURRENT

During multiple write operations, all SELECT statements operate in REPEATABLE READ mode.

All INSERT statements are considered atomic, and will use concurrent insert locks to ensure consistency.

Concurrent inserts are not supported on:

Data and indexes are crash safe. On crash, things will rollback to state of the start of statement or last LOCK TABLES commands.

Maria can replay everything from the log. Including CREATE/DROP/RENAME/TRUNCATE tables.

LOAD INDEX can skip index blocks for not wanted indexes

Supports all MyISAM row formats and the new transactional format where data is stored in pages.

When using transactional format (default) row data can be cached.

Maria has unit tests of most parts

Supports both crash safe (soon to be transactional) and not transactional tables. (Not transactional tables are not logged and rows uses less space.) CREATE TABLE foo (...) TRANSACTIONAL=0|1

Transactional is the only crashsafe/transactional row format.

Block format should give a notable speed improvement on systems with bad data caching (for example Windows).

Maria uses big (1GB by default) log files.

Maria has a log control file (maria_log_control) and log files ( maria_log.????????). The log files can be automatically purged when not needed or purged on demand (after backup).

Maria uses by default 8K pages for indexes (MyISAM 1K). Maria should be faster on static size indexes but slower on variable length keys (until we add a directory to index pages).

Maria 1.0 has one writer or many readers. (MyISAM can have one inserter and many readers when using concurrent inserts.)

Storage of very small rows (< 25 bytes) is not efficient for PAGE format.

In Maria PAGE format there is an overhead of 10 bytes/page and 5 bytes/row. Transaction and multiple concurrent writer support will use an additional overhead of 7 bytes for new rows, 14 bytes for deleted rows and 0 bytes for old compacted rows.

Maria doesn't support INSERT DELAYED.

The maria_page_buffer_size system variable that controls the Maria page cache size is not dynamically settable like the corresponding MyISAM variable, key_buffer_size.

No external locking (MyISAM has external locking, but it is not much used).

Maria has one page size for both index and data (defined when Maria is used first time). MyISAM supports different page sizes per index.

Index requires one extra byte per index page.

Maria doesn't support RAID (disabled in MyISAM too).

Minimum data file size for PAGE format is 16K (with 8K pages).

maria_chk — checks Maria tables for corruption. Similar to the myisamchk command; see Section 4.6.3, “myisamchk — MyISAM Table-Maintenance Utility”.

maria_ftdump — dumps information about fulltext indexes in Maria tables. Similar to the myisam_ftdump command; see Section 4.6.2, “myisam_ftdump — Display Full-Text Index information”.

maria_pack — packs a Maria table to save space. Similar to the myisampack command; see Section 4.6.5, “myisampack — Generate Compressed, Read-Only MyISAM Tables”.

maria_read_log — displays the contents of a Maria log file or applies the contents to existing tables. This tool is a utility for the log files but is not used or required for recovery of data from the log file.

maria_dump_log — used for interpreting log content for people who understand maria transaction log internals.

If the log files are damaged or inconsistent, Maria may fail to start. We should fix that if this happens and mysqld is restarted (thanks to mysqld_safe, instance manager or other script) it should disregard the old logs, start anyway and automatically repair any tables that were found to be crashed on open.

Temporary fix is to remove maria_log.???????? files from the data directory, restart mysqld and run CHECK TABLE/REPAIR TABLE or mysqlcheck on your Maria tables.

If we get a write failure on disk for the log, we should stop all usage of transactional tables and mark all transactional tables that are changed as crashed.

Missing features that are planned to be fixed before Beta

We will add a maria-recover option to automatically repair any crashed tables on open. (This is needed for non-transactional tables and also in edge cases for transactional tables when the table crashed because of a bug in MySQL or Maria code)

13.5.10.1: Is there compression of text/blob columns or entire pages in Maria?

13.5.10.2: Is DELAY_KEY_WRITE honored on Maria tables?

--ignore-builtin-innodb

This option that causes the server to behave as if the built-in InnoDB is not present. It causes other InnoDB options not to be recognized. This option was added in MySQL 5.1.33.

--innodb[=value]

Controls loading of the InnoDB storage engine, if the server was compiled with InnoDB support. As of MySQL 5.1.36, this option has a tristate format, with possible values of OFF, ON, or FORCE. Before MySQL 5.1.36, this is a boolean option. Use --innodb to enable or --skip-innodb to disable InnoDB. See Section 5.1.3, “Server Options for Loading Plugins”.

--innodb_status_file

Controls whether InnoDB creates a file named innodb_status.<pid> in the MySQL data directory. If enabled, InnoDB periodically writes the output of SHOW ENGINE INNODB STATUS to this file.

By default, the file is not created. To create it, start mysqld with the --innodb_status_file=1 option. The file is deleted during normal shutdown.

ignore_builtin_innodb

Whether the server was started with the --ignore-builtin-innodb option, which causes the server to behave as if the built-in InnoDB is not present. This variable was added in MySQL 5.1.33.

innodb_adaptive_hash_index

Whether InnoDB adaptive hash indexes are enabled or disabled (see Section 13.6.10.4, “Adaptive Hash Indexes”). This variable is enabled by default. Use --skip-innodb_adaptive_hash_index at server startup to disable it. This variable was added in MySQL 5.1.24.

innodb_additional_mem_pool_size

The size in bytes of a memory pool InnoDB uses to store data dictionary information and other internal data structures. The more tables you have in your application, the more memory you need to allocate here. If InnoDB runs out of memory in this pool, it starts to allocate memory from the operating system and writes warning messages to the MySQL error log. The default value is 1MB.

innodb_autoextend_increment

The increment size (in MB) for extending the size of an auto-extending tablespace file when it becomes full. The default value is 8.

innodb_buffer_pool_awe_mem_mb

The size of the buffer pool (in MB), if it is placed in the AWE memory. If it is greater than 0, innodb_buffer_pool_size is the window in the 32-bit address space of mysqld where InnoDB maps that AWE memory. A good value for innodb_buffer_pool_size is 500MB. The maximum possible value is 63000.

To take advantage of AWE memory, you will need to recompile MySQL yourself. The current project settings needed for doing this can be found in the storage/innobase/os/os0proc.c source file.

This variable was removed in MySQL 5.1.13. Before that, it is relevant only in 32-bit Windows. If your 32-bit Windows operating system supports more than 4GB memory, using so-called “Address Windowing Extensions,” you can allocate the InnoDB buffer pool into the AWE physical memory using this variable.

innodb_autoinc_lock_mode

The locking mode to use for generating auto-increment values. The allowable values are 0, 1, or 2, for “traditional”, “consecutive”, or “interleaved” lock mode, respectively. Section 13.6.4.3, “AUTO_INCREMENT Handling in InnoDB”, describes the characteristics of these modes.

This variable was added in MySQL 5.1.22 with a default of 1 (“consecutive” lock mode). Before 5.1.22, InnoDB uses “traditional” lock mode.

innodb_buffer_pool_size

The size in bytes of the memory buffer InnoDB uses to cache data and indexes of its tables. The default value is 8MB. The larger you set this value, the less disk I/O is needed to access data in tables. On a dedicated database server, you may set this to up to 80% of the machine physical memory size. However, do not set it too large because competition for physical memory might cause paging in the operating system.

innodb_checksums

InnoDB can use checksum validation on all pages read from the disk to ensure extra fault tolerance against broken hardware or data files. This validation is enabled by default. However, under some rare circumstances (such as when running benchmarks) this extra safety feature is unneeded and can be disabled with --skip-innodb-checksums.

innodb_commit_concurrency

The number of threads that can commit at the same time. A value of 0 (the default) allows any number of transactions to commit simultaneously.

As of MySQL 5.1.36, the value of innodb_commit_concurrency cannot be changed at runtime from zero to nonzero or vice versa. The value can still be changed from one nonzero value to another.

innodb_concurrency_tickets

The number of threads that can enter InnoDB concurrently is determined by the innodb_thread_concurrency variable. A thread is placed in a queue when it tries to enter InnoDB if the number of threads has already reached the concurrency limit. When a thread is allowed to enter InnoDB, it is given a number of “free tickets” equal to the value of innodb_concurrency_tickets, and the thread can enter and leave InnoDB freely until it has used up its tickets. After that point, the thread again becomes subject to the concurrency check (and possible queuing) the next time it tries to enter InnoDB. The default value is 500.

innodb_data_file_path

The paths to individual data files and their sizes. The full directory path to each data file is formed by concatenating innodb_data_home_dir to each path specified here. The file sizes are specified in KB, MB, or GB (1024MB) by appending K, M, or G to the size value. The sum of the sizes of the files must be at least 10MB. If you do not specify innodb_data_file_path, the default behavior is to create a single 10MB auto-extending data file named ibdata1. The size limit of individual files is determined by your operating system. You can set the file size to more than 4GB on those operating systems that support big files. You can also use raw disk partitions as data files. For detailed information on configuring InnoDB tablespace files, see Section 13.6.2, “InnoDB Configuration”.

innodb_data_home_dir

The common part of the directory path for all InnoDB data files in the shared tablespace. This setting does not affect the location of per-file tablespaces when innodb_file_per_table is enabled. The default value is the MySQL data directory. If you specify the value as an empty string, you can use absolute file paths in innodb_data_file_path.

innodb_doublewrite

If this variable is enabled (the default), InnoDB stores all data twice, first to the doublewrite buffer, and then to the actual data files. This variable can be turned off with --skip-innodb_doublewrite for benchmarks or cases when top performance is needed rather than concern for data integrity or possible failures.

innodb_fast_shutdown

The InnoDB shutdown mode. By default, the value is 1, which causes a “fast” shutdown (the normal type of shutdown). If the value is 0, InnoDB does a full purge and an insert buffer merge before a shutdown. These operations can take minutes, or even hours in extreme cases. If the value is 1, InnoDB skips these operations at shutdown. If the value is 2, InnoDB will just flush its logs and then shut down cold, as if MySQL had crashed; no committed transaction will be lost, but crash recovery will be done at the next startup. A value of 2 cannot be used on NetWare.

innodb_file_io_threads

The number of file I/O threads in InnoDB. Normally, this should be left at the default value of 4, but disk I/O on Windows may benefit from a larger number. On Unix, increasing the number has no effect; InnoDB always uses the default value.

innodb_file_per_table

If innodb_file_per_table is disabled (the default), InnoDB creates tables in the shared tablespace. If innodb_file_per_table is enabled, InnoDB creates each new table using its own .ibd file for storing data and indexes, rather than in the shared tablespace. See Section 13.6.2.1, “Using Per-Table Tablespaces”.

innodb_flush_log_at_trx_commit

If the value of innodb_flush_log_at_trx_commit is 0, the log buffer is written out to the log file once per second and the flush to disk operation is performed on the log file, but nothing is done at a transaction commit. When the value is 1 (the default), the log buffer is written out to the log file at each transaction commit and the flush to disk operation is performed on the log file. When the value is 2, the log buffer is written out to the file at each commit, but the flush to disk operation is not performed on it. However, the flushing on the log file takes place once per second also when the value is 2. Note that the once-per-second flushing is not 100% guaranteed to happen every second, due to process scheduling issues.

The default value of 1 is the value required for ACID compliance. You can achieve better performance by setting the value different from 1, but then you can lose at most one second worth of transactions in a crash. With a value of 0, any mysqld process crash can erase the last second of transactions. With a value of 2, then only an operating system crash or a power outage can erase the last second of transactions. However, InnoDB's crash recovery is not affected and thus crash recovery does work regardless of the value.

For the greatest possible durability and consistency in a replication setup using InnoDB with transactions, use innodb_flush_log_at_trx_commit = 1 and sync_binlog = 1 in your master server my.cnf file.

innodb_flush_method

By default, InnoDB uses fsync() to flush both the data and log files. If innodb_flush_method option is set to O_DSYNC, InnoDB uses O_SYNC to open and flush the log files, and fsync() to flush the data files. If O_DIRECT is specified (available on some GNU/Linux versions, FreeBSD, and Solaris), InnoDB uses O_DIRECT (or directio() on Solaris) to open the data files, and uses fsync() to flush both the data and log files. Note that InnoDB uses fsync() instead of fdatasync(), and it does not use O_DSYNC by default because there have been problems with it on many varieties of Unix. This variable is relevant only for Unix. On Windows, the flush method is always async_unbuffered and cannot be changed.

Different values of this variable can have a marked effect on InnoDB performance. For example, on some systems where InnoDB data and log files are located on a SAN, it has been found that setting innodb_flush_method to O_DIRECT can degrade performance of simple SELECT statements by a factor of three.

Formerly it was possible to specify a value of fdatasync to obtain the default behavior. This is no longer possible as of MySQL 5.1.24 because it can be confusing that a value of fdatasync causes use of fsync() rather than fdatasync() for flushing.

innodb_force_recovery

The crash recovery mode. Possible values are from 0 to 6. The meanings of these values are described in Section 13.6.6.2, “Forcing InnoDB Recovery”.

innodb_lock_wait_timeout

The timeout in seconds an InnoDB transaction may wait for a row lock before giving up. The default value is 50 seconds. A transaction that tries to access a row that is locked by another InnoDB transaction will hang for at most this many seconds before issuing the following error:

ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

When a lock wait timeout occurs, the current statement is not executed. The current transaction is not rolled back. (To have the entire transaction roll back, start the server with the --innodb_rollback_on_timeout option, available as of MySQL 5.1.15. See also Section 13.6.12, “InnoDB Error Handling”.)

innodb_lock_wait_timeout applies to InnoDB row locks only. A MySQL table lock does not happen inside InnoDB and this timeout does not apply to waits for table locks.

InnoDB does detect transaction deadlocks in its own lock table immediately and rolls back one transaction. The lock wait timeout value does not apply to such a wait.

innodb_locks_unsafe_for_binlog

This variable affects how InnoDB uses gap locking for searches and index scans. Normally, InnoDB uses an algorithm called next-key locking that combines index-row locking with gap locking. InnoDB performs row-level locking in such a way that when it searches or scans a table index, it sets shared or exclusive locks on the index records it encounters. Thus, the row-level locks are actually index-record locks. In addition, a next-key lock on an index record also affects the “gap” before that index record. That is, a next-key lock is an index-record lock plus a gap lock on the gap preceding the index record. If one session has a shared or exclusive lock on record R in an index, another session cannot insert a new index record in the gap immediately before R in the index order. See Section 13.6.8.4, “InnoDB Record, Gap, and Next-Key Locks”.

By default, the value of innodb_locks_unsafe_for_binlog is 0 (disabled), which means that gap locking is enabled: InnoDB uses next-key locks for searches and index scans. To enable the variable, set it to 1. This causes gap locking to be disabled: InnoDB uses only index-record locks for searches and index scans.

Enabling innodb_locks_unsafe_for_binlog does not disable the use of gap locking for foreign-key constraint checking or duplicate-key checking.

The effect of enabling innodb_locks_unsafe_for_binlog is similar to but not identical to setting the transaction isolation level to READ COMMITTED:

READ COMMITTED therefore offers finer and more flexible control than innodb_locks_unsafe_for_binlog. For additional details about the effect of isolation level on gap locking, see Section 12.4.6, “SET TRANSACTION Syntax”.

Enabling innodb_locks_unsafe_for_binlog may cause phantom problems because other sessions can insert new rows into the gaps when gap locking is disabled. Suppose that there is an index on the id column of the child table and that you want to read and lock all rows from the table having an identifier value larger than 100, with the intention of updating some column in the selected rows later:

SELECT * FROM child WHERE id > 100 FOR UPDATE;

The query scans the index starting from the first record where 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 same SELECT 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, if innodb_locks_unsafe_for_binlog is enabled, InnoDB guarantees at most an isolation level of READ COMMITTED. (Conflict serializability is still guaranteed.) For additional information about phantoms, see Section 13.6.8.5, “Avoiding the Phantom Problem Using Next-Key Locking”.

Enabling innodb_locks_unsafe_for_binlog has additional effects:

Consider the following example, beginning with this table:

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 (see Section 13.6.10.1, “Clustered and Secondary Indexes”).

Suppose that one client performs an UPDATE using these statements:

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

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

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

As InnoDB executes each UPDATE, it first acquires an exclusive lock for each row, and then determines whether to modify it. If InnoDB does not modify the row and innodb_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.

If innodb_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 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

If innodb_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

Semi-consistent read is available as of MySQL 5.1.5. Before 5.1.5, the second UPDATE proceeds part way before it blocks. It begins acquiring x-locks, and blocks when it tries to acquire one for a row still locked by first UPDATE. The second UPDATE does not proceed until the first UPDATE commits or rolls back:

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

In this case, the second UPDATE must wait for a commit or rollback of the first UPDATE, even though it affects different rows. The first UPDATE has an exclusive lock on row (2,3) that it has not released. As the second UPDATE scans rows, it tries to acquire an exclusive lock for that same row, which it cannot have. Thus, before MySQL 5.1.5, enabling innodb_locks_unsafe_for_binlog still does not allow operations such as UPDATE to overtake other similar operations (such as another UPDATE) even when they affect different rows.

innodb_log_arch_dir

This variable is deprecated, and was removed in MySQL 5.1.21.

innodb_log_archive

This variable is deprecated, and was removed in MySQL 5.1.18.

innodb_log_buffer_size

The size in bytes of the buffer that InnoDB uses to write to the log files on disk. The default value is 1MB. Sensible values range from 1MB to 8MB. A large log buffer allows large transactions to run without a need to write the log to disk before the transactions commit. Thus, if you have big transactions, making the log buffer larger saves disk I/O.

innodb_log_file_size

The size in bytes of each log file in a log group. The combined size of log files must be less than 4GB. The default value is 5MB. Sensible values range from 1MB to 1/N-th of the size of the buffer pool, where N is the number of log files in the group. The larger the value, the less checkpoint flush activity is needed in the buffer pool, saving disk I/O. But larger log files also mean that recovery is slower in case of a crash.

innodb_log_files_in_group

The number of log files in the log group. InnoDB writes to the files in a circular fashion. The default (and recommended) value is 2.

innodb_log_group_home_dir

The directory path to the InnoDB log files. If you do not specify any InnoDB log variables, the default is to create two 5MB files names ib_logfile0 and ib_logfile1 in the MySQL data directory.

innodb_max_dirty_pages_pct

This is an integer in the range from 0 to 100. The default value is 90. The main thread in InnoDB tries to write pages from the buffer pool so that the percentage of dirty (not yet written) pages will not exceed this value.

innodb_max_purge_lag

This variable controls how to delay INSERT, UPDATE, and DELETE operations when purge operations are lagging (see Section 13.6.9, “InnoDB Multi-Versioning”). The default value 0 (no delays).

The InnoDB transaction system maintains a list of transactions that have delete-marked index records by UPDATE or DELETE operations. Let the length of this list be purge_lag. When purge_lag exceeds innodb_max_purge_lag, each INSERT, UPDATE, and DELETE operation is delayed by ((purge_lag/innodb_max_purge_lag)×10)–5 milliseconds. The delay is computed in the beginning of a purge batch, every ten seconds. The operations are not delayed if purge cannot run because of an old consistent read view that could see the rows to be purged.

A typical setting for a problematic workload might be 1 million, assuming that transactions are small, only 100 bytes in size, and it is allowable to have 100MB of unpurged InnoDB table rows.

innodb_mirrored_log_groups

The number of identical copies of log groups to keep for the database. This should be set to 1.

innodb_open_files

This variable is relevant only if you use multiple tablespaces in InnoDB. It specifies the maximum number of .ibd files that InnoDB can keep open at one time. The minimum value is 10. The default value is 300.

The file descriptors used for .ibd files are for InnoDB only. They are independent of those specified by the --open-files-limit server option, and do not affect the operation of the table cache.

innodb_rollback_on_timeout

In MySQL 5.1, InnoDB rolls back only the last statement on a transaction timeout by default. If --innodb_rollback_on_timeout is specified, a transaction timeout causes InnoDB to abort and roll back the entire transaction (the same behavior as in MySQL 4.1). This variable was added in MySQL 5.1.15.

innodb_stats_on_metadata

When this variable is enabled (which is the default, as before the variable was created), InnoDB updates statistics during metadata statements such as SHOW TABLE STATUS or SHOW INDEX, or when accessing the INFORMATION_SCHEMA tables TABLES or STATISTICS. (These updates are similar to what happens for ANALYZE TABLE.) When disabled, InnoDB does not updates statistics during these operations. Disabling this variable can improve access speed for schemas that have a large number of tables or indexes. It can also improve the stability of execution plans for queries that involve InnoDB tables.

This variable was added in MySQL 5.1.17.

innodb_support_xa

When the variable is enabled (the default), InnoDB support for two-phase commit in XA transactions is enabled, which causes an extra disk flush for transaction preparation.

If you do not wish to use XA transactions, you can disable this variable to reduce the number of disk flushes and get better InnoDB performance.

innodb_sync_spin_loops

The number of times a thread waits for an InnoDB mutex to be freed before the thread is suspended. The default value is 20.

innodb_table_locks

If autocommit = 0, InnoDB honors LOCK TABLES; MySQL does not return from LOCK TABLES ... WRITE until all other threads have released all their locks to the table. The default value of innodb_table_locks is 1, which means that LOCK TABLES causes InnoDB to lock a table internally if autocommit = 0.

innodb_thread_concurrency

InnoDB tries to keep the number of operating system threads concurrently inside InnoDB less than or equal to the limit given by this variable. Once the number of threads reaches this limit, additional threads are placed into a wait state within a FIFO queue for execution. Threads waiting for locks are not counted in the number of concurrently executing threads.

The correct value for this variable is dependent on environment and workload. You will need to try a range of different values to determine what value works for your applications. A recommended value is 2 times the number of CPUs plus the number of disks.

The range of this variable is 0 to 1000. A value of 20 or higher is interpreted as infinite concurrency before MySQL 5.1.12. From 5.1.12 on, you can disable thread concurrency checking by setting the value to 0. Disabling thread concurrency checking allows InnoDB to create as many threads as it needs.

The default value is 20 before MySQL 5.1.11, and 8 from 5.1.11 on.

innodb_thread_sleep_delay

How long InnoDB threads sleep before joining the InnoDB queue, in microseconds. The default value is 10,000. A value of 0 disables sleep.

innodb_use_legacy_cardinality_algorithm

InnoDB uses random numbers to generate dives into indexes for calculating index cardinality. However, under certain conditions, the algorithm does not generate random numbers, so ANALYZE TABLE sometimes does not update cardinality estimates properly. An alternative algorithm was introduced in MySQL 5.1.35 with better randomization properties, and the innodb_use_legacy_cardinality_algorithm, system variable which algorithm to use. The default value of the variable is 1 (ON), to use the original algorithm for compatibility with existing applications. The variable can be set to 0 (OFF) to use the new algorithm with improved randomness.

This variable is not used in 5.4 because the improved algorithm is used by default.

sync_binlog

If the value of this variable is greater than 0, the MySQL server synchronizes its binary log to disk (using fdatasync()) after every sync_binlog writes to the binary log. There is one write to the binary log per statement if autocommit is enabled, and one write per transaction otherwise. The default value of sync_binlog is 0, which does no synchronizing to disk. A value of 1 is the safest choice, because in the event of a crash you lose at most one statement or transaction from the binary log. However, it is also the slowest choice (unless the disk has a battery-backed cache, which makes synchronization very fast).