35. PostgreSQL 基本调优

PostgreSQL 是一个非常通用的数据库系统，能够在非常低资源的环境以及与各种其他应用程序共享的环境中高效运行。为了确保它能在许多不同的环境中正常运行，默认配置非常保守，不太适合高性能的生产数据库。再加上地理空间数据库具有不同的使用模式，并且数据往往由比非地理空间数据库更少、更大的记录组成，因此您可以看到默认配置并不完全适合我们的目的。

所有这些配置参数都可以在 postgresql.conf 数据库配置文件中编辑。这是一个普通的文本文件，可以使用任何文本编辑器编辑。更改在服务器重新启动后才会生效。

本节介绍一些可以调整的配置参数，以便使地理空间数据库更适合生产环境。

注意

这些值仅为建议值；每个环境都会有所不同，需要进行测试以确定最佳配置。但是本节应该能帮助您有一个良好的开端。

35.1. shared_buffers

设置数据库服务器用于共享内存缓冲区的内存量。顾名思义，这些缓冲区在后端进程之间共享。默认值通常对于生产数据库来说是严重不足的。

默认值: 通常为 32MB

建议值: 数据库内存的约 75%，最大约为 2GB

35.2. effective_cache_size

除了 PostgreSQL 为 shared_buffers 设置的内存外，查询计划器还会考虑操作系统可能已缓存为虚拟文件系统一部分的磁盘块数量。对于具有大量内存的系统，这可能非常大。effective_cache_size 大约是机器上的内存量，减去 shared_buffers，再减去 work_mem 乘以预期连接数，再减去机器上运行的任何其他进程所需的任何内存，再减去大约 1GB 用于其他随机操作系统需求。数据库不会直接使用额外的缓存，但它会计算计划，预期操作系统已在约那么多内存中缓存了文件系统数据。

默认值: 通常为 4GB

建议值: 在正常运行条件下预计存在的任何“空闲”内存量

35.3. work_mem

定义内部排序操作、索引操作和哈希表在数据库切换到磁盘文件之前可以消耗的内存量。此值定义每个操作的可用内存；复杂的查询可能会并行运行多个排序或哈希操作，并且每个连接的会话都可能正在执行查询。

因此，您必须在增加此值之前考虑连接的数量以及预期查询的复杂性。增加的好处是，可以完成更多此类操作的处理，包括 ORDER BY 和 DISTINCT 子句、合并和哈希连接、基于哈希的聚合和基于哈希的子查询处理，而不会产生磁盘写入。增加的代价是每个连接都会使用的内存，在生产级别的连接下，这可能会非常高。

默认值: 1MB

建议值: 32MB

35.4. maintenance_work_mem

定义用于维护操作的内存量，包括清理、索引和外键创建。由于这些操作不是很常见，因此更高的值只会偶尔产生代价，并且可能会大大加快维护活动。如下所示，在执行多个 CREATE INDEX 或 VACUUM 调用之前，可以为单个会话交替增加此参数。

SET maintenance_work_mem TO '128MB';
VACUUM ANALYZE;
SET maintenance_work_mem TO '16MB';

默认值: 16MB

建议值: 128MB

35.5. wal_buffers

设置用于预写日志 (WAL) 数据的内存量。预写日志提供了一种高性能机制，用于确保数据完整性。在每个更改命令期间，更改的效果首先写入 WAL 文件并刷新到磁盘。只有在 WAL 文件刷新后，更改才会写入数据文件本身。这允许以最佳和异步的方式将数据文件写入磁盘，同时确保在发生崩溃时，可以从 WAL 恢复所有数据更改。

此缓冲区的大小只需要足够大以容纳单个典型事务的 WAL 数据即可。虽然默认值通常足以满足大多数数据，但地理空间数据往往要大得多。因此，建议增加此参数的大小。

默认值: 64kB

建议值: 1MB

35.6. checkpoint_segments

此值设置自动 WAL 检查点之间可以填充的最大日志文件段数（通常为 16MB）。WAL 检查点是 WAL 事务序列中的一个点，在该点保证数据文件已更新为检查点之前的所有信息。此时，所有脏数据页都会刷新到磁盘，并将检查点记录写入日志文件。这允许崩溃恢复过程找到最新的检查点记录并应用所有后续日志段来完成数据恢复。

由于检查点过程需要将所有脏数据页刷新到磁盘，因此会产生大量的 I/O 负载。上述相同的论点适用；地理空间数据足够大，可以打破非地理空间优化。增加此值将防止过多的检查点，尽管这可能会导致服务器在发生崩溃时重新启动速度较慢。

默认值: 3

建议值: 6

35.7. random_page_cost

这是一个无单位的值，表示从磁盘随机访问页面的成本。此值相对于许多其他成本参数，包括顺序页面访问和 CPU 操作成本。虽然此值没有万能的解决方案，但默认值通常是保守的，适用于在旋转介质上运行的数据库。SSD 的随机访问成本应设置得更低。

可以使用 SET random_page_cost TO 2.0 命令在每个会话的基础上设置此值，这对于测试它如何影响查询计划非常有用。

默认值: 4.0

建议值: 旋转介质为 2.0，SSD 为 1.0

35.8. seq_page_cost

此参数控制顺序页面访问的成本。此值通常不需要调整，但是此值与 random_page_cost 之间的差异会极大地影响查询计划器所做的选择。此值也可以在每个会话的基础上设置。

默认值: 1.0

建议值: 1.0

35.9. 重新加载配置

在进行这些更改后，保存更改并重新加载配置。最简单的方法是重新启动 PostgreSQL 服务。

  • 在 pgAdmin 中，右键单击服务器PostGIS (localhost:5432)，然后选择断开连接。

  • 在 Windows 服务 (services.msc) 中，右键单击 PostgreSQL，然后选择重新启动。

  • 返回 pgAdmin，再次单击服务器，选择断开连接。