postgresql参数优化

一 相关参数介绍

1.1 内存参数-shared_buffers

shared_buffers：共享缓存区的大小，相当于oracle数据库中的SGA.

一般推荐为内存的四分之一，不超过总内存的二分之一。

该值默认是128M。

1.2 cpu并行参数-max_parallel_workers

max_parallel_workers 定义了数据库可以使用的并行查询工作者的最大数量。每个并行工作者都可以在不同的CPU核心上执行查询操作，从而加速查询处理。通过适当配置这个参数，可以根据系统的硬件资源来控制并行查询的规模。可以在某种程度上控制数据库的整体CPU使用。

默认情况下，max_parallel_workers 的值为 8。这意味着 PostgreSQL 允许同时使用最多 8 个并行工作者来执行查询。

1.3 慢sql参数-log_min_duration_statement

log_min_duration_statement：从log找出执行超过一定时间的SQL。这个参数是设置执行最小多长时间的SQL输出到log，例如输出执行超过2秒的SQL。

启用查询日志：

首先，确保 PostgreSQL 的查询日志已启用。您可以编辑 PostgreSQL 的配置文件（通常是 postgresql.conf）并设置以下参数：

log_statement = 'ddl'

log_min_duration_statement = 2000

log_statement：该参数值为 "all"表示记录所有查询类型（SELECT、INSERT、UPDATE、DELETE），比较占用空间，且性能会降低，因此建议将其设置为ddl，只记录修改表结构的sql。

log_min_duration_statement：设置查询的最小持续时间，以毫秒为单位。在上面的示例中，它设置为 2000 毫秒（2秒）。

二 优化该参数

2.1 查看参数文件位置

show config_file;

2.2 修改参数值

修改以下参数：

shared_buffers=80GB

max_parallel_workers=20

log_statement = 'ddl'

log_min_duration_statement = 2000

2.3 重启数据库

配置以systemctl方式启动postgre，并配置开机自启。

2.3.1 新建service文件

systemctl restart postgresql

2.3.4 设置开机自启动

systemctl enable postgresql

2.4 检查确认

show max_parallel_workers;

show shared_buffers;

select pg_sleep(3);

观察日志，看是否能看到慢sql。