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.
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 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.
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.
This article analyzes a MySQL InnoDB lock wait timeout caused by batch INSERT operations, examines configuration parameters and slow‑query diagnostics, and presents a rewritten SQL solution that reduces execution time from over 100 seconds to under one second.
This article explains MySQL's crash recovery process, covering how the doublewrite buffer repairs corrupted pages, how redo logs are applied to restore data, and how undo tablespaces and unfinished transactions are handled during server startup.
A MySQL DBA recounts a 4‑TB InnoDB instance that stalled during startup, analyzes top, stack traces, and source code to pinpoint a bug involving truncate operations and the buf_flush_event, then presents three practical solutions—including adjusting innodb_flush_sync, using GDB to break the wait loop, or removing truncate logs—to restore normal operation.
This article explains how MySQL auto‑increment primary keys, InnoDB hidden row_id, transaction Xid, InnoDB trx_id, and the server thread_id behave when they reach their numeric limits, the consequences for data integrity, and recommended mitigation strategies.
This article explains the fundamentals of MySQL indexing, detailing how InnoDB pages, B+‑tree structures, clustered and non‑clustered indexes, and disk prefetching work together to reduce I/O operations and dramatically improve query performance.
This guide explains how to detect MySQL deadlock exceptions, retrieve and interpret InnoDB deadlock logs and binlog files, and use the information to pinpoint conflicting SQL statements and fix the underlying lock issues in live systems.
The article walks through a MySQL MGR failover incident, analyzes the InnoDB assertion failure shown in the error log, pinpoints the problematic code at row0pread.cc line 727, compares the fix in MySQL 8.0.28, and references the related bug report for future troubleshooting.
This article experimentally explores MySQL single‑table row limits, demonstrates how to generate massive test data, analyzes InnoDB page and B+Tree index structures, and derives the commonly cited 20 million‑row recommendation by calculating storage capacity and query‑performance implications.
The article explains why a seemingly non‑conflicting UPDATE on a secondary index can cause lock‑wait alerts under RC isolation, detailing the missing end‑range condition push‑down, the InnoDB row‑search flow, semi‑consistent read behavior, and differences between primary‑key and secondary‑index locking.
MVCC (Multiversion Concurrency Control) in MySQL InnoDB uses undo logs and read views to provide non‑locking snapshot reads, managing read‑write, write‑write, and read‑read concurrency, with implicit row fields and transaction IDs determining visibility across isolation levels.
This article explains MySQL's three concurrency types—read‑read, read‑write, and write‑write—describes how InnoDB uses shared/exclusive locks, MVCC snapshot reads, and ReadView metadata to ensure transaction isolation, and provides practical examples and lock‑based solutions for avoiding update loss.
This article explains MySQL's two‑layer storage engine architecture, details how the server layer prepares record metadata, and walks through a concrete InnoDB table example to show the exact memory layout and data‑exchange process between the server and storage engine.
This article explains how MySQL’s InnoDB storage engine, page size, B+‑tree index structure, and primary‑key limits combine to make roughly 20 million rows the practical ceiling for a single table, and shows how to generate massive test data and tune server settings.
This article explains MySQL InnoDB's tablespace architecture—including segments, extents, pages, and rows—and details how clustered and secondary indexes are implemented using B+ trees, comparing them with other tree structures to illustrate why B+ trees are optimal for disk‑based databases.
This article explains how MySQL's count() works across different storage engines, compares the performance of various count() forms, and offers practical strategies—such as using EXPLAIN rows, auxiliary count tables, and batch processing—to obtain accurate or approximate row counts efficiently even on massive tables.
The article details the refactoring of memory monitoring in MySQL 8.0.28, introducing a new PFS metadata structure for precise tracking of memory allocations and deallocations across various components like Buffer Pool and user variables.
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.
This article explains why locating a record in a B‑Tree index is crucial for MySQL InnoDB operations, describes scan intervals, index page structures, and details the step‑by‑step binary and sequential search process used to find the first matching record, including performance optimizations.
The article investigates MySQL InnoDB lock‑wait timeouts caused by hanging transactions that lack proper commit or rollback, explains ACID and lock mechanisms, shows how stale ThreadLocal bindings let pooled threads reuse unfinished transactions, and recommends using @Transactional with robust try‑catch handling to prevent such deadlocks.
The article explains the three primary MySQL data removal commands—DELETE, TRUNCATE, and DROP—detailing their execution speed, underlying mechanisms, impact on disk space for InnoDB and MyISAM engines, and best‑practice usage such as running OPTIMIZE TABLE after DELETE.
This article explains how MySQL performs crash recovery by using Redo logs, doublewrite buffers, undo tablespace handling, transaction subsystem initialization, and the processing of active, prepared, and committed transactions to restore data pages and ensure consistency after an unexpected shutdown.
MariaDB 10.10.1 RC and 10.9.2 GA have been released, delivering a short‑term support series with numerous bug fixes and improvements for InnoDB, replication, Galera clustering, the optimizer and other core components, enhancing stability and performance for database users.
This article explains how MySQL's auto‑increment primary keys behave when they reach their maximum value, the internal InnoDB row_id mechanism, the generation of Xid and trx_id for transactions, and the handling of thread identifiers, including practical examples and code snippets.
This article explains how the height of MySQL InnoDB B+‑tree indexes determines the number of page I/O operations for different query types, provides theoretical calculations of index height, and shows practical methods using information_schema and hexdump to inspect the actual index height of tables.
This article explains how MySQL 8.0's InnoDB generates Redo logs, groups them into mini‑transactions, writes them concurrently to the log buffer, and uses log_writer and log_flusher threads together with LSN, slots, and recent_written structures to ensure durable, ordered persistence.
This article explains how MySQL InnoDB stores data in 16 KB pages, analyzes the practical limits of single‑table row counts, demonstrates data‑generation experiments, and derives the commonly cited 20 million‑row recommendation by examining B+‑tree structures, index pages, and storage parameters.
This article demystifies MySQL's Multi‑Version Concurrency Control (MVCC) by explaining its Undo Log and Read View mechanisms, showing how InnoDB implements read‑committed and repeatable‑read isolation without locking, and illustrating the process with clear examples and diagrams.
This comprehensive guide examines why slow SQL queries degrade system performance, outlines key factors such as data volume, access patterns, and processing methods, and provides concrete optimization techniques, case studies, index design principles, and advanced features like MRR and index push‑down for MySQL‑InnoDB.
This article explains the new online REDO log size adjustment feature introduced in MySQL 8.0.30, compares it with the cumbersome pre‑8.0.30 procedure, and provides step‑by‑step commands and code snippets for both legacy and modern methods.
This article explains the new MySQL 8.0.30 feature that automatically generates invisible primary keys for InnoDB tables without explicit primary keys, demonstrates how to enable and use it, discusses DDL and replication implications, and outlines related limitations and backup considerations.
This article explains how MySQL 8.0’s clone plugin changes GTID handling by storing GTID information in InnoDB undo logs, detailing the allocation of undo log segments, recording GTIDs in undo log headers, persisting them to the gtid_executed table, and coordinating purge operations.
This article explains the lifecycle of MySQL InnoDB redo logs—from generation during DML/DDL operations, through writing into the log buffer, to being flushed into the redo log file and finally persisted on disk—detailing the roles of mtr, log_writer, and log_flusher threads.
Explore the inner workings of MySQL InnoDB indexes, from row formats like Compact, Redundant, and Dynamic to page structures, B+Tree organization, and practical indexing strategies, complete with diagrams and tips for efficient query optimization and index design.
This article explains MySQL locking mechanisms—including table‑level, row‑level, shared and exclusive locks, as well as gap locks—covers the differences between MyISAM and InnoDB engines, shows how to monitor lock statistics, and provides practical optimization tips for reducing lock contention.
The article explains MySQL InnoDB's logical storage architecture, including tablespaces, pages, segments, and B+‑tree indexes, demonstrates how primary and secondary indexes are organized and accessed, and shows how to calculate index tree height and data capacity using SQL queries and hexdump analysis.
This article explains how MySQL COUNT() works under different storage engines, why counting rows becomes slow on large InnoDB tables, and presents practical methods such as using EXPLAIN rows, auxiliary count tables, batch processing, and transaction‑based updates to obtain approximate or exact row counts efficiently.
The article investigates a MySQL 5.6 instance that consumed nearly all memory on a 32 GB test server, identifies misconfigurations, excessive MyISAM file handles, and severe memory fragmentation, and demonstrates how switching to tcmalloc reduced memory usage to a stable 5 GB.
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.
This article explains why undo logs can grow uncontrollably in MySQL, demonstrates how to verify and accelerate undo log truncation by adjusting purge settings in MySQL 5.7, and shows the new manual undo‑tablespace management commands introduced in MySQL 8.0.
This guide explains MySQL 8.0.17's Clone Plugin—how to install it, perform local and remote clones, monitor progress via performance_schema, build replicas from cloned data, understand its internal stages, configure parameters, and compare it with XtraBackup.
This article explains how MySQL’s various auto‑increment identifiers—table AUTO_INCREMENT, InnoDB row_id, Xid, trx_id, and thread_id—behave when they reach their maximum values, the resulting errors or data overwrites, and the underlying mechanisms that cause these edge‑case behaviors.
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.
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.
This article explains the fundamental differences between buffers and caches, details MySQL’s multi‑layer buffer architecture, and examines how write‑through, write‑back, and Direct I/O affect data durability and performance through various InnoDB flush parameters.
This article examines MySQL's ON DUPLICATE KEY UPDATE clause, explains its official behavior, runs experiments with primary and multiple unique indexes, compares InnoDB row‑level locking to MyISAM table‑level locking, and highlights performance and deadlock risks that developers should avoid.
This article investigates why S‑type GAP locks appear in deadlocks under the READ COMMITTED isolation level in MySQL InnoDB, detailing the transaction workflow, lock acquisition during INSERT operations, lock inheritance after rollbacks, and reproducing the deadlock scenario with code examples and diagrams.
This article explains the concept of MySQL auto‑increment locks, describes different insertion methods and lock modes, analyzes how InnoDB allocates and manages auto‑increment IDs—including simple, bulk, and mixed inserts—and provides source‑code insights, practical experiments, and mitigation strategies for ID continuity issues.
This article explores the various reasons MySQL queries become slow—including index misuse, insufficient connection pools, small InnoDB buffer pools, and query cache limitations—while providing practical solutions such as optimizing indexes, adjusting max_connections, enlarging buffer pools, and configuring connection pools for better performance.
This article explains MySQL’s core architecture, detailing components such as connection pools, SQL interface, optimizer, and storage engine plugins, then compares MyISAM and InnoDB, and dives deep into InnoDB’s memory structures, buffer pool, adaptive hash index, tablespaces, and redo log mechanisms.
This article explains MySQL's redo log, binlog, and undo log, covering their roles in crash recovery, durability, performance optimization, log formats, two‑phase commit handling, and how they together ensure data consistency and atomicity in InnoDB.
This article explains InnoDB’s role as a MySQL storage engine, how it reads and writes data using 16 KB pages, the mechanics of row formats—especially the dynamic format—and why varchar columns are limited to 16383 characters, illustrated with SQL examples and diagrams.
This article walks through MySQL's InnoDB storage engine, explaining how pages, locality, skip‑list concepts and B+‑tree structures work together to make indexes efficient, and shows how clustered, non‑clustered and covering indexes affect query performance and I/O behavior.
This article investigates a MySQL 5.7.30 replica crash caused by an InnoDB assertion in btr0btr.cc when handling temporary tables, reproduces the issue using the MySQL Test Run framework, analyzes logs and parameters, and proposes configuration and SQL‑optimisation fixes.
This article provides a comprehensive examination of InnoDB's physical file layout, detailing page components such as the file header, page header, record storage formats, variable‑length and NULL handling, free space, page directory, file trailer, external storage pages, and the differences among row formats and compression techniques, illustrated with concrete hexdump examples and SQL statements.
This article explains MySQL 8.0.17 InnoDB locking mechanisms—including latches, row and table locks, lock modes, intention locks, their compatibility matrix, and detailed source‑code examples that illustrate how IX and X locks are acquired during record updates.
This article explains that MySQL imposes no fixed row limit, discusses the common 5‑million‑row myth and Alibaba's 2 GB recommendation, shows a Java insertion test that slows after millions of rows, and highlights how InnoDB buffer size and memory constraints affect table performance.
Facing a sudden MySQL table lock? This guide walks through real‑world troubleshooting steps—from checking table usage and process lists to inspecting InnoDB transaction and lock tables, identifying lock‑wait relationships, and safely killing offending sessions, while also explaining lock types and common metadata‑lock scenarios.
A recent poll revealed that MySQL with the InnoDB engine can support up to four billion tables, a limit dictated by the underlying file system rather than MySQL itself, and the official documentation confirms this massive capacity.
MySQL 8.0.29, released on April 26 2022, brings 160 bug fixes, several feature refinements—including string handling, stricter time formats, replication defaults, instant‑algorithm support for InnoDB, and new clone variables—while signaling the final phase of the 8.0 lifecycle and urging users of older versions to upgrade for security.
