硬件性能调优（翻译）

原文：https://momjian.us/main/writings/pgsql/hw_performance/index.html

是Bruce大神的一篇博客。

注：原文中的图片都不能显示了，所以本文也没有...

 

POSTGRESQL是一个由一群遍布全球的开发人员在因特网上开发的对象关系数据库。它是Oracle和Informix等商业数据库的开源替代品。

POSTGRESQL最初是由加州大学伯克利分校开发的。1996年，一个小组开始在互联网上开发数据库。他们使用电子邮件来分享想法，使用文件服务器来共享代码。现在，POSTGRESQL在特性、性能和可靠性方面可以与商业数据库相媲美。它具有事务、视图、存储过程和引用完整性约束。它支持大量的编程接口，包括ODBC、Java (JDBC)、TCL/TK、PHP、Perl和Python。POSTGRESQL继续以惊人的速度进步，这要感谢有才华的互联网开发人员。
 

性能的概念

数据库性能调优有两个方面。一个是改进数据库对计算机中CPU、内存和磁盘驱动器的使用。第二个是优化发送到数据库的查询。本文将讨论性能调优的硬件方面。查询的优化是使用CREATE INDEX、VACUUM、VACUUM FULL、ANALYZE、CLUSTER和EXPLAIN等SQL命令完成的。这些都在我的书中讨论过：https://momjian.us/main/writings/pgsql/aw_pgsql_book/.

 

要理解硬件性能问题，了解计算机内部发生的事情是很重要的。为简单起见，计算机可以被看作是一个被存储设备包围的中央处理单元(CPU)。在CPU的同一个芯片上有几个CPU寄存器，它们存储中间结果和各种指针和计数器。围绕这一点的是CPU缓存，它保存了最近访问的信息。在CPU缓存之外是大量的随机访问主存(RAM)，它保存着正在执行的程序和数据。在这个主存储器之外是磁盘驱动器，它可以存储更多的信息。磁盘驱动器是唯一的永久存储区域，所以当电脑关闭时，任何要保存的东西都必须放在那里。

存储区域的大小随着离CPU越远而增加。理想情况下，可以在CPU旁边放置大量的永久内存，但这将过于缓慢和昂贵。在实践中，最常用的信息存储在CPU旁边，而访问频率较低的信息则存储在更远的地方，并根据需要提交给CPU。

 

将信息保存在CPU附近

在不同存储区域之间自动移动信息。编译器决定哪些信息应该存储在寄存器中。CPU芯片逻辑将最近使用的信息保存在CPU缓存中。操作系统控制哪些信息存储在RAM中，并在磁盘驱动器中来回传输。

数据库管理员无法有效地调优CPU寄存器和CPU缓存。有效的数据库调优包括增加RAM中有用信息的数量，从而在可能的情况下防止磁盘访问。

 

你可能认为这很容易做到，但事实并非如此。计算机的RAM包含很多东西:

执行程序

程序数据和堆栈

共享缓冲区缓存

内核磁盘缓冲缓存

内核

适当的调优包括在RAM中保留尽可能多的数据库信息，同时不影响操作系统的其他领域。

 

共享缓冲区缓存

POSTGRESQL不会直接更改磁盘上的信息。相反，它请求将数据读入POSTGRESQL共享缓冲区缓存。POSTGRESQL backends进程然后读写这些块，最后将它们刷新回磁盘。

需要访问表的backend进程首先在这个缓存中查找所需的块。如果他们已经在那里，他们可以立即继续处理。如果没有，则会发出操作系统请求来加载这些块。这些块要么从内核磁盘缓冲区缓存加载，要么从磁盘加载。这可能是昂贵的操作。

默认的POSTGRESQL配置分配1000个共享缓冲区。每个缓冲区是8千字节。增加缓冲区的数量使backends更有可能在缓存中找到它们需要的信息，从而避免昂贵的操作系统请求。可以通过使用postmaster命令行标志或修改postgresql.conf中的shared_buffers的值来进行更改。

 

多大才算太大?

你可能会想，“我将把我所有的RAM都给POSTGRESQL共享缓存。然而，如果你这样做了，就没有空间让内核或任何程序运行了。POSTGRESQL共享缓冲区缓存的合适大小是最大的，并且不会对其他活动产生负面影响。

