33. PostgreSQL 基础调优

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

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

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

注意

这些值仅供参考；每个环境都不同，需要进行测试才能确定最佳配置。但本节应该可以帮助您顺利开始。

33.1. shared_buffers

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

默认值：通常为 32MB

推荐值：大约 75% 的数据库内存，最大约为 2GB

33.2. effective_cache_size

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

默认值：通常为 4GB

推荐值：在正常运行条件下预计可用的“空闲”内存量

33.3. work_mem

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

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

默认值：1MB

推荐值：32MB

33.4. maintenance_work_mem

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

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

默认值：16MB

推荐值: 128MB

33.5. wal_buffers

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

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

默认值: 64kB

推荐值: 1MB

33.6. checkpoint_segments

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

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

默认值: 3

推荐值: 6

33.7. random_page_cost

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

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

默认值: 4.0

推荐值: 旋转介质为 2.0，SSD 为 1.0

33.8. seq_page_cost

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

默认值: 1.0

推荐值: 1.0

33.9. 重新加载配置

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

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

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

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