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.
This article explains MySQL's lock types across storage engines, details MyISAM and InnoDB locking behaviors, describes the four lock queues, outlines gap‑lock characteristics, and provides practical optimization and monitoring techniques to reduce lock contention.
This article explains the structure and components of MySQL 5.7 InnoDB tablespaces—including system, undo, temporary, and general tablespaces—covers double‑write buffering, redo logs, encryption with keyring, and provides practical configuration examples and command‑line demonstrations.
The article investigates a performance regression in MySQL 5.7.18 where partitioned tables cause excessive InnoDB row locking, leading to lock timeouts and slower updates, explains the root cause through source‑code analysis, reproduces the issue, validates the findings, and confirms it as a MySQL bug.
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.
This article investigates the performance degradation and lock timeout issues observed in MySQL 5.7.18 partition tables, reproduces the problem with test cases, analyzes InnoDB lock behavior through source code debugging, validates the root cause related to partition scan locking, and confirms it as a regression bug in MySQL 5.7.
This article compiles a comprehensive set of MySQL interview questions covering replication mechanisms, differences between MyISAM and InnoDB, character type nuances, transaction logging, binlog formats, performance tuning, backup strategies, monitoring, consistency checks, and handling large datasets, providing concise answers for each topic.
This article explains how concurrent transactions updating the same MySQL InnoDB row can cause inconsistent results, reviews InnoDB isolation levels and lock types, and demonstrates using SERIALIZABLE isolation combined with exclusive row locks in Sequelize to ensure correct sequential updates.
This article explains how to create a lightweight MySQL tool that extracts connection details from information_schema.processlist, groups them by user, host, database, command and state, fingerprints SQL statements, and correlates them with InnoDB transaction data to quickly pinpoint performance problems.
This article explains the new shared temporary tablespace introduced in MySQL 5.7, how to configure its size and location with innodb_temp_data_file_path, compares it with tmpdir, demonstrates the impact on compressed and uncompressed InnoDB temporary tables, and highlights related parameters and potential pitfalls.
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.
This article explains how MySQL 5.7 automatically saves and restores InnoDB buffer‑pool pages, how to tune the innodb_buffer_pool_dump_pct setting, monitor load progress, and handle multi‑node deployments or crashes to achieve faster, more predictable startup performance.
This article explains MySQL InnoDB deadlocks, describes how the engine detects and resolves them by rolling back a transaction with error 1213, and walks through real‑world examples and log analyses to illustrate the causes and troubleshooting steps.
This article explains the data structures and algorithms behind MySQL indexing, covering B‑Tree fundamentals, insertion and deletion steps with visual examples, the differences between B‑Tree and B+Tree, InnoDB's clustered and secondary index implementations, and why monotonically increasing primary keys improve performance.
This guide outlines nine practical MySQL recovery methods—including full backup with binlog, business‑logic rollback, flashback tools, innodb_force_recovery, tablespace discard/import, manual ibd editing, extundelete, and Percona's recovery utility—to help DBAs restore data after accidental deletions, corruption, or hardware failures.
This article details the design, performance enhancements, feature extensions, stability improvements, and benchmark results of TXSQL—a heavily customized MySQL branch used by Tencent Cloud’s CDB service—to illustrate how it achieves higher throughput, stronger consistency, and better reliability for large‑scale cloud databases.
This article explains InnoDB’s double‑write buffer in MySQL, compares its design and recovery handling with Oracle’s redo and control‑file mechanisms, discusses partial‑write issues, checkpoint strategies, performance impacts on SSDs, and provides practical commands and configuration tips for DBAs.
An in‑depth comparison of MySQL’s default storage engines—MyISAM and InnoDB—covers their evolution, core features, performance benchmarks, locking mechanisms, transaction support, file structures, and practical guidance on choosing the right engine for different workloads.
This article explains MySQL InnoDB's locking mechanisms—including MVCC snapshot vs. current reads, two‑phase locking, isolation levels, lock types, GAP locks, and deadlock scenarios—by analyzing simple and complex SQL examples and showing how different index and isolation configurations affect lock acquisition.
This article offers comprehensive advice on MySQL DBA interview preparation, covering the book’s positioning, essential technical topics such as replication and InnoDB internals, desired personal qualities, interview strategies, and practical tips for both interviewers and candidates.
The benchmark shows that while InnoDB delivers roughly 30 % higher insert throughput than TokuDB, the TokuDB engine achieves 10‑16× storage compression with comparable import times, minimal impact from log‑sync settings, and zlib emerging as the preferred compression algorithm for balanced performance and CPU usage.
This article walks through a puzzling MySQL InnoDB deadlock case, showing the table schema, the conflicting transactions, how to read the SHOW ENGINE INNODB STATUS output, and the reasoning that reveals why the deadlock occurs and how to diagnose similar issues.
The latest MySQL release introduces a transactional data dictionary, role‑based account permission management, multiple InnoDB enhancements—including stable auto‑increment counters, memcached multi‑key support, dynamic deadlock detection, shared temporary tables, and NOWAIT/SKIP LOCKED options—plus advanced JSON operators, aggregation functions, pretty‑print utility, and optimizer features such as hidden and descending indexes.
This article dissects the InnoDB transaction structure defined in storage/innobase/include/trx0trx.h, detailing each of the 63 variables—including their types, purpose, and interaction with MySQL’s transaction coordinator—so readers can grasp how InnoDB ensures data consistency and manages transaction state.
This guide details the most important MySQL server configuration settings—including connection limits, file handling, cache sizes, MyISAM and InnoDB options—explaining their purpose, recommended values, and how they impact performance and stability.
Learn why MySQL tables develop fragmentation, how to measure it using Data_free in INFORMATION_SCHEMA, and the appropriate commands—OPTIMIZE for MyISAM and ALTER ENGINE for InnoDB—to safely reclaim space, with practical scheduling tips to minimize impact on production workloads.
This article outlines comprehensive MySQL best practices, covering naming conventions, storage engine selection, data type choices, timestamp handling, NOT NULL enforcement, indexing strategies, query optimization, and safe schema evolution to improve reliability and performance of database systems.
This guide explains essential MySQL InnoDB parameters—such as buffer pool size, instances, connection limits, log buffer, statistics collection, lock timeout, thread concurrency, and I/O threads—and provides practical CPU‑based settings to help you optimize database performance.
Understanding InnoDB log files is crucial for MySQL recovery and performance; this guide explains their role, the impact of size on checkpoints and recovery time, and provides a step‑by‑step method to calculate an optimal log size based on one hour of generated logs.
A lottery system that relied on MySQL’s SELECT without ORDER BY unintentionally gave a single player multiple top‑prize wins because InnoDB reordered results by primary key, revealing a subtle database engine rule that can corrupt random selection logic.
This article demonstrates how InnoDB returns rows in primary‑key order during full‑table scans, while queries that use a secondary index follow the leaf‑node order of that index, and it compares performance implications of index‑only scans versus clustered‑index scans.
This article demonstrates a step‑by‑step method to confirm that InnoDB secondary index leaf pages store rows sorted first by the secondary key and then by the primary key, using bcview, mysqlblock, and a small C utility to read raw .ibd pages.
This article examines MySQL InnoDB's next‑key lock behavior by creating a test table, running concurrent transactions, and analyzing how the lock range is determined across primary and secondary index columns, revealing why certain insert statements are blocked while others succeed.
MyISAM, the original MySQL storage engine used for over two decades, is now limited and discouraged in MySQL 8.0 due to its lack of transactions, table‑level locking, and crash recovery, with InnoDB taking over its former advantages.
The article clarifies that nontransactional statements are SQL commands operating on non‑transactional tables such as MyISAM, explains how MySQL parameters max_binlog_stmt_cache_size and max_binlog_cache_size control memory for these statements, and demonstrates the difference with transactional InnoDB tables through concrete command‑line examples.
This guide explains MySQL's numeric, temporal, and string data types, their storage implications, and how to choose appropriate types, while detailing index types, creation rules, and optimization tips such as prefix lengths and B+Tree versus HASH strategies for faster queries.
This article walks through the major MySQL 5.7 enhancements—including InnoDB buffer‑dump improvements, multi‑threaded page cleaning, dynamic buffer‑pool resizing, global tablespaces, default DYNAMIC row format, native partitioning, truncate‑undo logs, native JSON support, sys schema, optimizer hints, trigger upgrades, syslog integration, and virtual columns—providing practical steps, examples, and usage tips for DBAs.
This guide demonstrates how to simulate a blocking situation in MySQL using two client connections, set a long lock wait timeout, execute conflicting UPDATE queries, and then query the InnoDB lock wait tables to identify blocked and blocking threads along with wait duration.
This article explains how major companies keep MySQL fast at massive scale and provides practical techniques—including schema design, InnoDB advantages, table compression, bulk loading order, and hardware choices—to optimize MySQL performance for billions‑row tables.
An in‑depth guide explains MySQL’s four transaction isolation levels, how to modify them globally or per session, the role of autocommit, shared and exclusive lock mechanisms, and provides step‑by‑step InnoDB examples with screenshots illustrating each level’s behavior.
This article walks through diagnosing severe I/O spikes in a MySQL project, explains SSD characteristics, and provides step‑by‑step InnoDB parameter tweaks, I/O scheduler changes, and monitoring results that lead to a stable, balanced database performance on SSD storage.
This article explains why transactions are essential, describes the ACID properties, details MySQL's isolation levels and their effects, outlines InnoDB's redo/undo logging mechanism, and introduces distributed transaction models with two‑phase commit, providing practical examples and diagrams.
This article explains how to enable MySQL scheduled events, shows the full CREATE EVENT syntax with practical timing examples, covers advanced operations like multi‑statement events and disabling, and then dives into table partitioning techniques (RANGE, HASH, KEY, LIST) with detailed commands and troubleshooting for large InnoDB tables.
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.
This guide explains step‑by‑step how to restore an accidentally deleted MySQL InnoDB log file (ib_logfile1) on Linux by using file descriptors, flushing buffers, and safely copying the deleted file back while keeping the database running.
MySQL 5.7 introduces major enhancements such as native JSON support, an expanded Performance Schema, a new SYS Schema for health monitoring, improved security, advanced GIS capabilities, three‑fold query throughput gains, InnoDB optimizations, multi‑source replication, and a smarter optimizer with dynamic costing.
This article explains the design and implementation of InnoDB‑based physical replication for MySQL, covering background concepts, advantages over native binlog replication, high‑level architecture, log file handling, instance roles, MVCC handling, failover procedures, and performance test results.
This article examines the common reasons MySQL performance may lag—outdated hardware, sub‑optimal database design, and poorly written application code—and provides concrete guidance on CPU, memory, storage, schema, and query optimizations to improve response times.
This guide explains why MySQL tables become fragmented, how to detect fragmentation using information_schema queries, and how to defragment tables safely with OPTIMIZE, ALTER TABLE, or MySQL startup options while noting engine‑specific behavior and best‑practice cautions.
This article analyzes a puzzling MySQL deadlock scenario where concurrent DELETE statements on a uniquely indexed row lead to a deadlock, explaining the lock modes, InnoDB's deadlock‑prevention strategy, and the conditions that make such deadlocks possible.
This article provides an in‑depth analysis of MySQL/InnoDB locking mechanisms, covering MVCC snapshot vs current reads, cluster indexes, two‑phase locking, isolation levels, lock behavior for simple and complex SQL statements, and practical deadlock diagnosis techniques.
This guide explains MySQL's four transaction isolation levels, how to change them globally or per session, the impact of autocommit, the different lock types, and provides step‑by‑step InnoDB examples that illustrate the behavior of READ‑UNCOMMITTED, READ‑COMMITTED, REPEATABLE‑READ, and SERIALIZABLE.
This article presents a series of MySQL backend improvements—including Group Commit redesign, DDL fast‑fail handling, GTID allocation refinements, persistent InnoDB auto‑increment values, replication parallelism, and key‑partition hash algorithm changes—detailing motivations, implementation steps, and performance results.
A MySQL replica was consuming an entire CPU core due to single‑process replication, and analysis of binlog and InnoDB status revealed heavy row locks on a table without a primary key, which was resolved by adding an auto‑increment ID, dropping CPU usage to 3% and eliminating replication lag.
This article explains InnoDB's file I/O interfaces, asynchronous and synchronous read/write mechanisms, background IO threads, AIO request handling, concurrency controls, prefetch strategies, log write padding, and the evolution of buffer pool initialization, chain management, and page eviction in MySQL 5.7.
This article explains three practical techniques—batching multiple rows into a single INSERT, wrapping inserts in transactions, and inserting records in primary‑key order—to significantly improve MySQL InnoDB insert performance, backed by detailed test results and important configuration tips.
This article examines the various scenarios in which MySQL can lose data, covering InnoDB and MyISAM engine behaviors, flush policies, master‑slave replication pitfalls, XA transaction handling, and practical solutions to improve consistency and availability.
This article explains the components, communication mechanisms, full and incremental backup procedures, and restoration steps of Percona XtraBackup (PXB), detailing how the innobackupex script and xtrabackup binary cooperate to perform hot physical backups of MySQL/InnoDB databases.
The article explains how InnoDB’s auto‑increment handling changed after MySQL 5.1.22—from using a table lock to a lightweight mutex for predictable‑row inserts—and details the three innodb_autoinc_lock_mode settings, their impact on concurrency and replication, and related pitfalls such as non‑sequential IDs.
This report details a series of MySQL InnoDB experiments that examine transaction locking, lock types, gap and next‑key locks, and deadlock behavior across various SQL operations, providing practical insights for optimizing concurrency and preventing deadlocks.
This article provides a comprehensive overview of MySQL InnoDB's transaction lock system, covering row‑level and table‑level lock types, their flags, isolation‑level behavior, deadlock detection, and illustrative examples with SQL and source‑code snippets.
This article explains MySQL 5.7 InnoDB's traditional compressed storage format, system page structures, external storage pages, encrypted and R‑TREE pages, the IO subsystem including asynchronous AIO handling and read‑ahead strategies, as well as buffer pool initialization, management, and concurrency control.
This article provides a comprehensive technical overview of InnoDB's physical file architecture, detailing how fixed‑size blocks, page types, extents, file‑list structures, segment creation, page allocation, recycling mechanisms, index page composition, and both traditional and transparent compression methods work together to manage MySQL storage efficiently.
This article explains how InnoDB's Adaptive Hash Index works, covering its initialization, information gathering, index and block statistics updates, page‑level hash construction, usage conditions, maintenance procedures, shortcut query mode, and monitoring via performance metrics.
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.
This article explains MySQL InnoDB full-text indexing, covering supported modes, auxiliary tables, creation, DML handling, query processing, transaction management, cache synchronization, optimization, background threads, monitoring, stopwords, plugins, and the built‑in n‑gram parser, with code references from MySQL 5.7.
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.
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.
This article explains the architecture and code flow of InnoDB's IO subsystem, covering read/write threads, asynchronous AIO handling, concurrency control, file prefetch strategies, and log write padding, with detailed examples of the underlying C++ structures and functions.
This article presents a comprehensive guide to MySQL optimization, covering its strengths and weaknesses, daily DBA tasks, development and operational standards, sharding strategies, backup solutions, performance tuning, replication options, InnoDB configuration, system-level tweaks, and future improvement directions.
This article shares a MySQL InnoDB core developer’s recommendations on DBA learning stages, Oracle’s stance on MySQL’s future, official comparisons with MariaDB and PostgreSQL, and how MySQL’s globally distributed development team collaborates, offering practical guidance for database professionals.
This guide explains how Percona XtraBackup works for MySQL hot backups, covering required permissions, key terminology, backup and incremental backup mechanisms, essential command‑line options, and practical script examples to ensure reliable, low‑impact data protection.
This article provides a comprehensive overview of MySQL indexing, covering the theoretical foundations of B‑Tree and B+Tree data structures, the differences between MyISAM and InnoDB index implementations, and practical optimization techniques such as left‑most prefix usage, selectivity analysis, and prefix indexing.
This article examines MySQL indexing theory, covering B‑Tree and B+Tree structures, storage‑engine differences between MyISAM and InnoDB, left‑most prefix rules, index selectivity, prefix indexes, and practical optimization techniques for high‑performance query execution.
This article walks through the background of I/O issues in a MySQL project, explains SSD characteristics, and details step‑by‑step InnoDB parameter adjustments—including I/O scheduler changes, innodb_io_capacity, and adaptive flushing—both before and after a master‑slave switch, supported by performance data and diagrams.
This article provides an extensive overview of MySQL, covering its history, strengths and limitations, recommended development and operational standards, replication and high‑availability strategies, backup and recovery methods, as well as detailed performance‑tuning techniques for InnoDB and alternative storage engines.
While InnoDB’s row‑level locking generally outperforms MyISAM’s table‑level locking under high concurrency, misuse can negate its advantages; this guide explains why indexing, precise lock scopes, minimal range queries, smaller transactions, and lower isolation levels are essential for optimal InnoDB performance.
