BestHub
Sign in
Tagged articles

InnoDB

905 articles · Page 4 of 10
Aikesheng Open Source Community
Aikesheng Open Source Community
Aug 2, 2023 · Databases

Understanding the Construction Process of Adaptive Hash Index (AHI) in MySQL 8.0.32

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.

Adaptive Hash IndexIndexingInnoDB
0 likes · 27 min read
Understanding the Construction Process of Adaptive Hash Index (AHI) in MySQL 8.0.32
dbaplus Community
dbaplus Community
Jul 30, 2023 · Databases

Why a MySQL Table Can Hold 100 Million Rows Without Slowing Down

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.

B+TreeInnoDBMySQL
0 likes · 14 min read
Why a MySQL Table Can Hold 100 Million Rows Without Slowing Down
ITPUB
ITPUB
Jul 28, 2023 · Databases

Master InnoDB Row Locking: Rules, Examples, and How Locks Are Applied

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.

Database ConcurrencyInnoDBMySQL
0 likes · 10 min read
Master InnoDB Row Locking: Rules, Examples, and How Locks Are Applied
MaGe Linux Operations
MaGe Linux Operations
Jul 23, 2023 · Databases

MySQL Redo, Bin, and Undo Logs Explained: Roles, Differences & Tips

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.

Database LoggingInnoDBMySQL
0 likes · 6 min read
MySQL Redo, Bin, and Undo Logs Explained: Roles, Differences & Tips
Tongcheng Travel Technology Center
Tongcheng Travel Technology Center
Jul 19, 2023 · Databases

InnoDB Storage Model Overview and Internal Structures

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.

B+TreeIndexInnoDB
0 likes · 26 min read
InnoDB Storage Model Overview and Internal Structures
dbaplus Community
dbaplus Community
Jul 13, 2023 · Databases

Understanding InnoDB Row Locks: Rules, Types, and Real-World Examples

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.

InnoDBMySQLSQL
0 likes · 10 min read
Understanding InnoDB Row Locks: Rules, Types, and Real-World Examples
Aikesheng Open Source Community
Aikesheng Open Source Community
Jun 15, 2023 · Databases

How to Shrink the InnoDB System Tablespace (ibdata1) in MySQL

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.

InnoDBMySQLPerformance
0 likes · 13 min read
How to Shrink the InnoDB System Tablespace (ibdata1) in MySQL
Code Ape Tech Column
Code Ape Tech Column
Jun 12, 2023 · Databases

Understanding MySQL DDL Execution and Online DDL Mechanisms

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.

DDLDatabaseInnoDB
0 likes · 16 min read
Understanding MySQL DDL Execution and Online DDL Mechanisms
Su San Talks Tech
Su San Talks Tech
Jun 12, 2023 · Databases

Master MySQL Indexes: From B+ Trees to Index Merge and Optimization

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.

B+TreeIndexInnoDB
0 likes · 31 min read
Master MySQL Indexes: From B+ Trees to Index Merge and Optimization
Aikesheng Open Source Community
Aikesheng Open Source Community
Jun 6, 2023 · Databases

Why Does Inserting a Record that Triggers a Unique Index Conflict Add a Next‑Key Exclusive Lock on the Primary‑Key Supremum Record?

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.

Database InternalsInnoDBMySQL
0 likes · 25 min read
Why Does Inserting a Record that Triggers a Unique Index Conflict Add a Next‑Key Exclusive Lock on the Primary‑Key Supremum Record?
Java Backend Technology
Java Backend Technology
May 30, 2023 · Databases

Why count(*) Slows Down MySQL and How to Optimize It

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.

COUNTClickHouseInnoDB
0 likes · 10 min read
Why count(*) Slows Down MySQL and How to Optimize It
Su San Talks Tech
Su San Talks Tech
May 30, 2023 · Databases

Master MySQL Indexes: From B+ Trees to Index Merge and Optimization

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.

B+TreeDatabase PerformanceIndex
0 likes · 29 min read
Master MySQL Indexes: From B+ Trees to Index Merge and Optimization
Liangxu Linux
Liangxu Linux
May 29, 2023 · Databases

What Happens When MySQL Auto‑Increment IDs Reach Their Limits?

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.

InnoDBMySQLXid
0 likes · 13 min read
What Happens When MySQL Auto‑Increment IDs Reach Their Limits?
Sanyou's Java Diary
Sanyou's Java Diary
May 23, 2023 · Databases

