How MySQL actually works in production: a mental model for operators

MySQL is a connection-oriented request processor with a pluggable storage engine. In production, that engine is almost always InnoDB. Operating MySQL at scale means managing memory pressure, write-ahead log capacity, version-chain traversal, and fsync latency. The optimizer matters, but the stalls that wake you up start inside InnoDB.

This guide maps the subsystems that fail in production. It assumes you know how to run SHOW PROCESSLIST and need to move from symptoms to root cause: why the server stalls, why replication drifts, or why a fast query suddenly saturates disk I/O.

What it is and why it matters

MySQL Community Edition uses a connection-per-thread model. Every client receives a dedicated thread for the life of the connection. Each thread allocates session memory: sort buffers, join buffers, temporary tables, and net buffers. Connection count is therefore a direct multiplier of memory consumption and internal mutex contention, not just a concurrency limit.

InnoDB manages a fixed-size buffer pool, a write-ahead redo log, undo logs for MVCC, and background threads that flush dirty pages and purge old row versions. The binary log sits above InnoDB and feeds replication. These components interact in repeatable failure archetypes. Understanding them lets you map symptoms to causes without guessing.

How it works

flowchart TD
    Client --> Conn[Connection thread]
    Conn --> Parser[Parser / Optimizer]
    Parser --> InnoDB[InnoDB Engine]
    InnoDB --> BP[Buffer Pool LRU Young/Old]
    InnoDB --> Redo[Redo Log WAL]
    InnoDB --> Undo[Undo Log MVCC Versions]
    BP --> DataFiles[Data Files]
    BP --> Cleaners[Page Cleaner Threads]
    Undo --> Purge[Purge Thread]
    Redo --> Checkpoint[Checkpoint Flush]
    InnoDB --> Binlog[Binary Log]
    Binlog --> Repl[Replication]

Connection and query lifecycle. A connection arrives, authenticates, and receives a thread. The server parses the SQL, optimizes it using table and index statistics, and executes the plan. Every data access goes through InnoDB.

Buffer pool. The buffer pool is a modified LRU cache of 16 KB data and index pages and the single strongest predictor of read performance. InnoDB divides the LRU into young and old sublists. New pages are inserted at the midpoint, by default 37 percent from the tail, controlled by innodb_old_blocks_pct. A page is promoted to the young sublist only if it is accessed again within innodb_old_blocks_time (default 1000 ms). This protects hot pages from eviction by full table scans or read-ahead.

All reads and writes go through the buffer pool. A write modifies a page in memory and marks it dirty. Page cleaner threads flush dirty pages to data files in the background.

Redo log (WAL). MySQL 8.0.30 introduced dynamic redo log capacity via innodb_redo_log_capacity, replacing the static innodb_log_file_size and innodb_log_files_in_group variables. InnoDB maintains 32 redo log files named #ib_redo{N} inside the #innodb_redo directory. Dedicated log_writer, log_flusher, and log_checkpointer threads handle WAL asynchronously since MySQL 8.0.11.

Because the redo log is circular, InnoDB must flush dirty pages to make space. The distance between the current log sequence number and the last checkpoint is the checkpoint age. When checkpoint age approaches capacity, InnoDB forces a synchronous flush. All writes stall until pages are flushed. This is a hard cliff, not gradual degradation.

Undo log and MVCC. InnoDB writes an undo log record for every row modification so the transaction can roll back. These records also enable consistent reads for other transactions. Each row carries DB_TRX_ID (last modifying transaction), DB_ROLL_PTR (pointer to the undo record), and DB_ROW_ID (cluster key if needed). Update undo logs are retained until no open transaction can see the older version. The purge thread removes obsolete rows asynchronously.

If a long-running transaction holds a read view, the purge thread cannot discard update undo logs. The history list length grows. Subsequent MVCC reads traverse longer version chains, causing progressive degradation. Undo tablespaces grow unbounded until the transaction completes.

Change buffer. The change buffer caches modifications to secondary index pages that are not in the buffer pool, merging them when those pages are later read. It can occupy up to 50 percent of the buffer pool (default 25 percent). MySQL 8.4 changed the default for innodb_change_buffering from all to none. Change buffering is not supported for descending indexes or primary keys containing descending columns.

Adaptive hash index (AHI). InnoDB builds a hash index on-demand for frequently accessed B-tree pages. It is enabled by default and partitioned into eight parts (innodb_adaptive_hash_index_parts = 8) to reduce latch contention. Under high concurrency with hot index access, the AHI’s internal rw-lock can become a severe bottleneck. Disable it dynamically:

SET GLOBAL innodb_adaptive_hash_index = OFF;

Warning: This takes effect immediately. Monitor query throughput before and after; disabling AHI can regress read performance if the hash index was active for your working set.