要理解逆向活动，您需要理解UNIX操作系统如何管理内存。如果有足够的内存来容纳所有的程序和数据，则需要很少的内存管理。但是，如果所有的东西都不能放进RAM中，内核就开始将内存页强制放到一个称为swap的磁盘区域中。它可以移动最近没有使用过的页面。这个操作称为交换分页。分页不是问题，因为它们发生在不活动的时期。糟糕的是，当这些页面必须从swap中返回时，这意味着为了交换而移出的旧页面必须移回RAM中。这被称为交换页。这很糟糕，因为当页面从交换区移出时，程序将挂起，直到页面导入完成。

vmstat和sar等系统分析工具显示Pagein活动，表明没有足够的内存可用来有效地运行。不要混淆交换页和普通页，普通页可能包括作为正常系统操作的一部分从文件系统读取的页。如果您无法找到交换页，那么许多页面换出是您正在进行交换页换的一个很好的指示。

 

缓存大小的影响

 

您可能想知道为什么缓存大小如此重要。首先，假设POSTGRESQL共享缓冲区缓存足够大，可以容纳整个表。对表的重复连续扫描不需要磁盘访问，因为所有的数据都已经在缓存中了。现在假设缓存比表小一个块。对表的连续扫描将把所有的表块加载到缓存中，直到最后一个表块。当需要该块时，将删除最古老的块，在本例中是表的第一个块。当另一个连续扫描发生时，第一个块不再在缓存中，要加载它，就需要删除最旧的块，在这种情况下，现在是表中的第二个块。从下一个需要的块中推出的操作将持续到表的末尾。这是一个极端的例子，但是你可以看到一个块的减少可以改变缓存的效率从100%到0%。这表明，找到正确的缓存大小可以极大地影响性能。

 

共享缓冲区缓存的适当大小

理想情况下，POSTGRESQL的共享缓存应该是:

大到足以容纳大多数经常访问的表

足够小，以避免swap pagein活动

请记住，postmaster在启动时分配所有共享内存。即使没有人访问数据库，这个区域也保持相同的大小。一些操作系统将未引用的共享内存分页出来，而另一些操作系统则将共享内存锁定到RAM中。优先使用锁定的共享内存。POSTGRESQL管理员指南有关于不同操作系统的内核配置的信息：http://developer.postgresql.org/docs/postgres/kernel-resources.html

 

用于排序的内存大小

另一个调优参数是用于排序批次的内存量。当对大型表或结果进行排序时，POSTGRESQL会将它们按部分排序，将中间结果放在临时文件中。然后对这些文件进行合并和重排序，直到对所有行进行排序。增加批处理的大小会创建更少的临时文件，并且通常允许更快的排序。但是，如果排序批次太大，就会导致pageins，因为排序批中的某些部分会在排序期间被换出用以交换。在这些情况下，使用更小的排序批和更多的临时文件要快得多，因此，swap pageins决定什么时候分配了过多的内存。请记住，这个参数用于每个执行排序的backend，无论是orderby、CREATE INDEX，还是merge join。多个同步排序将使用数倍于此数量的内存。

可以通过命令行标记或修改postgresql.conf文件中sort_mem的值来修改这个值。

 

缓存大小和排序大小

缓存大小和排序大小都会影响内存使用，因此不能最大化其中一个而不影响另一个。请记住，缓存大小是在postmaster启动时分配的，而排序大小取决于执行的排序数量。一般来说，缓存大小比排序大小更重要。但是，使用ORDER BY、CREATE INDEX或merge join的某些查询在较大的排序批处理规模下可能会得到加速。

此外，许多操作系统限制可以分配多少共享内存。增加这个限制需要操作系统特有的知识来重新编译或重新配置内核。更多信息可以在POSTGRESQL管理员指南http://developer.postgresql.org/docs/postgres/kernel-resources.html中找到。

作为调优的开始，如果只有几个大会话，则使用15%的RAM作为缓存大小，使用2% -4%作为排序大小，如果有很多小会话，则使用更小的内存大小。您可以尝试提高它，看看性能是否得到改善，以及是否没有发生交换。如果共享缓冲区过大，维护过多的缓冲区就会浪费开销，而且它会占用其他进程可以使用的RAM，并作为额外的内核磁盘缓冲区缓存。