Master MySQL Indexes: From B+ Trees to Index Merge and Optimization

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.

B+TreeDatabaseIndex
0 likes · 31 min read
Master MySQL Indexes: From B+ Trees to Index Merge and Optimization
MaGe Linux Operations
MaGe Linux Operations
May 20, 2023 · Databases

What Happens When MySQL Auto‑Increment IDs Reach Their Limits?

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.

Database InternalsInnoDBMySQL
0 likes · 13 min read
What Happens When MySQL Auto‑Increment IDs Reach Their Limits?
Aikesheng Open Source Community
Aikesheng Open Source Community
May 11, 2023 · Databases

Why MySQL 8.0 Upgrade Can Suddenly Slow Down Queries: The Hidden Lock Table Trap

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.

DatabaseInnoDBLock
0 likes · 11 min read
Why MySQL 8.0 Upgrade Can Suddenly Slow Down Queries: The Hidden Lock Table Trap
ITPUB
ITPUB
May 3, 2023 · Databases

Master MySQL Indexes: Types, Usage, and Optimization Tips

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.

B+TreeDatabaseIndex
0 likes · 19 min read
Master MySQL Indexes: Types, Usage, and Optimization Tips
ITPUB
ITPUB
Apr 19, 2023 · Databases

Can InnoDB’s REPEATABLE READ Truly Prevent Phantom Reads? A Deep Dive

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.

Gap LockInnoDBMVCC
0 likes · 9 min read
Can InnoDB’s REPEATABLE READ Truly Prevent Phantom Reads? A Deep Dive
ITPUB
ITPUB
Apr 16, 2023 · Databases

Why MySQL Indexes Matter: Understanding B+Tree vs B-Tree

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.

B+TreeIndexInnoDB
0 likes · 15 min read
Why MySQL Indexes Matter: Understanding B+Tree vs B-Tree
ITPUB
ITPUB
Apr 11, 2023 · Databases

What Happens on the MySQL Server When the Client Disconnects Abruptly?

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.

InnoDBMySQLSend
0 likes · 8 min read
What Happens on the MySQL Server When the Client Disconnects Abruptly?
政采云技术
政采云技术
Mar 28, 2023 · Databases

Understanding MVCC and Lock Mechanisms in MySQL

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.

InnoDBIsolation LevelsLocks
0 likes · 11 min read
Understanding MVCC and Lock Mechanisms in MySQL
ITPUB
ITPUB
Mar 23, 2023 · Databases

How MySQL InnoDB Implements ACID: Deep Dive into Transactions, Locks, and Isolation

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.

ACIDInnoDBIsolation
0 likes · 10 min read
How MySQL InnoDB Implements ACID: Deep Dive into Transactions, Locks, and Isolation
Shepherd Advanced Notes
Shepherd Advanced Notes
Mar 22, 2023 · Databases

Mastering MySQL Indexes: Advanced Concepts and Practical Tips

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.

Adaptive Hash IndexB+TreeIndex Pushdown
0 likes · 18 min read
Mastering MySQL Indexes: Advanced Concepts and Practical Tips
Aikesheng Open Source Community
Aikesheng Open Source Community
Mar 20, 2023 · Databases

New Features and Enhancements in MySQL 8.0

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.

8.0DatabaseInnoDB
0 likes · 23 min read
New Features and Enhancements in MySQL 8.0
Laravel Tech Community
Laravel Tech Community
Mar 12, 2023 · Databases

Why COUNT(*) May Be Slower Than COUNT(1) in MySQL InnoDB

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.

COUNTIndexInnoDB
0 likes · 10 min read
Why COUNT(*) May Be Slower Than COUNT(1) in MySQL InnoDB
ITPUB
ITPUB
Mar 12, 2023 · Databases

Why MySQL Defaults to Repeatable Read – Inside Transaction Isolation

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.

InnoDBMVCCMySQL
0 likes · 22 min read
Why MySQL Defaults to Repeatable Read – Inside Transaction Isolation
Shepherd Advanced Notes
Shepherd Advanced Notes
Mar 9, 2023 · Databases

Understanding MySQL’s SQL Execution Flow and Transaction Mechanics

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.

