データベースストレージ 基礎

我々の SELECT * FROM users WHERE id = 42 のプランナは 主キーインデックスを使うと決めた。ここからストレージエンジンの仕事は機械的: インデックスを降りて row 42 を保持する heap ページを見つけ、 ページを読み、tuple をデコードし、executor に渡す。それを理解するには、ページと バッファプールを理解する必要がある。

ページがすべての単位

データベースファイルはランダムなバイトストリームではない。 ディスクセクタサイズとファイルシステムブロックサイズの小さな倍数として選ばれた、固定サイズページの配列:

• Postgres:8 KB デフォルト(コンパイル時設定)。
• MySQL InnoDB:16 KB デフォルト(4 / 8 / 16 / 32 / 64 KB 設定可)。
• SQL Server:8 KB 固定。
• Oracle:8 KB デフォルト、2–32 KB 設定可。
• SQLite:4 KB デフォルト(512 B – 64 KB 設定可)。

ページはすべての単位:I/O の単位(1 ページが読み書き、1 行ではなく)、 バッファプールの単位(1 エントリが 1 ページをキャッシュ)、 多くのエンジンでのロックの単位、WAL ログレコードの単位 (「ページ X の 100–116 バイトを … に変更」)。 ページサイズを間違えるとエンジン全体の性能がずれる。

Slotted ページレイアウト

行は可変長だがページは固定。標準解 —— Postgres、MySQL InnoDB、SQL Server、SQLite、 ほぼ全員が使う —— が slotted page:

+--------------------------------------------------------+ offset 0
| ページヘッダ                                           |   24 bytes
|   pd_lsn  (WAL 位置)                                   |
|   pd_checksum                                          |
|   pd_lower (slot 配列の末端)                           |
|   pd_upper (tuple データの起点)                        |
|   pd_special、flags 等                                 |
+--------------------------------------------------------+
| Slot 0  →  offset 7900 (16 バイト)                     |  slot
| Slot 1  →  offset 7820 (32 バイト)                     |  配列
| Slot 2  →  offset 7760 (52 バイト)                     |  下に
| ...                                                    |  伸びる ↓
+----------------+---------------------------------------+ pd_lower
|                                                        |
|        フリースペース  (pd_upper - pd_lower バイト)    |
|                                                        |
+----------------+---------------------------------------+ pd_upper
| ...                                                    |  tuple
| Tuple 2 (52 B): xmin xmax | id=42 | name='alice' |...  |  データ
| Tuple 1 (32 B): xmin xmax | id=41 | name='bob'   |...  |  上に
| Tuple 0 (16 B): xmin xmax | id=40 | ...                |  伸びる ↑
+--------------------------------------------------------+ offset 8191

Slot 配列はヘッダから下に伸びる;tuple データは下から上に伸びる; フリースペースは真ん中。row 42 を見つけるには、エンジンは slot N を読み (slot エントリが 4 バイトなので定数時間)、offset 7900 長さ 16 を取得し、 そこの 16 バイトをデコードする。Insert はフリー領域の上に新しい tuple を詰めて slot を 1 つ追加する。Delete は slot をデッドとマーク —— スペースは後で VACUUM / コンパクションが回収する。

各 tuple には独自の行ヘッダがある:Postgres では xmin(それを挿入したトランザクション)と xmax(それを削除したトランザクション)。 MVCC 可視性チェックはデコード中に行う —— だから row 42 の複数バージョンが 同じページ上に共存できる。

大きな行:TOAST とオーバーフローページ

Tuple は 1 ページに収まる必要がある。5 MB のテキストカラムは? どのエンジンにもオーバーフローストーリーがある:

• Postgres TOAST(The Oversized-Attribute Storage Technique): tuple が ~2 KB を超えたら、大きな属性は圧縮され、~2 KB のチャンクに切られて 隣の pg_toast_* テーブルに格納される。メイン tuple はポインタを保持する。 読み時は透過、TOAST された列の UPDATE は痛い(新しいバージョン全体が書き直される)。
• MySQL InnoDB:閾値を超える可変長カラムはオーバーフローページ (1 つまたは複数)に格納され、インラインに 20 バイトのポインタが残る。

バッファプール

