This article explores the inner workings of InnoDB's architecture—including the Buffer Pool, Change Buffer, Adaptive Hash Index, and Log Buffer—detailing their designs, configuration parameters, performance impacts, and best‑practice recommendations for high‑throughput MySQL deployments.
This article explains MySQL InnoDB’s key buffering mechanisms—buffer pool, change buffer, log buffer, and doublewrite buffer—detailing their roles, internal structures, eviction policies, and how they improve performance and data durability while handling page reads, writes, and crash recovery.
This article provides a step‑by‑step walkthrough of MySQL’s execution pipeline—from the client connection and parsing, through optimization, execution, undo/redo logging, change buffer handling, and binlog recording—illustrated with diagrams and detailed explanations of each component.
This article explains InnoDB's in‑memory structures, detailing the Buffer Pool architecture, page classifications and their management via free, flush, and LRU lists, the role and workflow of the Change Buffer, and the purpose and configuration of the Log Buffer.
This article explains MySQL InnoDB's internal memory architecture, covering the buffer pool's composition and sizing, page classification and management via free, flush, and LRU lists, the change buffer for secondary index updates, log buffer behavior, and the role of the adaptive hash index.
Change Buffer in MySQL InnoDB (formerly insert buffer) caches modifications to secondary index pages when they are not in the buffer pool, using a B‑tree structure to batch‑apply inserts, delete‑marks, and deletes while avoiding index page splits, merges, and ensuring purge safety.
This article explains MySQL index fundamentals—including B‑tree, B+‑tree, hash, and ordered array structures—covers primary versus secondary indexes, describes index maintenance such as page splits and merges, examines the InnoDB change buffer, and discusses optimizer choices and cases where indexes become ineffective.
This article analyzes why using SET unique_checks=0 during large MySQL imports can bypass unique‑key checks when data is not cached in the InnoDB buffer pool, explains the underlying change‑buffer mechanism, demonstrates the behavior with sysbench tests, and offers a safe workaround.
This article provides a comprehensive overview of MySQL’s logging mechanisms—including Binlog, Undo log, Redo log, and Change Buffer—explaining their concepts, roles in replication and crash‑recovery, recording formats, flush timing, two‑phase commit, checkpoint handling, and how they interact during data modifications.
This article explains the differences between normal and unique indexes in MySQL, examines how they affect query and update performance, details InnoDB's change‑buffer mechanism, and provides practical guidance on when to prefer ordinary indexes for better write efficiency.
