Small decisions. Large outages.

Most production database outages do not start with a catastrophic failure.

Production databases almost never fail because of one big mistake. They fail because of dozens of small decisions that looked harmless.

MySQL does not die from high load.
It dies from small decisions made in a hurry.

Over the years I have seen dozens of production incidents that all started the same way:

“Let’s just add a small feature…”
And ended with a dead database.

Here are ten small decisions that quietly kill production databases.

The Search Query That Brought Production Down

It started with a tiny request: “Can we add a simple search field in the admin panel?” It sounded harmless, like something you could safely push before lunch.

The developer wrote:

SELECT *
FROM articles
WHERE title LIKE '%docker%'
ORDER BY created_at DESC
LIMIT 50;

A few days later production started behaving strangely. At certain hours the site became slower and then completely stopped responding.

Monitoring showed:

  • 100% CPU usage

  • constant disk activity

  • rapidly growing number of waiting threads.

The root cause was a single character — the leading % in LIKE.

MySQL could no longer use an index and was forced to read and sort millions of rows, creating temporary tables on disk.

The fix looked like this:

ALTER TABLE articles ADD FULLTEXT(title);

SELECT id, title
FROM articles
WHERE MATCH(title) AGAINST('docker' IN BOOLEAN MODE)
ORDER BY created_at DESC
LIMIT 50;

Production came back to life.

The JOIN That Turned One Row into Ten Thousand

It started with a simple admin request: “The orders page feels a bit slow — can we optimize it?” Nobody expected what came next.

In the project there were tables for orders, customers, tags, status history and delivery logs. One order could have dozens of tags, hundreds of status records and thousands of delivery log entries.

To display orders in the admin panel the developer wrote:

SELECT *
FROM orders o
JOIN customers c ON c.id = o.customer_id
LEFT JOIN order_tags t ON t.order_id = o.id
LEFT JOIN order_status_history s ON s.order_id = o.id
LEFT JOIN delivery_logs l ON l.order_id = o.id;

On the test database everything looked fine. But in production the server started hanging whenever the orders list was opened.

It turned out that each row from orders was being multiplied into 1,000–10,000 rows: first by tags, then by status history, and then by delivery logs. MySQL had to assemble tens of millions of rows into temporary tables and sort them on disk.

At some point the disk could no longer keep up.

The fix:

SELECT
  o.id,
  o.total,
  c.name,
  GROUP_CONCAT(DISTINCT t.tag) AS tags,
  MAX(s.status) AS last_status,
  COUNT(l.id) AS delivery_events
FROM orders o
JOIN customers c ON c.id = o.customer_id
LEFT JOIN order_tags t ON t.order_id = o.id
LEFT JOIN order_status_history s ON s.order_id = o.id
LEFT JOIN delivery_logs l ON l.order_id = o.id
GROUP BY o.id;

The number of processed rows dropped by hundreds of times.

The Trigger That Started a Lock Avalanche

It began with a well‑intentioned performance tweak: the team wanted real‑time per‑record statistics without running heavy COUNT queries.

To maintain per-record statistics, a trigger was added:

AFTER INSERT ON entries
UPDATE entry_stats
SET total = total + 1
WHERE forms_id = NEW.forms_id;

For the first few days everything worked perfectly. But as traffic grew, each new insert started taking longer and longer.

Under load, dozens of concurrent transactions were trying to update the same row in entry_stats. They lined up, held locks, and began blocking each other.

Replication started lagging, binlog size grew rapidly, and users began seeing endless loading states.

The correct approach

Instead of incrementing the counter directly, the trigger only marks the record as needing recalculation:

INSERT INTO entry_stats (forms_id, need_update)
VALUES (NEW.forms_id, 1)
ON DUPLICATE KEY UPDATE need_update = 1;

Recalculation is performed in background batches. For example, every few minutes a worker processes marked records in small chunks:

SELECT id FROM entry_stats WHERE need_update = 1 LIMIT 1000;
UPDATE entry_stats s
JOIN (
  SELECT record_id, COUNT(*) cnt
  FROM entries
  WHERE record_id IN ( .. )
  GROUP BY record_id
) x ON x.record_id = s.record_id
SET s.total = x.cnt,
    s.need_update = 0;

This way each transaction updates at most a thousand rows, completely eliminating the lock avalanche.

The UPDATE Without WHERE — MySQL’s Red Button

It started as a routine cleanup task: “We just need to archive some old call records.” It sounded like pure housekeeping.

The system had a calls table with tens of millions of rows. The task was to temporarily mark old inactive call records as archived.

