Tagged articles
Buffer Pool
86 articles · Page 1 of 1

ITPUBOct 1, 2026 · Databases
The article explains why a MySQL SELECT * query scanning 20 million rows won't cause server-side OOM due to its streaming protocol using a 16KB net_buffer, but warns that full table scans can pollute the Buffer Pool, though InnoDB's midpoint insertion strategy and innodb_old_blocks_time (default 1s) protect hot data from eviction.
Buffer PoolInnoDBInterview
0 likes · 13 min read

This article explains the 20-million-row rule of thumb for MySQL InnoDB tables by analyzing B+ tree index structure, 16KB page size, and fan-out calculations, showing how row size and tree depth affect query performance.
B+TreeBuffer PoolIndex
0 likes · 11 min read

This comprehensive guide covers MySQL 9.5 performance optimization across table design, indexing strategies, query analysis with EXPLAIN, JOIN optimization, pagination techniques, system-level InnoDB tuning, and monitoring using Performance Schema and slow query logs to achieve dramatic query speed improvements.
Buffer PoolEXPLAINInnoDB
0 likes · 30 min read

This guide walks MySQL operators through a complete slow‑query troubleshooting workflow—starting with enabling and analyzing the slow‑query log, using pt‑query‑digest and EXPLAIN to pinpoint index, SQL, schema, configuration or hardware bottlenecks, and then applying concrete optimizations such as proper indexing, cursor pagination, JOIN tuning, and server‑level parameter tweaks.
Buffer PoolEXPLAINIndex Optimization
0 likes · 33 min read

The article explains why InnoDB must cache disk pages in a Buffer Pool, details the pool's internal structure, eviction policies, dirty‑page flushing mechanisms, multi‑instance and chunk sizing for large workloads, and how to monitor its health with SHOW ENGINE INNODB STATUS.
Buffer PoolChunkInnoDB
0 likes · 13 min read

This guide walks through MySQL performance troubleshooting—from enabling and analyzing slow‑query logs with pt‑query‑digest, interpreting EXPLAIN plans, designing covering and composite indexes, tuning InnoDB buffer pool and connection settings, to best‑practice recommendations and real‑world case validation.
Buffer PoolEXPLAINIndex Optimization
0 likes · 27 min read

The article analyzes why a MySQL LIMIT OFFSET query on a 9.5 million‑row table takes 16 seconds, demonstrates how moving the filter into a sub‑query that returns only primary‑key IDs and joining back reduces execution to 0.35 seconds, and validates the theory by measuring InnoDB buffer‑pool page accesses.
Buffer PoolInnoDBLIMIT OFFSET
0 likes · 9 min read

This article walks through ten essential MySQL InnoDB interview questions, covering why InnoDB uses B+ trees, MVCC isolation levels, two‑phase commit, gap locks, buffer‑pool LRU design, undo‑log mechanics, binlog formats, lock‑upgrade behavior, double‑write buffering, and the differences between clustered and non‑clustered indexes.
2PCB+TreeBinlog Formats
0 likes · 17 min read

This comprehensive guide walks you through MySQL InnoDB buffer pool optimization—from assessing current settings and calculating optimal sizes for 32 GB to 256 GB servers, to configuring instances, enabling pre‑warming, tuning dirty‑page flushing, monitoring key metrics, and troubleshooting common issues—to achieve up to a 300 % throughput increase in production environments.
Buffer PoolInnoDBMySQL
0 likes · 33 min read

This article explains the complete lifecycle of a MySQL UPDATE—from the initial log buffer, through redo and undo logs, buffer‑pool dirty pages, checkpoint thresholds, binary logging, and the innodb_flush_log_at_trx_commit setting—highlighting how InnoDB guarantees durability, consistency, and crash recovery.
Buffer PoolDatabase InternalsInnoDB
0 likes · 8 min read

Even when scanning a 200 GB InnoDB table on a server with only 100 GB of RAM, MySQL does not consume all memory because it streams results using a limited net_buffer, employs socket send buffers, and InnoDB’s optimized LRU algorithm manages the buffer pool to prevent memory blow‑up.
Buffer PoolInnoDBLRU
0 likes · 12 min read

