MySQL

MySQL is a server with one storage engine that matters. Almost every operational property people attribute to MySQL (row-level locking, crash recovery, the transaction model, the way an unindexed UPDATE blocks half the application) is a property of InnoDB, not of the server layer above it. Understanding InnoDB’s four on-disk and in-memory structures gets you most of the way to understanding MySQL in production.

The release model is the other thing to settle first. Oracle splits releases into LTS and Innovation tracks. An LTS series receives only necessary fixes, removes nothing after its first release, and follows the Oracle Lifetime Support policy of five years premier plus three years extended support. An Innovation release is supported only until the next Innovation release, a window of roughly a quarter. It explicitly expects behaviour changes, including new reserved words and changed query execution.

MySQL 9.7 (April 2026) is the current LTS, and the final release line using sequential version numbers: later releases use calendar versioning in YY.M form, so MySQL 26.7 is the July 2026 Innovation release. MySQL 8.4 LTS (April 2024) remains supported, with premier support to April 2029 and extended support to April 2032 (endoflife.date, 2026). MySQL 8.0 reached end of life on 30 April 2026. Upgrade paths are constrained: 8.4.x LTS to 9.7.x LTS is supported, skipping an LTS series is not, and you cannot upgrade directly between Innovation series of different major versions. Run an LTS unless you have a specific reason and a fast upgrade pipeline.

InnoDB architecture

Four structures carry the weight.

The buffer pool caches table and index pages in memory. It is managed as a variant of an LRU list split into a young sublist of recently used pages and an old sublist of candidates for eviction; innodb_old_blocks_pct (default 37) sets the proportion given to the old sublist, which is what stops a single large table scan from evicting the working set.

The redo log is a disk structure that records physical page changes so that modifications which had not reached the data files at the time of a crash can be replayed at startup. Its size is set as a single capacity figure, innodb_redo_log_capacity; InnoDB maintains 32 files internally, each 1/32 of the capacity, named #ib_redoN under the data directory. Ordinary and spare files coexist, so resizing does not require deleting anything by hand:

SET GLOBAL innodb_redo_log_capacity = 8589934592;  -- 8 GiB
SELECT FILE_ID, START_LSN, END_LSN, SIZE_IN_BYTES, IS_FULL
FROM performance_schema.innodb_redo_log_files;

A larger redo log means less checkpoint pressure and fewer flush stalls under write bursts; the cost is a longer crash recovery. Set it in the option file as well, or the change is lost on restart.

The doublewrite buffer exists because a page write can be torn, partly old and partly new, by an operating system, storage or mysqld failure, and a physical redo record cannot repair a page it cannot trust. InnoDB therefore writes flushed pages first to doublewrite files in a large sequential chunk with a single fsync(), then to their real positions. Data is written twice but the I/O cost is far below double. DETECT_ONLY writes only metadata, so crash recovery reports an incomplete page write instead of repairing it: a diagnostic mode, not a tuning setting. Dynamic changes between ON, DETECT_AND_RECOVER and DETECT_ONLY are allowed; changes to or from OFF are not.

Undo logs hold the information needed to roll a transaction back and to serve consistent reads of earlier row versions. They live in undo log segments inside rollback segments, which live in undo tablespaces, and, for temporary tables, in the global temporary tablespace, where they are not redo-logged because they are never needed for crash recovery. A transaction is assigned up to four undo logs, one each for inserts and for updates/deletes, on regular and on temporary tables.

This is where the most common MySQL capacity failure lives. Undo cannot be purged while any transaction might still need to see the old row versions, so a single session left open in a transaction pins the purge horizon and undo tablespaces grow without bound. innodb_undo_log_truncate defaults to ON and truncates an undo tablespace once it exceeds innodb_max_undo_log_size (default 1073741824, 1 GiB), but only once purge has advanced past it. The diagnostic is the history list length in SHOW ENGINE INNODB STATUS climbing steadily while innodb_undo_tablespaces files grow:

SELECT trx_id, trx_started, TIMEDIFF(NOW(), trx_started) AS age, trx_rows_modified
FROM information_schema.innodb_trx
ORDER BY trx_started
LIMIT 10;