NVMe から 8 KB を読むのは ~100 μs。RAM から 8 KB を読むのは ~50 ns。3 桁。 だからすべてのデータベースはページのメモリ内キャッシュ —— バッファプール—— を保持し、(relation_oid, page_number) でハッシュインデックスする:

Postgres:  shared_buffers       = RAM の 25%(推奨開始点)
           effective_cache_size = RAM の ~50-75%(プランナヒント、割り当てではない)
MySQL:     innodb_buffer_pool_size = 専用サーバで RAM の 70-80%
SQL Svr:   max server memory       = 総 RAM - (~4 GB を OS に)
Oracle:    db_cache_size + sga_target / memory_target

OLTP でのバッファプールヒット率目標:≥ 99%。
95% は高く聞こえるが、20 回に 1 回読みがディスクに行く —— キャッシュパスの 20× レイテンシ。

Postgres は変わった存在:故意に shared_buffers を小さく保ち、 残りは OS ページキャッシュに依存する。Postgres ページは通常ファイルに住み、 OS はとにかくキャッシュするから。MySQL InnoDB は逆 —— O_DIRECT と巨大なバッファプールを使い、 OS ページキャッシュが 2 つ目のコピーにならないようにする。

ページ置換と pin カウント

バッファプールは有限、新しいページが来たら何かを追い出す必要がある:

• Postgres:clock sweep。各バッファに使用カウンタ(0–5)があり; 回転する「時計の針」がカウンタを通過時に減らし、ゼロのバッファを最初に追い出す。 安価、LRU を近似。
• MySQL InnoDB:中点挿入付きの LRU 変種 —— 新しく読まれたページは LRU リストの中点に入る、先頭ではない。 大規模な順次スキャンが頻繁に使用されるページを流し出すことでキャッシュの 「ホット」半分を汚染できない。
• 現在使用中のページは pinned —— クエリがページを読んでいるか バックグラウンドライタがそれをフラッシュしている間、追い出しを防ぐ参照カウント。

ダーティページ、チェックポイント、バックグラウンドライタ

UPDATE がページを変更すると、それはバッファプール内で dirtyとマークされる。すべての commit ですべてのダーティページをディスクに書き戻すと残酷; 代わりに:

1. 変更が WAL にログされる(Section 04 で扱う)。これが commit を耐久にするもの。
2. ダーティページ自体はバッファプールに残り、バックグラウンドライタが 一定レートで遅延書き戻すか、チェックポイントで大きなバッチで書き戻す。
3. チェックポイントはすべての現在ダーティなページをフラッシュし、 チェックポイント前の WAL を回収できるようにする。トリガ:checkpoint_timeout ごと(Postgres デフォルト 5 分)、 または WAL が max_wal_size を満たしたとき。

調整が悪いチェックポイントは、カーネルが数 GB のダーティページを fsync する間、 書き込みを数秒間ストールする —— 古典的な Postgres 本番事故。Postgres のcheckpoint_completion_target(デフォルト 0.9)は書き込みを間隔の 大部分に分散させてスパイクを平滑化する。

構造:幅広く、浅く、完全ソート済み

B+ ツリーは平衡 n 分木:

• 内部ノードはセパレータキーと子ポインタを保持する。 ルーティングをする —— 行データは決して保持しない。
• 葉ノードは行自体(クラスタード、例 MySQL InnoDB 主キー)、 または heap 内の行へのポインタ(非クラスタード、例 Postgres)を保持する。
• 葉はキー順に双方向リンクリストで連結される。 これが B+ ツリーのプラス、そして WHERE id BETWEEN 40 AND 50を安価にする理由。

分岐因子は巨大:16 KB ページからヘッダを引いて(キーサイズ + ポインタサイズ)で割る。 16 バイトセパレータキー + 8 バイト子ポインタ、16 KB ページで、 fan-out は約 ~600。その fan-out で:

fanout = 600

深さ    最大行数
  1       600              (ルート + 600 葉)
  2       360,000
  3       216,000,000
  4       129,600,000,000  (1300 億)

深さ 4 の B+ ツリーが 10 億行のテーブルをカバー。
Postgres は CREATE INDEX VERBOSE / EXPLAIN でインデックス深さをログする。
本番で depth > 5 はほぼ見ない。

下行 —— SELECT id = 42 が実際に何をするか

