Upgrade to Pro
— share decks privately, control downloads, hide ads and more …
Speaker Deck
Features
Speaker Deck
PRO
Sign in
Sign up for free
Search
Search
Performance_loss_due_to_data_file_extend
Search
nuko_yokohama
February 20, 2023
Technology
0
270
Performance_loss_due_to_data_file_extend
Is there any performance impact from PostgreSQL data file extend?
nuko_yokohama
February 20, 2023
Tweet
Share
More Decks by nuko_yokohama
See All by nuko_yokohama
PostgreSQL ECPG
nuko_yokohama
0
4
PostgreSQL 18 is coming soon!
nuko_yokohama
0
60
BRIN(Block Range INdex)
nuko_yokohama
1
400
ltree extension
nuko_yokohama
1
500
PostgreSQL Built in Sharding
nuko_yokohama
0
730
PostgreSQL 16 Support load balancing in libpq
nuko_yokohama
0
480
PostgreSQL 16 Add pg_stat_io view, providing more detailed IO statistics
nuko_yokohama
0
230
Do PostgreSQL 16 Dream of Multi-Master Replication?
nuko_yokohama
0
1.2k
Apache AGE
nuko_yokohama
0
410
Other Decks in Technology
See All in Technology
職種の壁を溶かして開発サイクルを高速に回す~情報透明性と職種越境から考えるAIフレンドリーな職種間連携~
daitasu
0
170
未経験者・初心者に贈る!40分でわかるAndroidアプリ開発の今と大事なポイント
operando
5
750
開発者を支える Internal Developer Portal のイマとコレカラ / To-day and To-morrow of Internal Developer Portals: Supporting Developers
aoto
PRO
1
480
DroidKaigi 2025 Androidエンジニアとしてのキャリア
mhidaka
2
380
ブロックテーマ時代における、テーマの CSS について考える Toro_Unit / 2025.09.13 @ Shinshu WordPress Meetup
torounit
0
130
現場で効くClaude Code ─ 最新動向と企業導入
takaakikakei
1
260
Firestore → Spanner 移行 を成功させた段階的移行プロセス
athug
1
500
COVESA VSSによる車両データモデルの標準化とAWS IoT FleetWiseの活用
osawa
1
380
20250912_RPALT_データを集める→とっ散らかる問題_Obsidian紹介
ratsbane666
0
100
下手な強制、ダメ!絶対! 「ガードレール」を「檻」にさせない"ガバナンス"の取り方とは?
tsukaman
2
460
初めてAWSを使うときのセキュリティ覚書〜初心者支部編〜
cmusudakeisuke
1
280
Snowflake Intelligenceにはこうやって立ち向かう!クラシルが考えるAI Readyなデータ基盤と活用のためのDataOps
gappy50
0
280
Featured
See All Featured
How STYLIGHT went responsive
nonsquared
100
5.8k
Making Projects Easy
brettharned
117
6.4k
A Tale of Four Properties
chriscoyier
160
23k
Build The Right Thing And Hit Your Dates
maggiecrowley
37
2.9k
Distributed Sagas: A Protocol for Coordinating Microservices
caitiem20
333
22k
実際に使うSQLの書き方 徹底解説 / pgcon21j-tutorial
soudai
PRO
188
55k
Become a Pro
speakerdeck
PRO
29
5.5k
Designing for humans not robots
tammielis
253
25k
YesSQL, Process and Tooling at Scale
rocio
173
14k
Large-scale JavaScript Application Architecture
addyosmani
513
110k
ピンチをチャンスに:未来をつくるプロダクトロードマップ #pmconf2020
aki_iinuma
127
53k
What’s in a name? Adding method to the madness
productmarketing
PRO
23
3.7k
Transcript
データファイルの拡張による パフォーマンス低下はあるのだろうか PostgreSQL Unconferecnce #39 (2023-02-20) トーク 10 分 議論
10 分 くらいです
自己紹介 • ぬこ@横浜 , @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 件 以外を DELETE VACUUM 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 500 460.28 440.44 pgbench_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.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 ) 効果なし…
実験 2 :複数クライアントからの挿入 • pgbench_history のレコード長が短い( 30 バイト程度)だと効果が みえないのかも •
1ブロック内に格納されるレコード数が多い→ブロック拡張の契機が 少ない?
実験 2-2 :複数クライアントからの挿入 ( レコード長拡大)
実験 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 -- ここに長大文字を入れる )
実験 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 ) 効果なし…
みんなに聞いてみたいこと
データファイル領域の事前確保 • PostgreSQL のデータファイルが 事前確保されていれば嬉しいケースって何かあるだろうか? – 実はそんなケースはないのか? – 拡張時のコストは気にするものではない? •
事前領域確保のスマートな方法はないものか。 – PostgreSQL 標準機能でもっとスマートにできないか? – 誰かこんなユーティリティ作っていたりしない?
みなさまのコメント • Oracle とかだと表領域として OS レベルでの連続領域をとっていた りした • ある程度のサイズ(数百 GB
)のテーブルで DELETE→VACUUM 切 り詰め→ INSERT の繰り返しのようなケースで問題になった ( PG14 で改善?) – SR 構成のスタンバイが上記のような状態で影響を受ける • テーブル AM として、事前確保+なるべく拡張しないものを作ると いうのが PostgreSQL の作法なのかもしれない。
おしまい