This article breaks down MySQL's three core log types—undo log, redo log, and binlog—explaining how they support transaction atomicity, durability, MVCC, crash‑safe recovery, write‑ahead logging, and replication, while also covering buffer pool behavior and flush policies.
Buffer PoolMySQLReplication
0 likes · 24 min read

The article explains why scanning a 200 GB InnoDB table on a MySQL server with only 100 GB of memory does not cause an out‑of‑memory crash, detailing MySQL's row‑by‑row net buffer transmission, InnoDB buffer‑pool behavior, and the improved LRU algorithm that limits memory impact.
Buffer PoolInnoDBLRU
0 likes · 10 min read

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.
Buffer PoolInnoDBLRU
0 likes · 10 min read

This article explains MySQL’s internal architecture, covering how data pages, B+‑tree and hash indexes, the Buffer Pool, Adaptive Hash Index, Change Buffer, Undo/Redo logs, the InnoDB storage engine, and the server layer work together to provide fast, reliable CRUD operations and support replication.
Buffer PoolDatabase ArchitectureInnoDB
0 likes · 14 min read

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.
Buffer PoolMVCCMySQL
0 likes · 19 min read

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.
Buffer PoolInnoDBMySQL
0 likes · 13 min read

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.
Buffer PoolLRUMySQL
0 likes · 5 min read

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.
Buffer PoolMVCCMySQL
0 likes · 22 min read

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.
Buffer PoolInnoDBMySQL
0 likes · 10 min read

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.
Buffer PoolDatabase InternalsInnoDB
0 likes · 11 min read

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.
Buffer PoolInnoDBLRU
0 likes · 6 min read

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.
Buffer PoolDatabase MiddlewareInnoDB
0 likes · 5 min read

This article explores why MySQL queries can become sluggish beyond just missing indexes, covering the full query execution flow, profiling tools, index pitfalls, connection limits, buffer pool sizing, and practical configuration tips to dramatically improve performance.
Buffer PoolIndex OptimizationMySQL
0 likes · 15 min read

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.
Buffer PoolMVCCexecution plan
0 likes · 19 min read

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.
Buffer PoolInnoDBMySQL
0 likes · 21 min read


ITPUBDec 10, 2023 · Databases
The article investigates whether the long‑standing rule of avoiding MySQL tables larger than 20 million rows still holds on modern SSD‑backed instances by creating tables of varying sizes, measuring query latency under different InnoDB buffer pool configurations, and analyzing the impact of caching and index usage.
Buffer PoolInnoDBLarge Tables
0 likes · 11 min read

This article explains MySQL's transaction implementation, covering write‑ahead logging, transaction IDs, multi‑version concurrency control, buffer pool management, and the roles of redo and undo logs in ensuring durability and atomicity.
Buffer PoolMVCCMySQL
0 likes · 27 min read

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.
Buffer PoolMySQLTDSQL-C
0 likes · 11 min read


政采云技术Jun 13, 2023 · Databases
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.
Buffer PoolDatabase ArchitectureLRU algorithm
0 likes · 14 min read

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.
Buffer PoolChunkInnoDB
0 likes · 18 min read


政采云技术Mar 14, 2023 · Databases
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.
Buffer PoolChange BufferInnoDB
0 likes · 11 min read


政采云技术Mar 14, 2023 · Databases
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.
Buffer PoolChange BufferInnoDB
0 likes · 11 min read

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.
Buffer PoolCachingDatabase Performance
0 likes · 47 min read


ITPUBNov 15, 2022 · Databases
This article walks through the complete lifecycle of a MySQL UPDATE statement, covering InnoDB page structure, tablespaces, the buffer pool, redo and undo logs, binlog, and the two‑phase commit process, illustrating each step with examples and command outputs.
Buffer PoolInnoDBMySQL
0 likes · 19 min read

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.
Adaptive Hash IndexBuffer PoolMySQL
0 likes · 24 min read

