1.查看参数文件的位置
使用show 命令查看,比较常用的show config_file.此还可以查看pg_settings数据字典.
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
|
test=# show config_file; config_file ------------------------------ /data/pgdata/postgresql.conf(1 row)test=# show hba_file test-# ; hba_file -------------------------- /data/pgdata/pg_hba.conf(1 row)test=# show ident_file ; ident_file ---------------------------- /data/pgdata/pg_ident.conf |
2.查看当前会话的参数值
可以使用show命令或者查看pg_settings字典.
使用show all可以查看全部的参数值.show 参数名查看指定参数
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
|
test=# show all;-------------------------------------+------------------------------------------------------+------------------------------------------------------------------------------------------------------------------------------- allow_system_table_mods | off | Allows modifications of the structure of system tables. application_name | psql | Sets the application name to be reported in statistics and logs. archive_command | test ! -f /data/archive/%f && cp %p /data/archive/%f | Sets the shell command that will be called to archive a WAL file. archive_mode | on | Allows archiving of WAL files using archive_command. archive_timeout | 0 | Forces a switch to the next WAL file if a new file has not been started within N seconds. array_nulls | on | Enable input of NULL elements in arrays.... test=# show work_mem; work_mem ---------- 4MB(1 row) test=# \xExpanded display is on.test=# select * from pg_settings where name in ('work_mem')test-# ;-[ RECORD 1 ]---+----------------------------------------------------------------------------------------------------------------------name | work_memsetting | 4096unit | kBcategory | Resource Usage / Memoryshort_desc | Sets the maximum memory to be used for query workspaces.extra_desc | This much memory can be used by each internal sort operation and hash table before switching to temporary disk files.context | uservartype | integersource | defaultmin_val | 64max_val | 2147483647enumvals | boot_val | 4096reset_val | 4096sourcefile | sourceline | pending_restart | f |
3.修改pg的参数值
1.全局修改pg的参数.
有些参数只有当pg服务重启的时候才生效,典型的例子就是shared_buffers,定义了共享内存的大小.
许多参数在pg服务运行的时候就能修改.再更改之后像服务器执行一个reload操作,强制pg重新读取postgresql.conf,因此你只需要编辑postgresql.conf文件,再执行 pg_ctl reload 即可 . 对于需要重启的,在修改完postgresql后需要执行 pg_ctl restart
对于9.5以后的版本,可以通过查看pg_file_settings查看你设置的参数是否生效.例如如果你设置了一个参数需要重启数据库才能生效或者设置错误,那么在此字典中会出现报错.
|
1
2
3
4
5
|
test=# select * from pg_file_settings where error is not null; sourcefile | sourceline | seqno | name | setting | applied | error -----------------------------------+------------+-------+-----------------+---------+---------+------------------------------ /data/pgdata/postgresql.auto.conf | 4 | 22 | max_connections | 10000 | f | setting could not be applied(1 row) |
对于9.4以后的版本,你还可以使用 alter system 命令修改参数.使用alter system命令将修改postgresql.auto.conf文件,而不是postgresql.conf,这样可以很好的保护postgresql.conf文件,加入你使用很多alter system命令后搞的一团糟,那么你只需要删除postgresql.auto.conf,再重新加载即可.
|
1
2
3
4
5
6
7
8
9
10
11
12
|
test=# show work_mem; work_mem ---------- 4MB(1 row)test=# alter system set work_mem='8MB';ALTER SYSTEMtest=# show work_mem; work_mem ---------- 4MB(1 row) |
查看postgresql.auto.conf:
|
1
2
3
4
|
[postgres@postgresql1 pgdata]$ cat postgresql.auto.conf # Do not edit this file manually!# It will be overwritten by the ALTER SYSTEM command.work_mem = '8MB' |
使用pg_ctl reload重新load配置文件,再查看参数值:
|
1
2
3
4
5
|
test=# show work_mem ; work_mem ---------- 8MB(1 row) |
2.直接使用set命令,在会话层修改,修改之后将被用于未来的每一个事务,只对当前会话有效:
|
1
2
3
4
5
6
7
8
|
test=# test=# set work_mem='16MB';SETtest=# show work_mem; work_mem ---------- 16MB(1 row) |
我们打开另外一个会话,查看work_mem参数,可以发现work_mem还是4MB
|
1
2
3
4
5
|
postgres=# show work_mem; work_mem ---------- 4MB(1 row) |
3.set命令后添加 local关键字, 只在当前事务中修改,只在当前事务内有效:
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
|
test=# show work_mem; work_mem ---------- 16MB(1 row)test=# begin;BEGINtest=# set local work_mem='8MB';SETtest=# show work_mem; work_mem ---------- 8MB(1 row)test=# commit;COMMITtest=# show work_mem; work_mem ---------- 16MB |
4.使用 reset恢复参数的默认值
再pg_settings字典reset_val字段表示了如果使用reset,则此参数恢复的默认值为多少
使用 reset 参数名 来恢复某个参数的默认值,使用 reset all来恢复所有的参数值.
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
|
test=# show work_mem; work_mem ---------- 16MB(1 row)test=# reset work_mem;RESETtest=# show work_mem; work_mem ---------- 4MB(1 row) test=# reset all;RESET |
5.为特定的用户组设置参数
一.为特定的数据库里的所有的用户设置参数,例如为test数据库所有的连接设置work_mem为16MB:
|
1
2
|
test=# alter database test set work_mem='16MB';ALTER DATABASE |
二.为数据库中的某个特定用户设置参数.例如为brent用户,设置work_mem为2MB:
|
1
2
|
postgres=# alter role brent set work_mem='2MB';ALTER ROLE |
经过测试发现,如果你同时为数据库和用户设置了特定参数,那么以用户为准.例如上面的,如果我用brent用户连接到test数据库,那么我的work_mem应该为2MB:
|
1
2
3
4
5
6
7
8
|
postgres=# \c test brentYou are now connected to database "test" as user "brent".test=> test=> test=> show work_mem; work_mem ---------- 2MB |
三.为某个特定用户连接到特定的数据库设置参数.例如为用户brent在数据库test中设置work_mem为8MB
|
1
2
|
test=# alter role brent in database test set work_mem='8MB';ALTER ROLE |
上面说的三种设置,优先级递增,也就是说,如果设置了1,2,3那么就以第3个为准,如果设置了1,2那么就是以2为准,以此类推.
pg对此的实现方法和当用户连接数据库的时候,立刻手动执行set命令的效果完全相同
查看你当前的参数值是从何处指定,可以通过查询pg_setttings中的source字段获取,例如如果设置了database级别的参数.那么查询结果应该如下:
|
1
2
3
4
|
test=# select name,setting,source from pg_settings where name='work_mem'; name | setting | source ----------+---------+---------- work_mem | 16384 | database |
其它的,例如设置了第三种:
|
1
2
3
4
5
6
|
test=# \c test brentYou are now connected to database "test" as user "brent".test=> select name,setting,source from pg_settings where name='work_mem'; name | setting | source ----------+---------+--------------- work_mem | 8192 | database user |
补充：postgresql重要参数解析及优化
1，max_connections 200
最大客户端连接数。每个连接在后端都会对应相应的进程，耗费一定的内存资源。如果连接数上千，需要使用连接池工具。
2，shared_buffers 25% of total memory
数据库用于缓存数据的内存大小。该参数默认值很低（考虑不同的系统平台），需要调整。不宜太大，很多实践表明，大于1/3的内存会降低性能。
3，effective_cache_size 50%-75% of total memory
This is a guideline for how much memory you expect to be available in the OS and PostgreSQL buffer caches, not an allocation! 这个参数只在查询优化器选择时使用，并不是实际分配的内存，该参数越大，查询优化器越倾向于选择索引扫描。
4，checkpoint_segments 256 checkpoint_completion_target 0.9
checkponit_segments wal个数达到多少个数checkponit，还有一个参数checkponit_timeout,控制最长多长时间checkpoint。对于写入比较大的数据库，该值越大越好。但是值越大，执行恢复的时间越长。
checkpoint_completion_target 控制checkponit write 分散写入，值越大越分散。默认值0.5，0.9是一个比较合适的值。
5，work_mem
用于排序，默认值即可。每个连接都会分配一定work_mem,这个是会实际分配的内存，不宜过大，默认值即可。如果要使用语句中有较大的排序操作，可以在会话级别设置该参数，set work_men = ‘2GB',提高执行速度。
6，maintanance_work_mem
维护性操作使用的内存。例如：vacuum ,create index,alter table add foreign key,restoring database dumps.做这些操作时可以临时设置该值大小，加快执行速度。set session maintanance_work_mem = ‘2GB';
7,random_page_cost (默认值 4) seq_page_cost(默认值 1)
设置优化器获取一个随机页的cost，相比之下一个顺序扫描页的cost为1.
当使用较快的存储，如raid arrays，scsi，ssd时，可以适当调低该值。有利于优化器悬着索引扫描。ssd 时，可以设置为2.
8，autovacuum
—maintenance_work_mem 1-2GB
—autovacuum_max_workers
如果有多个小型表，分配更多的workers，更少的mem。
大型表，更多的men，更少的workers。
以上为个人经验，希望能给大家一个参考，也希望大家多多支持服务器之家。如有错误或未考虑完全的地方，望不吝赐教。
原文链接：https://blog.csdn.net/su377486/article/details/80211104
