MySQL Under the Hood: InnoDB Buffer Pool, MVCC Isolation, the Optimizer and Binlog Replication

What this post covers

This guide combines hands-on SQL with how MySQL works under the hood: InnoDB storage layout, MVCC and isolation, the query optimizer, replication, and production patterns such as pooling and backups. Examples assume MySQL 8.0 or 8.4 LTS.

Slowness is rarely “because MySQL is slow.” It usually comes from missing or misordered indexes, predicates that cannot use an index, lock contention, replication lag, or a buffer pool too small for the working set. Indexing the handful of hottest queries usually helps more than any server-level tuning. The slowdowns that stay mysterious after that are most often long-running transactions that keep InnoDB from purging old row versions, and stale optimizer statistics that make the planner pick the wrong index. The sections below explain the mechanisms behind each of these.

---

What is MySQL?

MySQL is a widely deployed open-source relational database. For nearly all applications today the storage engine that matters is InnoDB (the default since 5.5), which implements ACID transactions, row-level locking, foreign keys, and crash recovery through redo logging. Its strengths in production are good OLTP performance on well-indexed queries, mature replication and operational tooling, and broad support from drivers, hosting providers, and managed services.

---

InnoDB storage engine internals

Understanding InnoDB explains why primary key design, secondary indexes, and write amplification behave the way they do.

Buffer pool and pages

InnoDB caches data and index pages in the buffer pool (innodb_buffer_pool_size). Tablespace files are divided into pages (16 KiB by default), grouped into extents and segments. When a query needs a page that is not cached, InnoDB reads it from disk into the buffer pool; modified (dirty) pages are written back later by background flushing.

If the working set does not fit in the buffer pool, queries turn into random disk reads and latency becomes erratic. On a dedicated database host, giving the buffer pool around 70–80% of RAM is a common starting point, leaving room for per-connection buffers and the OS. Check Innodb_buffer_pool_reads (reads that missed the pool) against Innodb_buffer_pool_read_requests under real traffic rather than relying on the rule of thumb.

Redo log and crash recovery

InnoDB modifies pages in memory and records each change in the redo log before the change is considered durable. After a crash, InnoDB replays the redo log to bring pages up to date with every committed transaction. The redo log is InnoDB’s write-ahead log (WAL); because it is written sequentially, a commit costs a sequential log write instead of random writes to every modified page.

innodb_flush_log_at_trx_commit controls when the redo log reaches disk:

Value	At commit	What a crash can lose
1 (default)	write and fsync the redo log	nothing committed
2	write to the OS page cache; fsync about once per second	up to about a second of commits if the OS or machine crashes (a mysqld crash alone loses nothing)
0	nothing; write and fsync about once per second	up to about a second of commits even if only mysqld crashes

With binary logging enabled, sync_binlog = 1 makes the binlog durable at commit as well. Using 1 for both is the setting that keeps InnoDB data and the binlog consistent after a crash, which matters as soon as replicas or point-in-time recovery depend on the binlog.

Doublewrite buffer

A 16 KiB page write is not atomic on most storage; a crash mid-write can leave a torn page that the redo log cannot repair, because redo records describe changes to a page rather than its full contents. InnoDB therefore writes pages first to the doublewrite area and then to their real location. If a page is torn, recovery restores it from the doublewrite copy. This costs extra write I/O, which is why it is sometimes disabled on file systems or devices that guarantee atomic 16 KiB writes.

Undo logs and rollback

Undo logs hold the previous versions of modified rows. They serve two purposes: rolling back a transaction, and building older row versions for consistent reads under MVCC. Undo records can be removed (purged) only when no active read view can still need them.

Clustered index (primary key)

InnoDB stores each table as a B+tree clustered on the primary key: the leaf pages of the primary key index contain the full rows. Secondary indexes store the primary key value, not a physical row pointer, so a lookup through a secondary index finds the primary key and then does a second lookup in the clustered index (unless the secondary index covers every column the query needs).