This article provides a comprehensive technical overview of MySQL's underlying index structures, SQL execution plan analysis, transaction isolation levels, MVCC concurrency control, and the InnoDB buffer pool mechanism, illustrating each concept with diagrams and practical SQL examples.
Buffer PoolMVCCMySQL
0 likes · 17 min read

This article explains the common reasons MySQL queries become sluggish—beyond missing indexes—including connection limits, buffer pool size, and query cache, and provides practical solutions such as profiling, adjusting connection pools, tuning InnoDB settings, and using force index.
Buffer PoolMySQLconnection pool
0 likes · 16 min read

This article demystifies MySQL’s processing pipeline by tracing a simple UPDATE statement through the application, service, and storage engine layers, detailing connection handling, SQL parsing, optimization, execution, buffer pool management, and the roles of undo and redo logs in ensuring data integrity.
Buffer PoolDatabase ArchitectureInnoDB
0 likes · 18 min read

This article explains why MySQL queries can become sluggish, covering the full query execution flow, profiling techniques, index pitfalls, connection‑pool limits, buffer‑pool sizing, and additional performance tricks, while providing concrete commands and code examples for each optimization step.
Buffer PoolIndex OptimizationMySQL
0 likes · 16 min read

The article explains why a MySQL query with a large LIMIT offset becomes extremely slow, demonstrates the underlying I/O behavior, and shows how rewriting the query with a sub‑query that fetches primary keys first can cut execution time from seconds to milliseconds while also reducing buffer‑pool pollution.
Buffer PoolInnoDBLIMIT
0 likes · 9 min read

This article investigates how many rows should be inserted per batch in MySQL by measuring the impact of packet size limits, buffer pool usage, insert buffers, transaction handling and index structures, and it provides practical recommendations based on tests with millions of rows.
Buffer PoolMySQLbatch insert
0 likes · 16 min read

This article explains the differences between buffers and caches, explores MySQL’s buffer pool architecture, details write‑through/write‑back strategies, and reviews key InnoDB parameters such as innodb_flush_log_at_trx_commit and innodb_flush_method for optimizing data durability and performance.
Buffer PoolCacheInnoDB
0 likes · 12 min read

This article explains MySQL's layered design, detailing how the Server and InnoDB storage engine interact, the role of the Buffer Pool in caching data, and the functions of redo log, undo log, and binlog in ensuring durability, recovery, and replication.
Buffer PoolDatabase ArchitectureInnoDB
0 likes · 14 min read

This article explains in detail what happens inside MySQL when an UPDATE statement is executed, covering the execution flow, the roles of undo log, redo log, and binlog, buffer pool mechanics, two‑phase commit, group commit, and practical optimization tips.
Buffer PoolMySQLbinlog
0 likes · 40 min read

The article explains how a pagination query on a 9.5‑million‑row MySQL table using a large OFFSET can take seconds, demonstrates an optimization that moves the condition into a subquery to fetch only primary keys before joining, and validates the speed gain with buffer‑pool measurements.
Buffer PoolIndexMySQL
0 likes · 10 min read

This article analyzes MySQL batch insertion by examining field size, max_allowed_packet limits, transaction overhead, buffer pool usage, and empirical tests with varying batch sizes, ultimately recommending a batch size around half of the max_allowed_packet for optimal throughput.
Buffer PoolMySQLbatch insert
0 likes · 14 min read

This article explains why MySQL queries can become sluggish beyond missing indexes, covering the full query execution flow, profiling tools, index pitfalls, connection‑pool limits, InnoDB buffer‑pool sizing, and practical steps to diagnose and accelerate database performance.
Buffer PoolMySQLconnection pool
0 likes · 16 min read


政采云技术Mar 29, 2022 · Databases
This article evaluates single‑threaded bulk‑insert performance in MySQL by testing various batch sizes, analyzes memory usage and transaction merging, and provides practical recommendations for efficiently generating millions of records.
Buffer PoolMySQLbulk insert
0 likes · 11 min read

