Tagged articles
Database Internals
99 articles · Page 1 of 1
This article walks through a big-tech interview question about finding top 10 hot items from 50M MySQL rows, revealing why naive SQL fails, how to clarify time windows and real-time needs, and explores solutions from pre-aggregated snapshots and partitioned parallel queries to the Space-Saving algorithm for approximate top-K with bounded memory.
Approximate AlgorithmsDatabase InternalsMySQL
0 likes · 14 min read

This article explains how Hash Join assigns build and probe sides, why the smaller table typically becomes the build side, how outer joins affect side selection, and the differences from Sort-Merge Join, with a concrete SQL example.
Build SideDatabase InternalsHash Join
0 likes · 6 min read

This article explains Lance's Stable Row ID feature, which assigns persistent logical identifiers to rows so they survive compaction and updates, reducing index maintenance costs at the expense of extra mapping storage and lookup overhead.
Database InternalsLanceStable Row ID
0 likes · 13 min read

Lance uses Multi-Version Concurrency Control to provide ACID guarantees, creating immutable table versions on each commit and employing atomic storage primitives, rebase logic, and retry mechanisms to handle concurrent writes, conflict detection, and resolution across multiple transaction types.
Database InternalsLanceMVCC
0 likes · 16 min read

MySQL’s ORDER BY DESC queries run slower than ASC because InnoDB stores records in a forward‑oriented singly linked list, using an infimum‑supremum structure, a page directory, and N_OWNED fields, which make backward scans O(n) while forward scans remain O(1).
Database InternalsIndex ScanInnoDB
0 likes · 9 min read

This article walks through the complete lifecycle of a MySQL SELECT statement, detailing how the client, connector, optional query cache, parser, optimizer, and executor interact to transform SQL text into an execution plan and finally return results.
Database InternalsExecutorMySQL
0 likes · 13 min read

This article explains the complete lifecycle of a MySQL UPDATE—from the initial log buffer, through redo and undo logs, buffer‑pool dirty pages, checkpoint thresholds, binary logging, and the innodb_flush_log_at_trx_commit setting—highlighting how InnoDB guarantees durability, consistency, and crash recovery.
Buffer PoolDatabase InternalsInnoDB
0 likes · 8 min read

This article examines why a SELECT … FOR UPDATE query that appears to use a covering index in MySQL actually performs a table‑row lookup, detailing indirect evidence from performance_schema locks and direct proof through InnoDB source code, and explains the necessity of accessing the primary key for transaction isolation.
Database InternalsIndex ScanInnoDB
0 likes · 9 min read

This article explains the three‑stage execution flow of a Redis command—connection establishment, command processing, and result return—detailing how the single‑threaded reactor pattern drives event handling, how commands are parsed and dispatched, and how the output is sent back, with code snippets and a comparison to Netty’s multithreaded reactor.
Command ExecutionDatabase InternalsI/O multiplexing
0 likes · 50 min read

This article provides a comprehensive index of a MySQL internals series covering the transaction, lock, and undo modules, detailing each sub‑topic such as transaction initialization, two‑phase commit, various lock types, deadlock handling, and undo log management across numerous linked articles.
Database InternalsInnoDBMySQL
0 likes · 7 min read

The article explains how InnoDB handles transaction commit by modifying Insert and Update Undo segment states, generating a transaction commit number, adding rollback segments to the purge queue, and finally cleaning or caching Undo segments based on their usage and state.
Database InternalsInnoDBMySQL
0 likes · 11 min read

This article explains how to read and parse the Undo log generated by a DELETE operation in MySQL 8.0.32 InnoDB, covering preparation, log extraction, detailed field parsing, locating the primary index record, and the step‑by‑step rollback of secondary and primary index records.
Database InternalsInnoDBMySQL
0 likes · 10 min read

This article explains how MySQL 8.0 InnoDB generates and stores Delete Undo logs, detailing the log structure, field meanings, type_flag and info_bits decoding, address composition via DB_ROLL_PTR, and provides concrete SQL examples and shell calculations to illustrate the process.
Database InternalsDelete OperationInnoDB
0 likes · 11 min read

