MySQL

Imagine a single shared whiteboard in an office where anyone can write down the answer to a common question so others can read it instead of recalculating it themselves. This works fine if the office rarely changes any of the underlying facts. But if even one person updating any related fact anywhere in the building requires erasing the entire whiteboard and someone standing in line to physically access it one at a time to either read or erase it, then in a busy, constantly-changing office, people spend more time queued up at the whiteboard than they would have spent just recalculating the answer themselves.

MySQL's query cache stored the complete result set of a SELECT statement, keyed by the literal text of the query, so that an identical subsequent query could skip execution entirely and return the cached rows. This looked attractive for read-heavy, rarely-changing tables, and early MySQL deployments in the 2000s, often serving mostly-static content, saw real benefit. The design's fatal flaw was invalidation granularity: the query cache tracked dependencies at the table level, not the row or even the specific rows a query actually touched, so a single INSERT, UPDATE, or DELETE on a table invalidated every cached query that referenced that table anywhere in its query text, regardless of whether the write actually affected the rows those cached queries cared about.

Internally, both reading from and invalidating the query cache required acquiring a single global mutex (a lock that only one thread can hold at a time) protecting the entire cache structure, because the cache was a single shared, global resource rather than something sharded by table or connection. On a busy server with many concurrent connections doing a mix of reads and writes, every query — cached or not — had to briefly contend for this one global lock just to check or update cache state, meaning the query cache added serialization overhead to every single query on the server, not just the ones benefiting from a cache hit.

In production, this played out as query cache being genuinely useful on read-only or read-mostly workloads with low connection concurrency, and actively harmful — sometimes reducing throughput by more than half — on typical modern OLTP workloads with frequent writes and high concurrency, which describes most production web applications. MySQL's own documentation eventually recommended disabling it (query_cache_type=0) for exactly this reason well before it was formally removed, and the team ultimately removed it entirely in 8.0 rather than trying to fix the architecture, because a single global mutex is fundamentally incompatible with high-concurrency scaling regardless of how the invalidation logic is tuned.

The modern replacement isn't one single feature but a layered approach: the InnoDB buffer pool already caches actual data pages and indexes in memory (which is a much more granular, engine-level cache that doesn't suffer the same table-wide invalidation problem), and for caching complete query results, teams use an external cache like Redis or Memcached with explicit application-level cache keys and TTLs, or a proxy layer like ProxySQL that can cache specific query result sets with fine-grained rules. The gotcha for teams migrating off an old MySQL version that still relied on the query cache: removing it doesn't just require flipping a config flag, because any application that was silently depending on the query cache masking a genuinely slow, unindexed query will suddenly see that query's real latency for the first time in years, and teams have been caught off guard discovering years-old unindexed queries that 'always ran fine' purely because the query cache had been quietly absorbing the cost.

Picture a highway with a single stalled car in one lane during rush hour. From a satellite view measuring total fuel burned (CPU usage), everything looks almost normal — most cars are idling, not accelerating. But every driver on the road is experiencing enormous delay, because they're all queued up behind one blockage, not because the road network itself is overloaded. Looking at aggregate 'engine usage' across all the idling cars would completely mislead you about where the actual problem is.

MySQL's Threads_running counts connections actively executing a statement, as opposed to Threads_connected which counts all open connections whether idle or busy. When Threads_running climbs while CPU stays flat, it means threads are executing but spending their time blocked — waiting on a row lock, a table lock, an internal mutex, or I/O — rather than burning CPU cycles computing something. This is a critical distinction because a CPU-bound problem and a lock-contention problem look completely different in every other regard but can both present as 'the database is slow,' and applying a CPU-bound fix (like adding more compute) to a lock-contention problem does nothing.

The diagnostic sequence starts with SHOW FULL PROCESSLIST or querying performance_schema.threads and performance_schema.data_lock_waits, looking specifically at the State column for values like 'Waiting for table metadata lock,' 'Waiting for row lock,' or 'statistics' — different wait states point to entirely different root causes. If many threads show 'Waiting for row lock' or similar, SHOW ENGINE INNODB STATUS's TRANSACTIONS section reveals exactly which transaction is holding the lock everyone else is waiting on, often a single long-running or forgotten-open transaction (an application connection that started a transaction and never committed or rolled back, perhaps due to a bug in error handling) blocking dozens of otherwise fast queries.

