This article explains how InnoDB organizes data with B+ trees, calculates the maximum number of rows a single tree can hold (around 20 million), and shows how page size, pointer count, and tree height affect MySQL index performance.
This article explains why MySQL requires locking, compares table and row lock types, details MyISAM and InnoDB lock mechanisms—including optimistic vs. pessimistic locks, lock algorithms, and deadlock handling—while providing practical SQL examples and prevention strategies.
This article explains the principles of pessimistic concurrency control in relational databases, details the conditions under which MySQL's SELECT … FOR UPDATE works, compares it with optimistic locking, provides concrete SQL examples, and demonstrates lock behavior through multiple transaction test scenarios.
This article explains how MySQL computes replication delay using show_slave_status, analyzes the relationship between binlog write timestamps and event generation, enumerates common causes of lag, and provides a detailed examination of the system‑lock state in the slave SQL thread with code examples.
This article explains InnoDB's logical storage hierarchy—tablespace, segment, extent, page—then details MySQL partitioning concepts, types, example DDL, performance impacts, and best‑practice tips for OLTP and OLAP workloads.
The article examines why a 100 GB MySQL 5.6 transaction causes high CPU usage during lock_number_of_rows_locked calculation, compares the lock‑counting implementations of MySQL 5.6 and 5.7, and offers diagnostic steps and version‑upgrade recommendations to mitigate the bottleneck.
This article analyzes a MySQL InnoDB deadlock caused by concurrent batch inserts into a table with a composite unique index, explains the lock types involved, and offers practical strategies such as shortening transactions and lowering isolation levels to avoid similar deadlocks.
This article explains the differences between MySQL table locks and row locks, describes how MyISAM and InnoDB handle locking, provides explicit lock syntax with examples, discusses gap locks, and offers practical recommendations for avoiding lock contention in concurrent applications.
This article explains what MySQL indexes are, the different categories such as ordinary, unique, composite, clustered and non‑clustered, how B‑Tree, B+Tree and hash indexes are implemented in InnoDB and MyISAM, and why auto‑increment primary keys are recommended for optimal performance.
This tutorial explains MySQL fundamentals, the advantages of using MySQL, detailed concepts of indexes, how they work, step‑by‑step SQL examples for creating, viewing, and dropping indexes, best‑practice principles, and query‑optimization techniques to improve performance.
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.
This tutorial explains how to create and operate a MySQL InnoDB ReplicaSet—deploying sandbox instances, configuring a replica set with primary and secondary roles, adding nodes, performing manual primary switches and failure recovery, and using MySQL Router to provide an automatic read/write endpoint, while highlighting current limitations.
This article explains the MySQL COUNT function, compares COUNT(*), COUNT(1) and COUNT(column) usages, discusses common interview questions, and details the specific optimizations MyISAM and InnoDB apply to COUNT(*) queries, concluding with best‑practice recommendations.
This article explains MySQL's row size restrictions at both the Server and InnoDB layers, detailing the calculation methods, relevant source code paths, error messages, and practical guidelines for ensuring total record length stays within the 65535‑byte server limit and the 8126‑byte InnoDB limit.
This article explains MySQL's row size restrictions, the differences between server and InnoDB limits, how VARCHAR and TEXT columns affect those limits, and demonstrates through calculations and tests the maximum count of TEXT fields that can be stored under various strict‑mode settings.
This article explains the fundamentals of MySQL InnoDB transaction isolation levels, describes the four standard isolation grades, shows how InnoDB implements them in source code, and provides concrete case studies with SQL scripts to illustrate locking behavior and MVCC effects.
This article explains how MySQL's InnoDB engine applies locks for DELETE and SELECT statements, covering MVCC, snapshot and current reads, clustered indexes, left‑most prefix rules, two‑phase locking, isolation levels, gap and next‑key locks, and analyzes nine different index‑and‑isolation scenarios with detailed conclusions.
This article explains how MySQL's Adaptive Hash Index (AHI) works, why it is needed to reduce B‑tree lookup costs, the three stages of building an AHI, practical examples of its construction, performance benefits, and operational recommendations for managing AHI in production environments.
This article explains the internal structure of InnoDB rollback segments, UNDO log storage formats, and the recovery process in MySQL, detailing how transaction IDs, segment headers, page headers, and log records are organized and used to safely roll back or purge data.
This article explains the InnoDB logical storage architecture—including segments, extents, and pages—then introduces MySQL table partitioning concepts, details the main partition types (RANGE, LIST, HASH, KEY) with SQL examples, and discusses their performance implications for OLTP and OLAP workloads.
This article explains how InnoDB uses REDO logs to recover a crashed MySQL database, detailing the steps of locating checkpoints, scanning log segments, parsing mini‑transactions, storing records in hash tables, and applying them to restore data consistency.
This article explains the essential role of MySQL REDO logs in ensuring data durability and performance, describes what would happen without logging, details various REDO log record formats, and outlines the multiple moments when InnoDB flushes logs to disk.
This article explains the purpose and management of InnoDB REDO log files, the role of Log Sequence Numbers, the structure of log pages, the MTR physical transaction mechanism, and how checkpoints ensure data integrity in MySQL databases.
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.
The article explains MySQL InnoDB’s four transaction isolation levels, how the default Repeatable Read is realized through MVCC versioning and snapshot reads, and details the three lock types—record, gap, and next‑key—illustrating their roles in preventing dirty, non‑repeatable, and phantom reads.
MySQL 8.0.19 introduces InnoDB ReplicaSet, Router enhancements, numerous SQL syntax additions, optimizer improvements, new Information Schema role views, security features, replication options, X Protocol compression, and a range of deprecations and bug fixes, providing a comprehensive update for database administrators and developers.
This article explains how MySQL executes COUNT(*) and COUNT(column) queries on InnoDB tables, showing example results, detailing each layer's processing steps, the handling of NULL values, and the subtle performance differences between the two forms.
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.
This article analyzes how MySQL/InnoDB applies row-level locks for DELETE and SELECT statements under various index configurations and isolation levels, explaining MVCC, current vs. snapshot reads, two‑phase locking, gap and next‑key locks, and the impact of primary, unique, non‑unique, and missing indexes.
This article explains the performance drawbacks of using UUIDs as primary keys in MySQL InnoDB tables, analyzes their impact on storage, I/O and CPU, and presents several mitigation strategies such as time‑ordered UUIDs, integer mapping, and alternative identifier schemes.
This article explains how MySQL InnoDB implements indexes with B+ trees, describes primary and secondary (clustered and non‑clustered) indexes, the concepts of row lookup, covering indexes, composite indexes, the left‑most prefix rule, index push‑down, and provides practical guidelines for creating and maintaining efficient indexes.
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.
This article presents three common MySQL interview questions—whether query fields are case‑sensitive, how InnoDB implements transactions and logs, and the different binlog formats—along with detailed explanations, configuration examples, and SQL code snippets for each scenario.
This article provides a comprehensive overview of InnoDB's buffer pool, detailing its role as a data cache, the underlying data structures such as instances, chunks, and blocks, the page lifecycle from allocation to flushing, and discusses limitations of the default page‑cleaner implementation along with Percona's enhancements.
This article explains how InnoDB auto‑increment values behave when the highest ID is deleted, after a MySQL restart, and when IDs are manually set, including experiments and guidance on handling overflow.
This article explains how InnoDB stores user tables and indexes in .ibd files, describes the structure of tablespaces, pages, extents, header pages, XDES entries, INODE pages, and file segments, and shows how indexes allocate and free these internal structures as they grow and shrink.
This article explains the different forms of MySQL's COUNT function, compares COUNT(*), COUNT(1) and COUNT(column), reveals engine‑specific optimizations for MyISAM and InnoDB, and provides guidance on which form to use in practice.
This article explains the fundamentals of MySQL indexing, tracing the evolution from binary search trees and balanced AVL trees to B‑trees and B+‑trees, and details how InnoDB implements clustered and non‑clustered indexes, including search processes and performance considerations.
This article explains why MySQL uses B+‑tree indexes by tracing the evolution from binary search trees through balanced trees and B‑trees, detailing the structure of clustered and non‑clustered indexes in InnoDB and illustrating how data is searched using these indexes.
This article explains the internal representation and meaning of MySQL Handler_read_* status variables, demonstrates how InnoDB implements each handler, and shows practical query tests that illustrate how these counters reflect full table scans, index scans, range scans, and join operations.
This article explains the MySQL Clone plugin introduced in version 8.0.17, describes its underlying mechanism, installation and execution steps, outlines usage constraints and scenarios, and presents a detailed performance comparison with LVM snapshots and Xtrabackup under various workloads.
This article explores MySQL's hidden rowid concept, demonstrates how to observe its existence, explains its relationship with primary keys, highlights the risks of primary‑key misuse, and provides a step‑by‑step test that reveals rowid's potential overflow bottleneck.
This article examines a MySQL performance problem where frequent requests become unresponsive, analyzes processlist, system I/O/CPU metrics, and pstack traces to identify InnoDB thread concurrency and concurrency tickets as the root cause, then explains the relevant parameters, configuration guidelines, and a simulated test to demonstrate the solution.
This article explains the new NOWAIT and SKIP LOCKED keywords introduced in MySQL 8.0.1, describes their background, functionality, and internal implementation, and demonstrates their practical impact on lock‑wait scenarios through a fictional flash‑sale case study with code examples.
This article provides a comprehensive guide to MySQL InnoDB tablespace encryption, covering its architecture, supported plugins, configuration steps, key rotation, limitations, encrypted table operations, import/export procedures, and backup/restore methods using mysqlbackup and innobackupex.
The article describes how to migrate a multi‑gigabyte InnoDB table from a pre‑production MySQL 5.7 instance to production using the transportable tablespace feature, detailing the step‑by‑step procedure, performance benefits, and the specific limitations of this method.
This article explains the fundamentals of Multi-Version Concurrency Control (MVCC), compares its implementation in PostgreSQL and InnoDB, and highlights key differences in version storage, transaction handling, vacuuming, and index updates.
MLOG_CHECKPOINT is a mechanism in MySQL InnoDB crash recovery that ensures complete mapping between space IDs and file paths by supplementing missing MLOG_FILE_NAME records, addressing gaps that could occur between checkpoints and subsequent file modifications.
This weekly MySQL community newsletter highlights September 2019 DB‑Engines rankings, offers free Mycat diagnosis, summarizes DBLE and DTLE development updates, and showcases original technical articles on WRITESET parallel replication, InnoDB primary‑key optimization, MySQL 8.0 error‑log enhancements, and query optimization, inviting community feedback.
This article explains how InnoDB stores data using B‑Tree primary keys, why explicit primary keys are essential, and compares ordered versus random insert patterns, illustrating their impact on page fill factor, index size, and query performance with practical sysbench examples.
This article explains how MySQL's InnoDB engine marks rows as deleted without freeing disk space, demonstrates the issue with 100,000 rows, and shows how the OPTIMIZE TABLE or ALTER TABLE commands can rebuild the table to reclaim space and improve performance.
This guide explains how to restore MySQL InnoDB tables and their dictionary metadata after accidental drops, corruption, or disk failures by using the undrop‑for‑innodb toolkit, parsing system tables, and executing a series of command‑line steps to rebuild schema and data.
This article explains MySQL’s fundamental concepts, distinguishing databases from instances, outlines the three‑layer MySQL architecture, and dives deep into InnoDB’s storage hierarchy—from tablespaces, pages, and row formats to .frm/.ibd files, record overflow handling, and the B+‑tree index structures that power fast queries.
This article explains the storage capacity of InnoDB B+‑tree indexes by detailing page size, record size, pointer calculations, tree height effects, and real‑world verification using page‑level metadata, showing that a typical B+‑tree can hold tens of millions of rows with only 1‑3 I/O operations.
This article explains how InnoDB’s 16 KB pages, row size assumptions, and B+‑tree node capacities combine to allow roughly 20 million rows per tree, demonstrates how to calculate tree height from page metadata, and shows why MySQL chooses B+‑trees for primary‑key indexes.
This article explains InnoDB's Multi-Version Concurrency Control (MVCC) architecture, detailing row formats, undo log types, ReadView visibility rules, purge processes, and related concepts such as semi-consistent reads and index condition pushdown, with code examples and diagrams illustrating each mechanism.
This article explains what huge pages are, why they can improve memory performance, provides step‑by‑step instructions to enable them for MySQL on CentOS, shows how to verify the configuration, and shares test results that reveal little performance gain.
This weekly digest highlights the August MySQL ranking surge, Percona XtraBackup 8.0.7 features, free Mycat diagnostic support, DBLE and DTLE weekly reports, and several in‑depth technical articles covering MySQL cloning, group replication, charset performance, ZooKeeper deployment, and InnoDB index construction.
Since MySQL 5.7 InnoDB builds secondary indexes using a bottom‑up, sorted‑index approach, the article explains the three build phases, presents a step‑by‑step B‑tree construction example with SQL code, and discusses the innodb_fill_factor setting, its impact, advantages, and drawbacks.
This article demonstrates how to install and verify the MySQL 8.0.17 clone plugin, create privileged clone users on both donor and recipient servers, configure cloning parameters, execute a full instance clone, set up replication with CHANGE MASTER, and discusses the plugin's limitations such as InnoDB‑only support and DDL restrictions.
This article explains why MySQL can exhaust disk space when implicit temporary tables grow too large, describes how memory limits and storage‑engine choices affect their creation, and provides practical configuration and query‑optimization techniques to avoid service interruptions.
This article explains how the MySQL 8.0 CLONE plugin simplifies creating reliable replica instances by installing the plugin, granting necessary privileges, executing the CLONE SQL statement, monitoring progress via performance_schema, and verifying successful completion of all clone stages.
This article provides a comprehensive overview of InnoDB's file structure, covering physical organization from pages to tablespaces, logical structure including segments and indexes, and file management processes.
The article analyzes a MySQL 5.6 high‑CPU issue triggered by a frequently executed query with many UID values, identifies mutex contention and excessive memory allocation in InnoDB as root causes, and presents a solution of switching the memory allocator to tcmalloc with configuration changes.
This article explains MySQL's lock types—including table and row locks, InnoDB's shared, exclusive, and intention locks—covers lock algorithms such as record, gap, next‑key, and insert‑intention locks, discusses deadlock and blocking issues, and details transaction management, isolation levels, and related configuration parameters.
This article explains how library indexing inspires database indexing, introduces binary search trees, AVL trees, B‑Tree and B+Tree structures, and details InnoDB and MyISAM storage mechanisms, page organization, clustered versus non‑clustered indexes, and practical index‑optimization advice.
This article explains MySQL's locking mechanisms, comparing table‑level and row‑level locks, detailing InnoDB lock types such as shared, exclusive and intention locks, describing lock algorithms like record, gap, next‑key and insert‑intention locks, and covering lock‑related issues, transaction isolation levels, autocommit behavior, and group commit optimization.
This article debunks the myth that COUNT(1) is faster than COUNT(*), explains how MySQL engines handle these functions, compares their performance with COUNT(column), and offers practical tips for avoiding costly full‑table counts.
The article investigates intermittent slow update performance in a Tencent Cloud internal system caused by massive lock_wait_thread contention, analyzes the underlying InnoDB lock mechanisms and thread behavior, implements a fix by disabling lock_wait_suspend_thread triggers, and demonstrates substantial latency reduction through benchmark results.
An InnoDB storage engine sits above the file system and below SQL tools, providing transactions with redo/undo logs, row and table locking, MVCC, B+‑tree and adaptive hash indexes, compression, encryption, checkpointing, multi‑threaded buffering, backup and replication mechanisms, and extensive performance‑monitoring commands.
This article explains how InnoDB’s B+‑tree index stores data, calculates the maximum number of rows a single tree can hold (around 20 million), shows how to determine the tree height from the tablespace file, and answers why MySQL uses B+ trees instead of other structures.
This article explains MySQL's lock mechanisms, comparing MyISAM's table‑level locks with InnoDB's row‑level locks, covering lock types, compatibility, transaction isolation levels, deadlock scenarios, and practical tips to avoid lock contention and improve database concurrency.
An online MySQL deadlock incident is dissected step‑by‑step, covering the error logs, InnoDB lock mechanisms, index structures, transaction isolation, execution plans, root causes, and practical solutions such as index redesign and transaction code adjustments to prevent future deadlocks.
This article explains how MySQL 8.0 InnoDB handles DDL statements, describes the five classification dimensions (Instant, In‑Place, Rebuild Table, Concurrent DML, Metadata‑only), provides examples of each cost level, and offers operational recommendations for safely applying schema changes.
A detailed investigation reveals that a seemingly trivial SELECT * FROM sbtest1 LIMIT 1 becomes slow due to InnoDB's MVCC visibility checks, a growing History List length, and a large number of deleted rows that must be scanned before a visible row is found.
Early Lock Release (ELR) is an optimization that moves lock release before log flushing to improve concurrency, and this article explains its principle, MySQL server‑ and InnoDB‑level implementations, potential side effects such as dirty reads and cascade rollbacks, and its measured performance impact.
This article explains the mechanisms behind MySQL's SELECT COUNT(*) operation, comparing InnoDB's row‑by‑row scan with MyISAM's constant‑time meta count, and explores execution steps, visibility rules, data structures, and performance implications.
This article explains MySQL backup classifications, then details XtraBackup's hot physical backup workflow—including redo‑log handling, global read‑lock usage, step‑by‑step file copying, and restore procedures—while addressing common FAQs and practical tips for ensuring data consistency.
This article explains MySQL's three lock types, the storage engines that use them, detailed MyISAM and InnoDB lock behaviors, gap‑lock pitfalls, optimization tips, and how to monitor lock contention with built‑in status variables.
This article explains MySQL indexes—defining what they are, classifying them by structure, storage, and logic, illustrating InnoDB clustered and secondary index structures with examples, and covering advanced concepts such as covering indexes, the leftmost‑prefix rule, and index condition pushdown to improve query performance.
This article explains why MySQL acquires row locks that persist until a transaction commits, using analogies with multithreading, detailed transaction scenarios, the role of intention locks, and how isolation levels affect lock behavior, all illustrated with code examples and diagrams.
This article explains MySQL's internal architecture and the step‑by‑step execution flow of SELECT and UPDATE statements, covering server layers, storage engines, parsers, optimizers, executors, and the role of binlog and redo log in ensuring data consistency.
This article explains how MySQL 8.0 redesigns the redo log with lock‑free mechanisms, describes the asynchronous thread architecture, details the mtr workflow and synchronization structures, and lists the new tunable parameters that improve write performance and checkpoint efficiency.
The article explains how MySQL's InnoDB engine triggers a semaphore crash after a thread waits over 600 seconds, details the lock locations in dict0dict.c and btr0sea.c, examines the role of Adaptive Hash Index and buffer pool pressure, and introduces a small utility (mysqldba) to help DBAs diagnose and resolve such issues.
This article tests whether MySQL re‑executes an UPDATE that sets a row to its existing values, comparing the behavior under binlog_format=ROW with binlog_row_image=FULL versus binlog_format=STATEMENT, and explains why redundant updates are ignored in ROW mode.
This article explains MySQL InnoDB’s transaction locking mechanisms—including shared, exclusive, intention, gap, and next‑key locks—covers lock compatibility, demonstrates lock behavior with practical examples, explores phantom reads, lost updates, optimistic vs. pessimistic locking, and analyzes common deadlock scenarios.
This article explains the evolution of MySQL's data dictionary from pre‑8.0 scattered metadata to the unified InnoDB dictionary tables in MySQL 8.0, covering storage structures, dictionary caching, information_schema changes, serialized dictionary information (SDI), atomic DDL mechanisms, persistent autoincrement handling, upgrade considerations, and provides practical code examples.
This article details a step‑by‑step MySQL performance tuning process—starting from hardware and software environment description, through parameter‑level optimization of InnoDB buffer pool and redo logs, to additional tweaks—demonstrating up to three‑fold throughput improvements based on sysbench oltp_read_write testing.
An InnoDB deadlock occurs when concurrent UPDATEs on a table with a composite (c,d) index generate conflicting GAP and Next‑Key locks during internal delete‑plus‑insert operations, and the simplest resolution is to drop column d from the index, leaving a single‑column index to prevent the lock clash.
This article examines MySQL bug #89126, explains how creating many tables can overflow the InnoDB table definition cache and cause crashes, illustrates the underlying mechanism with diagrams, and provides configuration advice to prevent the issue.
This article examines a MySQL 5.6 InnoDB deadlock scenario caused by concurrent batch INSERTs, detailing the table schema, test cases, lock acquisition phases, log analysis, lock compatibility matrix, and discusses why typical SQL‑level fixes are limited, suggesting index redesign or data sharding as mitigation.
This article analyzes a MySQL deadlock scenario where concurrent inserts and unique‑key conflicts lead to circular lock waits, explains the underlying lock behavior, walks through the full deadlock log, and proposes using INSERT ON DUPLICATE KEY UPDATE to prevent the issue.
This article examines a real MySQL deadlock case caused by concurrent UPDATE‑then‑INSERT logic, explains the underlying InnoDB lock phases, analyzes the deadlock log step by step, and proposes a safer single‑statement solution using INSERT ON DUPLICATE KEY.
The article examines a MySQL 5.6 deadlock caused by concurrent sessions locking a row via a non‑unique index and the primary key in different orders, explains the circular wait illustrated in the log, and recommends updating rows using the primary key to enforce consistent lock ordering and prevent such deadlocks.
The article analyzes how MySQL’s REPLACE INTO statement can cause deadlocks by acquiring exclusive GAP locks during duplicate‑key handling, illustrates the issue with a step‑by‑step case study of three concurrent transactions, and recommends using pre‑check SELECT‑INSERT or serializing the statements to avoid the problem.
This article walks through a step‑by‑step InnoDB deadlock case where three sessions inserting rows with the same unique key cause a lock‑compatibility conflict, explains the INSERT lock phases, shows the actual deadlock log, and reveals why the deadlock occurs.
This article walks through MySQL InnoDB lock mechanisms, demonstrates how concurrent INSERTs on a unique key generate intention and gap locks, and uses detailed engine status logs to explain why those locks can cause deadlocks.
An in‑depth MySQL case study shows how concurrent gap‑locks under REPEATABLE READ can cause a deadlock when two transactions delete non‑existent rows and then attempt inserts, includes full DDL, log excerpts, step‑by‑step analysis, and practical mitigation strategies.
This article examines two MySQL deadlock scenarios under REPEATABLE READ isolation—one with a non‑unique index and another with a unique index—by presenting table definitions, test data, raw deadlock logs, and step‑by‑step analysis of lock acquisition and waiting, ultimately offering practical recommendations to avoid such deadlocks.
The guide walks through creating a sample InnoDB table, reproducing a delete‑vs‑insert deadlock, and dissecting the SHOW ENGINE INNODB STATUS output to explain lock modes such as X, S, GAP and INSERT‑INTENTION, showing how one transaction’s IX lock and another’s X lock on the same index create a classic deadlock and why understanding these modes is crucial for diagnosis.
