勉強日記

チラ裏

内部構造から学ぶPostgreSQL 設計・運用計画の鉄則 ch4 処理/制御の基本

OSS-DB PostgreSQL 勉強メモ

gihyo.jp

サーバプロセスの役割

マスタサーバプロセス

  • いろいろfork()する人
postgres@b612ca6ed5fc:/$ ps -ef | grep postgres

postgres     1     0  0 Jan29 ?        00:00:01 postgres
postgres    57     1  0 Jan29 ?        00:00:00 postgres: checkpointer   
postgres    58     1  0 Jan29 ?        00:00:01 postgres: background writer   
postgres    59     1  0 Jan29 ?        00:00:01 postgres: walwriter   
postgres    61     1  0 Jan29 ?        00:00:01 postgres: stats collector   
postgres    62     1  0 Jan29 ?        00:00:00 postgres: logical replication launcher   
postgres   370   356  0 Jan29 pts/0    00:00:00 /usr/lib/postgresql/12/bin/psql
postgres   378     1  0 Jan29 ?        00:00:00 postgres: postgres postgres [local] idle
postgres  1224     1  0 Jan29 ?        00:00:00 postgres: autovacuum launcher
  • pid 1 postgresがマスタサーバプロセス
  • 他のは子

ライタプロセス

WALライタプロセス

チェックポインタ

自動バキュームランチャと自動バキュームワーカ

統計情報コレクタ

  • autovacuumは統計情報に依存している
  • ので、track_countsがoffならautovacuumもoffにしないといけない
  • autovacuumをonにするときはtack_countsもonにする必要がある

postgresql.conf

#autovacuum = on         # Enable autovacuum subprocess?  'on'
                    # requires track_counts to also be on.
  • 設定変更タイミングとか
postgres=# SELECT name,setting,context FROM pg_settings WHERE name=ANY(array['track_counts', 'autovacuum']);
     name     | setting |  context  
--------------+---------+-----------
 autovacuum   | on      | sighup
 track_counts | on      | superuser
(2 rows)
  • いずれもサーバ起動中に設定できる
  • track_countsはスーパユーザのSETでoffにできる
  • が、結局autovacuumもALTER SYSTEMか何かでoffにしてreloadする必要がある

バックエンドプロセス

Column: バックグラウンドワーカプロセス

  • 公式
  • ユーザ独自のワーカプロセスをPostgreSQLに組み込めるやつ
    • PostgreSQLサーバと同じライフタイム
    • 監視とかに

クライアントとサーバの接続/通信

問い合わせの実行

パーサ

  • 字句解析
  • 構文解析
    • セマンティクス
    • ここまででエラーが出たら「syntax error」
postgres=# SELECT *;
ERROR:  SELECT * with no tables specified is not valid
LINE 1: SELECT *;
               ^
  • 実在するかの確認
    • テーブル名
    • 列名
    • etc.
postgres=# SELECT * FROM tbl;
ERROR:  relation "tbl" does not exist
LINE 1: SELECT * FROM tbl;
                      ^

リライタ

  • 公式
  • viewに対してupdateをかけようとしたらtableへのupdateに読み替える的なやつ

プランナ/オプティマイザ

個々のテーブルに対するアクセス方法の選択

  • まずSeqScanが候補
  • 適宜IndexScanとかBitmapScanとかも候補に

結合方法の選択

  • 公式
  • 入れ子ループ
    • 実装は簡単
    • 実行時間がかさむ
      • ただし、右側のrelationがインデックススキャンだと速い
  • merge join
    • join前に左右のrelationがソート済ならば、走査が1回ずつだけで済むやつ
      • 【補】merge sortみたいなかんじ
    • 明示的にsortするか、ソート済の(B-treeとか)indexを使うか
  • hash join
    • 右のrelationに対してハッシュテーブルを作って、左のrelationでルックアップするやつ

GEQO

  • 公式/GEQO
  • GEQO: Genetic Query Optimization
  • クエリ最適化は巡回セールスマン問題
    • 複数のrelationをjoinするような場合、どの順番で・どの戦略でjoinをするか
  • relation数がgeqo_threshold以下ならほぼ全て網羅して最適な実行計画を探しに行く
  • 超えたらGEQOに切り替わる
postgres=# SELECT name,setting,context FROM pg_settings WHERE name SIMILAR TO 'geqo%';
        name         | setting | context 
---------------------+---------+---------
 geqo                | on      | user
 geqo_effort         | 5       | user
 geqo_generations    | 0       | user
 geqo_pool_size      | 0       | user
 geqo_seed           | 0       | user
 geqo_selection_bias | 2       | user
 geqo_threshold      | 12      | user
(7 rows)

エグゼキュータ

SQLの種別による動作

  • DDL,DCLは
    • プランナ素通り
      • 実行計画も何もない
    • エグゼキュータ素通り
      • 代わりに、対応する個々のコマンドを実行

トランザクション

トランザクションの特性

  • ACID
  • いつもの

トランザクションの制御

  • DCL

トランザクションの分離レベル

  • 公式
  • いつもの
  • PostgreSQL特有の要素
    • READ UNCOMITTEDはREAD COMMITTED扱い
      • ダーティリード発生しない
    • REPEATABLE READ
      • V9.0以前はSERIALIZABLE扱いだったらしい
  • 高ければいいというわけではない
    • 分離レベルが高いと、直列化に失敗する可能性が出てくる
    • 失敗したトランザクションの再実行などを別途考える必要が出てくる
postgres=# show transaction_isolation;
 transaction_isolation 
-----------------------
 serializable
(1 row)


postgres=# select * from tbl;
 col 
-----
   1
(1 row)

...
(他トランザクションによる更新・commit)
...


postgres=# update tbl set col=col+1;
ERROR:  could not serialize access due to concurrent update

ロック

Column: 勧告的ロック

  • advisory lock
  • 公式
  • アプリケーション固有のロックを定義できる
  • ビジネストランザクション(long transaction)のための悲観ロック等を実装するときに使える
    • フラグカラムとか使うより良い
      • 高速
      • テーブル太らない
      • DBセッション終了時に自動的にクリーンアップされる

同時実行制御

  • 公式
  • MVCC: Multi Version Concurrency Control を追記型アーキテクチャで実現
    • ロックとは異なる路線の同時実行制御
    • ロックと比較してパフォーマンスにすぐれる
      • read/writeが互いにブロックしない