私が失業しているため、ポストグレスを42,000倍遅くする

これを簡単にすることはできません。これはポストグレースチューニングの課題であり、スロットルの1つのメガヘルツとdelete-indexesチャレンジではないので、 すべての変更はパラメーターにある必要があります postgresql.conf。さらに、データベースには、合理的な時間内に少なくとも1つのトランザクションを処理する機能が必要です。ポストグラスを停止するだけでは簡単すぎます。 Postgresは、制限を実施し、構成を最小化することにより、この愚かな決定を下すことを可能な限り困難にしようとするため、これは見た目よりも困難です。

パフォーマンスを測定するために、ベンチベースで実装された128の倉庫でTPC-Cを使用します。それぞれが1秒あたり10Kトランザクションを出力しようとする100の接続を使用します。各テストは120秒続き、2回実行されます。最初にキャッシュを温め、測定を収集するために2秒を温めます。

すべてがデフォルトに残されたベースラインを測定しました postgresql.conf、基本的な調整が増加することを除いて shared_buffers、 work_mem、および労働者プロセスの数。そのテストでは、私は素敵な7082 TPSを手に入れました。さて、どれだけ遅いPostgresが行くかを見てみましょう。

Postgresが読み取りクエリに効率的に応答できる方法の1つは、広範なキャッシングを使用することです。ディスクからのデータへのアクセスはです 遅い、したがって、Postgresがディスクからデータのブロックを読み取るたびに、RAMでブロックするキャッシュを使用して、そのブロックがRAMから読み取る必要がある次のクエリを可能にします。もちろん、すべてのクエリに可能な限り低速の読み取り方法を使用するように強制したいので、このキャッシュが小さいほど良いです。バッファキャッシュのサイズと、Postgresの共有メモリのその他の要素を使用して自由に制御できます。 shared_buffers ノブ。残念ながら、Postgresはアクティブなデータベースページが処理される領域としてバッファキャッシュも使用するため、これを0に設定することはできません。幸いなことに、私はまだそれをかなり低くすることができます。

最初に、私はから行きました 10GB ベースラインに割り当てました 8MB。

shared_buffers = 8MB

すでに、Postgresは初期速度の1/7で動作しています。バッファキャッシュの削減により、PostgresがRAMのページを少なくすることを余儀なくされました。つまり、オペレーティングシステムに移動することなく99.90％から70.52％に達することなく満たすことができるページリクエストの割合を意味し、読み取りシステムの数がほぼ300倍増加します。

しかし、私たちはより良くすることができます。 70％がまだ高すぎるため、キャッシュのサイズをさらに減らすことができるはずです。次に、128kbを試しました。

おっと。 128kbの共有バッファーは、最大16個のデータベースページ（共有バッファーの他のコンテンツを除く）しか保存できません。ポストグラスには、16ページ以上に同時にアクセスする必要があります。いじくり回った後、可能な限り低い値が約2 MBであることがわかりました。ポストグレスは現在500 TPを下回っています。

shared_buffers = 2MB

Postgresには、計算上の高価なトランザクションを処理する以外にいくつかのタスクがあります。私はこれを私の利益のために使用することができます。ストレージの断片化を最小限に抑えるために、Postgresは空の空間（削除のような操作から）を見つけるオートバクウムプロセスを実行し、そのスペースを他のタプルで埋めます。通常、これは、過度のパフォーマンスペナルティを防ぐために特定の数の変更が行われた後にのみ実行されますが、各実行の間の時間を最小限に抑えるためにAutovacuumを再構成できます。

autovacuum_vacuum_insert_threshold = 1 # autovacuum can be triggered with only 1 insert
autovacuum_vacuum_threshold = 0 # minimum number of inserts, updates, or deletes needed to trigger a vacuum
autovacuum_vacuum_scale_factor = 0 # proportion of the unfrozen table size to consider when calculating thresholds
autovacuum_vacuum_max_threshold = 1 # max number of inserts, updates, or deletes needed to trigger a vacuum
autovacuum_naptime = 1 # the minimum delay between autovacuums in seconds; unfortunately, this cannot be set below 1, which limits us
vacuum_cost_limit = 10000 # query cost limit, which, if exceeded, will cause the vacuum to pause; I don't want the vacuum to ever stop, so I maxed this out
vacuum_cost_page_dirty = 0
vacuum_cost_page_hit = 0
vacuum_cost_page_miss = 0 # all of these minimize the cost for operations when calculating for `vacuum_cost_limit`

また、統計を収集するAutovacuum Analyzerを再構成しました。これは、掃除機とクエリ計画を導く統計を収集します（ネタバレ：正確な統計は、クエリプランナーをいじるのを止めるべきではありません）：

