`
lykops
  • 浏览: 86250 次
  • 性别: Icon_minigender_1
  • 来自: 深圳
文章分类
社区版块
存档分类
最新评论

PostgreSQL配置文件--资源使用(除WAL外)

 
阅读更多

2 资源使用(除WAL外) RESOURCE USAGE (except for WAL)

2.1 内存 Memory

2.1.1 shared_buffers

数字型
默认: shared_buffers = 128MB ,最小值128KB
重启数据库生效
影响postgresql性能的重要参数之一
共享缓冲区大小。postgresql对数据操作时都要先将数据从磁盘读取到内存中,然后进行更新,最后再将数据写回磁盘。
shared_buffers的功能就是用于存放从磁盘读取的数据。
根据文档参数的设置范围一般在25%~40%之间。
windows与linux对内存的管理方式不同,在linux中需要注意共享段大小的设置(/etc/sysctl.conf中的kernel.shmmax)。
kernel.shmmax决定了进程可调用的最大共享内存数量。
简单的计算方法是kernel.shmmax=postgres shared_buffers + 32 MB

2.1.2 huge_pages

字符型
默认: huge_pages = try ,on(使用)、off(不使用)、try(尽量使用)三选一。
重启数据库生效
数据库是否使用大页
需要OS的支持,配置vm.nr_hugepages*2MB大于shared_buffers.

2.1.3 temp_buffers

数字型
默认: temp_buffers = 8MB ,最小值800KB
称之为临时缓冲区,用于存放数据库会话访问临时表数据
可以在单独的session中对该参数进行设置,尤其是需要访问比较大的临时表时,将会有显著的性能提升。
临时表缓冲区存放在每个数据库进程的私有内存中,而不是存放在数据库的共享内存中。

2.1.4 max_prepared_transactions

数字型
默认: max_prepared_transactions = 0
重启数据库生效
它决定能够同时处于prepared状态的事务的最大数目(参考PREPARE TRANSACTION命令)。
值为0,则将数据库将关闭prepared事务的特性。
通常应该和max_connections的值一样大。
每个事务消耗600字节共享内存。
注意:设置为非0值不可取,除非知道你在做什么。it is not advisable to set max_prepared_transactions nonzero ,unless you actively intend to use prepared transactions.

2.1.5 maintenance_work_mem

数字型
默认: maintenance_work_mem = 64MB
影响postgresql性能的重要参数之一
称之为维护工作内存,主要是针对数据库的维护操作或者语句,决定数据库的维护操作使用的内存空间的大小。
只在VACUUM、CREATE INDEX和ALTER TABLE ADD FOREIGN KEY等操作时用到,使用频率不高,但往往消耗较多资源,应尽快让他快速执行完毕。
在对整个数据库进行VACUUM或者较大的index进行重建时,适当的调整该参数非常必要。
该值加大,可以缩短VACUUM数据库和从dump文件中恢复数据库需要的时间。
它存放在每个数据库进程的私有内存中,而不是存放在数据库的共享内存中。
最小值为1M,建议为总内存大小的1/4/autovacuum_max_workers

2.1.6 work_mem

数字型
默认: work_mem = 4MB 
最小值为64K,通常设置为实际RAM的2%-4%
影响postgresql性能的重要参数之一
数据库的排序操作和哈希表使用的内存缓冲区的大小,合适的work_mem大小能够保证这些操作在内存中进行。
ORDER BY、DISTINCT和merge连接会使用排序操作。哈希表在Hash连接、hash聚集函数和用哈希表来处理IN谓词中的子查询中被使用。

这些操作需要的内存超过work_mem,会把超出部分放入swap,设置太小可能直接使用swap,设置太大可能让其他操作使用swap。
合适的大小对数据库性能至关重要。

在实际的生产环境中,要对系统监控数据进行分析,作出最好的选择。
大致有两种方式:
第一种是可以根据业务量的大小和类型,一般语句运行时间,来粗略的估计一下。
第二种方式是通过对数据库的监控,数据采集,然后计算其大小。
在实际维护中通过explain analyze分析语句的work_mem大小是否合适。在语句中设置work_mem可以充分利用内存,提高语句的执行效率。
work_mem参数对系统的性能是如此的重要,让其实时的适应数据库的运行状况显的不太可能,但可以通过对数据库运行周期的监控,总结相应的数据,然后定制一个专用的脚本,专门用来修改work_mem的大小,使其阶段性的更加适应系统的状况,不失为一种好的方法。

对于work_mem内存分配时还要考虑数据库的并发情况,max_connections决定了系统的最大的并发连接数。
不论如何调整work_mem都要考虑max_connections*work_mem+shared_buffers+temp_buffers+maintenance_work_mem+OS所需内存不能够超过整个的RAM大小,这是非常重要的。

2.1.7 autovacuum_work_mem

数字型
默认: autovacuum_work_mem = -1
最小1MB,如果是-1,那么使用maintenance_work_mem设置的值

2.1.8 max_stack_depth

数字型
默认: max_stack_depth = 2MB
可动态修改,只有数据库超级用户才能修改,。
决定一个数据库进程在运行时的STACK所占的空间的最大值。数据库进程在运行时,会自动检查自己的STACK大小是否超过max_stack_depth,如果超过,会自动终止当前事务。应该比OS STACK(ulimit –s)的上限小1MB。

2.1.9 dynamic_shared_memory_type

字符型
默认: dynamic_shared_memory_type = posix ,posix、sysv、windows、mmap、''五选一。
动态共享内存类型,为空表示禁用

2.1.10 replacement_sort_tuples

数字型
默认: replacement_sort_tuples = 150000
limits use of replacement selection sort
replacement_sort_tuples建议设置的值与CPU cache size有关,CPU cache size越大,可以调大这个值。 否则建议不要修改它。
初次扫描的replacement_sort_tuples条记录使用replacement selection算法(以装下一个work_mem单位为止,所以可能实际的replacement selection记录数小于replacement_sort_tuples的设置),超出的tuples使用quicksort。

2.2 硬盘

temp_file_limit

数字型
默认: 
temp_file_limit = -1
限制每个线程的临时文件大小,单位为KB
-1不限制

2.3 内核资源 Kernel Resource Usage

2.3.1 max_files_per_process

数字型
默认: max_files_per_process = 1000 ,最少25
重启数据库生效
如果数据库有非常多小文件(比如有几十万以上的表,还有索引等,并且每张表都会被访问到时),建议FD可以设多一些,避免进程需要打开关闭文件。
但是不要大于系统设置的ulimit -n(open files)

2.3.2 shared_preload_libraries

数字型
默认: 
shared_preload_libraries = ''
重启数据库生效

2.4 Cost-Based Vacuum Delay

vacuum_cost_delay = 10        #0-100 milliseconds
vacuum_cost_page_hit = 1               # 0-10000 credits
vacuum_cost_page_miss = 10             # 0-10000 credits
vacuum_cost_page_dirty = 20            # 0-10000 credits
vacuum_cost_limit = 200                # 1-10000 credits

2.5 Background Writer

2.5.1 bgwriter_delay

数字型
默认: bgwriter_delay = 200ms,设定值在10-10000ms之间,单位是毫秒。
重启数据库生效
backgroud writer进程连续两次flush数据的间隔。
后台写数据库进程每次完成写数据到物理文件中的任务以后,就会睡眠bgwriter_delay指定的时间。
postgresql中脏数据的写入不仅仅是由backgroud writer参数决定的,checkpoint也对脏数据的写入进行了控制。
backgroud writer进程的间隔是以毫秒计算,而checkpoint的间隔时间相对要很长。
所以bgwriter进程如果能够实现周期均匀数据量合适的写入数据,在减少IO的次数的同时还能够提升命中率,对checkpoint也能够减轻压力,不至于发生集中的写入操作,而造成IO使用的高值,也可以说进一步降低了对系统资源的考验。
bgwriter_delay的值应该是10的倍数,如果不是10的倍数,数据库会自动将参数的值设为最接近指定值的10的倍数。

2.5.2 bgwriter_lru_maxpages

数字型
默认: bgwriter_lru_maxpages =  100,设定值为0-1000之间,单位buffers
重启数据库生效
backgroud writer进程每次写的最多数据量。
如果脏数据量小于该数值时,写操作全部由backgroud writer进程完成;反之,大于的部分将有server process进程完成。
设置该值为0时表示禁用backgroud writer写进程,完全有server process来完成;
配置为-1时表示所有脏数据都由backgroud writer来完成。(这里不包括checkpoint操作)

2.5.3 bgwriter_lru_multiplier

数字型
默认: bgwriter_lru_multiplier =  2.0,设定值为0-10.0之间(0-10.0 multiplier on buffers scanned/round)
重启数据库生效
一般使用默认值即可,不需要修改这个参数。
表示每次往磁盘写脏数据块量,当然该值必须小于bgwriter_lru_maxpages。
设置太小时需要写入的脏数据量大于每次写入的数据量,这样剩余需要写入磁盘的工作需要server process进程来完成,将会降低性能;
值配置太大说明写入的脏数据量多于当时所需buffer的数量,方便了后面再次申请buffer工作,同时可能出现IO的浪费。
bgwriter的最大数据量计算方式:1000/bgwriter_delay*bgwriter_lru_maxpages*8K=最大数据量。
参数值依据系统状态而设置,通过监控系统的运行状况,进行分析总结。参考数据字典pg_stat_bgwriter,\d pg_stat_bgwriter

2.5.4 bgwriter_flush_after

数字型
默认:bgwriter_flush_after = 512kB ,0表示禁止
当bgwriter进程写脏数据块量超过配置阈值时,触发调用OS sync_file_range,告诉os backend flush 线程异步刷盘。从而削减os dirty page堆积。  
IO很好的机器,不需要考虑平滑调度

2.6 Asynchronous Behavior

2.6.1 max_worker_processes

数字型
默认: max_worker_processes = 8
重启数据库生效
整个数据库集群同时能开启最大进程
注意:
    如果有standby,standby的参数必须大于等于主库的参数值。
    如果设置为0,表示不允许并行。

2.6.2 max_parallel_workers_per_gather

数字型
默认: max_parallel_workers_per_gather = 2
决定了每个Gather node最多允许启用多少个work process。
注意
    在OLTP业务系统中,不要设置太大,因为每个worker都会消耗同等的work_mem等资源,争抢会比较厉害。
    建议在OLAP中使用并行,并且做好任务调度,减轻冲突。

2.6.3 max_parallel_workers

数字型
默认: max_parallel_workers = 8
maximum number of max_worker_processes that can be used in parallel queries
最大并行查询工作线程数

2.6.4 effective_io_concurrency

数字型
默认:  effective_io_concurrency = 1 取值范围1-1000,0表示禁用预读取,不使用异步IO
异步IO并发数
目的是充分发挥块设备的吞吐能力,让块设备处于更繁忙的工作状态(一次连续摄取更多的块),而不是等用户进程需要数据时再读取。
如果数据库并发连接(或者活跃会话)足够时,并且块设备处于繁忙状态,那么没有必要开启异步IO,因为开了也没什么用,块设备已经很忙了。
目前PostgreSQL的bitmap heap scan支持异步IO,因为bitmap heap scan是按顺序读取堆表的数据块的,对于机械硬盘,bitmap heap scan异步IO效率可以得到充分的发挥。(实际上全表扫描也适合异步IO。)
异步IO的参数effective_io_concurrency如何设置?如果是磁盘阵列,根据表空间所在的块设备进行设置,例如RAID0/10设置为磁盘个数,而RAID5或者其他RAID,设置为实际的数据盘个数(如10块硬盘组成raid5则设置为9)。
仅仅当操作系统支持posix时,才能使用异步IO。
Sets the number of concurrent disk I/O operations that PostgreSQL expects can be executed simultaneously.   
Raising this value will increase the number of I/O operations that any individual PostgreSQL session attempts to initiate in parallel.   
The allowed range is 1 to 1000, or zero to disable issuance of asynchronous I/O requests. Currently, this setting only affects bitmap heap scans.

2.6.5 old_snapshot_threshold

数字型
默认: old_snapshot_threshold = -1 ,取值范围1min-60d、-1表示禁用,0表示立即
重启数据库生效
Time before a snapshot is too old to read pages changed after the snapshot was taken.

2.6.6 backend_flush_after

backend_flush_after = 0                # measured in pages, 0 disables
分享到:
评论

相关推荐

    postgresql-9.1.2-1-windows

    10. **Readme-说明.htm**:这个文件通常包含了详细的安装指南、系统需求、配置步骤以及可能遇到的问题解决方案,对于初学者来说是极有帮助的资源。 在实际应用中,PostgreSQL 9.1.2不仅适合小型项目,也能够胜任...

    开源项目-wal-g-wal-g.zip

    - **安装与配置**:首先,你需要在PostgreSQL服务器上安装wal-g,然后配置相应的云存储凭证。 - **创建全量备份**:运行wal-g backup-push命令,wal-g会自动处理WAL段并创建一个全量备份。 - **执行增量备份**:...

    postgresql--内核分析--多进程结构

    - `src/backend/utils/misc/guc.c`文件包含了配置参数的处理逻辑,这些参数控制着进程的行为。 - `src/backend/utils/misc/pg_shmem.c`文件中实现了共享内存的相关功能,这对于多进程之间的通信非常重要。 #### 五...

    postgresql自动增备pitr脚本

    - 设置恢复配置文件(`recovery.conf`),指定起始恢复的时间戳或XLOG位置。 - 监控和管理备份及WAL日志的生命周期,确保有足够的空间和历史记录。 3. **PITR的实现**: - 使用`pg_start_backup`和`pg_stop_...

    linux中pg11的包

    - 主要配置文件位于`/etc/postgresql/11/main/postgresql.conf`,包括端口号、日志设置、内存使用等。 5. **用户与权限**: - 默认创建了一个名为`postgres`的超级用户,可通过`psql -U postgres`进入。 - 可以...

    PGConf.CN2019大会资料 培训PPT--Ibrar-PostgreSQL Conf Beijing 2019-Performance-Tuning

    除了 PostgreSQL 自身的配置外,对 Linux 内核进行适当的调优也是必不可少的。这些调优措施包括但不限于: - **文件系统缓存**:通过调整 Linux 的文件系统缓存策略,可以更高效地利用内存资源,减少磁盘 I/O 操作。...

    postgresql-9.4.26-1-windows-x64.exe

    这个压缩包文件"postgresql-9.4.26-1-windows-x64.exe"是PostgreSQL 9.4.26版本的64位Windows安装程序。该版本发布于2018年,虽然现在已经有些过时,但仍然是许多项目和企业中稳定运行的基础。 PostgreSQL 9.4版本...

    Postgresql修炼之道 pdf

    了解配置文件 pg_hba.conf 和 postgresql.conf 的设置对于数据库的正常运行至关重要。 5. 高级特性:包括但不限于表分区、异步复制、流复制、写前日志(WAL)机制、表空间、物化视图等,这些特性使Postgresql能够应对...

    postgresql 10 high performance PDF、epub电子书和各章节中使用到的SQL语句文件

    书中有专门的章节讲解如何根据硬件资源和应用需求调整配置,如work_mem、shared_buffers和effective_io_concurrency等。 4. **并行查询**:PostgreSQL 10引入了并行查询功能,使得复杂的查询可以在多个CPU核心上...

    postgresql8.3中文手册

    - **recovery.conf**:备库上的配置文件,用于定义复制行为,包括从哪个主库恢复,何时启动等。 3. **复制命令和选项**: - `pg_basebackup`:用于获取一个初始的数据集,作为复制的起点。 - `-X` 和 `--xlog-...

    postgresql参数解析

    PostgreSQL的主配置文件通常名为`postgresql.conf`,这是设置数据库服务器参数的主要途径。在描述中提到的`postgresql 参数注释.conf`可能是这个配置文件的一个带有注释版本,对于理解和调整参数非常有帮助。 2. *...

    Postgresql之体系结构篇

    在服务器端,PostgreSQL的数据文件和配置文件有其特定的存储位置和结构。数据文件通常保存在数据目录中,其中包括基础文件、临时文件、索引文件、表文件等。配置文件则包含了控制服务器行为的参数设置,如postgresql...

    Troubleshooting PostgreSQL

    - 使用PostgreSQL的`pg_waldump`工具来检查WAL日志,及时发现潜在问题。 - 启用`fsync`和`full_page_writes`选项以减少数据损坏的风险。 ##### 3. **连接超时** - **原因分析**:网络延迟、服务器负载过高等因素...

    PostgreSQL流复制原理

    - `postgresql.conf`:这个配置文件包含了数据库服务器的主要配置。 - `synchronous_commit`:这个参数控制写事务返回成功的条件,有off、local、remote_write、on四种设置。 - `recovery.conf`:这个文件指示...

    postgresql 9 standby

    - 设定`archivecommand`为具体的归档命令,例如使用`cp`或更高效的`rsync`命令,将WAL文件归档到指定目录。 - 将`wallevel`设置为`archive`,确保WAL信息完整记录,便于同步。 ##### 3.2 进入备份状态 重启primary...

    PostgreSQL 12流复制实施手册

    2. **修改配置文件**: 编辑`postgresql.conf`,开启`hot_standby`和` wal_level = replica`,以允许备机接收主节点的WAL日志并进入热备份状态。 3. **创建复制用户**: 创建一个专门用于复制的用户,设置相应的权限,...

    Postgresql之体系结构全掌握

    - **postgresql.conf**、**pg_hba.conf**等:配置文件。 3. **数据库和表空间** - **Database**:逻辑上的数据库容器,包含多个表格和其他对象。 - **Tablespaces**:物理上可以分布在不同的文件系统上,允许...

    postgresql 日常维护、监控、排错、优化

    - **清理WAL**:通过合理配置`wal_keep_segments`参数来控制WAL文件的保留时间。 - **清理审计日志**:根据需求设置日志保留策略,避免占用过多磁盘空间。 - **进程维护**:定期检查内存使用情况,及时释放不必要的...

    顶级PostgreSQL DBA日常工作分享

    1. WAL(Write-Ahead Logging)机制:PostgreSQL使用预写日志进行事务的持久化,确保数据的完整性,DBA需要理解和管理WAL文件。 2. 触发器和存储过程:了解并编写触发器和存储过程,以便在数据库层面对数据进行有效...

Global site tag (gtag.js) - Google Analytics