Two consequences follow. A wide primary key makes every secondary index wider. And inserting in random key order (for example, random UUIDv4 primary keys) scatters inserts across the whole tree, causing page splits, half-empty pages, and a working set as large as the table. An auto-increment BIGINT, or a time-ordered identifier such as UUIDv7 stored in BINARY(16), keeps inserts appending at the right edge of the tree.

Change buffer

When a change affects a secondary index page that is not in the buffer pool, InnoDB can record the change in the change buffer and merge it later when the page is read, saving a random read at write time. This applies only to non-unique secondary indexes, because checking uniqueness requires reading the page anyway. MySQL 8.4 changed the default innodb_change_buffering to none, since on SSDs the saved reads rarely justify the merge cost.

---

Transaction isolation and MVCC

InnoDB uses Multi-Version Concurrency Control (MVCC) so that ordinary reads do not block writes and writes do not block ordinary reads. Each row carries the ID of the transaction that last modified it and a pointer into the undo log; following that pointer reconstructs older versions.

Read views

A plain SELECT (a consistent, non-locking read) uses a read view: a snapshot of which transactions were committed when the view was created. For each row, InnoDB walks back the version chain until it finds a version that the read view is allowed to see. Writes still take row locks, and locking reads (SELECT ... FOR UPDATE, FOR SHARE) read the latest committed version and lock it rather than using the snapshot.

Isolation levels

InnoDB supports READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ (the default), and SERIALIZABLE. The two in common use differ in when the read view is created.

Under REPEATABLE READ, the read view is created at the first consistent read in the transaction and reused, so repeated plain SELECTs see the same snapshot. Locking reads and UPDATE/DELETE statements take next-key locks (a lock on the index record plus the gap before it) on the index ranges they scan, which prevents other transactions from inserting rows into those ranges.

Under READ COMMITTED, each consistent read creates a fresh read view, so a later statement in the same transaction can see rows committed in the meantime. Gap locking is mostly disabled, which reduces lock waits and deadlocks for write-heavy workloads at the cost of allowing phantoms. Many applications that came from PostgreSQL or Oracle (both default to read committed) run MySQL in READ COMMITTED for that reason.

Phantoms and next-key locks

A phantom is a row that appears in a second execution of the same query because another transaction inserted it in between. Under REPEATABLE READ, plain SELECTs never see phantoms because they reuse the snapshot, and locking reads avoid them through next-key locks.

The cost of next-key locks is that they depend on how the statement uses indexes. An UPDATE ... WHERE status = 'pending' without an index on status must scan the whole clustered index, and it locks every row it scans and the gaps between them, which effectively blocks inserts and updates on the entire table until the transaction ends. InnoDB does not escalate locks; it simply locks everything it touched. An index that matches the WHERE clause keeps the locked range small.

Purge lag and history list length

Old row versions are removed by background purge threads once no read view needs them. A transaction that stays open for minutes or hours, even an idle one that ran a single SELECT under REPEATABLE READ, pins its read view and blocks purge of everything modified since. The “History list length” in SHOW ENGINE INNODB STATUS grows, undo tablespaces grow, and consistent reads by other sessions slow down because they walk longer version chains.

To find the culprit, look at information_schema.INNODB_TRX for transactions with an old trx_started, and fix the application path that leaves them open (missing commit, autocommit disabled in a connection pool, a transaction wrapped around a slow external call).

---

Query optimizer

The optimizer is cost-based: for each query it estimates the cost of candidate access methods (index lookups, range scans, full scans) and join orders, then picks the cheapest. Since MySQL 8.0.18, equi-joins without a usable index use a hash join instead of the older block nested-loop algorithm.

Statistics and histograms

Cost estimates depend on statistics: index cardinality from sampled pages, and, since 8.0, optional histograms on non-indexed columns for skewed value distributions. Stale or misleading statistics lead to bad plans, such as an index being ignored or the wrong join order. Run ANALYZE TABLE after large bulk loads or deletes, create histograms (ANALYZE TABLE t UPDATE HISTOGRAM ON col) for skewed columns used in filters, and validate plans with EXPLAIN and EXPLAIN ANALYZE (8.0.18+) using realistic parameter values.