autovacuum_analyze_threshold = 0 # same as autovacuum_vacuum_threshold, but for ANALYZE
autovacuum_analyze_scale_factor = 0 # same as autovacuum_vacuum_scale_factor

また、掃除プロセス自体を可能な限り遅くしようとしました。

maintenance_work_mem = 128kB # the amount of memory allocated for vacuuming processes
log_autovacuum_min_duration = 0 # the duration (in milliseconds) that a autovacuum operation is required to run for before it is logged; I might as well log everything;
logging_collector = on # enables logging in general
log_destination = stderr,jsonlog # sets the output format/file for logs

反対のアプローチも機能する可能性があることに注意する必要があります。自動吸収を完全に無効にすると、ページは死んだタプルで埋められ、パフォーマンスは徐々に減少します。ただし、これは2分間しか実行されていないインサートが多いワークロードであるため、そのアプローチは非効率的であるとは思いませんでした。

Postgresは現在、元の速度の1/20未満で動作しています。ログをチェックすることで、そのパフォーマンスヒットのソースを確認しました。

2025-07-20 09:10:20.455 EDT [25210] LOG:  automatic vacuum of table "benchbase.public.warehouse": index scans: 0
 pages: 0 removed, 222 remain, 222 scanned (100.00% of total), 0 eagerly scanned
 tuples: 0 removed, 354 remain, 226 are dead but not yet removable
 removable cutoff: 41662928, which was 523 XIDs old when operation ended
 frozen: 0 pages from table (0.00% of total) had 0 tuples frozen
 visibility map: 0 pages set all-visible, 0 pages set all-frozen (0 were all-visible)
 index scan not needed: 0 pages from table (0.00% of total) had 0 dead item identifiers removed
 avg read rate: 116.252 MB/s, avg write rate: 4.824 MB/s
 buffer usage: 254 hits, 241 reads, 10 dirtied
 WAL usage: 2 records, 2 full page images, 16336 bytes, 1 buffers full
 system usage: CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.01 s
2025-07-20 09:10:20.773 EDT [25210] LOG:  automatic analyze of table "benchbase.public.warehouse"
 avg read rate: 8.332 MB/s, avg write rate: 0.717 MB/s
 buffer usage: 311 hits, 337 reads, 29 dirtied
 WAL usage: 36 records, 5 full page images, 42524 bytes, 4 buffers full
 system usage: CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.31 s
2025-07-20 09:10:20.933 EDT [25210] LOG:  automatic vacuum of table "benchbase.public.district": index scans: 0
 pages: 0 removed, 1677 remain, 1008 scanned (60.11% of total), 0 eagerly scanned
 tuples: 4 removed, 2047 remain, 557 are dead but not yet removable
 removable cutoff: 41662928, which was 686 XIDs old when operation ended
 frozen: 0 pages from table (0.00% of total) had 0 tuples frozen
 visibility map: 0 pages set all-visible, 0 pages set all-frozen (0 were all-visible)
 index scan bypassed: 2 pages from table (0.12% of total) have 9 dead item identifiers
 avg read rate: 50.934 MB/s, avg write rate: 9.945 MB/s
 buffer usage: 1048 hits, 1009 reads, 197 dirtied
 WAL usage: 6 records, 1 full page images, 8707 bytes, 0 buffers full
 system usage: CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.15 s
2025-07-20 09:10:21.220 EDT [25210] LOG:  automatic analyze of table "benchbase.public.district"
 avg read rate: 47.235 MB/s, avg write rate: 1.330 MB/s
 buffer usage: 115 hits, 1705 reads, 48 dirtied
 WAL usage: 30 records, 1 full page images, 17003 bytes, 1 buffers full
 system usage: CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.28 s
2025-07-20 09:10:21.543 EDT [25212] LOG:  automatic vacuum of table "benchbase.public.warehouse": index scans: 0
 pages: 0 removed, 222 remain, 222 scanned (100.00% of total), 0 eagerly scanned
 tuples: 0 removed, 503 remain, 375 are dead but not yet removable
 removable cutoff: 41662928, which was 845 XIDs old when operation ended
 frozen: 0 pages from table (0.00% of total) had 0 tuples frozen
 visibility map: 0 pages set all-visible, 0 pages set all-frozen (0 were all-visible)
 index scan not needed: 0 pages from table (0.00% of total) had 0 dead item identifiers removed
 avg read rate: 131.037 MB/s, avg write rate: 5.083 MB/s
 buffer usage: 268 hits, 232 reads, 9 dirtied
 WAL usage: 1 records, 0 full page images, 258 bytes, 0 buffers full
 system usage: CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.01 s
