Raj's Notes

MySQL and InnoDB from Clustered Rows to Replication

Trace MySQL 8.4 statements through the server layer and InnoDB clustered indexes, buffer pool, redo, undo, locks, purge, binary log, replication, and online schema change.

Working model#

MySQL is a database server with SQL parsing, optimization, connections, privileges, metadata, binary logging, and replication. InnoDB is its default transactional storage engine in MySQL 8.4. A statement crosses both layers. Saying “MySQL stores rows in a B-tree” describes InnoDB's common table organization, not the whole server path.

The boundary matters in operation. InnoDB redo supports storage-engine crash recovery. Undo supports rollback and older consistent reads. The MySQL binary log records server changes for replication and point-in-time recovery. Relay logs stage changes on a replica. These logs overlap in time but have different formats, consumers, retention, and recovery jobs.

One connection reaches the server layer first#

A client authenticates, selects a database, and sends statements through the MySQL protocol. The server parses and resolves names, checks privileges, asks the optimizer for a plan, invokes storage-engine APIs, returns results, and can write the committed transaction to the binary log.

MySQL's default thread-handling model gives each client connection a dedicated server thread, with a thread cache reducing thread-creation cost. A connection can hold transaction state, locks through its storage-engine work, prepared statements, temporary tables, and per-operation buffers. max_connections is a refusal limit, not proof that the host can execute that many active queries safely.

An application pool should bound concurrent statements from the whole fleet. If 300 instances each open 30 sessions, the server can receive 9,000 connections. A 300-session database pool with bounded caller queues makes admission visible, but it does not create query capacity. Track pool wait, active sessions, transactions, statement latency, InnoDB concurrency evidence, CPU, and I/O together.

InnoDB stores the row in the clustered index#

An InnoDB table is organized around one clustered index. If the table declares a primary key, that key defines the clustered index. Its leaf records contain the row's other columns. If there is no primary key, InnoDB chooses the first suitable unique non-null index; if none exists, it creates a hidden clustered key using an internal row identifier.

A secondary index is another B-tree. Its leaf entry contains the secondary-key columns and the clustered primary-key value. Fetching columns not present in the secondary entry normally means a second traversal through the clustered index. This is different from PostgreSQL's usual heap layout, where an index entry points to a heap tuple location.

The primary key therefore affects every secondary index. A wide primary key is repeated in each secondary entry. A randomly distributed primary key spreads inserts across clustered leaf pages and can increase page splits and reduce locality. A monotonically increasing key keeps ordinary inserts near the right edge, which improves locality but can make that edge a concurrency hotspot at high rates.

The primary key is still a logical contract. Do not replace a stable product identity merely to optimize insertion. A common compromise uses a compact internal clustered key plus a unique constraint on the external identity, accepting the extra index lookup and maintenance in return for a smaller clustered reference.

Pages move through the buffer pool#

InnoDB reads and writes table and index data in pages, 16 KiB by default. The buffer pool caches those pages and tracks dirty state. An index lookup that hits the buffer pool avoids a data-file read; an update changes the buffered page and leaves later flushing to background work after redo ordering is satisfied.

The buffer pool uses lists and midpoint insertion to keep broad scans from immediately evicting every hot page. Read-ahead can fetch neighboring pages when access appears sequential. Dirty-page flushing must keep enough reusable pages and keep the checkpoint age within redo capacity.

A buffer-pool hit is not the whole query cost. The server can still spend CPU on comparisons, row decoding, predicates, joins, sorting, and protocol output. A miss may be served by an operating-system or storage cache rather than physical media. Use buffer-pool read requests, physical reads, page age, dirty pages, flush rate, storage latency, and query plans together.

A clustered point read can require two tree traversals#

Suppose this table stores orders:

CREATE TABLE orders (
  id BIGINT NOT NULL,
  tenant_id BIGINT NOT NULL,
  external_id BINARY(16) NOT NULL,
  status VARCHAR(24) NOT NULL,
  total_cents BIGINT NOT NULL,
  created_at DATETIME(6) NOT NULL,
  PRIMARY KEY (id),
  UNIQUE KEY orders_tenant_external (tenant_id, external_id),
  KEY orders_tenant_created (tenant_id, created_at DESC, id DESC)
) ENGINE = InnoDB;