In production, engineers correlate Threads_running against Threads_connected and max_connections, because a separate but easily confused failure mode is connection exhaustion — the application's connection pool holding onto more connections than the database allows, causing new connection attempts to queue or fail outright, which superficially also presents as 'everything is slow' but requires a completely different fix (connection pool sizing or max_connections tuning) than lock contention does. Distinguishing the two quickly during an incident saves critical minutes, since Threads_running high with Threads_connected near max_connections points at exhaustion, while Threads_running high with Threads_connected comfortably below max_connections but many threads in a lock-wait state points at contention.

The gotcha experienced engineers watch for: the actual root-cause transaction holding a lock is often not itself slow or expensive — it might be a completely ordinary UPDATE that ran fine, except the application connection that ran it never called COMMIT or ROLLBACK afterward due to a bug, leaving the transaction open (and its locks held) indefinitely while the connection sits idle. SHOW ENGINE INNODB STATUS and performance_schema will show this as a transaction with a long Trx_started time but essentially zero recent activity — it looks idle, not busy, which is exactly why teams scanning for 'the slow query' during an incident often miss it, since the actual blocking transaction isn't running anything slow at all, it's just never finishing.

A hospital emergency room's true stress signal isn't how many doctors are physically moving around at any moment — it's how long the waiting room has been filling up and how many patients are queued for a bed. A room could have doctors calmly standing by (low CPU-equivalent) while the actual bottleneck is beds not turning over fast enough (locks, I/O, connections), and by the time doctors themselves look overwhelmed, the real crisis started much earlier upstream.

MySQL's most commonly-watched infrastructure metric, CPU usage, measures compute time spent, which correlates well with query-plan-driven load like heavy sorting or hashing, but says nothing about time spent waiting — locks, disk I/O, network round trips to a replica, or semaphore waits inside InnoDB. A database experiencing serious internal pressure from any of these sources can show flat or even low CPU precisely because threads aren't computing, they're blocked, and blocked threads don't consume CPU cycles. This makes CPU usage a lagging or even entirely absent signal for the failure modes that most commonly cause real MySQL incidents in production.

InnoDB buffer pool hit ratio (the percentage of data page reads served from memory versus requiring a disk read) is a genuine leading indicator: as the working set grows beyond available buffer pool size, hit ratio degrades gradually before query latency visibly spikes, giving an early warning that either the buffer pool needs to grow or the working set needs to shrink (via archiving, sharding, or better indexes). History list length — the size of InnoDB's undo log, which grows when long-running transactions prevent old row versions from being purged under MVCC (MySQL's mechanism for letting readers see a consistent snapshot without blocking writers) — is another strong leading indicator: a growing history list means something is holding a long transaction open, and if left unchecked it directly degrades every subsequent query's performance because InnoDB has more old row versions to scan through.

In production, mature monitoring dashboards for MySQL prioritize buffer pool hit ratio, history list length, Threads_running relative to Threads_connected, and replication lag ahead of CPU and even ahead of raw query latency, specifically because these internal signals predict the latency spike before it happens, giving on-call engineers a window to intervene (killing a long-running transaction, scaling the buffer pool, adding a replica) before customers notice anything. Teams that alert primarily on CPU and query latency consistently get paged only once the incident is already fully user-visible, because those are lagging confirmations rather than early warnings.

