# 32GB内存服务器优化配置

# 查看当前内存参数
postgres=# SELECT name, setting, unit, short_desc
FROM pg_settings
WHERE name IN (‘shared_buffers’, ‘effective_cache_size’, ‘work_mem’, ‘maintenance_work_mem’);
name | setting | unit | short_desc
———————-+———+——+——————————————
effective_cache_size | 524288 | 8kB | Planner’s assumption about the size of t
maintenance_work_mem | 1048576 | kB | Sets the maximum memory to be used for m
shared_buffers | 1048576 | 8kB | Sets the number of shared memory buffers
work_mem | 65536 | kB | Sets the maximum memory to be used for q
(4 行记录)

# 优化内存参数
postgres=# ALTER SYSTEM SET shared_buffers = ‘8GB’;
ALTER SYSTEM

postgres=# ALTER SYSTEM SET effective_cache_size = ’24GB’;
ALTER SYSTEM

postgres=# ALTER SYSTEM SET work_mem = ’64MB’;
ALTER SYSTEM

postgres=# ALTER SYSTEM SET maintenance_work_mem = ‘1GB’;
ALTER SYSTEM

# 配置大页内存
# 首先在操作系统层面配置
$ cat /proc/meminfo | grep Huge
AnonHugePages: 0 kB
ShmemHugePages: 0 kB
FileHugePages: 0 kB
HugePages_Total: 5120
HugePages_Free: 5120
HugePages_Rsvd: 0 kB
HugePages_Surp: 0 kB
Hugepagesize: 2048 kB

# 在PostgreSQL中启用大页
postgres=# ALTER SYSTEM SET huge_pages = ‘try’;
ALTER SYSTEM

# 重启数据库使shared_buffers生效
$ pg_ctl restart -D /postgresql/data

# 查询优化参数配置

# 查看当前查询优化参数
postgres=# SELECT name, setting, short_desc
FROM pg_settings
WHERE name LIKE ‘%cost%’ OR name LIKE ‘%enable%’ OR name LIKE ‘%statistics%’;
name | setting | short_desc
—————————–+———+———————————————————-
cpu_index_tuple_cost | 0.005 | Sets the planner’s estimate of the cost of processing ea
cpu_operator_cost | 0.0025 | Sets the planner’s estimate of the cost of processing ea
cpu_tuple_cost | 0.01 | Sets the planner’s estimate of the cost of processing ea
default_statistics_target | 100 | Sets the default statistics target.
effective_cache_size | 3145728 | Sets the planner’s assumption about the size of the data
enable_bitmapscan | on | Enables the planner’s use of bitmap-scan plans.
enable_gathermerge | on | Enables the planner’s use of gather merge plans.
enable_hashagg | on | Enables the planner’s use of hashed aggregation plans.
enable_hashjoin | on | Enables the planner’s use of hash-join plans.
enable_indexonlyscan | on | Enables the planner’s use of index-only-scan plans.
enable_indexscan | on | Enables the planner’s use of index-scan plans.
enable_material | on | Enables the planner’s use of materialization.
enable_memoize | on | Enables the planner’s use of memoization.
enable_mergejoin | on | Enables the planner’s use of merge-join plans.
enable_nestloop | on | Enables the planner’s use of nested-loop join plans.
enable_parallel_fgappend | on | Enables the planner’s use of parallel fgappend plans.
enable_parallel_hash | on | Enables the planner’s use of parallel hash plans.
enable_partition_pruning | on | Enables plan time partition pruning.
enable_partitionwise_join | off | Enables partitionwise join.
enable_partitionwise_aggreg | off | Enables partitionwise aggregation.
enable_seqscan | on | Enables the planner’s use of sequential-scan plans.
enable_sort | on | Enables the planner’s use of explicit sort steps.
enable_tidscan | on | Enables the planner’s use of TID scan plans.
parallel_setup_cost | 1000 | Sets the planner’s estimate of the cost of starting up wo
parallel_tuple_cost | 0.1 | Sets the planner’s estimate of the cost of passing each t
random_page_cost | 4 | Sets the planner’s estimate of the cost of a non sequenti
(26 行记录)

# SSD存储优化配置
postgres=# ALTER SYSTEM SET random_page_cost = 1.1;
ALTER SYSTEM

postgres=# ALTER SYSTEM SET effective_io_concurrency = 200;
ALTER SYSTEM

postgres=# ALTER SYSTEM SET default_statistics_target = 200;
ALTER SYSTEM

# 重新加载配置
postgres=# SELECT pg_reload_conf();
pg_reload_conf
—————-
t
(1 行记录)

# 问题现象：修改参数后不生效
postgres=# SHOW work_mem;
work_mem
———-
4MB
(1 行记录)

