Articles tagged with #MySQL
A curated list of engineering series, deep dives, and notes related to #MySQL.
Operating Production Databases: High Availability Replication & Connection Pooling
Learn how to operate production databases: MySQL binary logging formats (RBR/SBR), GTIDs, semi-synchronous replication, and HikariCP connection pooling.
MySQL vs PostgreSQL MVCC: Heap Tuple Versions vs Undo Log Segments
Compare MySQL InnoDB and PostgreSQL MVCC implementations: Undo log version chains vs in-page heap tuples, HOT optimizations, and VACUUM engines.
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.
The Volcano Execution Model & Cost-Based Optimizer: From SQL to EXPLAIN Plans
Explore the internal life cycle of a SQL query: parsing ASTs, algebraic rewrites, predicate pushdown, Volcano Iterator model mechanics, and Cost-Based Optimizer (CBO) statistics.
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.
Mastering Database Internals from First Principles: Series Introduction & Learning Roadmap
Discover what you will learn in this 20-part series on Database Internals. Build a transactional storage engine and master B+ Trees and MVCC.