Is there any performance impact from PostgreSQL data file extend?
データファイルの拡張によるパフォーマンス低下はあるのだろうかPostgreSQL Unconferecnce #39 (2023-02-20)トーク 10 分議論 10 分くらいです
View Slide
自己紹介● ぬこ@横浜 , @nuko_yokohama● にゃーん● 趣味でポスグレをやってる者だ● 体重&お腹が EXTEND して困っています
PostgreSQL のデータファイル拡張
PostgreSQL のデータファイル● PostgreSQL のテーブルやインデックスはファイル化されている– 8KB 単位のブロックが連続しているファイル– ファイルサイズは可変( 0 バイト~ 1GB )● 事前の領域確保はされない。ブロック (8KB)ブロック (8KB)ブロック (8KB)ブロック (8KB)・・・ブロック (8KB)ブロック (8KB)ブロック (8KB)ブロック (8KB)・・・ブロック (8KB)ブロック (8KB)ブロック (8KB)・・・・・・ブロック (8KB)最大 1GB最終ブロックは1GB 以下セグメントファイル セグメントファイルセグメントファイル
PostgreSQL のデータファイルの拡張 / 縮小● 拡張– データ挿入( INSERT/COPY FROM )時– データ更新( UPDATE )時● FILLFACTOR が 100 だと拡張しやすい● HOT 更新でない場合● 縮小– TRUNCATE (切り詰め)– VACUUM FULL/CLUSTER (再編成)– 全件 DELETE 後の VACUUM
PostgreSQL のデータファイルの拡張● データファイルはブロック( 8KB )単位で拡張する● 1 ブロックのサイズは極端に大きくはないものの、拡張のコストはタダではないのではないか。– ページサイズ分のディスクへの書き込み– 拡張時のロック (wait_event の extend? )● 拡張しない状態だと挿入等は早くなるの?
拡張なし 状態の作り方● テーブルに最後に挿入されたレコード「以外」を DELETE● VACUUM– VACUUM FULL ではない!● テーブルに最後に挿入されたレコードを DELETE● 環境: Linux/PostgreSQL 15.2
拡張なし 状態の作り方(無理やり)ブロック (8KB)ブロック (8KB)ブロック (8KB)ブロック (8KB)・・・ブロック (8KB)無効領域無効領域1 レコードのみ残他は無効領域・・・無効領域空き領域空き領域1 レコードのみ残他は空き領域・・・無効領域 空き領域空き領域空き領域1 レコード無効他は空き領域・・・空き領域 空き領域初期状態最終ブロックの 1 件以外を DELETEVACUUM VACUUM
実験 1 :バルクコピー
実験 1 :バルクコピー● pgbench_hisotry を以下の条件で定義– UNLOGGED TABLE– FILFACTOR=100● 検証パターン– 拡張あり: TRUNCATE 後に COPY– 拡張なし:スライド 7 の状態から COPY● 400,000 件のデータを COPY FROM で登録
実験 1 :バルクコピー● 拡張なしの場合、約 5% 程度 COPY 時間が短縮拡張あり拡張なし0 100 200 300 400 500460.28440.44pgbench_history COPY(40 万件 ) 処理時間(3 回測定の平均 )処理時間( ms )効果はいまひとつだ…
実験 2 :複数クライアントからの挿入
実験 2 :複数クライアントからの挿入● pgbench_hisotry を以下の条件で定義– UNLOGGED TABLE– FILFACTOR=100● 検証パターン– 拡張あり: TRUNCATE 後に pgbench 実行– 拡張なし:スライド 7 の状態から pgbench 実行● 今回は 4 ~ 32 クライアント各 10 万~ 1.25 万回 INSERT
実験 2 :複数クライアントからの挿入● 検証に使ったスクリプト (ins-only.txt)\set aid random(1, 100000 * :scale)\set bid random(1, 1 * :scale)\set tid random(1, 10 * :scale)\set delta random(-5000, 5000)BEGIN;INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES (:tid, :bid, :aid, :delta,CURRENT_TIMESTAMP);END;● pgbench のオプション– DB 名、ユーザ名、ポート番号は省略pgbench -c 4 -t 100000 -n -r -f ins-only.txt
実験 2 :複数クライアントからの挿入拡張あり拡張なし0.0 0.1 0.2 0.3 0.4 0.50.090.100.130.140.160.17pgbench_history 4Cli INSERT(10 万件 ) 処理時間(3 回測定の平均 )BEGININSERTCOMMIT平均レイテンシ( ms )拡張あり拡張なし0.0 0.1 0.2 0.3 0.4 0.5 0.60.140.140.170.170.260.25pgbench_history 8Cli INSERT(5 万件 ) 処理時間(3 回測定の平均 )BEGININSERTCOMMIT平均レイテンシ( ms )拡張あり拡張なし0.0 0.1 0.2 0.3 0.4 0.5 0.6 0.7 0.8 0.9 1.0 1.1 1.20.300.300.30.30.540.54pgbench_history 16Cli INSERT(2.5 万件 ) 処理時間(3 回測定の平均 )BEGININSERTCOMMIT平均レイテンシ( ms )拡張あり拡張なし0.0 0.5 1.0 1.5 2.00.640.650.630.631.071.07pgbench_history 32Cli INSERT(1.25 万件 ) 処理時間(3 回測定の平均 )BEGININSERTCOMMIT平均レイテンシ( ms )効果なし…
実験 2 :複数クライアントからの挿入● pgbench_history のレコード長が短い( 30 バイト程度)だと効果がみえないのかも● 1ブロック内に格納されるレコード数が多い→ブロック拡張の契機が少ない?
実験 2-2 :複数クライアントからの挿入( レコード長拡大)
実験 2-2 :複数クライアントからの挿入● longrec というテーブルを定義– UNLOGGED TABLE– FILFACTOR=100● 検証パターン– 拡張あり: TRUNCATE 後に pgbench を実行– 拡張なし:スライド 7 の状態から pgbench を実行● 今回は 1 ~ 16 クライアント各 10 万~ 6250 回 INSERTCREATE UNLOGGED TABLE longrec (aid integer,mtime timestamp,data text -- ここに長大文字を入れる)
実験 2-2 :複数クライアントからの挿入拡張あり拡張なし0.0 0.1 0.2 0.3 0.4 0.5 0.60.050.050.390.390.070.07longrec 1Cli INSERT(10 万件 ) 処理時間(3 回測定の平均 )BEGININSERTCOMMIT平均レイテンシ( ms )拡張あり拡張なし0.0 0.2 0.4 0.6 0.8 1.0 1.20.110.110.670.660.320.33longrec 4Cli INSERT(2.5 万件 ) 処理時間(3 回測定の平均 )BEGININSERTCOMMIT平均レイテンシ( ms )拡張あり拡張なし0.0 0.5 1.0 1.5 2.0 2.50.180.181.091.070.991longrec 8Cli INSERT(1.25 万件 ) 処理時間(3 回測定の平均 )BEGININSERTCOMMIT平均レイテンシ( ms )拡張あり拡張なし0.0 1.0 2.0 3.0 4.0 5.00.760.751.081.052.392.4longrec 16Cli INSERT(0.625 万件 ) 処理時間(3 回測定の平均 )BEGININSERTCOMMIT平均レイテンシ( ms )効果なし…
みんなに聞いてみたいこと
データファイル領域の事前確保● PostgreSQL のデータファイルが事前確保されていれば嬉しいケースって何かあるだろうか?– 実はそんなケースはないのか?– 拡張時のコストは気にするものではない?● 事前領域確保のスマートな方法はないものか。– PostgreSQL 標準機能でもっとスマートにできないか?– 誰かこんなユーティリティ作っていたりしない?
みなさまのコメント● Oracle とかだと表領域として OS レベルでの連続領域をとっていたりした● ある程度のサイズ(数百 GB )のテーブルで DELETE→VACUUM 切り詰め→ INSERT の繰り返しのようなケースで問題になった( PG14 で改善?)– SR 構成のスタンバイが上記のような状態で影響を受ける● テーブル AM として、事前確保+なるべく拡張しないものを作るというのが PostgreSQL の作法なのかもしれない。
おしまい