1. インデックスのルートページを探す(常に既知 —— システムカタログに格納、 バッファプールに pin 済み)。
2. ルートのセパレータキーで 42 を二分探索。ポインタをたどって次レベルの子へ。
3. 各内部レベルで繰り返す。各ステップが 1 ページ検索 —— ほぼ常にキャッシュヒット、~150 ns。
4. 葉に到達。再度二分探索、キー 42 のエントリを見つける。 クラスタードインデックス:行はそこにある。 ヒープ組織化(Postgres):葉は (page, slot) タプル ID を保持する; その heap ページをフェッチし tuple をデコードする。

深さ 4 のツリーですべてキャッシュ:4 ページ読み ≈ 600 ns。 NVMe で heap に 1 つディスクミス:≈ 200 μs。完全に冷たいキャッシュで:≈ 1 ms。 これら 3 つが予測可能であることが B+ ツリーが勝った理由。

範囲スキャン —— 双方向リンク葉

SELECT * FROM users WHERE id BETWEEN 40 AND 50: 一度下行して 40 を見つけ、キーが 50 を超えるまで葉のリンクリストを右に歩く。 行ごとの再下行なし。これが B+ ツリーが範囲クエリでハッシュインデックスに勝つ理由 —— そして B+ ツリーインデックスでの WHERE created_at > ...が数枚の葉の順次スキャンになる理由。

分割とマージ

挿入が満杯の葉を狙うと、エンジンはそれを分割する: 新しいページを割り当て、エントリの半分をそこに移動し、 新しいセパレータキーを親に押し上げる。親も満杯なら、それも分割する —— カスケードは理論的にはルートに到達してツリーを 1 レベル成長させ得る。 対称的に、削除は葉をアンダーフローさせ、兄弟とマージできる (Postgres などの一部エンジンはここで怠惰、VACUUM まで半空のページを残す)。

分割は高価:2 ページを書き直し、3 番目(親)を変更し、すべて 3 つに対して WAL レコードを発行する。ランダム順キーへのバルク挿入ワークロードは 分割の安定した流れを得る;順次挿入は最右の葉に打ち、1 度分割してから進む (はるかに安価)。これが単調増加主キー(タイムスタンプ、BIGSERIAL)がランダム UUID よりも B+ ツリーに優しい理由 —— UUID は挿入をすべての葉に散らし、バッファプールを汚す。

並行性:ロックカップリングと B-link ツリー

複数のクエリが同時にツリーを下行する;1 つが分割中で別のものが読んでいるかもしれない。 素朴なロック —— ルートから葉までのすべてのノードで書き latch を取る —— はすべての変更で全ツリーをシリアル化する。実際のエンジンは:

• ロックカップリング / latch crabbing:親に latch、それから子、 それから親をリリース。リーダは共有 latch でこれをする;ライタは排他 latch で。 並行リードは互いをブロックしない;能動的に分割中のパスのみがロックされる。
• B-link ツリー(Lehman & Yao、1981;Postgres の nbtree が使用): 各内部ノードに追加の right-link ポインタがある。分割中のノードに到達したリーダは ルートから再起動するのではなく right-link をたどって欠落したキーを見つけられる。 これにより分割中に親 latch を保持する必要がなくなる —— 分割は並行性に優しくなる。

プランナが他のものを選ぶとき

B+ ツリーは唯一のインデックスオプションではない:

• ハッシュ:O(1) ルックアップだが範囲なし、順序なし。 Postgres はハッシュインデックスを出荷する;実際 n=4 ですでに B+ ツリーのポイント ルックアップが O(log n) なので、正しい選択であることはまれ。
• GIN(Generalized Inverted Index):配列、JSONB、全文用。 倒置:各トークンを行 ID のリストにマップする。
• GiST / SP-GiST:幾何学的 / 範囲 / カスタムインデックスのフレームワーク (PostGIS、exclusion constraint)。
• BRIN(Block Range INdex):巨大なソート済みテーブル用の小さなインデックス (時系列、append-only)。ブロック範囲ごとに min/max のみを格納する —— B+ ツリーより桁違いに小さく、プランナがブロックをスキップするだけのときに有用。
• LSM:純粋なインデックスではなく、むしろストレージレイアウト —— Section 03 で扱う。