Using a large OFFSET in MySQL's LIMIT clause forces the server to scan hundreds of thousands of index and data pages, causing excessive random I/O, whereas rewriting the query with a sub‑select and join reduces page accesses dramatically, as demonstrated through buffer‑pool measurements.
Buffer PoolIndexLIMIT
0 likes · 8 min read

The article demonstrates that MySQL queries using LIMIT with a large offset cause heavy random I/O by scanning many rows, and shows how rewriting the query with a sub‑query that selects only primary keys dramatically reduces execution time from seconds to milliseconds.
Buffer PoolLIMITMySQL
0 likes · 8 min read

This article analyzes why a MySQL query with a large LIMIT offset can take seconds to run, demonstrates the underlying index and buffer‑pool behavior, and shows how rewriting the query with a sub‑select of primary keys reduces execution time from over 16 seconds to under half a second.
Buffer PoolIndexLIMIT
0 likes · 9 min read

Even when scanning a 200 GB InnoDB table on a server with only 100 GB RAM, MySQL streams results using a small net buffer and an optimized InnoDB buffer‑pool LRU, so the server’s memory never blows up, though I/O load remains high.
Buffer PoolInnoDBLRU algorithm
0 likes · 11 min read

The article explains why large‑offset LIMIT queries on a MySQL table with millions of rows are slow, demonstrates how rewriting the query to use a subquery that first selects primary‑key IDs dramatically reduces execution time, and validates the improvement by measuring buffer‑pool page usage.
Buffer PoolDatabase PerformanceMySQL
0 likes · 9 min read

This article explains why a MySQL query that scans a 100‑gigabyte table and returns millions of rows does not exhaust server memory, describing the net_buffer mechanism, socket send buffer behavior, InnoDB buffer‑pool management, and the improved LRU algorithm used to keep large scans from degrading overall performance.
Buffer PoolInnoDBLRU
0 likes · 8 min read

Full table scans on large InnoDB tables do not exhaust MySQL server memory because results are streamed in small net buffers, while InnoDB’s optimized LRU algorithm and buffer pool management ensure that massive scans minimally affect buffer pool hit rates and overall system performance.
Buffer PoolInnoDBLRU
0 likes · 11 min read

The article explains how using a large OFFSET in MySQL pagination forces the server to read and discard many rows, causing severe performance degradation, and demonstrates that rewriting the query to first fetch primary‑key IDs via a subquery dramatically reduces I/O and execution time, with buffer‑pool experiments confirming the improvement.
Buffer PoolMySQLPagination
0 likes · 9 min read

This article explains how MySQL handles massive full‑table scans without blowing up server memory, detailing the role of net_buffer, socket send buffers, the "edge‑compute‑and‑send" model, and InnoDB's optimized LRU algorithm that protects the buffer pool during large scans.
Buffer PoolInnoDBLRU
0 likes · 11 min read

Using LIMIT with a large offset in MySQL can cause severe performance degradation due to excessive random I/O, but by rewriting the query to first fetch primary keys in a subquery and then joining, execution time can drop from seconds to milliseconds, as demonstrated with real data.
Buffer PoolInnoDBLIMIT
0 likes · 10 min read

The article explains why using LIMIT with a large OFFSET in MySQL causes severe performance degradation, demonstrates the issue with a 5‑million‑row table, and shows how rewriting the query as a join dramatically reduces I/O and execution time while also discussing buffer‑pool effects.
Buffer PoolIndexLIMIT
0 likes · 8 min read

The article demonstrates how a large OFFSET in a MySQL LIMIT query forces MySQL to scan millions of index rows and data pages, causing severe slowdown, and shows that rewriting the query with an inner‑join sub‑select dramatically reduces I/O and buffer‑pool usage, confirming the performance difference through experiments.
Buffer PoolInnoDBLIMIT
0 likes · 8 min read


21CTOAug 16, 2021 · Databases
This article explains why MySQL queries using LIMIT with a large offset become extremely slow, demonstrates the inefficiency through a 950‑million‑row table example, and shows how rewriting the query with a sub‑select join reduces execution time from seconds to milliseconds while preserving results.
Buffer PoolInnoDBLIMIT OFFSET
0 likes · 9 min read