# 检查参数来源
postgres=# SELECT name, setting, source, sourcefile, sourceline
FROM pg_settings WHERE name = ‘work_mem’;
name | setting | source | sourcefile | sourceline
———-+———+——————–+——————————–+————
work_mem | 4096 | configuration file | /postgresql/data/postgresql.co | 156
(1 行记录)

# 原因分析：修改了postgresql.auto.conf但postgresql.conf中也有设置
# 解决方案：检查配置优先级

# 查看postgresql.auto.conf
$ cat /postgresql/data/postgresql.auto.conf
# Do not edit this file manually!
work_mem = ’64MB’

# 查看postgresql.conf中的设置
$ grep work_mem /postgresql/data/postgresql.conf
work_mem = 4MB

# 解决方案1：注释postgresql.conf中的设置
$ sed -i ‘s/^work_mem/#work_mem/’ /postgresql/data/postgresql.conf

# 解决方案2：直接修改postgresql.conf
$ sed -i ‘s/work_mem = 4MB/work_mem = 64MB/’ /postgresql/data/postgresql.conf

# 重新加载配置
$ psql -U pgsql -c “SELECT pg_reload_conf();”

# 问题现象：启动数据库时报内存不足
$ pg_ctl start -D /postgresql/data
waiting for server to start…. stopped waiting
pg_ctl: could not start server

# 查看日志
$ cat /postgresql/data/log/postgresql-2026-04-02.log
2026-04-02 12:00:00.123 CST [12345] FATAL: could not map anonymous shared memory: Cannot allocate memory
2026-04-02 12:00:00.123 CST [12345] HINT: This error usually means that PostgreSQL’s request for a shared memory segment exceeded available memory, swap space, or huge pages. To reduce the request size (currently 8589934592 bytes), reduce PostgreSQL’s shared memory usage, perhaps by reducing shared_buffers or max_connections.

# 原因分析：shared_buffers设置过大，系统内存不足

# 解决方案1：减少shared_buffers
$ vi /postgresql/data/postgresql.conf
shared_buffers = 4GB

# 解决方案2：增加系统内存或交换分区
$ free -h
总计 已用 空闲 共享 缓冲/缓存 可用
内存： 15Gi 2.1Gi 12Gi 256Mi 1.2Gi 12Gi
交换： 8Gi 0B 8Gi

# 解决方案3：调整大页内存配置
$ cat /proc/sys/vm/nr_hugepages
0

$ sudo sysctl -w vm.nr_hugepages=4096
vm.nr_hugepages = 4096

# 重新启动数据库
$ pg_ctl start -D /postgresql/data
server started

# 创建配置审计脚本
$ cat > /postgresql/scripts/pg_config_audit.sql << 'EOF' -- pg_config_audit.sql -- from:www.itpux.com.qq113257174.wx:itpux-com -- web: http://www.fgedu.net.cn -- 查看非默认参数设置 SELECT name, setting, unit, source, short_desc FROM pg_settings WHERE source != 'default' ORDER BY name; -- 查看内存相关参数 SELECT name, setting, unit, context, short_desc FROM pg_settings WHERE name IN ( 'shared_buffers', 'effective_cache_size', 'work_mem', 'maintenance_work_mem', 'huge_pages', 'temp_buffers' ); -- 查看连接相关参数 SELECT name, setting, unit, context, short_desc FROM pg_settings WHERE name IN ( 'max_connections', 'superfgedu_reserved_connections', 'listen_addresses', 'port' ); -- 查看WAL相关参数 SELECT name, setting, unit, context, short_desc FROM pg_settings WHERE name LIKE '%wal%' OR name LIKE '%checkpoint%'; -- 查看日志相关参数 SELECT name, setting, unit, context, short_desc FROM pg_settings WHERE name LIKE '%log%'; EOF # 执行审计脚本 $ psql -U pgsql -f /postgresql/scripts/pg_config_audit.sql name | setting | unit | context | short_desc -----------------+---------+------+------------+------------------------------------------ autovacuum | on | | sighup | Starts the autovacuum subprocess. checkpoint_comp | 0.9 | | sighup | Time spent flushing dirty buffers during default_statist | 200 | | fgedu | Sets the default statistics target. effective_cache | 3145724 | 8kB | fgedu | Planner's assumption about the size of t effective_io_co | 200 | | fgedu | Number of simultaneous requests that can log_checkpoints | on | | superfgedu | Logs each checkpoint. log_connections | on | | superfgedu | Logs each successful connection. log_directory | log | | sighup | Directory where log files are written. log_disconnecti | on | | superfgedu | Logs end of a session, including duratio log_filename | postgre | | sighup | Log file name pattern. ... (25 行记录)