The external-identity lookup searches orders_tenant_external. Its leaf yields the clustered id. InnoDB then searches the primary tree for the row. The history query can seek into orders_tenant_created, walk entries in the requested order, and fetch primary rows for columns not covered by that secondary index.

Adding status and total_cents to the history index could avoid some clustered lookups. It also makes every entry wider, reduces leaf fanout, increases buffer use, and adds write work when status changes. Validate with EXPLAIN ANALYZE, rows examined, handler counters, buffer behavior, index bytes, and representative tenant distributions.

Redo makes dirty-page writeback recoverable#

InnoDB redo records physical changes needed to recover data pages. The log sequence number orders redo. A transaction can commit after its required redo and server-level commit records reach their configured durability boundaries while changed table pages remain dirty in the buffer pool. Checkpoint progress tells InnoDB how far redo is reflected in data files so older redo space can be reused.

The setting innodb_flush_log_at_trx_commit changes when the InnoDB log buffer is written and flushed. The binary log has its own sync_binlog setting. A durability statement must name both when binary logging participates in recovery or failover. Weaker settings can acknowledge transactions that a process, operating-system, or machine crash may lose under the documented condition.

Group commit lets several concurrent transactions share redo and binary-log synchronization work. MySQL coordinates storage-engine commit with binary logging so crash recovery does not leave an InnoDB transaction committed without its required binary-log record or vice versa. This is an internal atomic-commit problem; it does not make an external broker or payment service part of the transaction.

Redo capacity is a queue boundary. If dirty-page flushing cannot advance the checkpoint fast enough, foreground work eventually waits so InnoDB does not overwrite redo still needed for recovery. Observe redo generation, checkpoint age, log waits, dirty pages, flush throughput, and device latency before enlarging capacity. More log space delays the stall but cannot repair a sustained writeback deficit.

The doublewrite buffer protects page writes#

A storage failure can leave only part of a page updated. Redo may describe a change relative to a valid earlier page, but it cannot always repair a page whose base image is torn. InnoDB's doublewrite mechanism first writes page copies to a doublewrite area and then writes them to their final data-file locations. Recovery can use a valid copy when the final page is incomplete, then apply redo.

The extra write is deliberate protection, not duplicated logical data. Whether underlying storage claims atomic page writes does not justify disabling it without validating the entire path, including filesystem, volume, hypervisor, controller, and failure modes supported by the documented configuration.

Checksums detect some corruption; the doublewrite copy and redo can repair specific incomplete-page cases. Neither replaces replicas, backups, binary-log retention, and restore testing.

Undo serves rollback and consistent reads#

InnoDB stores old-version information in undo logs held in undo tablespaces and rollback segments. If a transaction rolls back, undo records help reverse its clustered-record changes. If a consistent read needs an earlier version, InnoDB follows undo history to reconstruct it.

InnoDB adds internal fields to clustered records, including the transaction identifier of the last modifying transaction and a pointer into undo history. A read view defines which transaction versions are visible. A current read used by locking statements follows different rules from an ordinary consistent snapshot read.

Purge removes obsolete undo-backed history and delete-marked records after no active read view needs them. A long-running transaction can keep the history list growing even if it executes no current statement. Symptoms include increasing undo space, history length, slower version reconstruction, and delayed record cleanup. Find the oldest transaction and read view before treating purge threads or undo size as the root cause.

Isolation changes both snapshots and locks#

InnoDB supports the SQL isolation levels, with REPEATABLE READ as the default in MySQL 8.4. A consistent nonlocking read at that level normally uses a transaction read view. READ COMMITTED obtains a newer snapshot for each consistent read. Locking reads and data changes use locks on index records and, depending on isolation and access path, gaps between records.

A record lock protects an index record. A gap lock protects an interval between index records. A next-key lock combines a record lock with the preceding gap. These range locks stop another transaction from inserting a value that would change a locked search range.

The chosen index and predicate determine the lock footprint. A unique equality lookup on all columns of a unique index can lock one existing record rather than its surrounding gap. A range predicate can lock several next-key intervals. A query without a selective index may examine and lock many records. READ COMMITTED reduces ordinary gap locking but does not remove it from cases such as foreign-key and duplicate-key checks.