ITPUBAug 13, 2021 · Databases
When a MySQL query uses LIMIT with a large offset on a table of millions of rows, the database must scan hundreds of thousands of index entries and corresponding clustered rows, causing massive random I/O; rewriting the query with a sub‑query join reduces I/O dramatically, cutting execution time from over a minute to under a second while also preventing buffer‑pool pollution.
Buffer PoolInnoDBLIMIT
0 likes · 9 min read

This article conducts a hands‑on experiment to demonstrate how a transaction that acquires many InnoDB locks consumes buffer‑pool memory, leading to the "total number of locks exceeds the lock table size" error when buffer‑pool usage approaches its theoretical 75% threshold.
Buffer PoolInnoDB LocksMySQL
0 likes · 4 min read

This article provides a comprehensive overview of InnoDB architecture, covering its memory structures, background threads, and key features like checkpoint mechanisms, double write, and neighbor page flushing, essential for understanding MySQL's default storage engine.
Background ThreadsBuffer PoolCheckpoint Mechanism
0 likes · 12 min read

This article provides a comprehensive overview of MySQL InnoDB's Buffer Pool, explaining its memory layout, data and cache pages, free, flush, and LRU lists, hot‑cold separation, dynamic resizing with chunk mechanisms, and practical configuration tips for production environments.
Buffer PoolChunkDatabase Performance
0 likes · 24 min read

This article explains how using a large LIMIT offset on a 9.5‑million‑row MySQL table causes massive I/O and slow queries, and demonstrates a sub‑query‑based rewrite that fetches only primary keys before joining, dramatically reducing execution time while also showing buffer‑pool effects.
Buffer PoolIndexLIMIT OFFSET
0 likes · 8 min read

This article demonstrates how to dramatically improve MySQL LIMIT offset query performance by moving the filter into a subquery that selects only primary keys, then joining to retrieve full rows, and validates the approach with buffer pool statistics and execution time measurements.
Buffer PoolLIMIT OFFSETMySQL
0 likes · 9 min read

The article demonstrates how moving the LIMIT‑OFFSET condition into a subquery that selects only primary‑key IDs and then joining back to the main table can cut execution time from over 16 seconds to a few hundred milliseconds by avoiding massive index and data‑page scans.
Buffer PoolIndexLIMIT
0 likes · 8 min read

The article repeats a performance test on a 140 GB MySQL 8 database (innodb_buffer_pool_size=140G) using various innodb_buffer_pool_instances values, showing that increasing the instances improves throughput up to 64 instances, after which no further gains are observed.
Buffer PoolInnoDBMySQL
0 likes · 5 min read

The article explains why using LIMIT with a large offset in MySQL causes severe performance degradation, demonstrates the problem with real data, and shows how rewriting the query with a sub‑query that selects only primary keys dramatically reduces execution time by avoiding massive random I/O and buffer‑pool pollution.
Buffer PoolIndexLIMIT
0 likes · 8 min read

This article explains why large OFFSET values in MySQL LIMIT queries cause severe performance degradation and demonstrates how rewriting the query to fetch primary keys first and then joining reduces execution time from seconds to milliseconds, supported by buffer‑pool analysis and practical test results.
Buffer PoolIndexLIMIT
0 likes · 8 min read

This article examines why a MySQL query with a large OFFSET is extremely slow, demonstrates the performance gap with a join‑based rewrite, and validates the findings by comparing InnoDB buffer‑pool page usage, highlighting how unnecessary random I/O pollutes the cache.
Buffer PoolIndexLIMIT
0 likes · 8 min read

This article provides a comprehensive overview of MySQL InnoDB's internal architecture, covering its memory and disk structures, buffer pool, change buffer, adaptive hash index, log buffer, various tablespace types, page layout, undo and redo logs, double‑write buffer, transaction isolation levels, and how the engine satisfies ACID properties.
ACIDBuffer PoolInnoDB
0 likes · 32 min read