ACIDInnoDBMVCC
0 likes · 19 min read
Understanding MySQL’s SQL Execution Flow and Transaction Mechanics
Tencent Database Technology
Tencent Database Technology
Feb 28, 2023 · Databases

Understanding InnoDB Undo Segment States and MySQL XA Transaction Crash Recovery (MySQL 8.0.30)

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.

Crash RecoveryDatabaseInnoDB
0 likes · 11 min read
Understanding InnoDB Undo Segment States and MySQL XA Transaction Crash Recovery (MySQL 8.0.30)
ITPUB
ITPUB
Feb 26, 2023 · Databases

Why InnoDB Stores Primary Keys in Secondary Index Leaves – A Deep Dive

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.

Covering IndexDatabaseInnoDB
0 likes · 7 min read
Why InnoDB Stores Primary Keys in Secondary Index Leaves – A Deep Dive
Liangxu Linux
Liangxu Linux
Feb 18, 2023 · Databases

Why Does One MySQL UPDATE Block While Another Doesn’t? Deep Dive into InnoDB Locks

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.

B+TreeInnoDBMySQL
0 likes · 8 min read
Why Does One MySQL UPDATE Block While Another Doesn’t? Deep Dive into InnoDB Locks
ITPUB
ITPUB
Feb 17, 2023 · Databases

Master MySQL Locking: From Row Locks to Deadlocks and How to Prevent Them

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.

DeadlockInnoDBIsolation Level
0 likes · 22 min read
Master MySQL Locking: From Row Locks to Deadlocks and How to Prevent Them
ITPUB
ITPUB
Feb 9, 2023 · Databases

Inside InnoDB: How MySQL Stores Data, Row Formats, and Indexes Explained

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.

B+TreeDatabase StorageIndexing
0 likes · 24 min read
Inside InnoDB: How MySQL Stores Data, Row Formats, and Indexes Explained
ITPUB
ITPUB
Jan 26, 2023 · Databases

What Locks Does SELECT … FOR UPDATE Acquire Under Different Isolation Levels?

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.

Database ConcurrencyInnoDBIsolation Levels
0 likes · 16 min read
What Locks Does SELECT … FOR UPDATE Acquire Under Different Isolation Levels?
Tencent Database Technology
Tencent Database Technology
Jan 18, 2023 · Databases

In‑depth Analysis of MySQL InnoDB Buffer Pool Architecture and Performance Optimizations

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.

CachingDatabase PerformanceInnoDB
0 likes · 47 min read
In‑depth Analysis of MySQL InnoDB Buffer Pool Architecture and Performance Optimizations
Sanyou's Java Diary
Sanyou's Java Diary
Jan 12, 2023 · Databases

Master MySQL Transaction Isolation: From READ UNCOMMITTED to SERIALIZABLE

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.

DatabaseInnoDBMVCC
0 likes · 20 min read
Master MySQL Transaction Isolation: From READ UNCOMMITTED to SERIALIZABLE
dbaplus Community
dbaplus Community
Jan 8, 2023 · Databases

Mastering MySQL Row‑Level Locks: Rules, Types, and How to Inspect Them

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.

Database ConcurrencyInnoDBLock Types
0 likes · 45 min read
Mastering MySQL Row‑Level Locks: Rules, Types, and How to Inspect Them
dbaplus Community
dbaplus Community
Jan 4, 2023 · Databases

Mastering MySQL Indexes: Costs, Pitfalls, and Best Practices

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.

InnoDBMySQLSQL
0 likes · 15 min read
Mastering MySQL Indexes: Costs, Pitfalls, and Best Practices
DeWu Technology
DeWu Technology
Dec 21, 2022 · Databases

InnoDB Storage Architecture and Transaction Persistence Mechanisms

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.

InnoDBMySQLPersistence
0 likes · 33 min read
InnoDB Storage Architecture and Transaction Persistence Mechanisms
Liangxu Linux
Liangxu Linux
Dec 5, 2022 · Databases

How Does MySQL Store a Row? Inside InnoDB Files, Pages, and Row Formats

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.

Database StorageExtentInnoDB
0 likes · 22 min read
How Does MySQL Store a Row? Inside InnoDB Files, Pages, and Row Formats
IT Xianyu
IT Xianyu
Dec 2, 2022 · Databases

How MySQL InnoDB Implements ACID: Locks, MVCC, and Logging

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.

