PostgreSQL性能暴涨的关键？内存IO并发参数居然要这么设置？

一、内存参数调优：合理分配内存资源

内存是PostgreSQL性能的核心资源之一，合理配置内存参数能大幅减少磁盘IO，提升查询速度。以下是关键内存参数的调优方法：

1.1 shared_buffers：共享内存缓冲区的核心

shared_buffers是PostgreSQL用于缓存数据页的共享内存缓冲区，所有连接共享这部分内存。它决定了PostgreSQL能在内存中保留多少热点数据，减少对磁盘的访问。

配置建议：

• 专用数据库服务器（1GB内存以上）：设置为系统内存的25%（官方推荐）。例如：
  • 8GB内存 → shared_buffers = 2GB
  • 16GB内存 → shared_buffers = 4GB
  • 32GB内存 → shared_buffers = 8GB
• 内存小于1GB的服务器：设置为内存的10%-20%，避免占用过多操作系统内存。

示例配置：

修改postgresql.conf文件（通常位于/var/lib/postgresql/17/main/或/etc/postgresql/17/main/）：

# postgresql.conf
shared_buffers = 4GB  # 16GB内存的25%

修改后需重启PostgreSQL使配置生效：

sudo systemctl restart postgresql-17

原理说明：

PostgreSQL依赖操作系统缓存（OS Cache）和自身的shared_buffers共同缓存数据。若shared_buffers过大，会挤压OS Cache的空间；过小则无法有效缓存热点数据。25%的比例是平衡两者的最优起点。

1.2 work_mem：查询操作的内存上限

work_mem是单个查询操作（如排序、哈希表、合并连接）能使用的最大内存。若操作超出此限制，PostgreSQL会将数据写入临时文件（磁盘），导致性能骤降。

配置建议：

• 默认值为4MB，适用于简单查询。
• 对于需要大量排序/哈希的复杂查询（如ORDER BY、DISTINCT、GROUP BY），可适当调大（如16MB或32MB）。
• 注意并发风险：若有10个并发查询，每个使用16MB，总内存消耗为160MB，需确保系统有足够剩余内存。

示例配置：

# postgresql.conf
work_mem = 16MB  # 每个查询操作的内存上限

实践场景：

假设你有一个查询需要对100万行数据排序，默认4MB内存可能不够，会生成临时文件。调大work_mem到16MB后，排序可在内存中完成，查询时间从10秒缩短到2秒。

1.3 maintenance_work_mem：维护操作的内存保障

maintenance_work_mem是维护操作（如VACUUM、CREATE INDEX、ALTER TABLE ADD FOREIGN KEY）的最大内存。这些操作对内存敏感，足够的内存能大幅提升效率。

配置建议：

• 默认值为64MB，可根据维护任务的大小调大（如256MB或512MB）。
• 注意：autovacuum进程会使用autovacuum_work_mem（默认继承maintenance_work_mem），若开启自动清理，需避免设置过大导致内存竞争。

示例配置：

# postgresql.conf
maintenance_work_mem = 256MB  # 加速CREATE INDEX和VACUUM

实践场景：

创建一个包含1000万行数据的索引，默认64MB可能需要30分钟；调大到256MB后，时间缩短到10分钟。

1.4 temp_buffers：临时表的内存缓冲区

temp_buffers是单个会话用于临时表（CREATE TEMP TABLE）的最大内存。默认值为8MB，若需频繁使用大临时表，可适当调大。

示例配置：

# postgresql.conf
temp_buffers = 32MB  # 增加临时表的内存缓冲区

二、IO参数调优：降低磁盘IO延迟

磁盘IO是PostgreSQL性能的常见瓶颈，通过调优IO参数可减少磁盘访问频率，提升响应速度。

2.1 背景写入器（Background Writer）：减少用户进程的IO等待

背景写入器是一个独立进程，负责将“脏页”（修改过但未写入磁盘的数据页）异步写入磁盘。合理配置其参数能减少用户查询的IO阻塞。

关键参数：

• bgwriter_delay：背景写入器的循环间隔（默认200ms）。调小此值可增加写入频率，减少脏页积累。
• bgwriter_lru_maxpages：每次循环最多写入的脏页数（默认100）。调大此值可加快脏页清理。
• bgwriter_lru_multiplier：写入页数的乘数（默认2.0）。值越大，预留的干净页越多，减少用户进程的IO等待。

示例配置（适用于高IO负载场景）：

# postgresql.conf
bgwriter_delay = 100ms         # 每100ms检查一次脏页
bgwriter_lru_maxpages = 200    # 每次最多写200个脏页
bgwriter_lru_multiplier = 3.0  # 预留更多干净页

2.2 effective_io_concurrency：异步IO的并发度