Do not reduce InnoDB isolation to “MVCC means readers never block.” Locking reads, writes, metadata locks, foreign-key work, and some internal operations wait. A plain consistent read can also keep history alive and indirectly increase maintenance work.

Deadlocks expose conflicting access order#

InnoDB detects transaction deadlocks and rolls back a participant. The application must retry the whole transaction if its effects are safe to repeat. SHOW ENGINE INNODB STATUS reports the most recent detected deadlock; performance schema tables can provide current lock and wait evidence.

Two transfers that update account 10 then 20 in opposite orders form a simple cycle. Locking accounts in ascending ID order reduces that pattern. Hidden access can still add edges: secondary indexes, foreign keys, unique checks, triggers, and metadata locks. Capture the actual deadlock report instead of inferring it from SQL arrival order.

A lock-wait timeout is not the same as deadlock detection. A timeout can result from one long blocker without a cycle. Diagnose blocker identity, transaction age, held resources, statement, and application work performed inside the transaction.

Change buffering defers some secondary-index reads#

When a nonunique secondary-index leaf page is absent from the buffer pool, InnoDB can record an insert, delete-mark, or purge-related change in its change buffer instead of reading that leaf immediately. The change is merged when the page later enters the buffer pool or during background work. This converts some random reads into deferred merging.

Unique secondary indexes generally need an immediate uniqueness check and do not receive the same benefit. A read-heavy workload can pay merge work when it first touches cold pages. Change-buffer size and merge rate are therefore part of both write and later read behavior.

The feature is not a universal accelerator. A large buffer pool that keeps target leaves resident may leave little work to defer. Fast storage and read-heavy access can shift the tradeoff. Measure buffered operations, merges, page reads avoided, query latency, and recovery behavior on the actual version.

The adaptive hash index is an optional cache over B-tree access#

InnoDB can observe repeated B-tree lookup patterns and build hash entries for hot index prefixes. The adaptive hash index is not a declared SQL index and does not replace the underlying B-tree. It consumes memory and uses internal synchronization; some workloads benefit while others lose throughput through contention or churn.

MySQL 8.4 documents innodb_adaptive_hash_index as disabled by default, unlike earlier releases where it was enabled by default. This is a version-sensitive setting. Benchmark both states with a representative workload before changing it, and record the server release with the result.

The optimizer must estimate rows before InnoDB executes#

The MySQL optimizer considers access methods, join order, derived-table behavior, indexes, costs, and other transformations using table and column statistics. InnoDB persistent statistics and server histograms can improve distribution estimates. A histogram helps an unindexed predicate estimate selectivity; it does not create a lookup path.

Traditional EXPLAIN reports estimated plan details. EXPLAIN ANALYZE executes the statement and reports iterator timing, rows, and loops, so it must be used with care on writes. Large estimated-versus-actual gaps near a leaf can multiply nested-loop work above it.

Read the access type, chosen and candidate keys, key length, reference values, estimated rows, filter fraction, and extra work such as temporary tables, filesort, covering access, or pushed conditions. “Using index” in Extra can mean a covering read; it should not be paraphrased as “an index exists, therefore the query is fast.”

The binary log is a committed change stream#

The MySQL server writes transactions to the binary log when binary logging is enabled. Row-based logging records row change events; statement-based logging records statements where supported; mixed mode can choose. The format changes what a replica or CDC consumer receives and which nondeterministic behavior must be considered.

Global transaction identifiers (GTIDs) assign a source identity and sequence number to committed transactions. GTID sets let a replica identify which transactions it has executed without relying only on one file and offset, which simplifies many topology changes. GTIDs do not by themselves make promotion safe: the candidate must contain the required transaction set, the old writer must be fenced, and clients must route to one accepted writer.

A replica receiver reads the source binary log into relay logs. Applier threads execute or apply transactions from those relay logs. Lag can therefore come from source generation, network transfer, relay-log storage, dependency scheduling, lock contention, or apply throughput. A single “seconds behind” value cannot identify the stage or prove zero data loss.

Asynchronous and semisynchronous replication acknowledge different boundaries#

Traditional source-to-replica replication is asynchronous: a source can commit and reply before a replica durably receives the transaction. If the source fails, a promoted replica can lack acknowledged transactions.

