64 GB EPYCインスタンスに適切にチューニングされたPostgres

これが構築するもの

E-8上の単一のPostgresインスタンスで、与えられたハードウェアを活用します：8つの専用Zen 4コア、64GBのレジスタードECC DDR5、そして電力損失保護付きのGen4 NVMe。初期状態では、Postgresは2009年のラップトップで起動するように設定されています。そのままにしておくと、このマシンのメモリの約100分の1しか使わず、なぜ遅いのか不思議に思うでしょう。

以下のすべての数値は、コピーではなく導出されたものです。インスタンスのメモリ量が異なる場合、算術計算を示しているので、再計算できます。これは、主にトランザクション処理で一部レポーティングがあるワークロード向けに書かれており、それは実際にほとんどの人が持っているものです（何と言おうと）。

始める前に

E-8以上。64GB未満では比率は成り立ちますが、絶対数は成り立たず、huge pagesは面倒に見合わなくなります。
ECCメモリ。これはEPYCラインにはありますが、Ryzenラインにはありません。共有バッファのメモリエラーは、警告なしにディスクに書き戻される破損ページとなります。
Debian 13。これはPostgreSQL 17をパッケージしています。

1. インストールと初期化

apt update && apt install -y postgresql postgresql-contrib numactl
systemctl stop postgresql
pg_dropcluster 17 main
pg_createcluster 17 main -- --data-checksums --encoding=UTF8 --locale=C.UTF-8

データチェックサムは初期化時に選択する必要があり、スループットの数パーセントを犠牲にします。これらは、破損ページが報告されるか、提供されるかの違いです。したがって、その2パーセントを受け入れます。

2. メモリ

4つの設定、4つの計算：

shared_buffersはRAMの4分の1：16GB。以前はより大きな値は推奨されませんでした。専用マシンで最新のカーネルでは、4分の1が十分にテストされた開始点であり、ワーキングセットが大きい場合は3分の1も妥当です。
effective_cache_sizeはRAMの4分の3：48GB。これは何も割り当てません。プランナーにカーネルがキャッシュしている可能性が高い量を伝えるだけで、低く設定しすぎると、プランナーが選択すべきインデックススキャンを拒否します。
work_memはソートノードごと（コネクションごとではありません）：200コネクションでそれぞれおそらく2つのソートがある場合、64GBマシンでは32MBが妥当な上限です。レポーティングクエリでは、グローバルにではなくセッションごとに引き上げてください。
maintenance_work_memはバキュームとインデックスビルド用：2GBで、autovacuum_work_memはそれを継承するために残します。

shared_buffers = 16GB
effective_cache_size = 48GB
work_mem = 32MB
maintenance_work_mem = 2GB
huge_pages = try

3. Huge pages

16GBの共有バッファを4キロバイトのページでマッピングすると、バックエンドごとに400万のページテーブルエントリが発生します。2メガバイトのページでは、それが500分の1に減り、その利点は見出しの数値ではなく、並行処理時のCPU低下として現れます。

systemctl start postgresql
sudo -u postgres psql -c "SHOW shared_memory_size_in_huge_pages;"

表示される数値を取得し、10%のヘッドルームを追加して書き留めます：

echo "vm.nr_hugepages = 8600" > /etc/sysctl.d/60-postgres.conf
echo "vm.overcommit_memory = 2" >> /etc/sysctl.d/60-postgres.conf
echo "vm.overcommit_ratio = 90" >> /etc/sysctl.d/60-postgres.conf
echo "vm.swappiness = 1" >> /etc/sysctl.d/60-postgres.conf
sysctl --system
echo never > /sys/kernel/mm/transparent_hugepage/enabled

明示的なhuge pagesははい、透過的なhuge pagesはいいえ。後者は予測不能な瞬間にメモリをデフラグし、人々がストレージのせいにして数週間を費やすレイテンシのスパイクを引き起こします。

4. 書き込み ahead log とチェックポイント

デフォルトのチェックポイント設定では、負荷時に数秒ごとにフラッシュが強制され、NVMeが小さな同期書き込みのキューになります。

wal_level = replica
max_wal_size = 16GB
min_wal_size = 2GB
checkpoint_timeout = 15min
checkpoint_completion_target = 0.9
wal_compression = zstd
wal_buffers = 64MB
synchronous_commit = on
full_page_writes = on

synchronous_commitはオンのままにします。オフにすることは利用可能な最大のスループット向上であり、電力損失で失われる可能性のあるトランザクションを確認することを意味します。当社のドライブにはコンデンサバックアップ付きの書き込みキャッシュがあるため、ここではfsyncは実際に安価です。代わりに正直な場所でスループットを購入してください。

5. ストレージとプランナー

random_page_cost = 1.1
seq_page_cost = 1.0
effective_io_concurrency = 200
maintenance_io_concurrency = 200
default_statistics_target = 200
jit = off

