InnoDB Internals

-- 1) Why InnoDB by default
-- • ACID transactions with crash recovery (no MyISAM-style index corruption)
-- • Row-level locking → much better concurrency than MyISAM's table locks
-- • Foreign keys enforced
-- • Clustered primary key index (data stored in PK order)
-- • MVCC: writers don't block readers
-- • Online DDL for many operations

-- 2) Confirm engine
SELECT engine, default_storage_engine FROM information_schema.engines
WHERE engine = 'InnoDB';
SHOW VARIABLES LIKE 'default_storage_engine';   -- should be InnoDB

-- 3) Architecture in one paragraph
-- The buffer pool caches data + indexes in memory.
-- Writes go to the buffer + the redo log (sequential append) for crash safety.
-- A background flush writes dirty pages to disk in batches.
-- The undo log holds old versions for MVCC and rollback.
-- The doublewrite buffer protects against partial page writes (default on).

-- 4) The buffer pool — your single most important setting
SHOW VARIABLES LIKE 'innodb_buffer_pool_size';
SELECT * FROM information_schema.innodb_buffer_pool_stats;

-- Rule of thumb: 60-80% of dedicated DB host RAM.
-- For 32 GB box dedicated to MySQL: innodb_buffer_pool_size = 24G.
-- Too small → disk I/O dominates; too big → OOMs.

-- 5) Clustered index — data IS the primary key
-- Rows are physically stored in PRIMARY KEY order.
-- Secondary indexes contain the PK value, not row pointers.
-- Implications:
--   • PK should be small, monotonic — bigint AUTO_INCREMENT or UUID v7
--   • Random PKs (UUID v4) → page splits + fragmentation under load
--   • Looking up by secondary index requires TWO lookups (idx → PK → row)
--   • Covering indexes (index contains all queried columns) skip the row lookup

-- 6) Choosing a primary key
CREATE TABLE users (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    email VARCHAR(255) NOT NULL UNIQUE,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB ROW_FORMAT=DYNAMIC;

-- AUTO_INCREMENT PK: monotonic, small, no random insert pessimisation.
-- UUIDs: prefer UUIDv7 (sortable) over v4 (random).

-- 7) Locking — InnoDB locks ROWS, not tables
-- Two main lock types:
--   • Shared (S):    read; multiple S can coexist
--   • Exclusive (X): write; blocks all others on same row
-- Plus intention locks at the table level (IS, IX) for coordination.

-- Gap locks + next-key locks prevent phantom reads at REPEATABLE READ.

-- Watch live locks:
SELECT * FROM performance_schema.data_locks;
SELECT * FROM performance_schema.data_lock_waits;

-- 8) Isolation levels
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;          -- MySQL default
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

-- REPEATABLE READ (RR) is stricter than Postgres' default:
--   • Snapshot at first read; subsequent reads see same data
--   • Gap locks prevent phantoms
--   • Less concurrency than READ COMMITTED

-- For OLTP that doesn't depend on RR's gap locks, READ COMMITTED often performs better.
SET GLOBAL transaction_isolation = 'READ-COMMITTED';

-- 9) MVCC + undo log
-- Readers see a consistent snapshot from the time the transaction started.
-- The undo log keeps old row versions until no transaction needs them.
-- Long-running transactions PREVENT undo cleanup → 'undo log too big' problems.

SELECT * FROM information_schema.innodb_trx;
-- Look for trx that started hours ago. Kill or wait; that's your bloat source.

-- 10) Foreign keys + cascading
CREATE TABLE orders (
    id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    user_id BIGINT UNSIGNED NOT NULL,
    total_cents BIGINT NOT NULL,
    CONSTRAINT fk_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- InnoDB enforces FKs; MyISAM didn't.
-- BUT: foreign keys can hurt write throughput at scale; some shops disable for sharded designs.

-- 11) Crash recovery
-- innodb_flush_log_at_trx_commit:
--   1  ACID compliant — fsync on commit (default; slowest)
--   2  fsync once per second — may lose last second on power loss
--   0  flush once per second — same risk as 2 + slightly less write throughput
-- Most prod systems keep 1.

SHOW VARIABLES LIKE 'innodb_flush_log_at_trx_commit';

-- 12) Online DDL
ALTER TABLE orders ADD COLUMN paid_at TIMESTAMP NULL, ALGORITHM=INPLACE, LOCK=NONE;
ALTER TABLE orders ADD INDEX idx_user_created (user_id, created_at), ALGORITHM=INPLACE;
-- InnoDB supports many DDL ops without blocking writes. Check support before assuming.

-- For bigger / less-supported changes use pt-online-schema-change or gh-ost.

-- 13) Row formats
CREATE TABLE big_text (id INT PRIMARY KEY, body TEXT) ENGINE=InnoDB ROW_FORMAT=DYNAMIC;
-- ROW_FORMAT options:
--   COMPACT      — older; small overhead per row
--   DYNAMIC      — default; large columns stored off-page
--   COMPRESSED   — page-level zlib compression; CPU vs disk trade-off
--   REDUNDANT    — legacy

-- 14) Useful monitoring queries
SHOW ENGINE INNODB STATUS;                                -- TONS of info on locks, txns, deadlocks
SELECT * FROM performance_schema.events_waits_summary_by_instance ORDER BY sum_timer_wait DESC LIMIT 10;
SELECT * FROM information_schema.innodb_metrics;

-- 15) Critical settings to review
--   innodb_buffer_pool_size               60-80% of RAM
--   innodb_log_file_size                  redo log size; bigger = better write throughput, slower recovery
--   innodb_flush_method = O_DIRECT        avoid double-buffering with OS cache
--   innodb_file_per_table = 1             one file per table (default)
--   innodb_io_capacity                    tune to disk IOPS; SSD: 2000+, NVMe: 10000+
--   innodb_thread_concurrency             0 (default) unless you've measured otherwise
--   transaction_isolation = READ-COMMITTED    consider for OLTP
--   innodb_strict_mode = ON                 reject incorrect storage syntax

-- 16) Deadlocks happen — design for retry
SHOW ENGINE INNODB STATUS;       -- look for 'LATEST DETECTED DEADLOCK'

-- App code:
--   • Wrap transactions in retry-on-deadlock logic (max 3 attempts)
--   • Touch rows in consistent order across transactions
--   • Keep transactions short
--   • SELECT ... FOR UPDATE only when you must (acquires X locks)

-- 17) Common bugs
-- • Default REPEATABLE READ blocking writers via gap locks unexpectedly → READ COMMITTED
-- • UUID v4 PK in InnoDB → page splits + bloat at scale; use auto-inc or UUID v7
-- • Buffer pool too small → 100% disk reads; cache hit ratio < 95% is a tuning red flag
-- • Long-running transaction → undo log bloat; check innodb_trx
-- • Foreign keys on a sharded design — at scale, app-level integrity may scale better
-- • Forgetting innodb_flush_log_at_trx_commit=1 in financial systems → lost commits on power loss
-- • COUNT(*) on huge InnoDB table → slow; cache or use approximate counts
-- • ALTER TABLE with COPY algorithm on a busy table → outage; check ALGORITHM=INPLACE
-- • Mixing engines in one database — replication, backups, and ops complexity

-- InnoDB: row-level locking, MVCC, foreign keys, crash-safe.
-- Each table is a B+tree clustered by primary key.
SHOW ENGINE INNODB STATUS\G