Sargable predicates

Wrapping an indexed column in a function prevents a range scan on that index:

-- Cannot use a range scan on an index over created_at
SELECT * FROM users WHERE YEAR(created_at) = 2026;

-- Rewritten as a range on the raw column
SELECT * FROM users
WHERE created_at >= '2026-01-01' AND created_at < '2027-01-01';

Implicit type conversions have the same effect: comparing a VARCHAR column to a number (WHERE phone = 01012345678) converts every row’s value and cannot use the index on phone. If you truly need to filter on an expression, MySQL 8.0.13+ supports functional indexes on that expression.

EXPLAIN in practice

EXPLAIN SELECT * FROM users WHERE email = '[email protected]';
EXPLAIN FORMAT=JSON SELECT * FROM users WHERE email = '[email protected]';
EXPLAIN ANALYZE SELECT * FROM users WHERE email = '[email protected]';

In the tabular output, read type (access method: const, ref, range, index, ALL), key (the chosen index), rows (estimated rows examined), and Extra. type = ALL on a large table, a huge rows estimate, or Using filesort/Using temporary on big result sets are the usual warning signs. EXPLAIN ANALYZE actually runs the query and shows real row counts and timings next to the estimates, which makes bad estimates obvious.

Selecting only the columns you need matters beyond network transfer: a query whose columns are all in a secondary index can be answered from that index alone (Using index in Extra), skipping the second lookup into the clustered index.

SELECT u.id, u.name, p.title
FROM users u
LEFT JOIN posts p ON u.id = p.user_id
WHERE u.is_active = 1;

Optimizer hints

Optimizer hints (/*+ INDEX(t idx) */, /*+ JOIN_ORDER(...) */) and FORCE INDEX can fix a plan when statistics mislead the optimizer. They also freeze that plan even when data distribution changes or a later version would choose better, so treat them as a documented, temporary fix and revisit them after upgrades.

---

Indexing strategy

Single-column and composite indexes

CREATE INDEX idx_email ON users(email);
CREATE INDEX idx_user_created ON orders(user_id, created_at);
CREATE UNIQUE INDEX idx_email_unique ON users(email);
SHOW INDEX FROM users;
DROP INDEX idx_email ON users;

A composite index is usable from its leftmost column onward. Put columns compared with equality first. After that, a column used in a range condition can still use the index, but any columns after the range column can no longer be used for filtering by range or for avoiding a sort. So for WHERE user_id = ? ORDER BY created_at, the index (user_id, created_at) serves both the filter and the sort, while for WHERE user_id = ? AND amount > 100 ORDER BY created_at, an index on (user_id, amount, created_at) cannot avoid the filesort. Confirm with EXPLAIN.

Full-text indexes

CREATE FULLTEXT INDEX idx_content ON articles(title, content);

SELECT * FROM articles
WHERE MATCH(title, content) AGAINST('mysql tutorial' IN NATURAL LANGUAGE MODE);

SELECT * FROM articles
WHERE MATCH(title, content) AGAINST('+mysql -oracle' IN BOOLEAN MODE);

The default full-text parser splits on whitespace and punctuation, so for Chinese, Japanese, or Korean text use the ngram parser (WITH PARSER ngram). Words shorter than innodb_ft_min_token_size (default 3) and stopwords are not indexed.

---

Partitioning

Partitioning splits one table into several physical pieces by a key. Queries that filter on the partition key can skip irrelevant partitions (pruning), and old data can be removed with ALTER TABLE ... DROP PARTITION, which is nearly instant compared with deleting millions of rows. Every unique key on a partitioned table, including the primary key, must include all columns used in the partitioning expression, and foreign keys are not supported on partitioned InnoDB tables.

Range partitioning

CREATE TABLE orders (
    id BIGINT NOT NULL,
    user_id INT NOT NULL,
    amount DECIMAL(10, 2),
    created_at DATE NOT NULL,
    PRIMARY KEY (id, created_at)   -- must include the partitioning column
)
PARTITION BY RANGE (YEAR(created_at)) (
    PARTITION p2024 VALUES LESS THAN (2025),
    PARTITION p2025 VALUES LESS THAN (2026),
    PARTITION p2026 VALUES LESS THAN (2027),
    PARTITION p_future VALUES LESS THAN MAXVALUE
);

