小小GUC，居然要我亲自去学？
前言
什么是GUC
首先什么是GUC，相信很多铁子都听到过：我的自研数据库新增了一个GUC参数，功能极其牛逼！其实GUC的全称是Grand Unified Configuration，就叫做"全局统一配置"，也就是我们通常所熟知的配置项，postgresql.conf里面的配置。
关于配置项的源码，在src/backend/utils/misc/guc.c
参数级别
首先参数级别，也就是说配置了参数，如何使参数生效呢？总共有7个配置
postgres=# select distinct context from pg_settings ;
context
-------------------
postmaster
superuser-backend
user
internal
backend
sighup
superuser
(7 rows)•backend：可以在postgresql.conf中对这些设置进行更改，而无需重新启动服务器。但新的配置值只会出现在这之后的连接中，在已有的连接中，这些值不会改变。•user：普通用户可以通过set命令来更改参数的配置值。•internal：该类参数是内部参数，也就是说，不可以进行修改，除非重新initdb。•postmaster：该类参数更改配置项后，需要重启PostgreSQL实例才能生效。•superuser：该类参数可以由超级用户来改变，改变时，只会影响到自身的session，不会影响到其他的用户。•sighup：在postgresql.conf配置文件中更改这种类型的参数无须重启实例，只需要向postmaster进程发送一个SIGHUP信号，让其重新读取配置文件即可。postmaster进程收到信号后，也会向其他子进程发送SIGHUP信号，让新的参数值在其他子进程中也生效。该类参数区别于backend类参数。•superuser-backend：该类参数可以由超级用户来改变，可以在postgresql.conf中对这些设置进行更改，而无需重新启动服务器。但新的配置值只会出现在这之后的连接中，在已有的连接中，这些值不会改变。
其中sighup可以通过三种方式生效，第二种方式相对陌生一点
pg_ctl -D PGDATA reload
kill -HUP <postmaster.pid>
psql -c "SELECT pg_reload_conf()"设置参数
设置参数的方式又有很多种，眼花缭乱
1.postgresql.conf2.config directory3.SET4.pg_settings5.ALTER objects6.command-line switches & PGOPTIONS7.ALTER SYSTEM SET
postgresql.conf
这个没什么多说的，只不过值得注意的是，假如同一个参数出现了多次的话，会以最后出现的为准，也就是后面的参数会覆盖前面的参数。这个和pg_hba.conf的规则是相反的，pg_hba.conf默认是匹配到第一条就不继续往下匹配了。
config directory
这种方式用在管理多个具有相关但不完全相同配置的服务器时，通过在postgresql.conf指定include_dir和include，类似于写代码指定头文件.h 一样。类似的是include_if_exists，不存在不会报错。
[2020-05-02 13:29:26 MEZ] node2 postgres [unknown] LOG: 00000: skipping missing configuration file "/tmp/test/data/foo.conf"可以看到，自定义的配置里面覆盖了原始的shared_buffers。
[postgres@xiongcc ~]$ cat pgdata/postgresql.conf | tail -n 2 ---指定了include和include_dir
include_dir = '/home/postgres/pgdata/myconf_dir'
include和 = '/home/postgres/pgdata/mycnf'
[postgres@xiongcc ~]$ cat pgdata/mycnf ---在mycnf里面指定了512MB
shared_buffers=512MB
[postgres@xiongcc ~]$ cat pgdata/postgresql.conf | grep shared_buffers
shared_buffers = 256MB # min 128kB
#wal_buffers = -1 # min 32kB, -1 sets based on shared_buffers
[postgres@xiongcc ~]$ pg_ctl -D pgdata/ restart
pg_ctl: PID file "pgdata/postmaster.pid" does not exist
Is server running?
trying to start server anyway
waiting for server to start....2021-12-30 16:54:44 CST [14307]: [1-1] user=,db=,app=,client= LOG: redirecting log output to logging collector process
2021-12-30 16:54:44 CST [14307]: [2-1] user=,db=,app=,client= HINT: Future log output will appear in directory "log".
done
server started
[postgres@xiongcc ~]$ psql -c "show shared_buffers"
shared_buffers
----------------
512MB
(1 row)典型的场景是在使用了CGroup进行资源隔离的服务器下，假如有三个库，ABC，A和C使用CGroup分配了8c16GB，B使用CGroup分配了16c32GB，那么我就可以在服务器某处放置一个三个库都可以访问到的文件，比如
include 'shared.conf'
include 'memory.conf'
include 'server.conf'shared.conf文件里面放置公共的配置，比如日志级别，memory.conf则可以分开为01memory-8GB.conf、01memory-16GB.conf，里面分别针对8GB内存和16GB内存进行的配置，server.conf也是类似。这样的话就不需要繁琐重复配置了，当然现在都是标准化配置了，这种需求也降低了。
SET
set设置的值只适用于当前会话上下文，所以很适合放在单个会话，比如work_mem，max_parallel_workers_per_gather，并且支持嵌入到存储过程中。
postgres=# CREATE PROCEDURE pro1()
LANGUAGE plpgsql
SET work_mem = '8MB'
AS $$
BEGIN
create table h1(id int);
END;
$$;
CREATE PROCEDURE
postgres=# show work_mem ;
work_mem
----------
4MB
(1 row)
postgres=# set work_mem = '8MB';
SET
postgres=# show work_mem ;
work_mem
----------
8MB
(1 row)
postgres=# \c postgres
You are now connected to database "postgres" as user "postgres".
postgres=# show work_mem ;
work_mem
----------
4MB
(1 row)还有一种方式，是使用set_config，第三个参数指定作用域，默认作用域是事务内生效，这种方式很适合放在脚本里面，交互式的设置
postgres=# begin;
BEGIN
postgres=*# SELECT set_config('work_mem','32MB', TRUE);
set_config
------------
32MB
(1 row)
postgres=*# show work_mem ;
work_mem
----------
32MB
(1 row)
postgres=*# commit ;
COMMIT
postgres=# SELECT set_config('work_mem','32MB', TRUE);
set_config
------------
32MB
(1 row)
postgres=# show work_mem ;
work_mem
----------
4MB
(1 row)
postgres=# SELECT set_config('work_mem','32MB', FALSE);
set_config
------------
32MB
(1 row)
postgres=# show work_mem ;
work_mem
----------
32MB
(1 row)
postgres=# \c postgres
You are now connected to database "postgres" as user "postgres".
postgres=# show work_mem ;
work_mem
----------
4MB
(1 row)与之类似的则是current_setting，第二个参数决定是否输出错误信息，我们可以巧妙的使用在函数中。
postgres=# select current_setting('work_mem',false);
current_setting
-----------------
4MB
(1 row)
postgres=# select current_setting('work_mem2',false);
ERROR: unrecognized configuration parameter "work_mem2"
postgres=# select current_setting('work_mem2',true);
current_setting
-----------------
(1 row)
postgres=# \! cat a.sql
create or replace function f1() returns text as
$$
declare
lv text;
begin
select current_setting('work_mem')
into lv;
return lv;
end;
$$ language plpgsql;
postgres=# \i a.sql
CREATE FUNCTION
postgres=# select f1();
f1
-----
4MB
(1 row)pg_settings
pg_settings通常用来查，不过它同样可以更新，改变会话本地的值，尽量不用这种方式
postgres=# select name,setting from pg_settings where name = 'work_mem';
name | setting
----------+---------
work_mem | 4096
(1 row)
postgres=# update pg_settings set setting = '12MB' where name = 'work_mem';
set_config
------------
12MB
(1 row)
UPDATE 0
postgres=# show work_mem ;
work_mem
----------
12MB
(1 row)
postgres=# \c
You are now connected to database "postgres" as user "postgres".
postgres=# show work_mem ;
work_mem
----------
4MB
(1 row)alter
alter支持的语法就多了，首先是alter user，不过要小心这里面的坑！参照之前的文章《小问题，大经验》，针对用户的设置，假如涉及到优化器相关的配置，比如enable_nestloop = off，优化器就会给这个用户针对nestloop 分配"disable cost"，假如SQL必须走nestloop，你就会发现这个用户的SQL跑的特别慢，后果可想而知。
postgres=# alter user u1 set work_mem to '4MB';
ALTER ROLE
postgres=# select usename,useconfig from pg_user where usename = 'u1';
usename | useconfig
---------+----------------
u1 | {work_mem=4MB}
(1 row)另外一个是不为熟知的alter user in database，可以针对某个用户针对某个库的配置，这种一般设用于设置search_path
postgres=# alter user u1 in database mydb set work_mem to '8MB';
ALTER ROLE
postgres=# select usename,useconfig from pg_user where usename = 'u1';
usename | useconfig
---------+-----------
u1 |
(1 row)
mydb=# \c mydb u1
You are now connected to database "mydb" as user "u1".
mydb=> show work_mem ;
work_mem
----------
8MB
(1 row)
mydb=> \c postgres u1
You are now connected to database "postgres" as user "u1".
postgres=> show work_mem ;
work_mem
----------
4MB
(1 row)第三个就是alter database了，也是类似的语法
postgres=# alter database mydb set work_mem = '32MB';
ALTER DATABASE
postgres=# \c mydb
You are now connected to database "mydb" as user "postgres".
mydb=# show work_mem ;
work_mem
----------
32MB
(1 row)
mydb=# \c postgres
You are now connected to database "postgres" as user "postgres".
postgres=# show work_mem ;
work_mem
----------
4MB
(1 row)
postgres=# alter database mydb set timezone = 'Asia/Kolkata';
ALTER DATABASE
postgres=# \c mydb postgres
You are now connected to database "mydb" as user "postgres".
mydb=# select now();
now
----------------------------------
2021-12-30 15:15:36.845862+05:30
(1 row)
mydb=# alter database mydb set timezone to default ;
ALTER DATABASE
mydb=# select now();
now
----------------------------------
2021-12-30 15:15:54.765091+05:30
(1 row)
mydb=# \c mydb postgres
You are now connected to database "mydb" as user "postgres".
mydb=# select now();
now
-------------------------------
2021-12-30 17:46:02.301933+08
(1 row)
mydb=# CREATE FUNCTION check_password(uname TEXT, pass TEXT)
RETURNS BOOLEAN AS $$
DECLARE passed BOOLEAN;
BEGIN
SELECT (pwd = $2) INTO passed
FROM pwds
WHERE username = $1;
RETURN passed;
END;
$$ LANGUAGE plpgsql
SECURITY DEFINER
-- Set a secure search_path: trusted
-- schema(s), then 'pg_temp'.
SET search_path = admin, pg_temp;
CREATE FUNCTIONcommand-line switches & PGOPTIONS
在服务器启动期间，可以通过-c命令传递参数
[postgres@xiongcc ~]$ pg_ctl -D pgdata/ stop
waiting for server to shut down.... done
server stopped
[postgres@xiongcc ~]$ postgres -c log_connections=yes -c log_destination='syslog' &
[1] 14695
[postgres@xiongcc ~]$ 2021-12-30 17:49:39 CST [14695]: [1-1] user=,db=,app=,client= LOG: redirecting log output to logging collector process
2021-12-30 17:49:39 CST [14695]: [2-1] user=,db=,app=,client= HINT: Future log output will appear in directory "log".
[postgres@xiongcc ~]$ psql
psql (14rc1)
Type "help" for help.
postgres=# show log_connections ;
log_connections
-----------------
on
(1 row)
postgres=# show log_destination ;
log_destination
-----------------
syslog
(1 row)这种方式设置的参数会覆盖postgresql.conf或者 ALTER SYSTEM提供的设置，只能重启服务器。
[postgres@xiongcc ~]$ kill -9 14695
[postgres@xiongcc ~]$ pg_ctl -D pgdata/ start
pg_ctl: another server might be running; trying to start server anyway
waiting for server to start....2021-12-30 17:51:47 CST [14728]: [1-1] user=,db=,app=,client= LOG: redirecting log output to logging collector process
2021-12-30 17:51:47 CST [14728]: [2-1] user=,db=,app=,client= HINT: Future log output will appear in directory "log".
done
server started
[1]+ Killed postgres -c log_connections=yes -c log_destination='syslog'
[postgres@xiongcc ~]$ psql
psql (14rc1)
Type "help" for help.
postgres=# show log_destination ;
log_destination
-----------------
stderr
(1 row)当通过libpq启动一个客户端会话时，也可以使用PGOPTIONS 环境变量指定参数设置。但是不会影响其他的会话，通过 shell 或者其他方式，其他客户端和库可能提供它们自己的机制，以便允许用户在不直接使用SQL命令的前提下修改会话设置。
[postgres@xiongcc ~]$ env PGOPTIONS="-c geqo=off -c statement_timeout=5min" psql
psql (14rc1)
Type "help" for help.
postgres=# show statement_timeout ;
statement_timeout
-------------------
5min
(1 row)
postgres=# ^Z
[1]+ Stopped env PGOPTIONS="-c geqo=off -c statement_timeout=5min" psql
[postgres@xiongcc ~]$ psql
psql (14rc1)
Type "help" for help.
postgres=# show statement_timeout;
statement_timeout
-------------------
0
(1 row)alter system
这种方式是最坑的，能不用就不用，因为会将配置设置到postgresql.auto.conf文件里面，而postgresql.auto.conf的优先级大于postgresql.conf！所以，当你发现修改postgresql.conf参数不生效的时候，看看是不是有postgresql.auto.conf同名参数。
[postgres@xiongcc ~]$ psql
psql (14rc1)
Type "help" for help.
postgres=# alter system set default_transaction_read_only to on;
ALTER SYSTEM
postgres=# select pg_reload_conf();
pg_reload_conf
----------------
t
(1 row)
postgres=# create table look(id int);
ERROR: cannot execute CREATE TABLE in a read-only transaction
postgres=# show default_transaction_read_only;
default_transaction_read_only
-------------------------------
on
(1 row)可以看到，一个小小的GUC参数居然这么多配置！那么大几百个参数，我该如何调整呢？
拿捏
对于一般调优，有一个入门级网站，https://pgtune.leopard.in.ua/，会根据输入的配置，生成一个通用的调优策略，不过可以看到，仅仅有部分参数，覆盖面并没有很广。
这个冷门的项目，地址在 https://github.com/jberkus/annotated.conf，顾名思义，给配置文件进行注释
主要的几个文件包括
•annotated.csv: a spreadsheet file of all 269 options ordered by category and subcategory, with recommendations and comments for your reference.•guc_tutorial_10.odp/pdf: the slides for the tutorial.•postgresql.10.simple.conf: a sample PostgreSQL 10 configuration file, with the 18 or so most common settings for users to alter called out.•extra.10.conf: an additional configuration file with another 24 options called out.•gucs.pgdump: a PostgreSQL 10 backup file, containing a database.•gucs.sql: a text-mode database dump
操作很简单，两条SQL
createdb gucs
pg_restore -d gucs -x -O -U postgres gucs.pgdump看下效果，错误可以忽视
[postgres@xiongcc annotated.conf]$ createdb gucs
[postgres@xiongcc annotated.conf]$ pg_restore -d gucs -x -O -U postgres gucs.pgdump
pg_restore: while PROCESSING TOC:
pg_restore: from TOC entry 3; 2615 2200 SCHEMA public postgres
pg_restore: error: could not execute query: ERROR: schema "public" already exists
Command was: CREATE SCHEMA public;
pg_restore: warning: errors ignored on restore: 1
[postgres@xiongcc annotated.conf]$ psql
psql (14rc1)
Type "help" for help.
postgres=# \c gucs
You are now connected to database "gucs" as user "postgres".
gucs=# \d
List of relations
Schema | Name | Type | Owner
--------+------------------+-------+----------
public | annotated | table | postgres
public | annotated_report | view | postgres
public | categories | table | postgres
public | subcategories | table | postgres
(4 rows)annotated_report是基于annotated、categories和subcategories的一个视图
gucs=# \d+ annotated_report
View "public.annotated_report"
Column | Type | Collation | Nullable | Default | Storage | Description
----------------+------+-----------+----------+---------+----------+-------------
category | text | | | | extended |
subcategory | text | | | | extended |
name | text | | | | extended |
type | text | | | | extended |
defaults | text | | | | extended |
recommendation | text | | | | extended |
range | text | | | | extended |
context | text | | | | extended |
docs | text | | | | extended |
comments | text | | | | extended |
View definition:
SELECT annotated.category,
annotated.subcategory,
annotated.name,
annotated.type,
annotated.defaults,
annotated.recommendation,
CASE
WHEN pg_settings.min_val IS NOT NULL THEN (pg_settings.min_val || COALESCE(pg_settings.unit, ''::text)) || COALESCE((' to '::text || pg_settings.max_val) || COALESCE(pg_settings.unit, ''::text), ''::text)
WHEN pg_settings.enumvals IS NOT NULL THEN array_to_string(pg_settings.enumvals, ','::text)
ELSE NULL::text
END AS range,
pg_settings.context,
pg_settings.short_desc AS docs,
annotated.comments
FROM annotated
JOIN pg_settings ON annotated.name = pg_settings.name
JOIN categories ON annotated.category = categories.category
LEFT JOIN subcategories ON annotated.category = subcategories.category AND annotated.subcategory = subcategories.subcategory
ORDER BY categories.category_sort, subcategories.subcategory_sort, annotated.name;categories和subcategories就是针对GUC的一个简单分类
gucs=# select * from categories limit 10;
category | category_sort
--------------------------------+---------------
File Locations | 1
Connections and Authentication | 2
Resource Usage | 3
WAL and Checkpoints | 4
Query Tuning | 6
Statistics | 7
Maintenance | 8
Customized Options | 15
Developer Options | 16
Locale & Formatting | 11
(10 rows)
gucs=# select * from subcategories limit 10;
category | subcategory | subcategory_sort
--------------------------------+-----------------------------+------------------
Client Connection Defaults | Locale and Formatting | 3
Client Connection Defaults | Statement Behavior | 2
Connections and Authentication | Connection Persistence | 4
Connections and Authentication | Connection Settings | 3
Connections and Authentication | Security and Authentication | 2
Locale & Formatting | Display | 2
Locale & Formatting | Locale | 3
Maintenance | Autovacuum | 4
Maintenance | Free Space Map | 5
Maintenance | Manual Vacuum | 3
(10 rows)那么看一下关键的视图
gucs=# select * from annotated_report limit 2;
-[ RECORD 1 ]--+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
category | File Locations
subcategory |
name | config_file
type | string
defaults | ConfigDir/postgresql.conf
recommendation | default
range |
context | postmaster
docs | Sets the server's main configuration file.
comments | Can only be changed via command-line switch for obvious reasons. Useful primarily for testing different configuration options, or for automated restart with different configuration options.
-[ RECORD 2 ]--+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
category | File Locations
subcategory |
name | data_directory
type | string
defaults | ConfigDir
recommendation | default
range |
context | postmaster
docs | Sets the server's data directory.
comments | Supports the ability to distribute files according to sysadmin or operating system defined schemes, or for launching multiple restart instances using the same binaries. Most of the time, it's better to use configuration options to define these locations so that all PostgreSQL binaries default to the correct paths.
gucs=# select * from pg_settings where name = 'config_file';
-[ RECORD 1 ]---+-------------------------------------------
name | config_file
setting | /home/postgres/pgdata/postgresql.conf
unit |
category | File Locations
short_desc | Sets the server's main configuration file.
extra_desc |
context | postmaster
vartype | string
source | override
min_val |
max_val |
enumvals |
boot_val |
reset_val | /home/postgres/pgdata/postgresql.conf
sourcefile |
sourceline |
pending_restart | f可以看到，相较于原生的pg_settings，做了一些精简，保留了几个关键字段，同时新增了recommendation（推荐值）和comments（论述），给了一些调优建议。
那既然是调优的，不妨看一看实际调优PostgreSQL过程中经常会去动的参数
常见调优参数
gucs=# select name,recommendation,context from annotated_report where name in ('shared_buffers','effective_cache_size','random_page_cost','checkpoint_timeout','max_wal_size','min_wal_size','checkpoint_completion_target','maintenance_work_mem','work_mem');
name | recommendation | context
------------------------------+----------------------------------------------------------------+------------
shared_buffers | AvRAM / 4 | postmaster
work_mem | ( AvRAM / max_connections ) OR ( AvRAM / 2 * max_connections ) | user
max_wal_size | default | sighup
min_wal_size | default | sighup
checkpoint_completion_target | default | sighup
checkpoint_timeout | default | sighup
effective_cache_size | ( AvRAM * 0.75 ) | user
random_page_cost | default | user
maintenance_work_mem | ( AvRAM / 8 ) for most | user
(9 rows)•shared_buffers：AvRAM / 4，也符合通用情况下的调优策略，只是要注意double buffer的影响，推荐看一下老白的《再聊聊PG数据库的shared buffers配置策略》•work_mem：( AvRAM / max_connections ) OR ( AvRAM / 2 * max_connections )，对于复杂的查询可能会同时并发运行好几个排序或散列(hash)操作；每个排序或散列操作都会分配这个参数声明的内存来存储中间数据。同样好几个正在运行的会话可能会同时进行排序操作,因此使用的总内存量可能是work_mem的很多倍。所以建议会话级设置•max_wal_size、min_wal_size和checkpoint_completion_target：此处未给出调优建议，checkpoint_completion_target在v14已经默认设置为0.9了，另外还需要考虑FPI的影响，可以使用pg_waldump -z，查看FPI占整个WAL的比例•effective_cache_size：AvRAM * 0.75，符合通用的调优策略•maintenance_work_mem：给的建议是( AvRAM / 8 ) for most，设置较大的值对于VACUUM、RESTORE、CREATE INDEX、ADD FOREIGN KEY和ALTER TABLE等操作有所性能提升。并且autovacuum worker也会使用该值的内存，所以可能会分配最高达这个内存的autovacuum_max_workers倍，通用的调优策略可以设置为每GB内存分配50MB•random_page_cost：Sets the ratio of seek to scan time for your database storage. Should not be altered unless you're using special storage (SSDs, high end SANs, etc.) where seek/scan ratios are actually different. If you need the database to favor indexes more, tune effective_cache_size and some of the cpu_* costs instead.，基于当前存储，顺序扫描和随机扫描的一个比例，默认是4，也就是PostgreSQL认为随机扫描的效率是顺序扫描的1/4。对于SSD和一些高端SAN存储可以调小，给一个通用的调优方案：高端NAS/SAN存储：2.5或3.0，亚马逊EBS和Heroku云平台：2.0，iSCSI或其他普通SAN存储：6.0，但可能变化较大，需要按照实际情况而定；固态硬盘：2.0或者2.5；NvRAM也叫NAND：1.5
bgwriter
gucs=# select name,recommendation,context from annotated_report where name like '%bgwriter%';
name | recommendation | context
-------------------------+----------------+---------
bgwriter_delay | default | sighup
bgwriter_flush_after | default | sighup
bgwriter_lru_maxpages | default | sighup
bgwriter_lru_multiplier | default | sighup
(4 rows)
gucs=# select name,comments from annotated_report where name like '%bgwriter%';
name |
comments
-------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
bgwriter_delay | Thanks to bgwriter autotuning, it should no longer be necessary for most users to touch the bgwriter settings. Only modify these if you have a demonstrated issue shown by checkpoint spikes and monitoring pg_stat_bgwriter. Laptop PostgreSQL users may want to increase bgwriter_delay to 60s to decrease I/O activity, since it is no longer possible to turn the bgwriter off.
bgwriter_flush_after | Unless you have time to tune memory flushing behavior and test for improvements/regressions
bgwriter_lru_maxpages |
bgwriter_lru_multiplier |
(4 rows)美中不足，没有给出相关调优建议，通常情况下，bgwriter应该是脏块回写的主力，并且要知道，PostgreSQL里面，backend process需要的话还会做刷脏的累活，所以需要合理调整这些值。引用一下白老师的文章
•bgwriter_delay：bgwriter两个任务之间的休眠时间的初始值为200毫秒；•bgwriter_lru_maxpages：每次bgwriter任务写buffer的最大page数，一旦达到这个数量，bgwriter就结束任务开始休息，也就是说bgwriter休眠200毫秒，然后写入几十毫秒就又开始了休息；•bgwriter_lru_multiplier：用于评估下一次写任务的数量，其依据是这段时间内新申请的buffer数量的倍数，如果这个参数乘以新增加buffer分配的数量后小于bgwriter_lru_maxpages，那么有可能下一个写任务的数量会小于预期的写入量。将这个参数调大，会增加bgwriter回写脏块的数量，不过会增加写IO压力，将这个参数调小，可以降低写IO压力，不过可能导致backend写脏块的比例增加。不同负载的系统中，这个参数的值应该是需要做调整的，而不是只是用缺省值2。如果你发现你的系统的写IO压力还不大，但是 bgwriter写比例偏低，backend写比例偏高，那么尝试加大这个参数还是有效的。
vacuum
gucs=# select name,recommendation,context from annotated_report where name like '%vacuum%';
name | recommendation | context
-------------------------------------+---------------------+------------
vacuum_defer_cleanup_age | 0 | sighup
vacuum_cost_delay | | user
vacuum_cost_limit | default | user
vacuum_cost_page_dirty | default | user
vacuum_cost_page_hit | default | user
vacuum_cost_page_miss | default | user
vacuum_freeze_min_age | lower, varies | user
vacuum_freeze_table_age | 400000000 | user
vacuum_multixact_freeze_min_age | lower, varies | user
vacuum_multixact_freeze_table_age | 350000000 | user
autovacuum | on | sighup
autovacuum_analyze_scale_factor | default | sighup
autovacuum_analyze_threshold | default | sighup
autovacuum_freeze_max_age | 500000000 | postmaster
autovacuum_max_workers | default | postmaster
autovacuum_multixact_freeze_max_age | default | postmaster
autovacuum_naptime | default | sighup
autovacuum_vacuum_cost_delay | default | sighup
autovacuum_vacuum_cost_limit | default | sighup
autovacuum_vacuum_scale_factor | default | sighup
autovacuum_vacuum_threshold | default | sighup
autovacuum_work_mem | AvRAM / workers / 4 | sighup
log_autovacuum_min_duration | 1min | sighup
(23 rows)
gucs=# select name,comments from annotated_report where name like '%vacuum%';
name |
comments
-------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
vacuum_defer_cleanup_age | No longer effective thanks to hot_standby_feedback.
vacuum_cost_delay | Most of the time, you will want manual vacuum to execute without vacuum_delay, especially if you're using it as part of ETL. If for some
reason you can't use autovacuum on an OLTP database, however, you may want to increase this to 20ms to decrease the impact vacuum has on currently running queries. Will cause vacuum to take up to twice as long to complete.
vacuum_cost_limit |
vacuum_cost_page_dirty |
vacuum_cost_page_hit |
vacuum_cost_page_miss |
vacuum_freeze_min_age | Most users will want to decrease this so that rows which have been cold for a long time get frozen earlier, and avoid an autovacuum_freeze. The suggestion of 500000 is for a moderately busy database; do not set to less than a few hours worth of XIDs. Maximum setting is 1/2 of autovaccuum_max_freeze_age.
vacuum_freeze_table_age | Generally set to 80% of autovacuum_max_freeze age to preempt a full vacuum freeze. If you can schedule cron vacuums during application slow periods, it might be valuable to lower this value in order to encourage vacuum freezing of tables before they are triggered by autovacuum.
vacuum_multixact_freeze_min_age | Like freeze_min_age, lower this to somewhere around an hour of XID burn. Try starting with 500000.
vacuum_multixact_freeze_table_age | Set to 80% of autovaccum_multixact_freeze_max_age
autovacuum | Starts the daemon which cleans up your tables and indexes, preventing bloat and poor response times. The only reason to set it to “off” is for databases which regularly do large batch operations like ETL. Note that you can adjust the frequency or stop autovacuum on individual tables by adding rows to the pg_autovacuum system table.
autovacuum_analyze_scale_factor | This setting should be optimal for most databases. However, very large tables (1m rows or more) in which rows are added in a skewed fashion may need to be autoanalyzed at a lower percentage, such as 5% or even 1%.
autovacuum_analyze_threshold |
autovacuum_freeze_max_age | Triggers autovacuum automatically if a table is about to suffer from XID rollover. The setting is very conservative, and should probably be increased to 500million, but not higher.
autovacuum_max_workers | If you have an installation with many tables (100's to 1000's) or with some tables which autovacuum takes hours to process, you may want to add additional autovacuum workers so that multiple tables can be vacuumed at once. Be conservative, though, as each autovacuum worker will utilize a separate CPU core, memory and I/O.
autovacuum_multixact_freeze_max_age | Triggers autovacuum automatically when the oldest “multixact” (a kind of lock transaction) is more than this old. Do not raise past 1billion.
autovacuum_naptime | Decrease this to 30s or 15s if you have a large number (100's) of tables, or if you otherwise see from pg_stat_user_tables that autovacuum is not keeping up.
autovacuum_vacuum_cost_delay | If autovacuum is having too much of a performance impact on running queries, you might want to increase this setting to 50ms. However, this will also cause individual vacuum tasks to take longer.
autovacuum_vacuum_cost_limit |
autovacuum_vacuum_scale_factor |
autovacuum_vacuum_threshold |
autovacuum_work_mem | Set a limit on this which is based on the number of autovac workers you expect to have running.
log_autovacuum_min_duration | Logs all autovacuum actions which take more than the specified time. Useful for figuring out if autovacuum is bogging down your system or blocking.
(23 rows)vacuum相关基本未给出推荐值，不过给出了相应的建议
•vacuum_cost_limit：在VACUUM和ANALYZE命令的执行过程中，系统维护着一个内部计数器来跟踪各种被执行的I/O操作的估算开销。当累计的代价达到一个阈值（vacuum_cost_limit），执行这些操作的进程将按照vacuum_cost_delay所指定的休眠一小段时间。然后它将重置计数器并继续执行，默认值可能和你的硬件能力极不匹配，导致垃圾回收速度跟不上垃圾产生的速度，并产生严重的性能问题。如果有多个活跃业务db，单纯提高autovacuum_max_workers而不提高autovacuum_vacuum_cost_limit（默认值为vacuum_cost_limit，即200）的值也没有用处。原因是autovacuum_vacuum_cost_limit对成本的限制是全局的，该参数控制了PostgreSQL实例所有worker在运行时可达到的"成本"上限，其计算公式为：autovacuum_vacuum_cost_limit/autovacuum_max_workers。一旦达到成本上限，worker就会进入休眠，sleep时间为参数vacuum_cost_delay所设定的值（12该参数默认为2ms，11该参数默认为20ms），然后再继续处理。对于有cache的raid卡，这个值应该设置成1000左右，对于ssd，应该设置成10000。•autovacuum_analyze_scale_factor：This setting should be optimal for most databases. However, very large tables (1m rows or more) in which rows are added in a skewed fashion may need to be autoanalyzed at a lower percentage, such as 5% or even 1%.，对于大表需要调小，可以表级调整
其他的就依葫芦画瓢照着敲吧。
小结
参考
https://pgtune.leopard.in.ua/
https://blog.dbi-services.com/postgresql-set_config-and-current_setting/
https://www.enterprisedb.com/blog/what-guc-variable
https://www.pgcon.org/2008/schedule/events/104.en.html
https://github.com/jberkus/annotated.conf
原文作者：PostgreSQL学徒；如有侵权，请联系管理员删除。
