PostgreSQL服务器参数配置
Contents
内存相关
shared_buffers
这个参数决定了有多少内存将用于PostgreSQL的数据缓存.当多个会话从同一张表中请求相同数据时，shared_buffers保证了不需要在内在保留数据集的多个副本。这种方法减少了物理I/O。它在启动时被分配。PostgreSQL9.3以上为默认为128MB, 旧版为32MB. 这个参数对性能有显著的影响，因为直接影响服务器上物理I/O的使用量.
推荐为RAM的40%
effective_cache_size
这个值告诉PostgreSQL大约有多少内存可用于缓存机制（shared_buffers + 文件系统缓存）。
这个不是分配的。而是用于查询规划器进行查询预估的。并发的查询，将共享可用的空间。
主要作用：
当增加effective_cache_size时，规划器认为内存中可以获取更多的页面。这使得使用索引比顺序扫描更好。
如果设置太低，PostgreSQL则可能会认为顺序扫描更高效。
即一个较高的值， 会增加使用索引的可能性；而较低的值，则增加了顺序扫描的可能性。
work_mem
推荐为
work_mem = (available_ram * 0.25) / max_connections
这样子，可以确保所有连接的工作内存占总内存的1/4 RAM左右.
它可以在会话级别设置，如果你的SQL有明显的需要，可以为单个连接分配更多的内存。比如，在批量处理的情况下，对一张大表进行排序.
注意，这个内存是独立的，不与shared_buffers共享的。
maintenace_work_mem
这个参数可用于自动清空进程， 索引和更改表语句中执行的。这个可以在会话级别设置。
可以将这个参数值，设置得比work_mem更高，因为服务器上一般不会有太多的维护操作同时发生。
查询规划相关
default_statistics_target
默认为100.
sky=# show default_statistics_target ;
default_statistics_target
---------------------------
100
(1 row)
sky=#
PG会考虑（300*当前值）个页面来完成随机抽样。从这些样本中，填充pg_statistics。这些值将用于规划器。
如果explain analyze显示出实际成本与预估成本之间有明显差异，那可以适当增加这个值。
sky=# select attstattarget, attname from pg_attribute where attrelid = 'wb_status'::regclass;
attstattarget | attname
---------------+------------------------
-1 | user_id
0 | ctid
0 | xmin
-1 | id
-1 | sid
-1 | idstr
-1 | mid
-1 | user_screen_name
-1 | user_profile_image_url
-1 | text
-1 | source
-1 | thumbnail_pic
-1 | bmiddle_pic
-1 | original_pic
-1 | retweeted_status_id
-1 | geo
0 | cmin
0 | xmax
0 | cmax
0 | tableoid
-1 | reposts_count
-1 | comments_count
-1 | attitudes_count
-1 | visible
-1 | pic_urls
-1 | create_at
-1 | update_at
-1 | ad
-1 | is_deleted
(29 rows)
sky=#
为表的某些列，设置指定的statistics：
sky=# alter table wb_status alter COLUMN sid set statistics 200;
ALTER TABLE
sky=# select attstattarget, attname from pg_attribute where attrelid = 'wb_status'::regclass;
attstattarget | attname
---------------+------------------------
-1 | user_id
0 | ctid
0 | xmin
-1 | id
200 | sid
-1 | idstr
-1 | mid
-1 | user_screen_name
-1 | user_profile_image_url
-1 | text
-1 | source
-1 | thumbnail_pic
-1 | bmiddle_pic
-1 | original_pic
-1 | retweeted_status_id
-1 | geo
0 | cmin
0 | xmax
0 | cmax
0 | tableoid
-1 | reposts_count
-1 | comments_count
-1 | attitudes_count
-1 | visible
-1 | pic_urls
-1 | create_at
-1 | update_at
-1 | ad
-1 | is_deleted
(29 rows)
sky=#
-1：表示默认值，增加这个值会导致更多的抽样，也就是更多的资源消耗。最好是在会话中设置这个值，可以看到分析表使用了多长时间，查询性能增加了多少，然后决定是否保留这个值。
seq_page_cost 与 random_page_cost
random_page_cost：默认为4,
seq_page_cost：默认为1
即默认为4:1，如果调低到2:1，则可增加规划器使用索引的机会。（建议如果是SSD的话，可以调低为这个）
千万不要将random_page_cost设置得比seq_page_cost低。
查看所有影响规划器的cost如下:
╭─sky@sky-linux /ihome/db/postgresql/postgresql-9.5.0/data
╰─➤ cat postgresql.conf | grep "cost = "
#seq_page_cost = 1.0 # measured on an arbitrary scale
#random_page_cost = 4.0 # same scale as above
#cpu_tuple_cost = 0.01 # same scale as above
#cpu_index_tuple_cost = 0.005 # same scale as above
#cpu_operator_cost = 0.0025 # same scale as above
╭─sky@sky-linux /ihome/db/postgresql/postgresql-9.5.0/data
╰─➤
enable_xxx
╭─sky@sky-linux /ihome/db/postgresql/postgresql-9.5.0/data
╰─➤ cat postgresql.conf | grep "enable_"
#enable_bitmapscan = on
#enable_hashagg = on
#enable_hashjoin = on
#enable_indexscan = on
#enable_indexonlyscan = on
#enable_material = on
#enable_mergejoin = on
#enable_nestloop = on
#enable_seqscan = on
#enable_sort = on
#enable_tidscan = on
上面的是各种查询方式。可以设置为off。注意，就算是设置为off，也不是表示PG绝对不用这种方式。只是尽最大的可能不去使用这种方式（它本质上，只是将这种扫描方式的cost设置得非常大而已）
WAL 相关
╭─sky@sky-linux /ihome/db/postgresql/postgresql-9.5.0/data
╰─➤ cat postgresql.conf | grep -E "checkpoint|segments"
#checkpoint_timeout = 5min # range 30s-1h
#checkpoint_completion_target = 0.5 # checkpoint target duration, 0.0 - 1.0
#checkpoint_warning = 30s # 0 disables
wal_keep_segments = 32 # in logfile segments, 16MB each; 0 disables
#log_checkpoints = off
checkpoint_segments（或叫wal_keep_segments，PG9.0及以后版本）
查看默认值:
sky=# show wal_keep_segments ;
wal_keep_segments
-------------------
32
(1 row)
sky=#
即一旦wal_keep_segments个WAL区段填充后，则出现一个checkpoint。
checkpoint_timeout
超时值.（可以根据不同的单位来设置不同，参考上面的配置文件及说明）
如果超时了，也会出现一个checkpoint
checkpoint_completion_target
这个参数告诉PostgreSQL在每一次迭代过程中，以多快的速度来设法完成checkpoint的操作。默认为0.5。即PostgreSQL可以在下一个checkpoint开始之前，花费大约一半的时间来完成每个检查点。
可以直观地理解为，以多激烈的硬盘写速度来完成checkpoint操作。越小，表示以最大的速度来完成checkpoint操作，越大，表示以最慢的速度来完成checkpoint操作。
也即值越小，系统负载的峰值会瞬间非常高，值越大，系统负载就越倾向于平稳。
Windows下
update_process_title
将这个参数，设置为off，可以大幅提高Windows下的PostgreSQL性能？
参考资料improve-postgresql-windows-performance-by-100
杂项
autovacuum_analyze_threshold
作用：
指定至少N个插入，更新或删除的无组时才需要触发 ANALYZE 去分析表.
查看系统默认值:
localhost:5433 sky@sky=# show autovacuum_analyze_threshold ;
autovacuum_analyze_threshold
------------------------------
50
(1 row)
Time: 0.146 ms
localhost:5433 sky@sky=#
默认为50.（可在配置文件或命令行里设置，也可以独立修改某张表的阀值）：
ALTER TABLE ... SET (autovacuum_analyze_threshold = ...);
如:
localhost:5433 sky@sky=# ALTER TABLE wb_status SET (autovacuum_analyze_threshold = 100);
ALTER TABLE
Time: 1.194 ms
localhost:5433 sky@sky=# \d+ wb_status;
Table "public.wb_status"
Column | Type | Modifiers | Storage | Stats target | Description
------------------------+-----------------------------+--------------------------------------------------------+----------+--------------+-------------
id | integer | not null default nextval('wb_status_id_seq'::regclass) | plain | |
sid | bigint | not null default '0'::bigint | plain | |
idstr | character varying(64) | not null default ''::character varying | extended | |
mid | bigint | default '0'::bigint | plain | 10000 |
user_id | bigint | | plain | |
user_screen_name | character varying(32) | not null default ''::character varying | extended | |
user_profile_image_url | character varying(128) | not null default ''::character varying | extended | |
text | character varying(512) | not null default ''::character varying | extended | |
source | character varying(256) | not null default ''::character varying | extended | |
thumbnail_pic | character varying(256) | not null default ''::character varying | extended | |
bmiddle_pic | character varying(256) | not null default ''::character varying | extended | |
original_pic | character varying(256) | not null default ''::character varying | extended | |
retweeted_status_id | character varying(32) | not null default ''::character varying | extended | |
geo | character varying(256) | default ''::character varying | extended | |
reposts_count | integer | default 0 | plain | |
comments_count | integer | default 0 | plain | |
attitudes_count | integer | default 0 | plain | |
visible | character varying(64) | not null default ''::character varying | extended | |
pic_urls | character varying(1024) | not null default ''::character varying | extended | |
create_at | timestamp without time zone | not null default now() | plain | |
update_at | timestamp without time zone | not null default now() | plain | |
ad | character varying(256) | not null default ''::character varying | extended | |
is_deleted | smallint | not null default '0'::smallint | plain | |
Indexes:
"wb_status_pkey" PRIMARY KEY, btree (id)
"sid_wb_staus" UNIQUE, btree (sid)
"user_id_wb_staus" btree (user_id)
Options: autovacuum_analyze_threshold=100
localhost:5433 sky@sky=#
查看所有表的状态信息
SELECT * FROM pg_stat_all_tables [where schemaname = 'public'];
查看所有用户定义的表的状态信息
SELECT * FROM pg_stat_user_tables;
执行一次analyze前后：
localhost:5433 sky@sky=# SELECT * FROM pg_stat_all_tables where schemaname = 'public';
-[ RECORD 1 ]-------+------------------------------
relid | 17864
schemaname | public
relname | wb_status
seq_scan | 80
seq_tup_read | 103252172
idx_scan | 47
idx_tup_fetch | 29580516
n_tup_ins | 1370996
n_tup_upd | 0
n_tup_del | 0
n_tup_hot_upd | 0
n_live_tup | 1370996
n_dead_tup | 0
n_mod_since_analyze | 0
last_vacuum | [null]
last_autovacuum | [null]
last_analyze | [null]
last_autoanalyze | 2016-08-18 15:03:00.763547+08
vacuum_count | 0
autovacuum_count | 0
analyze_count | 0
autoanalyze_count | 1
Time: 10.908 ms
localhost:5433 sky@sky=# ANALYZE wb_status ;
ANALYZE
Time: 110883.966 ms
localhost:5433 sky@sky=# SELECT * FROM pg_stat_all_tables where schemaname = 'public';
-[ RECORD 1 ]-------+------------------------------
relid | 17864
schemaname | public
relname | wb_status
seq_scan | 80
seq_tup_read | 103252172
idx_scan | 47
idx_tup_fetch | 29580516
n_tup_ins | 1370996
n_tup_upd | 0
n_tup_del | 0
n_tup_hot_upd | 0
n_live_tup | 1370996
n_dead_tup | 0
n_mod_since_analyze | 0
last_vacuum | [null]
last_autovacuum | [null]
last_analyze | 2016-09-26 18:02:29.72493+08
last_autoanalyze | 2016-08-18 15:03:00.763547+08
vacuum_count | 0
autovacuum_count | 0
analyze_count | 1
autoanalyze_count | 1
Time: 10.861 ms
localhost:5433 sky@sky=#
可以看到analyze_count+1了
delete一条数据
localhost:5433 sky@sky=# delete from wb_status where id = 185794019;
DELETE 1
Time: 0.975 ms
localhost:5433 sky@sky=# SELECT * FROM pg_stat_all_tables where schemaname = 'public';
-[ RECORD 1 ]-------+------------------------------
relid | 17864
schemaname | public
relname | wb_status
seq_scan | 80
seq_tup_read | 103252172
idx_scan | 49
idx_tup_fetch | 29580518
n_tup_ins | 1370996
n_tup_upd | 0
n_tup_del | 1
n_tup_hot_upd | 0
n_live_tup | 1370995
n_dead_tup | 1
n_mod_since_analyze | 1
last_vacuum | [null]
last_autovacuum | [null]
last_analyze | 2016-09-26 18:02:29.72493+08
last_autoanalyze | 2016-08-18 15:03:00.763547+08
vacuum_count | 0
autovacuum_count | 0
analyze_count | 1
autoanalyze_count | 1
Time: 10.814 ms
localhost:5433 sky@sky=#
也可以看到n_dead_tup+1了.
autoanalyze 与 autovacuum 的threshold
vacuum threshold = vacuum base threshold + vacuum scale factor * number of tuples
analyze threshold = analyze base threshold + analyze scale factor * number of tuples