After publishing 50 installments of the 'MySQL Core Modules' series covering transactions, locks, and undo, the author expresses gratitude to readers and the community, outlines upcoming articles on Undo, MVCC, and Redo, and reviews past content while planning a compiled book.
Database InternalsMVCCMySQL
0 likes · 7 min read

This article explains how MySQL 8.0.32's InnoDB engine generates, reads, and parses Undo logs for an UPDATE operation, and details the step‑by‑step process of locating the primary record, constructing a rollback record, and restoring both secondary and primary index entries.
Database InternalsInnoDBMySQL
0 likes · 14 min read

This article explains how MySQL 8.0.32 InnoDB generates and stores Update undo logs, detailing the log format, the meaning of each field, how to interpret the log address, and provides concrete SQL examples and shell calculations for practical insight.
Database InternalsInnoDBMySQL
0 likes · 11 min read

This article explains the complete InnoDB undo‑log rollback process on MySQL 8.0, covering preparation, reading and parsing undo logs, constructing primary‑key index records, and the detailed steps for deleting secondary and primary index entries during a transaction rollback.
Database InternalsInnoDBMySQL
0 likes · 12 min read

This article explains how MySQL 8.0.32 InnoDB generates and stores Insert Undo logs, detailing their binary format, field meanings, example values, and how the hidden DB_ROLL_PTR field encodes the log address, with accompanying SQL and shell examples.
Database InternalsInnoDBMySQL
0 likes · 6 min read

This article explains the four‑layer logical structure of InnoDB Undo logs—Undo pages, Undo segments, rollback segments, and Undo tablespaces—detailing their roles, management, related system variables, and how they affect transaction rollback and storage in MySQL 8.0.32.
Database InternalsInnoDBMySQL
0 likes · 9 min read

This article provides a concise English recap of the 27 previously published InnoDB lock module articles, covering theoretical concepts such as table and row locks, lock waiting, deadlock handling, and practical scenarios like lock behavior during inserts and duplicate key operations.
ConcurrencyDatabase InternalsInnoDB
0 likes · 6 min read


ITPUBNov 11, 2024 · Databases
This article examines how MySQL InnoDB decides whether to modify index blocks during UPDATE statements, walks through the internal mysql_update workflow, shows debugging with GDB, explains three test scenarios, and validates the behavior by inspecting block LSNs with the innblock tool.
Database InternalsIndex UpdateInnoDB
0 likes · 18 min read

This article provides a comprehensive overview of MySQL 8.0's concurrency control mechanisms, detailing table‑level MDL locks, server‑ and engine‑layer table locks, page‑level B+Tree locking, and row‑level lock types with code examples and real‑world deadlock scenarios.
Database InternalsInnoDBMySQL
0 likes · 28 min read

This article explains the core components of MySQL's InnoDB storage engine—including undo log, redo log, buffer pool, change buffer, and binlog—detailing their roles in data writing, crash recovery, MVCC, and master‑slave synchronization, and describing related flushing strategies and LRU management.
Buffer PoolDatabase InternalsInnoDB
0 likes · 11 min read

The article analyzes a MySQL 5.7 replication bug where, after consecutive crashes, the slave ends up with more GTIDs than the master because the last binlog’s GTIDs are not persisted, and demonstrates the problem using GDB debugging and step‑by‑step reproduction.
Crash RecoveryDatabase InternalsGTID
0 likes · 8 min read

This article explains the inner workings of InnoDB’s lock‑wait timeout thread, detailing how it scans waiting slots, identifies timed‑out transactions, processes timeout logic, and notifies the affected transactions, while also covering related data structures and configuration variables.
Database InternalsInnoDBMySQL
0 likes · 8 min read

This article explains the slow‑path row‑locking algorithm used by InnoDB in MySQL 8.0.32, detailing how the engine checks whether a transaction already holds a lock, determines if it must wait, reuses existing lock structures, and allocates new ones when necessary.
Database InternalsInnoDBMySQL
0 likes · 15 min read

