【PostgreSQL】如何通过调整PostgreSQL配置参数提高数据库性能

1. 数据库初始性能

使用pgbench在更新PostgreSQL数据库配置前先测试下数据库性能。测试PG数据库使用工具——pgbench。如何使用的PostgreSQL数据库没有安装该插件需要先进行安装。
然后，创建一个测试数据库性能用的测试库pgbench_test，命令：

createdb pgbench_test

接着，执行如下命令初始化数据：

pgbench -i -s 10 pgbench_test

-i: 表示初始化；
-s: 缩放因子，用于控制生成测试数据的数据量，-s 10 表示生成 10 倍于默认数据量的测试数据（默认 100,000 行数据）。
pgbench_test: 指定生成数据的数据库；
执行如下命令进行基准测试：

pgbench -c 10 -j 2 -t 1000 pgbench_test

-c 10: 表示使用10个并发的客户端连接；
-j 2: 使用2个线程处理；
-t 1000: 每个客户端发起1000个事务；

我的测试结果如下：

2. 内存相关参数

• shared_buffers
  • 建议设置为系统内存的 25%-40%
  • 这是PostgreSQL用于缓存数据的内存区域。
• maintenance_work_mem
  • 这是维护操作（如VACUUM、CREATE INDEX）可以使用的内存。
  • 建议设置为系统内存的 5%-10%

3. WAL（Write-Ahead Logging）相关参数

• wal_buffers：
  • 这是WAL日志缓存的大小。
  • 默认值为-1（自动调整），通常不需要修改。如果需要手动设置，建议为16MB。
• checkpoint_timeout：
  • 这是检查点的时间间隔。增加此值可以减少检查点频率，从而减少I/O压力。
  • 建议设置为15分钟到30分钟。

4. 并行查询相关参数

• max_parallel_workers_per_gather：
  • 这是每个查询可以使用的并行工作进程数：
  • 建议设置为CPU核心数的 50%-75%
• max_worker_processes:
  • 这是PostgreSQL可以使用的最大工作进程数;
  • 议设置为CPU核心数的 2倍

5. 连接相关参数

• max_connections：
  • 这是PostgreSQL允许的最大连接数
  • 根据应用需求设置，避免设置过高导致资源耗尽
• effective_cache_size：
  • 这是操作系统和PostgreSQL可以使用的缓存大小；
  • 建议设置为系统内存的 50%-75%

6. 根据情况调整

因为我是在云服务器上进行的测试，服务器规格2核4G，能榨取的性能有限：

但是调整之后还是略有提高，如下：

PS：调整时切记根据自己服务器规格和数据库的使用场景来，盲目调整可能导致性能恶化。