内部構造から学ぶPostgreSQL 設計・運用計画の鉄則 ch6 物理設計 (1/2)
各種ファイルのレイアウトとアクセス
PostgreSQLのテーブルファイルの実態
- 公式/データレイアウト
- ページ
- 8192バイトの固定長領域
- 1GBまで連続して配置される
- 8192 * 131072ページ
-rw------- 1 postgres postgres 8192 Feb 6 14:15 112 -rw------- 1 postgres postgres 8192 Feb 6 14:15 113 -rw------- 1 postgres postgres 81920 Feb 6 14:15 1247 -rw------- 1 postgres postgres 24576 Feb 6 14:15 1247_fsm -rw------- 1 postgres postgres 8192 Feb 6 14:15 1247_vm ...
- 1GBを超えると分割され、
ノード番号.1
,ノード番号.2
,...と管理される - PostgreSQLのビルド時のconfigureオプションで変更可能
- ページサイズ:
--with-blocksize
(kB単位)
# # Block size # AC_MSG_CHECKING([for block size]) PGAC_ARG_REQ(with, blocksize, [BLOCKSIZE], [set table block size in kB [8]], [blocksize=$withval], [blocksize=8]) case ${blocksize} in 1) BLCKSZ=1024;; 2) BLCKSZ=2048;; 4) BLCKSZ=4096;; 8) BLCKSZ=8192;; 16) BLCKSZ=16384;; 32) BLCKSZ=32768;; *) AC_MSG_ERROR([Invalid block size. Allowed values are 1,2,4,8,16,32.]) esac AC_MSG_RESULT([${blocksize}kB])
--with-blocksize=4
でビルドした場合- ページが4096単位
postgresql/data/base/1$ ls -lA
ls -lA total 6072 -rw------- 1 postgres postgres 4096 Feb 6 14:41 112 -rw------- 1 postgres postgres 4096 Feb 6 14:41 113 -rw------- 1 postgres postgres 77824 Feb 6 14:41 1247 -rw------- 1 postgres postgres 12288 Feb 6 14:41 1247_fsm -rw------- 1 postgres postgres 4096 Feb 6 14:41 1247_vm -rw------- 1 postgres postgres 450560 Feb 6 14:41 1249 -rw------- 1 postgres postgres 12288 Feb 6 14:41 1249_fsm -rw------- 1 postgres postgres 4096 Feb 6 14:41 1249_vm ...
- セグメントサイズ:
--with-segsize
(GB単位)
# # Relation segment size # AC_MSG_CHECKING([for segment size]) PGAC_ARG_REQ(with, segsize, [SEGSIZE], [set table segment size in GB [1]], [segsize=$withval], [segsize=1]) # this expression is set up to avoid unnecessary integer overflow # blocksize is already guaranteed to be a factor of 1024 RELSEG_SIZE=`expr '(' 1024 / ${blocksize} ')' '*' ${segsize} '*' 1024` test $? -eq 0 || exit 1 AC_MSG_RESULT([${segsize}GB])
- relation(テーブルやインデックス)は最大32TBまで
- 公式
- パーティショニングで限界突破できる
テーブルファイル
- 公式
- ページファイルのレイアウト
- PageHeaderData
- ItemId
- Free Space
- Item
- Special
PageHeaderData
- ページ先頭24バイト
フィールド | データ型 | バイト長 | 説明 |
---|---|---|---|
pd_lsn | PageXLogRecPtr | 8 | LSN: next byte after last byte of WAL record for last change to this page |
pd_checksum | uint16 | 2 | Page checksum |
pd_flags | uint16 | 2 | Flag bits |
pd_lower | LocationIndex | 2 | Offset to start of free space |
pd_upper | LocationIndex | 2 | Offset to end of free space |
pd_special | LocationIndex | 2 | Offset to start of special space |
pd_pagesize_version | uint16 | 2 | Page size and layout version number information |
pd_prune_xid | TransactionId | 4 | Oldest unpruned XMAX on page, or zero if none |
- このページに対する更新のWALについての情報
- LSN: Log Sequence Number
- このページに対して行われた最後の更新に関する、WALレコード最終バイトの次のバイト位置
- 【所感】「要するに、次はここからWALを書き出すよ」ってことかな
- このページに対して行われた最後の更新に関する、WALレコード最終バイトの次のバイト位置
- LSN: Log Sequence Number
- ページ内の管理情報
- Free Spaceの位置(start,end)
- Special領域の位置
ItemId
- Itemすなわちtupleを指すやつ。32ビット = 4バイト
- tuple開始オフセット (15ビット)
- tuple状態 (2ビット)
- tuple長 (15ビット)
Free Space
- ItemIdの後ろ、およびItemの後ろの使用されていない領域
1 - (Free Space領域の比率)
が充填率- データ挿入時に充填率がテーブルのFILLFACTORを超える場合、充填せずに新しいページを払い出す
- 更新用にFree Spaceをとっておく(HOT更新用のチューニング)
- データ挿入時に充填率がテーブルのFILLFACTORを超える場合、充填せずに新しいページを払い出す
Item
- タプル実体
- ページと同じような構造
- 先頭に固定長ヘッダがある(23バイト)
- nullビットマップ(オプショナル)
- oid(オプショナル)
- ユーザーデータ
HeapTupleHeaderData
- タプルのヘッダ(固定長23バイト)
フィールド | データ型 | バイト長 | 説明 |
---|---|---|---|
t_xmin | TransactionId | 4 | insert XID stamp |
t_xmax | TransactionId | 4 | delete XID stamp |
t_cid | CommandId | 4 | insert and/or delete CID stamp (overlays with t_xvac) |
t_xvac | TransactionId | 4 | XID for VACUUM operation moving a row version |
t_ctid | ItemPointerData | 6 | current TID of this or newer row version |
t_infomask2 | uint16 | 2 | number of attributes, plus various flag bits |
t_infomask | uint16 | 2 | various flag bits |
t_hoff | uint8 | 1 | offset to user data |
xmin
: XID周回問題の解消のために、VACUUMでFrozenTransactionId
(2)を入れるやつ- 新しいバージョンでは入れずにフラグを立てるだけ
Special
- インデックス特有の情報を格納
- テーブルデータのページファイルでは使用されない
- 例: B-Tree構造の両隣ページへのリンクとか
ページ数見積もり
- 行の想定平均サイズ(TS)
- ItemIdや、varchar等の可変長列のヘッダ含む
- 想定レコード数(RN)
FILLFACTOR(FF)
テーブルサイズ概算(バイト)
(RN * TS)
- 1ページあたりのデータサイズ(バイト/page)
- PageHeaderDataの24バイトを除いている
((8192 * FF) - 24)
- ページ数概算
(RN * TS) / ((8192 * FF) - 24)
増加するタイミング
- データ挿入
- データ更新時に新規ページが払い出された
減少するタイミング
- まるごと消す系
- DROP TABLE
- TRUNCATE TABLE
- CLUSTERによる、インデックス順でのタプル再構成
- VACUUM FULL
インデックスファイル
- テーブルファイルとほぼ同じ構成
- Special領域がある
増加するタイミング
- データ挿入
- データ更新時に新規ページが払い出された
減少するタイミング
- まるごと消す系
- DROP INDEX
- TRUNCATE TABLE
- REINDEXによる再構成
テーブルファイルに対するアクセス
シーケンシャルアクセス
- テーブルファイルのページを順々に全部見るやつ
O(n)
インデックスアクセス
- インデックスのリーフに設定されたテーブルファイルへのポインタからテーブルファイルの特定のページを取得する
- 検索が
O(n)
ではスケールしなくなる - 代わりにランダムアクセス頻発
WALファイルとアーカイブファイル
WALファイル
postgres@7edc466e9109:~/data$ ls -lA pg_wal
total 16388 -rw------- 1 postgres postgres 16777216 Feb 6 15:14 000000010000000000000001 drwx------ 2 postgres postgres 4096 Feb 6 14:15 archive_status
の、これ
-rw------- 1 postgres postgres 16777216 Feb 6 15:14 000000010000000000000001
- 16MB
アーカイブファイル
postgres=# SELECT name,setting,context FROM pg_settings WHERE name LIKE '%archive%'; name | setting | context ---------------------------+------------+------------ archive_cleanup_command | | sighup archive_command | (disabled) | sighup archive_mode | off | postmaster archive_timeout | 0 | sighup max_standby_archive_delay | 30000 | sighup (5 rows)
- アーカイブの有効/無効
# - Archiving - #archive_mode = off # enables archiving; off, on, or always # (change requires restart)
on
またはalways
だと、archive_command
のコマンドでバックアップが実施される- 古いものが溜まっていく
- 最新のベースバックアップ以降にしかロールフォワードしないならば、古いアーカイブファイルは不要
- 適宜削除しよう
HOTとFILLFACTOR
HOT
- 公式
- HOT: Heap-Only Tuple
- 冗長なインデックスを排除
- テーブル単位のVACUUMを待つことなく不要領域を再利用可能にする
- ページ単位でVACUUM
- Heap-Only: テーブル領域だけ
- インデックスエントリの追加処理をスキップする
- 線形リストみたいな感じ
- インデックスエントリの追加処理をスキップする
- ながれ
For example: Index points to 1 lp [1] [2] [111111111]->[2222222222]
- インデックスは
lp[1]
を指すインデックス->lp[1]->[11111111]
と検索
- tuple2
[22222222]
はHOT tuplelp[2]
を指すインデックスは無い
- が、tuple2
[22222222]
をインデックスで検索できる- tuple1が
HEAP_HOT_UPDATED
でマークされている HEAP_HOT_UPDATED
でマークされている限り、子孫tupleをtraverseする- 上図では
インデックス->lp[1]->tuple1->tuple2
という流れでtuple2を得る
- tuple1が
Index points to 1 lp [1]->[2] [2222222222]
- tuple1がどのトランザクションからも見えなくなったら
tuple1 [1111111]
領域を回収する- テーブル全体のVACUUMを待つことなく回収
- インデックスサーチで
tuple2
を取得できないといけないので、lp[1]
をlp[2]
へリダイレクトする- ItemIdデータの
lp_flags
で管理(2) インデックス->lp[1]->lp[2]->tuple2
という流れでtuple2を得る
- ItemIdデータの
Index points to 1 lp [1]->[2] [3] [2222222222]->[3333333333]
- 新たにHOT tuple3
[33333333]
が追加されるインデックス->lp[1]->lp[2]->tuple2->tuple3
と到達する
Index points to 1 lp [1]------>[3] [3333333333]
- tuple2がどのトランザクションからも不可視になったら
- tuple2の領域を回収
- lp1をlp3に向ける
インデックス->lp[1]->lp[3]->tuple3
と到達する
- 条件
- インデックスを持たない列への更新
- インデックスを持つ列を更新すると、普通にインデックスエントリが追加される
- 更新対象行と同じページ内に空きがある
- ないと普通に新しいページを払い出す
- ページ内に空きを設けるためのパラメータとして、
FILLFACTOR
がある
- インデックスを持たない列への更新
- HOT更新が働かないケース
- インデックス列をUPDATEで更新する
- DELETE/INSERTで更新
- TOAST対象列の更新は内部的にこれ
- インデックスエントリの追加処理はスキップされない
- 一度に大量の行を更新
- 1ページに収まらない
FILLFACTOR
- ページ内の空き領域をデータ挿入用に利用する割合
- 小さくすると、データ挿入時に使用する領域を減らし、更新用に確保する
CREATE TABLE
,ALTER TABLE
で指定できる
postgres=# CREATE TABLE tbl2 (col int) WITH (fillfactor=70); CREATE TABLE postgres=# \d+ tbl2 Table "public.tbl2" Column | Type | Collation | Nullable | Default | Storage | Stats target | Description --------+---------+-----------+----------+---------+---------+--------------+------------- col | integer | | | | plain | | Access method: heap Options: fillfactor=70
\d
では確認できない- インデックスのものは
\d+
でも確認できない
- インデックスのものは
pg_class
システムビューでも確認できる
postgres=# SELECT relname,reloptions FROM pg_class WHERE relname='tbl2'; relname | reloptions ---------+----------------- tbl2 | {fillfactor=70} (1 row)
- デフォルト値
- テーブル: 100%
- インデックス: 90%
- 値の決定指針
- テーブルに対してINSERT/DELETEしかしないなら100%のままでいい
- UPDATEする場合、ページあたり平均的なレコードの2倍程度の空き領域を確保する
- 新しいページの払い出しが起こる可能性が減少する
- そのページへ同時に更新が発生しない限り、空き領域が交互に使用される可能性が高い