0 views collected around this technical thread.
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.
A full‑table scan of a 200 GB InnoDB table on a MySQL server with 100 GB RAM does not exhaust server memory because MySQL streams rows to the client, uses a fixed net_buffer, and InnoDB’s optimized LRU algorithm limits buffer‑pool pressure, ensuring stable performance.
This comprehensive guide explains MySQL's underlying index structures (B‑tree, B+‑tree, hash), how execution plans are generated and interpreted, the mechanics of query execution, the role of locks and transaction isolation levels, the MVCC model, and the InnoDB buffer‑pool caching strategy.
This article explains how MySQL stores and manages data by introducing the InnoDB storage engine, its buffer pool, data pages, B+Tree and adaptive hash indexes, change buffer, undo/redo logs, binlog, and the server layer that parses, optimizes, and executes SQL statements.
Even when scanning tables with tens of millions of rows, MySQL avoids out‑of‑memory crashes by streaming data in small 16 KB net buffers, using socket buffers, and employing an improved LRU algorithm that isolates cold data in the buffer pool’s old generation.
This article provides a comprehensive technical guide on MySQL internals, covering the underlying data structures of indexes (B‑tree, B+‑tree, hash), how execution plans are generated and interpreted, the four transaction isolation levels, MVCC implementation, and the InnoDB buffer‑pool architecture.
This article explains how InnoDB’s buffer pool works, covering its purpose, configuration parameters, internal structures such as free, flush, and LRU lists, multi‑instance handling, adaptive hash indexing, and strategies for page eviction and performance optimization.
This article explains the core components of MySQL's InnoDB storage engine—including undo log, redo log, buffer pool, change buffer, and binlog—detailing their roles in data writing, crash recovery, MVCC, and master‑slave synchronization, and describing related flushing strategies and LRU management.
This article explains MySQL InnoDB’s linear and random prefetch mechanisms, the role of the innodb_read_ahead_threshold and innodb_random_read_ahead variables, and how the LRU buffer‑pool list manages hot and cold pages to improve overall query efficiency.
This article explains how MySQL's Buffer Pool caches data in memory, its default size and configuration, the internal structure of instances, chunks, control blocks and pages, and how it manages data loading, dirty pages, and flushing to disk for optimal query performance.
This article explains the limitations of MySQL's built‑in query cache, how the InnoDB buffer pool works and can be tuned, and introduces ReadySet as an external caching middleware to improve query performance after a major product upgrade.
This article provides a comprehensive overview of MySQL internals, covering index data structures such as B‑tree and B+‑tree, the differences between MyISAM and InnoDB indexes, SQL execution plan analysis, transaction isolation levels, lock mechanisms, MVCC implementation, and the InnoDB buffer‑pool caching process.
This article explains the complete execution process of a MySQL UPDATE statement, covering InnoDB page structures, tablespaces, buffer pool caching, redo/undo logs, binlog, and the two‑phase commit mechanism, helping readers understand how MySQL ensures data consistency and durability.
This article explains the architecture of TDSQL-C Serverless buffer pool, details how InnoDB buffer pool resize works, analyzes performance bottlenecks such as IO and mutex contention, and presents optimization techniques like chunk pre‑allocation, delayed release, and hash‑resize improvements that make serverless scaling more stable.
This article explains MySQL's four‑layer logical architecture, describes high‑performance read strategies, details data pages, buffer pool and LRU algorithm, and provides comprehensive guidance on index structures, optimization techniques, and best practices for improving query performance.
This article explains the MySQL Buffer Pool architecture, describing data pages, cache pages, the three page states, the various linked lists (Free, Flush, LRU), pre‑read mechanisms, hot‑cold separation, concurrency handling, and dynamic resizing using the chunk mechanism.
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.
This article provides a comprehensive technical walkthrough of MySQL‑8.0.22 InnoDB's buffer pool, detailing its physical and logical structures, page management algorithms, fast read/write optimizations, change buffer, adaptive hash index, and the complete page‑read call stack, complete with code excerpts and diagrams.
This article investigates a historical MySQL bug where dropping large tables with a sizable buffer pool and Adaptive Hash Index (AHI) can cause severe latency, presents test configurations for 5.7.29 and 8.0.28, compares AHI‑on/off results, shows stack traces, and explains the lazy‑delete fix introduced in 8.0.23.