A high-level overview of the main reasons for choosing a particular storage engine:

Topic list

General purpose

  • XtraDB is the best choice in the majority of cases. It is a performance-enhanced fork of InnoDB and is MariaDB's default engine until MariaDB 10.1.
  • InnoDB is a good general transaction storage engine. It is the default MySQL storage engine, and default MariaDB 10.2 storage engine, but in earlier releases XtraDB is a performance enhanced fork of InnoDB, and is usually preferred.
  • Aria, MariaDB's more modern improvement on MyISAM, has a small footprint and allows for easy copying between systems.
  • MyISAM has a small footprint and allows for easy copying between systems. MyISAM is MySQL's oldest storage engine. There is usually little reason to use it except for legacy purposes. Aria is MariaDB's more modern improvement.

Scaling, partitioning

When you want to split your database load on several servers or optimize for scaling. We also suggest looking at Galera, a synchronous multi-master cluster.

  • TokuDB is a transactional storage engine which is optimized for workloads that do not fit in memory, and provides a good compression ratio.
  • Spider uses partitioning to provide data sharding through multiple servers.
  • ColumnStore utilizes a massively parallel distributed data architecture and is designed for big data scaling to process petabytes of data.
  • The MERGE storage engine is a collection of identical MyISAM tables that can be used as one. "Identical" means that all tables have identical column and index information.
  • ScaleDB is a commercial storage engine suited for large-scale online transaction processing and data warehousing.

Compression / archive

  • MyRocks enables greater compression than InnoDB, as well as less write amplification giving better endurance of flash storage and improving overall throughput.
  • TokuDB is a transactional storage engine which is optimized for workloads that do not fit in memory, and provides a good compression ratio.
  • The Archive storage engine is, unsurprisingly, best used for archiving.

Connecting to other data sources

When you want to use data not stored in a MariaDB database.

  • CONNECT allows access to different kinds of text files and remote resources as if they were regular MariaDB tables.
  • The CSV storage engine can read and append to files stored in CSV (comma-separated-values) format. However, since MariaDB 10.0, CONNECT is a better choice and is more flexibly able to read and write such files.
  • FederatedX uses libmysql to talk to the data source, the data source being a remote RDBMS. Currently, since FederatedX only uses libmysql, it can only talk to another MySQL RDBMS.
  • CassandraSE is a storage engine allowing access to an older version of Apache Cassandra NoSQL DBMS. It was relatively experimental, and is no longer being actively developed.

Search optimized

Search engines optimized for search.

  • SphinxSE is used as a proxy to run statements on a remote Sphinx database server (mainly useful for advanced fulltext searches).
  • Mroonga provides fast CJK-ready full text searching using column store.

Cache, read-only

  • MEMORY does not write data on-disk (all rows are lost on crash) and is best-used for read-only caches of data from other tables, or for temporary work areas. With the default XtraDB and other storage engines having good caching, there is less need for this engine than in the past.

Other specialized storage engines

  • Sequence allows the creation of ascending or descending sequences of numbers (positive integers) with a given starting value, ending value and increment, creating virtual, ephemeral tables automatically when you need them.
  • The BLACKHOLE storage engine accepts data but does not store it and always returns an empty result. This can be useful in replication environments, for example, if you want to run complex filtering rules on a slave without incurring any overhead on a master.
  • OQGRAPH allows you to handle hierarchies (tree structures) and complex graphs (nodes having many connections in several directions).

Alphabetical list

  • The Archive storage engine is, unsurprisingly, best used for archiving.
  • Aria, MariaDB's more modern improvement on MyISAM, has a small footprint and allows for easy copy between systems.
  • The BLACKHOLE storage engine accepts data but does not store it and always returns an empty result. This can be useful in replication environments, for example, if you want to run complex filtering rules on a slave without incurring any overhead on a master.
  • CassandraSE is a storage engine allowing access to the Apache Cassandra NoSQL DBMS. It is still relatively experimental.
  • ColumnStore utilizes a massively parallel distributed data architecture and is designed for big data scaling to process petabytes of data.
  • CONNECT allows access to different kinds of text files and remote resources as if they were regular MariaDB tables.
  • The CSV storage engine can read and append to files stored in CSV (comma-separated-values) format. However, since MariaDB 10.0, CONNECT is a better choice and is more flexibly able to read and write such files.
  • FederatedX uses libmysql to talk to the data source, the data source being a remote RDBMS. Currently, since FederatedX only uses libmysql, it can only talk to another MySQL RDBMS.
  • InnoDB is a good general transaction storage engine. It is the default MySQL storage engine, and default MariaDB 10.2 storage engine, but in earlier releases XtraDB is a performance enhanced fork of InnoDB, and is usually preferred.
  • The MERGE storage engine is a collection of identical MyISAM tables that can be used as one. "Identical" means that all tables have identical column and index information.
  • MEMORY does not write data on-disk (all rows are lost on crash) and is best-used for read-only caches of data from other tables, or for temporary work areas. With the default XtraDB and other storage engines having good caching, there is less need for this engine than in the past.
  • Mroonga provides fast CJK-ready full text searching using column store.
  • MyISAM has a small footprint and allows for easy copying between systems. MyISAM is MySQL's oldest storage engine. There is usually little reason to use it except for legacy purposes. Aria is MariaDB's more modern improvement.
  • MyRocks enables greater compression than InnoDB, as well as less write amplification giving better endurance of flash storage and improving overall throughput.
  • OQGRAPH allows you to handle hierarchies (tree structures) and complex graphs (nodes having many connections in several directions).
  • ScaleDB is a commercial storage engine suited for large-scale online transaction processing and data warehousing.
  • Sequence allows the creation of ascending or descending sequences of numbers (positive integers) with a given starting value, ending value and increment, creating virtual, ephemeral tables automatically when you need them.
  • SphinxSE is used as a proxy to run statements on a remote Sphinx database server (mainly useful for advanced fulltext searches).
  • Spider uses partitioning to provide data sharding through multiple servers.
  • TokuDB is a transactional storage engine which is optimized for workloads that do not fit in memory, and provides a good compression ratio.
  • XtraDB is the best choice in the majority of cases. It is a performance-enhanced fork of InnoDB and is MariaDB's default engine until MariaDB 10.1.

Comments

Comments loading...