PostgreSQL性能优化

### **一、PostgreSQL参数调优**

|  序号   | 参数名称  |  默认值  | 优化值  | 参数说明 |
|  :----:  | ----  | ----  | ----  | ----  |
|1 | listen_addresses | localhost | * | 默认只有本地访问，开启允许所有IP地址访问 |
|2 | max_connections | 100 | 1000 | 系统允许的最大连接数，上限跟服务器配置相关|
|3 | superuser_reserved_connections | 3 | 	13 | 	为超级用户保留的连接数|
|4 | shared_buffers | 128MB | 16G | PostgreSQL缓存（数据库缓冲区），建议内存的1/4，不超过内存的1/2|
|5 | huge_pages | try | try | 应用大页，建议shared_buffers超过32GB时开启|
|6 | work_mem | 4MB | 8MB | 设置在写入临时磁盘文件之前查询操作（例如排序或哈希表）可使用的最大内存容量。如果指定值时没有单位，则以KB为单位。默认是4MB|
|7 | effective_cache_size | 4GB | 16GB | 可用OS的缓存大小，缓存值1/3~1/2|
|8 | max_stack_depth  | 2MB | 4M | 进程STACK所占的空间的最大值，复杂操作建议配置8MB|
|9 | maintenance_work_mem | 64MB | | 维护时可以使用的内存大小，这里定义的内存是被VACUUM等消耗资源较多的命令调用时应用，把该值调大，能放慢命令的执行，建议大于256MB|
|10 | vacuum_cost_limit | 200 | 500 | 清理delete后的空间，此时对io影响较大，增加该值可以缩小对性能的影响|
|11 | max_worker_processes | 8 | 128 | 最大并发过程数，parallel worker等都是worker process，该值需要设置的足够大|
|12 | max_parallel_workers_per_gather | 2 | 4 | 每个执行节点的最大并行处理过程数，应用并行查询时设置该值大于1，不建议超过主机cores-2|
|13 | max_parallel_workers | 8 | 8 | 并行查询时，最大线程数|
|14 | wal_buffers | 4MB	 | | 用于wal的内存大小，设置为shared_buffers/32,设置为-1表示按shared_buffers计算|
|15 | max_wal_size | 1GB | 16G | 该值越小，wal日志写入量越大，wal日志恢复时间越长|
|16 | min_wal_size | 80MB | 1G | 建议值shared_buffers/2|
|17 | checkpoint_timeout | 5min | 10min | checkpoint超时，wal查看写入磁盘时间，视情况可以加大。|
|18 | checkpoint_completion_target | 0.5 | 0.5 | 指定检查点完成的目标，作为检查点之间总时间的一部分。|
|19 | log_destination | stderr | csvlog | 文本格式日志|
|20 | log_truncate_on_rotation | on | on | 文本日志收集|

### **二、postgresql参数详解**
#### **(1) shared_buffers**

PostgreSQL既使用自身的缓冲区，也使用内核缓冲IO。这意味着数据会在内存中存储两次，首先是存入PostgreSQL缓冲区，然后是内核缓冲区。这被称为双重缓冲区处理。对大多数操作系统来说，这个参数是最有效的用于调优的参数。此参数的作用是设置PostgreSQL中用于缓存的专用内存量。
  shared_buffers的默认值设置得非常低，因为某些机器和操作系统不支持使用更高的值。但在大多数现代设备中，通常需要增大此参数的值才能获得最佳性能。
建议的设置值为机器总内存大小的25％，但是也可以根据实际情况尝试设置更低和更高的值。实际值取决于机器的具体配置和工作的数据量大小。举个例子，如果工作数据集可以很容易地放入内存中，那么可以增加shared_buffers的值来包含整个数据库，以便整个工作数据集可以保留在缓存中。
   在生产环境中，将shared_buffers设置为较大的值通常可以提供非常好的性能，但应当时刻注意找到平衡点。

#### **(2) wal_buffers**

PostgreSQL将其WAL（预写日志）记录写入缓冲区，然后将这些缓冲区刷新到磁盘。由wal_buffers定义的缓冲区的默认大小为16MB，但如果有大量并发连接的话，则设置为一个较高的值可以提供更好的性能。