一个有价值的服务器参数是effective_cache_size。优化器使用这个参数来估计内核的磁盘缓冲区缓存的大小。在具有统一缓冲缓存的内核中，这个值应该设置为内核中未使用RAM的平均数量，因为这样的内核使用未使用RAM来缓存最近访问的磁盘页面。在具有固定大小磁盘缓冲区缓存的内核上，这应该设置为内核缓冲区缓存的大小，通常为RAM的10%。

磁盘位置

将磁头移动到磁盘上的另一个柱面上需要相当长的时间。Unix内核开发人员知道这一点。当在磁盘上存储一个大文件时，他们尝试将文件的各个部分放置在彼此附近。例如，假设一个文件需要磁盘上的10个块。操作系统可以将block 1-5放置在一个柱面上，block 6-10放置在另一个柱面上。如果从头到尾读取文件，只需要进行两次头部移动——一次是到达柱面保持块1-5，另一次是到达块6-10。然而，如果文件是非顺序读取的，例如，块1,6,2,7,3,8,4,9,5,10;需要十个头部动作。如您所见，对于磁盘，顺序访问要比随机访问快得多。这就是为什么当需要读取表的重要部分时，POSTGRESQL更喜欢顺序扫描而不是索引扫描的原因。这也突出显示了缓存的价值。

多个磁盘

在数据库活动期间，磁头移动了很多。如果发出太多的读/写请求，驱动器可能会饱和，导致性能较差。(Vmstat和sar可以提供关于每个磁盘驱动器上的活动量的信息。)

磁盘饱和的一个解决方案是将一些POSTGRESQL数据文件移动到其他磁盘。请记住，将文件移动到同一磁盘驱动器上的其他文件系统没有帮助。驱动器上的所有文件系统都使用相同的磁头。

数据库访问可以通过几种方式分散在磁盘驱动器:

移动数据库、_Tables _Indexes

表空间允许您在不同的驱动器上创建对象。

移动写前日志

可以使用initdb -X和符号链接将pg_wal目录移动到另一个磁盘驱动器。与其他写操作不同，POSTGRESQL的日志写操作必须在完成一个事务之前刷新到磁盘。缓存不能用于延迟这些写操作。有一个单独的磁盘用于写日志，这允许磁盘头保持在当前日志柱面上，这样就可以在没有磁头移动延迟的情况下执行写操作。(您可以使用postgres -F参数来防止日志写入刷新到磁盘，但是操作系统崩溃可能需要从备份中恢复。)

其他选项包括使用RAID磁盘将单个文件系统分散到多个驱动器。镜像可以减慢数据库的写入速度，但是会加快数据库的读取速度，因为数据可以从任意一个驱动器中检索。由于RAID 1+0或RAID 0+1具有性能分条、可靠性镜像等优点，很多站点使用RAID 1+0或RAID 0+1。对于6个或6个以上的驱动器，RAID 5的运行速度更快。理想情况下，RAID 5将具有电池支持的缓存，因此可以以一种有效的方式将写操作刷新到磁盘，而不是在写操作击中磁盘时减慢应用程序的速度。

 

磁盘驱动器缓存

现代磁盘驱动器都有一个读和写缓存。读缓存在磁盘内存中保持最近请求的磁盘块可用。写缓存保存写请求，直到它们能够有效地存储在磁盘盘片上。您可能意识到这可能是一个问题——如果驱动器在保存尚未写到磁盘盘片上的写数据时断电了，该怎么办?一些磁盘驱动器和RAID控制器都有一个电池支持的写缓存，以保证数据的安全，并在恢复满电时将数据写入磁盘。然而，大多数驱动器没有这个功能，因此是不可靠的。

幸运的是，您可以在大多数驱动器中关闭写缓存。SCSI驱动器通常关闭写缓存。IDE驱动器通常打开写缓存，但是可以通过操作系统命令link hdparm -W0, sysctl hw.ata关闭。wc = 0，或scsicmd。然而，一些IDE驱动器会报告他们的写缓存已经关闭，但仍然使用它，并且仍然不可靠。如果没有广泛的测试，很难确定哪些驱动器存在问题。