List partitioning

CREATE TABLE users (
    id INT NOT NULL,
    name VARCHAR(100),
    country CHAR(2) NOT NULL
)
PARTITION BY LIST COLUMNS(country) (
    PARTITION p_us VALUES IN ('US'),
    PARTITION p_kr VALUES IN ('KR'),
    PARTITION p_jp VALUES IN ('JP'),
    PARTITION p_eu VALUES IN ('DE', 'FR', 'GB')
);

MySQL has no DEFAULT partition for list partitioning: inserting a value not listed in any partition fails with an error. Either list every expected value or use a different partitioning scheme.

---

Replication

Replication ships changes from a source (primary) to replicas through the binary log. A replica’s I/O thread copies the source’s binlog events into a local relay log, and applier threads replay them.

Binary log formats

STATEMENT logs the SQL statements themselves; it is compact but unsafe for non-deterministic statements (UUID(), LIMIT without ORDER BY, some triggers), which can produce different results on the replica. ROW logs the before and after images of each changed row; it is deterministic but generates more log volume for statements that change many rows. MIXED uses statements by default and switches to row format for unsafe statements. ROW has been the default since 5.7.7, and the binlog_format setting itself is deprecated in recent 8.x releases.

GTID

With global transaction identifiers (GTIDs), every committed transaction gets an ID of the form source_uuid:sequence. A replica knows exactly which transactions it has applied, so after a failover it can connect to a new source with SOURCE_AUTO_POSITION = 1 and fetch what it is missing, instead of someone calculating a binlog file and position by hand.

Asynchronous and semi-synchronous replication

By default, replication is asynchronous: the source commits without waiting for any replica, so a replica can fall behind under load, and a source crash can lose transactions that had not yet been sent. Applications reading from replicas must accept stale reads.

Semi-synchronous replication makes the source wait until at least one replica confirms it has received (written to its relay log) the transaction before the commit returns to the client. This narrows the window for data loss at the cost of a network round trip per commit. If no replica acknowledges within the timeout, the source falls back to asynchronous replication.

Parallel appliers

Replicas can apply transactions with several worker threads (replica_parallel_workers, default 4 in 8.0.27+) as long as the transactions do not depend on each other. Dependency tracking based on write sets (the rows each transaction modified) lets transactions that committed concurrently on the source run concurrently on the replica, which keeps lag low on write-heavy sources.

Group Replication and InnoDB Cluster

Group Replication runs a consensus protocol among members so that a transaction commits only after a majority agrees, and it handles membership changes and primary election automatically. InnoDB Cluster packages it with MySQL Shell and MySQL Router for deployment and connection routing. It adds operational complexity and has its own requirements (every table needs a primary key, network latency between members directly affects commit latency), so evaluate it against your availability targets.

Setting up a replica

Source configuration (binary logging is on by default in 8.0, shown here for clarity):

[mysqld]
server-id = 1
log_bin = /var/log/mysql/mysql-bin
gtid_mode = ON
enforce_gtid_consistency = ON

Replica configuration:

[mysqld]
server-id = 2
relay_log = /var/log/mysql/mysql-relay-bin
gtid_mode = ON
enforce_gtid_consistency = ON
read_only = ON
super_read_only = ON

Avoid binlog_do_db/replicate_do_db filters unless you understand them: with statement-based logging they filter by the session’s default database (USE db), not by the database a statement actually modifies, which silently skips or includes the wrong changes.

SQL (8.0.23+ syntax; SHOW BINARY LOG STATUS is 8.2+, older versions use SHOW MASTER STATUS):

-- on the source
CREATE USER 'repl'@'10.0.0.%' IDENTIFIED BY '...';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'10.0.0.%';