The developer ran:

UPDATE calls SET status = 0;

At first nothing happened. Then new queries to calls started hanging, connection queues grew, and the site gradually stopped responding.

InnoDB updates each row individually, creating undo records and taking exclusive row locks that are held until the entire transaction commits. Millions of rows mean millions of locks and a massive undo log.

While the UPDATE was running, purge could not clean undo records, the buffer pool was filled with internal pages, and the server started dying from I/O thrashing.

The correct way

UPDATE calls
SET status = 0
WHERE id > :last_id
ORDER BY id
LIMIT 5000;

Each batch is a separate transaction. Purge can keep up, and locks do not accumulate.

The ALTER TABLE That Stopped the World

It was supposed to be a harmless schema tweak: “Let’s add a small flag to mark archived calls.” Nobody expected any downtime.

One weekday the team decided to add a new flag to the calls table to mark records that had already been archived.

The developer connected to production and ran:

ALTER TABLE calls ADD COLUMN is_archived TINYINT(1) DEFAULT 0;

The team expected the operation to take just a few seconds. The table was large, but the column was small — what could go wrong?

Within seconds the site started slowing down, and a minute later almost completely stopped responding. All queries to the calls table were hanging with:

Waiting for table metadata lock

While executing the DDL operation, MySQL took an exclusive metadata lock on the table. Until the ALTER finished, any SELECT, INSERT or UPDATE against calls was forced to wait. Since the table contained tens of millions of rows, the operation took far longer than expected.

Waiting queries piled up, connection pools filled, and the service began failing not because of load, but because of locks.

The correct way

pt-online-schema-change \\\\
  --alter "ADD COLUMN is_archived TINYINT(1) DEFAULT 0)" \\\\
  D=mydb,t=calls --execute

The tool creates a shadow table, gradually copies data, and swaps tables without taking a global lock. Users remain online while the schema changes.

The 1,000 Connections That Ate All the Memory

It began with a tiny code change: “Let’s just open a new DB connection per request — it’s simpler.” No one expected it to become a memory bomb.

This is not a story about heavy queries. It is a story about emptiness — and the word Sleep.

The service had several backend applications. Each HTTP request did:

$db = new PDO(..);

and at the end of the request the connection was simply left open — without pooling, reuse, or limits.

In test everything worked perfectly. In production — too. At first.

One day traffic increased. A few minutes later the DBA opened SHOW PROCESSLIST and saw:

  • more than 1200 connections

  • more than 1000 of them in Sleep

  • Threads\_running constantly above 100

  • MySQL memory usage multiplied

The site started hanging, and then mysqld was killed by the OOM killer.

Each MySQL connection is a separate thread and a set of buffers: thread stack, net buffer, join buffer, sort buffer, read buffer and other internal structures. Even an idle connection consumes memory.

When there are too many connections, MySQL starts spending resources not on queries but on servicing thousands of its own threads: scheduler, context switches, TCP keepalive and internal queues.

This is called a connection storm — and it can kill MySQL without a single heavy query.

The correct approach is connection pooling or ProxySQL, where clients may keep thousands of connections while MySQL keeps only a limited number, for example 100–200.

How not to do it

// every HTTP request
$db = new PDO($dsn, $user, $pass);
$stmt = $db->query("SELECT 1");
// connection is not closed or reused

Each such request creates a new MySQL thread and new memory buffers.

Why the crash happens

SHOW GLOBAL STATUS LIKE 'Threads_connected';
SHOW GLOBAL STATUS LIKE 'Threads_running';

When Threads\_connected goes beyond a thousand, MySQL starts spending most CPU and RAM on thread management instead of queries. At this point the OS may kill mysqld by OOM, and the service goes down.

The One Transaction That Blocked the Entire System

It started with a harmless-looking optimization: “Let’s lock the row first, then call the payment API — just to be safe.”

In the billing system there was a calls table storing call statuses and billing results.

When a customer confirmed a payment, the backend did:

BEGIN;
SELECT * FROM calls WHERE id = 912384 FOR UPDATE;

Then the service called an external payment API and only after receiving the response executed:

UPDATE calls SET status = 'paid' WHERE id = 912384;
COMMIT;

If the external API responded slowly, the row stayed under an exclusive lock for several seconds. All other transactions trying to modify the same row lined up and waited. CPU was mostly idle, but Threads\_running grew and the site started to hang.

Why this is deadly

Each waiting transaction holds a thread, memory and undo log. This creates a cascading accumulation of waiting locks — lock queue amplification.

The correct way

