PostgreSQL配置文件--资源使用(除WAL外)

2 资源使用(除WAL外) RESOURCE USAGE (except for WAL)

2.1 内存 Memory

2.1.1 shared_buffers

数字型
默认： shared_buffers = 128MB ，最小值128KB
重启数据库生效
影响postgresql性能的重要参数之一
共享缓冲区大小。postgresql对数据操作时都要先将数据从磁盘读取到内存中，然后进行更新，最后再将数据写回磁盘。
shared_buffers的功能就是用于存放从磁盘读取的数据。
根据文档参数的设置范围一般在25%~40%之间。
windows与linux对内存的管理方式不同，在linux中需要注意共享段大小的设置(/etc/sysctl.conf中的kernel.shmmax)。
kernel.shmmax决定了进程可调用的最大共享内存数量。
简单的计算方法是kernel.shmmax=postgres shared_buffers + 32 MB

2.1.2 huge_pages

字符型
默认： huge_pages = try ，on（使用）、off（不使用）、try（尽量使用）三选一。
重启数据库生效
数据库是否使用大页
需要OS的支持，配置vm.nr_hugepages*2MB大于shared_buffers.

2.1.3 temp_buffers

数字型
默认： temp_buffers = 8MB ，最小值800KB
称之为临时缓冲区，用于存放数据库会话访问临时表数据
可以在单独的session中对该参数进行设置，尤其是需要访问比较大的临时表时，将会有显著的性能提升。
临时表缓冲区存放在每个数据库进程的私有内存中，而不是存放在数据库的共享内存中。

2.1.4 max_prepared_transactions

数字型
默认： max_prepared_transactions = 0
重启数据库生效
它决定能够同时处于prepared状态的事务的最大数目（参考PREPARE TRANSACTION命令）。
值为0，则将数据库将关闭prepared事务的特性。
通常应该和max_connections的值一样大。
每个事务消耗600字节共享内存。
注意：设置为非0值不可取，除非知道你在做什么。it is not advisable to set max_prepared_transactions nonzero ，unless you actively intend to use prepared transactions.

2.1.5 maintenance_work_mem

数字型
默认： maintenance_work_mem = 64MB
影响postgresql性能的重要参数之一
称之为维护工作内存，主要是针对数据库的维护操作或者语句，决定数据库的维护操作使用的内存空间的大小。
只在VACUUM、CREATE INDEX和ALTER TABLE ADD FOREIGN KEY等操作时用到，使用频率不高，但往往消耗较多资源，应尽快让他快速执行完毕。
在对整个数据库进行VACUUM或者较大的index进行重建时，适当的调整该参数非常必要。
该值加大，可以缩短VACUUM数据库和从dump文件中恢复数据库需要的时间。
它存放在每个数据库进程的私有内存中，而不是存放在数据库的共享内存中。
最小值为1M，建议为总内存大小的1/4/autovacuum_max_workers

2.1.6 work_mem

数字型
默认： work_mem = 4MB 
最小值为64K，通常设置为实际RAM的2%-4%
影响postgresql性能的重要参数之一
数据库的排序操作和哈希表使用的内存缓冲区的大小，合适的work_mem大小能够保证这些操作在内存中进行。
ORDER BY、DISTINCT和merge连接会使用排序操作。哈希表在Hash连接、hash聚集函数和用哈希表来处理IN谓词中的子查询中被使用。

这些操作需要的内存超过work_mem，会把超出部分放入swap，设置太小可能直接使用swap，设置太大可能让其他操作使用swap。
合适的大小对数据库性能至关重要。

在实际的生产环境中，要对系统监控数据进行分析，作出最好的选择。
大致有两种方式：
第一种是可以根据业务量的大小和类型，一般语句运行时间，来粗略的估计一下。
第二种方式是通过对数据库的监控，数据采集，然后计算其大小。
在实际维护中通过explain analyze分析语句的work_mem大小是否合适。在语句中设置work_mem可以充分利用内存，提高语句的执行效率。
work_mem参数对系统的性能是如此的重要，让其实时的适应数据库的运行状况显的不太可能，但可以通过对数据库运行周期的监控，总结相应的数据，然后定制一个专用的脚本，专门用来修改work_mem的大小，使其阶段性的更加适应系统的状况，不失为一种好的方法。

