PostgreSQL调优工具PGTune使用和PostgreSQL 12内存参数优化建议
一、PG调优工具----PGTune使用
1、PGTune
打开网址https://pgtune.leopard.in.ua/
会显示如下截图：
1.1 左边需要输入参数说明
# DB Version: 12
数据库的版本支持10,11,12,13,14,15
# OS Type: Linux
操作系统支持linux,mac苹果和Windows
# DB Type: Online transaction processing system
数据库类型：web应用，oltp,数据仓库(olap),桌面应用，混合系统（DB和app同时存在）
# Total Memory (RAM): 16 GB
OS总内存大小
# CPUs num:8
# Connections num: 1000
连接数
# Data Storage: SSD storage
数据存储类型：SSD固态硬盘存储，SAN存储，HDD机械硬盘存储。
说明：
操作系统一般都是Linux,比如centos 7 或者ubuntu18和ubuntu20,或者国产kylin V10
操作系统：生产环境一般都是数据库专用；测试环境存在其他应用和数据库共有的情况。
（如果数据库是专用的，按操作系统实际情况填写；如果是数据库和其他应用共用，建议cpu和内存减半填写）
cpu大小，看项目组需求,比如cpu有4U，8U,16U,32U等。
（因接触环境有限，cpu指intel或者海光的x86_64的架构；没玩过华为的arm架构）
内存大小, 看项目组需求，比如8G，16G，32G，64G等
连接数：接触过的生产环境，在10个以内，或者200以内的，我的经验值设置为1000
1.2 右边参数说明
a、设置postgresql.conf参数
Add/modify this settings in postgresql.conf
# DB Version: 12
# OS Type: linux
# DB Type: oltp
# Total Memory (RAM): 16 GB
# CPUs num: 8
# Connections num: 1000
# Data Storage: ssd
max_connections = 1000
shared_buffers = 4GB
effective_cache_size = 12GB
maintenance_work_mem = 1GB
checkpoint_completion_target = 0.9
wal_buffers = 16MB
default_statistics_target = 100
random_page_cost = 1.1
effective_io_concurrency = 200
work_mem = 1048kB
min_wal_size = 2GB
max_wal_size = 8GB
max_worker_processes = 8
max_parallel_workers_per_gather = 4
max_parallel_workers = 8
max_parallel_maintenance_workers = 4
b、登录PG后，使用psql执行对应SQL命令
ALTER SYSTEM writes the given parameter setting to the postgresql.auto.conf file, which is read in addition to postgresql.conf
# DB Version: 12
# OS Type: linux
# DB Type: oltp
# Total Memory (RAM): 16 GB
# CPUs num: 8
# Connections num: 1000
# Data Storage: ssd
ALTER SYSTEM SET
max_connections = '1000';
ALTER SYSTEM SET
shared_buffers = '4GB';
ALTER SYSTEM SET
effective_cache_size = '12GB';
ALTER SYSTEM SET
maintenance_work_mem = '1GB';
ALTER SYSTEM SET
checkpoint_completion_target = '0.9';
ALTER SYSTEM SET
wal_buffers = '16MB';
ALTER SYSTEM SET
default_statistics_target = '100';
ALTER SYSTEM SET
random_page_cost = '1.1';
ALTER SYSTEM SET
effective_io_concurrency = '200';
ALTER SYSTEM SET
work_mem = '1048kB';
ALTER SYSTEM SET
min_wal_size = '2GB';
ALTER SYSTEM SET
max_wal_size = '8GB';
ALTER SYSTEM SET
max_worker_processes = '8';
ALTER SYSTEM SET
max_parallel_workers_per_gather = '4';
ALTER SYSTEM SET
max_parallel_workers = '8';
ALTER SYSTEM SET
max_parallel_maintenance_workers = '4';
c、PGTune关于配置优化建议
也就是这个配置不是最优的，但是如果没有接触过PG的运维同学，不会优化PG。按这个配置，不会有大问题。
PGTune calculate configuration for PostgreSQL based on the maximum performance for a given hardware configuration. It isn't a silver bullet for the optimization settings of PostgreSQL. Many settings depend not only on the hardware configuration, but also on the size of the database, the number of clients and the complexity of queries. An optimal configuration of the database can only be made given all these parameters are taken into account.
PGTune根据给定硬件配置的最大性能计算PostgreSQL的配置。这并不是PostgreSQL优化设置的灵丹妙药。许多设置不仅取决于硬件配置，还取决于数据库的大小、客户端的数量和查询的复杂性。只有在考虑到所有这些参数的情况下，才能对数据库进行优化配置。
二、PostgreSQL 12内存参数优化建议
1、shared_buffers:设置数据库服务器使用的共享内存缓冲区数量,该缓冲区为缓存数据块所用。默认值是128MB。推荐值为物理内存的25%～40%，不超过物理内存的50%。
为什么推荐值是物理内存的1/4呢？因为PG是double buffer机制，缓存了2份数据。不仅有PG自己的shared buffer，还依赖于操作系统的高速缓存区。该值设置大于40%，不一定有性能上的提升，会造成额外的负载。
2、maintenance_work_mem:指定在维护性操作（例如VACUUM、CREATE INDEX和ALTER TABLE ADD FOREIGN KEY）
中使用的 最大的内存量。注意当自动清理运行时，可能会分配最多达这个内存的autovacuum_max_workers倍，因
此要小心不要把该默认值设置得太高。 推荐每GB的物理内存设置50MB，比如16GB，可以设置maintenance_work_mem为800MB（PGTune推荐值为1GB）。
比如建索引时，在会话级别，临时设置maintenance_work_mem为2GB。
set maintenance_work_mem to '2GB';
3、work_mem：设置在写入临时磁盘文件之前查询操作(例如排序或哈希表)可使用的最大内存容量。默认值4MB。
order by,distinct 和合并连接（merge joins）都会用到排序操作。
推荐值：（网上有建议2%-4%物理内存，觉得还是设置偏大）
物理内存<=16G，设置32MB；物理内存在32G--128G之间，设置64MB；
物理内存>=256G，设置为128M或者256M。
work_mem千万不能设置为1GB，有很多用户连接时，会把内存耗尽的。
4、temp_buffers: 为每个数据库会话设置用于临时缓冲区的最大内存.这些是仅用于访问临时表的会话本地缓冲。默认值8MB。
可以参考上面work_mem的设置。
当然，也可以在会话级别，需要访问比较大的临时表时，调大temp_buffers的值，比如 set temp_buffers TO '32MB';
5、wal_buffers：用于还未写入磁盘的 WAL 数据的共享内存量。默认值为-1（shared_buffers的1/32 或者3%的大小），通常设置为16MB即可。
6、effective_cache_size：预估执行计划中在一次索引扫描中可用的最大缓存,包括操作系统可使用的部分以及PostgreSQL可使用的部分(剔除操作系统本身和其他应用程序可用的内存后)。系统并不会根据这个值来真实地分配那么多内存,但是规划器会根据这个值来判断系统能否提供查询执行过程中所需的内存。如果设置的过小,规划器可能会认为没有足够的内存,从而不选择走索引扫描而是顺序扫描(因为使用索引速度上会提升,但需要占用更多的中间内存),在计算一个索引的预计开销值时会对此值加以考虑。这个参数对PostgreSQL分配的共享内存大小没有任何影响,它只用于执行计划中代价的估算。在优化器评估索引的时候，一般越大,估算的索引代价可能会越小,越倾向于走索引,而不是顺序扫描。建议设置为物理内存的50%。
说明：内存部分参考恩墨熊老师笔记，Postgre12.2官方手册。
如描述有误，欢迎指正。