#### **(3) effective_cache_size**

effective_cache_size提供可用于磁盘高速缓存的内存量的估计值。它只是一个建议值，而不是确切分配的内存或缓存大小。它不会实际分配内存，而是会告知优化器内核中可用的缓存量。在一个索引的代价估计中，更高的数值会使得索引扫描更可能被使用，更低的数值会使得顺序扫描更可能被使用。在设置这个参数时，还应该考虑PostgreSQL的共享缓冲区以及将被用于PostgreSQL数据文件的内核磁盘缓冲区。默认值是4GB。
查看当前effective_cache_size的值：
#### **(4) work_mem**

此配置用于复合排序。内存中的排序比溢出到磁盘的排序快得多，设置非常高的值可能会导致部署环境出现内存瓶颈，因为此参数是按用户排序操作。如果有多个用户尝试执行排序操作，则系统将为所有用户分配大小为work_mem *总排序操作数的空间。全局设置此参数可能会导致内存使用率过高，因此强烈建议在会话级别修改此参数值。默认值为4MB。
查看当前work_mem的值：

#### **(5) maintenance_work_mem**
  maintenance_work_mem是用于维护任务的内存设置。默认值为64MB。设置较大的值对于VACUUM，RESTORE，CREATE INDEX，ADD FOREIGN KEY和ALTER TABLE等操作的性能提升效果显著。
查看当前maintenance_work_mem的值：

#### **(6) synchronous_commit**

此参数的作用为在向客户端返回成功状态之前，强制提交等待WAL被写入磁盘。这是性能和可靠性之间的权衡。如果应用程序被设计为性能比可靠性更重要，那么关闭synchronous_commit。这意味着成功状态与保证写入磁盘之间会存在时间差。在服务器崩溃的情况下，即使客户端在提交时收到成功消息，数据也可能丢失。
查看当前synchronous_commit的设置值：

#### **(7) checkpoint_timeout和checkpoint_completion_target**
  PostgreSQL将更改写入WAL。检查点进程将数据刷新到数据文件中。发生CHECKPOINT时完成此操作。这是一项开销很大的操作，整个过程涉及大量的磁盘读/写操作。用户可以在需要时随时发出CHECKPOINT指令，或者通过PostgreSQL的参数checkpoint_timeout和checkpoint_completion_target来自动完成。
  checkpoint_timeout参数用于设置WAL检查点之间的时间。将此设置得太低会减少崩溃恢复时间，因为更多数据会写入磁盘，但由于每个检查点都会占用系统资源，因此也会损害性能。此参数只能在postgresql.conf文件中或在服务器命令行上设置。
  checkpoint_completion_target指定检查点完成的目标，作为检查点之间总时间的一部分。默认值是 0.5。 这个参数只能在postgresql.conf文件中或在服务器命令行上设置。高频率的检查点可能会影响性能。

#### **(8) max_connections**

允许客户端连接的最大数目

#### **(9) fsync**

强制把数据同步更新到磁盘,如果系统的IO压力很大，把改参数改为off

在fsync打开的情况下，优化后性能能够提升30%左右。因为有部分优化选项在默认的SQL测试语句中没有体现出它的优势，如果到实际测试中，提升应该不止30%。
测试的过程中，主要的瓶颈就在系统的IO，如果需要减少IO的负荷，最直接的方法就是把fsync关闭，但是这样就会在掉电的情况下，可能会丢失部分数据。

#### **(10) commit_delay**

事务提交后，日志写到wal log上到wal_buffer写入到磁盘的时间间隔。需要配合commit_sibling。能够一次写入多个事务，减少IO，提高性能

#### **(11) commit_siblings**

设置触发commit_delay的并发事务数，根据并发事务多少来配置。减少IO，提高性能

> **注意：**
> 并非所有参数都适用于所有应用程序类型。某些应用程序通过调整参数可以提高性能，有些则不会。必须针对应用程序及操作系统的特定需求来调整数据库参数。

An's Blog

Navigation

Recent Posts

Friend Links