More Related Content
Similar to PostgreSQLのSQL処理の流れとMVCC (第48回 PostgreSQLアンカンファレンス 発表資料)
More from NTT DATA Technology & Innovation
PostgreSQLのSQL処理の流れとMVCC (第48回 PostgreSQLアンカンファレンス 発表資料)
- 1. © 2024 NTTDATA Group Corporation © 2024 NTT DATA Group Corporation 第48回 PostgreSQLアンカンファレンス@オンライン PostgreSQLの SQL処理の流れ と MVCC 2024年8月29日(木) 株式会社NTTデータグループ Innovation技術部 松永 創
- 2. © 2024 NTTDATA Group Corporation 2 自己紹介 名前 所属 業務 経歴 松永 創（まつなが はじめ） 株式会社NTTデータグループ Innovation技術部 PostgreSQLのサポート、コミュニティ開発、等 社会人6年目、今年度からPostgreSQLに注力
- 3. © 2024 NTTDATA Group Corporation 3 はじめに 本資料では、PostgreSQLのSQL処理の一連の流れを把握することを目的に、以下について整理する • SQL処理の流れ • サーバ接続 • 簡易問い合わせの処理の流れ • トランザクションの同時実行制御 • MVCC • トランザクション分離レベル
- 4. © 2024 NTTDATA Group Corporation 4 01 SQL処理の流れ
- 5. © 2024 NTTDATA Group Corporation 5 サーバ接続 PostgreSQLは、1つのクライアントプロセスに1つのバックエンドプロセスを割り当てるクライアント/サーバモデル構成である • postmasterと呼ばれるプロセスが接続要求を監視し、要求を検出すると新しいバックエンドプロセスを生成する • バックエンドとクライアントの間で認証のためのやり取りが１回以上行われる • 認証が完了すると、クライアントプロセスは接続されたバックエンドプロセスに問い合わせを送ることができる PostgreSQL: Documentation: 17: 50.2. How Connections Are Established：https://www.postgresql.org/docs/17/connect-estab.html クライアント PostgreSQLサーバ postmaster クライアント backend backend backend ①接続要求 user, database,… ②バックエンド生成 凡例 ⑤問い合わせ ③認証 プロセス ④認証完了 … クライアント pg_hba.conf の 情報と突き合わせ、 認証方式を選択
- 6. © 2024 NTTDATA Group Corporation 6 簡易問い合わせ（Simple Query） 基本的な流れ PostgreSQLにおけるSQL処理の流れは以下のとおり PostgreSQLサーバ Backendプロセス ①Parser （パース処理） ②Analyze・Rewrite （アナライズ処理・リライト処理） ③Planner(Optimizer) （実行計画の生成） ④Executor （実行） 統計情報 システム カタログ 構文解析ツリー 問い合わせツリー 実行計画 クライアント 問い合わせ テーブル/ インデックス SQL 実行結果 解析ルール PostgreSQL: Documentation: 17: 50.1. The Path of a Query：https://www.postgresql.org/docs/17/query-path.html
- 7. © 2024 NTTDATA Group Corporation 7 簡易問い合わせ（Simple Query） ①Parser：パース処理 クライアントから受け取ったSQL文に文法的な誤りがないか確認し、内部で利用しやすい構造体にマッピングする 構文解析の結果は、構文解析ツリー（RawStmt構造体）としてアナライズに引き渡される SELECT name FROM employee WHERE ・・・ SQL文をトークンに分割 (解析器はflex(フレックス)*1) 文法確認・マッピング (解析器はbison(バイソン)*2) 字句解析 構文解析 SELECT FROM WHERE name employee ・・・ SELECT name ターゲットはname ・・・ コマンド種類は SELECT文 構文解析ツリー Parser 解析ルール （scan.l） 解析ルール （gram.y） トークン要求 トークン提供 文字列の 最後まで 繰り返し *2) Bison - GNU Project - Free Software Foundation https://www.gnu.org/software/bison/ *1) westes/flex: The Fast Lexical Analyzer https://github.com/westes/flex ①Parser ②Analyze・Rewrite ③Planner ④Executor PostgreSQL: Documentation: 17: 50.3. The Parser Stage：https://www.postgresql.org/docs/17/parser-stage.html 例）
- 8. © 2024 NTTDATA Group Corporation 8 簡易問い合わせ（Simple Query） ②Analyze・Rewrite：アナライズ処理・リライト処理 構文解析ツリーの語義解釈 と 問い合わせツリー(Query構造体)の構築 を実施する その後、問い合わせツリーの書き換え を実施してプランナに引き渡す 構文解析ツリー カタログから実際の情報を取得し、 問い合わせツリーを構築 ビュー定義に従って 問い合わせツリーを書き換え Analyze Rewrite FROM salary FROM （SELECT employee… 問い合わせツリー ビューを実際のテーブルに 対する副問い合わせに書き換え 例) salary は employee を 用いたビューとすると・・・ システム カタログ システム カタログ ①Parser ②Analyze・Rewrite ③Planner ④Executor PostgreSQL: Documentation: 17: 50.3. The Parser Stage：https://www.postgresql.org/docs/17/parser-stage.html ※テーブルが存在しない場合は、 このタイミングでエラーとなる • テーブルのOID※ • 列名 (例.SELECT * の場合) • 型のOID など
- 9. © 2024 NTTDATA Group Corporation 9 簡易問い合わせ（Simple Query） ③Planner(Optimizer)：実行計画の生成 Rewriteで書き換えられた問い合わせツリーを入力にして実行計画（PlannedStmt構造体）を生成する 問い合わせツリーを最適化 様々な式の簡略化を実施 例えば・・・ • FROM句のサブクエリを親クエリ にマージ可能か • 冗長な条件の削除 パスの探索・選択を行い、 実行計画を生成 • テーブルのアクセス方式 (SeqScan, IndexScan,…) • テーブルの結合方式 (Nested Loop Join, Sort Merge Join, Hash Join) • 結合順序 ⇒組み合わせ候補を作成し、 実行コストが一番低いものを選択 前処理 プラン作成 Planner 問い合わせツリー 実行計画 統計情報 ①Parser ②Analyze・Rewrite ③Planner ④Executor 例）A &（B＆C） ⇒ A & B & C PostgreSQL: Documentation: 17: 39.1. The Query Tree：https://www.postgresql.org/docs/17/querytree.html
- 10. © 2024 NTTDATA Group Corporation 10 簡易問い合わせ（Simple Query） ④Executor：実行 Plannerで生成された実行計画に従ってテーブルやインデックスの読み込み・書き込みを行う 実行計画 実行計画に従ってスキャンや結合、ソートなどを実施し、WALデータも書き込む 例） Executor 実行結果 ①Parser ②Analyze・Rewrite ③Planner ④Executor 共有バッファ ストレージ postgres=# EXPLAIN SELECT name FROM employee WHERE code = 100; QUERY PLAN ---------------------------------------------------------- Seq Scan on employee (cost=0.00..17.75 rows=3 width=98) Filter: (code = 100) (2 rows) SeqScan PlannedStmt WALバッファ WALファイル
- 11. © 2024 NTTDATA Group Corporation 11 02 トランザクションの同時実行制御 （MVCC）
- 12. © 2024 NTTDATA Group Corporation 12 トランザクション処理の要件 ACID特性 トランザクション処理には、「ACID特性」と呼ばれる4つの要件が存在する • 原子性（Atomicity） • 複数の処理をひとまとまりとし、これらの処理が「すべて実行された」か「すべて実行されていないか」のどちらかになること • 一貫性（Consistency） • トランザクションの実行前後で整合性が保たれた状態であること • 独立性（Isolation） • 実行中のトランザクションが他のトランザクションに影響を与えないこと • 永続性（Durability） • コミットされたトランザクションの結果は永続的に保存されること
- 13. © 2024 NTTDATA Group Corporation 13 トランザクション分離レベル 分離レベル と 並行処理で問題となる現象 SQL標準規格では、トランザクションの分離を4種類のレベルで定義している トランザクションの並列実行で問題となる現象のうち、各分離レベルで発生を許さないものが定められている 分離レベル Dirty Read Nonrepeatable Read Phantom Read Serialization Anomaly Read uncommitted 許容※ 可能性あり 可能性あり 可能性あり Read committed 安全 可能性あり 可能性あり 可能性あり Repeatable read 安全 安全 許容※ 可能性あり Serializable 安全 安全 安全 安全 ※PostgreSQLでは発生しない Dirty Read Tx.A Tx.B 1000 900 UPDATE 別Tx.の未コミットのデータを読み込む 900 SELECT Nonrepeatable Read 再読込みしたデータが1回目と2回目で異なる Tx.A Tx.B 1000 900 UPDATE COMMIT 900 SELECT 1000 SELECT Phantom Read 複数行を取得する問い合わせの再実行で結果が異なる Tx.A Tx.B 1000 INSERT COMMIT SELECT SELECT 2000 1000 2000 1000 2000 3000 1000 2000 3000 3000 PostgreSQL: Documentation: 17: 13.2. Transaction Isolation：https://www.postgresql.org/docs/17/transaction-iso.html デフォルト 今回、 Serialization Anomalyは割愛
- 14. © 2024 NTTDATA Group Corporation 14 トランザクションの同時実行制御 MVCC（Multiversion Concurrency Control）1/2 追記型アーキテクチャのPostgreSQLは、データの一貫性を多版型同時実行制御MVCCを使って管理する MVCCの利点は、トランザクションごとに異なる版を用いるため、読み込みと書き込みが互いをブロックしない • XID（トランザクションID）：各トランザクションに対して昇順に払い出される一意なID • xmin/xmax：タプルを追加/削除する際、実行したトランザクションのXIDを挿入するシステム列 PostgreSQL: Documentation: 17: 13.1. Introduction：https://www.postgresql.org/docs/17/mvcc-intro.html Tx.A（XID=103） BEGIN; UPDATE accounts SET balance=9000 WHERE name =‘豊洲太郎’; UPDATE accounts SET balance=21000 WHERE name =‘月島花子’; COMMIT; xmin xmax name balance 010 103 豊洲太郎 10,000 011 103 月島花子 20,000 012 0 木場新一 0 103 0 豊洲太郎 9,000 103 0 月島花子 21,000 Tx.B (XID104)から見たとき、 異なるTxによって削除(されようと) していることは分かるが、 XID103が実行中orコミット済 なのか判断がつかない ⇒ xmin/xmaxだけでは 可視性を判断できない PostgreSQL: Documentation: 17: 5.6. System Columns：https://www.postgresql.org/docs/17/ddl-system-columns.html accounts SELECT balance FROM accounts WHERE name =‘月島花子’; Tx.B（XID=104）
- 15. © 2024 NTTDATA Group Corporation 15 トランザクションの同時実行制御 MVCC（Multiversion Concurrency Control）2/2 自身のXID, タプルの xmin/xmax に加えて、以下の情報を基に可視性を判断する • CLOG ： 各トランザクションのステータスを管理するデータ、この情報によりコミットされた変更か判断が可能 • スナップショット ： ある時点におけるインスタンス全体のトランザクション状況の断面 トランザクション分離レベルの制御に利用 PostgreSQL: Documentation: 17: 9.27. System Information Functions and Operators：https://www.postgresql.org/docs/17/functions-info.html#FUNCTIONS-INFO-SNAPSHOT xmin xmax name balance 010 103 豊洲太郎 10,000 011 103 月島花子 20,000 012 0 木場新一 0 103 0 豊洲太郎 9,000 103 0 月島花子 21,000 accounts SELECT balance FROM accounts WHERE name =‘月島花子’; CLOG 101 Abort 102 Committed 103 In Progress 104 In Progress xmin = 103（実行中で最小のXID） xmax = 103（完了した最大のXID+1） xip_list =103,104 （実行中のXID） スナップショット（構造体） Tx.B（XID=104） Tx.A（XID=103） BEGIN; UPDATE accounts SET balance=9000 WHERE name =‘豊洲太郎’; UPDATE accounts SET balance=21000 WHERE name =‘月島花子’; COMMIT;
- 16. © 2024 NTTDATA Group Corporation 16 トランザクション分離レベル 分離レベル と 並行処理で問題となる現象 SQL標準規格では、トランザクションの分離を4種類のレベルで定義している トランザクションの並列実行で問題となる現象のうち、各分離レベルで発生を許さないものが定められている 分離レベル Dirty Read Nonrepeatable Read Phantom Read Serialization Anomaly Read uncommitted 許容※ 可能性あり 可能性あり 可能性あり Read committed 安全 可能性あり 可能性あり 可能性あり Repeatable read 安全 安全 許容※ 可能性あり Serializable 安全 安全 安全 安全 ※PostgreSQLでは発生しない Dirty Read Tx.A Tx.B 1000 900 UPDATE 別Tx.の未コミットのデータを読み込む 900 SELECT Nonrepeatable Read 再読込みしたデータが1回目と2回目で異なる Tx.A Tx.B 1000 900 UPDATE COMMIT 900 SELECT 1000 SELECT Phantom Read 複数行を取得する問い合わせの再実行で結果が異なる Tx.A Tx.B 1000 INSERT COMMIT SELECT SELECT 2000 1000 2000 1000 2000 3000 1000 2000 3000 3000 PostgreSQL: Documentation: 17: 13.2. Transaction Isolation：https://www.postgresql.org/docs/17/transaction-iso.html デフォルト 今回、 Serialization Anomalyは割愛 再掲 2つの分離レベルにおける スナップショットの取り方を次で説明
- 17. © 2024 NTTDATA Group Corporation 17 トランザクションの同時実行制御 スナップショットの取得タイミング（Read committed） トランザクションの分離レベルによってスナップショットの取得タイミングは異なる Read committed Tx.A （104） Tx.B （103） 問い合わせの直前までにコミットされたデータのみを参照する（ただし、自身のトランザクション内で更新した結果は未コミットでも参照する） SELECT問い合わせは、その問い合わせが実行を開始した時点のデータベースのスナップショットを参照する xmin = 103 xmax = 103 xip_list =103,104 Snapshot xmin = 104 xmax = 104 xip_list =104 Snapshot SELECT * UPDATE xmin xmax name balance 010 0 豊洲太郎 10,000 011 0 月島花子 20,000 012 0 木場新一 0 010 103 豊洲太郎 10,000 011 103 月島花子 20,000 012 0 木場新一 0 103 0 豊洲太郎 9,000 103 0 月島花子 21,000 COMMIT 豊洲太郎 10,000 月島花子 20,000 木場新一 0 SELECT * 豊洲太郎 9,000 月島花子 21,000 木場新一 0 PostgreSQL: Documentation: 17: 13.2. Transaction Isolation：https://www.postgresql.org/docs/17/transaction-iso.html BEGIN BEGIN
- 18. © 2024 NTTDATA Group Corporation 18 トランザクションの同時実行制御 スナップショットの取得タイミング（Repeatable read） トランザクションの分離レベルによってスナップショットの取得タイミングは異なる Repeatable read Tx.A （104） Tx.B （103） トランザクション開始までにコミットされたデータのみを参照する（ただし、自身のトランザクション内で更新した結果は未コミットでも参照する） トランザクションの最初のトランザクション制御以外の文の開始時点のスナップショットを見る SELECT * UPDATE COMMIT 豊洲太郎 10,000 月島花子 20,000 木場新一 0 PostgreSQL: Documentation: 17: 13.2. Transaction Isolation：https://www.postgresql.org/docs/17/transaction-iso.html SELECT * 豊洲太郎 10,000 月島花子 20,000 木場新一 0 xmin = 103 xmax = 103 xip_list =103,104 Snapshot 010 103 豊洲太郎 10,000 011 103 月島花子 20,000 012 0 木場新一 0 103 0 豊洲太郎 9,000 103 0 月島花子 21,000 BEGIN BEGIN xmin xmax name balance 010 0 豊洲太郎 10,000 011 0 月島花子 20,000 012 0 木場新一 0