当前位置：首页 > news >正文

【PostgreSQL从零到精通】第36篇：PostgreSQL内存配置与大页内存优化

news 2026/5/7 19:15:22

上一篇【第35篇】性能监控实战——数据库性能的“体检“方案
下一篇【第37篇】VACUUM优化与WAL写优化——减少后台维护对性能的影响

PostgreSQL 是一个内存消耗"大户"——shared_buffers、work_mem、操作系统缓存……内存配置是否合理，直接影响数据库性能。本文将深入讲解 PostgreSQL 各内存参数的含义、计算方法和优化技巧。

一、PostgreSQL 内存架构全景

PostgreSQL 内存使用全景图： ┌─────────────────────────────────────────────────────────────┐ │ 进程 │ │ ┌───────────┐ ┌───────────┐ ┌───────────┐ │ │ │ Main │ │ Backend │ │ Backend │ ... │ │ │ Server │ │ Process │ │ Process │ │ │ └─────┬─────┘ └─────┬─────┘ └─────┬─────┘ │ │ │ │ │ │ │ ┌─────┴─────────────┴─────────────┴────────┐ │ │ │ Shared Memory (共享内存) │ │ │ │ ┌──────────────────────────────────────────┐ │ │ │ │ │ shared_buffers（数据页缓存） │ │ │ │ │ │ WAL buffers（WAL 日志缓冲区） │ │ │ │ │ │ 锁表、进程等待表等 │ │ │ │ │ └──────────────────────────────────────────┘ │ │ │ └───────────────────────────────────────────────┘ │ │ │ │ ┌───────────────────────────────────────────────┐ │ │ │ Process Memory (进程私有内存) │ │ │ │ ┌─────────┐ ┌─────────┐ ┌─────────┐ │ │ │ │ │ work_mem │ │ temp_buffers │ │ maintenance_ │ │ │ │ │ │（排序、 │ │（临时表 │ │ work_mem │ │ │ │ │ │ 哈希） │ │ 缓存） │ │（维护操作）│ │ │ │ │ └─────────┘ └─────────┘ └─────────┘ │ │ │ └───────────────────────────────────────────────┘ │ │ │ │ ┌───────────────────────────────────────────────┐ │ │ │ OS Cache (操作系统文件缓存) │ │ │ │ 未被 shared_buffers 缓存的数据文件会缓存在这里 │ │ │ └───────────────────────────────────────────────┘ │ └─────────────────────────────────────────────────────────────┘

二、核心内存参数详解

2.1 shared_buffers——数据页缓存

shared_buffers 的作用： ┌─────────────────────────────────────────────────────────────┐ │ │ │ 相当于 InnoDB 的 innodb_buffer_pool_size │ │ │ │ 数据页读取流程： │ │ 1. 查询需要数据页 → 先查 shared_buffers │ │ 2. 命中（Cache Hit）→ 直接返回（很快） │ │ 3. 未命中 → 从磁盘读取 → 放入 shared_buffers │ │ │ │ 设置建议： │ │ 1. 专用数据库服务器：总内存的 25%-40% │ │ 2. 与其他服务共享：总内存的 20% 左右 │ │ 3. 不要超过 40%（留给 OS Cache 和进程内存） │ │ │ └─────────────────────────────────────────────────────────────┘

-- 查看当前设置SHOWshared_buffers;-- 默认 128MB（太小了！）-- 需要重启才能生效

# 修改 shared_buffers（需要重启）# postgresql.confshared_buffers=4GB# 64GB 服务器：25% = 16GB，保守点用 4-8GB

2.2 effective_cache_size——优化器的"参考指南"

effective_cache_size 的作用： ┌─────────────────────────────────────────────────────────────┐ │ │ │ 不分配实际内存！只是告诉优化器： │ │ "OS 大概能缓存多少数据" │ │ │ │ 影响优化器的决策： │ │ - 值大 → 优化器更倾向于使用索引 │ │ - 值小 → 优化器更倾向于顺序扫描 │ │ │ │ 设置建议： │ │ 1. 保守：总内存的 50% │ │ 2. 激进：总内存的 75%-80%（推荐） │ │ 3. 不设太大 → 不会更慢，只是可能错过索引 │ │ │ └─────────────────────────────────────────────────────────────┘

SHOWeffective_cache_size;-- 默认 4GB-- 设置为总内存的 75%ALTERSYSTEMSETeffective_cache_size='12GB';-- 假设总内存 16GBSELECTpg_reload_conf();

2.3 work_mem——排序和哈希操作的内存

