Storage Engines: InnoDB, MyISAM, and Beyond - MySQL: Architecture, Scaling, and Production Deployment | OneNoughtOne

0/319

System Design HLDMySQL Deep Dive

MySQL: Architecture, Scaling, and Production Deployment

LevelAdvanced

Duration90 mins

TopicMySQL Deep Dive

1 / 5

Storage Engines: InnoDB, MyISAM, and Beyond

The Engine That Powers the Data

In the world of databases, MySQL holds a unique distinction: pluggable storage engines. Unlike PostgreSQL, Oracle, or SQL Server, which use a single integrated storage system, MySQL separates the SQL processing layer from the physical storage layer. This separation allows you to choose—or even mix—different storage engines within the same database, each optimized for different workloads.

This architectural decision, born from MySQL's modular design philosophy, has profound implications for system design. The choice of storage engine affects:

Transactional guarantees — ACID compliance or lack thereof
Concurrency behavior — Row-level versus table-level locking
Recovery capabilities — Crash recovery and point-in-time restore
Performance characteristics — Write throughput, read latency, memory usage
Feature availability — Foreign keys, full-text search, geospatial queries

Understanding MySQL's storage engines is not optional knowledge for system designers—it's fundamental to every decision you make about data architecture on MySQL.

What You Will Learn

By the end of this page, you will understand MySQL's storage engine architecture, master the internals of InnoDB (the default ACID-compliant engine), understand when MyISAM still has value, explore other specialized engines, and develop a framework for storage engine selection in production systems.

MySQL's Layered Architecture

To understand storage engines, you must first understand how MySQL processes queries. MySQL uses a layered architecture that separates concerns into distinct components:

The MySQL Server Architecture:

MySQL Server Layers

•Connection Layer — Handles client connections, authentication, and connection pooling. Each client connection spawns a thread (or uses a thread pool in MySQL Enterprise).
•Parser & Optimizer Layer — Parses SQL statements, performs semantic analysis, builds query execution plans, and optimizes queries using cost-based analysis. This layer is storage-engine-agnostic.
•Executor Layer — Executes the optimized query plan by making calls to the storage engine API. Handles sorting, grouping, and joins that span multiple tables.
•Storage Engine Layer — Manages physical data storage, retrieval, indexing, and transactions. This is where InnoDB, MyISAM, and other engines operate. Each table can use a different storage engine.

The Storage Engine API (Handler API):

The storage engine layer communicates with the executor through a well-defined API called the Handler API. This API abstracts storage operations into generic calls:

┌─────────────────────────────────────────────────────────────┐
│                    MySQL Server                              │
│  ┌─────────────────────────────────────────────────────┐    │
│  │        Connection Manager / Thread Pool              │    │
│  └─────────────────────────────────────────────────────┘    │
│                          │                                   │
│  ┌─────────────────────────────────────────────────────┐    │
│  │   SQL Parser → Query Optimizer → Query Executor      │    │
│  └─────────────────────────────────────────────────────┘    │
│                          │                                   │
│              ┌───────────┼───────────┐                       │
│              │    Handler API        │                       │
│              └───────────┼───────────┘                       │
│    ┌─────────────┬───────┴──────┬─────────────┐             │
│    │             │              │             │             │
│ ┌──┴──┐     ┌────┴───┐    ┌─────┴───┐   ┌────┴───┐        │
│ │InnoDB│     │MyISAM  │    │Memory   │   │Archive │        │
│ └──────┘     └────────┘    └─────────┘   └────────┘        │
└─────────────────────────────────────────────────────────────┘

The Handler API includes operations like:

handler::open() — Open a table
handler::index_read() — Read via index
handler::read_next() — Sequential scan
handler::write_row() — Insert a row
handler::update_row() — Update a row
handler::delete_row() — Delete a row

This abstraction means the optimizer doesn't need to know how data is stored—just that it can request operations and receive results.

Cross-Engine Joins