2025-07-20 09:10:21.813 EDT [25212] LOG:  automatic analyze of table "benchbase.public.warehouse"
 avg read rate: 10.244 MB/s, avg write rate: 0.851 MB/s
 buffer usage: 307 hits, 337 reads, 28 dirtied
 WAL usage: 33 records, 3 full page images, 30864 bytes, 2 buffers full
 system usage: CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.25 s
# ... it continues similarly

Postgresは、1秒ごとにホットテーブルの自動真空と分析操作を実行します。これは、バッファキャッシュのヒット率がすでに低いため、ディスクからかなりの量を読み取るように強制します。さらに良いことに、これらはほとんど何もしていません。なぜなら、各ランの間にほとんど変わっていないからです。もちろん、293 TPSはまだ多すぎます。

イントロで、インデックスを台無しにすることはできないと言ったときのことを覚えていますか？まあ、私たちは本当にする必要はありません。 Postgresは、ランダムにアクセスしたページのロードがハードドライブのロードが遅くなるため、クエリプランの計算時にシーケンシャルアクセスとは異なるディスクからのページのランダムアクセスとは異なります。インデックスを使用してテーブルをクエリするには通常、ページにランダムにアクセスする必要がありますが、テーブルスキャンには通常、シーケンシャルアクセスが含まれます。つまり、ランダムページの相対コストを調整することで、インデックスの使用を防ぐことができます。

random_page_cost = 1e300 # sets the cost of accessing a random page
cpu_index_tuple_cost = 1e300 # sets the cost of processing one tuple from an index

これらは、ほとんどすべての場合にインデックスを無効にするために変更する必要がある2つのパラメーターです。私は最終的に増やさなければなりませんでした shared_buffers に戻る価値 8MB テーブルスキャンでエラーを防ぐためには、パフォーマンス面ではあまり役に立たなかったことは明らかです。

Postgresは現在、1秒あたり1回のトランザクション未満であり、デフォルトのチューニングよりも7,000倍以上遅く、すべて外部で何も変更せずに postgresql.conf。しかし、私はまだ最後のトリックを1つ持っています。

100の接続のそれぞれに独自のプロセスがあるため、Postgresを単一のスレッドにすることはできません。ただし、Postgres 18の新しいオプションを使用して、I/Oを単一のスレッドにすることができます。 Postgres 18は新しいノブを紹介します、 io_method、スレッドが同期的にi/o syscallsを発行するかどうかを制御します（io_method = sync）、非同期的に労働者のスレッドにsyscallsを発行するように依頼します（io_method = worker）、または新しいものを使用します io_uring Linux API（io_method = io_uring）。と組み合わせて io_workers、使用するときにワーカースレッドの最大数を確立する io_method=worker、すべてのI/Oを1つのワーカースレッドに強制することができます。

io_method = worker
io_workers = 1

さて、Postgresは現在、0.1 TPSでもはるかに低くなっています。デッドロックのために終了しなかったトランザクションを除外すると、ストーリーはさらに悪化します（より良いですか？）：100の接続と120秒で、11のトランザクションのみが正常に完了しました。

さて、数時間32個のノブ後、私はPostgresデータベースを正常に殺しました。誰があなたがそれをいじりすることによってポストグレスのパフォーマンスにそれほどダメージを与えることができると思っただろう postgresql.conf？私は1桁のTPSに到達できると考えましたが、Postgresが私にこれをあまりさせてくれるとは思いませんでした。これを自分で再現しようとする場合は、ここにデフォルトから変更されたノブがあります。

shared_buffers = 8MB
autovacuum_vacuum_insert_threshold = 1
autovacuum_vacuum_threshold = 0
autovacuum_vacuum_scale_factor = 0
autovacuum_vacuum_max_threshold = 1
autovacuum_naptime = 1
vacuum_cost_limit = 10000
vacuum_cost_page_dirty = 0
vacuum_cost_page_hit = 0
vacuum_cost_page_miss = 0
autovacuum_analyze_threshold = 0
autovacuum_analyze_scale_factor = 0
maintenance_work_mem = 128kB
log_autovacuum_min_duration = 0
logging_collector = on
log_destination = stderr,jsonlog
wal_writer_flush_after = 0
wal_writer_delay = 1
min_wal_size = 32MB
max_wal_size = 32MB
checkpoint_timeout = 30
checkpoint_flush_after = 1
wal_sync_method = open_datasync
wal_level = logical
wal_log_hints = on
summarize_wal = on
track_wal_io_timing = on
checkpoint_completion_target = 0
random_page_cost = 1e300
cpu_index_tuple_cost = 1e300
io_method = worker
io_workers = 1