Skip to content

PLANETSCALE 2022-07-28 Tier 3

Read original ↗

PlanetScale — The Slotted Counter Pattern

Summary

Short PlanetScale blog post (originally written July 2022 by Sam Lambert, republished via RSS Feb 2025) canonicalising the slotted counter pattern — a MySQL/InnoDB workaround for row-level lock contention on hot counter rows. Instead of incrementing a single row (UPDATE counters SET count = count + 1 WHERE id = 1) — which serialises all concurrent writers via InnoDB's row lock, causing deadlock risk + long lock-wait times — spread the writes across N slot rows keyed by (record_type, record_id, slot), picking slot = RAND() * 100 on each increment; read the total via SELECT SUM(count) FROM slotted_counters WHERE record_type = ... AND record_id = .... The pattern was used at GitHub — where several of PlanetScale's founders previously worked — to keep per-repository counters (downloads, page views) fast under bursty parallel traffic.

Key takeaways

  1. The problem is row-level lock serialisation under parallel increments, not MySQL counter performance per se. SHOW ENGINE INNODB STATUS\G surfaces LOCK WAIT ... TRX HAS BEEN WAITING N SEC FOR THIS LOCK TO BE GRANTED with lock_mode X locks rec but not gap waiting as the diagnostic signature. (Source: sources/2025-02-04-planetscale-the-slotted-counter-pattern.)
  2. Hot-row contention causes dramatic query-time increases + deadlock risk — "When multiple transactions are trying to update the counter, you are essentially forcing these transactions to run serially, which is bad for concurrency and can cause deadlocks. You can also see dramatic increases in query time when bursts like this occur."
  3. The fix is to replace one row with N rows keyed on a slot column and pick a random slot per write — typical N = 100. Increment query pattern: INSERT ... ON DUPLICATE KEY UPDATE count = count + 1 with slot = RAND() * 100. This spreads the single-row lock across 100 independent row locks; under uniform load, any given write has a 1/100 probability of contending with another concurrent write on the same slot.
  4. Reads aggregate via SUM(count) across the (record_type, record_id) matching slots. The records_and_slots unique key on (record_type, record_id, slot) gives efficient index-range access.
  5. ON DUPLICATE KEY UPDATE is the load-bearing MySQL primitive — it lets the first write to a slot insert a new row and subsequent writes increment it, without needing a separate upfront INSERT phase.
  6. GitHub was the canonical deployment — per-repository download + page-view counters on the github.downloads table, with PlanetScale engineers carrying the pattern forward from their GitHub work.
  7. Roll-up to a single column is a follow-on option — "One way would be to query the slotted_counters table to roll up the data and update a column stored with the rest of the data." Asynchronous consolidation trades read-time work for write-time work.

Systems / concepts / patterns extracted

Schema + query shapes

From the article:

CREATE TABLE `slotted_counters` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `record_type` int(11) NOT NULL,
  `record_id` int(11) NOT NULL,
  `slot` int(11) NOT NULL DEFAULT '0',
  `count` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `records_and_slots` (`record_type`,`record_id`,`slot`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Increment (write path):

INSERT INTO slotted_counters(record_type, record_id, slot, count)
VALUES (123, 456, RAND() * 100, 1)
ON DUPLICATE KEY UPDATE count = count + 1;

Read (aggregate):

SELECT SUM(count) AS count
FROM slotted_counters
WHERE (record_type = 123 AND record_id = 456);

Contention diagnosis snippet

From SHOW ENGINE INNODB STATUS\G:

---TRANSACTION 79853106, ACTIVE 5 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 1128, 1 row lock(s)
MySQL thread id 24, OS thread handle 6281670656, query id 107 localhost root updating
UPDATE slotted_counters SET count = count + 1 WHERE id = 1
------- TRX HAS BEEN WAITING 5 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 2 page no 4 n bits 184 index PRIMARY of table `github`.`downloads` trx id 79853106 lock_mode X locks rec but not gap waiting

Note: the table name github.downloads in the diagnostic output is the canonical artifact confirming the GitHub deployment origin.

Caveats / gaps

  • No production numbers disclosed — no QPS, no contention rate before/after, no end-to-end latency comparison, no deadlock-frequency delta, no throughput multiplier relative to single-row baseline.
  • N = 100 is presented without tuning guidance — no discussion of how to pick N for a given write rate, no mention of the cost of larger N (wider SUM scan on read, more rows written per hot key).
  • Read-cost amplification not quantified — reads now scan up to 100 rows per counter; for frequently-read / rarely-written counters, this can be worse than the single-row design. No break-even analysis provided.
  • Random-slot choice ignores thread-local locality — using thread_id % N or a client-process-sticky hash would also work and avoid RAND() cost; not discussed.
  • MySQL-specific — ON DUPLICATE KEY UPDATE is MySQL syntax. Postgres would use INSERT ... ON CONFLICT DO UPDATE. The pattern generalises but the exact SQL doesn't.
  • Not compared to alternatives — Redis INCR, in-memory aggregation + periodic flush, stream-based event counting, and CRDTs are all plausible alternatives for high-write counter workloads; the article doesn't discuss when any of them would win. (Netflix's Distributed Counter is the architectural successor when single-DB contention is no longer tolerable.)
  • Roll-up design deferred — "There are a few different ways you can implement this pattern, but it comes down to the architecture of your app." The article names roll-up as one option and stops.
  • Doesn't discuss decrement — slotted counters naturally support decrement (count can go negative per slot) but the article uses increment-only examples.
  • Doesn't discuss reset / TTL — no strategy for resetting a counter to zero or aging counts out.
  • Short post — 600 words, code-and-prose; canonical exposition rather than production retrospective.

Source

Last updated · 319 distilled / 1,201 read