对于work_mem内存分配时还要考虑数据库的并发情况，max_connections决定了系统的最大的并发连接数。
不论如何调整work_mem都要考虑max_connections*work_mem+shared_buffers+temp_buffers+maintenance_work_mem+OS所需内存不能够超过整个的RAM大小，这是非常重要的。

2.1.7 autovacuum_work_mem

数字型
默认： autovacuum_work_mem = -1
最小1MB，如果是-1，那么使用maintenance_work_mem设置的值

2.1.8 max_stack_depth

数字型
默认： max_stack_depth = 2MB
可动态修改，只有数据库超级用户才能修改,。
决定一个数据库进程在运行时的STACK所占的空间的最大值。数据库进程在运行时，会自动检查自己的STACK大小是否超过max_stack_depth，如果超过，会自动终止当前事务。应该比OS STACK（ulimit –s）的上限小1MB。

2.1.9 dynamic_shared_memory_type

字符型
默认： dynamic_shared_memory_type = posix ，posix、sysv、windows、mmap、''五选一。
动态共享内存类型，为空表示禁用

2.1.10 replacement_sort_tuples

数字型
默认： replacement_sort_tuples = 150000
limits use of replacement selection sort
replacement_sort_tuples建议设置的值与CPU cache size有关，CPU cache size越大，可以调大这个值。 否则建议不要修改它。
初次扫描的replacement_sort_tuples条记录使用replacement selection算法（以装下一个work_mem单位为止，所以可能实际的replacement selection记录数小于replacement_sort_tuples的设置），超出的tuples使用quicksort。

2.2 硬盘

temp_file_limit

数字型
默认： 
temp_file_limit = -1
限制每个线程的临时文件大小，单位为KB
-1不限制

2.3 内核资源 Kernel Resource Usage

2.3.1 max_files_per_process

数字型
默认： max_files_per_process = 1000 ，最少25
重启数据库生效
如果数据库有非常多小文件（比如有几十万以上的表，还有索引等，并且每张表都会被访问到时），建议FD可以设多一些，避免进程需要打开关闭文件。
但是不要大于系统设置的ulimit -n(open files)

2.3.2 shared_preload_libraries

数字型
默认： 
shared_preload_libraries = ''
重启数据库生效

2.4 Cost-Based Vacuum Delay

vacuum_cost_delay = 10        #0-100 milliseconds
vacuum_cost_page_hit = 1               # 0-10000 credits
vacuum_cost_page_miss = 10             # 0-10000 credits
vacuum_cost_page_dirty = 20            # 0-10000 credits
vacuum_cost_limit = 200                # 1-10000 credits

2.5 Background Writer

2.5.1 bgwriter_delay

数字型
默认： bgwriter_delay = 200ms，设定值在10-10000ms之间，单位是毫秒。
重启数据库生效
backgroud writer进程连续两次flush数据的间隔。
后台写数据库进程每次完成写数据到物理文件中的任务以后，就会睡眠bgwriter_delay指定的时间。
postgresql中脏数据的写入不仅仅是由backgroud writer参数决定的，checkpoint也对脏数据的写入进行了控制。
backgroud writer进程的间隔是以毫秒计算，而checkpoint的间隔时间相对要很长。
所以bgwriter进程如果能够实现周期均匀数据量合适的写入数据，在减少IO的次数的同时还能够提升命中率，对checkpoint也能够减轻压力，不至于发生集中的写入操作，而造成IO使用的高值，也可以说进一步降低了对系统资源的考验。
bgwriter_delay的值应该是10的倍数，如果不是10的倍数，数据库会自动将参数的值设为最接近指定值的10的倍数。

2.5.2 bgwriter_lru_maxpages

数字型
默认： bgwriter_lru_maxpages =  100，设定值为0-1000之间，单位buffers
重启数据库生效
backgroud writer进程每次写的最多数据量。
如果脏数据量小于该数值时，写操作全部由backgroud writer进程完成；反之，大于的部分将有server process进程完成。
设置该值为0时表示禁用backgroud writer写进程，完全有server process来完成；
配置为-1时表示所有脏数据都由backgroud writer来完成。(这里不包括checkpoint操作)