Because MySQL's executor handles joins at the server level, you can join tables using different storage engines. A query might join an InnoDB table (for transactional safety) with a Memory table (for blazing-fast lookup of reference data). However, cross-engine transactions are limited—a multi-table transaction only provides ACID guarantees if ALL participating tables use a transactional engine like InnoDB.

InnoDB: The Default Transactional Engine

InnoDB has been MySQL's default storage engine since version 5.5 (released 2010), and for good reason. It provides the ACID guarantees, row-level locking, and crash recovery that production systems require. Let's examine InnoDB's architecture in depth.

InnoDB Architecture Components:

InnoDB Internal Components
Component	Purpose	Key Details
Buffer Pool	In-memory cache for data and indexes	Configurable size (often 70-80% of RAM); uses LRU eviction; most critical performance setting
Redo Log (WAL)	Ensures durability before data pages are written	Sequential writes for performance; enables crash recovery; affected by innodb_flush_log_at_trx_commit
Undo Log	Stores old row versions for rollback and MVCC	Enables transactional rollback; provides read consistency without locks
Tablespace Files	Physical storage of data and indexes	Can be shared (ibdata1) or file-per-table (.ibd files); file-per-table preferred for manageability
Change Buffer	Caches secondary index changes	Reduces random I/O for non-unique secondary index updates
Adaptive Hash Index	Automatically creates in-memory hash indexes	Speeds up equality lookups on frequently accessed data; can be disabled
Doublewrite Buffer	Prevents torn page corruption	Writes pages to sequential area before final location; ensures atomicity of page writes

The Buffer Pool: InnoDB's Most Critical Component

The buffer pool is InnoDB's main memory area where it caches both data pages and index pages. Its size (innodb_buffer_pool_size) is the single most important InnoDB configuration parameter.

How the buffer pool works:

When InnoDB needs to read a row, it first checks if the page containing that row is in the buffer pool
If hit: Return data directly from memory (sub-millisecond latency)
If miss: Read page from disk into buffer pool, evict old pages if necessary (potential milliseconds)

A high buffer pool hit ratio (>99%) indicates most queries are served from memory. Low hit ratios indicate disk I/O bottlenecks.

-- Check buffer pool statistics
SHOW ENGINE INNODB STATUS\G

-- Key metrics to watch:
-- Buffer pool hit rate: Should be >99%
-- Pages read vs pages read ahead: Indicates I/O patterns
-- Modified db pages: Dirty pages pending flush

-- Check hit rate specifically
SELECT 
  (1 - (innodb_buffer_pool_reads / innodb_buffer_pool_read_requests)) * 100 AS hit_ratio
FROM (
  SELECT 
    VARIABLE_VALUE AS innodb_buffer_pool_reads 
  FROM performance_schema.global_status 
  WHERE VARIABLE_NAME = 'Innodb_buffer_pool_reads'
) r, (
  SELECT 
    VARIABLE_VALUE AS innodb_buffer_pool_read_requests 
  FROM performance_schema.global_status 
  WHERE VARIABLE_NAME = 'Innodb_buffer_pool_read_requests'
) rr;

Buffer Pool Sizing

Set innodb_buffer_pool_size to 70-80% of available memory on dedicated database servers. Too small, and you get excessive disk I/O. Too large, and the OS lacks memory for file system cache and may start swapping. Monitor with SHOW ENGINE INNODB STATUS and adjust based on hit ratio.

How InnoDB Achieves ACID Compliance

InnoDB's ACID compliance isn't magic—it's achieved through careful coordination of multiple components. Understanding this mechanism explains InnoDB's behavior and helps you tune it appropriately.

Atomicity: All or Nothing

InnoDB achieves atomicity through its undo log. Before modifying any row, InnoDB writes the old version to the undo log. If the transaction rolls back (explicitly or due to crash), InnoDB reads the undo log and restores the original values.