因为PostgreSQL在每个事务提交时都使用fsync()将预写日志写到磁盘上，并等待写入完成，所以如果使用了写缓存，用户将会看到性能的巨大提升。因此，为了性能和可靠性，PostgreSQL最好使用电池支持的写缓存。

SCSI和IDE

SCSI驱动器通常明显地比IDE驱动器更昂贵。SCSI驱动器有一个协议，用于与控制器和操作系统通信，而IDE驱动器要简单得多，一次只能接受一个请求。带标记队列的SCSI驱动器可以接受多个请求，并可以重新排列它们以提高效率。这就是为什么SCSI和IDE磁盘对于单用户或单文件I/O操作具有类似的性能特征，但是当请求由多个用户或进程生成时，SCSI的性能要比IDE好得多。因此，对于大量使用的数据库服务器，SCSI是首选。

事实上，SCSI或IDE只是通常区分两种主要类型的驱动器的一种方式——为高性能和可靠性而设计的企业驱动器，和为最小化成本而设计的个人计算机驱动器。本文http://www.seagate.com/content/docs/pdf/whitepaper/D2c_More_than_Interface_ATA_vs_SCSI_042003.pdf出色地描述了基于性能/可靠性或降低的成本生产驱动器时的权衡。它是根据驱动器的特性选择驱动器的优秀指南。

 

文件系统

一些操作系统支持多磁盘文件系统。在这种情况下，很难知道哪个文件系统性能最好。POSTGRESQL通常在传统的Unix文件系统上表现最好，比如许多操作系统都支持的BSD UFS/FFS文件系统。UFS默认的8K块大小与POSTGRESQL的页面大小相同。您可以在基于日志和日志的文件系统上运行，但是这些会在fsync处理预写日志时造成额外的开销。基于svr3的旧文件系统变得过于分散，无法产生良好的性能。

在Linux上选择文件系统特别困难，因为有很多文件系统可供选择，但没有一个是最优的:ext2不是完全安全的，ext3、XFS和JFS是基于日志的，Reiser针对小文件进行了优化，并进行日志记录。日志文件系统可能比ext2慢得多，但是当需要崩溃恢复时，ext2不是一个选项。如果必须使用ext2，则在挂载时启用同步。有些人建议使用data=writeback挂载ext3文件系统。

NFS和其他远程文件系统不建议POSTGRESQL使用。NFS不具有与本地文件系统相同的文件系统语义，这些不一致可能导致数据可靠性或崩溃恢复问题。

多个cpu

POSTGRESQL使用多进程模型，这意味着每个数据库连接都有自己的Unix进程。因此，所有多cpu操作系统都可以在可用的cpu之间扩展多个数据库连接。但是，如果只有一个数据库连接是活动的，那么它只能使用一个CPU。POSTGRESQL并没有使用多线程来允许一个进程使用多个cpu。

检查点

每隔几分钟就要检查一次。如果它们一分钟发生几次，性能就会受到影响。要确定检查是否过于频繁，请在服务器日志中查找由checkpoint_warning发出的消息。如果您在30秒的周期内检查不止一次，则会发出此消息。

减少检查点频率需要增加data/pg_wal中创建的写预写日志文件的数量。每个文件都有16兆字节，所以这会影响磁盘使用。默认设置使用最小数量的日志文件。为了减少检查点频率，需要增加该参数:

checkpoint_segments = 3

缺省值为3。增加它，直到每隔几分钟才会有检查点。另一个可能出现的日志消息是:

XLogWrite:创建的新日志文件—考虑增加WAL_FILES

这个消息表明wal_files参数应该在postgresql.conf中增加。

 

结论

幸运的是，POSTGRESQL不需要太多的调优。大多数参数自动调整，以保持最佳性能。缓存大小和排序大小是管理员可以控制的两个参数，以便更好地使用可用内存。磁盘访问也可以跨驱动器进行。其他参数可以在share/postgresql.conf.sample中设置。您可以将这个文件复制到data/ POSTGRESQL .conf中，以试验POSTGRESQL中一些更奇异的参数。