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.
The guide explains MySQL/InnoDB deadlocks—how circular lock waits arise, the wait‑for‑graph detection that rolls back the transaction with fewer undo logs, the various lock modes and their behavior in UPDATE/INSERT/DELETE, and practical strategies such as proper indexing, consistent access order, and configuration tweaks to detect, handle, and prevent deadlocks.
The 2019 MySQL Technical Exchange Conference in Shenzhen, co‑hosted by Shanghai Aikesheng and Oracle, featured six expert talks covering secure MySQL platform design, large‑scale architecture optimization, backup and recovery strategies, open‑source middleware, automated operations, and InnoDB row‑lock mechanics, followed by community Q&A and a look ahead to open‑source initiatives.
This article demonstrates how MySQL's repeatable-read isolation with consistent snapshots causes transaction A to read the original value 1 while transaction B reads the updated value 3, explaining InnoDB's MVCC versioning, active transaction arrays, and the distinction between consistent and current reads.
This article outlines the philosophy, risks, participants, and step‑by‑step strategies for MySQL optimization, covering query flow, hardware and system tuning, configuration parameters, monitoring tools, and SQL‑level improvements to achieve stable, high‑performance database operations.
The article explains why InnoDB semaphore waits exceeding 600 seconds trigger server crashes, details the lock structures and functions involved, shows how buffer pool pressure and metadata statistics cause contention, and introduces a small DBA utility that parses MySQL error logs to reveal the relationships between threads, locks, and transactions.
This article analyzes intermittent IO write spikes in a MySQL slave instance, tracing them to concurrent InnoDB tablespace extensions triggered by large BLOB inserts across thousands of tables, and proposes mitigation strategies.
This article explains how to select the right index for mixed‑type queries, quantitatively compare query performance with and without LIMIT, and safely handle long‑running DELETE statements by using InnoDB status and proper kill techniques.
The article analyzes why MySQL tables upgraded from 5.6 to 5.7 began generating duplicate‑key errors during inserts, examines related InnoDB auto‑increment mechanisms, reproduces the issue, discusses known bugs (including replace‑into replication problems), and proposes both operational and kernel‑level solutions.
This article investigates why MySQL master‑slave replication can exhibit a one‑second difference in datetime fields when using server‑side prepared statements, analyzing binlog generation, InnoDB rounding, and providing code examples, root‑cause explanation, and mitigation strategies.
This article explains how InnoDB stores data pages, builds page directories, and uses B+‑tree indexes—including clustered, secondary, and composite indexes—to enable fast record lookup, while also covering MyISAM indexing differences and the SQL statements for creating and dropping indexes.
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.
The article details Tencent’s TXSQL (Tencent MySQL) architecture and optimizations—including asynchronous table deletion, DDL NO_WAIT/WAIT, MDL visibility, lock splitting, parallel replication, query push‑down, read‑view enhancements, low‑overhead auditing and KMS‑integrated encryption—highlighting its role as the core engine powering CDB for diverse industries such as gaming, finance, e‑commerce and internal services like WeChat and QQ.
This article explains MySQL's index structures, how indexes accelerate queries but slow down writes, the differences between B‑tree and hash indexes, clustered versus non‑clustered indexes, the left‑most prefix rule, and provides a comprehensive overview of MySQL locking mechanisms and best practices.
This article explains MySQL shared (S) and exclusive (X) row‑level locks, demonstrates their behavior with two concurrent sessions, shows how lock ordering can cause deadlocks, and provides practical tips for using InnoDB locking correctly.
Tencent Cloud Academy will livestream a MySQL schema design and development standards session on September 17 at 20:00, featuring experts Ye Jinrong and Wu Bingxi, covering best practices such as default InnoDB engine, utf8mb4 charset, row size limits, partitioning, appropriate data types, index guidelines, and operational recommendations.
This article summarizes the major functional and performance enhancements introduced in MySQL 8.0 InnoDB, including a unified data dictionary, atomic DDL, fast column addition, encryption, improved Memcached plugin, parallel redo log writes, and future roadmap items.
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.
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.
This article explains MySQL InnoDB locking mechanisms—including optimistic and pessimistic locks, shared and exclusive locks, row and table locks—and provides practical SQL examples and step‑by‑step guidance for detecting and resolving deadlocks in high‑concurrency environments.
The article explains how to use MySQL 8.0's SKIP LOCKED and NOWAIT clauses to implement a pure‑database seat‑reservation system, detailing transaction flow, possible lock outcomes, error handling, and the underlying InnoDB source‑code changes that enable these features.
A technical article detailing the investigation and resolution of a MySQL error related to insert buffer merge operations during table deletion, involving code analysis and system debugging.
This article explains how database indexes are implemented using B+ trees, describing their structure, advantages such as shallow depth and ordered leaf nodes, and how they reduce disk I/O, illustrating calculations of node capacity and emphasizing the importance of index size for performance.
This article explains the importance of transaction isolation in ACID, describes the four InnoDB isolation levels—READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ, and SERIALIZABLE—covers their locking behavior, effects on replication, and performance trade‑offs, and offers guidance on choosing the appropriate level.
This article explains InnoDB's transaction and index principles, covering ACID properties, transaction logs, MVCC, locking mechanisms, and B+ tree index structures to help backend developers understand how InnoDB ensures data consistency and high concurrency.
This guide shows how simple MySQL configuration changes—switching to InnoDB, allocating most RAM to the buffer pool, and enabling multiple buffer pool instances—can deliver up to an 80% performance boost for typical web‑site workloads.
Xtrabackup is an open-source hot backup tool for InnoDB and XtraDB databases, offering non-blocking backups with features like fast backup speeds, reliable physical backups, and efficient disk space usage. The recovery process involves complex log parsing and page flushing mechanisms, which can be optimized to improve performance, especially for large datasets.
This article explains how MySQL InnoDB implements consistent reads using multi‑version concurrency control (MVCC), detailing isolation levels, undo‑log mechanics, hidden columns, rollback segments, transaction commit behavior, and the impact on SELECT, INSERT, UPDATE, and DELETE operations.
At the 2018 Percona Live conference in California, Alibaba Cloud received the MySQL Community Corporate Contributor Award for the first time, recognizing its extensive contributions—including Multiple Source Replication, FlashBack, Sequence, column compression, dynamic column addition, persistent auto‑increment, SemiSync optimization, InnoDB patches, and cross‑engine backup—to the MySQL and MariaDB ecosystems.
This article provides a comprehensive, step‑by‑step overview of MySQL's InnoDB storage engine, covering database vs instance definitions, internal processes, tablespace layout, file formats, record layouts, B‑tree indexes, lock types, and transaction isolation levels for developers seeking a solid foundation.
This article analyzes why two similar MySQL SELECT statements return different rows by examining index structures, covering index behavior, the optimizer's choice of secondary versus primary keys, and experimental verification using EXPLAIN statements and index modifications.
This article explains MySQL's architecture, details the various metadata lock types, their acquisition and release processes, lock levels, and relationships, and then examines InnoDB's transaction isolation levels, lock types, lock levels, gap locks, and relevant source‑code structures.
This guide walks through stopping services, backing up the MySQL data directory, compiling the undrop‑for‑InnoDB tool, parsing ibdata1 to rebuild system tables, extracting CREATE TABLE statements, and loading recovered data back into MySQL, while highlighting common pitfalls such as command‑line flags and UTF‑8 emoji issues.
By switching to the InnoDB storage engine, allocating about 80% of system memory to its buffer pool, and configuring multiple buffer pool instances, you can significantly improve MySQL performance, with practical commands and guidelines for various memory sizes.
This article explains MySQL's row‑level, table‑level, and page‑level locks, maps them to storage engines, details MyISAM and InnoDB lock types, describes the four internal lock queues, and provides practical optimization and monitoring techniques to improve concurrency and performance.
This article provides a comprehensive technical overview of InnoDB's bulk index build feature introduced in MySQL 5.7, detailing its design, fast index build phases, bottom‑up construction, dirty‑page flushing, compressed‑table handling, fill‑factor configuration, and potential future optimizations.
This article examines typical MySQL concurrency problems—including table lock‑induced slow queries, metadata lock blocking during online schema changes, deadlock scenarios, and lock‑wait timeouts—provides step‑by‑step reproductions, analysis of lock behavior, and practical mitigation strategies such as using InnoDB, online DDL tools, and monitoring techniques.
This article explains the four MySQL transaction isolation levels—Read Uncommitted, Read Committed, Repeatable Read, and Serializable—by creating a sample table and running step‑by‑step command‑line sessions that demonstrate dirty reads, non‑repeatable reads, phantom reads, and the practical limits of InnoDB's MVCC implementation.
This article explains how to quickly locate and fix online MySQL problems such as partial update failures, InnoDB deadlocks, and lock‑wait timeouts by analyzing server logs, understanding InnoDB row‑lock behavior, optimizing indexes, adjusting transaction settings, and using a custom log‑collection script.
This article presents seven practical MySQL performance‑tuning techniques—including mastering EXPLAIN, building effective indexes, overriding default settings, optimizing memory usage, adopting SSD storage, scaling horizontally, and improving visibility—while explaining key configuration variables and monitoring tools to keep your database fast and reliable.
The article walks through a puzzling MySQL deadlock caused by two concurrent transactions on a table with a unique index, explains how to read the InnoDB status output, identifies the lock types and order, and shows a detailed step‑by‑step reconstruction of the deadlock scenario.
This article introduces MySQL InnoDB Memcached Plugin, explains its architecture and storage strategies, compares its performance and capacity with traditional MySQL and Redis, provides step‑by‑step installation and configuration commands, and describes a high‑availability deployment using binlog replication and sentinel clusters.
The article analyzes why dropping large InnoDB tables in MySQL can drastically degrade overall database performance due to buffer‑pool mutex and file‑system I/O bottlenecks, and presents both operational work‑arounds and source‑code modifications to alleviate the issue.
This guide explains MySQL's configuration fundamentals, how MySQL reads options, variable scopes, dynamic settings, common pitfalls, recommended habits, and provides a minimal yet comprehensive example my.cnf with detailed explanations for each section.
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.
This article summarizes the most compelling MySQL 8.0 enhancements—including a unified Data Dictionary, revamped Information Schema, role‑based privilege management, persistent configuration, auto‑increment fixes, InnoDB histogram and invisible index support, mutex refinements, row‑cache improvements, temporary‑table changes, character‑set updates, default parameter tweaks, and atomic DDL operations—providing practical guidance for DBAs and developers.
This article explains InnoDB's record storage mechanics, compares auto‑increment and business primary keys, details primary, secondary, and composite index structures, and shares practical table‑design guidelines and real‑world case studies from ZhaiZhai's MySQL usage.
This article explains InnoDB storage mechanics, compares auto‑increment and business primary keys, details primary, secondary, and composite index structures, and presents practical table‑design principles and real‑world case studies from ZhaiZhai's services to guide developers in building efficient MySQL schemas.