┌─────────────────────────────────────────────────────────────┐
│                    Write Operation Flow                      │
│                                                              │
│   1. Write old row version to Undo Log                       │
│   2. Modify data page in Buffer Pool (not yet durable)       │
│   3. Write change to Redo Log                                │
│   4. On COMMIT: Flush redo log (now durable!)                │
│   5. Later: Background thread flushes dirty pages to disk    │
│                                                              │
│   On ROLLBACK:                                               │
│   - Read undo log entries for this transaction               │
│   - Apply inverse operations to restore original state       │
│   - Mark undo log entries as reclaimable                     │
└─────────────────────────────────────────────────────────────┘

Consistency: Valid State Transitions

InnoDB enforces consistency through:

Foreign key constraints (checked at row modification time)
Unique constraints (maintained via index)
NOT NULL constraints (validated at insert/update)
CHECK constraints (MySQL 8.0+)
Transactional isolation (no dirty reads of uncommitted data)

Isolation: MVCC (Multi-Version Concurrency Control)

InnoDB implements MVCC to allow readers and writers to coexist without blocking each other. Here's how it works:

MVCC Implementation Details

•Hidden Columns: Each InnoDB row has hidden columns: DB_TRX_ID (transaction ID that last modified the row), DB_ROLL_PTR (pointer to undo log for previous version), and DB_ROW_ID (row ID if no primary key).
•Read View: When a transaction starts (or on first read for READ COMMITTED), InnoDB creates a 'read view'—a snapshot of currently active transaction IDs.
•Version Chain: The undo log forms a chain of row versions. A SELECT traverses this chain to find the version visible to its read view.
•No Read Locks: Readers don't acquire locks; they simply read the appropriate version. Writers proceed independently.
•Write Conflicts: If two transactions modify the same row, the second to commit waits for the first (or detects deadlock).

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
-- Session 1: Start a transaction and update a row
START TRANSACTION;
UPDATE users SET balance = 100 WHERE id = 1;
-- balance in Session 1's view: 100 (uncommitted)
 