This article explains the structure and components of InnoDB's lock module, including the global lock_sys object, its hash tables, latch mechanisms, waiting thread slots, and related attributes, with detailed code examples and diagrams to illustrate how row and table locks are managed.
ConcurrencyDatabase InternalsInnoDB
0 likes · 15 min read

This article examines the shared and distinct fields of InnoDB's table‑lock and row‑lock structures in MySQL 8.0.32, explains the layout of the lock_t, lock_table_t and lock_rec_t structs, decodes the type_mode bit‑field, and discusses how InnoDB merges compatible row‑locks using a bitmap memory area.
ConcurrencyDatabase InternalsInnoDB
0 likes · 13 min read

This article explains the architecture of MySQL's InnoDB storage engine locks, covering server‑level metadata locks, table‑level shared and exclusive locks, intent locks, and the three modes of AUTO‑INC locks, with practical SQL examples and performance‑schema queries.
Database InternalsInnoDBIntent Locks
0 likes · 16 min read

This article explains how InnoDB implements savepoints in MySQL 8.0.32, covering undo log sequencing, the savepoint structures in the server, binlog, and InnoDB layers, the process of locating and deleting duplicate savepoints, and the three-step procedure for creating a new savepoint.
Database InternalsInnoDBMySQL
0 likes · 8 min read

This article explains the internal steps InnoDB performs when committing a transaction, covering undo segment caching, generation of the transaction commit number, state changes of insert and update undo segments, history‑list insertion, and the final reinitialisation of the transaction object.
Database InternalsInnoDBMySQL
0 likes · 12 min read

This article explains how InnoDB in MySQL 8.0.32 distinguishes user and internal transactions, details the allocation of transaction objects from transaction pools, describes initialization steps, and shows how transaction objects are linked into appropriate transaction lists.
Database InternalsInnoDBMySQL
0 likes · 8 min read

This article explains how InnoDB in MySQL 8.0.32 manages transaction objects through a transaction pool and a single pool manager, detailing pool creation, initialization of a limited number of objects, and the on‑demand expansion strategy to improve performance under high concurrency.
Database InternalsInnoDBMySQL
0 likes · 8 min read


政采云技术Dec 27, 2023 · Databases
This article explains MySQL's binary log (binlog), a binary format file that records SQL statements for database updates, its role in master-slave replication, crash recovery, and detailed implementation mechanisms including cache management and event writing processes.
Crash RecoveryDatabase InternalsDatabase Replication
0 likes · 8 min read

This article walks through the complete lifecycle of a MySQL query—from the client request, through connection handling, parsing, optimization, execution, and storage engine processing—illustrating each component with diagrams, code snippets, and a detailed analysis of execution order for both simple and join queries.
Database InternalsMySQLQuery Optimization
0 likes · 12 min read

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.
Database InternalsIndexInnoDB
0 likes · 14 min read

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

This article explores MySQL character set implementation by analyzing system tables (CHARACTER_SETS and COLLATIONS) in information_schema and tracing internal source code structures like CHARSET_INFO, initialization logic, and client connection handling.
Client-Server ProtocolDatabase InternalsMySQL
0 likes · 6 min read

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


ITPUBApr 30, 2023 · Databases
This article provides a detailed walkthrough of Redis's internal architecture, covering the client module, event‑driven networking, command processing, memory management, persistence strategies, high‑availability features, and the low‑level data structures such as redisServer, redisDb, dict, dictEntry, and redisObject that power its fast key‑value storage.
C languageData StructuresDatabase Internals
0 likes · 12 min read


ITPUBNov 13, 2022 · Databases
This article explains MySQL's client‑server architecture and walks through the three main stages—connection handling, query parsing and optimization, and storage engine selection—detailing protocols, authentication, cache behavior, optimizer mechanics, and engine characteristics with practical commands and examples.
Connection HandlingDatabase InternalsMySQL
0 likes · 21 min read

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.
Database InternalsInnoDBMySQL
0 likes · 13 min read

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.
Database InternalsInnoDBMySQL
0 likes · 12 min read

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.
ConcurrencyDatabase InternalsInnoDB
0 likes · 20 min read