The transaction model

InnoDB implements all four SQL:1992 isolation levels. REPEATABLE READ is the default, and this is the single most important behavioural difference from PostgreSQL, whose default is READ COMMITTED.

Under REPEATABLE READ, several plain SELECT statements in one transaction are consistent with each other, because they all read the snapshot taken by the first of them. The trap is that locking statements do not use that snapshot: they use the most recent committed state. The manual is explicit that mixing UPDATE/INSERT/DELETE/SELECT ... FOR UPDATE with plain SELECT in one REPEATABLE READ transaction produces two mutually inconsistent views of the table, and that if you want that combination you probably want SERIALIZABLE.

READ COMMITTED behaves closer to PostgreSQL and has two further effects worth knowing. Record locks for rows that do not match the WHERE condition are released after evaluation, which greatly reduces deadlock probability without eliminating it. And for UPDATE, if a row is already locked, InnoDB performs a semi-consistent read, returning the latest committed version so the server can decide whether the row matches at all. Note the constraint: only row-based binary logging is supported with READ COMMITTED, and binlog_format=MIXED is silently promoted to row-based.

Gap locks and next-key locks

Row locks in InnoDB are index-record locks. There is no such thing as locking “a row”. There is locking an entry in an index, and if the table has no index InnoDB creates a hidden clustered index and locks entries in that.

A gap lock locks the space between two index records, or before the first or after the last. It is “purely inhibitive”: its only function is to stop another transaction inserting into that gap. Gap locks do not conflict with each other, and shared and exclusive gap locks are indistinguishable in effect. A next-key lock is a record lock plus a gap lock on the gap preceding that record. For an index containing 10, 11, 13 and 20, the possible next-key locks cover (-∞, 10], (10, 11], (11, 13], (13, 20] and (20, +∞). The final interval locks the gap above the highest indexed value, via the supremum pseudo-record.

Two rules decide how much of your table a statement locks:

• Gap locking is skipped when a statement locks rows through a unique index with a unique search condition. Supplying only some columns of a multi-column unique index does not count, and gap locking does occur.
• If the searched column is not indexed, or has a non-unique index, the preceding gap is locked.

So SELECT * FROM child WHERE id = 100 FOR UPDATE on a unique id takes one record lock and blocks nothing else. The same statement against an unindexed column locks the range scanned - in the worst case the whole table - and every concurrent insert into that range queues behind it. This is the mechanism behind most “MySQL locked up under load” reports, and the fix is an index, not a configuration change.

READ COMMITTED disables gap locking for searches and index scans entirely. That is a legitimate reason to choose it, at the cost of phantom rows.

Insert intention locks are the reason concurrent inserts into one gap do not serialise: two transactions inserting 5 and 6 between existing values 4 and 7 both take insert intention locks on that gap and do not block each other.

Deadlocks

Deadlock detection is enabled by default (innodb_deadlock_detect = ON). InnoDB rolls back the transaction it judges smallest, measured by rows inserted, updated or deleted. Two limits are worth knowing because they show up as a confusing error, not a normal deadlock report: a wait-for list exceeding 200 transactions is treated as a deadlock, as is a locking thread having to examine more than 1,000,000 locks held by transactions on that list. Both surface as TOO DEEP OR LONG SEARCH IN THE LOCK TABLE WAITS-FOR GRAPH in the LATEST DETECTED DEADLOCK section.

InnoDB cannot detect deadlocks involving a LOCK TABLES table lock or a lock taken by another storage engine. Those resolve only by timeout, which is innodb_lock_wait_timeout (default 50 seconds, long enough that a stuck request looks like an outage). Note also that by default a lock-wait timeout rolls back only the statement, not the transaction, because innodb_rollback_on_timeout defaults to OFF; the application must decide what to do with the half-finished transaction it still owns.

On very high concurrency, detection itself becomes a contention point, and disabling it in favour of a short innodb_lock_wait_timeout is a documented option. Treat that as a last resort: it converts an immediate, diagnosable error into a delay.

Deadlocks are not recorded anywhere permanent unless you ask. Turn that on:

SET GLOBAL innodb_print_all_deadlocks = ON;   -- default OFF, writes to the error log
SET GLOBAL innodb_status_output_locks = ON;   -- default OFF, adds per-lock detail

Replication

MySQL replication is asynchronous by default: the source writes to its binary log and does not wait for anyone.

Binary log format. Row-based logging is the default and is what you should use. Statement-based logging replays SQL on the replica and has documented problems with stored routines and triggers; mixed format starts statement-based and switches to row-based for statements the server judges unsafe. Changing binlog_format was deprecated in 8.0, and the manual states the expectation that it will be removed and row-based will be the only format.

GTIDs. A global transaction identifier makes a transaction identifiable independently of file and offset, which is what allows a replica to be repointed at a new source without working out the equivalent coordinates. gtid_mode and enforce_gtid_consistency are not enabled by default and must both be set to ON. MySQL 8.4 added tagged GTIDs, of the form UUID:TAG:NUMBER, set through gtid_next and gated by a new TRANSACTION_GTID_TAG privilege; the original UUID:NUMBER form is unchanged and existing topologies need no modification.

The multi-threaded applier. replica_parallel_workers defaults to 4, so replicas are multi-threaded out of the box, and replica_preserve_commit_order defaults to ON so that parallel apply is invisible to applications. In 8.4 the tuning knob for this went away: binlog_transaction_dependency_tracking was removed and now raises an error if set. The source always uses write-sets to compute dependency information, equivalent to the old WRITESET value. That is a behaviour improvement and an upgrade hazard. An option file carried over from 8.0 will stop the server from starting.

Semi-synchronous replication is a pair of plugins, not built in, and must be installed on both sides:

-- on the source
INSTALL PLUGIN rpl_semi_sync_source SONAME 'semisync_source.so';
SET GLOBAL rpl_semi_sync_source_enabled = 1;

-- on each replica
INSTALL PLUGIN rpl_semi_sync_replica SONAME 'semisync_replica.so';
SET GLOBAL rpl_semi_sync_replica_enabled = 1;
STOP REPLICA IO_THREAD;
START REPLICA IO_THREAD;

Be precise about the guarantee. The source waits for rpl_semi_sync_source_wait_for_replica_count acknowledgements (default 1), and a replica acknowledges only after the events are written to its relay log and flushed to disk, not applied. The default wait point is AFTER_SYNC, which waits before the storage-engine commit, so all clients see a transaction at the same time and failover is lossless with respect to acknowledged commits. AFTER_COMMIT commits first and waits afterwards, which lets other clients observe a transaction that no replica has yet received. (MariaDB defaults to AFTER_COMMIT; see MariaDB for how the two projects’ replication stacks have diverged.)

The failure mode to instrument is the timeout. rpl_semi_sync_source_timeout defaults to 10000 ms, after which the source reverts to asynchronous replication and continues serving writes. Monitor Rpl_semi_sync_source_status; do not assume the setting is in force. The manual is also blunt that a source which crashed under semi-synchronous replication should be discarded and not reused, because it may hold transactions no replica acknowledged.

Group Replication and InnoDB Cluster. Group Replication is a plugin providing a fault-tolerant group with certification-based conflict detection over XCom, a Paxos variant. InnoDB Cluster is Group Replication plus MySQL Router and MySQL Shell, packaged so that failover and routing are handled for you instead of by hand.

Its requirements are constraints, not suggestions: InnoDB only; every replicated table needs a primary key or a non-null unique equivalent; binlog_format=ROW and log_replica_updates=ON (both already default); gtid_mode=ON and enforce_gtid_consistency=ON (neither default); replica_preserve_commit_order=ON; and lower_case_table_names and default_table_encryption identical on every member.

The limitations decide whether it fits:

• Certification ignores gap locks, table locks and named locks. Gap-lock information does not exist outside InnoDB. For multi-primary groups the manual recommends READ COMMITTED, which removes gap locks and so aligns local with distributed conflict detection.
• SERIALIZABLE is refused in multi-primary groups by default.
• Concurrent DDL and DML on the same object from different members is unsupported in multi-primary mode.
• Cascading foreign keys are unsupported in multi-primary mode; set group_replication_enforce_update_everywhere_checks=ON to catch the conflicts it cannot detect.
• Nine members maximum.
• Transaction size is capped. group_replication_transaction_size_limit defaults to 150000000 bytes (about 143 MB); larger transactions are rolled back before they reach the group. Messages above group_replication_compression_threshold (1000000 bytes) are compressed and above group_replication_communication_max_message_size (10485760 bytes) fragmented. A transaction whose message cannot cross the network inside a five-second window can get its member expelled for looking failed; group_replication_member_expel_timeout buys time.

Two defaults changed in 8.4 and both change failure behaviour: group_replication_consistency moved from EVENTUAL to BEFORE_ON_PRIMARY_FAILOVER, so a newly elected primary holds reads until it has applied its backlog instead of serving stale data; and group_replication_exit_state_action moved from READ_ONLY to OFFLINE_MODE, so a member that leaves the group stops serving ordinary clients instead of continuing read-only.

Configuration that matters

Every default below is from the 9.7 and 8.4 manuals and is identical in both.

Durability. innodb_flush_log_at_trx_commit = 1 is full ACID: the log buffer is written and flushed at each commit. 2 writes the log at commit but flushes about once per second, so a mysqld crash loses nothing but an OS crash or power loss loses the last second. 0 writes and flushes only once per second, so the process dying is enough to lose committed transactions. Pair 1 with sync_binlog = 1, which is already the default, for a configuration where an acknowledged commit is recoverable.

A working baseline for a 64 GB dedicated server on modern SSD:

[mysqld]
innodb_buffer_pool_size        = 48G
innodb_redo_log_capacity       = 8G
innodb_log_buffer_size         = 128M
innodb_flush_log_at_trx_commit = 1
innodb_lock_wait_timeout       = 10
innodb_print_all_deadlocks     = ON
innodb_io_capacity             = 4000
innodb_io_capacity_max         = 8000

gtid_mode                      = ON
enforce_gtid_consistency       = ON
sync_binlog                    = 1
binlog_expire_logs_seconds     = 604800
replica_parallel_workers       = 8
replica_preserve_commit_order  = ON

slow_query_log                 = ON
long_query_time                = 0.5
log_queries_not_using_indexes  = ON
min_examined_row_limit         = 1000

innodb_io_capacity is set below the default here deliberately. 10000 tells InnoDB it may issue that many I/O operations per second for background flushing and change-buffer merging; on a throttled cloud volume that estimate starves foreground queries.

Diagnostics

performance_schema is enabled by default, the opposite of MariaDB, where it is off and needs a restart. The tables that earn their keep:

-- Statement digests: aggregate by query shape, not by literal values
SELECT DIGEST_TEXT, COUNT_STAR, ROUND(SUM_TIMER_WAIT/1e12, 2) AS total_s,
       ROUND(AVG_TIMER_WAIT/1e9, 2) AS avg_ms, SUM_ROWS_EXAMINED, SUM_ROWS_SENT,
       SUM_NO_INDEX_USED, SUM_CREATED_TMP_DISK_TABLES
FROM performance_schema.events_statements_summary_by_digest
ORDER BY SUM_TIMER_WAIT DESC
LIMIT 20;

-- Who is blocking whom, right now
SELECT r.trx_id AS waiting_trx, r.trx_mysql_thread_id AS waiting_thread,
       SUBSTRING(r.trx_query, 1, 80) AS waiting_query,
       b.trx_id AS blocking_trx, b.trx_mysql_thread_id AS blocking_thread,
       SUBSTRING(b.trx_query, 1, 80) AS blocking_query,
       TIMEDIFF(NOW(), r.trx_wait_started) AS wait_age
FROM performance_schema.data_lock_waits w
JOIN information_schema.innodb_trx r ON r.trx_id = w.REQUESTING_ENGINE_TRANSACTION_ID
JOIN information_schema.innodb_trx b ON b.trx_id = w.BLOCKING_ENGINE_TRANSACTION_ID;