random_page_costが4というのはスピンドルディスクの数値であり、これは野生で最も一般的な単一の誤設定です。NVMeではランダム読み取りはシーケンシャルとほぼ同じコストであり、プランナーにそうでないと伝えると、大きなテーブルでインデックスを回避します。JITコンパイルはオフです。短いトランザクションクエリでは、実行よりもコンパイルに時間がかかるためです。分析クエリではセッションごとにオンにしてください。

6. 並列性、自動バキューム、コネクション

max_connections = 200
max_worker_processes = 8
max_parallel_workers = 8
max_parallel_workers_per_gather = 4
max_parallel_maintenance_workers = 4

autovacuum_max_workers = 4
autovacuum_naptime = 15s
autovacuum_vacuum_scale_factor = 0.02
autovacuum_analyze_scale_factor = 0.01
autovacuum_vacuum_cost_limit = 3000

shared_preload_libraries = 'pg_stat_statements'
track_io_timing = on
log_min_duration_statement = 500ms
log_checkpoints = on
log_autovacuum_min_duration = 0

ワーカー数は8つの専用コアに一致します。なぜならそれらは専用だからです。オーバーサブスクライブされたホストでは、これらの数値は嘘になり、実際に販売されたコアの割合に応じて調整する必要があります。ここではその問題はありません。

デフォルトの自動バキュームのスケールファクター0.2は、1億行のテーブルが2千万のデッドタプルを待ってから何かが起こることを意味します。20%ではなく2%にすると、バキュームはまれに壊滅的にではなく、頻繁に短時間実行されます。

7. プーリング

8コアで200のバックエンドは並列処理ではなく、追加のメモリオーバーヘッドを伴うキューです。プーラーを前に置きます：

apt install -y pgbouncer

[databases]
app = host=/var/run/postgresql dbname=app

[pgbouncer]
listen_addr = 10.9.0.1
listen_port = 6432
auth_type = scram-sha-256
auth_file = /etc/pgbouncer/userlist.txt
pool_mode = transaction
default_pool_size = 32
max_client_conn = 2000
server_idle_timeout = 60

32のサーバーコネクションでのトランザクションプーリングは、コアあたり4つになり、これはこのシリコンでスループットの向上が止まるおおよその点です。セッションレベルの機能（アドバイザリロックやトランザクションをまたぐプリペアドステートメントなど）を使用するアプリケーションは、セッションプーリングが必要であり、その場合はより少ない数になります。

systemctl restart postgresql pgbouncer

検証

まず設定。ここで反映されなかったものは、3週間後ではなく、ここで明らかになります：

sudo -u postgres psql -c "SELECT name, setting, unit FROM pg_settings WHERE name IN ('shared_buffers','effective_cache_size','work_mem','huge_pages','random_page_cost','max_wal_size');"
grep -E "HugePages_(Total|Free)" /proc/meminfo

HugePages_FreeはHugePages_Totalより数千ページ少ないはずです。これはPostgresが実際にマップしたことを意味します。等しい値は小さなページにフォールバックしたことを意味し、huge_pages = tryが静かに失敗を飲み込んだことを意味します。

次に測定します。共有バッファに収まるほど小さいが興味深いほど十分に大きなデータセットを構築します：

sudo -u postgres createdb bench
sudo -u postgres pgbench -i -s 800 bench
sudo -u postgres pgbench -c 32 -j 8 -T 60 -S bench
sudo -u postgres pgbench -c 32 -j 8 -T 60 bench

E-8では、読み取り専用の実行は毎秒数十万トランザクションの範囲に入り、読み書きの実行は毎秒数千の範囲に入ります。正確な数値はサイトとデータセットに依存するため、有用な信号は桁数です：selectのみのテストが数十万ではなく数万を返す場合、shared_buffersが有効になっていないか、最初の実行でコールドキャッシュを通してベンチマークしています。

最後に、統計拡張機能がロードされていることを確認します。それはその後毎週実際に使用するものだからです：

sudo -u postgres psql bench -c "CREATE EXTENSION IF NOT EXISTS pg_stat_statements;"
sudo -u postgres psql bench -c "SELECT calls, round(mean_exec_time::numeric,2) AS ms, query FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 5;"

その後

このデータベースにあなたが失いたくないものが入る前に、別の都市のセカンダリインスタンスへのWALアーカイブをオンにします。テストしたことのないベースバックアップの復元はバックアップではなく、希望です。ロケーションページは、スタンバイがプライマリと同じ建物にないようにするために部分的に存在します。

続きを読む

すべて表示

インフラストラクチャ

都市を選べ。サイズを選べ。コインで支払え。

あなたが誰かに関するフォームも、承認する人間を待つ必要も、確認のための電話もありません。請求が完了すれば、認証情報があなたの受信トレイに届きます。

サーバーを構成エンジニアに相談する