PostgreSQL 8.0 性能清单

Este texto é a tradução do texto de Josh Berkus (desenvolvedor do PostgreSQL). 
Este texto é um bom ponto de partida para quem está aprendendo sobre tuning no PostgreSQL 8.0. 
Espero em breve atualizar o artigo para as versões 8.1 e 8.2.

这里是一组配置您的 PostgreSQL 8.0 服务器的规则。以下内容很多是基于实际的证据或可扩展性测试；关于数据库性能，我们和 OSDL 仍在不断探索。然而，这应该是一个好的开始。以下所有信息都是从 2005 年 1 月 12 日开始有效的，之后将进行更新。关于以下配置的讨论超出了我在 General Bits 中进行的讨论。

配置您的 PostgreSQL 服务器的五个硬件原则

磁盘 > 内存 > CPU

如果您要为 PostgreSQL 服务器花钱，就花在高性能磁盘阵列上，并配备中等处理器和充足的内存。如果您有更多资金，那就多买一些 RAM。PostgreSQL 与其他支持 ACID 的数据库一样，使用非常密集的 I/O 操作，而且应用程序很少会比 SCSI 卡（当然也有一些例外）更频繁地使用 CPU。这适用于小型和大型服务器；如果这能让您购买高性能 RAID 卡或多个磁盘，那么就购买价格低廉的 CPU。

将事务日志与数据库分离：

假设您已经在磁盘组上投入了资金用于搭建一个尺寸合理的阵列，那么除了将所有东西都放到一个 RAID 中之外，还有许多更明智的选择。首先，将事务日志 (pg_xlog) 放到自己的磁盘资源（阵列或磁盘）中，这会对高写入量数据库的性能产生约 12% 的提升。这在磁盘速度较慢的小型系统（例如 SCSI 或 IDE）中尤其重要：即使在只有两个磁盘的服务器上，您也可以将事务日志放到操作系统磁盘上，并从中获益。

RAID 1+0/0+1 > RAID 5：

拥有 3 个磁盘的 RAID 5 一直是经济型服务器供应商之间一个不幸的标准。这导致了 PostgreSQL 最慢的磁盘配置；与使用普通 SCSI 磁盘相比，您至少可以预期查询速度降低 50%。另一方面，对于 2、4 或 6 个磁盘的组，请关注 RAID 1 或 1+0。超过 6 个磁盘，RAID 5 的性能开始变得可以接受，而且与您的特定控制器相比，优势更加明显。然而，最重要的是，使用便宜的 RAID 卡可能存在风险；与内置于服务器上的 Adaptec 卡相比，使用软件 RAID 始终是更好的选择。

应用程序应该良好共存：

我在许多组织中看到的一个重大错误是将 PostgreSQL 放置在一个与其他应用程序争夺相同资源的服务器上。最糟糕的情况是将 PostgreSQL 与其他数据库放在同一台机器上；两个数据库都会争夺磁盘访问带宽和操作系统的磁盘缓存，并且性能都会下降。文件服务器和安全日志程序也很糟糕。PostgreSQL 可以与主要使用 CPU 和内存密集型应用程序（如 Apache）共享同一台机器，前提是确保有足够的内存。

您将在 PostgreSQL.Conf 文件中需要进行的十二项调整

PostgreSQL.conf 文件中存在许多真正令人望而生畏的新选项。即使是过去 5 个版本中已经熟悉的选项也更改了名称和参数格式。它们旨在为数据库管理员提供更多控制，但可能需要一些时间才能使用。

以下是大多数 DBA 想要更改的配置，重点是在任何其他方面都优先考虑提高性能。有一些配置是大多数用户特别不愿意更改的，但谁要是更改了就会发现它们不可或缺。对于这些配置，您需要耐心等待本书出版。

请记住：PostgreSQL.conf 中的配置需要取消注释才能生效，但重新注释它们不一定能恢复默认值！

连接

listen_addresses：替换 7.4 中的 tcp_ip 和 virtual_hosts 配置。大多数安装中的默认值为 localhost，仅允许通过控制台进行连接。大多数 DBA 都会将其更改为“*”，这意味着所有可访问的接口（在 hba.conf 中正确配置权限后）都将使 PostgreSQL 可通过网络访问。与之前版本相比，"localhost" 允许通过“循环回环”接口 127.0.0.1 进行连接，从而启用许多基于 Web 服务器的实用程序。

max_connections：与之前版本完全相同，这需要设置为预期需要的当前并发连接数。高配置需要更多共享内存 (shared_buffers)。由于 PostgreSQL 和主机操作系统的每个连接开销都可能非常高，因此如果需要为大量用户提供服务，则使用连接池非常重要。例如，在一个具有中等 32 位处理器的 Linux 服务器上，150 个活动连接将消耗大量资源，而此硬件的限制为 600 个连接。当然，更强大的硬件将允许更多连接。