ACIDInnoDBLock
0 likes · 14 min read
How MySQL InnoDB Implements ACID: Locks, MVCC, and Logging
Architecture Digest
Architecture Digest
Nov 29, 2022 · Databases

MySQL InnoDB Deadlock Analysis and Resolution for a Video‑Conference Service

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.

DatabaseDeadlockDebugging
0 likes · 21 min read
MySQL InnoDB Deadlock Analysis and Resolution for a Video‑Conference Service
dbaplus Community
dbaplus Community
Nov 27, 2022 · Databases

Unraveling MySQL InnoDB Deadlocks in Video Conference Sessions

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.

DatabaseDeadlockInnoDB
0 likes · 22 min read
Unraveling MySQL InnoDB Deadlocks in Video Conference Sessions
Su San Talks Tech
Su San Talks Tech
Nov 16, 2022 · Databases

Why count(*) Is Slow in MySQL InnoDB and How to Speed It Up

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.

COUNTClickHouseInnoDB
0 likes · 12 min read
Why count(*) Is Slow in MySQL InnoDB and How to Speed It Up
ITPUB
ITPUB
Nov 15, 2022 · Databases

How MySQL Executes an UPDATE: Inside InnoDB Pages, Buffer Pool, and Logs

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.

InnoDBMySQLRedo Log
0 likes · 19 min read
How MySQL Executes an UPDATE: Inside InnoDB Pages, Buffer Pool, and Logs
ITPUB
ITPUB
Nov 13, 2022 · Databases

Why Does MySQL Hang on Startup? Deep Dive into InnoDB Truncate Bug and Fix

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.

DebuggingInnoDBMySQL
0 likes · 16 min read
Why Does MySQL Hang on Startup? Deep Dive into InnoDB Truncate Bug and Fix
ITPUB
ITPUB
Nov 3, 2022 · Databases

How to Diagnose and Resolve MySQL InnoDB Deadlocks in Production

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.

Database TroubleshootingDeadlockInnoDB
0 likes · 9 min read
How to Diagnose and Resolve MySQL InnoDB Deadlocks in Production
Tencent Database Technology
Tencent Database Technology
Oct 27, 2022 · Databases

Analysis of Unexpected InnoDB Lock Waits Caused by Missing End‑Range Push‑Down and Semi‑Consistent Reads

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.

InnoDBLock WaitMySQL
0 likes · 12 min read
Analysis of Unexpected InnoDB Lock Waits Caused by Missing End‑Range Push‑Down and Semi‑Consistent Reads
ITPUB
ITPUB
Oct 10, 2022 · Databases

Inside MySQL Record Buffer: How Server and Storage Engine Exchange Data

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.

InnoDBMySQLrecord buffer
0 likes · 14 min read
Inside MySQL Record Buffer: How Server and Storage Engine Exchange Data
macrozheng
macrozheng
Sep 30, 2022 · Databases

Why MySQL count(*) Slows Down on Large Tables and How to Optimize It

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.

COUNTInnoDBMyISAM
0 likes · 13 min read
Why MySQL count(*) Slows Down on Large Tables and How to Optimize It
JD Cloud Developers
JD Cloud Developers
Sep 27, 2022 · Databases

Unveiling MySQL’s Inner Workings: From SQL Query to InnoDB Storage Engine

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.

Database ArchitectureInnoDBMySQL
0 likes · 18 min read
Unveiling MySQL’s Inner Workings: From SQL Query to InnoDB Storage Engine
Su San Talks Tech
Su San Talks Tech
Sep 19, 2022 · Databases

How InnoDB Locates the First Record in a B‑Tree Scan Range

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.

B-TreeIndex ScanInnoDB
0 likes · 28 min read
How InnoDB Locates the First Record in a B‑Tree Scan Range
DaTaobao Tech
DaTaobao Tech
Sep 16, 2022 · Databases

Investigation of Hanging Transactions and Lock Wait Timeout in MySQL InnoDB

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.

DebuggingHangingTransactionInnoDB
0 likes · 14 min read
Investigation of Hanging Transactions and Lock Wait Timeout in MySQL InnoDB
Aikesheng Open Source Community
Aikesheng Open Source Community
Sep 6, 2022 · Databases

Understanding MySQL Crash Recovery and the Role of Redo Logs

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.

Crash RecoveryDoublewriteInnoDB
0 likes · 28 min read
Understanding MySQL Crash Recovery and the Role of Redo Logs