-- Session 2: Read the same row (in a different connection)
SELECT balance FROM users WHERE id = 1;
-- With READ COMMITTED: Sees old value (before Session 1's update)
-- With REPEATABLE READ: Sees old value (consistent with session start)
 
-- Session 1: Commit
COMMIT;
 
-- Session 2: Read again
SELECT balance FROM users WHERE id = 1;
-- With READ COMMITTED: Now sees 100 (committed value)
-- With REPEATABLE READ: Still sees old value! (snapshot isolation)
 
-- This is MVCC: Session 2 reads without waiting for Session 1's lock.
-- Both transactions proceed concurrently without blocking.

Durability: Write-Ahead Logging

InnoDB guarantees durability through its redo log (also called the Write-Ahead Log or WAL). Before a transaction commits, its changes must be written to the redo log on disk. Even if the server crashes before dirty pages are flushed to tablespace files, the redo log contains all committed changes and recovery can replay them.

The critical configuration parameter is innodb_flush_log_at_trx_commit:

Durability vs Performance Trade-offs
Setting	Behavior	Durability	Performance
1 (default)	Flush and fsync log on every commit	Full ACID (survives crash)	Slowest (safest)
0	Write log to buffer, flush every second	Up to 1 second of data loss	Fastest (OS crash = data loss)
2	Flush log on commit, fsync every second	Survives mysqld crash, not OS crash	Medium (good compromise)

When to Relax Durability

Setting innodb_flush_log_at_trx_commit=2 is common in replicated environments. If the primary crashes, you fail over to a replica that has all committed transactions (assuming semi-synchronous replication). The slight durability relaxation on the primary is acceptable because the replica provides the durability guarantee.

InnoDB Indexing: Clustered Indexes and B+ Trees

InnoDB uses B+ Tree indexes for both primary and secondary indexes. However, InnoDB has a unique characteristic that profoundly affects schema design: clustered indexes.

The Clustered Index Concept:

In InnoDB, the primary key IS the table. The data rows are stored in primary key order within the B+ tree leaf nodes. This is why InnoDB tables are sometimes called clustered tables or index-organized tables.

┌─────────────────────────────────────────────────────────────┐
│              InnoDB Clustered Index (Primary Key)            │
│                                                              │
│    ┌───────────────────────────────────────────┐            │
│    │         B+ Tree Internal Nodes             │            │
│    │     (contain primary key ranges)           │            │
│    └────────────────────┬──────────────────────┘            │
│                         │                                    │
│    ┌────────────────────┴────────────────────┐              │
│    │              Leaf Nodes                  │              │
│    │   ┌────────┬────────┬────────┐          │              │
│    │   │ PK=1   │ PK=2   │ PK=3   │...       │              │
│    │   │ Row 1  │ Row 2  │ Row 3  │          │              │
│    │   │ (full  │ (full  │ (full  │          │              │
│    │   │ data)  │ data)  │ data)  │          │              │
│    │   └────────┴────────┴────────┘          │              │
│    └─────────────────────────────────────────┘              │
│                                                              │
│   * Leaf nodes contain the actual row data                  │
│   * Rows are physically ordered by primary key              │
│   * Range scans on primary key are extremely efficient      │
└─────────────────────────────────────────────────────────────┘

Implications of Clustered Indexing:

Clustered Index Effects

•Primary key lookups are O(log n) with single B+ tree traversal — No separate step to fetch the row; it's embedded in the index leaf.
•Primary key range scans are extremely efficient — Rows are physically contiguous, enabling sequential I/O.
•Primary key choice affects ALL indexes — Secondary indexes store the primary key as their 'pointer' to the row. Larger primary keys = larger secondary indexes.
•Avoid random primary keys (like UUIDv4) — Random insertions cause page splits and fragmentation. Use auto-increment or time-ordered UUIDs (UUIDv7).
•Secondary index lookups require two B+ tree traversals — First to find the primary key in the secondary index, then to find the row in the clustered index.

Secondary Index Structure:

Secondary indexes in InnoDB work differently than in heap-organized databases:

┌─────────────────────────────────────────────────────────────┐
│           Secondary Index (e.g., INDEX on email)             │
│                                                              │
│    ┌───────────────────────────────────────────┐            │
│    │         B+ Tree Internal Nodes             │            │
│    │     (contain indexed column values)        │            │
│    └────────────────────┬──────────────────────┘            │
│                         │                                    │
│    ┌────────────────────┴────────────────────┐              │
│    │              Leaf Nodes                  │              │
│    │   ┌──────────────┬──────────────┐       │              │
│    │   │ email='a@..' │ email='b@..' │...    │              │
│    │   │ → PK=42      │ → PK=17      │       │              │
│    │   └──────────────┴──────────────┘       │              │
│    └─────────────────────────────────────────┘              │
│                                                              │
│   * Leaf nodes contain indexed column + primary key         │
│   * Primary key is used to look up full row in clustered    │
│   * Larger primary keys make ALL secondary indexes larger   │
└─────────────────────────────────────────────────────────────┘

Covering Indexes:

If a query only needs columns that are in the secondary index (including the primary key), InnoDB can satisfy the query from the secondary index alone—no clustered index lookup needed. This is called a covering index or index-only scan.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
-- Table structure
CREATE TABLE orders (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    user_id BIGINT NOT NULL,
    status VARCHAR(20) NOT NULL,
    total DECIMAL(10,2) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_user_status (user_id, status)
);
 
-- This query uses a covering index (no clustered lookup needed)
SELECT user_id, status FROM orders WHERE user_id = 123;
-- EXPLAIN shows: Using index (meaning covering index)
 
-- This query requires clustered index lookup (needs 'total' column)
SELECT user_id, status, total FROM orders WHERE user_id = 123;
-- EXPLAIN shows: Uses idx_user_status, then looks up in clustered index
 
-- Optimize by adding 'total' to the index (trade-off: larger index)
CREATE INDEX idx_user_status_total ON orders(user_id, status, total);
-- Now the query can use a covering index

Primary Key Design Best Practices

For InnoDB tables: (1) Use auto-increment integers or time-ordered UUIDs (UUIDv7) for insert performance. (2) Keep primary keys small—every secondary index stores the PK. (3) Avoid wide composite primary keys. (4) Never use randomly-generated UUIDv4 as primary key—it destroys insert performance due to random page placement.

InnoDB Locking: Row-Level Locks and Deadlock Handling

InnoDB provides row-level locking rather than table-level locking, enabling high concurrency for write-heavy workloads. However, InnoDB's locking behavior is nuanced and requires careful understanding to avoid performance issues.

Lock Types in InnoDB:

InnoDB Lock Types
Lock Type	Abbreviation	Purpose	Compatibility
Shared Lock	S	Allow reading a row; acquired by SELECT ... FOR SHARE	Compatible with other S locks
Exclusive Lock	X	Allow modifying a row; acquired by UPDATE/DELETE/SELECT...FOR UPDATE	Incompatible with all other locks
Intention Shared	IS	Signals intent to acquire S locks on rows	Table-level; enables efficient lock checking
Intention Exclusive	IX	Signals intent to acquire X locks on rows	Table-level; IS/IX compatible with each other
Gap Lock		Lock gap between index records	Prevents phantom reads; key for REPEATABLE READ
Next-Key Lock		Record lock + gap lock on gap before record	Default for REPEATABLE READ isolation

Next-Key Locking: Preventing Phantoms

In REPEATABLE READ isolation (MySQL's default), InnoDB uses next-key locks to prevent phantom reads. A next-key lock locks both the index record and the gap before it.

-- Suppose we have users with id = 10, 20, 30

-- Transaction 1:
SELECT * FROM users WHERE id BETWEEN 15 AND 25 FOR UPDATE;
-- Locks: gap (10, 20), record 20, gap (20, 30)

-- Transaction 2:
INSERT INTO users (id, name) VALUES (18, 'New User');
-- BLOCKS! The gap (10, 20) is locked by Transaction 1

-- This prevents Transaction 1 from seeing 'phantom' rows
-- if it re-executes the same SELECT ... FOR UPDATE

Gap locks are only taken on indexed columns. If your WHERE clause doesn't use an index, InnoDB may lock entire table or large portions of the index.

Deadlock Detection and Handling:

InnoDB has an automatic deadlock detector that runs continuously. When a deadlock is detected:

InnoDB chooses a victim transaction (typically the one with fewer changes)
The victim is rolled back, releasing its locks
The victim receives an error: ERROR 1213: Deadlock found when trying to get lock
The other transaction(s) proceed

Minimizing Deadlocks:

Deadlock Prevention Strategies

•Access tables in consistent order — If transactions always lock table A before table B, cyclic waits are impossible.
•Keep transactions short — Less time holding locks means less chance of conflict.
•Use appropriate indexes — Queries without indexes lock more rows than necessary.
•Lock all required rows early — If you need to lock multiple rows, acquire them at transaction start with SELECT...FOR UPDATE.
•Use READ COMMITTED isolation when possible — Eliminates gap locks (but allows phantom reads).
•Retry on deadlock — Deadlocks are expected; applications should catch error 1213 and retry the transaction.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
-- View current InnoDB locks and waits (MySQL 8.0+)
SELECT * FROM performance_schema.data_locks;
SELECT * FROM performance_schema.data_lock_waits;
 
-- View InnoDB status including latest deadlock info
SHOW ENGINE INNODB STATUS\G
-- Look for "LATEST DETECTED DEADLOCK" section
 
-- Monitor lock wait timeouts
SHOW VARIABLES LIKE 'innodb_lock_wait_timeout';
-- Default is 50 seconds; transactions waiting longer are rolled back
 
-- Enable deadlock logging to error log
SET GLOBAL innodb_print_all_deadlocks = ON;

Lock Wait Timeouts Are Not Deadlocks

A lock wait timeout (error 1205) is different from a deadlock (error 1213). Lock wait timeout means a transaction waited too long for a lock held by another transaction that is still running. This often indicates a long-running transaction holding locks—investigate with SHOW PROCESSLIST and SHOW ENGINE INNODB STATUS.

MyISAM: The Legacy Storage Engine

MyISAM was MySQL's original default storage engine before InnoDB took over in MySQL 5.5. While rarely used for new applications, understanding MyISAM is important because:

You may encounter legacy systems using MyISAM
Some workloads genuinely benefit from MyISAM characteristics
Understanding MyISAM clarifies what InnoDB's complexity buys you

MyISAM Architecture:

MyISAM is dramatically simpler than InnoDB. Each table consists of three files:

MyISAM File Structure
File Extension	Contents	Notes
.frm	Table structure definition	Shared with all engines; contains column definitions
.MYD	Data file (My Data)	Contains actual row data; heap-organized (rows not sorted)
.MYI	Index file (My Index)	Contains all indexes; separate from data

Key MyISAM Characteristics:

MyISAM Advantages

•Smaller disk footprint — No transaction overhead, no undo logs, simpler format
•Full-text search (legacy) — MyISAM had full-text search before InnoDB; now InnoDB has it too
•COUNT(*) is O(1) — MyISAM maintains exact row count; no table scan needed
•Simple backup — Just copy the files (when table is locked)
•Good for read-heavy, append-only — No locking overhead for inserts at end of table (concurrent_insert)

MyISAM Disadvantages

•Table-level locking — A single write blocks all other reads and writes on the table
•No transactions — No COMMIT/ROLLBACK; partial updates persist on crash
•No foreign keys — Referential integrity must be enforced in application
•Crash-unsafe — Power loss can corrupt data requiring repair
•No MVCC — Readers block writers; writers block readers

Do Not Use MyISAM for New Applications

MyISAM is essentially deprecated. InnoDB matches or exceeds MyISAM's read performance in most cases, provides superior write concurrency, and adds ACID guarantees. The only legitimate remaining use cases are: (1) temporary tables for intermediate query results, (2) truly immutable append-only logging where atomicity doesn't matter. Even these are better served by Memory engine or InnoDB respectively in most cases.

Other MySQL Storage Engines

While InnoDB dominates production use, MySQL supports several other storage engines for specialized workloads:

Memory (HEAP) Engine:

Stores all data in RAM. Tables are lost on server restart but provide extremely fast access for temporary data.

Memory Engine Use Cases

•Lookup tables — Small reference data accessed frequently (country codes, status mappings)
•Session data — Ephemeral data that can be lost on restart
•Intermediate query results — Temp tables in complex queries (MySQL uses this internally)
•Caching layer — When you need SQL queries on cached data (though Redis is usually better)

Archive Engine:

Designed for storing large volumes of historical data with compression. Insert-only (no UPDATE/DELETE), but achieves high compression ratios (often 10:1 or better).

CSV Engine:

Stores data in comma-separated value format. Tables can be directly edited with text editors or imported into spreadsheets. Useful for data exchange but not production workloads.

Blackhole Engine:

Accepts writes but discards them. Used in replication setups to relay binlog events without storing data, or for benchmarking write performance.

NDB (MySQL Cluster):

Distributed, shared-nothing storage engine for MySQL Cluster. Provides synchronous replication and automatic partitioning across nodes. Complex to operate but enables true high-availability MySQL deployments.

Storage Engine Feature Comparison
Feature	InnoDB	MyISAM	Memory	Archive	NDB
ACID Transactions	✓	✗	✗	✗	✓
Row-Level Locking	✓	✗ (table)	✗ (table)	✗	✓
Foreign Keys	✓	✗	✗	✗	✓
Full-Text Search	✓	✓	✗	✗	✗
Crash Recovery	✓	✗	N/A	✗	✓
Compression	✓	✓ (myisampack)	✗	✓ (built-in)	✓
Cluster Support	Via replication	✗	✗	✗	✓ (native)

Engine Selection Rule

Default to InnoDB for all tables unless you have a specific, measured reason to use another engine. The only common exceptions: Memory engine for true temporary lookup data (not persistent), and Archive for write-once audit/logging data where you need high compression and never update or delete.

Migrating Between Storage Engines

Converting tables between storage engines is a common administrative task, especially when upgrading systems from MyISAM to InnoDB. There are several approaches with different trade-offs:

Method 1: ALTER TABLE (Simple but Slow)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
-- Convert a single table from MyISAM to InnoDB
ALTER TABLE users ENGINE = InnoDB;
 
-- This method:
-- 1. Creates a new empty InnoDB table with the same schema
-- 2. Copies all rows from the old table
-- 3. Drops the old table and renames the new one
-- 4. Rebuilds all indexes
 
-- Issues:
-- - Locks the table for the entire duration
-- - Very slow for large tables (hours for 100M+ rows)
-- - Requires 2x the table's disk space temporarily
-- - Progress is not visible
 
-- Convert all MyISAM tables in a database
-- (requires scripting; no single command available)

Method 2: pt-online-schema-change (Production-Safe)

Percona's pt-online-schema-change tool performs schema changes (including engine conversion) with minimal locking:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
# Install Percona Toolkit
apt-get install percona-toolkit
 
# Convert table to InnoDB with minimal downtime
pt-online-schema-change \
  --alter "ENGINE=InnoDB" \
  --execute \
  D=mydb,t=users
 
# How it works:
# 1. Creates a new table with desired schema
# 2. Adds triggers on original table to capture changes
# 3. Copies data in chunks (configurable chunk size)
# 4. Applies accumulated changes from triggers
# 5. Atomically swaps tables
# 6. Drops old table
 
# The original table remains readable/writable during migration
# Only brief lock at the final swap

Method 3: Dump and Reload (For Bulk Migration)

For migrating an entire database or server, dump and reload may be faster:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
# Dump the database, converting to InnoDB in the dump
mysqldump --default-storage-engine=InnoDB mydb > dump.sql
 
# Or edit the dump to replace ENGINE=MyISAM with ENGINE=InnoDB
sed -i 's/ENGINE=MyISAM/ENGINE=InnoDB/g' dump.sql
 
# Reload into a new database
mysql -e "CREATE DATABASE mydb_new"
mysql mydb_new < dump.sql
 
# Benefits:
# - Fast for full database migration
# - Can do on a replica, then promote
 
# Drawbacks:
# - Requires downtime
# - Needs disk space for dump file

Post-Migration Considerations

After converting to InnoDB: (1) Increase innodb_buffer_pool_size—InnoDB uses more memory. (2) Review and adjust innodb_log_file_size for write-heavy workloads. (3) Add foreign keys that were impossible on MyISAM. (4) Update backup procedures—InnoDB hot backups work differently than MyISAM file copies.

Summary: MySQL Storage Engines

We've covered MySQL's unique pluggable storage engine architecture in depth. Let's consolidate the key takeaways:

Key Takeaways

•MySQL separates SQL processing from storage — The Handler API allows different storage engines to coexist, each optimized for different workloads.
•InnoDB is the production default — ACID transactions, row-level locking, MVCC, crash recovery, and foreign keys make InnoDB suitable for virtually all use cases.
•InnoDB's buffer pool is the key tuning parameter — Size it to 70-80% of available memory on dedicated servers; monitor hit ratio.
•InnoDB uses clustered indexes — The primary key determines physical row order; secondary indexes point to the primary key, not row locations.
•Primary key design affects all indexes — Use small, auto-increment or time-ordered keys to optimize insert performance and index size.
•Row-level locking enables concurrency, but understand gap locks — REPEATABLE READ isolation uses next-key locks that can cause surprising lock waits.
•MyISAM is effectively deprecated — Table-level locking, no transactions, and crash vulnerability make it unsuitable for new applications.
•Specialized engines exist for specific needs — Memory for ephemeral data, Archive for compressed historical data, NDB for true shared-nothing clustering.

What's Next:

Now that we understand MySQL's storage architecture, we'll explore how MySQL achieves high availability and horizontal scalability through replication and clustering. In the next page, we'll examine MySQL's replication topologies, group replication, and clustering options that enable MySQL to scale beyond a single server.

Page Complete

You now have deep knowledge of MySQL's storage engine architecture, InnoDB's internals, and when (rarely) to consider alternatives. This understanding is essential for optimizing MySQL performance, designing efficient schemas, and diagnosing production issues.