This article explains MySQL's internal execution process—including the connector, cache, parser, optimizer, and executor—describes privilege tables and verification, discusses query caching removal, outlines logical SQL execution order, and provides practical tips for ordering WHERE conditions to improve performance.
Database InternalsMySQLPerformance Tuning
0 likes · 12 min read

This article explores MySQL's client‑server interaction, connection methods, query parsing and optimization, InnoDB storage engine design, data page layout, BufferPool structures, and the free, flush, and LRU linked lists, including their challenges and optimization techniques.
BufferPoolDatabase InternalsInnoDB
0 likes · 13 min read

This article analyzes the excessive memory usage and blocking issues in Redis master‑slave replication, explains how the upcoming Redis 7.0 shared replication buffer solves these problems, and details the design, implementation, and key considerations of the shared buffer mechanism.
Database InternalsReplicationShared Buffer
0 likes · 14 min read

This article explains Redis’s original single‑threaded event‑driven architecture, its performance bottlenecks, and how versions 4.0 and 6.0 introduced Lazy Free and multithreaded I/O mechanisms—including asynchronous key deletion, background freeing, and I/O thread pools—to improve scalability while outlining their implementation details, limitations, and comparisons with Tair.
Database InternalsLazy FreeMultithreaded I/O
0 likes · 15 min read


政采云技术Jan 20, 2022 · Databases
This article explains InnoDB's Multi-Version Concurrency Control (MVCC) mechanism, detailing undo log, readview, hidden columns, visibility algorithms for RR and RC isolation levels, and includes source code snippets illustrating read view assignment and visibility checks.
Database InternalsInnoDBMVCC
0 likes · 12 min read

This article explains the MySQL InnoDB undo log—its definition, role in transaction rollback and MVCC, when it is created, where and how it is stored across different MySQL versions, the internal tablespace and rollback‑segment structures, log types, and real‑world examples of its usage.
Database InternalsInnoDBMVCC
0 likes · 15 min read

This article dissects MySQL's InnoDB storage engine by first exposing the internal page layout, then explaining the clustered index organization, and finally detailing the slot mechanism used for intra‑page queries, while illustrating insertion strategies and space‑reclamation techniques.
Database InternalsInnoDBMySQL
0 likes · 11 min read

This article analyzes MySQL 8.0.25 source code, detailing the server architecture, parser reconstruction, prepare/rewrite stages, the optimizer transformations, and the new hypergraph optimizer, while also comparing these mechanisms with PostgreSQL’s processing pipeline.
Database InternalsHypergraphMySQL
0 likes · 12 min read

This article explains the fundamentals of LSM‑Tree storage engines, introduces SSTable concepts, describes how to build and maintain ordered log files, and provides a complete Java implementation of a tiny key‑value store with code examples and design analysis.
Database InternalsJavaLSM-tree
0 likes · 17 min read

This article examines why MySQL’s KILL command often leaves a connection in a prolonged “Killed” state, analyzes the 5.7 source‑code flow, explains socket closure, thread flag handling, condition‑variable notifications, and reproduces real‑world cases that illustrate the underlying causes.
Database InternalsMySQLdebugging
0 likes · 24 min read

This article demonstrates how PostgreSQL 14's pageinspect extension adds functions to examine GIST index internal pages, provides step‑by‑step SQL examples for creating a test table and index, and shows how to query opaque page info and item data using the new functions.
Database InternalsGiST indexPostgreSQL
0 likes · 4 min read

This article explains Redis's original single‑threaded event loop, the performance problems it causes, and how Redis 4.0 introduced Lazy Free and Redis 6.0 added multithreaded I/O, comparing the implementation with Tair's more elegant multithreaded design.
ConcurrencyDatabase InternalsLazy Free
0 likes · 16 min read

This article explains in detail how ClickHouse processes a user‑submitted SQL query, covering the server’s request handling, parsing, query rewrite, optimization, interpreter execution, and result transmission, while illustrating key source code snippets and architectural components.
BackendClickHouseDatabase Internals
0 likes · 18 min read