2.5.3 bgwriter_lru_multiplier

数字型
默认： bgwriter_lru_multiplier =  2.0，设定值为0-10.0之间（0-10.0 multiplier on buffers scanned/round）
重启数据库生效
一般使用默认值即可，不需要修改这个参数。
表示每次往磁盘写脏数据块量，当然该值必须小于bgwriter_lru_maxpages。
设置太小时需要写入的脏数据量大于每次写入的数据量，这样剩余需要写入磁盘的工作需要server process进程来完成，将会降低性能；
值配置太大说明写入的脏数据量多于当时所需buffer的数量，方便了后面再次申请buffer工作，同时可能出现IO的浪费。
bgwriter的最大数据量计算方式：1000/bgwriter_delay*bgwriter_lru_maxpages*8K=最大数据量。
参数值依据系统状态而设置，通过监控系统的运行状况，进行分析总结。参考数据字典pg_stat_bgwriter，\d pg_stat_bgwriter

2.5.4 bgwriter_flush_after

数字型
默认：bgwriter_flush_after = 512kB ，0表示禁止
当bgwriter进程写脏数据块量超过配置阈值时，触发调用OS sync_file_range，告诉os backend flush 线程异步刷盘。从而削减os dirty page堆积。  
IO很好的机器，不需要考虑平滑调度

2.6 Asynchronous Behavior

2.6.1 max_worker_processes

数字型
默认： max_worker_processes = 8
重启数据库生效
整个数据库集群同时能开启最大进程
注意:
    如果有standby，standby的参数必须大于等于主库的参数值。
    如果设置为0，表示不允许并行。

2.6.2 max_parallel_workers_per_gather

数字型
默认： max_parallel_workers_per_gather = 2
决定了每个Gather node最多允许启用多少个work process。
注意
    在OLTP业务系统中，不要设置太大，因为每个worker都会消耗同等的work_mem等资源，争抢会比较厉害。
    建议在OLAP中使用并行，并且做好任务调度，减轻冲突。

2.6.3 max_parallel_workers

数字型
默认： max_parallel_workers = 8
maximum number of max_worker_processes that can be used in parallel queries
最大并行查询工作线程数

2.6.4 effective_io_concurrency

数字型
默认：  effective_io_concurrency = 1 取值范围1-1000，0表示禁用预读取，不使用异步IO
异步IO并发数
目的是充分发挥块设备的吞吐能力，让块设备处于更繁忙的工作状态（一次连续摄取更多的块），而不是等用户进程需要数据时再读取。
如果数据库并发连接（或者活跃会话）足够时，并且块设备处于繁忙状态，那么没有必要开启异步IO，因为开了也没什么用，块设备已经很忙了。
目前PostgreSQL的bitmap heap scan支持异步IO，因为bitmap heap scan是按顺序读取堆表的数据块的，对于机械硬盘，bitmap heap scan异步IO效率可以得到充分的发挥。（实际上全表扫描也适合异步IO。）
异步IO的参数effective_io_concurrency如何设置？如果是磁盘阵列，根据表空间所在的块设备进行设置，例如RAID0/10设置为磁盘个数，而RAID5或者其他RAID，设置为实际的数据盘个数（如10块硬盘组成raid5则设置为9）。
仅仅当操作系统支持posix时，才能使用异步IO。
Sets the number of concurrent disk I/O operations that PostgreSQL expects can be executed simultaneously.   
Raising this value will increase the number of I/O operations that any individual PostgreSQL session attempts to initiate in parallel.   
The allowed range is 1 to 1000, or zero to disable issuance of asynchronous I/O requests. Currently, this setting only affects bitmap heap scans.

2.6.5 old_snapshot_threshold

数字型
默认： old_snapshot_threshold = -1 ，取值范围1min-60d、-1表示禁用，0表示立即
重启数据库生效
Time before a snapshot is too old to read pages changed after the snapshot was taken.

2.6.6 backend_flush_after

backend_flush_after = 0                # measured in pages, 0 disables