起源:O'Neil 他、1996(『Log-Structured Merge-Tree』)。 Google の Bigtable(2006)、LevelDB(オープンソース 2011)、 そして RocksDB(Facebook の LevelDB フォーク、2012)によって有名になり再形成された。 今日 LSM は RocksDB、Cassandra、ScyllaDB、HBase、InfluxDB、CockroachDB(LSM 書き直し Pebble 上で動作)、 およびほとんどの現代 KV ストアの下のストレージレイアウト。

基本形状

            ┌─────────────────────────┐
write ───►  │  WAL  (順次ファイル)    │   耐久性
            └─────────────────────────┘
            ┌─────────────────────────┐
            │  Memtable (skiplist)    │   最近の書き込み、RAM 内
            │  ~64 MB                 │   キーでソート
            └────────────┬────────────┘
                         │ 満杯 → flush
                         ▼
            ┌─────────────────────────┐
            │  L0:  SSTable SSTable … │   最近の flush(重複あり)
            ├─────────────────────────┤
            │  L1:  SSTable           │   ~10× L0 サイズ、重複なし
            ├─────────────────────────┤
            │  L2:  SSTable           │   ~10× L1
            ├─────────────────────────┤
            │  L3:  SSTable           │   ~10× L2
            ├─────────────────────────┤
            │  ...                    │
            └─────────────────────────┘

書き込みは常に上 → 下に流れる。読みはキーが見つかるまで(または使い果たすまで) 各レイヤをチェックする。すべてのディスク上レイヤは不変 —— SSTable ファイルは一度書かれると決して変更されない。 変更は新しいファイルを書いて最終的にそれらをコンパクトすることでのみ起こる。

書き込みパス —— 純粋に順次

1. WAL にレコードを追記する(順次ディスク書き込み)。
2. RAM 内 memtable(skiplist または red-black tree、どちらも O(log n))に挿入。
3. リターン。書き込みは耐久(WAL)で可視(memtable)。

B+ ツリー下行なし、ページ分割なし、ランダム書き込みなし。 NVMe で group commit 付き、これは書き込みあたり ~10–50 μs —— スループット上限はランダム書き込み速度ではなく、デバイスの順次書き込み速度。 だから LSM はベンチマークで 100K writes/sec/core を超え、 B+ ツリーは同じハードウェアではるかに早くプラトーする。

Memtable flush → SSTable

Memtable がサイズ上限(RocksDB デフォルト 64 MB)に達すると、 凍結されてバックグラウンドスレッドがそれを L0 の不変でソート済みSSTable(Sorted String Table)ファイルとして書き出す。 新しい空の memtable が引き継ぐ。SSTable ファイルフォーマットはおおよそ:

SSTable ファイルレイアウト
┌──────────────────────────────────┐
│ データブロック (各 ~4 KB)        │   キー/値ペア、ソート済み
│   block 0: keys aaa…abc          │
│   block 1: keys abc…ade          │
│   …                              │
├──────────────────────────────────┤
│ インデックスブロック             │   データブロックごとに 1 エントリ
│   (最終キー、ブロックオフセット) │
├──────────────────────────────────┤
│ ブルームフィルタ (~10 bits/key)  │   「キーがおそらく / 絶対にない」
├──────────────────────────────────┤
│ Footer (offsets, magic, version) │
└──────────────────────────────────┘

ブルームフィルタは重要:各 SSTable に 1 つ、偽陽性率が ~1% になるようサイズ調整される。 キー K のポイントルックアップは最初にブルームフィルタを参照する —— いくつかのハッシュ、ディスク I/O なし —— 答えが「このファイルには絶対にない」なら SSTable 全体をスキップする。ブルームフィルタなしでは、すべての読みがすべての SSTable に触れる。

コンパクション —— 請求書が来る

SSTable は蓄積する。クリーンアップなし:すべての読みが数百に触れる; 削除されたキーは決してなくならない(LSM での削除は実際には墓石 —— 「このキーは今なくなった」と言うレコード)。コンパクションは SSTable をマージし、上書きおよび墓石エントリを破棄する バックグラウンドプロセス:

• サイズ階層化コンパクション(Cassandra デフォルト、歴史的): L0 が同様のサイズの N テーブルを持つとき、すべてを 1 つの大きいテーブルにマージ。 単純だが、過渡的バースト時に読みアンプリフィケーションが高くなり得る。
• Leveled コンパクション(LevelDB / RocksDB デフォルト): 各レベルは前の ~10× 大;レベル内(L0 を除く)、キー範囲は重複しない。 L_i を L_(i+1) にチャンクごとにマージする。読みアンプリフィケーションが低く (L0 を超えてレベルごとに ≤1 SSTable)、書き込みアンプリフィケーションが高い。
• Universal コンパクション、FIFO コンパクション: チューンされたバリアント。RocksDB は両方を公開する。

3 種のアンプリフィケーション

LSM 性能は 3 つの数で記述され、それらはトレードオフ:

読みアンプ        = 論理読み 1 回あたり読まれるページ
                    (LSM leveled: ~ L + 1)
                    (B ツリー:    ~ 深さ、≤ 4-5)

書き込みアンプ    = ユーザ書き込み 1 バイトあたりディスクに書かれるバイト
                    (LSM leveled: 典型 10-30×)
                    (LSM tiered:  典型 5-15×)
                    (B ツリー:    典型 1-2×)

空間アンプ        = ディスクサイズ / 論理データサイズ
                    (LSM leveled: ~ 1.1×)
                    (LSM tiered:  ~ 2×)
                    (B ツリー:    ~ 1.3-2×、断片化)

3 つすべてを同時に最小化できない。Leveled コンパクションは書き込みアンプを犠牲にして 空間と読みアンプを最小化する;tiered は空間と読みアンプを犠牲にして書き込みアンプを最小化する; ワークロードで選ぶ。RocksDB のチューナブル(level0_file_num_compaction_trigger、max_bytes_for_level_multiplier、compaction_pri、rate_limiter_bytes_per_sec) はすべて 3 つの数の間を動かすノブ。

読みパス —— そしてなぜそれがストールできるか

1. Memtable をチェック(RAM 内、マイクロ秒)。
2. 各 L0 SSTable をチェック(重複可能)。最初にブルームフィルタ; ポジティブなら SSTable インデックスを読み、次にデータブロック。
3. 各下位レベル:キー範囲がルックアップをカバーする 1 つの SSTable をブルームフィルタチェック; ポジティブならインデックス、次にデータブロックを読む。
4. 最新バージョン(または墓石)を見つけたらリターン。

最悪ケース:各レベルでブルームフィルタ偽陽性 → 読みアンプ = レベル数。 一般的な本番障害モード:コンパクションキューがコンパクタが排出できるより速く成長し、 L0 に数十の SSTable が蓄積する、ポイントルックアップが今 4–5 ではなく 30+ ファイルをチェックする。 レイテンシスパイク —— しかし書き込みスループットはまだ健康に見える。古典的 LSM デバッグストーリー。

どちらを選ぶか

• B+ ツリー:リレーショナル OLTP で混合読み書き、 頻繁なポイント + 範囲クエリ、書き込みアンプ許容度が低い。Postgres、MySQL —— あなたのトランザクションレコードシステム。
• LSM:書き込み重い取り込み(メトリクス、イベント、ログ)、 ブルームフィルタ付きキー値ルックアップ、コンパクション CPU 許容。 埋め込みストレージとしての RocksDB、時系列の Cassandra/Scylla、分析の ClickHouse。
• 両方:ますますハイブリッド設計。Postgres には zheap、 MySQL は LSM カラムストアを実験した、CockroachDB はワークロードが書き込み重い分散だから LSM(Pebble)を選んだ。選択はテーブルごとまたはエンジンごと、言語ごとではない。

1 行のルール

先行書き込みログ(WAL)とは:データページへの変更が耐久ストレージに到達することを許可される前に、 その変更を記述するログレコードがすでに耐久ストレージ上になければならない。 ページ変更は遅く、決して、または順不同にフラッシュ可能 —— WAL が規範的真実。

なぜこのルールで、より単純な「ページを直接書く」ではないのか? なぜならページは大きく(8–16 KB)、ディスク全体でランダムで、 変更は小さなパッチ(ここ 16 バイト、そこ 4 バイト)で来る。 WAL 追記は順次で、多くのトランザクションでバッチされ、変更そのもののサイズ。 WAL 上の 1 つの fsync が数千のパッチをコミットする; ダーティページごとに 1 つの fsync は潰してくる。

コミット、ステップごとに