effective_io_concurrency控制PostgreSQL能同时发起的异步IO请求数，适用于支持异步IO的存储（如SSD、RAID）。

配置建议：

• 机械硬盘（HDD）：设为RAID组的磁盘数量（如4块盘的RAID 0→4）。
• 固态硬盘（SSD）：设为较高值（如200-500），利用SSD的高并发特性。

示例配置：

# postgresql.conf
effective_io_concurrency = 200  # SSD存储的最优值

2.3 temp_file_limit：限制临时文件大小

temp_file_limit是单个进程能使用的最大临时文件空间（默认-1，无限制）。若临时文件过大，会占满磁盘空间，导致查询失败。

配置建议：

根据磁盘空间设置合理上限（如1GB或10GB）。

示例配置：

# postgresql.conf
temp_file_limit = 1GB  # 限制临时文件最大1GB

三、并发参数调优：提升并行处理能力

PostgreSQL支持并行查询和并行维护操作，通过调优并发参数可充分利用多核CPU资源。

3.1 max_worker_processes：最大后台进程数

max_worker_processes是PostgreSQL能启动的最大后台进程数（包括并行查询worker、autovacuum进程等）。默认值为8，若需更多并行任务，可适当调大。

示例配置：

# postgresql.conf
max_worker_processes = 16  # 支持更多后台进程

3.2 max_parallel_workers_per_gather：查询的并行worker数

max_parallel_workers_per_gather是单个查询能启动的最大并行worker数（默认2）。调大此值可增加查询的并行度，但需注意CPU和内存的消耗（每个worker会占用work_mem内存）。

示例配置：

# postgresql.conf
max_parallel_workers_per_gather = 4  # 每个查询用4个worker并行处理
max_parallel_workers = 8             # 全局并行worker总数（不超过max_worker_processes）

实践场景：

一个扫描1000万行的SELECT查询，默认2个worker需要10秒；调大到4个worker后，时间缩短到5秒。

3.3 max_parallel_maintenance_workers：维护操作的并行worker数

max_parallel_maintenance_workers是维护操作（如CREATE INDEX、VACUUM）能启动的最大并行worker数（默认2）。调大此值可加速维护任务。

示例配置：

# postgresql.conf
max_parallel_maintenance_workers = 4  # 并行创建索引

四、课后Quiz：巩固调优知识

问题1

对于一台32GB内存的专用PostgreSQL服务器，shared_buffers的推荐配置是多少？请说明理由。

问题2

当查询出现ERROR: temporary file size exceeds temp_file_limit时，可能的原因是什么？如何解决？

答案与解析

问题1答案：

推荐配置为8GB（32GB的25%）。
理由：官方文档建议**专用数据库服务器（1GB内存以上）**将shared_buffers设置为内存的25%，平衡PostgreSQL共享缓冲区与操作系统缓存的使用，避免资源浪费。

问题2答案：

原因：查询的临时文件（如排序、哈希操作产生的临时文件）大小超过了temp_file_limit的限制。
解决办法：

1. 调大temp_file_limit（如从1GB改为2GB）；
2. 优化查询：添加索引减少排序操作，或重写查询逻辑（如用JOIN代替子查询）；
3. 增加work_mem，让更多操作在内存中完成，减少临时文件的产生。

五、常见报错解决方案

报错1：ERROR: out of memory

原因：work_mem或shared_buffers设置过小，导致查询/共享内存不足；或并发数过高，总内存超过系统限制。
解决：

• 调大work_mem（如从4MB改为16MB）；
• 调大shared_buffers（若内存充足）；
• 优化查询：减少结果集大小，或添加索引避免全表扫描；
• 限制并发数（如用max_connections控制连接数）。
  预防：根据服务器内存和并发数合理设置work_mem和shared_buffers，避免过度分配。

报错2：ERROR: temporary file size exceeds temp_file_limit

原因：临时文件大小超过temp_file_limit的限制。
解决：参考课后Quiz问题2的解决办法。
预防：根据查询需求设置合理的temp_file_limit，同时优化查询减少临时文件的产生。

报错3：WARNING: bgwriter could not free enough memory

原因：背景写入器无法释放足够的干净缓冲区，导致用户进程需要等待IO。
解决：

• 调大shared_buffers，增加缓冲区数量；
• 减小bgwriter_delay（如从200ms改为100ms），增加背景写入频率；
• 增加bgwriter_lru_maxpages（如从100改为200），每次写入更多脏页。
  预防：根据磁盘IO负载调整背景写入器参数，确保及时清理脏页。

参考链接

• shared_buffers：www.postgresql.org/docs/17/run…
• work_mem：www.postgresql.org/docs/17/run…
• 背景写入器参数：www.postgresql.org/docs/17/run…
• 并行查询参数：www.postgresql.org/docs/17/run…