伏龙阁资源网

postgresql 中的参数查看和修改方式
站长资源 2024/12/27 佚名
2 0

1.查看参数文件的位置

使用show 命令查看,比较常用的show config_file.此还可以查看pg_settings数据字典.

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 参数名查看指定参数

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=# \x
Expanded display is on.
test=# select * from pg_settings where name in ('work_mem')
test-# ;
-[ RECORD 1 ]---+----------------------------------------------------------------------------------------------------------------------
name      | work_mem
setting     | 4096
unit      | kB
category    | Resource Usage / Memory
short_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     | user
vartype     | integer
source     | default
min_val     | 64
max_val     | 2147483647
enumvals    | 
boot_val    | 4096
reset_val    | 4096
sourcefile   | 
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查看你设置的参数是否生效.例如如果你设置了一个参数需要重启数据库才能生效或者设置错误,那么在此字典中会出现报错.

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,再重新加载即可.

test=# show work_mem;
 work_mem 
----------
 4MB
(1 row)
test=# alter system set work_mem='8MB';
ALTER SYSTEM
test=# show work_mem;
 work_mem 
----------
 4MB
(1 row)

查看postgresql.auto.conf:

[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配置文件,再查看参数值:

 
test=# show work_mem ;
 work_mem 
----------
 8MB
(1 row)

2.直接使用set命令,在会话层修改,修改之后将被用于未来的每一个事务,只对当前会话有效:

test=# 
test=# set work_mem='16MB';
SET
test=# show work_mem;
 work_mem 
----------
 16MB
(1 row)

我们打开另外一个会话,查看work_mem参数,可以发现work_mem还是4MB

postgres=# show work_mem;
 work_mem 
----------
 4MB
(1 row)

3.set命令后添加 local关键字, 只在当前事务中修改,只在当前事务内有效:

test=# show work_mem;
 work_mem 
----------
 16MB
(1 row)
test=# begin;
BEGIN
test=# set local work_mem='8MB';
SET
test=# show work_mem;
 work_mem 
----------
 8MB
(1 row)
test=# commit;
COMMIT
test=# show work_mem;
 work_mem 
----------
 16MB

4.使用 reset恢复参数的默认值

再pg_settings字典reset_val字段表示了如果使用reset,则此参数恢复的默认值为多少

使用 reset 参数名 来恢复某个参数的默认值,使用 reset all来恢复所有的参数值.

test=# show work_mem;
 work_mem 
----------
 16MB
(1 row)
test=# reset work_mem;
RESET
test=# show work_mem;
 work_mem 
----------
 4MB
(1 row)
 
test=# reset all;
RESET

5.为特定的用户组设置参数

一.为特定的数据库里的所有的用户设置参数,例如为test数据库所有的连接设置work_mem为16MB:

test=# alter database test set work_mem='16MB';
ALTER DATABASE

二.为数据库中的某个特定用户设置参数.例如为brent用户,设置work_mem为2MB:

postgres=# alter role brent set work_mem='2MB';
ALTER ROLE

经过测试发现,如果你同时为数据库和用户设置了特定参数,那么以用户为准.例如上面的,如果我用brent用户连接到test数据库,那么我的work_mem应该为2MB:

postgres=# \c test brent
You are now connected to database "test" as user "brent".
test=> 
test=> 
test=> show work_mem;
 work_mem 
----------
 2MB

三.为某个特定用户连接到特定的数据库设置参数.例如为用户brent在数据库test中设置work_mem为8MB

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级别的参数.那么查询结果应该如下:

test=# select name,setting,source from pg_settings where name='work_mem';
  name  | setting | source 
----------+---------+----------
 work_mem | 16384  | database

其它的,例如设置了第三种:

test=# \c test brent
You 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。

以上为个人经验，希望能给大家一个参考，也希望大家多多支持。如有错误或未考虑完全的地方，望不吝赐教。

广告合作：本站广告合作请联系QQ：858582 申请时备注：广告合作（否则不回）
免责声明：本站资源来自互联网收集,仅供用于学习和交流,请遵循相关法律法规,本站一切资源不代表本站立场,如有侵权、后门、不妥请联系本站删除！
www.wwsws.com 伏龙阁资源网
39,976影音资源
44,792技术资源
21,817软件资源
651,128站长资源

更新日志

友情链接

杰晶网络 DDR爱好者之家 桃源资源网 杰网资源 富贵资源网 南强小屋 铁雪资源网 幽灵资源网 万梅资源网 狼山资源网 白云岛资源网 昆仑资源网 相思资源网 明霞山资源网 内蒙古资源网 黑松山资源网 茶园资源网 饿虎岗资源网 大旗谷资源网 常春岛资源网 岱庙资源网 兴国资源网 快活林资源网 蝙蝠岛资源网 帝王谷资源网 白云城资源网 伏龙阁资源网 清风细雨楼 天枫庄资源网 圆月山庄资源网 无争山庄资源网 神水资源网 移花宫资源网 神剑山庄资源网 无为清净楼资源网 金钱帮资源网 丐帮资源网 华山资源网 极乐门资源网 小李飞刀资源网 凤求凰客栈 风云阁资源网 金狮镖局 鸳鸯亭资源网 千金楼资源网 更多链接