This article explains the theory behind InnoDB's log management, details the architecture and dynamic sizing of the Buffer Pool, describes its internal data structures and multi‑instance implementation, and provides annotated source code snippets to illustrate how MySQL allocates and manages buffer pages.
Buffer PoolDatabase InternalsInnoDB
0 likes · 15 min read

This article demonstrates how rewriting a MySQL LIMIT OFFSET query to first fetch primary keys in a sub‑query and then join the main table can cut execution time from over 16 seconds to under one second by avoiding massive random I/O and buffer‑pool pollution.
Buffer PoolIndex ScanLIMIT OFFSET
0 likes · 7 min read

This article explains the three InnoDB Buffer Pool page types—Free, Clean, and Dirty—their associated LRU, Flush, and Free linked lists, and how MySQL uses configurable parameters to balance memory usage, query speed, and data safety during flushing and checkpoint operations.
Buffer PoolInnoDBLRU
0 likes · 13 min read

This article explains how using a large OFFSET in a MySQL LIMIT clause forces the server to scan hundreds of thousands of index and data pages, causing massive random I/O, and demonstrates a faster rewrite with an inner join that dramatically reduces buffer‑pool usage and execution time.
Buffer PoolIndexInnoDB
0 likes · 7 min read

The article analyzes a recurring MySQL alarm where over 200 SQL statements are killed per minute due to buffer‑pool latch contention and a near‑90% dirty‑page ratio, then demonstrates how adjusting InnoDB parameters and doubling the buffer‑pool size eliminated the kills and restored performance.
Buffer PoolDirty PagesMySQL
0 likes · 7 min read

This article analyzes the instability of Alibaba's test environment container provisioning, identifies root causes, and presents a comprehensive solution—including automatic container replacement, a buffer pool, and resource‑pool rationalization—that raised the container success rate to 99.9% and stabilized performance.
Buffer PoolOperationsTest Environment
0 likes · 9 min read

The article analyzes why dropping large InnoDB tables in MySQL can drastically degrade overall database performance due to buffer‑pool mutex and file‑system I/O bottlenecks, and presents both operational work‑arounds and source‑code modifications to alleviate the issue.
Buffer PoolDROP TABLEIO
0 likes · 10 min read


ITPUBJun 23, 2017 · Databases
This article provides an in‑depth technical walkthrough of MySQL InnoDB's Buffer Pool, covering its core data structures, instance layout, LRU and Flush list management, memory allocation strategies, read‑ahead/write‑ahead mechanisms, double‑write buffering, and the specialized threads that keep the pool efficient.
Buffer PoolDatabase InternalsInnoDB
0 likes · 39 min read

This article explains how MySQL 5.7 automatically saves and restores InnoDB buffer‑pool pages, how to tune the innodb_buffer_pool_dump_pct setting, monitor load progress, and handle multi‑node deployments or crashes to achieve faster, more predictable startup performance.
Buffer PoolConfigurationInnoDB
0 likes · 6 min read

This article explains InnoDB's file I/O interfaces, asynchronous and synchronous read/write mechanisms, background IO threads, AIO request handling, concurrency controls, prefetch strategies, log write padding, and the evolution of buffer pool initialization, chain management, and page eviction in MySQL 5.7.
Buffer PoolIOInnoDB
0 likes · 19 min read

This article explains MySQL 5.7 InnoDB's traditional compressed storage format, system page structures, external storage pages, encrypted and R‑TREE pages, the IO subsystem including asynchronous AIO handling and read‑ahead strategies, as well as buffer pool initialization, management, and concurrency control.
Buffer PoolCompressionIO Subsystem
0 likes · 25 min read

This article provides a comprehensive technical overview of MySQL InnoDB's internal page structures—including compressed, system, external, encrypted, and R‑TREE pages—along with detailed explanations of the IO subsystem, double‑write buffer, temporary tablespaces, and buffer pool memory management mechanisms.
Buffer PoolDatabase InternalsIO Subsystem
0 likes · 25 min read