Call the external API first, then use a short transaction:

call_external_api();

BEGIN;
UPDATE calls SET status='paid' WHERE id=912384;
COMMIT;

Legitimate use of transactions

When multiple related tables must be changed atomically:

BEGIN;
UPDATE calls SET status='paid' WHERE id=912384;
INSERT INTO billing_logs(call_id, event) VALUES (912384, 'paid');
COMMIT;

Here the transaction is short, locks are minimal, and atomicity is truly required.

The COUNT(\*) Query That Killed Disk I/O

It started with a UX request: “Can we show how many calls we have processed so far?” It felt like a nice little touch.

On the main page the team wanted to show a simple counter: how many records exist in the system. The developer wrote:

SELECT COUNT(*) FROM calls;

The query was executed on every page load.

At first everything was fine. But as the calls table grew to tens of millions of rows, the server started experiencing constant disk read spikes. During peak traffic the disk was at 100% utilization and other queries had to wait.

Each COUNT(\*) forced MySQL to scan huge ranges of data, evicting useful pages from the buffer pool and creating I/O thrashing.

The correct way

Use an aggregate table:

CREATE TABLE calls_counter (
  shard TINYINT PRIMARY KEY,
  total BIGINT NOT NULL
);
INSERT INTO calls_counter(shard, total)
VALUES (MOD(:call_id,25),1)
ON DUPLICATE KEY UPDATE total = total + 1;

On the main page:

SELECT SUM(total) FROM calls_counter;

Why a sharded counter is used

A naive counter:

UPDATE calls_counter SET total = total + 1;

looks harmless, but all threads update the same row, creating a lock queue and killing InnoDB under load.

With sharding, the table contains multiple shard rows and write load is distributed across them, allowing MySQL to process writes in parallel.

The Indexes That Look Right — and Do Nothing “for show” that do not work

It started with a vague complaint: “The calls list feels a bit sluggish sometimes.” It didn’t sound urgent.

Complaints about slow call lists began to appear. The developer saw that “there is already an index” and added another one just in case:

CREATE INDEX idx_calls_a_b ON calls (status, type);

But the real queries were:

SELECT * FROM calls
WHERE type = 'outgoing'
ORDER BY created_at DESC
LIMIT 50;

Despite the index, MySQL still performed filesort and scanned thousands of rows.

Why the index did not work

Composite indexes follow the left‑prefix rule. The index (status, type) can only be efficiently used when filtering starts with status. Here filtering starts with type, making the index useless.

The correct way

CREATE INDEX idx_calls_filter
ON calls (type, created_at DESC);

When there are too many indexes

Each index is a separate B‑tree, separate buffer pool pages and separate write operations. Too many indexes slow down writes, increase deadlocks and write amplification.

If a table accumulates dozens of indexes, it is almost always an architectural smell. Serious teams prefer read‑optimized tables, materialized views and splitting write‑heavy and read‑heavy workloads instead of adding more indexes.

How Replication Slowly Killed the Master

It began with a well-meaning decision: “Let’s route a few heavy reports to the master — just temporarily.” It sounded like a harmless workaround.

The system used a standard setup: one master and several read replicas. Over time replicas started lagging more often.

When reports and heavy queries could not finish on replicas in time, developers temporarily routed them to the master “so users would not wait”.

At first it helped. But gradually the master began receiving:

— heavy analytical SELECTs — joins over large tables — background recalculations

The master now had to handle both write load and heavy read load. Its buffer pool filled with analytical pages, evicting hot write pages. Commit latency increased, dirty pages grew, and eventually replicas lagged even more.

Soon all reads were routed to the master and it ended up under double load, causing cascading latency growth and full system degradation.

The correct way

— strict read/write separation — super_read_only = 1 on replicas — move analytics and reports to separate storage — monitor Seconds_Behind_Master

Replication should relieve the master, not kill it.

What happens inside

Heavy reads on the master evict hot write pages from the buffer pool. Write latency grows, fsync operations increase, commits slow down.

Slower writes delay binlog generation, replicas fall further behind, and Seconds_Behind_Master grows even more — creating a positive feedback loop known as replication feedback collapse.

Afterword

All these stories share one thing. MySQL rarely dies from a single catastrophic mistake.

Instead, production systems slowly degrade from small compromises: a quick query here, a trigger there, an index that “might help”.

Individually they look harmless. Together they can bring down an entire system.

Good backend engineering is not about writing clever queries.
It is about avoiding the small decisions that quietly destroy production.

If you work on high-load systems, you have probably seen some of these problems in production.