一、为什么PostgreSQL也会"吃内存"？
相信很多DBA都遇到过这样的情况：明明服务器配置不错，PostgreSQL运行一段时间后突然就OOM（Out Of Memory）了。这就像你买了个大房子，结果住着住着发现东西越堆越多，最后连下脚的地方都没了。
PostgreSQL的内存使用主要分为几个"大户"：
- shared_buffers：这是数据库的共享内存缓冲区
- work_mem：每个查询操作可以使用的内存
- maintenance_work_mem：维护操作使用的内存
- temp_buffers：临时表使用的缓冲区
举个例子，假设我们有个查询：
-- 这个排序操作可能会占用大量work_mem
SELECT * FROM large_table ORDER BY create_time DESC;
如果large_table有1000万条记录，而work_mem设置过大，几十个这样的并发查询就能把内存吃光。
二、内存配置参数详解
2.1 shared_buffers的黄金比例
shared_buffers相当于数据库的"缓存区"，建议设置为总内存的25%-40%。比如64GB内存的服务器：
-- 在postgresql.conf中设置
shared_buffers = 16GB -- 64GB内存的25%
但要注意，Linux系统对共享内存段大小有限制，可能需要调整内核参数：
# 查看当前共享内存段限制
$ cat /proc/sys/kernel/shmmax
2.2 work_mem的精细控制
work_mem控制每个查询操作使用的内存量。设置太小影响性能，太大容易OOM。建议：
-- 针对不同业务设置不同值
work_mem = 8MB -- 默认值
SET LOCAL work_mem = '64MB'; -- 对大查询临时增加
这里有个实用技巧：通过pg_stat_statements找出内存消耗大的查询：
SELECT query, calls, total_time, shared_blks_hit
FROM pg_stat_statements
ORDER BY shared_blks_hit DESC LIMIT 10;
三、实战：大并发下的内存优化
3.1 连接池管理
每个PostgreSQL连接都会占用内存，连接数太多直接导致OOM。解决方案是使用连接池，比如PgBouncer：
# pgbouncer.ini配置示例
[databases]
mydb = host=127.0.0.1 port=5432 dbname=mydb
[pgbouncer]
pool_mode = transaction # 事务级连接池
max_client_conn = 1000 # 允许的最大客户端连接数
default_pool_size = 50 # 每个数据库的默认连接池大小
3.2 查询优化案例
看一个实际案例：某电商平台大促时出现OOM，分析发现是商品搜索查询导致的：
-- 问题查询：多表JOIN+排序
SELECT p.*, c.category_name
FROM products p
JOIN categories c ON p.category_id = c.id
WHERE p.status = 'active'
ORDER BY p.price DESC
LIMIT 100;
优化方案：
- 创建覆盖索引
CREATE INDEX idx_products_active_price ON products(status, price DESC)
WHERE status = 'active';
- 重写查询避免全表排序
SELECT p.*, c.category_name
FROM (SELECT * FROM products
WHERE status = 'active'
ORDER BY price DESC LIMIT 100) p
JOIN categories c ON p.category_id = c.id;
四、高级技巧与监控预警
4.1 使用扩展监控内存
安装pg_top插件可以实时监控内存使用：
CREATE EXTENSION pg_top;
SELECT * FROM pg_top ORDER BY mem_usage DESC LIMIT 10;
4.2 自动内存调整脚本
编写一个Shell脚本自动调整work_mem：
#!/bin/bash
# 根据当前负载自动调整work_mem
LOAD=$(uptime | awk '{print $NF}')
CONNS=$(psql -U postgres -c "SELECT count(*) FROM pg_stat_activity" -t)
if [ $(echo "$LOAD > 5" | bc) -eq 1 ] && [ $CONNS -gt 50 ]; then
psql -U postgres -c "ALTER SYSTEM SET work_mem = '4MB'"
psql -U postgres -c "SELECT pg_reload_conf()"
fi
4.3 预防OOM的内核参数
在Linux系统中设置：
# 防止PostgreSQL被OOM killer优先杀死
echo -17 > /proc/$(pgrep postmaster)/oom_adj
# 调整系统overcommit设置
sysctl -w vm.overcommit_memory=2
sysctl -w vm.overcommit_ratio=80
五、总结与最佳实践
经过上面的分析和优化，我们可以总结出PostgreSQL内存优化的"三要三不要"原则：
三要：
- 要合理设置shared_buffers（25%-40%总内存）
- 要使用连接池控制连接数
- 要对大查询单独设置work_mem
三不要：
- 不要盲目增加work_mem
- 不要允许无限连接
- 不要忽视长期运行的查询
最后记住，数据库优化是个持续的过程，需要结合监控数据不断调整。就像照顾一个花园，需要定期修剪才能长得更好。
评论