PostgreSQL 性能调优
PostgreSQL 是一个对象关系型数据库，由全球各地的开发者在互联网上共同开发。它是 Oracle 和 Informix 等商业数据库的开源替代品。
PostgreSQL 最初在加州大学伯克利分校开发。1996 年，一个团队开始在互联网上开发该数据库。他们使用电子邮件分享想法，并使用文件服务器共享代码。如今，PostgreSQL 在功能、性能和可靠性方面可与专有数据库相媲美。它具有事务、视图、存储过程和参照完整性约束。它支持大量的编程接口，包括 ODBC、Java (JDBC)、Tcl/Tk、PHP、Perl 和 Python。得益于一批才华横溢的互联网开发者，PostgreSQL 持续快速改进。
数据库性能调优有两个方面。一是提高数据库对计算机中 CPU、内存和磁盘驱动器的利用率。二是优化发送到数据库的查询。本文讨论性能调优的硬件方面。查询优化使用 SQL 命令（如 CREATE INDEX、VACUUM、VACUUM ANALYZE、CLUSTER 和 EXPLAIN）完成。这些内容在我的书《PostgreSQL: 入门与概念》中有讨论，网址为 www.postgresql.org/docs/awbook.html [另见 Stephanie Black 在第 76 页的评论]。
为了理解硬件性能问题，重要的是要理解计算机内部发生了什么。为简单起见，可以将计算机视为由存储器包围的中央处理单元 (CPU)。与 CPU 在同一芯片上的是几个 CPU 寄存器，它们存储中间结果以及各种指针和计数器。围绕 CPU 寄存器的是 CPU 缓存，它保存最近访问的信息。CPU 缓存之外是大量的随机存取主内存 (RAM)，它保存正在执行的程序和数据。主内存之外是磁盘驱动器，它存储更大量的信息。磁盘驱动器是唯一的永久存储区域，因此计算机关闭时要保留的任何内容都必须放在那里（见表 1）。图 1 显示了 CPU 周围的存储区域。
图 1. 存储区域
您可以看到，存储区域的大小随着它们离 CPU 越来越远而增加。理想情况下，大量的永久内存可以放置在 CPU 旁边，但这会太慢且成本太高。在实践中，最常用的信息存储在 CPU 旁边，而不太常用的信息存储在更远的地方，并在需要时带到 CPU。
不同存储区域之间的信息移动自动发生。编译器确定哪些信息应存储在寄存器中。CPU 芯片逻辑将最近使用的信息保存在 CPU 缓存中。操作系统控制哪些信息存储在 RAM 中，并在 RAM 和磁盘驱动器之间来回传输。
数据库管理员无法有效地调整 CPU 寄存器和 CPU 缓存。有效的数据库调优包括增加 RAM 中有用信息的量，从而尽可能避免磁盘访问。
您可能认为这很容易做到，但事实并非如此。计算机的 RAM 包含许多东西，包括正在执行的程序、程序数据和堆栈、PostgreSQL 共享缓冲区缓存和内核磁盘缓冲区缓存。适当的调优包括在尽可能多地将数据库信息保存在 RAM 中的同时，不致对操作系统的其他区域产生不利影响。
PostgreSQL 不直接更改磁盘上的信息。相反，它请求将数据读取到 PostgreSQL 共享缓冲区缓存中。PostgreSQL 后端然后读取/写入块，最后将它们刷新回磁盘。需要访问表的后端首先在此缓存中查找所需的块。如果它们已在那里，则可以立即继续处理。否则，将向操作系统发出加载块的请求。块从内核磁盘缓冲区缓存或磁盘加载。这些可能是昂贵的操作。
默认的 PostgreSQL 配置分配 64 个共享缓冲区。每个缓冲区为 8 千字节。增加缓冲区数量使后端更有可能在缓存中找到他们需要的信息，从而避免昂贵的操作系统请求。
您可能会想，“我只需将所有 RAM 都给 PostgreSQL 共享缓冲区缓存。” 但是，如果您这样做，内核或任何程序都将没有空间运行。PostgreSQL 共享缓冲区缓存的合适大小是在不致对其他活动产生不利影响的情况下，最大的有用大小。
要理解不利活动，您需要理解 UNIX 操作系统如何管理内存。如果有足够的内存来保存所有程序和数据，则几乎不需要内存管理。但是，如果所有内容都无法装入 RAM，则内核开始强制将内存页写入称为交换的磁盘区域。它移动最近未使用的页面。此操作称为交换页出。页出不是问题，因为它们发生在不活动期间。糟糕的是，当这些页面必须从交换空间中调入时，这意味着必须将已移至交换空间的旧页面调回 RAM。这称为交换页入。这很糟糕，因为当页面从交换空间移动时，程序会暂停，直到页入完成。
页入活动由系统分析工具（如 vmstat 和 sar）显示，表明没有足够的可用内存来有效运行。不要将交换页入与普通页入混淆，普通页入可能包括作为正常系统操作一部分从文件系统读取的页面。如果您找不到交换页入，则许多页出是一个很好的指标，表明您也在进行交换页入。
您可能想知道为什么缓存大小如此重要。首先，想象一下 PostgreSQL 共享缓冲区缓存足够大，可以容纳整个表。重复顺序扫描表将不需要磁盘访问，因为所有数据都已在缓存中。现在想象一下缓存比表小一个块。顺序扫描表会将所有表块加载到缓存中，直到最后一个块。当需要该块时，将删除最旧的块，在这种情况下，它是表的第一个块。当另一个顺序扫描发生时，第一个块不再在缓存中，并且为了将其加载到缓存中，将删除最旧的块，在这种情况下，它现在是表中的第二个块。这种推出下一个所需块的情况一直持续到表的末尾。这是一个极端的例子，但您可以看到，减少一个块可能会将缓存的效率从 100% 变为 0%。这表明找到合适的缓存大小可以显着影响性能。
理想情况下，PostgreSQL 共享缓冲区缓存将足够大，可以容纳最常用的表，并且足够小，以避免交换页入活动。请记住，postmaster 在启动时会分配所有共享内存。即使没有人访问数据库，此区域的大小也保持不变。某些操作系统会将未引用的共享内存页出，而其他操作系统会将共享内存锁定到 RAM 中。《PostgreSQL 7.2 管理员指南》包含有关各种操作系统的内核配置信息 (www.postgresql.org/devel-corner/docs/admin/kernel-resources.html)。
另一个调优参数是用于排序批处理的内存量。在对大型表或结果进行排序时，PostgreSQL 会将它们分部分排序，并将中间结果放在临时文件中。然后合并和重新排序这些文件，直到所有行都排序完毕。增加批处理大小会创建更少的临时文件，并且通常可以加快排序速度。但是，如果排序批处理太大，它们会导致页入，因为排序批处理的部分内容在排序期间会被分页到交换空间。在这些情况下，使用较小的排序批处理和更多的临时文件要快得多，因此，交换页入再次决定何时分配了过多的内存。请记住，此参数用于每个执行排序的后端，无论是用于 ORDER BY、CREATE INDEX 还是用于合并连接。几个同时进行的排序将使用此内存量的数倍。
缓存大小和排序大小都会影响内存使用率，因此您不能在不影响另一个的情况下最大化一个。请记住，缓存大小在 postmaster 启动时分配，而排序大小则因执行的排序次数而异。通常，缓存大小比排序大小更重要。但是，某些使用 ORDER BY、CREATE INDEX 或合并连接的查询可能会在较大的排序批处理大小下看到速度的提升。
此外，许多操作系统限制了可以分配的共享内存量。增加此限制需要特定于操作系统的知识来重新编译或重新配置内核。《PostgreSQL 7.1 管理员指南》中可以找到更多信息 (www.postgresql.org/docs/admin/kernel-resources.html)。
磁盘驱动器的物理特性使其性能特征与其他存储区域（本文中提到的）不同。其他存储区域可以以相同的速度访问任何字节。磁盘驱动器及其旋转的盘片和移动的磁头，访问磁头当前位置附近的数据比访问远处的数据快得多。
将磁盘磁头移动到盘片上的另一个柱面需要相当长的时间。UNIX 内核开发人员知道这一点。在磁盘上存储大文件时，他们会尝试将文件的各个部分彼此靠近放置。例如，假设一个文件在磁盘上需要十个块。操作系统可能会将块 1-5 放在一个柱面上，将块 6-10 放在另一个柱面上。如果从头到尾读取文件，则只需要两次磁头移动——一次到达包含块 1-5 的柱面，另一次到达块 6-10。但是，如果非顺序读取文件，例如，块 1、6、2、7、3、8、4、9、5、10；则需要十次磁头移动。如您所见，对于磁盘，顺序访问比随机访问快得多。这就是为什么如果需要读取表的大部分内容，PostgreSQL 更喜欢顺序扫描而不是索引扫描。这也突出了缓存的价值。
在数据库活动期间，磁盘磁头会移动很多。如果发出过多的读/写请求，驱动器可能会饱和，从而导致性能下降（Vmstat 和 sar 可以提供有关每个磁盘驱动器上的活动量的信息）。
解决磁盘饱和问题的一种方法是将一些 PostgreSQL 数据文件移动到其他磁盘。请记住，将文件移动到同一磁盘驱动器上的其他文件系统无济于事。驱动器上的所有文件系统都使用相同的磁盘磁头。数据库访问可以通过以下几种方式分散到磁盘驱动器上
移动数据库—initlocation 允许您在不同的驱动器上创建数据库。
移动表—符号链接允许您将表和索引移动到不同的驱动器。移动应仅在 PostgreSQL 关闭时进行。此外，PostgreSQL 不知道符号链接，因此如果您删除表并重新创建它，它将在该数据库的默认位置创建。在 7.1 中，pg_database.oid 和 pg_class.relfilenode 将数据库、表和索引名称映射到它们的数字文件名。
移动索引—符号链接允许将索引从其堆表移动到不同的驱动器。这允许在一个磁盘上执行索引扫描，而第二个磁盘执行堆查找。
移动连接—符号链接允许将连接表移动到单独的磁盘。如果连接表 A 和表 B，则可以在一个驱动器上执行表 A 的查找，而在第二个驱动器上执行表 B 的查找。
移动日志—符号链接可用于将 pg_xlog 目录移动到不同的磁盘驱动器。（Pg_xlog 存在于 PostgreSQL 7.1 及更高版本中。）与其他写入不同，PostgreSQL 日志写入必须在完成事务之前刷新到磁盘。缓存不能用于延迟这些写入。为日志写入使用单独的磁盘允许磁盘磁头停留在当前的日志柱面上，以便可以在没有磁头移动延迟的情况下执行写入。您可以使用 postgres -F 参数来防止日志写入刷新到磁盘，但操作系统崩溃可能需要从备份还原。
其他选项包括使用 RAID 功能将单个文件系统分散到多个驱动器上。