The article explains how massive DROP TABLE operations in PostgreSQL master‑slave replication trigger costly buffer‑invalidation loops that cause severe lag, and describes extracting this step into a separate subprocess with a shared hash table, cutting replication lag from over 400 GB to about 10 MB—a 30,000‑fold speedup.
Buffer ManagementDatabase InternalsDatabase Replication
0 likes · 7 min read

This comprehensive guide explains MySQL's server and storage engine layers, SQL execution order, BinLog/RedoLog/UndoLog mechanisms, index structures and design principles, transaction isolation levels, lock types, MVCC, buffer pool behavior, table slimming, join strategies, statistics, random queries, and practical optimization techniques for high‑performance database operations.
Database InternalsMySQLSQL
0 likes · 43 min read

LevelDB stores data using an in‑memory Memtable that flushes to immutable tables and disk‑based SSTables, writes are logged then batched and applied through a writer queue, reads check Memtable, immutable Memtable, then SSTables, and background compactions merge tables to improve read performance and reclaim space.
Database InternalsLSM-treeLevelDB
0 likes · 16 min read


ITPUBNov 23, 2020 · Databases
The article examines the hidden lock‑contention issue in InnoDB’s Adaptive Hash Index (AHI) observed during high‑concurrency sysbench runs, explains why multiple threads block on the AHI hash table’s exclusive lock, and presents a lightweight fallback optimization that checks the lock before building AHI, reducing lock wait time and stabilizing QPS performance.
Adaptive Hash IndexDatabase InternalsInnoDB
0 likes · 9 min read

This article provides a comprehensive overview of MySQL’s logging mechanisms—including Binlog, Undo log, Redo log, and Change Buffer—explaining their concepts, roles in replication and crash‑recovery, recording formats, flush timing, two‑phase commit, checkpoint handling, and how they interact during data modifications.
Change BufferDatabase InternalsInnoDB
0 likes · 22 min read

This article explains how MySQL InnoDB locates and deletes a specific row by describing the buffer‑pool lookup, B‑tree page traversal, page‑directory binary search, linear record scan, the compact physical record format, and the handling of signed integers and next‑record offsets, with GDB debugging examples.
Compact Row FormatDatabase InternalsInnoDB
0 likes · 25 min read

This article explains MySQL 8.0.20's lexical analysis, describing the lexer’s role, the state‑machine implementation, step‑by‑step debugging with LLDB on macOS, the init_state_maps function, key source code snippets, and the optimizations introduced in this version.
Database InternalsMySQLdebugging
0 likes · 12 min read

This article explains how MySQL parses SQL statements using Yacc, detailing the grammar structure, definition and rule sections, relevant source files, parse‑tree node declarations, common error messages, and the call stacks involved in both parsing and pre‑parsing phases.
Database InternalsMySQLParse Tree
0 likes · 7 min read

This article demonstrates how to use MySQL's DBUG debugging tool to observe the creation of a table, track changes in internal statistics tables such as innodb_index_stats, and reveal the underlying operations and stored procedures that update these tables.
DBUGDatabase InternalsMySQL
0 likes · 3 min read

This article explains MySQL’s internal workflow, covering the Server layer components, Storage Engine layer, connection handling, query cache behavior, lexical and syntax parsing, optimization decisions, and execution steps, including tips for long‑connection management and common pitfalls.
Connection ManagementDatabase InternalsMySQL
0 likes · 9 min read

This article explains the internal implementations of Redis’s five major data structures—simple dynamic strings (SDS), linked lists, dictionaries, skiplists, integer sets, and ziplists—detailing their definitions, memory layouts, key characteristics, and how Redis leverages them for high‑performance storage and retrieval.
Data StructuresDatabase InternalsLinked List
0 likes · 12 min read

This article explains the internal workflow of MySQL's multi‑threaded slave (MTS) parallel replication, covering the coordination thread, logical‑clock sub‑mode, worker allocation, waiting states, key configuration parameters, and illustrative code snippets and log analysis.
Database InternalsGTIDMTS
0 likes · 13 min read

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.
Database InternalsInnoDBMySQL
0 likes · 22 min read

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.
Database InternalsInnoDBMySQL
0 likes · 22 min read

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.
Buffer PoolDatabase InternalsInnoDB
0 likes · 15 min read

