PostgreSQL教程FG067-PG 存储参数：shared_buffers 与 work_mem 配置优化
本文档详细介绍PostgreSQL中shared_buffers和work_mem参数的配置与优化，风哥教程参考PostgreSQL官方文档内容，适合数据库管理员在生产环境中进行内存参数调优，提高数据库性能。
Part01-基础概念与理论知识
1.1 shared_buffers 参数概述
shared_buffers是PostgreSQL中最重要的内存参数之一，用于设置PostgreSQL服务器用于缓存数据页的内存量。这些缓冲区由所有PostgreSQL进程共享，用于存储从磁盘读取的数据页和要写入磁盘的数据页。更多视频教程www.fgedu.net.cn
- 数据缓存：缓存频繁访问的数据页，减少磁盘I/O
- 写缓冲：缓冲待写入磁盘的数据，提高写入性能
- 共享访问：所有PostgreSQL进程共享这些缓冲区
1.2 work_mem 参数概述
work_mem参数用于设置PostgreSQL执行排序、哈希等操作时使用的内存量。每个操作（如排序、哈希连接）都会使用不超过这个值的内存。如果内存不足，PostgreSQL会使用临时磁盘文件。
- 排序操作：用于ORDER BY、DISTINCT、GROUP BY等操作
- 哈希操作：用于哈希连接、哈希聚合等操作
- 位图操作：用于位图索引扫描
1.3 PostgreSQL内存架构
PostgreSQL的内存架构主要包括以下几个部分：
- shared_buffers：共享缓冲区，用于缓存数据页
- work_mem：工作内存，用于排序、哈希等操作
- maintenance_work_mem：维护工作内存，用于VACUUM等维护操作
- wal_buffers：WAL缓冲区，用于缓存WAL日志
- shared_memory：其他共享内存，如锁表、统计信息等
- 进程内存：每个后端进程的私有内存
Part02-生产环境规划与建议
2.1 shared_buffers 配置建议
— 一般建议：
— – 对于较小的服务器（内存小于4GB）：设置为内存的25%
— – 对于较大的服务器（内存大于4GB）：设置为内存的25-40%
— – 最大不建议超过内存的50%
— 具体配置示例：
— 4GB内存服务器
shared_buffers = 1GB
— 8GB内存服务器
shared_buffers = 2GB
— 16GB内存服务器
shared_buffers = 4GB
— 32GB内存服务器
shared_buffers = 8GB
— 64GB内存服务器
shared_buffers = 16GB
— 128GB内存服务器
shared_buffers = 32GB
2.2 work_mem 配置建议
— 计算方法：
— work_mem = (系统总内存 × 0.25) / (最大并发连接数 × 2)
— 其中0.25是分配给work_mem的内存比例，2是考虑每个连接可能有多个操作
— 具体配置示例：
— 4GB内存，最大连接数100
work_mem = 5MB — (4GB × 0.25) / (100 × 2) = 1GB / 200 = 5MB
— 8GB内存，最大连接数100
work_mem = 10MB — (8GB × 0.25) / (100 × 2) = 2GB / 200 = 10MB
— 16GB内存，最大连接数100
work_mem = 20MB — (16GB × 0.25) / (100 × 2) = 4GB / 200 = 20MB
— 32GB内存，最大连接数100
work_mem = 40MB — (32GB × 0.25) / (100 × 2) = 8GB / 200 = 40MB
— 64GB内存，最大连接数100
work_mem = 80MB — (64GB × 0.25) / (100 × 2) = 16GB / 200 = 80MB
— 注意：work_mem设置过大可能导致内存不足，设置过小可能导致大量临时文件使用
2.3 内存分配策略
内存分配策略：
- 系统预留：预留20-30%的内存给操作系统和其他应用
- shared_buffers：分配25-40%的内存
- work_mem：分配20-25%的内存，根据最大并发连接数计算
- maintenance_work_mem：分配5-10%的内存，最大不超过1GB
- 其他内存：剩余内存用于其他PostgreSQL内存需求
Part03-生产环境项目实施方案
3.1 shared_buffers 配置方法
— 1. 修改postgresql.conf文件
$ vi /postgresql/fgdata/postgresql.conf
— 设置shared_buffers
shared_buffers = 4GB
— 2. 重启PostgreSQL服务
$ systemctl restart postgresql-18
— 3. 验证配置
$ psql -h 192.168.1.100 -p 5432 -U fgedu -d fgedudb -c “SHOW shared_buffers;”
— 输出：
— shared_buffers
— —————-
— 4GB
— (1 row)
— 4. 检查共享内存设置
$ ipcs -m
— 5. 调整系统共享内存限制（如果需要）
— 修改/etc/sysctl.conf文件
$ vi /etc/sysctl.conf
— 添加以下设置
kernel.shmmax = 4294967296 — 4GB
kernel.shmall = 1048576 — 4GB / 4KB
— 应用设置
$ sysctl -p
3.2 work_mem 配置方法
— 1. 修改postgresql.conf文件
$ vi /postgresql/fgdata/postgresql.conf
— 设置work_mem
work_mem = 20MB
— 2. 重新加载配置（不需要重启）
$ systemctl reload postgresql-18
— 3. 验证配置
$ psql -h 192.168.1.100 -p 5432 -U fgedu -d fgedudb -c “SHOW work_mem;”
— 输出：
— work_mem
— ———-
— 20MB
— (1 row)
— 4. 临时修改（仅当前会话有效）
$ psql -h 192.168.1.100 -p 5432 -U fgedu -d fgedudb
fgedudb=# SET work_mem = ’32MB’;
fgedudb=# SHOW work_mem;
— 输出：
— work_mem
— ———-
— 32MB
— (1 row)
3.3 内存监控与调优
— 1. 监控shared_buffers使用情况
SELECT
name,
setting,
unit,
context,
source
FROM pg_settings
WHERE name IN (‘shared_buffers’, ‘work_mem’, ‘maintenance_work_mem’, ‘wal_buffers’);
— 2. 监控缓冲区使用情况
SELECT
buffers_clean,
buffers_backend,
buffers_backend_fsync,
buffers_alloc,
stats_reset
FROM pg_stat_database
WHERE datname = ‘fgedudb’;
— 3. 监控临时文件使用情况（work_mem不足的指标）
SELECT
temp_files,
temp_bytes,
stats_reset
FROM pg_stat_database
WHERE datname = ‘fgedudb’;
— 4. 监控排序操作
SELECT
sum(blk_read_time) AS total_blk_read_time,
sum(blk_write_time) AS total_blk_write_time,
sum(sort_mem) AS total_sort_mem,
sum(sort_rows) AS total_sort_rows
FROM pg_stat_user_tables;
— 5. 监控哈希操作
SELECT
queryid,
query,
mean_hash_buckets_used,
mean_hash_ops,
mean_hash_ops_per_bucket
FROM pg_stat_statements
WHERE query LIKE ‘%JOIN%’ OR query LIKE ‘%GROUP BY%’
ORDER BY mean_hash_ops DESC
LIMIT 10;
Part04-生产案例与实战讲解
4.1 shared_buffers 配置案例
— 系统配置：
— – 内存：32GB
— – CPU：8核
— – 存储：SSD
— – 数据库：OLTP系统，高并发，小事务
— 配置步骤：
— 1. 修改postgresql.conf文件
$ vi /postgresql/fgdata/postgresql.conf
— 设置shared_buffers为内存的25%
shared_buffers = 8GB
— 设置其他相关参数
work_mem = 40MB
maintenance_work_mem = 1GB
wal_buffers = 16MB
— 2. 调整系统共享内存限制
$ vi /etc/sysctl.conf
— 添加以下设置
kernel.shmmax = 8589934592 — 8GB
kernel.shmall = 2097152 — 8GB / 4KB
— 应用设置
$ sysctl -p
— 3. 重启PostgreSQL服务
$ systemctl restart postgresql-18
— 4. 验证配置
$ psql -h 192.168.1.100 -p 5432 -U fgedu -d fgedudb -c “SHOW shared_buffers;”
— 输出：
— shared_buffers
— —————-
— 8GB
— (1 row)
— 5. 监控性能
$ psql -h 192.168.1.100 -p 5432 -U fgedu -d fgedudb -c “SELECT * FROM pg_stat_database WHERE datname = ‘fgedudb’;”
— 6. 测试性能
— 使用pgbench进行基准测试
$ pgbench -h 192.168.1.100 -p 5432 -U fgedu -d fgedudb -i -s 10
$ pgbench -h 192.168.1.100 -p 5432 -U fgedu -d fgedudb -c 50 -j 8 -T 60
4.2 work_mem 配置案例
— 系统配置：
— – 内存：64GB
— – CPU：16核
— – 存储：SSD
— – 数据库：OLAP系统，复杂查询，大表连接
— 配置步骤：
— 1. 分析工作负载
— 查看当前临时文件使用情况
$ psql -h 192.168.1.100 -p 5432 -U fgedu -d fgedudb -c “SELECT temp_files, temp_bytes FROM pg_stat_database WHERE datname = ‘fgedudb’;”
— 2. 修改postgresql.conf文件
$ vi /postgresql/fgdata/postgresql.conf
— 设置work_mem
— 计算方法：(64GB × 0.25) / (50 × 2) = 16GB / 100 = 160MB
work_mem = 160MB
— 设置其他相关参数
shared_buffers = 16GB
maintenance_work_mem = 1GB
— 3. 重新加载配置
$ systemctl reload postgresql-18
— 4. 验证配置
$ psql -h 192.168.1.100 -p 5432 -U fgedu -d fgedudb -c “SHOW work_mem;”
— 输出：
— work_mem
— ———-
— 160MB
— (1 row)
— 5. 测试复杂查询
$ psql -h 192.168.1.100 -p 5432 -U fgedu -d fgedudb
fgedudb=# EXPLAIN ANALYZE
fgedudb-# SELECT
fgedudb-# p.category_id,
fgedudb-# c.name AS category_name,
fgedudb-# COUNT(*) AS product_count,
fgedudb-# SUM(p.price) AS total_price
fgedudb-# FROM
fgedudb-# fgedu_products p
fgedudb-# JOIN
fgedudb-# fgedu_categories c ON p.category_id = c.id
fgedudb-# GROUP BY
fgedudb-# p.category_id, c.name
fgedudb-# ORDER BY
fgedudb-# product_count DESC;
— 6. 监控临时文件使用情况
$ psql -h 192.168.1.100 -p 5432 -U fgedu -d fgedudb -c “SELECT temp_files, temp_bytes FROM pg_stat_database WHERE datname = ‘fgedudb’;”
4.3 内存调优综合案例
— 系统配置：
— – 内存：128GB
— – CPU：32核
— – 存储：NVMe SSD
— – 数据库：混合工作负载，既有OLTP又有OLAP
— 配置步骤：
— 1. 内存分配规划
— 系统预留：128GB × 0.2 = 25.6GB
— shared_buffers：128GB × 0.3 = 38.4GB
— work_mem：128GB × 0.25 = 32GB
— maintenance_work_mem：128GB × 0.05 = 6.4GB（最大1GB）
— 其他内存：剩余
— 2. 修改postgresql.conf文件
$ vi /postgresql/fgdata/postgresql.conf
— 设置内存参数
shared_buffers = 38GB
work_mem = 160MB — (32GB) / (100 × 2) = 160MB
maintenance_work_mem = 1GB
wal_buffers = 16MB
— 设置连接数
max_connections = 100
— 设置其他性能参数
effective_cache_size = 96GB — 75% of total memory
random_page_cost = 1.1 — SSD存储
seq_page_cost = 1.0
— 3. 调整系统参数
$ vi /etc/sysctl.conf
— 添加以下设置
kernel.shmmax = 41006540800 — 38GB
kernel.shmall = 9961472 — 38GB / 4KB
vm.swappiness = 10
vm.overcommit_memory = 2
vm.overcommit_ratio = 90
— 应用设置
$ sysctl -p
— 4. 重启PostgreSQL服务
$ systemctl restart postgresql-18
— 5. 验证配置
$ psql -h 192.168.1.100 -p 5432 -U fgedu -d fgedudb -c “SHOW shared_buffers, work_mem, maintenance_work_mem;”
— 输出：
— shared_buffers | work_mem | maintenance_work_mem
— —————-+———-+———————-
— 38GB | 160MB | 1GB
— (1 row)
— 6. 监控性能
— 监控缓冲区使用情况
$ psql -h 192.168.1.100 -p 5432 -U fgedu -d fgedudb -c “SELECT * FROM pg_stat_database WHERE datname = ‘fgedudb’;”
— 监控临时文件使用情况
$ psql -h 192.168.1.100 -p 5432 -U fgedu -d fgedudb -c “SELECT temp_files, temp_bytes FROM pg_stat_database WHERE datname = ‘fgedudb’;”
— 监控排序和哈希操作
$ psql -h 192.168.1.100 -p 5432 -U fgedu -d fgedudb -c “SELECT * FROM pg_stat_statements ORDER BY mean_exec_time DESC LIMIT 10;”
— 7. 性能测试
— OLTP测试
$ pgbench -h 192.168.1.100 -p 5432 -U fgedu -d fgedudb -c 50 -j 16 -T 60
— OLAP测试
$ psql -h 192.168.1.100 -p 5432 -U fgedu -d fgedudb -f complex_query.sql
Part05-风哥经验总结与分享
5.1 内存调优技巧
内存调优技巧：
- shared_buffers 调优：
- 根据系统内存大小调整，一般为内存的25-40%
- 对于SSD存储，可以适当增加shared_buffers
- 对于HDD存储，shared_buffers不宜过大，建议不超过内存的25%
- 确保系统有足够的内存用于操作系统缓存
- work_mem 调优：
- 根据最大并发连接数和系统内存计算
- 对于OLAP系统，可以适当增加work_mem
- 对于OLTP系统，work_mem不宜过大
- 监控临时文件使用情况，调整work_mem大小
- 其他内存参数调优：
- maintenance_work_mem：设置为5-10%的内存，最大不超过1GB
- effective_cache_size：设置为75%的系统内存
- wal_buffers：一般设置为shared_buffers的1-2%
5.2 内存问题排查
— 1. 内存不足问题
— 症状：
— – PostgreSQL进程被OOM killer杀死
— – 系统 swap 使用量高
— – 查询执行缓慢
— 排查步骤：
— – 检查系统内存使用情况
$ free -h
— – 检查PostgreSQL内存使用情况
$ ps aux | grep postgres | sort -rn -k 4 | head -10
— – 检查work_mem设置是否过大
$ psql -h 192.168.1.100 -p 5432 -U fgedu -d fgedudb -c “SHOW work_mem;”
— – 检查最大连接数
$ psql -h 192.168.1.100 -p 5432 -U fgedu -d fgedudb -c “SHOW max_connections;”
— 2. 临时文件使用过多问题
— 症状：
— – 查询执行缓慢
— – 临时目录空间使用高
— – pg_stat_database中temp_files和temp_bytes值高
— 排查步骤：
— – 检查临时文件使用情况
$ psql -h 192.168.1.100 -p 5432 -U fgedu -d fgedudb -c “SELECT temp_files, temp_bytes FROM pg_stat_database WHERE datname = ‘fgedudb’;”
— – 检查work_mem设置
$ psql -h 192.168.1.100 -p 5432 -U fgedu -d fgedudb -c “SHOW work_mem;”
— – 分析慢查询
$ psql -h 192.168.1.100 -p 5432 -U fgedu -d fgedudb -c “SELECT * FROM pg_stat_statements WHERE temp_blks_written > 0 ORDER BY temp_blks_written DESC LIMIT 10;”
— 3. 共享内存不足问题
— 症状：
— – PostgreSQL启动失败
— – 错误信息：”could not create shared memory segment”
— 排查步骤：
— – 检查系统共享内存限制
$ ipcs -lm
— – 检查shared_buffers设置
$ psql -h 192.168.1.100 -p 5432 -U fgedu -d fgedudb -c “SHOW shared_buffers;”
— – 调整系统共享内存限制
$ vi /etc/sysctl.conf
— 添加或修改kernel.shmmax和kernel.shmall
5.3 内存管理最佳实践
— 1. 监控内存使用
— – 定期监控系统内存使用情况
— – 监控PostgreSQL缓冲区使用情况
— – 监控临时文件使用情况
— – 监控慢查询的内存使用
— 2. 调整内存参数
— – 根据实际负载调整shared_buffers
— – 根据并发连接数调整work_mem
— – 根据维护操作需求调整maintenance_work_mem
— – 定期重新评估内存配置
— 3. 系统配置优化
— – 调整系统共享内存限制
— – 配置适当的swappiness值
— – 启用大内存页
— – 关闭透明大页
— 4. 查询优化
— – 优化复杂查询，减少排序和哈希操作
— – 创建合适的索引，减少排序需求
— – 使用物化视图，减少重复计算
— – 限制结果集大小，避免大结果集排序
— 5. 硬件优化
— – 使用足够的内存
— – 使用高速存储（SSD/NVMe）
— – 配置适当的CPU核心数
— – 考虑使用内存数据库（如TimescaleDB）处理时间序列数据
本文由风哥教程整理发布,仅用于学习测试使用,转载注明出处:http://www.fgedu.net.cn/10327.html
联系我们
在线咨询：
微信号：itpux-com
工作日：9:30-18:30，节假日休息
