pgsql数据库优化相关参数

之前写过一篇sql优化的文章，但是后来发现还是不够快，开发中用到两个库，在测试库中测试速度要比用来演示的库快的多，同样的索引，同样的sql,同样的数据量，查询速度相差却很大。所以远程连接了服务器查看了一下数据库的配置参数，发现该数据库大部分参数是默认的，只修改了最大连接数为2000，然而共享内存却仍然是默认的512M.

于是去查询了一下优化pgsql 的相关参数都是干嘛的。小结一下

max_connections = 500

max_connections的目的是防止max_connections * work_mem超出了实际内存大小。比如，如果将work_mem设置为实际内存的2%大小，则在极端情况下，如果有50个查询都有排序要求，而且都使用2％的内存，则会导致swap的产生，系统性能就会大大降低。当然，如果有4G的内存，同时出现50个如此大的查询的几率应该是很小的。不过，要清楚 max_connections和work_mem的关系。由于服务器是15g内存，所以这里500连接肯定是没有问题的。

shared_buffers = 3840MB 共享内存

postgresql通过shared_buffers和内核和磁盘打交道，因此应该尽量大(减少IO)，让更多的数据缓存在shared_buffers中。PostgreSQL既使用自身的缓冲区，也使用内核缓冲IO。这意味着数据会在内存中存储两次，首先是存入PostgreSQL缓冲区，然后是内核缓冲区。这被称为双重缓冲区处理。对大多数操作系统来说，这个参数是最有效的用于调优的参数。此参数的作用是设置PostgreSQL中用于缓存的专用内存量。linux服务器建议设置为内存的25%

effective_cache_size = 11520MB

postgresql能够使用的最大缓存。这个数字对于独立的pgsql服务器而言应该足够大。优化器假设一个查询可以使用的最大内存（包括pg使用的和操作系统缓存），和shared_buffer等内存无关，只是给优化器生成计划使用的一个假设值。设置较大后优化器更倾向使用索引扫描而不是顺序扫描，建议的设置为可用空闲内存的25%，这里的可用空闲内存指的是主机物理内存在运行pg时得空闲值。

maintence_work_mem=960MB：这里定义的内存只是在CREATE INDEX, VACUUM等时用到，因此用到的频率不高，但是往往这些指令消耗比较多的资源，因此应该尽快让这些指令快速执行完毕：给maintence_work_mem大的内存。

wal_buffers = 16MB预写日志缓存

两种情况下要酌情调大：

1…单事务的数据修改量很大，产生的日志大于wal_buffers，为了避免多次IO，调大该值。

2.系统中并发小数据量修改的短事务较多，并且设置了commit_delay，此时wal_buffers需要容纳多个事务（commit_siblings个）的日志，调大该值避免多次IO。

work_mem = 1572kB 本地内存

ORDER BY, DISTINCT 和mergejoin都要用到排序操作，而哈希操作在哈希连接、哈希聚集和以哈希为基础的 IN 子查询处理中都会用到。该参数是会话级参数。

postgresql在执行排序操作时，会根据work_mem的大小决定是否将一个大的结果集拆分为几个小的和 work_mem差不多大小的临时文件。增加work_mem有助于提高排序的速度。通常设置为实际RAM的2% -4%。

checkpoint_completion_target = 0.7

指定检查点完成的目标，作为检查点之间总时间的一部分。默认值是 0.5。 这个参数只能在postgresql.conf文件中或在服务器命令行上设置。高频率的检查点可能会影响性能。

default_statistics_target= 100

为没有通过ALTER TABLE SET STATISTICS设置的列特定目标设置表列的默认统计目标。

max_worker_processes=10

总worker进程数

min_wal_size = 1GB

只要WAL磁盘使用率低于这个设置，旧的WAL文件总数被回收，以供将来检查点使用。而不是删除。 这可以用来确保预留足够的WAL空间处理WAL使用中的峰值，比如当运行大批量工作时。

关于pgsql新特性并行查询的相关参数：

max_parallel_maintenance_workers

max_parallel_maintenance_workers 参数设置维护命令(例如 CREATE INDEX) 命令允许的最大并行进程数，默认值为2。

max_parallel_workers

设置系统支持的最大并行进程数，默认值为8。

max_worker_processes

设置数据库的最大后台进程数，默认值为8。

max_parallel_workers_per_gather

设置单个Gather或Gather Merge节点能够启用的最大并行进程数，默认值为2，并行查询(Parallel Query)的并行度受此参数的影响，CREATE INDEX 命令的并行度不受此参数影响。

各参数之间的关系如下：

max_worker_processes 参数设置的是数据库允许的最大后台进程数，并行进程属于后台进程的一种；

max_parallel_workers 参数设置数据库允许的最大并行进程数，这个值小于或等于 max_worker_processes。

并行进程数设置分为两类，第一类是并行查询，并行查询的并行度由 max_parallel_workers_per_gather 参数控制，第二类是维护命令(例如 CREATE INDEX)，维护命令的并行度由 max_parallel_maintenance_workers 参数控制。

max_parallel_workers_per_gather+max_parallel_maintenance_workers值应小于或等于 max_parallel_workers。