本文共 3051 字，大约阅读时间需要 10 分钟。
PostgreSQL 资源使用（除 WAL 之外）
在 PostgreSQL 数据库的性能优化中，资源配置是至关重要的一环。本文将从内存、硬盘、内核资源等多个方面，详细介绍如何优化 PostgreSQL 的资源使用。
内存 Memory
shared_buffers
- 默认值：shared_buffers = 128MB
- 最小值：128KB
- 作用：这是一个关键参数，决定了数据库共享缓冲区的大小。PostgreSQL 在读取数据时，首先将数据从磁盘读取到内存中，然后进行操作，最后再将数据写回磁盘。shared_buffers 用于存放从磁盘读取的数据。
- 建议：将 shared_buffers 设置在 25%~40% 的范围内。需要注意的是，Linux 和 Windows 对内存管理的方式不同。在 Linux 中，需要配置 /etc/sysctl.conf 中的 kernel.shmmax 参数。一个简单的计算方法是 kernel.shmmax = postgres shared_buffers + 32MB。
huge_pages
- 默认值：huge_pages = try（使用）、off（不使用）、try（尽量使用）中的一种。
- 作用：决定了数据库是否使用大页内存。大页内存需要操作系统的支持。建议将 vm.nr_hugepages 设置为 2 倍的 shared_buffers 大小，以确保其足够大。
temp_buffers
- 默认值：temp_buffers = 8MB
- 最小值：800KB
- 作用：用于存放数据库会话访问临时表数据的内存缓冲区。临时表缓冲区存放在每个数据库进程的私有内存中，而不是共享内存中。
max_prepared_transactions
- 默认值：max_prepared_transactions = 0
- 作用：决定了能够同时处于 prepared 状态的事务的最大数量。值为 0 时，数据库会关闭 prepared 事务的特性。通常建议与 max_connections 的值相同。每个事务消耗 600 字节共享内存。
maintenance_work_mem
- 默认值：maintenance_work_mem = 64MB
- 作用：决定了数据库维护操作（如 VACUUM）使用的内存空间大小。建议将其设置为总内存大小的 1/4 或 autovacuum_max_workers 的值。
work_mem
- 默认值：work_mem = 4MB
- 作用：决定了数据库排序操作和哈希表使用的内存缓冲区大小。合理设置 work_mem 对提高查询性能至关重要。建议根据业务量和系统监控数据进行调整。
autovacuum_work_mem
- 默认值：autovacuum_work_mem = -1
- 作用：如果设置为 -1，则使用 maintenance_work_mem 的值。建议根据实际需求进行调整。
max_stack_depth
- 默认值：max_stack_depth = 2MB
- 作用：决定了数据库进程运行时的栈空间最大值。建议将其设置为比 OS 的栈限制小 1MB。
dynamic_shared_memory_type
- 默认值：dynamic_shared_memory_type = posix
- 作用：决定了动态共享内存的类型。支持 posix、sysv、windows 等多种类型。
replacement_sort_tuples
- 默认值：replacement_sort_tuples = 150000
- 作用：影响了替换选择排序的记录数。建议根据 CPU 缓存大小进行调整。
硬盘
temp_file_limit
- 默认值：temp_file_limit = -1
- 作用：限制每个线程的临时文件大小。-1 表示不限制。
内核资源 Kernel Resource Usage
max_files_per_process
- 默认值：max_files_per_process = 1000
- 作用：限制数据库进程打开文件的最大数量。建议根据实际文件数量进行调整。
shared_preload_libraries
- 默认值：shared_preload_libraries = ''
- 作用：指定共享预加载库的路径。
Cost-Based Vacuum Delay
vacuum_cost_delay
- 默认值：vacuum_cost_delay = 10ms
- 作用：控制 VACUUM 操作的延迟。值越大，VACUUM 操作的延迟越大。
vacuum_cost_page_hit、vacuum_cost_page_miss、vacuum_cost_page_dirty
- 默认值：分别为 1、10、20 credits
- 作用：控制 VACUUM 操作对页面访问、页面缺页和页面脏页的成本。
vacuum_cost_limit
- 默认值：vacuum_cost_limit = 200 credits
- 作用：限制 VACUUM 操作的总成本。
Background Writer
bgwriter_delay
- 默认值：bgwriter_delay = 200ms
- 作用：控制后台写入进程的等待时间。建议在 10ms~10s 之间。
bgwriter_lru_maxpages
- 默认值：bgwriter_lru_maxpages = 100
- 作用：决定后台写入进程每次写入的最大数据量。
bgwriter_lru_multiplier
- 默认值：bgwriter_lru_multiplier = 2.0
- 作用：决定后台写入进程每次申请缓冲区的倍率。
bgwriter_flush_after
- 默认值：bgwriter_flush_after = 512kB
- 作用：控制后台写入进程在写入大块数据时是否同步刷盘。
Asynchronous Behavior
max_worker_processes
- 默认值：max_worker_processes = 8
- 作用：决定数据库集群中可以同时运行的最大工作进程数量。
max_parallel_workers_per_gather
- 默认值：max_parallel_workers_per_gather = 2
- 作用：决定每个 GATHER 节点可以启用多少个工作进程。
max_parallel_workers
- 默认值：max_parallel_workers = 8
- 作用：决定数据库可以同时执行的最大并行查询工作线程数。
effective_io_concurrency
- 默认值：effective_io_concurrency = 1
- 作用：控制磁盘 I/O 操作的并发数。建议根据磁盘类型和负载进行调整。
backend_flush_after
- 默认值：backend_flush_after = 0
- 作用：如果设置为 0，则禁用后台刷盘操作。
替换快照阈值
old_snapshot_threshold
- 默认值：old_snapshot_threshold = -1
- 作用：决定快照可以保持的最久时间。-1 表示禁用。
通过合理配置以上参数，可以显著提升 PostgreSQL 的性能表现。建议根据实际系统负载和业务需求，对每个参数进行调整，并通过监控工具（如 pg_stat_bgwriter）持续优化数据库性能。
转载地址：http://zvxfk.baihongyu.com/