PostgreSQLスキルアップノート（自己啓発のための個人サイト）
データベースページ・ページに空きがない場合のUPDATE
【一覧に戻る】
マニュアルへのリンクは/9.2/としています。
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
■■■■ PostgreSQL スキルアップノート
■■■■
■◆■■ データベースページ・ページに空きがない場合のUPDATE
■■■■
■■■■
■■■■ 使用環境：PostgreSQL9.2.3 (CentOS6.2)
2013/03/20
(C) 2013 ohdb
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
ページに空きのない場合のUPDATEでは別のページに行が追記される。
その際のラインポインタの状態や、後続のSQL文が負わされてしまう処理をpageinspectを使って確認した。
【マニュアル】
付録 F. 追加で提供されるモジュール・pageinspect→●[マニュアル]
【マニュアル参考】
−
【その他】
−
■１■ autovacuum停止
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
今回の確認に支障がでないように、autovacuumを止める。
【□】 pg_ctl restart -o '-c autovacuum=off'
■２■ テーブルの作成
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
ページの空きのないFILLFACTOR=100のテーブルを作成する。
テーブルのFILLFACTORデフォルトは100なので（9.2現在）指定は必須ではない。
【□】 テーブル作成し約１００万件のデータを挿入 (1000001件）
┌────────────────────────────┐
DROP TABLE t1;
CREATE TABLE t1 (
c1 int,
c2 text,
c3 text,
c4 text,
c5 text
) WITH (FILLFACTOR=100);
\timing on
INSERT INTO t1 SELECT *,*,*,*,* FROM generate_series(1000000,2000000);
VACUUM ANALYZE t1;
\pset pager off
SELECT * FROM t1 LIMIT 20;
└────────────────────────────┘
■３■ UPDATE実行
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
１．UPDATE実行
──────────────────────────────
【□】 UPDATE t1 SET c4='AAAAAAA' WHERE mod(c1,10)=0;
２．ページの状態出力
──────────────────────────────
以下によりページの状態を出力。 blkno=0の１ブロック分のみを確認。
以下スクリプトの詳細は →●[サイト内]
【□】bashコマンドラインより一度にコピー＆ペーストする
# ==ここから====
DBNAME=database1 # 対象のデータベース名を入力。
TBL=t1 # 対象のテーブル名を入力。
BLKNO=0 # 対象のブロック番号を入力。
FORK=main # main,fsm,vmのいずれか。
(
echo "database= ${DBNAME} table= ${TBL} fork= ${FORK} blk= ${BLKNO} "`date "+%Y-%m-%d %H:%M:%S"`
echo ""
psql "$DBNAME" -P pager=off -c "SELECT * from heap_page_items(get_raw_page('$TBL','$FORK',$BLKNO))";
psql "$DBNAME" -P pager=off -t -A -c "SELECT get_raw_page('$TBL','$FORK',$BLKNO)"\
|awk '{skp=2;print substr($0,1,skp);i=skp+1;print " OFFSET";\
while (lin<=127) {printf("%4d-%4d %-128s\n",lin*64,lin*64+63,substr($0,i,128));lin++;i=i+128}}'\
)\
|tee page_${DBNAME}_${TBL}_${FORK}_${BLKNO}_`date "+%Y%m%d_%H%M%S"`.txt
# ==ここまで====
３．出力結果の確認
──────────────────────────────
実際の出力結果は、■資料１■を参照。→●[サイト内]
前半はheap_page_itemsにより得られたlpの一覧。
後半はget_raw_pageにより得られた16進のダンプ。
確認は主に前半部分の内容が中心。後半の16進のダンプの方は参考程度に記載。
ちなみに前半の「lp_off」の値から16進ダンプの対応する位置を確認できる。
資料１よりわかること。
・このページ内にあるlp（ラインポインタ）は計120個である。
・うちt_max（削除XID）に値のあるものは上記の更新で削除された行のlpである。
lp=1,11,21,31,41,51,61,71,81,91,101,111 の計12個
・ページ内に空きがなかったのでUPDATEによる追記行はすべて別のページ(8333番)のブロックに追記された。
（t_ctid の欄を参照）
(参考) なお、シェル変数に「BLKNO=8333」をセットして前述のスクリプトを実行す
れば、追記された側のブロックも確認することができる。
■４■ UPDATE後にSELECT実行 （後続のSQL文への影響）
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
UPDATE後、vacuumがかかる間もなく後続のSQLがそのブロックアクセスした場合、大きな
負担を負わされる。
１．SELECT実行
──────────────────────────────
【□】 EXPLAIN (ANALYZE,VERBOSE,BUFFERS) SELECT * from t1 WHERE c1=0;
１回目
database1=# EXPLAIN (ANALYZE,VERBOSE,BUFFERS) SELECT * from t1 WHERE c1=0;
QUERY PLAN
--------------------------------------------------------------------------------------------------------------
Seq Scan on public.t1 (cost=0.00..22916.41 rows=1 width=36) (actual time=1320.947..1320.947 rows=0 loops=1)
Output: c1, c2, c3, c4, c5
Filter: (t1.c1 = 0)
Rows Removed by Filter: 1000001
Buffers: shared hit=218 read=8949 written=3509 ★ writtenが発生
Total runtime: 1320.999 ms ★
２回目
database1=# EXPLAIN (ANALYZE,VERBOSE,BUFFERS) SELECT * from t1 WHERE c1=0;
QUERY PLAN
------------------------------------------------------------------------------------------------------------
Seq Scan on public.t1 (cost=0.00..22916.41 rows=1 width=36) (actual time=112.640..112.640 rows=0 loops=1)
Output: c1, c2, c3, c4, c5
Filter: (t1.c1 = 0)
Rows Removed by Filter: 1000001
Buffers: shared hit=4035 read=5132 ★ writtenは発生しない
Total runtime: 112.668 ms ★
以上のように１回目のSELECTの中でwrittenが発生している。
どんな書き込みが発生したのかを以降で確認する。
２．ページの状態出力
──────────────────────────────
【□】 前項の「２．ページの状態出力」と同じ方法でページ状態を出力する。
３．出力結果の確認
──────────────────────────────
実際の出力結果は、■資料２■を参照。→●[サイト内]
資料２よりわかること。
UPDATEの後、同じブロックへアクセスするSELECT文は以下のような書き込みを強いられた。
・lp_flagsを「３」（LP_DEAD）に変更。
・そのラインポインタの内容の大半をクリア。
(lp=1,11,21,31,41,51,61,71,81,91,101,111の計12個のlp)
■５■ まとめ
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
ページ内に空きがない場合はUPDATE時の追記行は別ブロックの中に書き込まれる。
その後、同じブロックへアクセスする後続の別のSQL文（今回はSELECT)によって、
lp_flagsを3(LP_DEAD)へ変更する。
（参考比較）ページ内に空きがある場合
UPDATE時の追記行は同じブロックの中に書き込まれる。
その後の誰か別のSQL文によって、lp_flagsを2(LP_REDIRECT)へ変更する。
→●[サイト内]
ただしVACUUMが先に実行されればUPDATEの後始末を強いられることはない。
以上