我々のリクエストの UPDATE 形式(UPDATE users SET email = ... WHERE id = 42)で、COMMIT とレスポンス返却の間に実際に何が起こるか:

1. Executor がバッファプール内で heap ページを見つけて変更する。 ページは今 RAM 内でダーティ。ディスク上は何も変わっていない。
2. 変更を記述する WAL レコードがメモリ内で追記される:ページ X、slot S、バイト 32-79 を ... にセット。 各レコードは変更するページの LSN(Log Sequence Number)を持つ。
3. COMMIT 時、コミットレコードが追記される。WAL writer は メモリ内 WAL バッファをディスクにフラッシュ(write() 経由)し、 WAL ファイルに fsync() を呼ぶ。fsync が戻った時にのみ COMMIT は成功を返す。
4. ダーティデータページはバッファプールに残る。数分後にバックグラウンドライタ、 または次のチェックポイントでフラッシュされる。 WAL ルールにより、これは安全:クラッシュしたら WAL リプレイが変更を再作成する。

WAL での fsync が耐久性のボトルネック。バッテリーバックアップキャッシュ付き NVMe:~50–500 μs。 電源喪失保護なしのコンシューマ NVMe:5–15 ms。回転ディスク:5–50 ms。 遅いディスクでコミットごとに 2 つの fsync(Postgres はチェックポイント時にデータファイルも fsync する)は、 接続あたりの最大コミットレートをおそらく 100 TPS にピン留めする。

クラッシュ回復:3 文字の ARIES

Mohan 他、1992(ARIES paper)はすべての現代リレーショナル DB が 何らかのフレーバを実装する回復アルゴリズムを定義した。WAL で実行される 3 フェーズ:

• 分析。最後のチェックポイントから前方にスキャン。 クラッシュ時にどのトランザクションが飛行中だったか(アクティブトランザクションリスト)、 どのページがダーティだったか(ダーティページテーブル)を決定する。
• Redo。ダーティページテーブルの最小 LSN から前方に、 元のトランザクションがコミットしたかどうかに関係なく、すべての WAL レコードをリプレイする。 これはすべてのページを最後にログされた状態まで持っていく —— データベースは今、クラッシュ時の正確な場所にいる、すべての変更が今実際にディスク上にある以外は。
• Undo。クラッシュ時のすべての飛行中(未コミット)のトランザクションについて、 その WAL レコードを逆方向に歩き、逆操作を適用する。 Undo の後、コミット済みトランザクションのみが可視のまま。

回復時間はデータベースサイズではなく、チェックポイント間の WAL でスケールする。 これがチェックポイントが存在する理由、そして max_wal_size /innodb_log_file_size をチューンする理由 —— それらは回復にどれくらいかかるかを境界する。

グループコミット —— fsync 崖と戦う

すべてのコミットは fsync を必要とする。1000 接続が各々 200 TPS でコミット、 それは 200K fsync/秒 —— 不可能。修正は グループコミット: コミットを少し保持し、次の数マイクロ秒にキューされた他のコミットを同じ fsync に相乗りさせる。 1 つの fsync、多くのコミット。意味は変わらず(各コミットは依然として自分のデータが耐久になるのを待つ)、 スループットは急激にスケールする。

Postgres ノブ:commit_delay(fsync 前に待つマイクロ秒、デフォルト 0)、commit_siblings(遅延が有効になる前の最小他のアクティブトランザクション数、デフォルト 5)。 MySQL InnoDB:フラッシュモードがバッチを許可するときの innodb_flush_log_at_timeout。

実際にチューンできる耐久性ノブ

POSTGRES — synchronous_commit
  on            (デフォルト)各コミットで WAL を fsync。ACID 安全。
                コスト:コミットごとに 1 fsync(NVMe で 5-15 ms)。
  local         ローカル fsync、しかし同期レプリカを待たない。
                レプリカレイテンシが耐久性マージンより重要なときに使用。
  remote_write  レプリカが WAL を書く(fsync なし)のを待つ ——
                プライマリ障害には耐えるが、同時プライマリ + レプリカクラッシュには耐えない。
  remote_apply  レプリカが WAL を redo するのを待つ —— 最強、最遅。
  off           wal_writer_delay(デフォルト 200 ms)までバッチ。
                クラッシュで最大 ~600 ms のコミット済みトランザクションを失う可能性。
                どのデータが失われて OK か正確に理解していない限り、本番では決して使用しない。

