PostgreSQL 参数调整作为 OLAP 统计数据库

完整 SQL

ALTER SYSTEM SET shared_buffers = '8GB';
ALTER SYSTEM SET work_mem = '64MB';
ALTER SYSTEM SET maintenance_work_mem = '1GB';
ALTER SYSTEM SET effective_cache_size = '24GB';
ALTER SYSTEM SET temp_buffers = '64MB';
ALTER SYSTEM SET max_parallel_workers_per_gather = 4;
SELECT pg_reload_conf();

各参数说明

shared_buffers

• 用途：定义PostgreSQL实例用来缓存数据块的共享内存大小。
• 设置：占服务器总内存的25%-40%。更多内存可以改善读操作性能。
• 示例：对于32GB内存的服务器，可以设置为8GB。

work_mem

• 用途：用于每个查询操作（如排序和哈希表）分配的内存。
• 设置：对于复杂查询，建议设置较大的值，如64MB，但需根据并发查询数量进行调整。

maintenance_work_mem

• 用途：用于维护任务（如VACUUM、CREATE INDEX等）的内存。
• 设置：可以设置较高的值，如1GB，但不应超过系统可用内存的限制。

effective_cache_size

• 用途：估计操作系统空闲内存，用于查询规划器。它并不实际分配内存。
• 设置：通常设置为服务器总内存的50%-75%。
• 示例：对于32GB内存的服务器，可以设置为24GB。

temp_buffers

• 用途：为临时表分配的缓冲区大小。
• 设置：对于需要大量临时表的查询，可以设置较大，比如64MB。

max_parallel_workers_per_gather

• 用途：每个并行查询允许使用的最大工作进程数。
• 设置：默认值为2。在多核服务器上可以设置更高的值，如4。

max_worker_processes

• 用途：PostgreSQL允许的最大工作进程数，包括并行查询工作进程。
• 设置：推荐设置为CPU核心数的一半或更多，具体依赖于并行查询需求。

max_parallel_workers

• 用途：整个服务器允许的最大并行工作进程数。
• 设置：可以与max_worker_processes保持一致。

更多文章

PostgreSQL 16 编译安装：https://www.zhangbj.com/p/1699.html

PostgreSQL 搭建主从同步实现读写分离：https://www.zhangbj.com/p/1795.html

PostgreSQL 搭建级联从库：https://www.zhangbj.com/p/1796.html