-- on the replica, after restoring a consistent copy of the source's data
CHANGE REPLICATION SOURCE TO
  SOURCE_HOST = 'primary-host',
  SOURCE_USER = 'repl',
  SOURCE_PASSWORD = '...',
  SOURCE_AUTO_POSITION = 1,
  SOURCE_SSL = 1;
START REPLICA;
SHOW REPLICA STATUS\G

The default authentication plugin in MySQL 8 is caching_sha2_password, which requires either a TLS connection (SOURCE_SSL = 1) or GET_SOURCE_PUBLIC_KEY = 1 for the replication user; otherwise the replica fails to connect with an authentication error. The privilege is still named REPLICATION SLAVE. FLUSH PRIVILEGES is not needed after CREATE USER and GRANT.

---

Transactions in practice

START TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE id = 1 AND balance >= 100;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;   -- or ROLLBACK;

The application should check that the first UPDATE actually changed a row before committing; otherwise a transfer from an account with insufficient funds would credit the second account without debiting the first. Locking rows in a consistent order (for example, by ascending id) across all transfer code paths prevents two concurrent transfers from deadlocking.

SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
SELECT @@transaction_isolation;

---

Production patterns

Connection pooling

Each MySQL connection is served by a dedicated server thread with its own buffers, so opening a new connection per HTTP request is slow and, at scale, exhausts max_connections. Use a connection pool in the application, and consider a proxy such as ProxySQL or RDS Proxy when many application instances each keep their own pool.

Read scaling with replicas

Routing read-mostly queries to replicas adds read capacity, roughly in proportion to the number of replicas as long as the source keeps up with writes. Because replicas lag, flows that read their own writes (a user saves a profile and immediately reloads it) should read from the primary, or wait until the replica has applied the transaction, for example with WAIT_FOR_EXECUTED_GTID_SET.

Backups: logical vs physical

mysqldump produces SQL statements. It is portable and allows selective restores, but dumping and especially restoring large databases is slow because every row is reinserted and every index rebuilt. Physical backup tools such as Percona XtraBackup or MySQL Enterprise Backup copy InnoDB data files while the server runs and are much faster to restore for large datasets. In both cases, regularly test that a backup actually restores; a backup that has never been restored is unverified.

# Consistent logical backup of InnoDB tables without locking them
mysqldump --single-transaction --routines --triggers --events \
  --source-data=2 mydb > mydb.sql      # --master-data=2 before 8.0.26
mysql mydb < mydb.sql

--single-transaction dumps everything from one consistent snapshot without blocking writes (InnoDB tables only). --source-data=2 records the binlog position as a comment, which you need for point-in-time recovery or to start a replica from the dump.

#!/bin/bash
# Credentials come from ~/.my.cnf ([client] section, mode 600), not the command line
DATE=$(date +%Y%m%d_%H%M%S)
mysqldump --single-transaction --routines --triggers mydb | gzip > /backups/mydb_${DATE}.sql.gz
find /backups -name "mydb_*.sql.gz" -mtime +7 -delete

Passing the password with -p"$MYSQL_PASSWORD" exposes it in the process list to other users on the host, which is why an option file is preferred.

Monitoring

The metrics that matter most are buffer pool misses (Innodb_buffer_pool_reads), redo log waits (Innodb_log_waits), history list length, row lock waits and deadlocks, replication lag (Seconds_Behind_Source, or better, a heartbeat table), slow queries, and disk latency. Alert on sustained lag, a steadily growing history list, rising lock waits, and replicas whose I/O or SQL thread has stopped.

Configuration sketch

[mysqld]
innodb_buffer_pool_size = 8G
max_connections = 500
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 0.5

The query cache was removed in MySQL 8.0, so old tuning guides that recommend query_cache_size do not apply.

mysqldumpslow -s t -t 10 /var/log/mysql/slow.log

mysqldumpslow groups the slow log by normalized query and sorts by total time. pt-query-digest from Percona Toolkit gives a more detailed breakdown, and the performance_schema.events_statements_summary_by_digest table provides similar data without enabling the slow log.

---

Related Articles

• MongoDB Internals for Production
• PostgreSQL Internals That Matter
• MySQL EXPLAIN and Query Optimization Guide | Index Tuning