Semisynchronous replication makes the source wait for a configured replica acknowledgment stage before returning, within its timeout and policy. The acknowledgment is not the same as the replica applying the transaction to user tables. If semisynchronous waiting times out, the source can fall back according to configuration, changing the effective protection. Observe whether semisynchronous operation is active, the acknowledged stage, fallbacks, and replica transaction sets.

Group Replication is another design, built on GTIDs and a group communication and certification protocol. It supports single-primary and multi-primary modes with documented restrictions. It should not be described as ordinary asynchronous replication with automatic failover. Certification conflicts, member state, quorum, transaction size, network partitions, and fencing all become part of the system.

Backups and binary logs serve point-in-time recovery#

A replica is not an independent backup because accidental changes and many corruptions replicate. Point-in-time recovery starts from a compatible physical or logical backup and applies retained binary-log events through a chosen position or GTID boundary.

A usable recovery plan records backup consistency, encryption, privileges, binary-log retention, schema and server compatibility, expected restore bandwidth, and the final transaction boundary. Test restore on separate infrastructure. Measure time to provision, read backup bytes, prepare data files, apply logs, validate invariants, and switch traffic. A successful backup job is not proof that the recovery-time objective can be met.

Online DDL is an algorithm and lock contract#

InnoDB data definition language (DDL) operations can use different algorithms:

The LOCK clause declares the desired concurrent-access level. “Online” does not mean no metadata lock, no I/O, no temporary storage, no redo or binary log, or no replica work. Operations often need brief exclusive metadata locks at start or completion and can wait behind old transactions. Concurrent DML during an online build can accumulate in a temporary modification log; exceeding its configured limit can fail the DDL.

Request an explicit algorithm and lock when a fallback would be unsafe:

ALTER TABLE orders
  ADD INDEX orders_status_created (tenant_id, status, created_at DESC, id DESC),
  ALGORITHM=INPLACE,
  LOCK=NONE;

If the operation cannot honor those clauses, it fails instead of silently selecting a more blocking algorithm. Support depends on the exact MySQL version, operation, row format, table features, and constraints. Consult the current support matrix for every production change.

An instant column operation can leave internal row-version metadata and has documented limits. A later operation may require a rebuild. Metadata-only does not mean permanent zero cost or unlimited schema churn.

Worked trace: change status during an online index build#

Assume the orders table holds 2 billion rows and 18 TiB across data and indexes. Peak traffic is 25,000 reads and 7,000 status updates each second. An operator adds (tenant_id, status, created_at, id) for a support query using ALGORITHM=INPLACE, LOCK=NONE on a tested MySQL 8.4 release.

One foreground update follows this path:

  1. The server admits a pooled connection, parses the guarded update, and chooses the unique external-ID access path.
  2. InnoDB searches the secondary unique tree, obtains the clustered key, and searches the clustered tree.
  3. The locking update rechecks status = 'pending', creates undo, changes the clustered record, and maintains the existing status-related secondary entries.
  4. Because the new index build is active, its online modification log also records concurrent changes that must be applied to the building index.
  5. InnoDB emits redo; the server coordinates the commit with the binary log.
  6. The source acknowledges at the configured redo, binary-log, and replication durability boundary.
  7. Replicas receive the binary-log event and apply it while also processing the DDL work.

The build scans and sorts table data, writes new index pages, consumes temporary space, and later needs metadata-lock progress to publish the result. If foreground DML produces its online log faster than the DDL applies it, the log can reach its limit and the operation fails. If a long transaction holds an incompatible metadata lock, cutover waits while all scan work and production risk remain.

The release record should include estimated and actual scan bytes, build rate, temporary and final bytes, online-log growth, foreground p99, buffer-pool churn, redo and binary-log rates, replica receive/apply lag, metadata-lock waits, final index visibility, and cleanup after failure. A successful command return is the last step, not the operating plan.

Summary#

MySQL's server layer and InnoDB solve different parts of one statement path. InnoDB organizes rows in the clustered primary-key tree, keeps secondary keys with the primary key, caches pages, records redo, reconstructs older reads from undo, and later purges obsolete history. The server plans SQL, coordinates the binary log, and drives replication.

References#