After a normal DDL change on the OneDBA platform, a table's storage grew nearly 100% and some queries slowed down, revealing a MySQL InnoDB B+Tree page‑split flaw that stores only one row per 16KB page when large records are inserted, which also corrupts statistics and leads to slow SQL.
Understanding MySQL's clustered indexes—including their definition, selection rules, customization via primary key design, practical creation examples, and key design considerations—helps developers optimize query performance and storage efficiency in InnoDB tables for various workloads.
This article explains how InnoDB stores data in 16 KB pages, how B+Tree indexes work, derives the formula for the maximum number of rows a single MySQL table can hold, and why the practical limit of about twenty million rows exists, along with performance implications and optimization tips.
This article explains how InnoDB's redo log works as a safety cushion, detailing its generation, buffering, flushing, circular storage, and recovery process to ensure transaction durability even when the database crashes unexpectedly.
A nighttime incident revealed that a MySQL transaction updated a row but subsequent reads returned the old value, prompting a deep investigation that uncovered InnoDB’s repeatable‑read snapshot behavior, concurrent updates, and how MySQL may skip updates when data appears unchanged, along with reproducible steps and mitigation advice.
This article examines why a SELECT … FOR UPDATE query that appears to use a covering index in MySQL actually performs a table‑row lookup, detailing indirect evidence from performance_schema locks and direct proof through InnoDB source code, and explains the necessity of accessing the primary key for transaction isolation.
This article explains the main directory layout of MySQL 8, the locations of data files, configuration files, system databases, and how InnoDB and MyISAM storage engines represent tables and indexes on the file system, including the use of system and file‑per‑table tablespaces.
This article investigates a puzzling MySQL behavior where a row updated within a transaction sometimes returns its previous value, explains the role of InnoDB's snapshot reads and ReadView, reproduces the issue, and offers practical ways to prevent the “update disappearance” scenario.
This article explains MySQL InnoDB's Next-Key Lock mechanism, how it combines row and gap locks to prevent phantom reads under the REPEATABLE READ isolation level, illustrates lock ranges with examples, discusses when it degrades to row or gap locks, and highlights its advantages and potential deadlock risks.
This comprehensive guide explains MySQL storage engine concepts, compares built‑in engines like InnoDB and MyISAM, details how to view and configure engines, and covers transaction fundamentals, isolation levels, locking mechanisms, and practical migration case studies for reliable database operations.
This article explores MySQL InnoDB's memory and on‑disk architecture, detailing tablespace types, table and row formats, primary key strategies, index structures, the doublewrite buffer, redo log, undo log, and temporary tablespaces, complete with diagrams and code examples for practical understanding.
This article explains why MySQL row overflow occurs, compares the four InnoDB row formats, shows how overflow data is stored, discusses performance and space impacts, and offers practical tips to prevent overflow by optimizing schema and page size settings.
This guide details how to restore a CDH cluster after MySQL metadata loss caused by an accidental deletion of the MySQL data directory, covering database locking, locating deleted file handles, reconstructing shared and independent tablespaces, and verifying consistency to bring the platform back online quickly.
This article explains why MySQL data pages can suffer partial write failures during crashes, how the mismatch between InnoDB and OS page sizes contributes to the problem, and how the Double Write Buffer mechanism safeguards data integrity by providing a recoverable copy of each page.
MySQL 9.4.0 introduces extensive updates including charset handling fixes, macOS compilation options, component behavior changes, new system variables, deprecations, InnoDB memory and indexing enhancements, revised installation procedures, JavaScript stored‑procedure improvements, performance‑mode tweaks, vector function fixes, numerous new features, and a long list of resolved bugs across the server, client, and replication subsystems.
This guide walks through diagnosing a MySQL metadata loss in a CDH cluster, locking the database, locating deleted file handles, restoring shared and independent tablespace files, fixing table structures, verifying consistency, and explains the underlying Linux and MySQL mechanisms.
This guide presents more than twenty practical MySQL 8.0 optimization recommendations—including hardware tuning, InnoDB configuration, index design, query rewriting, security hardening, monitoring, backup strategies, and benchmarking—to help engineers dramatically improve database throughput, latency, and stability.
This article compares MySQL's three common storage engines—InnoDB, MyISAM, and Memory—by examining their core features, locking mechanisms, transaction support, durability, foreign‑key capabilities, typical use cases, and provides concrete CREATE TABLE examples and a side‑by‑side feature matrix to help developers choose the right engine.
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.
This article provides a deep dive into MySQL’s architecture, covering the InnoDB storage engine’s layered structure, index design and usage, query execution plans, deep pagination challenges, slow‑query diagnostics, transaction processing, MVCC isolation, and the roles of undo, redo and binary logs.
This article explains MySQL’s index structures, SQL execution plan analysis, query execution flow, transaction isolation levels, MVCC mechanisms, and InnoDB buffer‑pool caching, providing detailed diagrams and practical examples to help developers optimize database performance.
This article consolidates theory and practical calculations to reveal how MySQL's B+‑tree storage, page structure, and row size determine the realistic maximum number of records a single table can store, ranging from millions to billions depending on schema choices.
This article explains what MySQL indexes are, how they work, their advantages and drawbacks, the different index types, the inner workings of B+Tree structures, and the differences between clustered and non‑clustered indexes, providing practical insights for database optimization.
This article explains why MySQL 5.7 community edition may block online DDL operations when leftover temporary table files remain, analyzes the root cause, and provides step‑by‑step commands and alternative solutions to safely remove the orphaned tables.
This article explains MySQL InnoDB's dual memory and on‑disk architecture, detailing the various tablespace types (system, file‑per‑table, general, undo, temporary), their internal structures (segments, extents, pages), configuration tips, and how tables, row formats, primary keys, auto‑increment lock modes, and B+Tree indexes are organized and managed.
This article explains MySQL's four transaction isolation levels—READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ, and SERIALIZABLE—through step‑by‑step examples, demonstrating their effects on dirty reads, non‑repeatable reads, phantom reads, and performance considerations, helping developers choose the appropriate level.
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’s locking mechanisms, categorizing locks by performance, operation type, data granularity, and finer‑grained levels such as gap and next‑key locks, and offers guidance on selecting the appropriate lock strategy for reliable concurrent database operations.
This article explains how MySQL InnoDB transforms a simple INSERT statement into a complex on‑disk record by detailing the COMPACT row format, record header, variable‑length field list, NULL bitmap, and overflow handling, helping developers optimize storage and performance.
This article explains MySQL InnoDB locking mechanisms—including row, record, gap, next‑key, and intention locks—illustrates each type with SQL statements and performance_schema data, and shows how they interact during concurrent transactions.
This article explains MySQL's index limits for InnoDB and MyISAM engines, detailing the maximum number of indexes per table, the maximum columns per index, and provides practical recommendations on how many indexes a table should have to balance performance and maintenance.
This guide walks you through locating MySQL deadlock logs, analyzing their contents to pinpoint transaction timing, order, and lock details, and identifying the root cause of the deadlock, including special locking scenarios that can trigger deadlocks.
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.
This guide walks you through locating the deadlock log, analyzing its contents to determine the time, order, and involved SQL statements, and pinpointing the root cause of MySQL InnoDB deadlocks, including special locking scenarios and how to interpret lock structures.
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 article explains how to find the MySQL deadlock log, parse its contents to determine the time, order, and affected rows, identify the lock types and root cause, and provides extended examples of special locking scenarios, all illustrated with real‑world SQL and code snippets.
This article explains how to reproduce a MySQL InnoDB lock‑wait scenario, use system tables such as information_schema.processlist, innodb_trx, sys.innodb_lock_waits and performance_schema to locate the blocking transaction, and finally resolve the issue by killing the offending session, while also providing quick‑check queries and parameter tuning advice.
This article explains how MySQL's count() works across storage engines, why InnoDB scans rows and can time out on massive tables, and presents practical alternatives such as using EXPLAIN rows, a dedicated count table, batch processing, or binlog‑to‑Hive for efficient row‑count estimation.
This article explains InnoDB clustered and secondary indexes, their physical B‑tree storage, page‑size and fill‑factor settings, the three‑phase sorted index build process, and the design, tables, cache, document‑ID handling, transaction semantics, and monitoring of InnoDB full‑text indexes.
This article explains how MySQL redo logs and binlog logs work together to ensure data durability, consistency, and high availability, covering InnoDB crash‑recovery, flush policies, log file groups, binlog formats, two‑phase commit, and the complete recovery workflow.
This article walks through the investigation of MySQL bug #115352, describing how an assertion failure in InnoDB’s dictionary cache caused a server crash during a killed ALTER TABLE on a partitioned table, the debugging steps taken, and the eventual fix applied in MySQL 8.0.40.
This article experimentally compares MySQL auto_increment, UUID, and random Snowflake keys by measuring insert and query speeds, analyzing InnoDB index behavior, and discussing the trade‑offs of each primary‑key strategy, ultimately showing why auto_increment generally outperforms UUID in large‑scale workloads.
This article explains what MySQL indexes are, how they work, their advantages and drawbacks, the different index types—including primary, ordinary, composite, full‑text, clustered and non‑clustered—and compares B‑Tree with B+Tree structures to help you design faster, more efficient queries.
This article provides a comprehensive index of a MySQL internals series covering the transaction, lock, and undo modules, detailing each sub‑topic such as transaction initialization, two‑phase commit, various lock types, deadlock handling, and undo log management across numerous linked articles.
The article explains how InnoDB handles transaction commit by modifying Insert and Update Undo segment states, generating a transaction commit number, adding rollback segments to the purge queue, and finally cleaning or caching Undo segments based on their usage and state.
This guide explains MySQL 8's main directory layout, the locations of command binaries and configuration files, how databases and system schemas are represented on the file system, and the differences between InnoDB and MyISAM storage engines, including tablespace and file naming conventions.
This article explains how to read and parse the Undo log generated by a DELETE operation in MySQL 8.0.32 InnoDB, covering preparation, log extraction, detailed field parsing, locating the primary index record, and the step‑by‑step rollback of secondary and primary index records.
This article explains how MySQL 8.0 InnoDB generates and stores Delete Undo logs, detailing the log structure, field meanings, type_flag and info_bits decoding, address composition via DB_ROLL_PTR, and provides concrete SQL examples and shell calculations to illustrate the process.
This article explains MySQL's storage engine architecture, compares built‑in engines such as InnoDB and MyISAM, details their file system interactions, table‑space types, and transaction features—including ACID properties, isolation levels, locking mechanisms, and practical migration steps for production environments.
This article experimentally compares the performance of various MySQL COUNT queries on an InnoDB table with one million rows, explains the underlying execution plans using EXPLAIN, analyzes primary‑key versus secondary‑index behavior, and contrasts InnoDB’s fast count implementation.
This article explains how MySQL 8.0.32's InnoDB engine generates, reads, and parses Undo logs for an UPDATE operation, and details the step‑by‑step process of locating the primary record, constructing a rollback record, and restoring both secondary and primary index entries.
This article analyzes why MySQL 8.0.13 introduced the sql_require_primary_key parameter, demonstrates how adding auto‑increment primary keys can cause master‑slave data inconsistencies, explains the underlying InnoDB row‑id behavior, and provides a step‑by‑step solution to synchronize tables.
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.
This article explains how MySQL 8.0.32 InnoDB generates and stores Update undo logs, detailing the log format, the meaning of each field, how to interpret the log address, and provides concrete SQL examples and shell calculations for practical insight.
This article walks through a real MySQL deadlock case, explaining the background, reproducing the issue, analyzing lock information from InnoDB status, and presenting practical steps to identify root causes and fix the deadlock safely.
This article explains the complete InnoDB undo‑log rollback process on MySQL 8.0, covering preparation, reading and parsing undo logs, constructing primary‑key index records, and the detailed steps for deleting secondary and primary index entries during a transaction rollback.
This article explains MySQL InnoDB's ACID properties in depth, detailing how atomicity relies on Redo and Undo logs, how consistency ensures business logic integrity, how isolation uses locks and MVCC with snapshot and current reads, and how durability is achieved through Redo logs, the innodb_flush_log_at_trx_commit setting, and the Double Write mechanism.
This article investigates a deadlock that occurs when using INSERT … ON DUPLICATE KEY UPDATE in a multi‑threaded MySQL 8.0 environment, reproduces the issue, examines InnoDB lock logs, walks through the relevant source‑code call chain, and demonstrates how gap locks cause the deadlock.
This article explains how MySQL 8.0.32 InnoDB generates and stores Insert Undo logs, detailing their binary format, field meanings, example values, and how the hidden DB_ROLL_PTR field encodes the log address, with accompanying SQL and shell examples.
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.
This article explains the internal process MySQL 8.0.32 InnoDB uses to allocate, create, and manage Undo segments—including cached segment retrieval, new segment creation, insertion into rollback segment lists, conflict avoidance through mutexes, and a concise step‑by‑step summary of the entire workflow.
The article explains why MySQL 8 replaces fragmented, non‑transactional metadata files with a unified, InnoDB‑backed transactional data dictionary, details the information_schema views that expose it, and enumerates the resulting benefits such as atomicity, consistency, crash recovery, performance gains, simplified administration, reduced lock contention, smoother upgrades, improved DDL handling, and enhanced security.
This article explains MySQL InnoDB's multi-version concurrency control (MVCC), covering its basic concepts, hidden columns, isolation levels, the creation and evaluation of Read Views, and includes detailed source code examples and step‑by‑step experiments to illustrate transaction visibility.
This article examines MySQL 8.0.32 InnoDB's rollback segment memory layout and details the algorithms used to allocate rollback segments for user temporary tables and regular tables, including the role of system variables, counters, and the distribution matrix across undo tablespaces.
This article explains why COUNT(*) on a large InnoDB table can take seconds while MyISAM returns instantly, explores the underlying storage‑engine differences, and presents six practical techniques—including Redis counters, counting tables with transactions or triggers, parallel read threads, secondary indexes, and SHOW TABLE STATUS—to dramatically speed up row counting in MySQL.
This article explains the four‑layer logical structure of InnoDB Undo logs—Undo pages, Undo segments, rollback segments, and Undo tablespaces—detailing their roles, management, related system variables, and how they affect transaction rollback and storage in MySQL 8.0.32.
This article provides a concise English recap of the 27 previously published InnoDB lock module articles, covering theoretical concepts such as table and row locks, lock waiting, deadlock handling, and practical scenarios like lock behavior during inserts and duplicate key operations.
This article reviews the long‑standing performance problems of TRUNCATE and DROP TABLE in MySQL, tracing their origins through official manuals and historical bugs, summarizing the optimizations introduced in MySQL 5.5.23, 5.7, 8.0, and 8.4, and offering practical guidance for mitigating remaining latency.
This article examines how InnoDB acquires various row, gap, and next‑key locks during INSERT … ON DUPLICATE KEY UPDATE statements under REPEATABLE‑READ and READ‑COMMITTED isolation levels, illustrating the lock behavior with detailed SQL examples and performance‑schema queries.
This article investigates how many records a plain SELECT * query can return in MySQL 8 by examining max_allowed_packet, packet definitions, single‑row size limits, column count restrictions, InnoDB row formats, and practical experiments that reveal the real constraints on query result size.
This article explains InnoDB lock types, their characteristics, and how MySQL 5.6 and 8.0 handle adding a column without fully locking the table, detailing online DDL, atomic DDL, and performance considerations for large tables.
This article explains MySQL transaction fundamentals, the ACID properties, MVCC implementation, isolation levels, snapshot versus current reads, and the various lock types and modes, and provides practical examples of how different SQL statements acquire locks under InnoDB.
This article examines how InnoDB acquires row‑level locks during INSERT … ON DUPLICATE KEY operations under REPEATABLE‑READ and READ‑COMMITTED isolation levels, explains the lock types on primary and unique indexes, and shows the rollback and lock‑conversion process with concrete SQL examples.
This article examines how MySQL InnoDB decides whether to modify index blocks during UPDATE statements, walks through the internal mysql_update workflow, shows debugging with GDB, explains three test scenarios, and validates the behavior by inspecting block LSNs with the innblock tool.
This article explains why a MySQL 8 InnoDB table with a unique index can still store duplicate rows—especially when indexed columns contain NULL or when logical deletion is used—and presents several practical strategies, including status counters, timestamps, extra IDs, hash fields, and batch‑insert techniques, to enforce true uniqueness.
This article explains how MySQL 8.0.32 InnoDB handles INSERT … ON DUPLICATE KEY UPDATE under REPEATABLE‑READ and READ‑COMMITTED isolation levels, showing the preparation steps, lock acquisition process, and why the same exclusive record lock is obtained in both cases.
This article reproduces a MySQL 8.0.32 InnoDB deadlock using two concurrent DELETE statements on the same secondary index, explains the lock types and lock‑wait graph, and details why the deadlock occurs and how it is resolved.
This article explains how MySQL InnoDB deadlocks occur during order‑record idempotency checks, demonstrates step‑by‑step reproduction with SQL scripts, analyzes the lock types involved, and provides practical strategies to avoid and resolve such deadlocks in production systems.
This article details a MySQL 8.0.35 crash caused by an InnoDB assertion that a VARCHAR length exceeds 65,535 bytes, explains the error‑log and core‑dump analysis, shows how to reproduce the bug, and notes its resolution in MySQL 8.0.35.
This article investigates how many rows should be inserted per batch in MySQL, analyzes the limits of SQL statement size, buffer pool, transaction overhead, and indexes, and presents empirical tests that reveal the most efficient batch size for different data volumes.
The article explains MySQL InnoDB’s multi‑thread architecture—including the Master, IO, Purge, and Page Cleaner threads—detailing each thread’s responsibilities, configurable parameters, and how they cooperate to achieve high‑performance, concurrent data reads and writes while maintaining consistency and crash recovery.
This article demonstrates how InnoDB acquires and releases row‑level locks for a sample table under REPEATABLE‑READ and READ‑COMMITTED isolation levels, showing the SQL statements used, the lock types observed in the performance_schema, and the reasons behind the observed locking patterns.
This article demonstrates how InnoDB handles row, next‑key, and gap locks for a SELECT … FOR SHARE query under REPEATABLE‑READ and READ‑COMMITTED isolation levels, showing the preparation steps, SQL examples, lock inspection queries, and a concise summary of the differences.
This article provides a comprehensive overview of MySQL 8.0's concurrency control mechanisms, detailing table‑level MDL locks, server‑ and engine‑layer table locks, page‑level B+Tree locking, and row‑level lock types with code examples and real‑world deadlock scenarios.
This article demonstrates how InnoDB acquires different types of row and gap locks under REPEATABLE-READ and READ-COMMITTED isolation levels by creating a test table, inserting data, setting transaction isolation, executing SELECT ... FOR SHARE statements, and inspecting lock information from performance_schema.
COUNT(*), COUNT(1) and COUNT(column) may look similar, but they differ in how MySQL processes them, the rows they count, and performance implications, especially with NULL values and index usage; this article explains each form, cites official InnoDB behavior, and guides proper usage.
The article demonstrates how MySQL Router 8.2 enables transparent read/write splitting for an InnoDB ReplicaSet, showing the architecture, configuration steps, connection behavior, and the generated routing settings that improve database performance without modifying application code.
The article outlines MySQL’s layered architecture—connector, parser, optimizer, executor, and binary log in the server layer and pluggable storage engines like InnoDB—then walks through the step‑by‑step processing of SELECT and UPDATE statements, highlighting permission checks, caching, plan selection, redo and binlog handling for crash‑safe durability.
This article explains MySQL storage engine concepts, compares InnoDB, MyISAM, and MEMORY, describes how data is stored in shared tablespace versus file‑per‑table modes, and provides configuration commands for switching modes, changing default engines, and inspecting or altering a table’s engine.
The article explains MySQL’s Multi-Range Read (MRR) optimization, detailing how it scans secondary indexes, buffers and sorts primary keys, leverages disk pre‑read and cache locality to convert random I/O into sequential I/O, and shows configuration, monitoring methods, and a concrete orders‑table example.
This tutorial demonstrates, using MySQL 8.0.32 InnoDB source code, how REPEATABLE-READ and READ-COMMITTED isolation levels affect the locking behavior of SELECT ... FOR SHARE statements on primary‑key rows, including preparation, execution, and detailed lock analysis.
A batch INSERT triggers a massive lock record buildup, and a concurrent SELECT on performance_schema.data_locks causes a memory allocation error that leaves trx_sys‑mutex unreleased, leading to a full MySQL hang; the article reproduces the bug, analyses the stack, explains the root cause, and shows the official fix in MySQL 8.0.37.
This article explains the fundamental concepts of MySQL index structures, detailing how clustered (primary) indexes store data physically in order, how non‑clustered (secondary) indexes reference primary keys, and compares their performance implications and practical usage scenarios.
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 investigates whether the REPEATABLE‑READ isolation level in InnoDB fully prevents phantom reads by conducting a series of current‑read and snapshot‑read experiments, analyzing transaction behavior, MVCC mechanisms, and offering recommendations to avoid phantom anomalies.
This article examines how MySQL 8.0.32 InnoDB handles locking when inserting a row that violates a unique index under the READ‑COMMITTED isolation level, detailing the preparation steps, observed lock types, the underlying mechanism, and the final cleanup actions.
This article explains MySQL index classifications, the inner workings of B+‑tree and hash indexes, the differences between clustered and secondary indexes, and advanced concepts such as covering indexes, index push‑down, index merge, cost‑based index selection, and common pitfalls that cause index inefficiency.
This article examines how InnoDB handles row locking when an INSERT triggers a primary‑key index conflict, detailing the lock types on primary and unique indexes, the implicit‑to‑explicit lock conversion, and the role of the supremum record in the locking process.
The article describes a MySQL 8.0.18 backup failure caused by an undo log remaining in cleanup state for over ten hours, details the error logs, shows how the ChatDBA AI tool analyses and resolves the issue, compares it with ChatGPT‑4o, and provides related activity previews.