内存

shared_buffers：提醒一下：这不是 PostgreSQL 将使用的总内存。这是专门用于 PostgreSQL 的内存块，用于活动操作，并且应该是机器总 RAM 中最小的部分，因为 PostgreSQL 也使用磁盘缓存。不幸的是，shared buffers 的确切大小需要一个复杂计算，该计算需要考虑总 RAM、数据库大小、连接数和查询复杂性。因此，在分配时最好遵循一些规则，并监控服务器（尤其是 pg_statio 视图）以确定调整。

在专用服务器上，有用的值通常在 8MB 到 400MB 之间（对于 8K 页，在 1000 到 50000 之间）。增加 shared buffers 数量的因素包括数据库活动部分的大小、查询的大小和复杂性、并发连接数量、长时间过程和事务、可用的 RAM 数量、更快的 CPU 或更多 CPU（显然），以及同一台机器上的其他应用程序。与许多人的预期相反，分配过多的 shared_buffers 甚至可能会降低性能，从而增加挖掘它的所需时间。以下是一些基于经验和 TPC 测试的 Linux 机器示例

- 笔记本电脑，赛扬处理器，384MB RAM，25MB 数据库：12MB/1500
- Athlon 服务器，1GB RAM，10GB 数据库，用于决策支持：120MB/15000
- 四核 PIII 服务器，4GB RAM，40GB 数据库，具有 150 个连接和繁重的交易处理：240MB/30000
- 四核至强服务器，8GB RAM，200GB 数据库，具有 300 个连接和繁重的交易处理：400MB/50000

请注意，增加 shared_buffer 和一些其他内存参数将需要您修改操作系统的 System V。有关此方面的说明，请参见 PostgreSQL 主要文档。

work_mem：通常称为 sort_mem，但已重命名，因为它现在涵盖排序、聚合和一些其他操作。这部分内存不是共享内存，而是为每个操作（每个查询一次或多次）分配的；此配置用于为单个操作占用内存设置上限，在强制将内存写入磁盘之前。这应该通过将可用 RAM（在应用程序和 shared_buffers 之后）除以预期最大并发查询数乘以每个连接使用的内存数量来计算。需要考虑每个查询的 work_mem 数量；处理大型数据集需要更多内存。Web 应用程序数据库通常使用较小的数字，因为它们具有许多连接但查询简单，512K 到 2048K 通常就足够了。相反，决策支持应用程序的查询有 160 行，聚合有 1000 万行，需要大量内存，在内存充足的服务器上可能达到 500MB。对于混合使用数据库，此参数可以在运行时通过连接进行调整，以按此顺序为特定查询提供更多 RAM。

maintenance_work_mem：以前称为 vacuum_mem，这部分 RAM 由 PostgreSQL 用于 VACUUM、ANALYZE、CREATE INDEX 和添加外键。随着数据库表的增大和可用的 RAM 数量增多，您应该增加此值，以便尽可能快地执行这些操作。一个好的规则是将此值设置为磁盘上最大表或索引的 50% 到 75%，或者如果无法确定则设置为 32MB 到 256MB。

磁盘和 WAL

checkpoint_segments：定义写入操作的事务日志磁盘缓存大小。对于大多数读操作为主的 Web 数据库，您可以忽略此配置，但对于事务处理数据库或涉及大量数据加载的数据库，增加此配置对于性能至关重要。根据数据量，将其增加到 12 到 256 个片段之间，从保守地开始，如果在日志中看到警告消息，则增加它。磁盘上所需的空間等於 (checkpoint_segments * 2 + 1) * 16MB，因此請確保有足夠的磁碟空間（32 代表超過 1GB）。

max_fsm_pages: 用于设置跟踪部分空数据页的记录大小；如果设置正确，它将使 VACUUM 速度更快，并消除 VACUUM FULL 或 REINDEX 的必要性。该值应该略大于 VACUUM 之间的更新和删除操作将触及的数据页总数。确定此数字的两种方法是运行 VACUUM VERBOSE ANALYZE，或者如果您使用的是 autovacuum（见下文），则根据参数 -V 设置为数据库使用的总数据页数的百分比。fsm_pages 需要很少的内存，所以最好在这里设置得更大一些。

vacuum_cost_delay: 如果您有大型表格和大量的并发写入活动，您可能需要利用这个新功能，它可以降低 VACUUM 的 I/O 负载，但代价是让 VACUUM 运行时间更长。由于这是一个新功能，我们只进行了很少的性能测试，因此设置起来比较复杂，它包含 5 个相互依赖的配置参数。将 vacuum_cost_delay 设置为非零值将激活此功能；使用一个合理的延迟值，例如 50 到 200 毫秒。要进行微调，请增加 vacuum_cost_page_hit 并减少 vacuum_cost_page_limit 将降低 VACUUM 的影响，并使它们运行时间更长；在 Jan Wieck 的测试中，在一个事务处理测试中，延迟 200、page_hit 6 和 limit 100 将 VACUUM 的影响降低了 80% 以上，同时将其运行时间延长了三倍。

