データ層

状態がスケーリングの難しい部分である理由、データベース、キャッシュ、リードレプリカ、クォーラムのストアが速さと新しさをどう引き換えるか。

State is the hard part of scaling. App Servers are easy to copy because they hold no data, so ten copies are as correct as one. Data is different, because every copy must agree with the others or users see the wrong answer. That is why the data tier becomes the long-term challenge of every Company.

The Database is the source of truth#

The Database answers read queries, write queries, and transactions. Until you link a better Component, it also does three other jobs badly: full-text search, uploaded files stored as rows, and report scans. Each costs far more CPU than a normal read. A Search Engine, Object Storage, and a Data Warehouse take those jobs away.

Four limits cap a Database:

  • CPU. Every query costs CPU, and a cold Database, just restarted, needs more of it per query.

  • Connections. Postgres runs one process per connection and accepts a fixed number. MySQL gives each connection a thread and holds several times as many. Read Errors and retries for why more App Servers make this worse.

  • The volume's IOPS. Every write and every read that misses the buffer cache waits on the disk. A general purpose volume answers 3,000 of those a second, a provisioned one 64,000.

  • The buffer cache. Report scans fill memory with cold data. Other reads then go to disk, and writes wait behind the scans. This is why analytics and transactions should not share a Database. See OLTP and OLAP.

A bigger box lifts the first two limits. Sharding lifts the CPU limit on writes, because each shard takes its share of them, but each shard bills like a Database of its own.

Two engines, two shapes of work#

Postgres and MySQL are the same job with different costs. Postgres holds 100 connections at about 8 MB each and answers reads, scans, and full-text search about 5–10% cheaper than MySQL. MySQL holds 500 connections at about 1 MB each and answers writes and checkouts about 30% cheaper, because InnoDB updates the row in place. It also changes schema online, while many Postgres ALTERs take the table lock, and only Postgres keeps dead rows for VACUUM, so only Postgres meets Table bloat. A read-heavy Company wants Postgres; a write-heavy one wants MySQL.

Replicas always run their primary's engine, and an engine change lands on every Database at once. Replication only copies between matching engines.

More than one Database: group them#

A second Database is not a follower. Box the Databases that hold one dataset together: the box makes one Data Group, and its topology says how those Databases share the group's rows. Single primary (the start) takes writes on one Database and reads on Read Replicas. Sharded gives each Database its partition of the group's rows, so writes scale but cross-shard transactions cost up to 3× and can fail on either shard. Multi-leader copies every row to every Database and lets each take writes, so local writes are fast but certification drops conflicting writes (first commit wins) and every write costs 1.25× the CPU to replicate. Switching a group's topology moves its rows: 24 Dev Hours and a minute a GB, like a resharding. A group with one Database has nowhere to move them, so it takes the pick at once and the copy starts with the second Database in the box.

Rows follow the Services. Each Service owns an equal share of the Company's data, so a group holds the shares of the Services whose datastores sit in it. Two groups never touch each other's rows, and neither takes the shared-database penalty for the other, which is what lets one Board hold two independent products. A Database outside a box holds its own share and stands alone.

Quorum stores (Wide-column Store) and consensus (Distributed SQL) are the other answers to "writes on more than one node": a quorum waits for R of N on a read and W of N on a write, while consensus waits for a majority on every write. See Topologies.

Slow queries hide until you grow#

Two code problems cost little at first and a lot later.

An unindexed query scans the table. Its cost grows with the number of rows, and the rows grow with your users, so it is invisible in the Garage and becomes the bottleneck later. The Slow query incident is this problem. Query analysis shows it, and the Add index task fixes it.

An N+1 query comes from an ORM that sends one query per item in a list. Laravel, Rails, and Django make extra reads on page views and API reads until you ship Eager loading. See N+1 query.

The Cache: memory in front of the Database#

A Cache answers read queries from memory and sends misses on to the Database. Its hit ratio decides how much load it takes off:

hit ratio=memoryhot data×TTL factor×write penalty×warmth\text{hit ratio} = \sqrt{\frac{\text{memory}}{\text{hot data}}} \times \text{TTL factor} \times \text{write penalty} \times \text{warmth}

