サーバーゆる勉強会 ~DBMS の仕組み編~ 株式会社WinTicket 鍛冶 維吹 1

鍛冶 維吹 - kaji, ibuki 9 2022年 株式会社サイバーエージェント新卒入B 9 株式会社 WinTicket 所 9 サーバーチームマネージャー 自己紹介 @kj4555 @kj455

今日のゴール C クエリが実行されるまでの流れがチョットワカ C Spannerのドキュメントを読みたくなる

はじめに 話す/話せること2 DBMS の内部的な仕組みの概9 関連する Spanner の挙動 話さない/話せないこと2 実装の詳" DBMSの網羅的な設計パターン

参考資料 7 Database Design and Implementatio0 7 詳説データベー 7 データ指向アプリケーションデザイ 7 各種Spannerドキュメン8 7 ...

参考資料 Å Å Å Å Å Database Design and Implementatio 詳説データベー3 データ指向アプリケーションデザイF 各種Spannerドキュメン0 ネットの海に落ちているQiita/Zenn これをベースに話します [Database Design and Implementation : Sciore, Edward] (https://www.amazon.co.jp/-/en/Edward-Sciore-ebook/dp/B085DZM79S) *本書固有の命名については 色で記載 オレンジ

01 Disk/File 02 Memory 03 Transaction 04 Record 05 Metadata 06 Query/Parse/Plan 目次

01 Disk/File 02 Memory 03 Transaction 04 Record 05 Metadata 06 Query/Parse/Plan 目次 低レイヤー 高レイヤー

01 Disk/File 02 Memory 03 Transaction 04 Record 05 Metadata 06 Query/Parse/Plan 目次

DBも「ファイルにデータを保存」している 普段は意識しないものの、DBもデータをディスク上に保存していB S ディスクからデータを読み取るとき、バイト単位では読み取らなe S ブロックと呼ばれる一塊のデータでやりとりする（数KB） epoch.tbl ファイル Block1 Block2 Block3

01 Disk/File 02 Memory 03 Transaction 04 Record 05 Metadata 06 Query/Parse/Plan 目次

なぜメモリ管理？ H ディスクアクセスはメモリアクセスに比べて格段に遅8 H HDDで約10万倍、SSDでも100～1000倍 ほ# H 基本的にはオンメモリで処理すればするほど高速にな H しかし、メモリは揮発性

なぜメモリ管理？ H ディスクアクセスはメモリアクセスに比べて格段に遅8 H HDDで約10万倍、SSDでも100～1000倍 ほ# H 基本的にはオンメモリで処理すればするほど高速にな H しかし、メモリは揮発性 大事なのは..™ H いかに賢くメモリを使うˆ H いつメモリからファイルに書き込む（永続化）するか

メモリ管理にまつわるコンポーネント ( ログ管理（ & ( バッファ管理（ ） LogMgr BufferMgr

メモリ管理: LogMgr データベースのリカバリに必要なログを記録すR 0 データベースへの変更ログを記録すR 0 メモリに貯めつつ、必要なタイミングで
 ファイルに書き込む（永続化） 変更ログの例

メモリ管理: BufferMgr バッファを”いい感じ”に管理するコンポーネンG E ブロックを全てオンメモリに持つことはできな E 有限のメモリでブロックアクセスを最小化したい Block1 Block2 Block3 Block4 Buffer1 Buffer2 Buffer1 Buffer2 BufferMgr foo.tbl bar .tbl

メモリ管理: BufferMgr バッファを”いい感じ”に管理するコンポーネン™ — ブロックを全てオンメモリに持つことはできなE — 有限のメモリでブロックアクセスを最小化したい 他ブロックのデータが必要な時は バッファに取り込むブロックを差し替え‰ — FIFO：（First In, First Out“ — LRU：（Least Recently Used“ — クロック Block1 Block2 Block3 Block4 Buffer1 Buffer2 Buffer1 Buffer2 BufferMgr foo.tbl bar .tbl

01 Disk/File 02 Memory 03 Transaction 04 Record 05 Metadata 06 Query/Parse/Plan 目次

モチベーション 「ACID特性」を満たした„  Atomicity（原子性）: 全ての操作が全体として成功するか、全て失敗することを保m  Consistency（一貫性）: データベースの状態が常に整合性を保B  Isolation（独立性）: 他のトランザクションは、現在進行中のトランザクションに影 響を与えな„  Durability（永続性）: トランザクションが確定（コミット）された後は、その変更が 永続的に保持される

モチベーション 「ACID特性」を満たした) Consistency（一貫性）←スキーマベースで正しい処理が行われれば満たされ# … Atomicity（原子性）: 全ての操作が全体として成功するか、全て失敗することを保 … … Isolation（独立性）: 他のトランザクションは、現在進行中のトランザクションに影 響を与えな) … Durability（永続性）: トランザクションが確定（コミット）された後は、その変更が 永続的に保持される

モチベーション 「ACID特性」を満たしたR I Atomicity（原子性）: 全ての操作が全体として成功するか、全て失敗することを保y I I I Durability（永続性）: トランザクションが確定（コミット）された後は、その変更が 永続的に保持される Consistency（一貫性）: データベースの状態が常に整合性を保f Isolation（独立性）: 他のトランザクションは、現在進行中のトランザクションに影 響を与えなR

モチベーション 「ACID特性」を満たしたR I Atomicity（原子性）: 全ての操作が全体として成功するか、全て失敗することを保y I I I Durability（永続性）: トランザクションが確定（コミット）された後は、その変更が 永続的に保持される Consistency（一貫性）: データベースの状態が常に整合性を保f Isolation（独立性）: 他のトランザクションは、現在進行中のトランザクションに影 響を与えなR →「リカバリー」が実現できれば良い

リカバリー（ ） RecoveryMgr トランザクションの途中でプロセスが落ちても復旧できるようにする仕組みが必I 5 ログレコードの書き込H 5 トランザクションのロールバッc 5 システムクラッシュ後のデータベース復元

リカバリー（ ） RecoveryMgr そもそも..S Y トランザクション実行時にはログを記録してお1 Y Star@ Y SetXX# Y Commit / RollbacV Y Checkpoint ログの例 Database Design and Implementation p.11より引用

RecoveryMgr もしトランザクション中にDBプロセスが落ちたら...？ ログから Tx: 3 のトランザクションは コミット/ロールバックされていない →起動時に変更を取り消す処理を実行

RecoveryMgr トランザクション中にDBプロセスが落ちたら...？ ログから Tx: 3 のトランザクションは コミット/ロールバックされていない →起動時に変更を取り消す処理を実行 「そもそもログが永続化される前にプロセスが落ちたら？検知できなくない？」

RecoveryMgr トランザクション中にDBプロセスが落ちたら...？ ログから Tx: 3 のトランザクションは コミット/ロールバックされていない →起動時に変更を取り消す処理を実行 「そもそもログが永続化される前にプロセスが落ちたら？検知できなくない？」 「ログを先行して永続化していたら」問題なし と呼ばれる 　 WAL（Write Ahead Log）

WAL（Write Ahead Log） g Spanner の内部サーバーも WAL を使っていC g PostgreSQL には WAL を無効にすることでパフォーマンスを上げるオプションがあっ たりすC g [spanner-osdi2012.pdf](https://static.googleusercontent.com/media/research.google.com/ja//archive/spanner-osdi2012.pdf‡ g [WALの設定](https://www.postgresql.jp/docs/9.4/wal-configuration.html‡ g なお、耐障害性

モチベーション 「ACID特性」を満たしたA Isolation（独立性）: 他のトランザクションは、現在進行中のトランザクションに影 響を与えなA Š Atomicity（原子性）: 全ての操作が全体として成功するか、全て失敗することを保‘ Š Consistency（一貫性）: データベースの状態が常に整合性を保q Š Š Durability（永続性）: トランザクションが確定（コミット）された後は、その変更が 永続的に保持される

ロック - ConcurrencyMgr ①どの領域に対して、②どのロックが取られているかを管理する ②ロッW gV ReaderShared ロッW eV WriterShared ロッW `V Exclusive ロッW XV WriterSharedTimestamp ロック Cloud Spanner におけるロックのタイプ https://cloud.google.com/blog/ja/products/databases/transaction-locking-in-cloud-spanner ①領‹ † 粒度が細かい方がロックの競合が発生しづら † Spannerは行x列の交点（セル）単位

ロック - ConcurrencyMgr 複数のトランザクションがお互いのロックの解放を待ち合う 「デッドロック」を検知・解消する必要がある T1はb1の排他ロック取得, b2ロック解放待ち T2はb2の排他ロック取得, b1ロック解放待ち

ロック - ConcurrencyMgr 複数のトランザクションがお互いのロックの解放を待ち合う 「デッドロック」を検知・解消する必要がある デッドロック解消アルゴリズムC 0 Wait-Di" 0 タイムリミッƒ 0 Wound-Wait（SpannerはこれU 0 ... Cloud Spanner におけるロックのタイプ https://cloud.google.com/blog/ja/products/databases/transaction-locking-in-cloud-spanner

マルチバージョン同時実行制御（MVCC） d 最新のデータを読もうとするからロックが発生す€ d 古いデータの読み取りではロックが発生しなa d Spanner における Staleness Rea9 d 内部的には複数のバージョンのデータを保持することで実現 データのバージョン, トランザクションの前後関係の判定には タイムスタンプで使われることが多い

分散システムにおけるタイムスタンプの信頼性 q トランザクションの前後関係はタイムスタンプで決めることが多f q Spanner など、分散システムになると、マシンの時刻は信用できなf q 実行マシンによって若干時刻がズレていた場合、前後関係を保証できない 分散 DB ごとに対策は色々H q Google Spanner: True Time APIg q CochroachDB: Hybrid Logical Clock (HLC) q [Spanner: TrueTime と外部整合性](https://cloud.google.com/spanner/docs/true-time-external-consistency?hl=ja{ q [Hybrid Logical Clock (HLC) Timestamps](https://www.cockroachlabs.com/glossary/distributed-db/hybrid-logical-clock-hlc-timestamps/)

トランザクションにまつわる”アノマリー” DBによってはトランザクション中に変なデータが見えたり、見えなかったりする このような一貫性のない挙動のことをアノマリーと呼ぶ “Spanner はすごい”ので、Anomaly は発生しない （Dirty Read, Lost Update, Non-repeatable Read, Phantom Read, Read Skew, Write Skew） È [Cloud Spanner を使って様々な Anomaly に立ち向かう | by Yuki Furuyama | google-cloud-jp | Medium](https://medium.com/google- cloud-jp/cloud-spanner-%E3%82%92%E4%BD%BF%E3%81%A3%E3%81%A6%E6%A7%98%E3%80%85%E3%81%AA-anomaly- %E3%81%AB%E7%AB%8B%E3%81%A1%E5%90%91%E3%81%8B%E3%81%86-5132f691ccf4… È [Cloud Spanner におけるトランザクションの排他制御 | TECH | NRI Digital](https://www.nri-digital.jp/tech/20230926-14591/)

01 Disk/File 02 Memory 03 Transaction 04 Record 05 Metadata 06 Query/Parse/Plan 目次

レコード管理 ファイルに保存されたバイト列からどうやっ6 @ N番目のレコードの場3 @ N番目のレコードのフィールドAの値 を知れば良い...？

レコード管理 ファイルに保存されたバイト列からどうやっ6 @ N番目のレコードの場3 @ N番目のレコードのフィールドAの値 を知れば良い...？ →スキーマを決める スキーマの例 （≒プロトコルを決める） Database Design and Implementation p.166より引用

レコード管理 テーブルのスキーマ（ ）が決まると..D % 各フィールドのオフセットが決まる（ 6 % 1レコードの大きさが決ま2 % N番目のレコードのオフセットが決まる →テーブルのすべてのレコードを走査できる（ , ） Schema Layout RecordPage TableScan *実際にはB-Treeなどの場合が多い Database Design and Implementation p.166,167より引用

可変長のレコード 可変長のフィールドを扱うテクニックは色々ある ex. 「ポインタ」を利用する手法# データの実態は別の場所に保 レコードには保存場所のポインタ（固定長）を保存 →レコード自体は「固定長」になる

フォーマット p レコードは削除されることもあd p レコードが削除されても周りのデータを動かしたくない（大規模な移動になるため@ p 時間が経つと「虫食い」なストレージ状況になりかねない →定期的にデータを配置し直すことで効率的なストレージ状況を実現 未使用スペース Database Design and Implementation p.168より引用

01 Disk/File 02 Memory 03 Transaction 04 Record 05 Metadata 06 Query/Parse/Plan 目次

メタデータの種類 クエリ実行の際に必要な諸々のメタデータを保 テーブ ビュ! インデックÉ 統計データ

メタデータ - テーブル 例えば..' 4 テーブ& 4 テーブル$ 4 レコードの大き 4 フィール 4 名 4 4 大き 4 オフセット テーブル名 テーブル名 Database Design and Implementation p.192より引用

メタデータ - 統計データ 例えば..6 H テーブルのブロック数(B& H テーブルのレコード数(R& H フィールドごとのユニークなデータ種(V) →クエリのコスト算出等に利用 統計データの例 Database Design and Implementation p.197より引用

01 Disk/File 02 Memory 03 Transaction 04 Record 05 Metadata 06 Query/Parse/Plan 目次

クエリ実行までの流れ クエリ文字列 クエリデータ 実行計画 実行 Parse Plan Scan

Parse クエリ文字列を解析（パース）し、扱いやすいデータに変換する Select SName From STUDENT Where GradYear = 2020 and MajorId = 10 QueryData { fields: []{“SName”}, tables: []{“STUDENT”}, pred: Predicate{ terms: []Term{“GradYear = 2020”, “MajorId = “”10”} } } ただの文字列 QueryData構造体 Parse→Plan→Scan

Plan クエリの“実行計画”を立て$ ! 実行計画は複数ありえ$ ! その中で”最適な”実行計画を選# ! データアクセスはせず、推定のみ →メタデータを元に判断 スキャンごとのコスト評価 Parse→ →Scan Plan Database Design and Implementation p.268より引用

Scan 各種演算子ごとに Scan が実装される 例えば..@ （行から列の絞り込み （行の絞り込み （2テーブルの直積結果を返す） これらを組み合わせてクエリを処理する s ProjectScan s SelectScan s ProductScan Select SName From STUDENT Where GradYear = 2020 and MajorId = 10 Parse→Plan→Scan スキャンツリー Database Design and Implementation p.227より引用

Scan Parse→Plan→Scan “foo”, 2020, 10 “foo”, 2019, 10 “foo”, 2019, 10 “foo”, 2020, 10 “foo”, 2020, 10 “foo” “foo”, 2020, 9 “bar”, 2020, 10 “bar”, 2020, 10 “bar” “bar”, 2020, 10 各スキャンを通じてパイプライン的にデータを取得する様子：

07 まとめ 目次

クエリ実行までの流れ クエリ文字列 クエリデータ 実行計画 実行 Parse Plan Scan

クエリ実行までの流れ クエリ文字列 クエリデータ 実行計画 実行 実行 Parse Plan Scan

クエリ実行までの流れ 「クエリ実行」の中で..l h レコードごとに処理（スキーマのおかげでNレコード目の場所がわかるk h ロックを使って並行制d h 変更はログ書き込み→メモリ反映→ファイル反映で永続’ h 高速に処理するために、メモリを賢く活用 参考実装： https://github.com/kj455/simple-db-go （スターくれると嬉しいです）

参考資料 d [Database Design and Implementation: Second Edition](https://www.amazon.co.jp/-/en/Edward-Sciore/dp/3030338355 d [詳説 データベース - O'Reilly Japan](https://www.oreilly.co.jp/books/9784873119540/ d [データ指向アプリケーションデザイン - O'Reilly Japan](https://www.oreilly.co.jp/books/9784873118703/ d [Spanner: Google's Globally-Distributed Database](https://static.googleusercontent.com/media/research.google.com/ja//archive/spanner-osdi2012.pdf d [Cloud Spanner におけるトランザクションのロックについて | Google Cloud 公式ブログ](https://cloud.google.com/blog/ja/products/databases/transaction- locking-in-cloud-spanner d [Spanner: TrueTime と外部整合性 | Google Cloud](https://cloud.google.com/spanner/docs/true-time-external-consistency?hl=ja d [Cloud Spanner を使って様々な Anomaly に立ち向かう | by Yuki Furuyama | google-cloud-jp | Medium](https://medium.com/google-cloud-jp/cloud-spanner- %E3%82%92%E4%BD%BF%E3%81%A3%E3%81%A6%E6%A7%98%E3%80%85%E3%81%AA-anomaly- %E3%81%AB%E7%AB%8B%E3%81%A1%E5%90%91%E3%81%8B%E3%81%86-5132f691ccf4 d [データベース(RDB)を理解できるようになる動画 / UUIDを主キーにすると遅い？](https://www.youtube.com/watch?v=ifnszOLLgjs&t=627s)