Skip to content
Tutorials Center
My learning

Topics

Lesson 14 of 40 · Data Types and Storage Engines

MariaDB Storage Engines – InnoDB, Aria, MyISAM and More

Understand MariaDB's pluggable storage engines: InnoDB, Aria, MyISAM, MEMORY, CONNECT, ColumnStore, MyRocks and Spider, how to choose one, check it, change it and why InnoDB is the default.

A storage engine is the part of MariaDB that actually stores, reads and locks table data. The SQL layer above (parser, optimizer) is the same for every table, but each table can pick a different engine, and the engine decides whether the table supports transactions, foreign keys, crash recovery and full-text search. MariaDB's pluggable design is one of its strengths, and one of the things that separates it from many other databases.

The statements here need a real server, so they are read-only; a runnable stand-in near the end demonstrates the transaction difference that matters most.

Seeing and choosing the engine

SQL
SHOW ENGINES;                                  -- what this server offers and its default
SHOW CREATE TABLE orders\G                     -- look for ENGINE=
SELECT table_name, engine
FROM information_schema.tables
WHERE table_schema = DATABASE();

Pick an engine when you create the table, or convert it later:

SQL
CREATE TABLE sessions_cache (
  id   CHAR(32) PRIMARY KEY,
  data VARCHAR(2000)
) ENGINE=MEMORY;

ALTER TABLE logs ENGINE=Aria;       -- rebuilds the whole table
SET GLOBAL default_storage_engine = 'InnoDB';

If you do not name an engine, MariaDB uses default_storage_engine, which is InnoDB in every modern release.

The main engines

Engine Transactions Row locking Crash safe Typical use
InnoDB Yes (ACID) Yes Yes Almost everything: OLTP, e-commerce, apps
Aria No (crash-safe) Table Yes System tables, temporary tables on disk, read-mostly data
MyISAM No Table No Legacy; avoid for new work
MEMORY No Table No (data lost on restart) Small lookup tables, scratch data
CSV No Table No Importing/exporting CSV files
ARCHIVE No Row on insert Partial Compressed append-only logs
BLACKHOLE No n/a n/a Discards data; used as a replication relay
SEQUENCE n/a n/a n/a Virtual tables of numbers
CONNECT No Table Depends Read external files and remote tables
ColumnStore Limited n/a Yes Analytics on very large tables (columnar)
MyRocks Yes Row Yes Write-heavy workloads, compact on flash
Spider Depends Depends Depends Sharding across servers

InnoDB

InnoDB is the default for good reasons:

  • ACID transactions with COMMIT and ROLLBACK.
  • Row-level locking and MVCC, so readers do not block writers.
  • Foreign keys and crash recovery through its redo log.
  • The primary key is a clustered index: row data lives in the leaves of the primary key B-tree.
  • A buffer pool caches data and indexes in memory. Setting innodb_buffer_pool_size (often 50 to 70 percent of RAM on a dedicated database server) is the most important single tuning knob.

If you are unsure, use InnoDB.

Aria

Aria is MariaDB's crash-safe successor to MyISAM. It is used for the internal temporary tables that large queries create on disk and for some system tables. It does not support transactions or foreign keys, so you rarely choose it for your own data, except for read-mostly tables that need fast scans, with the PAGE row format and TRANSACTIONAL=1 for crash safety.

MyISAM

The old MySQL default. Table-level locks (a write blocks all reads), no transactions, no foreign keys, and tables can be corrupted by a crash. You will meet it in old schemas. Convert with ALTER TABLE t ENGINE=InnoDB.

MEMORY

Stores rows in RAM. Very fast, but contents vanish at restart, and it does not support TEXT or BLOB. Good for small, rebuildable caches. Often an InnoDB table plus a good buffer pool is just as fast and safer.

ColumnStore, MyRocks, Spider, CONNECT

These are installed as plugins: INSTALL SONAME 'ha_rocksdb';. ColumnStore stores columns together, which makes scanning a few columns of billions of rows very fast, ideal for reporting. MyRocks uses an LSM tree for compact storage and heavy writes. Spider shards one logical table over many servers. CONNECT exposes CSV, JSON or remote tables as if they were local. Treat these as specialist tools you adopt for a measured need.

Mixing engines in one query

You can join tables of different engines, but transactions only cover transactional engines. A ROLLBACK will undo the InnoDB changes and leave a MyISAM or MEMORY write in place, which is a source of subtle bugs. Keep tables that change together in the same transactional engine.

flowchart TD
  SQL["SQL layer: parser, optimizer"] --> API["Storage engine API"]
  API --> I["InnoDB"]
  API --> A["Aria"]
  API --> M["MEMORY"]
  API --> C["ColumnStore"]
  API --> X["... plugins"]

One SQL layer, many engines behind a common API.

Choosing: a short guide

Which engine?

Normal app data InnoDB
Huge analytics ColumnStore
Tiny scratch cache MEMORY
Old MyISAM table Convert to InnoDB

When in doubt, InnoDB.

1 / 2

What the transaction difference looks like

An engine without transactions applies every statement immediately. With InnoDB you can undo a group of changes. The browser engine behaves like InnoDB here, so you can see the difference a transactional engine makes:

SQL ROLLBACK undoes a whole group of changes

Press Run (or Ctrl + Enter) to see the result here.

On MyISAM or MEMORY, the same two UPDATE statements would stay applied even after ROLLBACK, and a crash between them would leave money missing.

A second stand-in shows what a MEMORY-style table is good at, a small lookup that you can rebuild at any moment:

SQL A rebuildable lookup table

Press Run (or Ctrl + Enter) to see the result here.

Converting safely

  1. Back up the table (mariadb-dump).
  2. Check for features the new engine lacks (foreign keys, full-text indexes on old InnoDB, spatial indexes).
  3. Run ALTER TABLE t ENGINE=InnoDB; in a quiet period. It copies the table and blocks writes while doing so on very old versions; check ALGORITHM=COPY and timing on a copy first.
  4. Verify with SHOW CREATE TABLE.

Check your understanding

Quick check · 4 questions

of 4 answered You scored / 4 · perfect!

  1. 1. Which engine should you choose for ordinary application tables?

    InnoDB gives ACID transactions, row locking, foreign keys and crash recovery, and it is the default.
  2. 2. What happens to a MEMORY table's rows when the server restarts?

    The definition survives but the data only lives in RAM.
  3. 3. Why is mixing InnoDB and MyISAM in one transaction risky?

    Non-transactional engines apply changes immediately, so a rollback leaves them behind.
  4. 4. What is MariaDB's pluggable storage engine architecture?

    The SQL layer talks to engines through one interface, so you can choose per table.

Summary

  • A storage engine stores data and decides on transactions, locking and crash safety; you choose it per table with ENGINE=.
  • InnoDB is the right default; Aria, MEMORY, ColumnStore, MyRocks and others serve special jobs.
  • Avoid MyISAM in new work and convert old tables to InnoDB.
  • Keep tables that change together in the same transactional engine.

Next: Character sets and collations