work_mem 的作用： ┌─────────────────────────────────────────────────────────────┐ │ │ │ 每个排序/哈希操作能使用的内存 │ │ │ │ 注意：不是每个连接！而是每个操作！ │ │ 一个复杂查询可能有多个排序/哈希操作 │ │ 所以如果设置 64MB，复杂查询可能用 64MB × N │ │ │ │ 设置过低 → 使用临时文件（磁盘 I/O） │ │ 设置过高 → 高并发时内存耗尽 │ │ │ │ 设置建议： │ │ 1. work_mem × max_connections < 总内存 × 25% │ │ 2. 典型值：4MB-64MB（根据并发连接数调整） │ │ 3. 可以对特定大查询单独设置（会话级）： │ │ SET LOCAL work_mem = '256MB'; │ │ │ └─────────────────────────────────────────────────────────────┘

SHOWwork_mem;-- 默认 4MB-- 适当增加ALTERSYSTEMSETwork_mem='16MB';SELECTpg_reload_conf();-- 对特定大查询设置BEGIN;SETLOCALwork_mem='256MB';-- 执行需要大量排序的查询SELECT...ORDERBY...LIMIT...;COMMIT;-- LOCAL 设置随事务结束自动还原

2.4 maintenance_work_mem——维护操作的内存

maintenance_work_mem 的作用： ┌─────────────────────────────────────────────────────────────┐ │ │ │ 维护操作的内存： │ │ - VACUUM │ │ - VACUUM FULL │ │ - CREATE INDEX │ │ - ALTER TABLE ... ADD FOREIGN KEY │ │ - CREATE TABLE ... AS SELECT ... │ │ │ │ 设置建议： │ │ 1. 越大越好（维护操作不频繁，但每个操作是单线程的） │ │ 2. 建议值：256MB - 1GB │ │ 3. autovacuum_work_mem 优先使用这个设置 │ │ │ └─────────────────────────────────────────────────────────────┘

SHOWmaintenance_work_mem;-- 默认 64MBALTERSYSTEMSETmaintenance_work_mem='512MB';SELECTpg_reload_conf();

2.5 temp_buffers——临时表缓存

SHOWtemp_buffers;-- 默认 8MB-- 使用临时表较多的场景可以适当增大ALTERSYSTEMSETtemp_buffers='32MB';-- 需要在每个会话中设置才生效（不能在全局生效）-- 或在会话中设置：SETtemp_buffers='32MB';

三、Huge Pages（大页内存）优化

3.1 为什么需要 Huge Pages？

内存分页原理： ┌─────────────────────────────────────────────────────────────┐ │ │ │ 操作系统使用"页"管理内存（默认 4KB 一页） │ │ │ │ 问题： │ │ 1. shared_buffers = 8GB │ │ 需要 8GB / 4KB = 2,097,152 个页表项 │ │ 2. TLB（Translation Lookaside Buffer） │ │ CPU 缓存页表项，大小有限（通常 512-4096 项） │ │ 3. TLB Miss → 需要去内存查页表 → 性能下降 │ │ │ │ 解决方案：Huge Pages（2MB 一页） │ │ 8GB / 2MB = 4,096 个页表项（减少 512 倍！） │ │ │ └─────────────────────────────────────────────────────────────┘

3.2 配置 Huge Pages

# 1. 查看当前 Huge Pages 状态cat/proc/meminfo|grep-ihuge# 输出示例：# AnonHugePages: XXXXX kB （透明大页，需要关闭！）# ShmemHugePages: 0 kB# HugePages_Total: 0 （当前没有配置）# HugePages_Free: 0# HugePages_Rsvd: 0# HugePages_Surp: 0# Hugepagesize: 2048 kB （每页 2MB）# 2. 计算需要的 Huge Pages 数量# 假设 shared_buffers = 8GB# 8GB = 8192 MB# 8192 MB / 2 MB = 4096 页# 再加 10% 余量：4500 页# 3. 修改 /etc/sysctl.confcat>>/etc/sysctl.conf<<EOF vm.nr_hugepages = 4500 EOFsysctl-p# 4. 验证cat/proc/meminfo|grepHugePages_# HugePages_Total: 4500# HugePages_Free: 4500 （说明还没使用）# HugePages_Rsvd: 0# 5. 配置 PostgreSQL 使用 Huge Pages# postgresql.confhuge_pages=on# try 也可以（能开就开，不能开也不报错）# 6. 重启 PostgreSQLpg_ctl restart# 7. 验证 PostgreSQL 使用了 Huge Pagescat/proc/meminfo|grepHugePages_Free# HugePages_Free 应该减少了（说明被使用了）

3.3 透明大页（THP）必须关闭！

