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.
An expert panel from the High‑Efficiency Operations community answers eleven pressing MySQL 5.7 questions, covering test hardware, GA timeline, GIS speed, virtual column indexing, materialized view plans, 2‑D geo support, performance monitoring, Oracle’s market strategy, geojson handling, multi‑source replication, buffer‑pool locking, and InnoDB Memcached consistency.
The article reviews MySQL InnoDB 5.7’s major performance enhancements—such as transaction pooling, temporary‑table redesign, buffer and DDL optimizations—and introduces new capabilities like native partitioning, dynamic buffer sizing, GIS support, virtual columns, and advanced compression, all illustrated with benchmark graphics.
This guide walks you through essential MySQL and MariaDB performance‑tuning techniques—including storage engine settings, disk placement, buffer pool sizing, swappiness, connection limits, thread cache, DNS lookup, query cache, temporary tables, slow‑query logging, idle connections, file‑system choices, packet size, and testing tools—to help you optimize and maintain a high‑speed, reliable database server.
Learn how to fine‑tune MySQL by adjusting key general, MyISAM, and InnoDB parameters—such as max connections, buffer sizes, query cache, and log settings—to improve performance, stability, and resource utilization based on recommended percentages of system memory.
This article presents a comprehensive technical overview of MySQL performance tuning and feature enhancements—including group‑commit redesign, DDL fast‑fail, GTID handling, persistent auto‑increment, replication parallelism, and key‑partition hash changes—backed by real‑world test data from Taobao's large‑scale deployment.
