Articles tagged with #InnoDB
A curated list of engineering series, deep dives, and notes related to #InnoDB.
InnoDB Undo Logs and Read Views: Constructing Point-In-Time Snapshots
Deep dive into MySQL InnoDB Undo Logs: Insert vs Update Undo Logs, Purge Thread mechanics, Read View generation, and why long-running transactions cause undo tablespace bloat.
InnoDB Locking Deep Dive: Record Locks, Gap Locks, and Next-Key Locks
Master MySQL InnoDB locking mechanics: Record locks, Gap locks, Next-Key locks, and the supremum pseudo-record to prevent phantom reads.
InnoDB Primary Clustered Indexes vs Secondary Index Lookups (The Double Lookup Cost)
Explore InnoDB clustered index architectures, secondary index lookup mechanics, double lookup costs, and why UUID v4 primary keys degrade database insertion performance.
MySQL Architecture: Server Layer vs Pluggable Storage Engines (InnoDB vs MyISAM)
Understand MySQL's two-tier architecture: the SQL Server Layer vs the Pluggable Storage Engine layer, handler API contracts, and InnoDB vs MyISAM comparisons.
Composite & Covering Indexes: Maximizing Index-Only Scans and Avoiding Table Lookups
Master multi-column composite indexes in MySQL: leftmost prefix rules, range predicate traps, Index Condition Pushdown (ICP), and covering indexes.
Multi-Version Concurrency Control (MVCC): How Non-Blocking Snapshot Reads Work
Learn how modern databases like MySQL InnoDB implement Multi-Version Concurrency Control (MVCC) to achieve non-blocking snapshot reads using DB_TRX_ID and roll pointers.
Two-Phase Locking (2PL) & Deadlock Resolution: Shared vs Exclusive Lock Mechanics
Understand Strict 2-Phase Locking (2PL), lock hierarchy (S, X, IS, IX), lock compatibility matrices, Wait-For Graph deadlock detection, and rollback cost strategies.
The InnoDB Buffer Pool: Dirty Pages, LRU Eviction, and LSN Checkpointing
Learn how MySQL InnoDB Buffer Pool caches 16KB data pages, evicts cold pages via midpoint LRU algorithms, and flushes dirty pages asynchronously.
Write-Ahead Logging (WAL) & ARIES Crash Recovery: How Databases Guarantee Durability
Learn how Write-Ahead Logging (WAL) and the ARIES algorithm guarantee zero data loss during power outages and system crashes.
Pages, Blocks, and Heap Files: How Database Storage Engines Layout Data on Disk
Explore how database engines organize table data on disk using 16KB Slotted Pages, slot offset arrays, tuple headers, and Record IDs.