查询规划器

这些配置允许查询规划器更准确地估计操作成本，从而选择最佳执行计划。需要关注的两个配置值是

effective_cache_size: 告诉查询规划器预期可以缓存的最大数据库对象大小。通常情况下，它应该设置为总 RAM 的三分之二，如果是在专用服务器上。在混合使用服务器上，您需要估计其他应用程序将使用多少 RAM 和磁盘缓存，并将其减去。

random_page_cost: 一个变量，估计索引数据页随机访问的平均成本。在速度更快的机器上，使用快速磁盘阵列，它应该降低到 3.0、2.5 甚至 2.0。但是，如果您的数据库活动部分经常大于您的 RAM，您需要将该因子恢复到默认值 4.0。或者，您可以根据性能进行调整。如果规划器不公平地偏向顺序扫描而不是索引扫描，请降低它。如果它在不应该使用索引的情况下使用索引，请提高它。请确保测试各种查询。不要将它降低到 2.0 以下；如果需要这样做，您需要在其他方面进行调整，例如规划器的统计信息。

日志记录

log_destination: 这是对以前版本中直观的 syslog 配置的替代。您的选择是使用操作系统的管理日志（syslog 或 eventlog），或者使用一个单独的 PostgreSQL 日志（stderr）。第一个更适合监控系统；第二个更适合查找数据库问题和调整。

redirect_stderr: 如果您决定使用单独的 PostgreSQL 日志，此配置将允许您使用 PostgreSQL 自带的工具记录到文件，而不是使用命令行重定向，这将允许您进行日志轮转。将其设置为 True，然后调整 log_directory 来指定日志存放位置。默认的 log_filename、log_rotation_size 和 log_rotation_age 配置对大多数人来说已经足够了。

Autovacuum 和您

一旦您在 8.0 版本中投入生产，您就需要制定一个维护计划，其中包括 VACUUM 和 ANALYZE。如果您的数据库涉及持续的数据写入，但不需要大量的数据插入和删除或频繁的重启，这意味着您应该配置 pg_autovacuum。这比预定 VACUUM 更好，因为它：

- 表格根据其活动进行 VACUUM，排除仅进行读取操作的表格。
- VACUUM 的频率会随着数据库活动量的增长而自动增加。
- 更容易计算自由空间图，并避免数据库膨胀。

配置 autovacuum 需要从 PostgreSQL 源代码的 contrib/pg_autovacuum 目录中编译一个简单的模块（Windows 用户应该在安装包中查找 autovacuum）。您可以在 README 中找到有关配置详细统计信息的说明。然后，在 PostgreSQL 启动后，您可以将 autovacuum 启动为一个单独的进程；当 PostgreSQL 关闭时，它将自动关闭。

autovacuum 的默认设置非常保守，我想，它们更适合非常小的数据库。我通常使用更激进的设置，例如：

-D -v 400 -V 0.4 -a 100 -A 0.3

这将使 VACUUM 在 400 行 + 表格 40% 的更新或删除之后对表格进行 VACUUM，并将 ANALYZE 在 100 行 + 表格 30% 的插入、更新或删除之后进行 ANALYZE。上述设置还允许我将 max_fsm_pages 设置为数据页的 50%，确信此数字不会被低估，从而导致数据库膨胀。我们目前正在 OSDL 测试各种配置，很快就会提供更多信息。

请注意，您也可以使用 autovacuum 配置延迟选项，而不是在 PostgreSQL.conf 中配置。在具有大型表格和索引的系统中，延迟 VACUUM 非常重要；在极端情况下，它可能会阻止重要操作。

不幸的是，8.0 中的 autovacuum 有几个严重的限制，将在未来的版本中消除。

- 没有长期内存：autovacuum 会在您重启数据库时忘记所有活动。因此，如果您定期重启，您应该在重启之前或之后对整个数据库进行 VACUUM ANALYZE。
- 注意服务器的繁忙程度：有一些计划在进行 VACUUM 之前检查服务器的负载，但目前还没有这个功能。因此，如果您有极端的负载峰值，autovacuum 不适合您。

版权说明

文本最初由 Fábio Telles Rodriguez <[email protected]> 翻译成中文，发布在 http://savepoint.blog.br/checklist-de-performance-do-postgresql-80/

本文自由翻译自 Josh Berkus 于 2005 年 1 月 12 日发布在 http://www.powerpostgresql.com/PerfList 上的“PostgreSQL 8.0 Performance Checklist”。