Storage Engines and InnoDB Architecture

MySQL's pluggable storage engine architecture lets each table use an engine.

Concept

MySQL's pluggable storage engine architecture lets each table use an engine. InnoDB is the default and the right choice for almost all tables.

Row-level + MVCC Everything (default) No (needs repair) Legacy read-mostly tables Data lost on restart Temporary lookup data Compressed insert-only logs Replication relay tricks NDB (MySQL Cluster) Distributed, in-memory, telecom-grade

SHOW ENGINES;
SELECT table_name, engine FROM information_schema.tables WHERE table_schema = 'eh_company';
ALTER TABLE legacy_log ENGINE = InnoDB;   -- convert

Why It Matters

Understanding InnoDB internals explains performance tuning (buffer pool size), durability settings, crash recovery, and why primary key choice affects everything.

Syntax

CREATE TABLE t (...) ENGINE = InnoDB ROW_FORMAT = DYNAMIC;
SHOW ENGINE INNODB STATUS\G
SHOW VARIABLES LIKE 'innodb%';

Example

18.1 InnoDB architecture Buffer pool Caches data and index pages in RAM; the most important memory setting (innodb_buffer_pool_size) Change buffer

Buffers changes to non-unique secondary index pages not in memory Adaptive hash index

Automatic in-memory hash index for hot B-tree lookups Log buffer / redo log

Write-ahead log; ensures committed changes survive crashes Old row versions for rollback and MVCC snapshots Doublewrite buffer

Protects against torn (partially written) pages .ibd per table (default), system tablespace, undo, temporary, general tablespaces 18.2 Durability settings Write and fsync redo at each commit None (durable) Write at commit, fsync once per second

Up to ~1 s of commits lost on OS crash/power loss Write and fsync once per second Up to ~1 s lost even on mysqld crash

Keep 1 and sync_binlog = 1 for production data you cannot lose; relaxed settings are acceptable for disposable test/staging loads.

18.3 Crash recovery On restart after a crash, InnoDB replays the redo log (roll forward committed changes) and uses undo logs to roll back uncommitted transactions — automatically, usually in seconds to minutes.

18.4 Row formats and page size Default page size 16 KB; row format DYNAMIC (long VARCHAR/TEXT/BLOB values stored off-page with a 20-byte pointer).

COMPRESSED row format or transparent page compression reduce disk usage at a CPU cost.

Maximum index key length: 3072 bytes (DYNAMIC/COMPRESSED).

18.5 Purge and history list length Old row versions in undo logs are removed by purge threads once no transaction needs them. A long-running transaction (even an idle one left open) prevents purge, so undo grows and queries slow down:

18.6 MyISAM vs InnoDB summary Transactions / ROLLBACK Manual repair (REPAIR TABLE), possible data loss Table-level (writers block all readers) COUNT(*) without WHERE

Scans an index (can be slow on huge tables) Instant (stored count) Default for everything

flowchart TB
    subgraph Memory
        BP[Buffer pool<br/>data + index pages, LRU]
        CB[Change buffer<br/>deferred secondary index changes]
        AHI[Adaptive hash index]
        LB[Log buffer]
    end
    subgraph Disk
        SYS[(System tablespace ibdata1)]
        IBD[(File-per-table tablespaces *.ibd)]
        UNDO[(Undo tablespaces)]
        REDO[(Redo log files<br/>#innodb_redo)]
        DW[(Doublewrite buffer files)]
        TMP[(Temporary tablespaces)]
    end
    BP -->|dirty pages flushed by page cleaners| DW --> IBD
    LB -->|commit: write + fsync| REDO
    BP --- UNDO

SHOW VARIABLES LIKE 'innodb_flush_log_at_trx_commit';   -- 1 (default): flush redo on every commit — full ACID
SHOW VARIABLES LIKE 'sync_binlog';                      -- 1 (default): fsync binary log on every commit

SELECT name, count FROM information_schema.innodb_metrics WHERE name = 'trx_rseg_history_len';
SELECT trx_id, trx_started, trx_mysql_thread_id, trx_query
FROM information_schema.innodb_trx ORDER BY trx_started;   -- find old transactions

Expected Result

SELECT ENGINE, SUPPORT, TRANSACTIONS FROM information_schema.engines WHERE ENGINE IN ('InnoDB', 'MyISAM', 'MEMORY');

+--------+---------+--------------+
| ENGINE | SUPPORT | TRANSACTIONS |
+--------+---------+--------------+
| MEMORY | YES     | NO           |
| MyISAM | YES     | NO           |
| InnoDB | DEFAULT | YES          |
+--------+---------+--------------+

Common Mistakes

Mixing MyISAM tables into transactional logic.

Leaving the buffer pool at the 128 MB default on a dedicated server.

Setting innodb_flush_log_at_trx_commit = 0/2 in production without understanding the risk.

Idle open transactions blocking purge.

Best Practices

InnoDB for all tables; convert legacy MyISAM.

Size the buffer pool to hold the working set (often 50–75% of RAM on dedicated servers; innodb_dedicated_server = ON can auto-size).

Keep durable settings (1/1) for important data.

Monitor history list length and long transactions.

Interview Relevance

High: "InnoDB vs MyISAM?", "What is the buffer pool?", "Redo vs undo log?", "What is the doublewrite buffer?", "What does innodb_flush_log_at_trx_commit do?".

Practice Questions

Find all non-InnoDB tables on your server.

Explain what happens to committed and uncommitted transactions during crash recovery.

Explain why an idle transaction left open for hours can slow down the server.

Calculate a buffer pool size for a dedicated 32 GB database server and justify it.