This article explains how MySQL processes a full‑table‑scan query, detailing the construction of read‑sets, template creation, cursor positioning, row conversion to MySQL format, where‑clause filtering, and subsequent row iteration, while highlighting the performance impact of the number of selected fields.
Database InternalsMySQLPerformance Optimization
0 likes · 11 min read

This article explains the internal calculation of the Time column shown by MySQL's SHOW PROCESSLIST command, covering the source functions, overloads of THD::set_time, various scenarios that affect the result—including replication lag, manual timestamp settings, and idle sessions—and why Percona and official builds may display different values.
Database InternalsMySQLReplication
0 likes · 10 min read

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.
Database InternalsInnoDBPercona
0 likes · 16 min read

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.
Database InternalsInnoDBMySQL
0 likes · 10 min read

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.
Database InternalsInnoDBMLOG_CHECKPOINT
0 likes · 7 min read

This article explains MySQL's write‑set based parallel replication, detailing how writeset hashes are generated, stored in a historical map, and used to adjust the last‑commit value for improved parallel replay, including configuration parameters, code examples, and the impact of different dependency‑tracking modes.
Database InternalsMySQLTransaction Dependency
0 likes · 15 min read

This article explains MySQL's Instant Add Column feature, describing the traditional costly column‑addition process, the instant method’s internal mechanics, how data is read and written before and after the change, and the practical constraints that limit its usage.
DDLDatabase InternalsInstant Add Column
0 likes · 7 min read

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.
Database InternalsInnoDBMySQL
0 likes · 11 min read

This article provides a comprehensive technical overview of TiDB's architecture, its SQL optimizer—including logical and physical optimization phases, rule‑based transformations, join ordering, and statistics collection—and outlines future work to improve query planning stability and performance.
Database InternalsDistributed DatabaseQuery Planning
0 likes · 16 min read

After an unexpected crash, InnoDB restores data using a multi‑stage process that first replays redo logs based on checkpoints, then leverages binlog and undo logs to resolve uncommitted transactions, with detailed steps, optimizations, and checkpoint handling explained.
Database InternalsInnoDBMySQL
0 likes · 12 min read

This article provides a comprehensive overview of MySQL's lock architecture, covering the two‑layer server/storage engine design, metadata (MDL) locks, InnoDB row and gap locks, transaction isolation levels, and practical debugging techniques using source‑code entry points and real‑world examples.
Database InternalsInnoDBLocks
0 likes · 18 min read

This article presents a comprehensive walkthrough of MySQL's two‑layer architecture, metadata (MDL) locks, InnoDB lock types—including gap and record locks—and practical debugging techniques, helping readers understand lock acquisition, release points, and source‑code entry points for effective database operation and development.
Database InternalsInnoDBLock Mechanisms
0 likes · 19 min read

This article explains Redis's dictionary structure, the conditions that trigger rehashing, the step‑by‑step incremental rehash process, and provides the core C implementation code, illustrating how Redis maintains high performance while resizing its hash tables.
C++Database InternalsHash Table
0 likes · 8 min read

This article explains MySQL's online DDL mechanism, detailing the copy and inplace methods, the three execution phases, the allocation and structure of row_log_t, how log entries are written during DDL, and the replay process that applies those logs to the rebuilt table.
Database InternalsInnoDBMySQL
0 likes · 14 min read

This article explains how upgrading to MySQL 5.7.18 caused severe performance drops on partitioned tables due to a regression that locks too many rows during single‑row updates, details the reproduction steps, shows the InnoDB lock inspection, pinpoints the faulty code path, and confirms the bug with further tests.
Database InternalsInnoDBLock Regression
0 likes · 9 min read


ITPUBJun 23, 2017 · Databases
This article provides an in‑depth technical walkthrough of MySQL InnoDB's Buffer Pool, covering its core data structures, instance layout, LRU and Flush list management, memory allocation strategies, read‑ahead/write‑ahead mechanisms, double‑write buffering, and the specialized threads that keep the pool efficient.
Buffer PoolDatabase InternalsInnoDB
0 likes · 39 min read