Durability path. A committed transaction is durable only after its redo log record is fsynced to disk. With innodb_flush_log_at_trx_commit = 1 (default), InnoDB fsyncs the redo log on every commit. If the binary log is enabled and sync_binlog = 1, the binlog is also fsynced. Storage latency dominates commit latency here. Even with idle CPU, slow fsyncs stall commits.

Replication. The binary log records data-changing statements. A replica’s I/O thread fetches events from the source to the relay log; SQL/applier threads replay them. MySQL 8.0.27 and later default to multi-threaded apply (replica_parallel_workers = 4). GTID-based replication is the recommended mode for topology management and precise lag measurement.

Where it shows up in production

Buffer pool miss and disk saturation. When the working set exceeds the buffer pool, the hit ratio drops and disk I/O saturates. Query latency increases non-linearly: slowly from 99.9 percent to 99 percent, rapidly from 99 percent to 95 percent, and catastrophically below 95 percent. Watch for Innodb_buffer_pool_reads spiking and Innodb_buffer_pool_wait_free becoming nonzero.

Redo log checkpoint stall. Write-heavy workloads fill the redo log faster than page cleaners flush dirty pages. Checkpoint age crosses 75 percent, then 85 percent, then 90 percent. InnoDB enters synchronous flushing. Write throughput collapses to near zero. CPU may be idle while commits hang on fsync.

History list explosion. A single idle transaction, such as a BEGIN without COMMIT or an unclosed ORM session, holds an MVCC read view. The purge thread stops cleaning update undo logs. trx_rseg_history_len grows past one million. Read queries slow down across the board because every consistent read traverses a longer version chain. This is silent and gradual.

Metadata lock cascade. A DDL statement such as ALTER TABLE needs an exclusive metadata lock. If a long-running transaction holds a shared metadata lock on the same table, the DDL waits. All new DML on that table queues behind the DDL. Within minutes, Threads_connected approaches max_connections and queries time out. Only one table is affected, which makes the pattern confusing.

Connection exhaustion. Slow queries or lock waits cause connections to pile up. Once Threads_connected reaches max_connections, new connections are refused instantly. This is a hard cliff. The symptom is often “MySQL is down” when the server is healthy but full.

Replication lag spiral. If the replica’s apply threads cannot keep up with the source write rate, Seconds_Behind_Source grows monotonically. Relay logs accumulate. If the source expires binlogs before the replica consumes them, replication breaks and requires a rebuild.

Tradeoffs and when to tune them

• innodb_buffer_pool_size: Size to 60 to 80 percent of available RAM. Too small and you hit the buffer pool cliff. Too large and you starve the OS, connection buffers, and other caches. Dynamic since MySQL 5.7.5.
• innodb_redo_log_capacity: Post-8.0.30, this is dynamic. Size it so peak checkpoint age stays below 50 percent of capacity. Pre-8.0.30, changing redo log size requires a restart.
• innodb_io_capacity and innodb_io_capacity_max: These cap the IOPS that page cleaner threads issue. If your storage has headroom, raise innodb_io_capacity_max to help InnoDB keep up with write bursts without hitting the checkpoint cliff.
• innodb_page_cleaners: Defaults to innodb_buffer_pool_instances. If dirty page flushing cannot keep up with your write rate, increasing this may help, but the root cause is often undersized redo log capacity or slow storage.
• innodb_flush_log_at_trx_commit and sync_binlog: Setting innodb_flush_log_at_trx_commit = 2 writes to the OS page cache without fsyncing on every commit. It improves write throughput but sacrifices durability: an OS crash can lose up to one second of committed transactions. Many operators misconfigure this expecting “fast but safe.” Do not use this on a primary that cannot tolerate data loss.
• innodb_adaptive_hash_index: If you see btr_search latch waits in SHOW ENGINE INNODB STATUS or high CPU with low throughput, disable AHI dynamically. Many high-performance operators disable it by default.
• innodb_change_buffering: MySQL 8.4 defaults to none. If you are upgrading from 8.0 and relied on change buffering, set it explicitly to all if your workload benefits from deferred secondary index merges.
• innodb_max_purge_lag: This setting deliberately throttles DML when the history list grows too long. It is a safety valve, not a fix for forgotten transactions.

Signals to watch in production

Seconds_Behind_Source is unreliable in several scenarios. For critical decisions, use GTID set comparison or heartbeat tables.

How Netdata helps

Netdata collects MySQL metrics from SHOW GLOBAL STATUS, INFORMATION_SCHEMA.INNODB_METRICS, and performance_schema without log parsing or a database-side agent. Use it to:

• Correlate Threads_running with CPU, disk latency, and buffer pool hit ratio to distinguish lock contention from I/O saturation.
• Track checkpoint age as a ratio of redo log capacity and alert before the synchronous flush cliff.
• Monitor trx_rseg_history_len and active transaction age from INNODB_TRX to catch purge lag before reads degrade.
• Surface metadata lock waits from performance_schema.metadata_locks that standard status variables do not expose.
• Compare GTID sets across replicas to detect ordinary lag versus errant transactions or true divergence.