100,000 views a second hit one MySQL row

01The video goes viral

Picture a short-video app. A streamer with a few million followers drops a link, and within about ten seconds 100,000 people press play on the same clip. Under the video sits a little number, the view counter, and every one of those plays runs the same innocent line: UPDATE videos SET views = views + 1 WHERE id = 42. The first time I worked through this scenario I reached for the answer (“put a queue in front”) before I could say why the database falls over. My gut said CPU. It is not the CPU.

This post follows that single row from the first request to the outage, does the arithmetic that makes the outage predictable, and then walks through three fixes. It is the seventh and last post in a series where I re-learn Designing Data-Intensive Applications (Kleppmann) by turning each idea into a scenario I can break on purpose, and it borrows from nearly all the others: the load parameters of post 1, the row locks of post 6, the lost update of post 2, the replicas of post 5. It is also the shortest distance between chapter 1 and chapter 7 (first-edition numbering).

view events at the peak

100k / s

a row lock is held per UPDATE

~1 ms

ceiling for one row, any CPU

~1k / s

writes after batching

1 / s

02Data-intensive, not compute-intensive

So why did my gut say CPU? Because I had skipped the first idea of the book. DDIA opens with a reframing that is easy to nod at and hard to internalize: most applications today are data-intensive, not compute-intensive. Raw CPU power is rarely the thing that limits them. What limits them is the amount of data, its complexity, and how fast it changes. A view counter is the extreme case: the “computation” is adding 1, and a laptop does that a billion times a second. All the difficulty is in where the 1 gets written.

The same chapter describes the toolbox as a set of building blocks: a database to store data, a cache to remember an expensive result, a search index, stream processing to react to events, batch processing to crunch a pile of data later. The useful part is that the boundaries between the blocks are blurring. Redis is a datastore that also works as a message queue; Kafka is a message queue with durability guarantees close to a database. So when someone says “use a queue”, they are really saying “combine two bricks in a way that fits this load”. Hold that thought, because the fix below is exactly that.

03Why one row can take the database down

Look at what the statement needs. To change views from 41,203 to 41,204, InnoDB takes an exclusive lock on row 42, changes it, writes the redo log, and releases the lock at commit. While that lock is held, every other transaction that wants row 42 waits. The statement is atomic and correct (it is exactly the atomic-write fix for the lost update from post 2: no read-modify-write in the app, so no increment gets overwritten). That is the problem: correctness here means “one at a time”. This is the thread that chapter 7 picks up: writes to the same object have to be serialized by a lock (post 6 showed how many rows a lock can quietly cover), and a lock turns concurrency into a queue.

A back-of-the-envelope ceiling

Suppose each transaction holds the row lock for about 1 ms (a round-number assumption: a commit that includes forcing the log to disk; measure your own). Locks on one row cannot overlap, so a single row can finish at most 1 / 0.001 = ~1,000 writes per second. Not “per core”. Per row. Add 64 CPUs and the number does not move, because the 64 cores are all standing in the same line. (Group commit and faster storage move the 1 ms around, but they do not remove the line.)

Now the queue. Little's law says the average number of requests inside a system equals the arrival rate times the average time each one spends there: L = λ · T. Treat the row as a single-server queue with a 1 ms service time (the textbook M/M/1 model, a simplification, since real traffic is burstier and therefore worse). The time in the system is T = 1 / (μ − λ), with μ = 1,000 per second:

Our viral clip is not at 990 per second, it is at 100,000. The queue grows by about 99,000 requests every second, so a pool of 100 connections (the one in Fig. 1) is fully parked in about a millisecond. After that, requests for any table, including the login page that has nothing to do with videos, wait for a connection that never frees up. They time out. The retry logic fires. The retries make it worse.

04Three ways out

The database is not too slow; it is being asked the same question 100,000 times a second. Every fix below does the same thing in a different costume: stop asking the database to record each view individually. Either collect many views into one write, or spread the writes across many rows. A view is a tiny, commutative event (the order of two +1s does not matter), which is why this is allowed at all.

1. A queue, then one batched UPDATE