The square root models skewed access. Most reads ask for a few hot keys, so a small Cache already holds the hottest ones, and the first gigabyte is worth more than the tenth. Hot data grows with your users, so a Cache that was big enough last Era may be too small in the next.

Writes invalidate keys, so a write-heavy mix lowers the hit ratio. A longer TTL keeps more hits but serves more stale data. A Cache that restarts comes back cold, and every miss goes to the Database at once, which is a cache stampede. Stampede protection and Cache warming soften it.

Read Replicas: more reads, older data#

A Read Replica runs like a Database but answers only reads and reports. Writes go to the primary and then stream to the replica, so the replica is always a little behind, and the lag grows with your write rate. See replication lag.

Three Nodes take load off a busy Database, and each has its own job:

  • A bigger Database is the only fix for writes. It costs the most, and a resize restarts it.

  • A Cache is the cheapest fix for reads, while the same hot rows are read again and again. It cannot answer a report, and its hit ratio falls as writes rise.

  • A Read Replica answers a read of any row and takes the reports, at 0.7× the box price of a Database, and placing one restarts nothing. Some of its reads come back stale.

A read from a lagging replica can return old data. Whether that matters depends on the kind:

  • A page view accepts slightly old data.

  • An API read that comes back stale fails as stale.

  • A checkout that reads its own write fails as stale, which costs real money.

Read-your-writes routing sends the reads that care to the primary. That fixes most stale errors, at the price of some read load on the primary.

サンドボックス古いデータのチェックアウトApdexを0.9以上に90秒間保ってください。

Quorums: the game's namesake#

A Wide-column Store keeps each key on N replicas. You choose two numbers: R, the replicas a read asks, and W, the replicas a write waits for.

R+W>Nevery read asks at least one replica that has the latest writeR+W≤Na read can miss the latest write: eventual consistency\begin{aligned} R + W &> N && \qquad \text{every read asks at least one replica that has the latest write} \\ R + W &\leq N && \qquad \text{a read can miss the latest write: eventual consistency} \end{aligned}

Smaller R and W are faster, because each operation waits for an earlier reply, and they cost fewer replica operations. The price is that a weak quorum serves stale reads. A quorum that needs every replica fails while one replica is down. See quorum.

理解度チェック

A Wide-column Store keeps N = 3 replicas. Which setting makes every read see the latest write?

Overlap does not give a single, ordered history. Concurrent writes can still conflict, so even a strong quorum gets an occasional stale read or duplicate on checkouts. A Distributed SQL Node fixes that with consensus: every write waits for a majority of its replicas. Inside one region, that is cheap, but across regions every write pays a long round trip. See Raft.

Hot keys#

Spreading data across nodes only helps if the load spreads too. In the Hot key incident, a large share of reads asks for one key, such as a celebrity's post. That key lives on one shard, so that shard saturates while the others idle. Hybrid fan-out lets you split hot keys across shards. See hot key.

The pattern behind all of it#

Every data choice in Quorum trades among speed, freshness, and survival. A Cache and a replica buy speed with freshness, and a strong quorum and consensus buy freshness with speed. Copies in other zones and regions buy survival with Burn. The Data track shows the research behind each tool:

  1. キャッシュ
  2. コネクションプーリング
  3. リードレプリカ
  4. クエリ分析
  5. 全文検索
  6. オブジェクトストレージ
  7. キャッシュ戦略
  8. ストレージライフサイクルルール
  9. 保持ポリシー
  10. スタンピード対策
  11. キャッシュウォーミング
  12. read-your-writesルーティング
  13. データウェアハウス
  14. イベントストリーミング
  15. エンベディング
  16. ポイントインタイムリカバリ
  17. 同期レプリケーション
  18. シャーディング
  19. ドキュメントデータベース
  20. ハイブリッドファンアウト
  21. リーダーレスレプリケーション
  22. 合意形成データベース
  23. 変更データキャプチャ
  24. オンラインスキーママイグレーション
  25. トランザクショナルアウトボックス
  26. Expand/Contract

For practical steps, see Scale the database and Fix slow queries.