PostgreSQL数据库的参数设置有哪些-创新互联
这篇文章主要讲解了“PostgreSQL数据库的参数设置有哪些”，文中的讲解内容简单清晰，易于学习与理解，下面请大家跟着小编的思路慢慢深入，一起来研究和学习“PostgreSQL数据库的参数设置有哪些”吧！
假设数据库主机OS为Linux 64bit,内存为8G,存储阵列使用RAID 5(带宽约为200MB/s,IOPS约为200),主机没有其他服务。
一、常规部分
listen_addresses
#PG默认监听本地连接,如需接受其他Client的连接请求,需修改为* listen_addresses = '*'
max_connections
#大连接数,默认为100,根据业务应用情况和主机配置设置 #由于PG使用多进程模式,如连接数大于一定数量(与机器配置相关)时,会因为进程上下文的频繁切换导致性能降低 #如需要支持数千个连接,可以考虑使用分库读写分离的方式,以及使用连接池组件(如PgBouncer) max_connections=256
查看当前连接数的SQL:
testdb=# SELECT sum(numbackends) FROM pg_stat_database; sum ----- 1 (1 row)
二、内存相关
假设机器内存为N,则需满足以下要求:
N > max_connections x work_mem + shared_buffers + temp_buffers + maintenance_work_mem + OS运行最小要求的内存
shared_buffers
#共享缓冲区,用于配置可用于Cache Data(包括Hot Data/Index等等)的内存大小 #一般设置为主机内存的25%-40% shared_buffers=3G
effective_cache_size
#剔除操作系统本身和其他应用程序可用的内存后,期望操作系统和数据库本身可用于缓存数据的内存大小 #该参数仅用于优化的estimate(评估)阶段,如果设置有误会影响优化器的判断,得出不合理的执行计划 #比如在只有512M的主机上,设置该参数为4G,查询一张有索引的大表时,如果访问的索引大小<4G,那么执行计划有可能会使用该索引,但实际上主机内存并不支持容纳这么大的索引,导致实际执行SQL时出现性能问题;同样的,如果设置的过小,执行计划可能不会选择用索引. effective_cache_size=4G
work_mem
#每个Session执行排序和建立散列表等操作时可使用的内存大小,默认1M #如需执行较大/复杂的排序或连接操作,建议增大此参数 #注意:示例中大连接数为256,如此参数设置为4M,那么在满载情况下使用的内存为4Mx256=1G work_mem=2M
maintenance_work_mem
#VACUUM, CREATE INDEX, and ALTER TABLE ADD FOREIGN KEY等操作可使用的内存大小,默认64M #由于这类操作并发数不会很大,增大此参数相对较为安全,如希望提升这些操作的性能,可适当加大此参数 maintenance_work_mem=256M
wal_buffers
#用于缓存WAL data,默认为-1(约为shared_buffers的1/32),最小为64KB,大为WAL segment大小(典型为16MB) #由于WAL data在事务commit时写入到日志文件中,在典型的主机配置下,假设每次Buffer都全满,写入的延迟约为wal_buffers/200,在0.32ms(64K)至81.92ms(16M)之间 wal_buffers=4M
三、性能相关
max_wal_size/min_wal_size
wal日志占用的大值和最小值,默认大为1G,最小为80M,用以替换先前的参数checkpoint_segments
checkpoint_segments
在参数在9.5+后已废弃
#1.该参数定义了写了多少个WAL Segment执行一次checkpoint,默认为3(典型的,即48M) #checkpoint最主要的功能是把缓存中脏数据写入到磁盘中(注意:这里的写是随机写,不是WAL的顺序写!) #频繁的checkpoint会影响数据库性能,在常规的场景下,使用默认值会极大的降低数据库性能 #假设该参数值为n,那么粗略上来说,每间隔(n*16MB/200MB)秒就会执行一次checkpoint #2.该参数会影响Recover,数值越大,人出现问题Recover的时间可能越长 #假设该参数值为n,那么极端情况下在产生n*16MB个WAL segment时出现宕机,下次启动时,粗略来说就需要n*16MB个WAL segment进行Recover checkpoint_segments=32
checkpoint_timeout
#checkpoint超时时间,默认为5分钟,大1d #在checkpoint_completion_target*checkpoint_timeout超时或者产生的WAL Segment超过checkpoint_segments时,执行checkpoint #增大此参数,会减少磁盘IO压力,但会延长Recover时间 checkpoint_timeout=8min
checkpoint_completion_target
#指定checkpoint的完成"目标",默认值为0.5,取值区间为0.0-1.0 #通过两个checkpoint间隔时间的百分比来衡量,也就是checkpoint_completion_target*checkpoint_timeout checkpoint_completion_target=0.9
random_page_cost
#随机读取Page的代价,以顺序读取为基准,默认值为4.0 #该参数会影响优化器选择执行计划,随机读取数据一般发生在通过Index访问数据的时候 #如数据存储在SSD这类随机读写友好的设备上,可以降低至2.0甚至更低 random_page_cost=4.0
四、运维相关
autovacuum
#是否启动自动vacuum,默认为on,常规情况下设置为on #vacuum的详细解析请参见先前章节,此处不再累述 autovacuum=on
log_XX
#log_destination:日志类型,包括stderr, csvlog, syslog, eventlog #建议设置为csvlog log_destination='csvlog' #logging_collector:如log_destination配置为csvlog,则该参数要求配置为on logging_collector=on #log_directory:日志存储路径,可使用相对路径,在$PGDATA目录下 log_directory='pg_log' #log_rotation_age:每个多长时间产生一个日志,如设置为1d,则每隔一天产生一个日志文件 #logging_collector=on时生效 log_rotation_age=1d #log_rotation_size:单个文件的大大小,超过此值,重新生成日志文件 #logging_collector=on时生效 log_rotation_size=16MB #log_min_duration_statement:记录执行时长>此值定义的SQL语句 log_min_duration_statement=1s
五、谨慎使用
synchronous_commit
#是否等待WAL数据写入到磁盘后才返回成功,可选的值为on, remote_apply, remote_write, local, off,默认为on #synchronous_commit设置为off,可能会导致虽然成功但实际上没有持久化的事务 synchronous_commit=on
commit_delay/commit_siblings
#commit_delay:事务提交后，日志写到wal_buffer至wal_buffer中的内容写入磁盘的时间间隔 #commit_siblings:触发commit_delay等待的并发事务数,如并发事务数<该值,则commit_delay无用 #这两个参数用于提升在高并发非只读事务的情况下的性能,可以让buffer一次可以刷出较多的事务(Bulk Flush的效果) #但同样的,如果出现极端情况,buffer来不及持久化的时候出现崩溃,将会导致数据丢失 commit_delay=0 commit_siblings=5
fsync
#设置为on时，日志缓冲区刷盘时确认已经写入磁盘才会返回; #设置为off时，由OS负责调度，能更好利用OS的缓存机制，提高IO性能。 #利用异步写入的机制提升性能,但同样存在数据丢失的风险 fsync=on
感谢各位的阅读，以上就是“PostgreSQL数据库的参数设置有哪些”的内容了，经过本文的学习后，相信大家对PostgreSQL数据库的参数设置有哪些这一问题有了更深刻的体会，具体使用情况还需要大家实践验证。这里是创新互联，小编将为大家推送更多相关知识点的文章，欢迎关注！
当前题目：PostgreSQL数据库的参数设置有哪些-创新互联
网页URL：http://scyanting.com/article/gegio.html