Put a log such as Kafka between the app and the database. The app appends a tiny event ({video: 42}) and returns immediately, which is fast (an asynchronous send) and never waits on a row lock. A consumer reads events, adds them up per key in memory, and once a second writes one statement: views = views + 100000. A hundred thousand lock acquisitions become one. Because Kafka stores the events durably, a consumer crash loses nothing: it restarts and re-reads.

“Re-reads” is the catch. Kafka consumers are naturally at-least-once: if the consumer writes to MySQL and crashes before committing its offset, the same events arrive again and the counter is bumped twice. The standard defense is to make the write idempotent by storing the consumer's progress in the same transaction as the counter change (one batch covers one partition, and the consumer_progress row is created beforehand):

START TRANSACTION;

-- fence: apply a batch at most once, even if Kafka redelivers it
UPDATE consumer_progress
   SET last_offset = 91204                       -- highest offset in this batch
 WHERE topic = 'view-events' AND part = 3
   AND last_offset < 91204;
-- ROW_COUNT() = 0  ->  already applied: ROLLBACK and move on

-- one statement for every video that got views in this window
INSERT INTO video_stats (video_id, views)
VALUES (42, 100000), (77, 3120), (91, 8)
AS new
ON DUPLICATE KEY UPDATE views = views + new.views;

COMMIT;
-- on startup AND on every partition assignment, seek to last_offset + 1
-- read from this table; this is what keeps batches from overlapping.
-- Kafka's own committed offset is only a hint

The row lock is still there, but it is now taken once a second per video instead of 100,000 times. (AS new needs MySQL 8.0.19 or later; on older versions write VALUES(views).)

2. Redis INCR, flushed on a timer

The lighter version skips the log. Redis keeps its data in RAM and runs commands one at a time on a single thread, so INCR is atomic and a single instance handles on the order of a hundred thousand operations a second (more with pipelining). The app increments a counter in Redis; a flush loop periodically swaps the counter out and adds it to MySQL. The only delicate step is the atomic read-and-reset: if you do GET then DEL, an INCR that lands between them is silently erased. GETDEL (Redis 6.2+) or a Lua script does both in one indivisible step; on older Redis, GETSET key 0 does the same.

import Redis from "ioredis";
import mysql from "mysql2/promise";

const redis = new Redis();
const db = mysql.createPool({ host: "db", user: "app", database: "tube", connectionLimit: 10 });

// 1) request path: two O(1) commands in RAM, MySQL is never touched
export async function recordView(videoId: number) {
  await redis.multi().incr("views:delta:" + videoId).sadd("views:dirty", videoId).exec();
}

// 2) atomic read-and-reset, in one script: pop dirty ids, take each delta and delete it
const DRAIN = [
  "local ids = redis.call('SPOP', KEYS[1], tonumber(ARGV[1]))",
  "local out = {}",
  "for _, id in ipairs(ids) do",
  "  local d = redis.call('GETDEL', 'views:delta:' .. id)",
  "  if d then out[#out + 1] = id; out[#out + 1] = d end",
  "end",
  "return out",
].join("\n");

async function flushOnce() {
  const flat = (await redis.eval(DRAIN, 1, "views:dirty", 500)) as string[];
  const rows: [number, number][] = [];
  for (let i = 0; i < flat.length; i += 2) rows.push([Number(flat[i]), Number(flat[i + 1])]);
  if (rows.length === 0) return;
  try {
    await db.query(
      "INSERT INTO video_stats (video_id, views) VALUES ? AS new ON DUPLICATE KEY UPDATE views = views + new.views",
      [rows],
    );
  } catch (e) {
    // MySQL refused: put the counts back so the next tick retries them
    const m = redis.multi();
    for (const [id, n] of rows) m.incrby("views:delta:" + id, n).sadd("views:dirty", id);
    await m.exec();
    throw e;
  }
}

// 3) one flush per second, never overlapping
export async function flushLoop() {
  for (;;) {
    await flushOnce().catch(console.error);
    await new Promise((r) => setTimeout(r, 1000));
  }
}

3. Sharded counter rows

