Master InnoDB: Architecture, Indexes, Transactions & Performance Tuning

1. InnoDB Overview

InnoDB is MySQL's default transactional storage engine designed for high‑concurrency OLTP workloads. Key features include ACID‑compliant transactions, row‑level locking, MVCC snapshot reads, and foreign‑key support. Its design goals are high reliability, high concurrency, and suitability for frequent short transactions.

2. InnoDB Architecture

2.1 Memory Structures

Buffer Pool : core component caching data and index pages; uses an improved LRU algorithm; size controlled by innodb_buffer_pool_size (check with SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool%';).

Change Buffer : buffers secondary index modifications for non‑unique indexes, turning random I/O into sequential I/O.

Adaptive Hash Index : automatically builds hash indexes on hot B‑Tree pages, beneficial for equality queries.

Log Buffer : holds redo log entries before flushing; size set by innodb_log_buffer_size.

2.2 Disk Structures

Tablespaces : system tablespace (ibdata1), file‑per‑table tablespaces ( .ibd) enabled by innodb_file_per_table=ON, undo tablespace for rollback logs, and temporary tablespace for transient data.

Redo Log : ensures transaction durability; written cyclically and used for crash recovery (forward roll).

Undo Log : guarantees atomicity and implements MVCC by storing previous row versions.

2.3 Background Threads

Master Thread – flushes dirty pages, merges indexes, maintains buffer pool.

IO Threads – handle read/write requests.

Page Cleaner Thread – batch page flushing.

Purge Thread – removes obsolete undo log entries.

3. Storage Layout: Pages and Rows

Pages are the smallest disk unit (default 16 KB) and can be data, index, undo, or system pages.

Data pages contain Infimum and Supremum virtual records that define range boundaries.

Row formats: Antelope (Compact/Redundant) and Barracuda (Dynamic/Compressed, default Dynamic). Dynamic stores large BLOB/TEXT off‑page with pointers.

4. Indexes and Algorithms

Clustered Index : the table itself; leaf nodes store full rows; primary key defines physical order.

Secondary Index : leaf nodes store primary key values; queries require a “back‑lookup”.

B+Tree : non‑leaf nodes hold keys and child pointers; leaf nodes linked for range scans.

5. Transactions and Concurrency Control

5.1 ACID Implementation

Atomicity – Undo Log.

Consistency – enforced by ACID rules.

Isolation – locks plus MVCC.

Durability – Redo Log and Doublewrite Buffer.

5.2 Locking

Row locks: shared (S) and exclusive (X).

Intention locks coordinate table and row locks.

Gap locks prevent phantom reads.

5.3 MVCC

Hidden columns DB_TRX_ID and DB_ROLL_PTR track the creating transaction and undo pointer.

Read‑Committed: a new read view for each statement.

Repeatable‑Read: a single read view for the whole transaction.

6. Crash Recovery and Backup

Recovery uses Redo logs for forward roll and Undo logs for rollback.

Backup methods: logical backup with mysqldump (cross‑version, slower) and physical backup with Percona XtraBackup (hot backup, production‑ready).

7. Performance Tuning Highlights

Memory: set innodb_buffer_pool_size to 50‑80 % of RAM and consider multiple buffer pool instances.

Log: increase innodb_log_file_size to reduce checkpoint frequency; innodb_flush_log_at_trx_commit=1 for maximum safety.

I/O: use innodb_flush_method=O_DIRECT to avoid double buffering.

Monitoring: SHOW ENGINE INNODB STATUS\G and query information_schema.INNODB_TRX for lock and transaction info.

8. Practical Examples

Example 1 demonstrates repeatable‑read isolation where a second session updates a row but the first session continues to see the original value until it commits.

-- Session 1
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
BEGIN;
SELECT balance FROM account WHERE id=1;  -- reads 100

-- Session 2
BEGIN;
UPDATE account SET balance = balance - 50 WHERE id=1;
COMMIT;

-- Session 1 again
SELECT balance FROM account WHERE id=1;  -- still sees 100
COMMIT;

Example 2 shows how to inspect current transactions and locks using SELECT * FROM information_schema.INNODB_LOCKS;.

9. Summary

Entry‑level: understand buffer pool, redo/undo logs, B+Tree structure, and ACID basics.

Advanced: master MVCC, lock mechanisms, and isolation levels.

Hands‑on: use SHOW ENGINE INNODB STATUS and information_schema to diagnose issues.

Optimization: tune buffer pool, redo log size, and flush strategies for better performance.