针对 64 GB EPYC 实例正确调优 Postgres

本文构建的内容

在 E-8 上运行一个 Postgres 实例，充分利用其硬件：八个专用 Zen 4 核心、64 GB 带 ECC 的 DDR5 注册内存，以及带断电保护的 Gen4 NVMe。开箱即用，Postgres 的配置是为 2009 年的笔记本设计的。照此配置，它只会用掉这台机器内存的百分之一左右，然后抱怨自己为什么慢。

以下每个数字都是推导出来的，不是照搬的。如果你的实例内存不同，我这里展示了计算过程，你可以重新计算。本文针对的是以事务为主、带一些报表的工作负载，这也是大多数人实际的工作负载，不管他们怎么说。

开始之前

• E-8 或更大。低于 64 GB 时，比例仍然成立，但绝对数值不成立，而且大页就变得不值得麻烦了。
• 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. 内存

四项设置，四个计算：

• shared_buffers 为 RAM 的四分之一：16GB。以前不鼓励使用较大的值；在专用机器和现代内核上，四分之一是经过充分测试的起点，如果工作集更大，三分之一也是合理的。
• effective_cache_size 为 RAM 的四分之三：48GB。这不分配任何东西；它告诉规划器内核可能缓存了多少，设置过低会导致规划器拒绝本来应该选择的索引扫描。
• work_mem 按排序节点设置，而不是按连接设置：有 200 个连接，每个连接大约两次排序，32MB 在 64 GB 的机器上是一个合理的上限。对于报表查询，按会话调高，而不是全局调高。
• maintenance_work_mem 用于 vacuum 和索引构建：2GB，autovacuum_work_mem 留作继承。

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

3. 大页

十六 GB 的共享缓冲区以 4 KB 页映射，意味着每个后端有 400 万个页表项。2 MB 页将这一数量减少 500 倍，其优势体现在并发下的 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

显式大页开启，透明大页关闭。透明大页会在不可预测的时刻进行内存碎片整理，产生延迟峰值，人们会花数周时间责怪存储。

4. 预写日志和检查点

默认的检查点设置会导致负载下每几秒刷新一次，将 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

工作进程数量与八个专用核心匹配，因为它们是专用的。在超售主机上，这些数字是虚假的，你会把它们调低到实际获得的核心数量；但这里没有这个问题。

默认的自动清理比例因子为 0.2，意味着一个一亿行的表要等待两千万个死元组才会进行清理。将 20% 改为 2%，可以让清理频繁而短暂地运行，而不是偶尔且灾难性地运行。

7. 连接池

八个核心上的两百个后端并不是并行，而是带有额外内存开销的队列。在前面加一个连接池：

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 个服务器连接，每个核心四个，大致就是这类 CPU 上吞吐量不再提升的点。使用会话级功能（如咨询锁或跨事务准备语句）的应用程序需要会话池化，并且会得到更小的数字。

systemctl restart postgresql pgbouncer

验证

先检查设置。任何未生效的设置都会在这里显示出来，而不是三周后：

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 上，只读运行每秒达到数十万事务，读写运行达到每秒数千。具体数字取决于站点和数据集，因此有用的信号是数量级：如果只选测试返回数万而不是数十万，说明 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 归档到另一个城市的第二台实例。从未测试过的恢复基本备份不是备份，而是一种希望，位置页面的存在部分是为了让你的备用机不与主服务器位于同一栋楼。