The third option adds no new component. Instead of one row holding the count, keep N rows, say 16. Each view picks a random slot and increments only that one; the total is the SUM of the slots. Sixteen independent row locks mean the ceiling rises from ~1,000 to roughly 16,000 writes a second, and the number stays exact and transactional because it never leaves MySQL.

CREATE TABLE video_view_slots (
  video_id BIGINT           NOT NULL,
  slot     TINYINT UNSIGNED NOT NULL,           -- 0..15, rows created up front
  n        BIGINT           NOT NULL DEFAULT 0,
  PRIMARY KEY (video_id, slot)
);

-- write: the app picks slot = random integer in [0, 16)
UPDATE video_view_slots SET n = n + 1 WHERE video_id = 42 AND slot = ?;

-- read: N rows to add up
SELECT SUM(n) AS views FROM video_view_slots WHERE video_id = 42;

Adjacent rows share a storage page, but the page is only latched for microseconds, whereas a row lock lasts until commit, so in practice the slots do not queue behind each other. Pick N from the arithmetic above: peak writes divided by ~1,000, with headroom.

05What each fix costs

None of these is free, so here they are side by side with the direct UPDATE as the baseline.

Notice what the first three share: you traded exactness-right-now for throughput. The displayed count is a few seconds old, and with Redis it might lose a few events in a crash. For a view counter nobody notices, and nobody should pay for guarantees the product does not need. The mantra I took from this exercise is the one worth keeping: choosing an architecture is choosing the trade-off that fits the business requirement.

06Where you must never do this

Now read the previous section again with a bank balance in mind. A balance is also “a number that many requests modify”, and the same hot-row problem appears for a very popular account. But every buffering trick above acknowledges the event before it is durable and exact. For a balance, that is lying: a withdrawal must see the true current value (or the account overdraws), and a deposit that the customer was told succeeded must survive a crash. Views are commutative and forgiving; money has invariants.

07Scale up before you scale out

One more instinct this exercise corrected. When the database gasps, “shard it” is the glamorous answer, and I had it ready. The better order, which chapter 1 hints at when it discusses coping with load, is: first understand the load parameters, then try the boring moves. Stateless services scale out easily; stateful ones do not. Adding app servers is trivial because they hold nothing. Splitting data across databases means choosing a partition key, giving up cross-shard transactions and joins, and rebalancing under traffic.

1. Fix the access pattern (this post): batch, buffer or spread the hot key. Costs a day, not a quarter.
2. Scale up: a bigger machine. Crude, and it needs no application change.
3. Read replicas: most traffic is reads, and a replica copies the same data, so nothing is split (it does nothing for a hot write row, and brings the lag and failover trouble of post 5).
4. Sharding: when the write volume or the data size truly exceeds one node.

The book makes the point that the common wisdom is to keep a database on a single node until scaling cost or high-availability requirements force you to distribute it. Two well-known cases show that both ends are legitimate: Stack Overflow has publicly described serving a huge audience from a small number of large machines, while Google has no choice but to scale out. Both are right, because their load parameters are different. The question is never “which is better” but “what is my load, and which parameter hurts first”.

08Back to the turnstile

Choosing an architecture is choosing the trade-off that fits the business requirement.

Duy Anh, notes from re-reading DDIA

Back to the streamer's link and its 100,000 people: the crowd is the same, only the turnstile has changed. Looking across all seven posts, the same two questions keep coming back. The first is Kleppmann's load parameters: requests per second, the read/write ratio, the shape of the hot spot. A system is not “slow” in general, it is slow for a specific load, and this post's load was one key receiving 100,000 writes a second. The second is the trade-off: every speed-up here bought throughput by spending exactness, durability or simplicity, and the only honest way to choose is to know which of those the product can afford to lose.

If I had to compress the whole post into four lines: a hot row is a queue in disguise; one row caps near 1,000 writes a second no matter the hardware; batch, buffer or spread the writes; and never apply any of it to a number that has to be right.

Written by Vũ Duy Anh, backend engineer in Hà Nội. Questions or corrections are welcome by email.