The gotcha even experienced teams miss: history list length can grow silently from an application bug that opens a transaction and never closes it (not necessarily a large or complex transaction — a completely trivial SELECT run inside an ORM's auto-transaction wrapper that never commits can hold back purge indefinitely), and this metric climbing has no direct correlation with any specific slow query in the slow query log, since the 'culprit' transaction may not itself be running anything slow — it's just sitting open. This is why history list length has to be monitored as its own first-class signal rather than assumed to be implied by slow-query logging, which will show nothing wrong at all while the real problem quietly compounds in the background.

Think of a company where the CEO suddenly stops answering calls, and the leadership team needs to decide whether to promote a deputy. If only one assistant's phone call attempt failing is used as proof the CEO is unreachable, that assistant might just have a broken phone line, while the CEO is actually fine and everyone else can still reach them — promoting a deputy in that case creates two people simultaneously issuing directives to the company. The safe process requires several independent people confirming the CEO is truly unreachable, and once a deputy is promoted, actively revoking the original CEO's signing authority so they can't issue a conflicting order if they resurface unexpectedly.

MySQL replication automation tools like Orchestrator or MHA (Master High Availability) exist specifically to solve the failover decision safely, because a naive failover script that simply promotes 'a' replica the instant a health check fails is dangerous in exactly the way the analogy describes: a monitoring node's own network partition can make a perfectly healthy primary look unreachable, and if the automation acts on that single vantage point, it can promote a replica while the original primary is still alive and accepting writes from clients that haven't yet noticed the failover — this is split-brain, where two nodes both believe they're the authoritative primary.

Correctly designed failover automation requires multiple independent observers to agree the primary is actually down (cross-checking from several hosts in different network zones, not trusting a single health check), and uses GTID (Global Transaction Identifier, a unique ID assigned to every transaction that lets MySQL compare exactly how caught-up two servers are) to determine which replica has replicated the most complete set of transactions and is therefore the safest promotion candidate — promoting a replica that's behind means silently losing the delta of transactions the old primary had that never made it to the new primary. Before completing the promotion, the automation must fence the old primary: setting it read-only, revoking its ability to serve as a replication source, and ideally blocking it at the network or proxy layer, specifically so that if it comes back online after a transient issue (not an actual crash, just a network blip), it cannot accept writes that would silently diverge from the new primary's state.

In production, the fencing step is frequently the one teams skip when building a first version of failover automation, because it's the part with no positive-path benefit during normal operation — it only matters in the exact failure scenario it's designed for, so it's easy to under-test. Engineers validate failover automation with actual game-day exercises that simulate a network partition (not just killing the MySQL process, which is a cleaner and easier-to-handle failure than a network split), specifically because a hard process crash and a network partition produce very different symptoms to the monitoring system, and automation tuned only against the easy case (process crash) can behave dangerously against the harder, more realistic case (partition).

The gotcha that catches teams who built solid automation and still hit an incident: GTID-based promotion correctly picks the most caught-up replica, but if application-side connection routing (via a proxy, DNS, or service discovery) isn't updated atomically with the promotion, some application instances can keep writing to the old primary for a window after the database-level failover completed, because they cached a connection or resolved an old endpoint before the failover began — meaning the database layer did everything correctly, but the failure now happens one layer up, in connection routing, which many teams don't test as part of their failover drill because they consider the database automation itself to be 'the failover.'

Imagine a single cashier at a busy store re-ringing up every receipt from a much larger store's entire day of sales, one line item at a time, in the exact order they originally happened. Even if the register tape (network transfer) arrives instantly, the cashier can only process as fast as one person typing — replaying history one transaction at a time is fundamentally slower than the original store, which had dozens of registers running simultaneously.

MySQL replication historically applied every change from the binary log (binlog, the source's record of every data-changing statement or row event) using a single SQL thread on the replica, strictly in commit order, to guarantee the replica ends up in exactly the same state as the source. This was the safe default because naively parallelizing could apply conflicting changes out of order and corrupt data — but it meant a replica's apply capacity was capped at whatever a single CPU core and one thread of I/O could sustain, no matter how many cores or disks the replica actually had available.

Replication in MySQL is split into two independent threads: the IO thread, which streams binlog events from the source over the network and writes them to the replica's local relay log, and the SQL thread, which reads the relay log and actually applies the changes to the replica's data. Seconds_Behind_Source measures the SQL thread's lag relative to the source's current time, so diagnosing the bottleneck means checking whether the IO thread is caught up (relay log receipt is current) while the SQL thread lags — that isolates the problem to apply speed. Multi-threaded replication (enabled via replica_parallel_workers) fixes exactly this by allowing the SQL thread's job to be split across multiple worker threads, applying transactions from different logical partitions (by default, schema/database) concurrently as long as MySQL can prove they don't touch overlapping data.

In production, engineers watch Seconds_Behind_Source as the headline metric but drill into Slave_SQL_Running_State and performance_schema replication tables to see whether workers are actually parallelizing effectively or serializing anyway — a common trap is that if every write goes to a single schema or a small number of hot tables, replica_parallel_workers has almost nothing to parallelize across, because the default parallelization mode groups work by schema, and one busy schema is still effectively one lane. WRITESET-based parallelization (available in newer MySQL versions) parallelizes based on which actual rows a transaction touches rather than which schema, which unlocks real concurrency even for single-schema workloads, at the cost of needing row-based binlog format.

The gotcha that catches teams enabling multi-threaded replication for the first time: increasing replica_parallel_workers doesn't help, and can even make ordering-sensitive lag worse, if the workload is dominated by large single transactions (a big batch update or a long-running migration) — a multi-gigabyte transaction still applies as one atomic unit on one worker thread regardless of how many other workers are configured, so the real fix for large-transaction-driven lag is breaking up application-side batch jobs into smaller transactions, not tuning replication thread count, and no amount of replica-side configuration can parallelize a single indivisible transaction.

Picture two delivery drivers on a narrow one-lane bridge, each entering from opposite ends at the same moment, each blocking the other's path forward and unable to reverse without help. Neither driver did anything individually wrong — they just happened to start crossing at the same instant from opposite directions. Someone has to back one truck out to break the standoff, and the sensible choice is to back out whichever truck has the lighter, easier-to-reverse load, not whichever truck happened to enter the bridge first.

InnoDB (MySQL's default storage engine since 5.5) uses row-level locking to allow high concurrency, but that means two transactions can each hold a lock the other one needs — transaction A holds a lock on row 1 and is waiting for row 2, while transaction B holds row 2 and waits for row 1. InnoDB maintains an internal wait-for graph representing which transactions are blocked on which locks held by which other transactions, and it checks this graph for cycles proactively rather than waiting for a timeout, which is why deadlocks are detected in milliseconds rather than after lock_wait_timeout expires (that timeout is for genuine long waits, not deadlocks).

When a cycle is detected, InnoDB picks a victim transaction to roll back based on an internal heuristic approximating rollback cost — generally the transaction that has done less work (fewer modified rows) is killed, since undoing it is cheaper than undoing the other transaction's larger set of changes. This is a deliberate engineering tradeoff: InnoDB isn't trying to be 'fair' about which transaction started first, it's trying to minimize the total wasted work of resolving the deadlock, which is the right objective for overall system throughput even though it can feel arbitrary to the specific application that gets its transaction killed.

In production, engineers reduce deadlock frequency by keeping transactions short (holding locks for the shortest possible window), always accessing tables and rows in a consistent order across all code paths that touch multiple tables, ensuring proper indexes exist so UPDATE and DELETE statements don't lock far more rows than necessary via a full table scan, and considering READ COMMITTED isolation instead of the default REPEATABLE READ when gap locking behavior is causing unnecessary contention. Monitoring innodb_deadlocks and enabling innodb_print_all_deadlocks surfaces the exact competing statements so a team can identify a recurring pattern rather than treating each deadlock as a one-off.

The gotcha that defeats teams who did enforce consistent application-level lock ordering: InnoDB's default REPEATABLE READ isolation level uses gap locks and next-key locks (locks that cover the empty space between index entries, not just the index entries themselves) to prevent phantom reads, and these locks are taken based on how a query's WHERE clause maps onto the index it uses — not based on any explicit ordering the application controls. Two transactions can update completely different specific rows, in what looks like non-overlapping order from the application code, and still deadlock because their range-scan queries acquired overlapping gap locks on the same index. This is why deadlocks can appear between transactions whose application-level logic never seems to conflict, and the actual fix often requires either a covering index that avoids the gap-locking range scan, or explicitly switching that transaction to READ COMMITTED, not touching the application's lock ordering at all.

Think of InnoDB like a bank that keeps a detailed transaction journal of every step of every transfer, so that if the power goes out mid-transfer, the bank can replay the journal on restart and know exactly which transfers were fully completed, which were only half-done and must be reversed, and which never started. MyISAM is like a bank that just directly edits the ledger in place with no journal — if the power goes out mid-edit, there's no record of what state the edit was in, and the ledger page itself might be left half-written or corrupted with no way to automatically detect or repair it.

InnoDB became the default engine in MySQL 5.5 specifically because it provides ACID transactions (Atomicity, Consistency, Isolation, Durability — the guarantees that a transaction either fully happens or not at all, and survives a crash), row-level locking for concurrency, and crash recovery via a redo log and doublewrite buffer that let it detect and repair incomplete writes on restart. MyISAM predates all of this: it uses table-level locking (an entire table is locked for any write, killing concurrency under write-heavy load), has no transaction support at all, and has no crash recovery mechanism — a MyISAM table that was mid-write during a crash can come back corrupted, requiring a manual REPAIR TABLE that itself can lose data.

MyISAM tables persist in real environments for a few specific, recurring reasons: schemas migrated from very old MySQL installations that were never explicitly converted; ORM or migration tooling that hardcoded ENGINE=MyISAM years ago and nobody revisited; and historically, MyISAM's fulltext search support being a reason teams chose it before InnoDB gained fulltext indexing in 5.6. The danger is that a table's engine is easy to overlook in day-to-day work — SELECT and INSERT statements behave identically regardless of engine until something goes wrong, so a MyISAM table can sit unnoticed in a schema for years until the exact moment it matters most: during a crash, a failover, or a concurrency spike.

In production, the actual incident pattern looks like this: a server experiences an unclean shutdown (OOM kill, power loss, forced restart), and on restart, InnoDB tables recover automatically and transparently via redo log replay — completely invisible to the team, no action needed. MyISAM tables in the same crash come back marked as crashed or, worse, silently return incorrect results without being marked as crashed at all, and the team only discovers it when a report's numbers look wrong or a query errors out days later. Engineers audit engine usage across a schema periodically with information_schema.TABLES specifically because engine choice is invisible in normal query behavior and only surfaces at the worst possible moment.

The gotcha that catches teams doing an ENGINE=InnoDB conversion under time pressure: ALTER TABLE ... ENGINE=InnoDB on a large MyISAM table is not a quick metadata change — it's a full table rebuild that copies every row into a new InnoDB table under the hood, which can take hours on a large table, hold significant locks depending on MySQL version and settings, and requires enough free disk space to hold both the old and new copies of the table simultaneously mid-conversion. Teams that discover a critical MyISAM table during an incident and try to 'just convert it real quick' as part of the fix often turn a data-integrity incident into a second, self-inflicted availability incident because the conversion itself locks or slows the table for far longer than expected.

Imagine renovating a busy retail store's checkout counter without ever closing the store. Instead of shutting the doors, you build an entire new counter in the back room, copy over the existing till and inventory records at your own pace, and meanwhile keep a running log of every single sale still happening at the old counter out front. Only at the very last moment — once the back-room counter has caught up completely — do you do a fast, five-second swap of the signage so customers now walk to the new counter, which by then already reflects every sale that happened while you were building it.

A direct ALTER TABLE in MySQL, for most schema changes, requires rebuilding the table and until relatively recent versions and instant-DDL-eligible changes, holds a metadata lock that blocks reads and writes for the duration — on a multi-billion-row table this can mean hours of full unavailability, which is unacceptable for a production system. gh-ost (and its predecessor pt-online-schema-change) were built specifically to avoid this by never touching the original table's structure directly: they create a new table with the desired schema, copy rows across in small batches to avoid overloading the source, and separately capture every write happening to the original table during the copy so nothing is lost.

gh-ost's key internal difference from pt-online-schema-change is how it captures ongoing changes: pt-osc uses triggers on the original table (extra code that fires on every INSERT/UPDATE/DELETE to also apply the change to the shadow table), which adds write overhead and risk directly on the production table's hot path. gh-ost instead connects to the source as if it were a replica and tails the binary log asynchronously, parsing row-based binlog events and applying them to the shadow table — this means zero triggers and zero added overhead on the actual production write path, since the binlog stream would be produced regardless of whether gh-ost is watching it. Once the shadow table's backfill is complete and its binlog-derived state is caught up to nearly real-time, gh-ost performs the cutover: a brief table rename swap, typically locking for well under a second.

In production, the critical operational parameter is throttling: gh-ost continuously monitors the source's replication lag and load, and deliberately slows or pauses its own batch copying if it detects the source struggling, specifically to avoid the migration itself becoming the cause of a production incident. Engineers monitor the migration's own progress percentage, its current copy throughput, and the replica lag it's inducing, and set explicit throttle flags (max load thresholds on metrics like Threads_running) so the tool self-regulates rather than needing constant manual babysitting during a migration that might run for many hours on a huge table.

The actual failure mode when a migration falls behind production write volume isn't data loss — gh-ost's binlog-tailing approach means it will eventually catch up as long as the binlog retention window is long enough — but if production write volume sustained during the migration exceeds gh-ost's replay throughput indefinitely, the cutover point simply never arrives, and the migration can run for days without completing, all while consuming binlog disk space on the source and leaving the schema change in permanent limbo. The gotcha experienced engineers watch for is exactly this scenario during a traffic spike or a concurrent bulk-import job: a migration that was on track to finish in 3 hours can effectively stall indefinitely if a separate high-volume batch job starts writing to the same table mid-migration, and the correct response is to pause the competing batch job or the migration itself, not to disable gh-ost's throttling to force it through, which would risk exactly the production impact the whole tool exists to avoid.