A high SUM_ROWS_EXAMINED against a low SUM_ROWS_SENT is the signature of the missing index that also causes the gap-lock problem above. SUM_NO_INDEX_USED finds the same thing more directly.

The sys schema is a set of views over performance_schema and information_schema with the formatting and ordering already applied. sys.schema_unused_indexes, sys.statements_with_full_table_scans and sys.innodb_lock_waits answer in one statement what the raw tables answer in ten.

EXPLAIN ANALYZE runs the statement and reports, per iterator, the estimated cost and row count alongside the actual timing, actual rows and loop count. It always uses tree-shaped output; actual time values are milliseconds. It works with SELECT, multi-table UPDATE and DELETE, and TABLE statements, and it can be cancelled with KILL QUERY.

EXPLAIN ANALYZE SELECT o.id FROM orders o JOIN customers c ON c.id = o.customer_id
  WHERE c.region = 'EMEA' AND o.created_at > NOW() - INTERVAL 1 DAY;

Read the gap between rows= in the cost estimate and rows= in the actual measurement. A large divergence is a statistics problem, not a plan problem, and points at ANALYZE TABLE or a histogram rather than at index hints. From 8.4, histograms can be declared AUTO UPDATE so that ANALYZE TABLE and InnoDB’s persistent statistics recalculation both refresh them; MANUAL UPDATE remains the default.

SHOW ENGINE INNODB STATUS is still where engine-level state lives. SEMAPHORES shows mutex and I/O contention; TRANSACTIONS shows long-running transactions and lock waits; LOG compares the current LSN against the last checkpoint, which tells you whether the redo log is under pressure; BUFFER POOL AND MEMORY gives hit rates; and LATEST DETECTED DEADLOCK holds the last deadlock. That is the only copy, unless innodb_print_all_deadlocks is on.

The slow query log is disabled by default and long_query_time defaults to 10 seconds, which on a healthy OLTP server logs nothing. Lower it, and bound log_queries_not_using_indexes with min_examined_row_limit so that small lookup tables do not flood the file.

What changed, and in which release

The removals bite hardest. binlog_transaction_dependency_tracking=WRITESET was standard advice for 8.0 replication tuning, and an option file carrying it forward prevents 8.4 from starting. The innodb_io_capacity jump to 10000 is the change most likely to hurt silently: on storage that cannot sustain it, background flushing competes with user queries and the symptom is latency, not an error.

Where MySQL is the wrong choice

Reach for PostgreSQL when the workload needs the type system or the extension ecosystem. MySQL has no equivalent of PostGIS-class geospatial support, no extension mechanism comparable to PostgreSQL’s, and nothing like the range of index types: no GiST, no GIN over arrays and JSON containment, no BRIN for append-only tables. If your query patterns need those access methods, no amount of InnoDB tuning substitutes.

Vector search is a concrete example. MySQL 9.x does add a VECTOR type (default 2048 entries, maximum 16383, single-precision, InnoDB only) with DISTANCE(), VECTOR_DIM() and conversion functions. But a VECTOR column cannot be used as a key of any kind (not primary, unique, foreign or partitioning) and cannot be a histogram source, so similarity search over it is a scan. There is no approximate-nearest-neighbour index in the community server. If you need one in your operational database, pgvector provides it in PostgreSQL; see pgvector for the index types and their recall trade-offs.

Do not choose MySQL for write scale-out. Group Replication is a high-availability mechanism: every member certifies and applies every write-set, so the group’s write capacity is that of its slowest member, and the nine-member limit and 143 MB transaction cap are hard. Scaling writes needs sharding or a different architecture.

Do not choose it for analytics on the community server. There is no columnar engine in core, no parallel query beyond InnoDB’s parallel table scans for a few operations, and the optimiser is built for OLTP access paths.

And if what you want is a MySQL-compatible server and not MySQL specifically, read MariaDB first. The two projects share a wire protocol and an InnoDB lineage and have diverged in system tables, JSON storage, GTID format and authentication. The divergence is enough that “drop-in replacement” is no longer true in either direction. That page covers the differences in detail; this one does not repeat them.