MySQL/MariaDB Storage Engines

The Pluggable Storage Engine Architecture

MySQL and MariaDB use a pluggable architecture that separates the query processing and connection handling from the actual data storage and retrieval mechanisms. This architecture has several key components:

1. Connection Layer: Handles client connections, authentication, and security
2. Service Layer: Processes SQL statements, creates query execution plans, and manages caching
3. Storage Engine Layer: Responsible for storing and retrieving all data stored in MySQL

The storage engine layer is designed to be pluggable—meaning you can select different engines with different capabilities to power your tables. This design offers tremendous flexibility for optimizing your database for specific workloads.

How Storage Engines Work

When you create a table in MySQL or MariaDB, you specify which storage engine to use:

1CREATE TABLE example (
2  id INT PRIMARY KEY,
3  data VARCHAR(100)
4) ENGINE=InnoDB;

If you don't specify an engine, the default engine is used (InnoDB in modern MySQL/MariaDB installations). Each table is managed by a specific storage engine, and different tables in the same database can use different engines.

When a query is executed, the query parser and optimizer create an execution plan, but the storage engine handles:

• How data is physically stored (file formats, compression, etc.)
• Index structures and implementation
• Locking mechanisms for concurrency control
• Transaction support and ACID compliance
• Crash recovery mechanisms

Viewing Available Storage Engines

To see which storage engines are available in your MySQL/MariaDB instance:

1SHOW ENGINES;

This command displays all supported engines along with:

• Support status (YES, NO, DEFAULT)
• Whether transactions are supported
• Whether XA distributed transactions are supported
• Whether savepoints are supported
• Comments about the engine

Checking a Table's Storage Engine

To check which engine a table uses:

1SHOW TABLE STATUS WHERE Name = 'table_name';
2-- or
3SHOW CREATE TABLE table_name;

Default Storage Engine

The default storage engine is defined in the MySQL configuration file (my.cnf or my.ini):

1[mysqld]
2default-storage-engine=InnoDB

You can check the current default:

1SELECT @@default_storage_engine;

MySQL vs. MariaDB Storage Engine Support

While MySQL and MariaDB share many storage engines, there are some differences:

MySQL-specific engines:

• InnoDB (customized implementation)
• NDB Cluster (for MySQL Cluster)

MariaDB-specific engines:

• Aria (enhanced replacement for MyISAM)
• ColumnStore (columnar storage for analytics)
• Spider (database sharding engine)
• CONNECT (connects to external data sources)
• Mroonga (full-text search engine)
• MyRocks (based on Facebook's RocksDB)
• S3 (for Amazon S3 storage)

The pluggable storage engine architecture is one of the most powerful features of MySQL and MariaDB, allowing you to optimize for different workloads without switching database platforms.

InnoDB Storage Engine

InnoDB has been the default storage engine in MySQL since version 5.5 and in MariaDB since version 10.2. It's a robust, ACID-compliant, transactional storage engine designed for high reliability and high performance.

Key Features of InnoDB

1. Full ACID Transaction Support

• Atomicity: Operations are atomic—they either complete entirely or not at all
• Consistency: Data remains in a consistent state after each transaction
• Isolation: Transactions are isolated from one another (with configurable isolation levels)
• Durability: Committed transactions survive system crashes and power failures

1. Row-Level Locking

• Allows multiple transactions to modify different rows in the same table simultaneously
• Greatly improves concurrency compared to table-level locking
• Includes non-locking reads to further increase throughput

1. Foreign Key Constraints

• Enforces referential integrity between related tables
• Prevents orphaned records and data inconsistencies

1. Crash Recovery

• Automatic recovery after system crashes
• Uses a doublewrite buffer to prevent corruption during partial page writes
• Transaction logs ensure durability of committed transactions

1. MVCC (Multi-Version Concurrency Control)

• Maintains multiple versions of rows to enable consistent non-locking reads
• Allows reads and writes to proceed concurrently with minimal blocking
• Configured through transaction isolation levels

1. Clustered Indexes

• Data is physically stored in the order of the primary key
• Primary key lookups are extremely fast
• All indexes contain the primary key, enabling efficient index-only queries

1. Automatic Deadlock Detection

• Identifies and resolves deadlocks automatically
• Rolls back the transaction that required the least amount of undo work

1. Buffer Pool

• Caches table and index data in memory for faster access
• Implements a least recently used (LRU) algorithm with optimizations for full table scans
• Can be configured to span multiple instances for better memory utilization

1. Adaptive Hash Index

• Automatically creates hash indexes in memory for frequently accessed pages
• Can dramatically improve performance for certain workloads
• Self-tuning with minimal configuration required

InnoDB Disk Storage Format

InnoDB organizes data using:

1. Tablespaces:

• System Tablespace: Contains the data dictionary, undo logs, and can store table data
• File-Per-Table Tablespaces: Each table has its own .ibd file (recommended approach)
• General Tablespaces: User-defined tablespaces that can store multiple tables

1. Redo Logs:

• Sequential log files that record all modifications to data
• Used for crash recovery and transaction durability
• Written to disk immediately on transaction commit

1. Doublewrite Buffer:

• Provides protection against partial page writes
• Writes pages to a contiguous buffer before writing to their final locations

1. Undo Logs:

• Store the previous versions of modified data
• Enable transaction rollback and consistent reads (MVCC)
• Managed in the system tablespace or dedicated undo tablespaces

When to Use InnoDB

InnoDB is the best choice for most applications, especially when you need:

• Transactional integrity: Financial applications, e-commerce, any data that must be consistent
• High concurrency: Applications with many simultaneous users modifying data
• Foreign key constraints: Applications with complex data relationships
• Crash-safe operation: Systems where data loss is unacceptable
• General purpose database usage: Default choice for most modern web applications

InnoDB Best Practices

1. Properly size your buffer pool:

1   -- Set buffer pool to 70-80% of available RAM on a dedicated DB server
2   SET GLOBAL innodb_buffer_pool_size = 8G;

1. Use file-per-table tablespaces:

1   SET GLOBAL innodb_file_per_table = ON;

1. Choose appropriate primary keys:

• Use short, monotonically increasing values for primary keys
• Avoid large or frequently updated columns as primary keys

1. Monitor and tune InnoDB-specific metrics:

1   SHOW ENGINE INNODB STATUS;

1. Adjust the redo log size for write-intensive workloads:

1   SET GLOBAL innodb_log_file_size = 512M;

1. Configure proper transaction isolation level:

1   -- For better performance if full SERIALIZABLE isolation not required
2   SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

InnoDB is the most feature-rich and robust storage engine available in MySQL and MariaDB. Unless you have a specific reason to use another engine, InnoDB should be your default choice.

file: Index file (all indexes)

When to Use MyISAM

While MyISAM is considered legacy, it may still be appropriate for:

• Read-intensive workloads with few or no updates
• Data warehousing applications with bulk loads and complex reporting
• Low concurrency environments where table-level locking is not an issue
• Legacy applications specifically designed for MyISAM
• Systems with limited memory where InnoDB's overhead is problematic

MariaDB's Aria: The Enhanced MyISAM Replacement

MariaDB includes the Aria storage engine, which is designed as an enhanced crash-safe replacement for MyISAM. Key differences include:

1. Crash Recovery

• Aria uses transaction logs to recover from crashes
• Interrupted operations can be recovered without data corruption

1. Better Caching

• Improved page caching mechanisms
• More efficient memory usage

1. Enhanced Concurrency

• Better handling of concurrent operations
• Improved performance under high read/write loads

1. Still Non-Transactional

• Like MyISAM, Aria does not support transactions
• No support for ROLLBACK operations or savepoints

MyISAM Best Practices

1. Enable delayed key writes for performance:

1   SET GLOBAL delay_key_write = ON;

1. Adjust the key buffer size for better index performance:

1   -- Set to 25-50% of available memory for MyISAM-focused servers
2   SET GLOBAL key_buffer_size = 256M;

1. Use REPAIR TABLE for maintenance:

1   REPAIR TABLE mytable;

1. Enable concurrent inserts:

1   SET GLOBAL concurrent_insert = 2;

1. Optimize tables regularly:

1   OPTIMIZE TABLE mytable;

MyISAM vs. InnoDB Comparison

| Feature | MyISAM | InnoDB |

|---------|--------|--------|

| Transactions | ❌ No | ✅ Yes |

| Foreign Keys | ❌ No | ✅ Yes |

| Locking Level | Table | Row |

| Crash Recovery | ❌ Limited | ✅ Robust |

| Full-Text Search | ✅ Yes | ✅ Yes (since 5.6) |

| COUNT(*) without WHERE | ✅ Instant | ❌ Full scan |

| Memory Usage | Lower | Higher |

| Disk Space | Less | More |

Important Note

MyISAM is considered legacy technology. Unless you have specific requirements that make MyISAM clearly superior for your use case, InnoDB is almost always the better choice. For MariaDB users, Aria provides a more crash-safe alternative to MyISAM while maintaining most of its characteristics.