Upgrade to Pro — share decks privately, control downloads, hide ads and more …

Performance_loss_due_to_data_file_extend

nuko_yokohama
February 20, 2023

 Performance_loss_due_to_data_file_extend

Is there any performance impact from PostgreSQL data file extend?

nuko_yokohama

February 20, 2023
Tweet

More Decks by nuko_yokohama

Other Decks in Technology

Transcript

  1. データファイルの拡張による
    パフォーマンス低下はあるのだろうか
    PostgreSQL Unconferecnce #39 (2023-02-20)
    トーク 10 分
    議論 10 分
    くらいです

    View full-size slide

  2. 自己紹介
    ● ぬこ@横浜 , @nuko_yokohama
    ● にゃーん
    ● 趣味でポスグレをやってる者だ
    ● 体重&お腹が EXTEND して困っています

    View full-size slide

  3. PostgreSQL のデータファイル拡張

    View full-size slide

  4. PostgreSQL のデータファイル
    ● PostgreSQL のテーブルやインデックスはファイル化されている
    – 8KB 単位のブロックが連続しているファイル
    – ファイルサイズは可変( 0 バイト~ 1GB )
    ● 事前の領域確保はされない。
    ブロック (8KB)
    ブロック (8KB)
    ブロック (8KB)
    ブロック (8KB)
    ・・・
    ブロック (8KB)
    ブロック (8KB)
    ブロック (8KB)
    ブロック (8KB)
    ・・・
    ブロック (8KB)
    ブロック (8KB)
    ブロック (8KB)
    ・・・
    ・・・
    ブロック (8KB)
    最大 1GB
    最終ブロックは
    1GB 以下
    セグメントファイル セグメントファイル
    セグメントファイル

    View full-size slide

  5. PostgreSQL のデータファイルの拡張 / 縮小
    ● 拡張
    – データ挿入( INSERT/COPY FROM )時
    – データ更新( UPDATE )時
    ● FILLFACTOR が 100 だと拡張しやすい
    ● HOT 更新でない場合
    ● 縮小
    – TRUNCATE (切り詰め)
    – VACUUM FULL/CLUSTER (再編成)
    – 全件 DELETE 後の VACUUM

    View full-size slide

  6. PostgreSQL のデータファイルの拡張
    ● データファイルはブロック( 8KB )単位で拡張する
    ● 1 ブロックのサイズは極端に大きくはないものの、拡張のコス
    トはタダではないのではないか。
    – ページサイズ分のディスクへの書き込み
    – 拡張時のロック (wait_event の extend? )
    ● 拡張しない状態だと挿入等は早くなるの?

    View full-size slide

  7. 拡張なし 状態の作り方
    ● テーブルに最後に挿入されたレコード「以外」を DELETE
    ● VACUUM
    – VACUUM FULL ではない!
    ● テーブルに最後に挿入されたレコードを DELETE
    ● 環境: Linux/PostgreSQL 15.2

    View full-size slide

  8. 拡張なし 状態の作り方(無理やり)
    ブロック (8KB)
    ブロック (8KB)
    ブロック (8KB)
    ブロック (8KB)
    ・・・
    ブロック (8KB)
    無効領域
    無効領域
    1 レコードのみ残
    他は無効領域
    ・・・
    無効領域
    空き領域
    空き領域
    1 レコードのみ残
    他は空き領域
    ・・・
    無効領域 空き領域
    空き領域
    空き領域
    1 レコード無効
    他は空き領域
    ・・・
    空き領域 空き領域
    初期状態
    最終ブロックの 1 件
    以外を DELETE
    VACUUM VACUUM

    View full-size slide

  9. 実験 1 :バルクコピー

    View full-size slide

  10. 実験 1 :バルクコピー
    ● pgbench_hisotry を以下の条件で定義
    – UNLOGGED TABLE
    – FILFACTOR=100
    ● 検証パターン
    – 拡張あり: TRUNCATE 後に COPY
    – 拡張なし:スライド 7 の状態から COPY
    ● 400,000 件のデータを COPY FROM で登録

    View full-size slide

  11. 実験 1 :バルクコピー
    ● 拡張なしの場合、約 5% 程度 COPY 時間が短縮
    拡張あり
    拡張なし
    0 100 200 300 400 500
    460.28
    440.44
    pgbench_history COPY(40 万件 ) 処理時間
    (3 回測定の平均 )
    処理時間( ms )
    効果はいまひとつだ…

    View full-size slide

  12. 実験 2 :複数クライアントからの挿入

    View full-size slide

  13. 実験 2 :複数クライアントからの挿入
    ● pgbench_hisotry を以下の条件で定義
    – UNLOGGED TABLE
    – FILFACTOR=100
    ● 検証パターン
    – 拡張あり: TRUNCATE 後に pgbench 実行
    – 拡張なし:スライド 7 の状態から pgbench 実行
    ● 今回は 4 ~ 32 クライアント各 10 万~ 1.25 万回 INSERT

    View full-size slide

  14. 実験 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

    View full-size slide

  15. 実験 2 :複数クライアントからの挿入
    拡張あり
    拡張なし
    0.0 0.1 0.2 0.3 0.4 0.5
    0.09
    0.10
    0.13
    0.14
    0.16
    0.17
    pgbench_history 4Cli INSERT(10 万件 ) 処理時間
    (3 回測定の平均 )
    BEGIN
    INSERT
    COMMIT
    平均レイテンシ( ms )
    拡張あり
    拡張なし
    0.0 0.1 0.2 0.3 0.4 0.5 0.6
    0.14
    0.14
    0.17
    0.17
    0.26
    0.25
    pgbench_history 8Cli INSERT(5 万件 ) 処理時間
    (3 回測定の平均 )
    BEGIN
    INSERT
    COMMIT
    平均レイテンシ( 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.2
    0.30
    0.30
    0.3
    0.3
    0.54
    0.54
    pgbench_history 16Cli INSERT(2.5 万件 ) 処理時間
    (3 回測定の平均 )
    BEGIN
    INSERT
    COMMIT
    平均レイテンシ( ms )
    拡張あり
    拡張なし
    0.0 0.5 1.0 1.5 2.0
    0.64
    0.65
    0.63
    0.63
    1.07
    1.07
    pgbench_history 32Cli INSERT(1.25 万件 ) 処理時間
    (3 回測定の平均 )
    BEGIN
    INSERT
    COMMIT
    平均レイテンシ( ms )
    効果なし…

    View full-size slide

  16. 実験 2 :複数クライアントからの挿入
    ● pgbench_history のレコード長が短い( 30 バイト程度)だと効果が
    みえないのかも
    ● 1ブロック内に格納されるレコード数が多い→ブロック拡張の契機が
    少ない?

    View full-size slide

  17. 実験 2-2 :複数クライアントからの挿入
    ( レコード長拡大)

    View full-size slide

  18. 実験 2-2 :複数クライアントからの挿入
    ● longrec というテーブルを定義
    – UNLOGGED TABLE
    – FILFACTOR=100
    ● 検証パターン
    – 拡張あり: TRUNCATE 後に pgbench を実行
    – 拡張なし:スライド 7 の状態から pgbench を実行
    ● 今回は 1 ~ 16 クライアント各 10 万~ 6250 回 INSERT
    CREATE UNLOGGED TABLE longrec (
    aid integer,
    mtime timestamp,
    data text -- ここに長大文字を入れる
    )

    View full-size slide

  19. 実験 2-2 :複数クライアントからの挿入
    拡張あり
    拡張なし
    0.0 0.1 0.2 0.3 0.4 0.5 0.6
    0.05
    0.05
    0.39
    0.39
    0.07
    0.07
    longrec 1Cli INSERT(10 万件 ) 処理時間
    (3 回測定の平均 )
    BEGIN
    INSERT
    COMMIT
    平均レイテンシ( ms )
    拡張あり
    拡張なし
    0.0 0.2 0.4 0.6 0.8 1.0 1.2
    0.11
    0.11
    0.67
    0.66
    0.32
    0.33
    longrec 4Cli INSERT(2.5 万件 ) 処理時間
    (3 回測定の平均 )
    BEGIN
    INSERT
    COMMIT
    平均レイテンシ( ms )
    拡張あり
    拡張なし
    0.0 0.5 1.0 1.5 2.0 2.5
    0.18
    0.18
    1.09
    1.07
    0.99
    1
    longrec 8Cli INSERT(1.25 万件 ) 処理時間
    (3 回測定の平均 )
    BEGIN
    INSERT
    COMMIT
    平均レイテンシ( ms )
    拡張あり
    拡張なし
    0.0 1.0 2.0 3.0 4.0 5.0
    0.76
    0.75
    1.08
    1.05
    2.39
    2.4
    longrec 16Cli INSERT(0.625 万件 ) 処理時間
    (3 回測定の平均 )
    BEGIN
    INSERT
    COMMIT
    平均レイテンシ( ms )
    効果なし…

    View full-size slide

  20. みんなに聞いてみたいこと

    View full-size slide

  21. データファイル領域の事前確保
    ● PostgreSQL のデータファイルが
    事前確保されていれば嬉しいケースって何かあるだろうか?
    – 実はそんなケースはないのか?
    – 拡張時のコストは気にするものではない?
    ● 事前領域確保のスマートな方法はないものか。
    – PostgreSQL 標準機能でもっとスマートにできないか?
    – 誰かこんなユーティリティ作っていたりしない?

    View full-size slide

  22. みなさまのコメント
    ● Oracle とかだと表領域として OS レベルでの連続領域をとっていた
    りした
    ● ある程度のサイズ(数百 GB )のテーブルで DELETE→VACUUM 切
    り詰め→ INSERT の繰り返しのようなケースで問題になった
    ( PG14 で改善?)
    – SR 構成のスタンバイが上記のような状態で影響を受ける
    ● テーブル AM として、事前確保+なるべく拡張しないものを作ると
    いうのが PostgreSQL の作法なのかもしれない。

    View full-size slide

  23. おしまい

    View full-size slide