ITPUBNov 7, 2016 · Databases
This article provides an in‑depth technical walkthrough of MySQL’s MEM_ROOT memory allocator, covering the key macros, the MEM_ROOT and USED_MEM structures, initialization and allocation routines, pointer‑manipulation logic, and the heuristic algorithm that grows block sizes as usage increases.
C++Database InternalsMEM_ROOT
0 likes · 8 min read


ITPUBSep 5, 2016 · Databases
This article explains how Oracle protects shared resources with enqueue queues, details the naming of TM queue locks per table, describes the resource structure (KSQRS) that tracks owners, waiters and converters, and shows how hash tables and related parameters manage these structures efficiently.
Database InternalsEnqueueHash Table
0 likes · 11 min read


ITPUBAug 17, 2016 · Databases
This article explains how Oracle protects shared resources using enqueue queues, details the naming of queue identifiers like TM, describes the creation and composition of resource structures in the SGA, and examines the hash‑bucket mechanism that locates these structures while highlighting relevant parameters and diagnostic queries.
Database InternalsEnqueue LocksOracle
0 likes · 13 min read


ITPUBJun 23, 2016 · Databases
This article explains two practical techniques for identifying Oracle hidden parameters—querying internal views like GV$PARAMETER and X$ tables, and using AUTOTRACE to inspect execution plans—so DBAs can reveal undocumented settings, their defaults, and current values.
Database InternalsHidden ParametersOracle
0 likes · 5 min read

This article explains InnoDB row lock types, including shared and exclusive locks and GAP variants, describes how locks split, inherit, and migrate during B‑tree operations, provides code examples and SQL demonstrations, and discusses related bugs and lock compatibility.
Database InternalsInnoDBLock Splitting
0 likes · 8 min read


ITPUBMay 31, 2016 · Databases
This article walks through PostgreSQL's complete SQL execution pipeline, detailing each component—from the Main entry point and Postmaster process to parsing, traffic coordination, query rewriting, plan generation, and execution—while comparing its process architecture to other database systems.
Database InternalsPostgreSQLPostmaster
0 likes · 6 min read


21CTOApr 1, 2016 · Databases
This article details the evolution of MySQL's Metadata Lock subsystem, the implementation of lock‑free hash tables and fast‑path locking in MySQL 5.7, the hash_scan algorithm used in replication, as well as performance enhancements in TokuDB 7.5.0 and MariaDB's small‑LIMIT filesort optimization.
Database InternalsHash ScanMetadata Lock
0 likes · 18 min read


21CTOMar 2, 2016 · Databases
This article examines MySQL’s InnoDB full‑text indexing from MySQL 5.6 onward, detailing supported search modes, the structure of auxiliary index files, the lifecycle of DML operations, transaction handling, cache synchronization, optimization procedures, background threads, monitoring tables, stop‑word configuration, and the built‑in n‑gram parser.
Database InternalsInnoDBMySQL
0 likes · 23 min read

This article provides a comprehensive technical overview of MySQL InnoDB's internal page structures—including compressed, system, external, encrypted, and R‑TREE pages—along with detailed explanations of the IO subsystem, double‑write buffer, temporary tablespaces, and buffer pool memory management mechanisms.
Buffer PoolDatabase InternalsIO Subsystem
0 likes · 25 min read

This article provides a comprehensive overview of InnoDB's transaction lock mechanisms, covering row‑level lock types, table‑level locks, lock management procedures, deadlock detection, and practical examples, all based on MySQL 5.7.10.
ConcurrencyDatabase InternalsInnoDB
0 likes · 30 min read

This article explains the internal structures and roles of Oracle undo and redo logs in transaction processing, walks through practical experiments that dissect undo segment headers, undo blocks, undo chains, redo records, fast‑commit behavior, and the true nature of DELETE operations, and shows how these insights help diagnose ORA‑600 startup failures.
Database InternalsOracleRedo
0 likes · 14 min read
