postgresql 中的参数查看和修改方式
1.查看参数文件的位置
使用show命令查看,比较常用的showconfig_file.此还可以查看pg_settings数据字典.
test=#showconfig_file; config_file ------------------------------ /data/pgdata/postgresql.conf (1row) test=#showhba_file test-#; hba_file -------------------------- /data/pgdata/pg_hba.conf (1row) test=#showident_file; ident_file ---------------------------- /data/pgdata/pg_ident.conf
2.查看当前会话的参数值
可以使用show命令或者查看pg_settings字典.
使用showall可以查看全部的参数值.show参数名查看指定参数
test=#showall; -------------------------------------+------------------------------------------------------+------------------------------------------------------------------------------------------------------------------------------- allow_system_table_mods|off|Allowsmodificationsofthestructureofsystemtables. application_name|psql|Setstheapplicationnametobereportedinstatisticsandlogs. archive_command|test!-f/data/archive/%f&&cp%p/data/archive/%f|SetstheshellcommandthatwillbecalledtoarchiveaWALfile. archive_mode|on|AllowsarchivingofWALfilesusingarchive_command. archive_timeout|0|ForcesaswitchtothenextWALfileifanewfilehasnotbeenstartedwithinNseconds. array_nulls|on|EnableinputofNULLelementsinarrays. ... test=#showwork_mem; work_mem ---------- 4MB (1row) test=#\x Expandeddisplayison. test=#select*frompg_settingswherenamein('work_mem') test-#; -[RECORD1]---+---------------------------------------------------------------------------------------------------------------------- name|work_mem setting|4096 unit|kB category|ResourceUsage/Memory short_desc|Setsthemaximummemorytobeusedforqueryworkspaces. extra_desc|Thismuchmemorycanbeusedbyeachinternalsortoperationandhashtablebeforeswitchingtotemporarydiskfiles. 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_ctlreload即可.对于需要重启的,在修改完postgresql后需要执行pg_ctlrestart
对于9.5以后的版本,可以通过查看pg_file_settings查看你设置的参数是否生效.例如如果你设置了一个参数需要重启数据库才能生效或者设置错误,那么在此字典中会出现报错.
test=#select*frompg_file_settingswhereerrorisnotnull; sourcefile|sourceline|seqno|name|setting|applied|error -----------------------------------+------------+-------+-----------------+---------+---------+------------------------------ /data/pgdata/postgresql.auto.conf|4|22|max_connections|10000|f|settingcouldnotbeapplied (1row)
对于9.4以后的版本,你还可以使用altersystem命令修改参数.使用altersystem命令将修改postgresql.auto.conf文件,而不是postgresql.conf,这样可以很好的保护postgresql.conf文件,加入你使用很多altersystem命令后搞的一团糟,那么你只需要删除postgresql.auto.conf,再重新加载即可.
test=#showwork_mem; work_mem ---------- 4MB (1row) test=#altersystemsetwork_mem='8MB'; ALTERSYSTEM test=#showwork_mem; work_mem ---------- 4MB (1row)
查看postgresql.auto.conf:
[postgres@postgresql1pgdata]$catpostgresql.auto.conf #Donoteditthisfilemanually! #ItwillbeoverwrittenbytheALTERSYSTEMcommand. work_mem='8MB'
使用pg_ctlreload重新load配置文件,再查看参数值:
test=#showwork_mem; work_mem ---------- 8MB (1row)
2.直接使用set命令,在会话层修改,修改之后将被用于未来的每一个事务,只对当前会话有效:
test=# test=#setwork_mem='16MB'; SET test=#showwork_mem; work_mem ---------- 16MB (1row)
我们打开另外一个会话,查看work_mem参数,可以发现work_mem还是4MB
postgres=#showwork_mem; work_mem ---------- 4MB (1row)
3.set命令后添加local关键字,只在当前事务中修改,只在当前事务内有效:
test=#showwork_mem; work_mem ---------- 16MB (1row) test=#begin; BEGIN test=#setlocalwork_mem='8MB'; SET test=#showwork_mem; work_mem ---------- 8MB (1row) test=#commit; COMMIT test=#showwork_mem; work_mem ---------- 16MB
4.使用reset恢复参数的默认值
再pg_settings字典reset_val字段表示了如果使用reset,则此参数恢复的默认值为多少
使用reset参数名来恢复某个参数的默认值,使用resetall来恢复所有的参数值.
test=#showwork_mem; work_mem ---------- 16MB (1row) test=#resetwork_mem; RESET test=#showwork_mem; work_mem ---------- 4MB (1row) test=#resetall; RESET
5.为特定的用户组设置参数
一.为特定的数据库里的所有的用户设置参数,例如为test数据库所有的连接设置work_mem为16MB:
test=#alterdatabasetestsetwork_mem='16MB'; ALTERDATABASE
二.为数据库中的某个特定用户设置参数.例如为brent用户,设置work_mem为2MB:
postgres=#alterrolebrentsetwork_mem='2MB'; ALTERROLE
经过测试发现,如果你同时为数据库和用户设置了特定参数,那么以用户为准.例如上面的,如果我用brent用户连接到test数据库,那么我的work_mem应该为2MB:
postgres=#\ctestbrent Youarenowconnectedtodatabase"test"asuser"brent". test=> test=> test=>showwork_mem; work_mem ---------- 2MB
三.为某个特定用户连接到特定的数据库设置参数.例如为用户brent在数据库test中设置work_mem为8MB
test=#alterrolebrentindatabasetestsetwork_mem='8MB'; ALTERROLE
上面说的三种设置,优先级递增,也就是说,如果设置了1,2,3那么就以第3个为准,如果设置了1,2那么就是以2为准,以此类推.
pg对此的实现方法和当用户连接数据库的时候,立刻手动执行set命令的效果完全相同
查看你当前的参数值是从何处指定,可以通过查询pg_setttings中的source字段获取,例如如果设置了database级别的参数.那么查询结果应该如下:
test=#selectname,setting,sourcefrompg_settingswherename='work_mem'; name|setting|source ----------+---------+---------- work_mem|16384|database
其它的,例如设置了第三种:
test=#\ctestbrent Youarenowconnectedtodatabase"test"asuser"brent". test=>selectname,setting,sourcefrompg_settingswherename='work_mem'; name|setting|source ----------+---------+--------------- work_mem|8192|databaseuser
补充:postgresql重要参数解析及优化
1,max_connections200
最大客户端连接数。每个连接在后端都会对应相应的进程,耗费一定的内存资源。如果连接数上千,需要使用连接池工具。
2,shared_buffers25%oftotalmemory
数据库用于缓存数据的内存大小。该参数默认值很低(考虑不同的系统平台),需要调整。不宜太大,很多实践表明,大于1/3的内存会降低性能。
3,effective_cache_size50%-75%oftotalmemory
ThisisaguidelineforhowmuchmemoryyouexpecttobeavailableintheOSandPostgreSQLbuffercaches,notanallocation!这个参数只在查询优化器选择时使用,并不是实际分配的内存,该参数越大,查询优化器越倾向于选择索引扫描。
4,checkpoint_segments256checkpoint_completion_target0.9
checkponit_segmentswal个数达到多少个数checkponit,还有一个参数checkponit_timeout,控制最长多长时间checkpoint。对于写入比较大的数据库,该值越大越好。但是值越大,执行恢复的时间越长。
checkpoint_completion_target控制checkponitwrite分散写入,值越大越分散。默认值0.5,0.9是一个比较合适的值。
5,work_mem
用于排序,默认值即可。每个连接都会分配一定work_mem,这个是会实际分配的内存,不宜过大,默认值即可。如果要使用语句中有较大的排序操作,可以在会话级别设置该参数,setwork_men=‘2GB',提高执行速度。
6,maintanance_work_mem
维护性操作使用的内存。例如:vacuum,createindex,altertableaddforeignkey,restoringdatabasedumps.做这些操作时可以临时设置该值大小,加快执行速度。setsessionmaintanance_work_mem=‘2GB';
7,random_page_cost(默认值4)seq_page_cost(默认值1)
设置优化器获取一个随机页的cost,相比之下一个顺序扫描页的cost为1.
当使用较快的存储,如raidarrays,scsi,ssd时,可以适当调低该值。有利于优化器悬着索引扫描。ssd时,可以设置为2.
8,autovacuum
—maintenance_work_mem1-2GB
—autovacuum_max_workers
如果有多个小型表,分配更多的workers,更少的mem。
大型表,更多的men,更少的workers。
以上为个人经验,希望能给大家一个参考,也希望大家多多支持毛票票。如有错误或未考虑完全的地方,望不吝赐教。