# ❌ 透明大页对数据库性能有害！cat/sys/kernel/mm/transparent_hugepage/enabled# [always] madvise never ← always 是有害的！# 关闭透明大页echonever>/sys/kernel/mm/transparent_hugepage/enabled# 永久关闭（/etc/rc.local）echo"echo never > /sys/kernel/mm/transparent_hugepage/enabled">>/etc/rc.localchmod+x /etc/rc.local# 或在 grub 中添加：# transparent_hugepage=never

四、内存计算工具——pgtune

4.1 使用 pgtune 自动计算

# 安装 pgtuneapt-getinstallpgtune# Debian/Ubuntuyuminstallpgtune# RHEL/CentOS# 自动生成优化后的配置pgtune-i/etc/postgresql/14/main/postgresql.conf\-o/etc/postgresql/14/main/postgresql.conf.pgtune\--type=OLTP\--memory=16GB\--connections=200# 查看生成的配置cat/etc/postgresql/14/main/postgresql.conf.pgtune

4.2 使用 pgconfig 在线工具

pgconfig 在线工具： https://pgconfig.org/ 输入： - 总内存 - 连接数 - 存储类型（SSD/HDD） - 应用场景（OLTP/OLAP/Web/DW） 输出：优化的 postgresql.conf 参数

五、内存监控实战

5.1 查看 PostgreSQL 内存使用

-- 1. 查看 shared_buffers 使用率SELECTcount(*)FILTER(WHEREisdirty='f')ASclean_pages,count(*)FILTER(WHEREisdirty='t')ASdirty_pages,count(*)AStotal_pages,round(100.0*count(*)FILTER(WHEREisdirty='f')/count(*),2)ASclean_pctFROMpg_buffercache;-- 2. 估算 shared_buffers 中的数据分布SELECTc.relname,count(*)ASpages,round(count(*)*8192.0/1024/1024,2)ASsize_mbFROMpg_buffercache bJOINpg_class cONb.relfilenode=c.relfilenodeGROUPBYc.relnameORDERBYpagesDESCLIMIT20;

5.2 操作系统层面监控

# 1. 查看 PostgreSQL 进程内存使用psaux|grep"postgres:"|awk'{sum+=$6} END {print sum/1024" MB"}'# 2. 查看 shared memory 段ipcs-m# 3. 查看 Huge Pages 使用grepHuge /proc/meminfo# 4. 监控内存使用趋势watch-n1'free -h'

六、内存配置 Checklist

内存配置 Checklist： ┌─────────────────────────────────────────────────────────────┐ │ □ 总内存是否 > 2GB？（PostgreSQL 需要足够内存） │ │ □ shared_buffers 是否设置为总内存的 25%-40%？ │ │ □ effective_cache_size 是否设置为总内存的 50%-75%？ │ │ □ work_mem 是否根据 max_connections 合理设置？ │ │ □ maintenance_work_mem 是否设置为 256MB-1GB？ │ │ □ huge_pages 是否设置为 on（并且 nr_hugepages 已配置）？ │ │ □ 透明大页（THP）是否已关闭？ │ │ □ vm.swappiness 是否设置为 1（避免使用 swap）？ │ │ □ 操作系统总内存是否足够（保留至少 20% 给 OS）？ │ └─────────────────────────────────────────────────────────────┘

七、常见问题排查

Q1：内存使用远超 shared_buffers 设置？

正常现象！PostgreSQL 内存使用 = shared_buffers + work_mem × 当前排序操作数 + temp_buffers × 使用临时表的连接数 + 每个连接固定开销（约 2-5MB）+ OS Cache（文件缓存）

Q2：如何判断 shared_buffers 是否足够？

-- 查看缓存命中率SELECTsum(heap_blk_read)ASheap_read,sum(heap_blk_hit)ASheap_hit,round(100.0*sum(heap_blk_hit)/(sum(heap_blk_hit)+sum(heap_blk_read)),2)AShit_rate_pctFROMpg_statio_user_tables;-- 命中率低于 95% → 考虑增大 shared_buffers-- 但要注意：OS Cache 也能缓存数据文件-- 所以即使 shared_buffers 命中率低，OS Cache 命中率高也 OK

Q3：OOM（Out of Memory）怎么办？

# 1. 查看系统日志dmesg|grep-i"out of memory"# 或grep-i"out of memory"/var/log/messages# 2. 减小 work_mem（防止并发排序耗尽内存）# 3. 减小 max_connections（使用连接池）# 4. 增大 vm.overcommit_memory = 2（限制内存分配）# 5. 设置 vm.overcommit_ratio = 80（只允许分配 80% 内存）