ぺんじにあの部屋ぺんじにあの部屋
DB

DBにおけるデータ保存フローについて

DBはデータ保存するために様々な工夫がされている。 データ保存の安全性を高めると単純にディスクにデータを保存すればいいわけではないということがわかる。 急に停電が発生したら？急にハードが壊れ、障害が発生したら？ もちろん、これらが起きても完全にデータの安全性は保証できない。しかし、安全性を高めて消えるはずだったデータを救うことはできるし、復旧という手段も講じることができるかもしれない。 RDBは長年使用されてきた技術であるからこそ、様々な工夫がされて現在に至る。データの安全性を高めつつ、処理速度を保つ工夫を表面的だが記載していく。

コマンドはMySQLに準拠するものとする

1.クエリの受付

SQL分をパーサーが解析し、オプティマイザが最適な実行計画を作成する

実行計画の見方

-- 推定プラン（軽い・安全）
EXPLAIN SELECT ...;
 
-- JSONで詳細（コストや選択性などが見やすい）
EXPLAIN FORMAT=JSON SELECT ...;
 
-- 実測つきプラン（実行して計測。8.0.18+）
EXPLAIN ANALYZE SELECT ...;

2.バッファへの書き込み

テーブルやインデックスをページ単位(4kb~16kb)で管理

SHOW VARIABLES LIKE 'innodb_page_size';
 
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| innodb_page_size | 16384 |
+------------------+-------+

バッファプール(メモリキャッシュ)にページを読み込む。
ページのデータを変更し、ダーティページとしてマークを行う。
ダーティページはメモリ上では更新済みだが、まだディスクに反映されておらず、永続化されていないページ

基本統計

SHOW ENGINE INNODB STATUS\G
  • Free buffers: 空きバッファ数
  • Database pages: 実際にデータが載っているページ数
  • Modified db pages: ダーティページ（まだディスクに書き戻していない）
  • Buffer pool hit rate: ヒット率（理想は 99% 以上）

グローバルステータス変数

SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool%';
  • Innodb_buffer_pool_reads
    → ディスクから読み込んだ回数（少ない方が良い）
  • Innodb_buffer_pool_read_requests
    → 論理的な読み取りリクエスト数

ヒット率計算

1 - (Innodb_buffer_pool_reads / Innodb_buffer_pool_read_requests)
  • Innodb_buffer_pool_pages_total / Innodb_buffer_pool_pages_free
    → 全体と空きページ数。空きが少ない場合はバッファプールが足りていない可能性あり。

チューニングの目安

  • ヒット率 < 95%
    → バッファプールサイズ不足。innodb_buffer_pool_size を増やす検討。
  • ダーティページが多すぎる(3で説明)
    → フラッシュ（書き戻し）が追いついてない。
    innodb_io_capacity や innodb_flush_method の調整を検討。
  • Free pages が常にゼロ付近
    → 新しいデータを読むたびに古いデータを追い出している。サイズ不足サイン。

3.WAL(Write Ahead Logging)へ追記

データファイルに直接書き込みに行く前にログファイルに追記する
InnoDB->RedoLog
PostgreSQL->WALファイル
永続化する前にログに残すことで障害時にこのログから復旧を行うことができる
このログはシーケンシャルに書き込みを行うため、低コストで書き込みを行うことができる。
HDDの場合

  • シーケンシャルアクセス: データが連続した領域にあるので、ディスクのヘッドはほとんど動かさずに済む。円盤が回るにつれて順番にデータを読める。
  • ランダムアクセス: データがバラバラの場所にあるため、ヘッドを動かす「シーク」やディスク回転待ちが発生 → 数ミリ秒単位の遅延が大きく積み重なる。

SSDの場合

  • SSDはヘッドや回転がないためシーク時間はほぼゼロに近い。
  • ただし、フラッシュメモリ内部のページやブロック構造のため、連続領域の読み出しは効率よく行えるが、ランダムに飛ぶと制御回路でのアドレス変換やコントローラ負荷が増える → 相対的に遅い。

シーケンシャルの場合は、プリフェッチも可能で、ストレージやネットワークの転送もブロック単位でそのまま次の 処理に使える。

SHOW ENGINE INNODB STATUS\G
  • Log sequence number (LSN): Redo Log の現在の書き込み位置
  • Log flushed up to: ディスクにフラッシュされた位置
    → 差分が大きいと、クラッシュ時のリカバリに時間がかかる。

4.トランザクションのコミット

  • COMMITが来たらWALに記述された内容をfsyncでディスクに確実に保存を行う。 →障害が発生してもデータが失われない状態となる

fsyncはOSのバッファキャッシュにあるデータを強制的にディスクに書き込むシステムコールである。 (永続化が可能となる) fsyncは非常に重たい処理であり、何回も呼び出すとオーバーヘッドが大きくなってしまう。 DBではバッファプールにある程度貯めて、まとめてfsyncでディスクにフラッシュしていたりする。

なぜ、fsyncは重いのか？ writeシステムコールであれば、早い処理が行えるが、実はユーザランドからカーネルへのメモリコピーを行なっているところで、結果を返却し、非同期でディスクへの書き込みを行うため、早い返答が得られる。 しかし、障害等が発生した場合にデータが失われるリスクはあるため、メモリコピー後の非同期部分も同期的に処理を行い、データのファイル書き出しが確定されるまでを返すfsyncを使用する。しっかりディスクへ書き込みを行うところまで保証するため、fsyncは重い処理とされる。
わかりやすい記事

5.データファイルへの反映(バックグランド処理)

  • WALに書いたあとは、実際のテーブルファイルの更新は急がない。
  • バックグラウンドで「チェックポイント」処理が走り、ダーティページをまとめてディスクに書き出す。

チェックポイント→ある時点までの変更をディスクに反映して「この時点から復旧すればOK」と印をつける処理。 これによりWALが肥大化するのを防ぐ。

障害が発生した場合

ここまで来れば、データファイルは古くてもWALが残っているため、起動時にWALを読み込んで整合性を回復することができる。

まとめ

  • 即ディスクに書かない → バッファで高速化
  • まずログに追記 → WALで安全性確保
  • コミット時にfsync → Durability保証
  • 後でまとめて反映 → チェックポイントで効率化

概略フロー

アプリ(SQL) ─▶ パーサ/オプティマイザ ─▶ 実行エンジン
                                   │
                                   ▼
                        バッファプール（メモリ）
                           │    │
        ダーティページ ────┘    ▼
                           WALログに追記（ディスク）
                                   │
                             COMMIT → fsync
                                   │
                     （バックグラウンドで）
                     データファイルに反映（ディスク）

この記事が役に立ったら、Xでシェアしていただけると嬉しいです。質問やフィードバックがあればお気軽にDMください。