This article explains MySQL InnoDB's various buffers—including the buffer pool, change buffer, log buffer, and doublewrite buffer—detailing their purposes, internal mechanisms such as LRU variants, configuration options, and how they improve performance and ensure data integrity.
This article explains MySQL's various lock mechanisms—including shared (S) and exclusive (X) locks, intention locks, record, gap, next‑key, and insert‑intention locks—illustrates their behavior with SQL examples, analyzes a concurrent insert deadlock scenario, and offers practical mitigation strategies.
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 explains MySQL InnoDB's Multi-Version Concurrency Control (MVCC), covering its basic concepts, read/write scenarios, implicit fields, undo logs, read view mechanisms, transaction handling, and differences between RC and RR isolation levels, providing a comprehensive guide for developers.
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 guide explains MySQL's lock mechanisms—including shared and exclusive locks, row‑level locking requirements, pessimistic versus optimistic strategies, and practical implementations with InnoDB, transaction handling, and JMeter concurrency testing—to prevent overselling in high‑traffic scenarios.
Starting with MySQL 8.0.30, the Generated Invisible Primary Keys (GIPKs) feature automatically adds an invisible bigint auto‑increment column named my_row_id when a table lacks an explicit primary key, simplifying schema design, easing DBA‑developer conflicts, and offering flexible handling in MGR and replication environments, while noting its limitations and usage commands.
This article analyzes the differences between MySQL's full table scan and full primary‑key scan by examining execution plans, source‑code paths, and runtime behavior, demonstrating that both ultimately read data from the primary‑key index and explaining why the optimizer distinguishes them.
This article walks through how a Java application interacts with MySQL, covering the driver, connection pooling, thread handling, query parsing, the optimizer, storage engine, buffer pool, undo/redo logs, binlog, and transaction commit, providing a complete end‑to‑end view of MySQL’s internal processing.
This article breaks down MySQL's complex locking mechanisms—global, table, and row locks—explaining how different SELECT statements, transaction isolation levels, and index types affect lock acquisition, and provides practical test scripts and visual examples to help developers master lock behavior.
This article examines how InnoDB processes UPDATE statements on a table with primary and secondary indexes, analyzes three scenarios to determine which index blocks are modified, explains the internal filtering steps, and validates the behavior by inspecting LSN changes of the affected blocks.
This article explains how MySQL 8 improves the auto‑increment counter by persisting its maximum value across server restarts, contrasting the behavior with MySQL 5.7 through detailed examples and SQL commands that demonstrate the differences in primary‑key generation.
The article examines a MySQL InnoDB deadlock that occurs when concurrent inventory‑write transactions in a pre‑release environment contend for row and gap locks, explains InnoDB lock types, isolation‑level effects, and proposes solutions such as distributed locks and isolation level adjustments.
This article explains why MySQL needs locking, details every InnoDB lock type—including shared, exclusive, intention, record, gap, next‑key, insert‑intention and auto‑increment locks—covers lock compatibility, deadlock causes and prevention, compares optimistic and pessimistic locking, and shows how SELECT FOR UPDATE behaves under different isolation levels.
This article explains MySQL's index prefix length restrictions for BLOB/TEXT columns in versions 8.0 and 5.7, demonstrates verification experiments showing the 767‑byte and 3072‑byte limits for different InnoDB row formats, and discusses the underlying design implications.
This article explains MySQL InnoDB's multi-version concurrency control (MVCC), detailing how transaction isolation levels such as read‑committed and repeatable‑read create snapshots, how undo logs and hidden columns form version chains, and walks through concrete examples to show which row values are visible to concurrent transactions.
This article examines how INSERT, REPLACE and INSERT … ON DUPLICATE KEY UPDATE statements can cause deadlocks in MySQL InnoDB under various scenarios, explains the underlying lock types such as GAP, NEXT‑KEY and INSERT‑INTENTION locks, and offers practical recommendations to avoid them.
This article explains how an UPDATE without indexed WHERE conditions can trigger full‑table next‑key locks in InnoDB, causing business downtime, and provides practical safeguards such as enabling sql_safe_updates and using FORCE INDEX to ensure safe execution.
This article explains MySQL transaction fundamentals, the ACID properties, how to start and control transactions, the problems of dirty, non‑repeatable and phantom reads, isolation level settings, MVCC mechanics, read‑view structures, and the role of undo logs in ensuring data consistency.
This article describes how to identify, analyze, and fix InnoDB page corruption in MySQL instances, covering log inspection, querying metadata tables, using innodb_force_recovery, and the inno_space tool, with example commands and best‑practice recommendations to prevent data loss.
This article explains how MySQL handles overflow of auto_increment, InnoDB row_id, Xid, trx_id, and thread_id, showing the resulting primary‑key errors, data overwrites, rare Xid collisions, a potential dirty‑read bug, and recommended safeguards such as using BIGINT.
This article explains the purpose, classification, and implementation of MySQL locks—including global, table, page, row, intention, and gap/record/next-key locks—as well as optimistic and pessimistic locking strategies, providing code examples and usage scenarios for ensuring data consistency in concurrent transactions.
This article explains why MySQL, the world’s most popular open‑source relational database, is a fundamental skill for developers, operators and testers, and outlines a two‑book learning path covering installation, SQL mastery, InnoDB internals, indexing and performance optimization.
This article explains why MySQL's Adaptive Hash Index (AHI) can cause slow TRUNCATE operations, describes its benefits, and provides a detailed step‑by‑step walkthrough of the AHI construction process—including index counting, construction‑info counting, data‑page counting, and the final hash‑index building—using MySQL 8.0.32 source code examples.
This article explains how Percona XtraBackup 8.0.33-28 introduces a new design that reduces the time required for the backup‑prepare phase, lowers memory and I/O usage, and speeds up SST when adding new nodes to a Percona XtraDB Cluster, with detailed performance benchmarks and code examples.
This article explains the myth of a 2 million‑row limit for MySQL tables, shows how primary‑key size and InnoDB page structure determine theoretical row limits, details B+‑tree indexing mechanics, calculates practical capacities, and compares B+‑tree with B‑tree performance.
This article explains InnoDB's three row‑lock types, when MySQL statements acquire implicit or explicit locks, and walks through concrete examples for unique and non‑unique index queries, showing how Next‑key, Record and Gap locks are determined.
This article explains MySQL's doublewrite buffer mechanism, how it ensures data consistency by writing to a redo log before disk, provides configuration steps to enable it, and outlines performance optimization techniques such as adjusting redo log size, separating log files, and tuning flush settings.
The article analyzes a replication failure where mysqldump creates duplicate primary‑key errors on a MyISAM table, explains why the --single-transaction option only guarantees consistency for InnoDB, and provides solutions such as converting tables to InnoDB or using Xtrabackup.
This article introduces MySQL’s three primary logs—redo log, binlog, and undo log—explaining their purposes, how they ensure durability and recovery, the mechanisms for flushing them to disk, and the key differences among them, along with practical configuration recommendations.
This article explains how InnoDB stores data on disk, describing tablespaces, segments, extents, pages, row formats, record headers, page directories, B+‑tree indexes, and the differences between clustered and secondary indexes, with examples and diagrams to illustrate each component.
This article explains InnoDB's three row‑lock types, when they are applied implicitly or explicitly, the two core locking principles, and walks through concrete examples for unique and non‑unique index equality and range queries, showing exact lock ranges and lock‑type degradations.
This article walks through MySQL's three‑layer logical architecture, explains the roles of redo, undo, bin, and relay logs, shows how they ensure crash‑safety and point‑in‑time recovery, and provides practical commands for inspecting and configuring these logs.
The article investigates why an UPDATE statement on a MySQL 5.7 InnoDB table with RR isolation experiences high lock time, demonstrates how missing indexes cause full‑table scans and blocking, and shows how adding an index and using optimizer trace can diagnose and resolve the issue.
This article investigates why TRUNCATE and DROP TABLE statements appear in MySQL slow‑query logs, analyzes the internal execution paths and performance bottlenecks in MySQL 5.7 and 8.0, and proposes configuration‑based optimizations and debugging techniques to mitigate the latency.
This article explains the different types of InnoDB tablespaces, why the ibdata1 file can grow uncontrollably, and provides a step‑by‑step procedure—including full backup, instance recreation, and configuration of UNDO and file‑per‑table settings—to safely reduce its size in MySQL environments.
This article explains the differences between MySQL DDL and DML, describes how online DDL works—including copy and inplace algorithms, execution phases, common pitfalls, limitations, and recent MySQL 8.0 enhancements—providing practical guidance for safely altering large tables.
This article explains MySQL InnoDB index fundamentals, covering index types, data structures, clustered and secondary indexes, covering indexes, index pushdown, index merge, cost‑based index selection, common index‑inefficiency scenarios, and practical guidelines for designing effective indexes.
This article explains how MySQL stores and updates auto‑increment values, why gaps appear after failed inserts, unique‑key conflicts, transaction rollbacks or batch operations, and how different innodb_autoinc_lock_mode settings and MySQL 8.0 improvements can mitigate the issue.
This article reproduces a MySQL 8.0.32 InnoDB scenario where inserting a row that violates a unique index causes a next‑key exclusive lock on the primary‑key supremum record, explains the underlying implicit‑lock mechanism, walks through the relevant source‑code stack, and details the lock‑conversion and rollback steps under REPEATABLE‑READ isolation.
This article provides a detailed source‑code walkthrough of MySQL 8.0.33’s two‑phase commit process, explaining the Prepare and Commit stages, internal structures such as binlog queues, GTID generation, redo‑log flushing, sync handling, and the interactions between InnoDB and the binary log.
The article explains how MySQL auto‑increment primary keys, InnoDB invisible row_id, transaction Xid and trx_id, and thread identifiers behave when they reach their numeric limits, showing the resulting errors, data overwrites, and potential visibility bugs in long‑running instances.
This article explains why MySQL count(*) can become a performance bottleneck on InnoDB tables, compares different count() variants, and presents practical optimization techniques such as Redis caching, second‑level caches, parallel execution, reducing joins, and offloading analytics to ClickHouse.
This article explains MySQL index fundamentals—including classification, B+‑tree and hash structures, clustered and secondary indexes, single‑ and composite‑column indexes, covering indexes, index condition pushdown, index merge strategies, cost‑based index selection, common index‑invalidating scenarios, and practical guidelines for creating effective indexes.
This article explains how MySQL handles overflow of various auto‑generated identifiers—including table auto_increment, InnoDB row_id, Xid, trx_id, and thread_id—detailing the wrap‑around behavior, potential data loss, and the rare edge cases that can lead to bugs.
This article provides a comprehensive guide to MySQL indexing, covering index types, data structures, clustering vs. non‑clustering, hash and B+Tree indexes, covering indexes, index push‑down, index merge strategies, cost‑based index selection, common pitfalls, and practical indexing principles with illustrative examples and SQL snippets.
This article explains how MySQL's lock_time metric is calculated by summing table‑level and row‑level lock wait times, details the InnoDB implementation, shows source‑code paths and functions involved, and clarifies why lock_time may appear small even when query execution is long.
The article investigates a MySQL 8.0 instance where many sessions were stuck in the executing state, analyzes InnoDB thread‑concurrency settings, stack traces and source code to reveal that exhausted InnoDB tickets caused nanosleep‑based blocking, and proposes configuration and SQL‑optimisation solutions.
This article explains how MySQL handles auto‑increment primary keys, InnoDB’s internal row_id, transaction Xid and trx_id counters when they reach their maximum values, the resulting primary‑key conflicts, data overwrites, and a theoretical dirty‑read bug caused by counter wrap‑around.
This article explains the fundamentals of database transactions, the ACID properties, and how InnoDB uses redo and undo logs together with MVCC to ensure atomicity, consistency, isolation, durability, and reliable crash recovery.
This article explains MySQL’s various lock types—global, table, metadata, intention, and row‑level—and shows how SELECT statements acquire different locks under each isolation level, with examples for existing and missing rows, range scans, unique and non‑unique indexes, and full‑table scans.
This article examines why long‑running SELECT COUNT(*) statements are not recorded in MySQL's slow query log, analyzes the relevant source‑code functions, demonstrates debugging with gdb, identifies the min_examined_row_limit setting as the root cause, and provides practical solutions and bug‑report details.
After upgrading from MySQL 5.7 to 8.0, a client experienced a flood of slow queries despite low system load; the root cause was a change in the sys.innodb_lock_waits view that now relies on performance_schema.data_locks, whose large lock table holds a global mutex and blocks other transactions, a problem solved by adding an index to reduce lock volume.
This comprehensive guide explains what MySQL indexes are, details various index types—including B+‑tree, hash, full‑text, and spatial—covers when indexes become ineffective, shows how to design, use, and troubleshoot them, and provides practical steps for large‑scale index management.
This article examines a MySQL deadlock caused by concurrent INSERT operations on a unique index, detailing the detection, reproduction, root cause analysis using InnoDB status, performance_schema data, and provides scripts and recommendations to trace and resolve such deadlocks.
This article explains how InnoDB’s REPEATABLE READ isolation level uses MVCC and gap locks to prevent most phantom reads, illustrates the mechanisms with SQL examples and diagrams, and outlines the remaining edge cases and best practices for fully avoiding phantom reads.
This article explains the different types of auto‑increment identifiers used by MySQL—including table primary keys, InnoDB row_id, Xid, trx_id, and thread_id—covers their limits and behaviors when they overflow, and introduces Redis as an external auto‑increment key solution.
This article explains MySQL index fundamentals, compares B+Tree and B-Tree structures, shows how page size and tree height affect capacity, outlines which query patterns benefit from indexes, and discusses practical limits and the adaptive hash index feature.
This article explains how to activate MySQL 8.0.17+ REDO log archiving, configure the required directory and permissions, resolve common errors when using backup tools like mysqlbackup, and demonstrates manual archiving via the MySQL client with proper user privileges.
This article explains how MySQL 8.0.32 (InnoDB) handles a situation where the client sends a DML/DDL statement, the server executes it, and the client silently drops the connection before the server can detect the disconnect, detailing transaction commit, rollback, and send() behavior.
This article explores MySQL single‑table row limits, demonstrates how to generate massive test data, explains InnoDB page and index structures, calculates theoretical maximum rows based on B+‑tree parameters, and provides practical tuning tips for handling large tables.
This article explains why MySQL uses MVCC, how undo‑log version chains and read views implement multi‑version concurrency control, and details the various lock types—including global, table, metadata, intention, row, gap, and next‑key locks—under different isolation levels with practical code examples.
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 how MySQL InnoDB achieves the ACID properties—Atomicity, Consistency, Isolation, Durability—by detailing the roles of undo logs, redo logs, buffer pool, lock mechanisms, and MVCC, and outlines transaction lifecycle commands, isolation levels, and consistency guarantees.
This article provides a comprehensive deep‑dive into MySQL indexing, covering the definition, pros and cons, differences between clustered and non‑clustered indexes, InnoDB vs MyISAM implementations, B+Tree structure, capacity calculations, adaptive hash indexes, index pushdown optimization, and how to interpret EXPLAIN output to verify index usage.
MySQL 8.0 introduces numerous enhancements including InnoDB engine defaults, atomic DDL, instant column addition, CTE support, UTF8MB4 default charset, Clone plugin, resource groups, role management, multi-valued and functional indexes, invisible indexes, descending indexes, SET_VAR syntax, persistent parameters, window functions, online DDL, and improved backup and binlog capabilities.
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 analyses how MySQL 5.7 InnoDB processes COUNT(*) versus COUNT(1), COUNT(id) and COUNT on indexed or non‑indexed columns, showing execution‑plan differences across various index configurations, explaining why count(*) can be unexpectedly slower and offering practical guidance for query optimization.
This article explains what a database transaction is, details the ACID properties, compares MySQL's four isolation levels, clarifies why Repeatable Read is the default due to binlog formats and consistency, examines concurrency anomalies of each level, and provides practical guidance on handling large transactions, optimizing performance, and understanding InnoDB's MVCC implementation.
This article explains MySQL’s internal architecture, walks through how a SELECT query and an UPDATE statement are processed—including the connector, parser, optimizer, executor, redo log, binlog, and two‑phase commit—and details transaction properties, isolation levels, MVCC, and related configuration parameters.
This article explains how InnoDB uses undo segment states for crash‑safe recovery, details the XA transaction protocol, describes bugs in earlier MySQL versions, and shows how MySQL 8.0.30 introduces a new undo state and revised prepare order to make external XA transactions crash‑safe, including code examples and recovery steps.
This article explains InnoDB’s clustered and secondary index structures, why secondary index leaf nodes store primary key values, how lookups trigger “row‑lookup” (回表) operations, and how covering indexes can eliminate those extra scans, illustrated with MySQL examples.
The article explains why an UPDATE on the same row in MySQL can block in one transaction but not in another, by analyzing the types of row‑level locks (record, next‑key, gap) acquired during SELECT … FOR UPDATE, normal updates, and updates that modify primary‑key values, illustrating the lock behavior with B+‑tree examples.
This article explains why MySQL needs locking, details every InnoDB lock type—including shared, exclusive, intention, record, gap, next‑key, insert‑intention and auto‑increment locks—shows how they interact, and provides practical guidance for avoiding deadlocks and safely updating rows.
This article breaks down MySQL's InnoDB storage engine, detailing where data files reside, the different row formats (compact, redundant, dynamic, compressed), the internal 16 KB page layout, record header fields, overflow handling, and how B‑tree indexes (clustered and secondary) are built and searched.
This article explains MySQL transaction fundamentals, including the ACID properties, isolation levels, MVCC mechanics, transaction start methods, and practical examples that illustrate how different isolation levels affect data visibility and consistency.
This article experimentally demonstrates how MySQL's SELECT … FOR UPDATE statement acquires various locks—IX, X, and gap locks—under RC (read‑committed) and RR (repeatable‑read) isolation levels with unique, primary, normal, and missing indexes, revealing when row‑level versus table‑level locking occurs.
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 presents a series of MySQL 5.7.26 experiments that reveal how record locks on non‑indexed columns are applied to the primary (clustered) index under different isolation levels, how full‑table scans affect locking behavior, and why updates and inserts may or may not be blocked.
This article experimentally verifies how SELECT FOR UPDATE behaves under different MySQL versions, isolation levels, and index conditions, revealing when it acquires row‑level, gap, or table‑level locks across 20 distinct scenarios.
This article explains MySQL's transaction concepts, the four isolation levels—READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ, and SERIALIZABLE—detailing their effects on dirty reads, non‑repeatable reads and phantom reads, how they are implemented with locks and MVCC, and provides practical SQL examples and diagrams.
This article explains how InnoDB implements row‑level locking in MySQL, detailing the lock types (record, gap, next‑key), the scenarios that trigger each lock, how unique and non‑unique indexes affect locking, and provides step‑by‑step commands and examples for analyzing locks with performance_schema.
This article walks through practical MySQL index usage, illustrating space and time costs, back‑table overhead, covering indexes and index condition pushdown, common scenarios where indexes fail, and concrete best‑practice guidelines, all demonstrated with a 5‑million‑row InnoDB table and real SQL examples.
This article introduces an open‑source MySQL Shell plugin collection, explains how to install it on Fedora, and demonstrates practical usage of its replication, locks, profiling, InnoDB fragmentation, and binlog‑check components with concrete command‑line examples and output.
The article explains MySQL InnoDB’s internal storage hierarchy—from tablespaces, segments, and extents to pages and rows—its physical components such as buffer pool, change buffer, adaptive hash index, redo and binary logs, persistence techniques like double‑write buffering and log‑flushing policies, and how two‑phase commit and SQL execution interact, helping developers write correct, high‑performance applications.
This article explains how MySQL stores AUTO_INCREMENT values for InnoDB and MyISAM tables, describes the algorithm that updates these values during inserts, and analyzes why generated IDs may become non‑sequential due to unique‑key conflicts, transaction rollbacks, and batch insert strategies.
This article explains MySQL 5.7 InnoDB's four isolation levels, the structure and purpose of undo logs, the mechanics of multi‑version concurrency control (MVCC), read‑view creation and closure, and why REPEATABLE READ still allows phantom reads, illustrated with code examples.
This article analyzes a MySQL 8.0 deadlock caused by concurrent update‑then‑insert logic that creates X GAP locks and conflicting insert‑intention locks, explains the lock trace, and proposes using INSERT ON DUPLICATE KEY to avoid the deadlock.
This article delves into MySQL InnoDB's storage engine, explaining the COMPACT row format, hidden columns, data page structure, page header, directory, and how B+‑tree indexes—including clustered and secondary indexes—operate and are optimized for fast queries.
This article explains how to use the MySQL innodb_metrics table to transform the unreadable output of the show engine innodb status command into clear, queryable metrics, including buffer pool statistics and CPU counters, and shows how to enable or disable specific counters as needed.
This article explains the purpose and advantages of MySQL indexes, compares MyISAM and InnoDB storage engines, details B+‑tree, full‑text, and hash indexes, and provides practical optimization techniques to improve query performance while noting their trade‑offs.
This article explains where MySQL keeps its data files, how InnoDB organizes tablespaces into segments, extents, pages and rows, details the Compact row format—including variable‑length field length lists, NULL‑value lists, and hidden fields—and clarifies the limits of VARCHAR and row‑overflow handling.
This article explains how MySQL's InnoDB engine guarantees ACID properties by describing the four components of ACID, the four isolation levels, lock granularity and types, the MVCC mechanism with version chains and read views, and the roles of undo, redo, and bin logs in ensuring atomicity, consistency, isolation, and durability.
This comprehensive guide explains how MySQL's InnoDB engine applies row‑level locks—including shared, exclusive, gap, and next‑key locks—based on different SQL statements, isolation levels, and index types, and shows how to analyze and avoid phantom reads with practical experiments.
The article details a step‑by‑step investigation of an intermittent deadlock occurring during session creation in a video‑conference system, explains how InnoDB lock types caused the conflict between DELETE and INSERT statements on the session and session_endpoint tables, reproduces the issue, debugs MySQL source code, and presents a simple code‑order fix to eliminate the deadlock.
A detailed investigation of intermittent MySQL InnoDB deadlocks occurring during video‑conference session creation, covering log extraction, lock analysis, source‑code debugging, reproducible test cases, and a simple code change that eliminates the deadlock while explaining the underlying locking mechanisms.
This article explores MySQL InnoDB table size limits, explains how page and B+ tree structures affect performance, provides scripts for generating massive test data, and derives a practical recommendation of around 20 million rows based on storage and index calculations.
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 why MySQL's count(*) can be slow on InnoDB, compares different count variations, and presents practical optimization techniques such as Redis caching, second‑level caches, multithreading, reducing joins, and using ClickHouse for massive datasets.