MYSQL InnoDB — innodb_flush_log_at_trx_commit
  1   (デフォルト)各コミットでログを write + fsync。ACID 準拠。
  2             各コミットでログを write、秒に 1 回 fsync。
                MySQL クラッシュには耐えるが、~1 秒以内の OS / ハードウェアクラッシュには耐えない。
  0             秒に 1 回 write + fsync。
                クラッシュで最大 ~1 秒のコミット済みトランザクション喪失。

すべてのエンジン — ディスク書き込みキャッシュ
  完了について嘘をつくドライブ書き込みキャッシュ(安価なコンシューマ SSD)は
  耐久性を見えなく壊す。diskchecker.pl / fsync 検証付き fio でテスト、
  または電源喪失保護付きエンタープライズドライブを使用。

レプリケーションと CDC は WAL に乗る

WAL は回復のためだけではない。それはデータベース状態変更の規範的ストリームなので、 以下の自然なフィード:

• 物理レプリケーション:生の WAL レコードをスタンバイにストリーム; スタンバイがバイト単位で適用する。同じメジャーバージョンが必要。 Postgres ストリーミングレプリケーション、MySQL binlog + 位置ベースレプリカ。 速いが、スタンバイは完全クローン —— テーブルごとのフィルタリングなし。
• 論理レプリケーション:WAL レコードを行レベルイベント (INSERT id=42 with values {..})にデコードして送る。 テーブルごと、バージョン柔軟、メジャーバージョン跨ぎ可能。 Postgres 論理レプリケーション、MySQL 行ベース binlog。パブリッシャでわずかに CPU 増。
• CDC / Change Data Capture ツール:Debezium、Maxwell、 Postgres の論理デコーディングプラグイン。WAL を読み、Kafka / Kinesis イベントを発行する。 OLTP データベースの下流のすべての「イベント駆動」アーキテクチャは実際にこれで動く。
• ポイントインタイムリカバリ:ベースバックアップを取得、 以降のすべての WAL セグメントをアーカイブ、 選択した LSN またはタイムスタンプまで WAL をリプレイする。 粒度は 1 つの WAL レコード —— 実質的にトランザクションごと。

WAL は事実上、データベースの変更に対するパブリック API。それを内部化すると、 「WAL は変更のストリームで、テーブルはそのアキュムレータ」 —— LSM と stream-table 双対性の両方がより理にかなう。

コードレビューの赤旗

• 本番での「パフォーマンス」のための synchronous_commit = off /innodb_flush_log_at_trx_commit = 2。ACID 耐久性を ~10% スループットと交換した。 グループコミットは耐久性損失なしでその大部分を取り戻す。 最後の 1 秒の書き込みを失っても本当に気にしないなら(分析取り込み、 ベストエフォートログ)、明示的にそう言う —— トレードを config ファイルに隠さない。
• shared_buffers を総 RAM に設定(または Postgres で 90%+)。OS ページキャッシュ、クエリソートの work_mem、 接続メモリ、または他のプロセスに何も残さない。Postgres の 25% ルールが開始点; MySQL InnoDB の 70–80% は InnoDB が O_DIRECT を使い OS ページキャッシュをスキップするから適用。
• LSM コンパクションキューが無制限に成長。書き込みは問題なく見える;L0 が数十の SSTable で満ちすべてのポイントルックアップが すべてをチェックすると読みは静かに劣化する。num-files-at-level0(RocksDB)を監視、アラートを設定、 コンパクションが崩壊する前に取り込みをスロットルする。
• 低カーディナリティカラムの B+ ツリーインデックス(boolean、deleted_at、4 つの異なる値しか持たない status_id)。 インデックスは「助けている」テーブルスキャンよりも大きくなり、プランナはそれを無視する。 部分インデックス(WHERE status = 'active')を使う、 またはインデックスなしで seq スキャンが勝つようにする。
• 毎秒数千回更新されるホット行。カウンタ行、セッショントークン行、「最終ログイン」タイムスタンプ。 すべての更新が WAL レコードを生成し同じページをダーティにする; WAL がボトルネックになりロック競合があなたを締め上げる。 パーティションごとの行を読み時に合計するカウンタテーブルに移す、 または Redis に移して定期的にフラッシュバックする。